データベースでSQLを扱う際に不可欠なインデックス設計についてまとめてみました。
個人的にSQL Serverを使うことが多いので、割とSQL Server寄りの話になっています。
インデックスとは
・インデックス（索引）は、データベースの性能を向上させる方法の一つ。
・DBの検索を高速に行うための索引のようなもの。
・データ件数が多いテーブルの検索を早くすることができる。
・インデックスの仕組みはBツリーが多い。(クラスタ化インデックスも非クラスタ化インデックスも)
インデックスがない場合
・実テーブルの先頭から全件検索する(Table Scan)
インデックスがある場合
・インデックスを使用して実テーブルの場所を特定してから実データにアクセスする。(Index Seek)
・テーブルのすべてのデータを検索せずに目的のデータを特定する事ができるため、SQLのパフォーマンス向上に役立つ
・インデックスを使っているけど、インデックスを結局全部読んでデータにアクセスしているのは、Index Scan。
クラスタ化インデックス
・テーブル内に1個まで。
・データが物理的に並び替えられる。
・ただし、使用するインデックスによってはヒープを利用してアクセスすることもある。
・主キーを作成すると暗黙でクラスタ化インデックスが作成される。
非クラスタ化インデックス
・テーブル内に複数作成できる。
・テーブルとインデックスのオブジェクトは別物で、クラスタ化インデックスの構成されていない実際のテーブルのことをヒープという。
・UNIQUE制約を設定すると暗黙で非クラスタ化インデックスが作成される。
・非クラスタ化インデックスは、たどって行った先にキーが入っている。
・クラスタ化インデックスの方が非クラスタ化インデックスより早い。（非クラスタ化インデックスはキー参照が発生するため。)
RID Lookup
・RIDとは行識別子
・RID Lookup
→行識別子を元にヒープへアクセスしたことを示している。つまり、クラスタ化インデックスで検索できていれば出ない。
インデックスが使用されていることを確認する方法
・推定実行プランを使用する。
・実行計画を見て確認する。
・SQL ServerならSSMSを使用するなど。
結局どうなってればいい？
・基本的な考え方
Scan→❌
RID Lookup→❌
Seek→⭕️
・しかし、データ量が多くない(数百件くらいなど)ところであればTable Scanでも別に構わないと思う。
なぜならB-treeよりもフルスキャンの方が早いデータの件数があるため。ただし、実際に測定して試すのが大事。
インデックス設定のポイント
・行数が多いテーブルに使用
→データ量が少ないテーブルに使用しても効果はあまりない。
・カーディナリティの高い列に使用する。
→カーディナリティとはデータの種類のこと。
→カーディナリティが低い例：男、女。
→カーディナリティが高い例：IDとか。
→ただし、複合インデックスのように複数の列がある場合は、
1つ1つの列のカーディナリティが低くても効果あり。
・データがほとんど絞り込めない条件ではインデックスの意味がない。
→例えばたくさんデータはあるが、ある値で絞り込もうとしてもテーブルのデータのほとんどが同じ値なので絞り込めないようなものなど。
→また、動的に変わる条件(入力パラメータをセットするなど)で値の選択率が変わるなどでインデックスを使うよりテーブルスキャンした方が早い場合などもありえる。
・Where句や結合条件に使用されている列にインデックスを設定する。
・更新が少ない列に使用する。
→データの挿入、更新、削除等の処理では索引のメンテナンス作業が発生し、速度の低下につながる。
・INDEXは、必要なものだけにする
→INDEXが多ければ多いほど、update、insert、delete文の性能にも影響してしまう。
→複数インデックスがあると、検索時に想定とは違うインデックスが使用される可能性がある。
(インデックスはオプティマイザが判定して使用する。)
インデックスが使用されないパターン
・インデックス列に演算を行っている
→例 colA * 2.2 > 200
→(置き換えの例)colA > 200/2.2
・索引列に関数を使用している
→関数適用前の値で検索木が形成されているので、検索木をたどることが出来なくなる。
・IS NULLを使っている
→DBMSの実装に依存(SQL Serverはインデックスを使用できる)
・否定形を使っている
・ORを使っている
→(置き換えの例)INを使用する。（INもDBの種類によっては×）
・後方一致、中間一致のLIKEを使用している（前方一致はOK）
→先頭文字が分からないと検索木をたどることが出来なくなるので使えない。
・暗黙の型変換を行っている
→文字列定義のcolAがあったとして。
❌WHERE colA = 10
⭕️WHERE colA= ‘10’
DBは内部で暗黙の型変換をするとインデックスを使用出来なくなる。
・全テーブルを検索するようなselectなど条件が指定されていないと非クラスタ化インデックスしかない時は、Table Scanになったり、クラスタ化インデックスがある時は、Index Scan(Clusterd Index Scan)になる(Table Scanと出してくれない)。
