RDBMSインデックスの概要と最適化手法

SQL

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

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

コメント

タイトルとURLをコピーしました