インデックスとSQL
パフォーマンスチューニングでまず思い浮かべられるインデックス
正しく使えば効果が大きいが、使い方を間違えると全く意味がなくなってしまう
インデックスが効かないパターンをご紹介します
インデックス列に演算を行っている
SELECT *
FROM SomeTable
WHERE column * 1.1 > 100;
インデックスを作成した列はSQLにおいて「裸」で用いるのが原則。
インデックスの中で保持されているデータはあくまで「column」に対してであって「column*1.1」ではない。
回避するためには
WHERE column > 100/1.1
とする
索引列に対してSQL関数を適用している
SELECT *
FROM SomeTable
WHERE SUBSTR(column, 1, 1) = 'word';
理由は同じで、インデックスの中に存在する値は「column」であって「SUBSTR(column, 1, 1)」の値ではないから。
IS NULL述語を使っている
SELECT *
FROM SomeTable
WHERE column IS NULL
B-treeインデックスは一般的にNULLについてはデータの値とはみなさず、保持していない。
なのでIS NULLやIN NOT NULL述語に対しては有効ではない。
否定系を使っている
SELECT *
FROM SomeTable
WHERE column <> 100;
否定系はインデックスを利用できない
ORを用いている
SELECT *
FROM SomeTable
WHERE column = 99 OR column = 100;
ORを用いた場合はインデックスが利用されない。
INで書き換えることで回避できる
WHERE column IN (99, 100);
後方一致、または中間一致のLIKE述語を用いている
SELECT *
FROM SomeTable
WHERE column
LIKE '%word';
や
SELECT *
FROM SomeTable
WHERE column
LIKE '%word%';
はインデックスが効かない
↓のように前方一致はOK。
SELECT *
FROM SomeTable
WHERE column
LIKE 'word%';
暗黙の型変換を行なっている
columnが文字列で定義されているとする
SELECT *
FROM SomeTable
WHERE column = 10;
は、データ型が異なるのでNG
↓ならOK
SELECT *
FROM SomeTable
WHERE column = '10'
SELECT *
FROM SomeTable
WHERE column = CAST(10, AS CHAR(2));
インデックスに関する注意事項
主キーおよび一意制約の列には作成不要
DBMSは主キー制約や一意制約を作成する際、内部的にはB-treeインデックスを作成している。
二重にインデックスを作成する必要はない。
インデックスは更新性能を劣化させる
インデックスはテーブルとは独立したオブジェクトとしてDBMS内部に保持されている
そのため、インデックスが作成されている対象の列値が変更されると、インデックス内に保持している値も変更する必要がある。
インデックスを作成すればするほど、当該テーブルに対する更新性能が劣化していく。
無駄なインデックスは作成しないようにする。
定期的なメンテナンスを行うことが望ましい
テーブルのデータが更新されていくt、長期的には構造が崩れて性能が劣化していく。
そのため運用において定期的なメンテナンスを行う、具体的にはインデックスの再構築を行うことが性能を維持するためには望ましい。
まとめ
インデックスが効かないパターンをご紹介しました。
ルールとマナーを守って正しくインデックスを貼りましょう。