RDBMSのインデックスの概略的なまとめ

5年前に、RDBMSのインデックスについて、個人的に簡単にまとめていましたので、そのまとめを展開します。

今回の記事では、情報処理技術者試験の出題範囲内で、要点を箇条書きしています。
実務で使うには+αの知識(主にRDBMS固有の知識)が必要になりますが、情報処理技術者試験の知識はその+αの知識を身に付ける上での土台になります。


【インデックス全般の知識】

  • インデックスは、テーブルの検索を高速化する目的で用いる。
  • インデックスは、検索条件として指定するカラムに対して設定する。
  • テーブルのレコード数が少ない場合は、インデックスの効力が落ちる。(インデックスを使わずに順番に走査した方が早い場合もある)
  • インデックスには、レコードの挿入や更新や削除が遅くなるデメリットがある。(それらの操作を行う度にインデックスの再構成が発生するため)

【インデックスの種類と特徴】

①B+木インデックス

  • RDBMSで一般的に用いられるインデックスである。(情報処理技術者試験で「インデックス」と出たら、指定がない限りこのインデックスだと思って良い)
  • 検索キー値を用いて二分検索を行うインデックスである。
  • BETWEEN句等の範囲検索で後述のビットマップインデックスより優れる。
  • ORDER BY等のソートが必要な場合に優れる。
  • NULL検索やNOT検索では効果を発揮できない。
  • 少ない件数に絞り込めるカラムに設定すると効果が高い。例えば、一意な値が格納されるカラムに設定すると、1件のみに絞ることができ、効果が高い。逆に、二値しか持たない「性別」のようなカラムに設定しても効果は低い。
  • 検索値が等しく分布している時に効果が高い。

効果が高い例

カラムの値件数
a200
b200
c200
d200
e200
f200

効果が低い例

カラムの値件数
a1200
b200
c0
d0
e0
f0

②ビットマップインデックス

  • 検索キー値が持ち得る値をビットで保持し、それをレコード毎に保持する。
  • AND/OR条件のみの検索ではB+木インデックスより効果が高い。
  • NOT検索ではB+木インデックスより効果が高い。
  • 持ち得る値が少ないカラムに設定するとB+木インデックスより効果が高い。(例えばカラム「性別」等)

③ハッシュインデックス

  • 検索キー値をハッシュ化したものをインデックスとして用いる。(ハッシュ化…特定のアルゴリズムにより不可逆の固定長の値にすること)
  • 一意な値を検索するのに向いている。
  • データ量が増えても検索にかかる時間が変わらないと言われている。(正確には、データ量が増えるとハッシュ値の衝突が増え、時間が微増する)
  • BETWEEN句等の範囲検索には適用できない。
  • ORDER BY等のソートが必要な場合には適用できない。
  • ワイルドカードを用いて検索する場合はB+木インデックスの方が良い。

繰り返しになりますが、今回のまとめは一般論であり、RDBMS固有の知識は別途必要です。
また、今回のまとめは5年前のものなので、現在のトレンドを捉えたものではありません。

例えば、私が実務を通して知った範囲で言うと、現在では「関数インデックス」と呼ばれるインデックスを備えたRDBMSが増えています。
これは、カラムを関数で計算した結果、例えばカラム1とカラム2の合計値をインデックスとして持たせることができる、というものです。
MySQLでは、バージョン8.0.13(リリース日:2018/10/22)から導入されました。
https://blogs.oracle.com/mysql-jp/post/functional-indexes-in-mysql-jp

私も、基礎を大事にしつつも、知識のアップデートを怠らないようにしていきたいと思います。

SQL_select文の結果の結合等(集合演算子)

今回は、select文の結果を結合する文法の紹介です。
結果を結合するためには、集合演算子のUNION(重複排除したい場合はUNION ALL)を用います。
運用作業やプログラミングで複数のselect文の結果を1回のSQL文発行で取得したいことがあるので、知っておくと便利です。

