DBMSにおいて、索引(インデックス)が重要なことは認識しているが、

  • そもそもどんな索引にはどんな種類があるのか
  • どんな場合に索引をつければよいのか
  • 索引をつけた場合のデメリットにはどんなものがあるのか

理解が浅かったため、参考図書・参考URLを元に、索引設計の心得をまとめておく。

インデックスの特性

まずインデックスの特性として、以下3つが挙げられる。

1.事前に作成しておく必要がある

表が大きい場合、作成にはそれなりの時間が掛かり、インデックスのためにディスクを消費する。

2.検索に必要な情報が含まれるインデックスを(RDBが)自動的に使用する

検索に必要な列(=WHERE句に書かれた列) - 条件検索の列 - ジョインのターゲット列

3.インデックスはメンテナンスが必要

表のデータが更新されると、インデックスは必ず(自動的に)更新される。そのため、インデックスがあることにより、検索は早くなるが、更新処理(INSERT, UPDATE, DELETE)は遅くなる。

INSERT,UPDATE,DELETE時のインデックスのメンテナンス処理の概要について。
新しい行の追加 リーフブロックへ索引エントリを追加する
行の削除 リーフブロックから索引エントリを削除する
索引列の列値の更新 リーフブロックから古い索引エントリを削除し、新しい列値に対応したリーフブロックに索引エントリを追加する
[インデックスをタクサン作ったときINSERT,UPDATE,DELETEは遅くなるか - kagamihogeのblog](http://d.hatena.ne.jp/kagamihoge/20130817/1376707339)

ORACLEの場合、UPDATEのときは、DELETE⇛INSERTになるためより時間が掛かる。

インデックスはどこに、どれだけ作成すべきか

インデックスをつけるべき箇所

  • 表のプライマリーキーは(ほぼ)必須 (例外として小さい表)
  • 読み込み中心のシステム(表)は、インデックスが多くても問題ない
  • WHERE句で検索やジョインによく使用される列
  • カーディナリティが高い列 (SYSCAT, COLUMNS表のCOLCARD列で確認)

インデックスを避けるべき箇所

  • 更新が多いシステム(表)はインデックスを控えめにする
  • カーディナリティが低い列(フラグ列など)

索引の種類

  1. Bツリー索引 カーディナリティが高い、定型のSQL文の場合に用いる。 ビットマップ索引のようにビットマップからROWIDに変換する必要がないのでダイレクトにデータにアクセスできる(データブロックへのアクセスは速い)。ただし、索引を論理演算(AND, OR)で結合することはできないので非定型のSQL文にはあまり向かない。

  2. ビットマップ索引 カーディナリティが低い場合に用いる。 非定型のSQL文に対しては索引を論理演算で結合できるので、複数索引の組合せを気にする必要がない(複数索引を持つ必要がない)。

Oracle Databaseの索引(インデックス)の種類 - Bツリー、ビットマップ、索引構成表、索引クラスタ(ハッシュ・クラスタ)

作成する列の候補

1.一意性の高い索引を

Bツリー索引はテーブルに対して最適な一つの索引を使う。(ビットマップ索引は複数の索引が論理演算されるので、複数の索引を利用できる)

そのため、複数の列を条件に指定している場合は、できるだけ全ての列で複数索引を作成すること。これにより、一意性が高くなり、アクセスするブロック数を少なくでき、早くアクセスできる。

2.使用頻度の高い列を先頭に

全ての列の組み合わせの索引を作成すると数が多くなるため、使用頻度の多くない組み合わせ、または性能要件が高くない条件は省く。

このとき、作成する索引の先頭には頻繁に使用する列を持ってきて、多くのSQL文で使われるようにする。 (索引は、大小比較でデータを絞り込むため、先頭の値が決まらないと絞り込めない。よって、索引の先頭の列は条件で使用されている必要がある)

3. 選択率の低い列を先頭に

選択率の低い列(ユニーク性の高い列)から指定する。

4. 非ユニーク索引にはキー圧縮

候補

頻繁にWHERE句の比較条件で使用されている選択率の低い列

比較条件の列に索引が作成されていると、索引範囲スキャン(INDEX RANGE SCAN) が行われる。 (一意索引で等価条件の場合はINDEX UNIQUE SCAN)

この場合、アクセスする範囲は少ないほうが性能が良いため、選択率が低くなる列が効果的である。

カーディナリティの高い列は選択率が低くなるが、範囲指定(<,>など)では選択率が高くなる場合がある。

頻繁に結合が行われている列

ネステッド・ループ結合では内部表(最初にアクセスしないテーブル)の結合列で索引を使用して結合するため、結合列に索引がないと効率のよいアクセスにならない。

結合する行数が少ない ⇛ 結合列に索引を作成してネステッド・ループ結合するほうが効率が良い。

頻繁にソートされている列

索引はソートして格納されていますので、ORDER BYのようなソート処理にも効果がある(索引範囲スキャンや全索引スキャンするだけでソート結果になる)。ただし、NULLが存在する列のBツリー索引では効果がない(使用されない)ので注意すること(これは、Bツリー索引ではキー値にNULLは含まれないため。ただし、複数列索引ではすべての列値がNULLの場合のみ含まれないようになる)。

頻繁に更新されていない列

索引が作成されている列が更新されると索引も更新する必要がありますので、頻繁に更新されるとオーバーヘッドが大きくなり、断片化も発生し易くなるため効果が低下する。その断片化を解消するための再構築の頻度も多くなる可能性があるので、作成しない方が良い(作成しなくても良いようにデータベースを設計すること)。ただし、検索の性能が更新よりも優先度が高い場合もあるため、その場合のみ作成するとすればよい。

結合の種類

1. ネステッド・ループ結合

表の一部分を結合する場合に有効な結合方法。内部表の結合条件列に索引が作成されていて、その索引を使用することでデータに効率的にアクセスできる場合に利用する。

結合を試みるレコード数がより少ない方を外部表、レコード数に大差がない場合、結合条件列の索引スキャンがより効率的な方を内部表とする。

2. ソート/マージ結合

表の大部分を結合する場合に有効な結合方法。

双方の表を結合条件列でソートし、結果をマージすることで対象レコードを抽出する。

3. ハッシュ結合

結合条件に等価条件が指定され、大量のレコード、あるいは表の大部分を結合する場合に有効な結合方法。

ソート/マージ結合に比べて、事前のソート処理が不要となるため効率的な結合方法。

Oracle SQLチューニング講座(3):SQLチューニングの必須知識を総ざらい(後編) (13) - @IT

SQLチューニングの必須知識を総ざらい(後編) - @IT

結合のアルゴリズムについては、以下の記事で説明する。 [SQL実践入門]結合のアルゴリズム Nested Loops, Hash, Sort Merge

用語説明

参考

津島博士のパフォーマンス講座は参考になった。Oracleの動きを押さえたい方は、一読することをおすすめする。

参考図書