［SQLチューニング］お勉強⑦～ここに注意「インデックス編」～

ついに実践「インデックス編」

SQLチューニングの目的は「実行速度の向上」。
それはだいたいにおいて「アクセスブロック数の低減」。
※「ディスクブロック」よりも「メモリブロック」にアクセスした方がパフォーマンスがよいが、今回はスナップショットはとらないので考えないこととする。

（１）抽出レコード数が多く、フルテーブルスキャンになっている。

◆「フルテーブルスキャンが一番効率がいい可能性あり」

抽出レコード数が多い場合は、シングルブロックアクセスになるインデックススキャンよりもマルチブロックアクセスを行うフルテーブルスキャンの方が効率がいい。
※インデックスはキー値を追うので、まとめ取り（マルチブロックアクセス）が出来ないと考えればいいか？

実際に何割？というのはブロックサイズやレコード長次第のようなので、都度判断が必要。

ただし、抽出列をすべてインデックスに含めることが可能であれば、全索引スキャンの検討も可能。
似たものに高速全索引スキャンもある。

スキャン方法	全索引スキャン	高速全索引スキャン
アクセス方法	シングルブロックアクセス	マルチブロックアクセス
順番	保証されている	保証されていない。ORDER BY句が指定されている場合はデータ取得後にソートが実行される。
パラレルスキャン	不可能	可能
制約	－	指定した列のうち少なくとも1つはNOT NULL制約が必要

◆「列のカーディナリティが高ければインデックス張りたい」

カーディナリティが低い場合はかえって遅くなったり（インデックス→テーブルの2度手間、マルチブロックアクセスできないetc.）、パフォーマンスを意識しなくていい機能だったり、頻度が低い機能の場合はインデックス更新のコストがネックになったりすることもある。

※カーディナリティは判断が若干複雑で、

◆「NULL値ではインデックスが作れない。が、手はある」

ビットマップインデックスを使う。
OLTP系の場合は基本使用不可。
複合インデックスの第２キー以降にNULL値が含まれる列を指定する。
第１キーがNULLでなければインデックスは作られるので、インデックスによる検索が可能になる。
ファンクションインデックスを利用し、NULL変換した結果をインデックスにする。
使用ルールを明確にしてチーム内で共有しておかないとぼろぼろになりそう。

◆「インデックス列はそのまま使おう」

うっかり系と知らなかった系かな？

うっかり系	暗黙型変換。「インデックス列（文字列）＝1」（シングルクォーテーション付け忘れ）とかで、インデックス列を型変換しちゃってる場合。
知らなかった系	「インデックス列 \|\| 文字列」、「SUBSTR（インデックス列, 1, 2)」とか。

うっかり系は直せばいいとして、知らなかった系は

◆「前方一致ならインデックス使えるんだけど」

とはいえ、使いたいときはあるよね。

◆「別の条件に書き換えられないかな」

書き換えられない場合は、

◆「複合インデックスの方がいいんじゃない？」

このインデックスの状態のままインデックスを使うとなると、インデックス・マージということになるが、

※2.のほうがパフォーマンスがいいことが多い気がする。
（抽出条件１でかなり絞られるのなら2.の方がいいし、絞られないのならそもそもフルテーブルスキャンの方がいいのではないだろうか）

◆「インデックス列の一部しか使用しない場合は大して速くならない場合もある」
◆「データが絞り込まれやすい列から順番にインデックス列にした方が効果的」
◆「基本的に複合インデックスの第1キーに指定している列は別途インデックスを張る必要はない」

インデックス列すべて使う検索の場合はルート・ブロックから順番に走査するが、先頭キー列を使わず、途中のキー列のみを使用する場合は多くのリーフ・ブロックにアクセスする。（スキップスキャン）
例えば、３つの複合インデックスの場合、

○	「第1キー使用」、「第1キー、第2キー使用」、「第1キー、第2キー、第3キー使用」
×	「第2キー使用」、「第2キー、第3キー使用」、「第3キー使用」 ※すべてのリーフ・ブロックにアクセスすることになる
△	「第1キー、第3キー使用」 ※第1キーである程度リーフ・ブロックを絞り込める

※×と△がスキップスキャンになる。

◆「インデックス使うよ」

ビューやシノニムでも元になっているテーブルのインデックスが使用されるよ。

◆「関数が実行される行数に注意」

特にテーブル問合せなどの処理をユーザー定義関数にしている場合、実行される行数が多くなるとパフォーマンスの劣化が激しい。
実行計画の「Rows」が実行行数の目安。