前に調べた記事の内容を見返しながら、再度「自分の中で押さえておきたいポイント」を整理してみた。
※せっかくまとめたけど、しばらくSQLServerは使わなそう...残念。
インデックスの種類
そもそもインデックスって？
テーブルやビューからの行取得を高速に行うためのもの。
SQLServerの場合は「B-Tree構造」で作成され、指定したキーで昇順に並び替えられたデータの範囲によって枝分かれしている。
クラスター化インデックス
リーフレベルに、「実際のデータそのもの」が格納されている。
クラスター化インデックスが作成されると、非クラスター化インデックスのリーフレベルには
「ポインタ(RID)」ではなく「クラスター化インデックスの値」が格納される。
そのため、実際のデータを探すことなく(RID Lookup)することなく、インデックスをSeekするのみで検索が可能になる。
また、クラスター化インデックスは「テーブルに1つのみ作成可能」で、デフォルトでは主キーの値で作成される。
非クラスター化インデックス
リーフレベルに「実際のデータへのポインタ(RID)」が格納されており、インデックスの走査後にポインタを元にデータを取得する。
この実データへのアクセスを「ページジャンプ」という。
また、クラスタ化インデックスがテーブルに作成されている場合は、「クラスター化インデックスのキー」が格納されているため、クラスター化インデックスからデータを取り出す処理となる(Key Lookup)。
複合インデックス(カバリングインデックス)
複数カラムをインデックスキーに指定したもの。
複合インデックスでもリーフノードからの「ページジャンプ」を行って、実データへのアクセスを行う。
しかし利用したいデータが「複合インデックスのキー」に含まれている場合、実データ領域へのアクセスをすることなくデータを取得することができる。
これを活かして、「取得するデータをインデックスに全て含めてしまおう」というのが「カバリングインデックス」。
ただしすべてのノードに複数カラムのデータを保持するため、インデックスのサイズが大きくなってしまう。
付加列インデックス
すべてのノードに複数カラムのデータを保持するのではなく、リーフノードにのみカラムを追加する(列を付加する)ことで、
ページジャンプを防ぎながらインデックスサイズを押さえることもできる。
「WHERE句に指定するカラムをキーに指定し、取得するカラムを付加しておく」といった使い方をする。
#データへのアクセス
Index Seek, Clusterd Index Seek
Bツリーを走査したあと、一致するデータを見つけるためにリーフノードを検索する。
Index Scan, Clusterd Index Scan
インデックスのツリー構造を使った走査を行わず、リーフノードを順番に検索する。
Index Seekよりも多くのページにアクセスすることになるため、非効率な検索方法といえる。
Key Lookup(Clusterd)
非クラスタ化インデックスのリーフノードに格納された「クラスター化インデックスのキー」を基に、クラスター化インデックスから1行を取り出す。
RID Lookup(Heap)
非クラスタ化インデックスのリーフノードに格納された「RID(行識別子)」を基に、実テーブルから1行を取り出す。
なぜIndex Scanになってしまうのか
WHERE句の左辺に関数を使用している
せっかく値が入っているのに、関数を使ってしまったらインデックスの値で探せなくなってしまう。
インデックスが張ってある列の一部しか条件指定していない、または順番が間違っている
インデックスは「指定したキーでデータが並んでいる」ため、そのキーの一部しか条件に指定しなかった場合、正しくインデックスをSeekせずにScanしてしまう。
パフォーマンス
Index ScanからIndex Seekに
↑に書いたような点を意識してSQLを見直す。
正しくインデックスが利用されるようなSQLにすることで、Index Seekにする。
Key LookupやRID Lookupを無くす
クエリのチューニングをするためには、「クエリによって行われる読み取り数を減らす」ことが重要。