ついでに、UNIONやUNION ALL以外の集合演算子についても紹介します。

【文法】

※集合演算子には以下のようなものがある

・UNION

前後のselect文の結果を結合する。
重複する結果は1行にまとめられる。

・UNION ALL

前後のselect文の結果を結合する。
重複する結果はまとめられない。

・MINUS、EXCEPT

前のselect文の結果から後のselect文の結果を取り除く。
OracleはMINUS、それ以外はEXCEPTを用いる。
(MySQLではサポート外)

・INTERSECT

前のselect文の結果と後のselect文の結果で一致するものだけを抽出する。
(MySQLではサポート外)

※「order by」は個別のselect文にはかからず、集合演算子で結合した結果全体にかかる。

【対象テーブル例】

・A支店商品

・B支店商品

【使用例】

・UNION

・UNION ALL

・MINUS(EXCEPT)

・INTERSECT


いかがでしたでしょうか。

今回は、UNIONに代表される集合演算子について記事を書きました。
何回かに分けて基本的なSQLの書き方に関する記事を書いてきましたが、これが最後になります。
これまで書いてきた文法は何れも実務では頻出なので、運用作業に携わっている人は特に覚えておいた方が良いものばかりです。知らない・使い方があやふやな文法があれば、これを機に覚えることをお勧めします。

これからも、様々な角度から役に立つ記事を書いていきたいと思います!

SQL_副問合せ・相関副問合せ

今回は副問合せと相関副問合せの紹介です。
この文法を用いると、問い合わせの結果を別の問い合わせに使い回すことができるようになり、複雑な問い合わせを1本のSQLで書けるようになります。

副問合せは相関副問合せよりも文法的に簡単で、実務、特に運用作業では頻出です。
また、性能面を考慮すると相関副問合せを使った方が良いケースが多いため、相関副問合せもプログラミングでは良く使われます。

なお、今回は紹介しませんが、副問合せはFROM句にも書くことができます。

【文法】

・副問合せ(単一行を返す場合のみ使用可能)

・副問合せ(副問合せの何れかの結果と一致することを確認)

・副問合せ(副問合せのどの結果とも一致しないことを確認)

・副問合せ(副問合せの全ての結果と比較し、全ての結果よりも(大きい、小さい等))

・副問合せ(副問合せの何れかの結果よりも(大きい、小さい等))

※SOMEの代わりにANYを使用しても良い

・相関問い合わせ(副問合せ部で存在する行のみ抽出)

※上記は正式な文法ではないが、通常は上記のように使う
※EXISTSの前のNOTをつけると存在しない行のみ抽出することができる
※EXISTSの中のSELECT文のカラム指定は問わない(1でなくても良い)

【対象テーブル例】

・商品

・消費者テスト結果

・合格基準点

【使用例】

asは省略可能である。
実務でも試験でも使用頻度が高い文法のみ例を記載する。

・=演算子

・不等号演算子

・IN句

・EXISTS句

※上記のIN句の例と同じ結果となる

【性能について補足】

EXISTS句と同様のことはIN句でも可能だが、性能面で違いが出ることが多い。
細かい所はRDBMSによって異なるため、最終的にはRDBMSの特性を調べたり実行計画を取得したりしながらチューニングするべきであるが、一般的に以下のようなことが言える。

1.実行順の違い

原則として件数の絞り込みはなるべく早い段階で行うべきであり、少ない件数に絞り込むことができる条件は先に実行するべきである。
例えば、100件中10件まで絞り込める条件と、100件中90件までしか絞り込めない条件がある場合、100件中10件まで絞り込める条件を先に実行すれば後に実行される条件はその10件(以下)のみ対象とすれば良くなるので、その方が性能が良くなる。
IN句とEXISTS句ではこの実行順が異なり、先に副問合せが実行されるのがIN句、後で副問合せが実行されるのはEXISTS句なので、副問合せで条件を絞り込めるか否かでどちらの句を使うのかを決めるべきである。

