SIS Lab
>
Blog
>
索引設計についてのまとめ

索引設計についてのまとめ

更新日:2022.07.02 作成日:2013.10.02

#索引設計 #DB #Oracle

DBMSにおいて、索引（インデックス）が重要なことは認識している。しかし、次の点の理解が浅かったため、参考図書・参考URLを元に索引設計の心得をまとめておく。

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

インデックスの特性

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

事前に作成しておく必要がある
検索に必要な情報が含まれるインデックスを（RDBが）自動的に使用する
インデックスはメンテナンスが必要

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

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

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

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

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

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

INSERT、UPDATE、DELETE時のインデックスのメンテナンス処理の概要について。
新しい行の追加 リーフブロックへ索引エントリを追加する
行の削除 リーフブロックから索引エントリを削除する
索引列の列値の更新 リーフブロックから古い索引エントリを削除し、新しい列値に対応したリーフブロックに索引エントリを追加する
インデックスをタクサン作ったときINSERT,UPDATE,DELETEは遅くなるか - kagamihogeの日記

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

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

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

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

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

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

索引の種類

1. Bツリー索引

カーディナリティが高い、定型のSQL文の場合に用いる。

ビットマップ索引のようにビットマップからROWIDに変換する必要がないので、ダイレクトにデータへアクセスできる（データブロックへのアクセスは速い）。ただし、索引を論理演算（AND、OR）では結合できないので非定型のSQL文にはあまり向かない。

2. ビットマップ索引

カーディナリティが低い場合に用いる。

非定型のSQL文に対しては索引を論理演算で結合できるので、複数索引の組合せを気にする必要がない（複数索引を持つ必要がない）。

Oracle Databaseの索引（インデックス）の種類 - Bツリー、ビットマップ、索引構成表、索引クラスタ（ハッシュ・クラスタ） | Oracle オラクルエンジニア通信 - 技術資料、マニュアル、セミナー Blog

作成する列の候補

1. 一意性の高い索引を

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

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

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

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

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

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

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

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

候補

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

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

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

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

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

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

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

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

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

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

索引が作成されている列が更新されると索引も更新する必要があり、頻繁に更新されるとオーバーヘッドが大きくなり、断片化も発生し易くなる。その断片化を解消するための再構築の頻度も多くなる可能性があるので、作成しない方が良い（作成しなくても良いようにデータベースを設計すること）。

ただし、検索の性能が更新よりも優先度が高い場合もあるため、その場合のみ作成するとすればよい。

結合の種類

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

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

内部表の結合条件列に索引が作成されていて、その索引を使用することでデータに効率的にアクセスできる場合に利用する。結合を試みるレコード数がより少ない方を外部表、レコード数に大差がない場合、結合条件列の索引スキャンがより効率的な方を内部表とする。

2. ソート/マージ結合

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

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

3. ハッシュ結合

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

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

SQLチューニングの必須知識を総ざらい（後編） (1/3)：Oracle SQLチューニング講座（3） - ＠IT

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

用語説明

カーディナリティ

レコード中の一意な値の個数を指す。例えば、性別データ（男、女）のように値の種類が少ない場合には、カーディナリティが低いという。逆に社員番号のように、ほとんどの値が一意である場合は、カーディナリティが高いという。 SQLチューニングの必須知識を総ざらい（後編） (2/3)：Oracle SQLチューニング講座（3） - ＠IT