はじめに
皆さんこんにちは、新卒2年目エンジニアの細川です。
今回は輪読会にて勉強したDBのインデックスが効かなくなるSQLについて紹介したいと思います。
今回参考にさせていただいた書籍
今回輪読会で読ませていただいた書籍は「達人に学ぶDB設計徹底指南書: 初級者で終わりたくないあなたへ」という書籍です。DBの良い設計や悪い設計について、それがなぜ良いのか悪いのかを例を踏まえて分かりやすく解説してくれている本です。
正規化手順などについても詳しく書かれており、DBに不安を感じる方はぜひご一読をお勧めします！
DBのインデックスについて
DBのインデックスと聞くと、何となく貼ると早くなるくらいの認識で使っている方もいらっしゃるのではないでしょうか？(僕はそうでした笑)
しかし、インデックスも万能ではなく、種類によって向き不向きもあり、正しい使い方をしないとうまく効果を発揮できません。
インデックスにはいくつか種類があり、DBMSによって使えるものも異なってくるのですが、最もポピュラーなものはB-Treeインデックスと呼ばれる、木構造のインデックスです。
B-treeインデックスの詳細な構造については今回は省略しますが、気になる方は上述した書籍やこちらの記事などが分かりやすいかと思います。
B-treeインデックスが効かない検索方法
それでは本題のB-treeインデックスが効かなくなるSQLや検索方法について書いていきます。
1. 検索などに利用されない列にインデックスを張る
まずは、非常に当たり前の話かとは思いますが、検索に利用されない列にインデックスを作成しない方が良いというものです。インデックスを作成することにもコストがかからないわけではなく、更新の際のパフォーマンスにも影響することから基本的に必要ないインデックスは作成しないようにした方が良いです。検索に利用されない列にインデックスを作成しても、もちろん検索に利用されないので、作成しないようにしましょう。
2. インデックスを張っている列に演算を行う
続いてインデックスを張っている列に、演算を行うとインデックスが検索に利用されなくなるというものです。以下に例を示します。この例ではTable1というテーブルのcolumn_1という列にインデックスを張っていると思ってください(以降の例でも同様の状況で考えます)。
SELECT * FROM Table1 WHERE column_1 * 1.1 > 100;この例ではcolumn_1 * 1.1が100よりも大きなレコードを検索しています。インデックスの中に保持されているデータは、あくまでcolumn_1のデータであり、column_1 * 1.1ではありません。そのため、このようなSQLで検索すると、インデックスが検索で利用されないようです。
このような検索を行いたい場合は以下のように書き換えましょう。
SELECT * FROM Table1 WHERE column_1 > 100 / 1.13. インデックスを張っている列に対してSQL関数を利用する
これも例を見てもらった方が早いかと思います。
SELCT * FROM Table1 WHERE SUBSTR(column_1, 1, 1)='a';これはcolumn_1の1文字目が”a”のレコードを探すSQLですが、これも2. の時と同様で、column_1の値を直接検索に利用しないとインデックスが検索に利用されません。
4. IS NULLを使う
SELECT * FROM Table1 WHERE column_1 IS NULL;これはcolumn_1の値がNULLのレコードを探すSQLです。
B-treeインデックスでは基本的にはNULLを値とみなしておらず、保持していません。そのため、IS NULLやIS NOT NULLに対してインデックスは有効ではありません。ただ、最近、DBMSによってはIS NULLなどで検索してもインデックスが利用されることも多いようです。
調べてみたところ、MySQL, SQL Server, PostgreSQLでは、IS NULLを使ってもインデックスが機能するようです。
5. 否定形を使う
SELECT * FROM Table1 WHERE column_1 <> 100;これはcolumn_1の値が100ではないレコードを探すSQLです。
これが機能しない原因としてはB-treeインデックスの構造が影響してきます。簡単に言うと、B-treeインデックスは作成するタイミングで、その列のすべてのレコードをソートして作成されます。
そのため、不等号での検索の場合はあるレコードよりも小さいもの、もしくは大きいものだけ見ればよいというように絞り込んで検索できます。また、等号での検索の場合も同様にあるレコードより小さいか大きいか、でどんどん検索範囲を絞ることができます。
ただ、否定形の場合はその値ではないということしか分からず結局ほぼすべてのデータを見ていく必要がありインデックスを張っていない場合とほとんど検索時間が変わらなくなってしまいます。
このあたりはB-treeインデックスの構造を知っていれば納得できる話かと思うので、興味のある方は調べてみてください。
6. ORを使う
ORを用いた場合も検索にインデックスが利用されなくなるようです。
SELECT * FROM Table1 WHERE column_1 = 99 OR column_1 = 100;これはINを使うように書き換えると良いようです。
SELECT * FROM Table1 WHERE column_1 IN (99, 100);ただ、これもDBMSの実装によるところもあるようで、ORを利用してもインデックスが効く場合も効かない場合もあるようです(参考)。それぞれのDBMSのドキュメントを読んだり検証したりする必要もあるかもしれませんが、一旦は「ORは要注意！」と覚えておくだけでもいいかと思います。
7. 後方一致、または中間一致のLIKEを使う
NG(後方一致)
SELECT * FROM Table1 WHERE column_1 LIKE '%a';NG(中間一致)
SELECT * FROM Table1 WHERE column_1 LIKE '%a%';OK(前方一致)
SELECT * FROM Table1 WHERE column_1 LIKE 'a%'LIKEを使う場合、前方一致以外の使い方w￥をすると検索の際にインデックスの効果があまり得られません。
これは先ほど否定形のところでも少し書いた通り、B-treeインデックスの構造が影響しています。B-treeインデックスを作成する際に全レコードをソートしているのですが、中間一致や後方一致の場合はそのソートが意味をなさなくなり、インデックスがあってもあまり効果がありません。
8. 暗黙の型変換を行う
例としてcolumn_1は文字列型であるとします。
NG
SELECT * FROM Table1 WHERE column_1 = 10;OK
SELECT * FROM Table1 WHERE column_1 = '10';OK
SELECT * FROM Table1 WHERE column_1 = CAST(10, AS CHAR(2));SQLを書く際に、明示的に型変換をしなくてもDBMSがよしなに型変換を行ってくれます。これは非常に便利なのですが、型が変わった場合、インデックスは検索に利用されなくなるようです。そのためインデックスを張った列では可能な限り、明示的に型を指定するようにしましょう。
9. そのほかの注意点
これ以外にもSQLの書き方というわけではないのですが、以下の注意点も覚えておくとよいと思います。
-
主キーや一意制約の列にはインデックスの作成は不要
DBMSは主キー制約や一意制約を作成するときに内部的にインデックスを作成しているため、わざわざ作成する必要は無いようです。
-
インデックスは更新性能を劣化させる
最初の項目でも書きましたが、インデックスを作成するということは、対象の列を更新する場合に、他に更新しなければならない場所が増えるということでもあります。そのため、更新性能は劣化していきます。極力ムダなインデックスは作成しないようにしましょう。
-
定期的にインデックスのメンテナンスを行う
インデックスはテーブルのデータが更新されていくにつれて徐々に構造が崩れていき、性能が落ちていきます。そのため定期的にインデックスの再構築を行うのが望ましいです。DBMSごとにインデックスの構造がどの程度崩れているかの基準になるパラメータと目安値などが存在するので、ドキュメントで調べてみることをお勧めします。ちなみにpostgreSQLではどの程度木構造が崩れているかの指標に断片化率を使うようです(参考)。
まとめ
SQLを書く際に下記のようなSQLを書くとインデックスの効果が得られないので注意しましょう！
- 検索に利用しない列にインデックスを張る
- インデックス列に対して演算を行う
- インデックス列に対してSQL関数を適用する
- IS NULLを使う(DBMSによる)
- 否定形を使う
- ORを使う(DBMSによる)
- 後方一致、または中間一致のLIKEを使う
- 暗黙の型変換を行っている
- その他の注意点
- 主キー列にインデックスは不要
- インデックスは更新性能を劣化させる
- 定期的にメンテナンスする
おわりに
今までなんとなく使っていたインデックスですが、少しは中身が分かったような気がします。今後は今まで以上に効率的にインデックスを使えるようにしていきたいです。上記の条件は検証したわけではないので、DBMSや検索の仕方によってインデックスが効く場合、効かない場合はまだまだありそうですが、一つの指標としてこれらは覚えておいてもいいと思います。少しでも参考になれば幸いです。余裕があれば、B-treeインデックスの構造についても自分なりにまとめてみたいと考えています。あと、インデックスを”はる”の漢字が”張る”なのか”貼る”なのか調べてみたところこちらの記事がヒットし、記事によると”張る”が正解なようなので、漢字の使い方にも気を付けようと思いました。
他にも様々な本で輪読会を行っていますので、気になる方はぜひこちらのページをチェックしてみてください。
