[MYSQL] datetimeカラムのインデックスの使われ方をテストしてみた

インデックスを張ったのに使われてない！！

27万レコードくらいのテーブルで、2カラムに指定条件を入れるSQLで、当該カラムにはインデックスを張ったのに、なぜか重い。おかしいと思ってクエリを調べてみたところ、、、

EXPLAIN SELECT * FROM `table1` WHERE `status` = '1' AND `datetime` < '2017-10-03 14:00:00'

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  tasks   ALL     status_datetime     NULL    NULL    NULL    270174  Using where

インデックス使われてない。。なぜ。。。

MYSQLの仕様を見てみると、、、

調べてみたところ、どうやらフルテーブルスキャンと同じような条件の場合は、インデックスを使わずにフルテーブルスキャンを使うというMYSQLの仕様のようです。

インデックスが設定されたカラムと定数値を比較していて、MySQL が (インデックスツリーに基づいて) その定数がテーブルのきわめて大きい部分をカバーしており、テーブルスキャンが高速に行われると計算しました。
8.2.1.20 フルテーブルスキャンを回避する方法 | mysql.com

日本語がちょっとおかしいけど（多分翻訳時のミスかと）、

インデックスカラムと定数値を比較
範囲がテーブルの広範囲になる判定
フルテーブルのがいいので、ALL（フルテーブルスキャン）で処理する

という流れのようです。

上記のSQLの場合、間違いなく問題なのはdatetimeカラムのところ。

インデックスを使う時はLIKE検索はしてはいけないというのはわかっていたけど、datetimeで比較する場合は、テーブルの状況を頭に入れておかないと同じような状況になるようです。

実際にテストしてみる

では、具体的にどういう条件でインデックスが効いてくるのかテストしてみます。

比較でなく、日時指定した場合

まずは、日付を=で指定した場合。

EXPLAIN SELECT * FROM `table1` WHERE `status` = '1' AND `datetime` = '2017-10-03 14:00:00'

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  table1  ref     status_datetime     status_datetime     11  const,const     1   NULL

当たり前ですが、効いてますね。

比較範囲を少し広げてみる（対象範囲：1%未満）

EXPLAIN SELECT * FROM `table1` WHERE `status` = '1' AND `datetime` > '2017-10-02 14:00:00' AND `datetime` < '2017-10-03 14:00:00';

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  table1  range   status_datetime     status_datetime     6   NULL    1109    Using index condition; Using MRR

インデックスが効いてますね。1100/27万で対象は1%未満なので、当然効きますね。

比較範囲をさらに広げてみる（対象範囲：10%未満）

EXPLAIN SELECT * FROM `table1` WHERE `status` = '1' AND `datetime` > '2017-09-26 14:00:00' AND `datetime` < '2017-10-03 14:00:00';

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  table1  range   status_datetime     status_datetime     6   NULL    20390   Using index condition; Using MRR

インデックスが効いてますね。20,390/27万で対象は10%ほど。

比較範囲をもっと広げてみる（対象範囲：30%程度）

EXPLAIN SELECT * FROM `table1` WHERE `status` = '1' AND `datetime` > '2017-09-03 14:00:00' AND `datetime` < '2017-10-03 14:00:00';

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  table1  range   status_datetime     status_datetime     6   NULL    107020  Using index condition; Using MRR

まだ効いてますね。

ただし、この時点でテーブルの1/3くらいのレコードを参照しているので、次くらいから怪しいですね。

比較範囲をかなり広げてみる（対象範囲：40%程度）

EXPLAIN SELECT * FROM `table1` WHERE `status` = '1' AND `datetime` > '2017-08-03 14:00:00' AND `datetime` < '2017-10-03 14:00:00';

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  table1  ALL     status_datetime     NULL    NULL    NULL    270174  Using where

対象範囲が40%ほどになるとインデックスを使わなくなりました。

datetimeで検索するって、どんなサービスでもあるような状況だと思いますが、なるべく範囲を狭くして検索しないとスロークエリ化確定ということがよくわかりました。

開発段階は、数100件とかのレコードでテストしたりするのでなかなか気づきませんが、実際にサービスが本番に乗ると徐々に重くなってきたりするので、この辺りはしっかりと頭に入れて開発しなきゃダメだということですね。

新着記事