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

MYSQLで、datetimeカラムの検索をする際に、インデックスが使われていないことがあったので、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

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

  1. インデックスカラムと定数値を比較
  2. 範囲がテーブルの広範囲になる判定
  3. フルテーブルのがいいので、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件とかのレコードでテストしたりするのでなかなか気づきませんが、実際にサービスが本番に乗ると徐々に重くなってきたりするので、この辺りはしっかりと頭に入れて開発しなきゃダメだということですね。