2005年03月17日

SQL の書き方によってインデックスが利用されない

インデックスを張ってあるカラムを where 句で指定すれば、無条件でインデックスが利用できるわけではありません。

その例を挙げていきます。
以下、基本的に Oracle が対象です。

インデックスが利用されないケース1:カラムに対する計算や関数
誤: ...WHERE col * 12 > 500
正: ...WHERE col > 500 / 12

これはがっかりというか、もうちょっと気を回して最適化してくれよ、と思いますね。
どうしてもカラムに対する計算や関数が必要な場合は、Oracle 8i 以降ならファンクションインデックスが利用できます。


インデックスが利用されないケース2:条件が否定形
誤: ...WHERE col NOT in ( 4, 5, 6 )
正: ...WHERE col < 4 AND col > 6

Not や <>、!= を用いると、「否定形ならデータの大半がヒットするはず。ならインデックスなんかいちいち使わずにテーブルを上から全部調べていった方が手っ取り早い」と DBMS が判断する傾向があります。


インデックスが利用されないケース3:Null を指定
誤: ...WHERE col IS NULL

通常のインデックス(B*Tree、バランスツリー)では、そもそも Null はインデックスに含められません。
ビットマップインデックスならば Null を含められますが、ビットマップインデックスはデータのバラつきの小さいカラム(例えば身長よりも性別)でなければ有用ではありません。
Null 条件の問題を SQL のコーディングレベルで回避する方法はありません。
DB の設計の段階で可能な限り Not Null 制約をつけるべきです。


インデックスが利用されないケース4:文字列の中間一致と後方一致
誤: ...WHERE col LIKE '%cle'
誤: ...WHERE col LIKE '%rac%'

B*Tree でできている以上、インデックスはソート済みになっています。
文字列が文字コード順に並べられていれば、「Ora から始まる」ものは各文字列の最初の部分(ソート済み)をチェックすればいいですが、「rac を含む」ものでは、結局全体をチェックせざるを得ません。丁度、辞書から調べたい単語を探す際に後方一致や中間一致では探せないのと同じ理屈です。
後方一致に関しては、後方一致検索用のインデックスもあります。


インデックスが利用されないケース5:複合インデックスの順序
col1, col2, col3 の順で複合インデックスが作られているとして、
誤: ...WHERE col2 = 'A'
誤: ...WHERE col3 = 'A'
誤: ...WHERE col2 = 'A' AND col3 = 'B'
正: ...WHERE col1 = 'A' AND col3 = 'B'

複数のカラムの組み合わせで作られる複合インデックスは、カラムの指定順序による制限を受けます。
最初に指定されたカラムが条件に含まれない場合、複合インデックスは利用されません。


インデックスが利用されないケース6:暗黙の型変換
col が文字列型のカラムだったとして
誤: ...WHERE col = 10
正: ...WHERE col = '10'

当たり前といえば当たり前なんですが、型を暗黙のうちに変換してくれる便利機能の副作用で、誤の例とした SQL もエラーにならず実行可能です。
実行可能なので気づきにくいですが、この場合もインデックスは利用されません。
posted by 市井賢児 at 2005年03月17日 02:11
| Comment(3) | TrackBack(0) | SQL


この記事へのコメント
毎日楽しみに拝見しております。

複合インデックスやアンチジョインは、特に認知度が低いように感じます。
例えば、数十万程度のデータを扱わせると、途端に差が出ます。

やはり大規模DBでの開発経験が無いと、意識が低いんでしょうかね。
Posted by 西口 at 2005年03月17日 22:45
ども、お久しぶりです。

幸いというか何と言うか、私の場合は 100 万件の insert が毎日発生する DWH を扱ったことがあります。

普通に SQL を勉強していると、あまり意識しないポイントだと私も思いますね。
大抵のゲンバでは適切なインデックスはもちろん、物理設計まで DB 設計がきちんと済んでいて、プログラマが考えなくてもいいようになっているはずですし、それで済んじゃいますから。

パフォーマンスを意識しなくて済むならそっちの方がハッピーだと思います。

……に、してもこのエントリ日本語が酷いですね。
いつかこっそり修正しよう。
(誤字脱字は結構頻繁に修正しています)
Posted by 市井賢児 at 2005年03月17日 23:51
おっ、SQLの最適化ネタですね。
毎日苦労してチューニングしてる私は
こういう話題にはとても敏感です。

うちの場合、数億件単位のテーブル同士をぶつけたり
することがよくあるので、インデックス走査や
結合方式(HASH/NESTED LOOP)などのチューニングは日常茶飯事です。
頭の悪いOracleくんに代わって、自分でヒント文を
切るのもデフォルトになってます。

実行計画とにらめっこしながらSQLをちまちま
いじってる自分は「SQL職人」なのではないか?
とタマに思ったりしますね。
Posted by itpro at 2005年03月18日 01:11
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:

この記事へのTrackBack URL

×

この広告は1年以上新しい記事の投稿がないブログに表示されております。