【MySQL】日付型のインデックスを検証してみての気付き。

なんか日付け型のカラムにインデックスをつけようと思ったんだけど、これってほんとに有効なの?って思ったので、改めて検証して見たんだよという話からの気付き。

皆これ当たり前の様に知っているのかなぁ。少なからず僕は改めて気づいたのでメモとして。

前提条件

MySQL5.6

以下のテーブルを検証用に用意します。検証するデータ型はTIMESTAMPとDATETIMEです。

CREATE TABLE test (c1 INT NOT NULL AUTO_INCREMENT, c2 TIMESTAMP, c3 DATETIME, PRIMARY KEY(c1), KEY (c2), KEY (c3)) ENGINE InnoDB;

INSERT INTO test (c2, c3) VALUES
('2014-01-01 00:00:00', '2014-01-01 00:00:00'),
('2014-01-01 04:00:00', '2014-01-01 04:00:00'),
('2014-01-01 08:00:00', '2014-01-01 08:00:00'),
('2014-01-01 12:00:00', '2014-01-01 12:00:00'),
('2014-01-01 16:00:00', '2014-01-01 16:00:00'),
('2014-01-01 20:00:00', '2014-01-01 20:00:00'),
('2014-01-02 00:00:00', '2014-01-02 00:00:00'),
('2014-01-02 04:00:00', '2014-01-02 04:00:00'),
('2014-01-02 08:00:00', '2014-01-02 08:00:00'),
('2014-01-02 12:00:00', '2014-01-02 12:00:00'),
('2014-01-02 16:00:00', '2014-01-02 16:00:00'),
('2014-01-02 20:00:00', '2014-01-02 20:00:00'),
('2014-01-03 00:00:00', '2014-01-03 00:00:00'),
('2014-01-03 04:00:00', '2014-01-03 04:00:00'),
('2014-01-03 08:00:00', '2014-01-03 08:00:00'),
('2014-01-03 12:00:00', '2014-01-03 12:00:00'),
('2014-01-03 16:00:00', '2014-01-03 16:00:00'),
('2014-01-03 20:00:00', '2014-01-03 20:00:00'),
('2014-01-04 00:00:00', '2014-01-04 00:00:00'),
('2014-01-04 04:00:00', '2014-01-04 04:00:00'),
('2014-01-04 08:00:00', '2014-01-04 08:00:00'),
('2014-01-04 12:00:00', '2014-01-04 12:00:00'),
('2014-01-04 16:00:00', '2014-01-04 16:00:00'),
('2014-01-04 20:00:00', '2014-01-04 20:00:00');

日付型はLIKE検索すな。

できるからと言ってLIKE検索してはいけません。

mysql> SELECT * FROM test WHERE c2 = '2014-01-01 08:00:00';
+----+---------------------+---------------------+
| c1 | c2                  | c3                  |
+----+---------------------+---------------------+
|  3 | 2014-01-01 08:00:00 | 2014-01-01 08:00:00 |
+----+---------------------+---------------------+
1 row in set (0.05 sec)

mysql> SELECT * FROM test WHERE c2 LIKE '2014-01-01 08:00:00';
+----+---------------------+---------------------+
| c1 | c2                  | c3                  |
+----+---------------------+---------------------+
|  3 | 2014-01-01 08:00:00 | 2014-01-01 08:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

上記のSQLは2つとも同じ結果になりますが、LIKE検索の場合はインデックスが使用されずフルテーブルスキャンが走ってしまいます。

mysql> EXPLAIN SELECT * FROM test WHERE c2 = '2014-01-01 08:00:00';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | ref  | c2            | c2   | 4       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c2 LIKE '2014-01-01 08:00:00';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | c2            | NULL | NULL    | NULL |   18 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

ということは、もちろん日付毎にデータを取得したい場合に下記の様なことしちゃダメです。