2.インデックスの使用有無

IN句、特にNOT IN句については、インデックスを使用しない逐次検索が行われてしまうことが多い。
そのため、一般論として、インデックスが使われやすいEXISTS句の方が性能が良くなることが多い。


いかがでしたでしょうか。

今回は、SQLでの副問合せと相関副問合せについて記事を書きました。
プログラムでSQLを発行する場合は複数回問い合わせを発行してその結果をプログラム側で突き合わせることの方が多いかもしれませんが、運用作業では重宝します。

複数の問い合わせを1つの問い合わせで済ませる文法としては、UNIONのような集合演算子も良く使います。
これは次回取り上げたいと思います!

SQL_重複排除と集計

今回は重複排除(distinct句)と集計(group by)の紹介です。
どちらも挙動が似ているのでまとめて紹介します。

【機能説明】

・重複排除

select文の結果について、重複を排除することができる。

・集計

指定したカラムで集計を行うことができる。
(結果として重複排除もされる)
select文で集計関数を使用することができるようになる。
(該当行数を返すCOUNT関数、最大値を返すMAX関数、最小値を返すMIN関数等)
なお、having句でgroup byで集計した結果に対して条件で抽出することができる。
(where句も同じように抽出を行うが、where句は集計前に、having句は集計後に抽出する違いがある)

【文法】

・重複排除

・集計

【対象テーブル例】

・ご意見

【使用例】

asは省略可能である。

・重複排除(distinct句)

※店舗ID=1、商品ID=4の行は2行存在するが、重複排除され1行のみ出力された。

・重複排除(group by句)

※この例のように、order by句でdistinct句と同じように重複排除を実現できる。
 しかし、本来、order by句は後述の集計で使うので、重複排除のために使うのであればdistinct句の方が意図は伝わりやすい。
 また、distinct句とorder by句で実行時間に差が出る場合もある。

・集計(count関数)

※count関数の引数にカラム名を指定すると、そのカラムがNULLではない件数を返す。
 例えば、count(ご意見件数)なら、店舗ID=1、商品ID=4の行のご意見件数は1になる。

・集計(having句での抽出)


いかがでしたでしょうか。

今回は、SQLでの重複排除と集計について記事を書きました。
SQLではこのような操作も簡単に書くことができます。
特に、運用作業でデータ加工する時に重宝すると思います。

次回は、SQLを入れ子で実行する副問合せ・相関副問合せについて取り上げたいと思います!

SQL_取得結果に対する条件判定

SELECT文で取得した結果に対して、CASE式を使用することで条件判定が可能です。
条件判定を行うことで、SQL文の出力結果を変えることができます。
プログラムのIF文の判定をSQL文の中で行っているようなイメージであり、プログラムを作るまでもない運用作業で使用する他、プログラムに組み込む場合も使い方次第でプログラムの可読性・保守性を高めることができる場合があります。

なお、NULLの時に出力結果を変えるだけであればCOALESCE関数の方がスマートなので、これも併せて紹介します。

【文法】

・CASE式(単純CASE式)

SELECT~FROMの間のカラム指定の箇所で使用する。
カラムの値に応じて返す値を指定する。

・CASE式(検索CASE式)

SELECT~FROMの間のカラム指定の箇所で使用する。
式に応じて返す値を指定する。

・COALESCE関数

SELECT~FROMの間のカラム指定の箇所で使用する。
可変長の引数を持ち、NULLではない最初の値を返す。

【対象テーブル例】

・商品

【取得例】

※ASは省略可能


いかがでしたでしょうか。

今回紹介した文法も、日々の保守運用作業の役に立つものです。
WHERE句以外の箇所でも条件を指定できることを知ると、SQLでの表現の幅が広がり、作業が効率的になります。

SELECT文の結果については、結果として表示される値を変えるだけでなく、その値の重複を排除したり、集計したりすることもできます。
こちらについては次回の記事で紹介したいと思います!