mysql> SELECT * FROM test WHERE c2 LIKE '2014-01-02%';
+----+---------------------+---------------------+
| c1 | c2                  | c3                  |
+----+---------------------+---------------------+
|  7 | 2014-01-02 00:00:00 | 2014-01-02 00:00:00 |
|  8 | 2014-01-02 04:00:00 | 2014-01-02 04:00:00 |
|  9 | 2014-01-02 08:00:00 | 2014-01-02 08:00:00 |
| 10 | 2014-01-02 12:00:00 | 2014-01-02 12:00:00 |
| 11 | 2014-01-02 16:00:00 | 2014-01-02 16:00:00 |
| 12 | 2014-01-02 20:00:00 | 2014-01-02 20:00:00 |
+----+---------------------+---------------------+
6 rows in set (0.00 sec)

日付毎に取得したい場合は以下の様にしましょう。

mysql> SELECT * FROM test WHERE c2 >= '2014-01-02' AND c2 < '2014-01-03';
+----+---------------------+---------------------+
| c1 | c2                  | c3                  |
+----+---------------------+---------------------+
|  7 | 2014-01-02 00:00:00 | 2014-01-02 00:00:00 |
|  8 | 2014-01-02 04:00:00 | 2014-01-02 04:00:00 |
|  9 | 2014-01-02 08:00:00 | 2014-01-02 08:00:00 |
| 10 | 2014-01-02 12:00:00 | 2014-01-02 12:00:00 |
| 11 | 2014-01-02 16:00:00 | 2014-01-02 16:00:00 |
| 12 | 2014-01-02 20:00:00 | 2014-01-02 20:00:00 |
+----+---------------------+---------------------+
6 rows in set (0.00 sec)

ちなみにDATETIME型も同じでした。

あんまり広範囲で検索すな

年間のデータから特定の日付のみを取得するようなものなら大丈夫かもしれませんが、例えば10月以降とか、1〜4月とかの場合には注意が必要になってくるかもしれません。

TIMESTAMP

mysql> EXPLAIN SELECT * FROM test WHERE c2 < '2014-01-01 00:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c2            | c2   | 4       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c2 < '2014-01-01 04:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c2            | c2   | 4       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c2 < '2014-01-01 08:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c2            | c2   | 4       | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c2 < '2014-01-01 12:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c2            | c2   | 4       | NULL |    3 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c2 < '2014-01-01 16:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c2            | c2   | 4       | NULL |    4 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c2 < '2014-01-01 20:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c2            | c2   | 4       | NULL |    5 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c2 < '2014-01-02 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | c2            | NULL | NULL    | NULL |   24 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

DATETIME

mysql> EXPLAIN SELECT * FROM test WHERE c3 < '2014-01-01 00:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c3            | c3   | 6       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c3 < '2014-01-01 04:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c3            | c3   | 6       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c3 < '2014-01-01 08:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c3            | c3   | 6       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c3 < '2014-01-01 12:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c3            | c3   | 6       | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c3 < '2014-01-01 16:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c3            | c3   | 6       | NULL |    3 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c3 < '2014-01-01 20:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c3            | c3   | 6       | NULL |    4 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c3 < '2014-01-02 00:00:00';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c3            | c3   | 6       | NULL |    5 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c3 < '2014-01-02 04:00:00';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | c3            | NULL | NULL    | NULL |   24 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

TIMESTAMPとDATETIMEで若干違いがありますが、だいたい1/4以上の結果を取得するクエリで、フルテーブルスキャンになってしまいます。

しかしLIMIT句をつけるとインデックスが使用されているという不思議。

mysql> EXPLAIN SELECT * FROM test WHERE c2 < '2014-01-02 04:00:00' LIMIT 23;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c2            | c2   | 4       | NULL |    7 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test WHERE c3 < '2014-01-02 04:00:00' LIMIT 23;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | c3            | c3   | 6       | NULL |    6 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

日付型で範囲指定するときには、どれくらいの結果が帰ってくるかわからないので、LIMIT句で上限を設定するのが吉。

2015年3月4日追記: 日付型ではなくてもSQLの検索結果が多くなるとフルテーブルスキャンになるのは当たり前の話だったようで。。。

まとめ

まあ、他のキーと一緒に検索するのであれば細かいことは言いっこなしです。日付型のみで検索する場合は要注意。

なぜそんな動きになってしまったのか深く掘り下げられませんが、知っておくことは転ばぬ先の杖となり、今後のMySQLライフに何かしらの役に立つことでしょう。

たぶんね。

コメントを残す