2011/07/26

[MySQL]日付時刻型の列から「年月日」指定で選択する

久々のMySQL関連エントリ。しかもまたもや日付型関連エントリ。

主に「日付時刻型の列に対して検索をかけるときに WHERE句をどう書くのか」というテーマ。
その中でも今回は年月日指定で行を抽出する場合について、実測値も交えてパフォーマンスを検証してゆく。





前提条件

バージョン:MySQL 5.1.48
テーブル定義:
CREATE TABLE IF NOT EXISTS `access` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `access_time` datetime NOT NULL COMMENT '閲覧日時',
  `page_name` varchar(200) NOT NULL COMMENT 'ページ名'
  PRIMARY KEY (`id`),
  KEY `access_time` (`access_time`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='アクセスログ';
検証時レコード数:約450万レコード
access_time にINDEXが貼ってあるのとないのとでは検証結果が大きく変わるので注意。
今回はこのレコードの中から「2009/01/01」の行の数を数えるSQLを試す。


結果の見かた

SQL
実行するSQL文
EXPLAIN
上記SQL文にEXPLAINをつけて実行した結果。
EXPLAINについては @nippondanji 氏のブログエントリ「 MySQLのEXPLAINを徹底解説!! 」 を参考に。
本文中では横幅が収まるよう、文字を小さくしている。
RESULT
実行結果。ここでは特に実行時間に注目。
PROFILE
参考までに。
PROFILEについては @nippondanji 氏のブログエントリ「プロファイリングで快適MySQLチューニング生活」 を参考に。
解説
評価など。



1. 「LIKE ~%」を使うパターン

SQL
SELECT COUNT(*) AS CNT
  FROM `access` 
 WHERE `access_time` LIKE '2009-01-01 %';
EXPLAIN
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows    | Extra                    |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | access | index | access_time   | access_time | 8       | NULL | 4672726 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
RESULT
+-----+
| CNT |
+-----+
|   2 |
+-----+
1 row in set (2.52 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000029 |
| checking query cache for query | 0.000053 |
| Opening tables                 | 0.000373 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000024 |
| init                           | 0.000019 |
| optimizing                     | 0.000006 |
| statistics                     | 0.000017 |
| preparing                      | 0.000007 |
| executing                      | 0.000003 |
| Sending data                   | 2.514543 |
| end                            | 0.000006 |
| query end                      | 0.000003 |
| freeing items                  | 0.000058 |
| storing result in query cache  | 0.000004 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000005 |
+--------------------------------+----------+
解説
こんなSQLがエラーもなく実行できるのはMySQLだけなんではなかろうか。かなり強引である。
これだけ見ても比較ができないのでわかりづらいが、2.5秒もかかるようではちょっと・・と思われただろう。
推測だが、datetimeの列に対して文字列比較を行うため、access_timeの列をいちいち値変換(型変換)しているように思う。
WHERE句の左辺で値変換(型変換)が起こるとクエリの実行が致命的に遅くなる。
そしてINDEXはといえば EXPLAINのtypeがindex(フルインデックススキャン)になってしまっている。



2. 列を日付型にCASTするパターン

SQL
SELECT COUNT(*) AS CNT
  FROM `access` 
 WHERE CAST(`access_time` AS date) = '2009-01-01';
EXPLAIN
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows    | Extra                    |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | access | index | NULL          | access_time | 8       | NULL | 4603215 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
RESULT
+-----+
| CNT |
+-----+
|   2 |
+-----+
1 row in set (2.36 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000029 |
| checking query cache for query | 0.000094 |
| Opening tables                 | 0.000011 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000097 |
| init                           | 0.000023 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000012 |
| preparing                      | 0.000008 |
| executing                      | 0.000003 |
| Sending data                   | 2.357085 |
| end                            | 0.000005 |
| query end                      | 0.000003 |
| freeing items                  | 0.000056 |
| storing result in query cache  | 0.000004 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
解説
LIKEを使わなくて済むよう、日付要素を切り取って比較してみたが、結果は2.3秒と実用には耐えない。
先ほど同様、いやむしろ明確に「左辺を値変換」しているのでそのせいだろう。
こちらも EXPLAINのtypeがindex(フルインデックススキャン)になってしまっている。




3. BETWEENを使うパターン

SQL
SELECT COUNT(*) AS CNT
  FROM `access` 
 WHERE `access_time` BETWEEN '2009-01-01 00:00:00' AND '2009-01-01 23:59:59';
EXPLAIN
+----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | access | range | access_time   | access_time | 8       | NULL | 2    | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+
RESULT
+-----+
| CNT |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000029 |
| checking query cache for query | 0.000052 |
| Opening tables                 | 0.000400 |
| System lock                    | 0.000002 |
| Table lock                     | 0.000023 |
| init                           | 0.000017 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000069 |
| preparing                      | 0.000007 |
| executing                      | 0.000003 |
| Sending data                   | 0.000044 |
| end                            | 0.000002 |
| query end                      | 0.000001 |
| freeing items                  | 0.000029 |
| storing result in query cache  | 0.000003 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000002 |
+--------------------------------+----------+
解説
嘘のように圧倒的に早くなった。実際にかかっているのは0.0006秒ほどである。
EXPLAIN でも type が range になっており、INDEXが有効に利用されているのがわかる。
なお、datetimeの列に対するBETWEENの時刻指定は ANDの左は 00:00:00、右は 23:59:59でよい。
datetimeのデータ型では1秒より細かい精度は保持していないからである。




4. >= <= 演算子を使うパターン

SQL
SELECT COUNT(*) AS CNT
  FROM `access` 
 WHERE `access_time` >= '2009-01-01 00:00:00'
   AND `access_time` <= '2009-01-01 23:59:59';
EXPLAIN
+----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | access | range | access_time   | access_time | 8       | NULL | 2    | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+
RESULT
+-----+
| CNT |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000030 |
| checking query cache for query | 0.000056 |
| Opening tables                 | 0.000012 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000038 |
| init                           | 0.000023 |
| optimizing                     | 0.000010 |
| statistics                     | 0.000076 |
| preparing                      | 0.000009 |
| executing                      | 0.000003 |
| Sending data                   | 0.000040 |
| end                            | 0.000002 |
| query end                      | 0.000002 |
| freeing items                  | 0.000028 |
| storing result in query cache  | 0.000016 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
解説
BETWEENの式を二つの式に分解したものである。
こちらも時間はほとんどかからない。
INDEXもうまく使われていて全く問題がない。ように見える。

結論

INDEXを貼ったdatetime列に年月日指定で行を抽出するなら「BETWEEN」で。 「>= AND <=」のパターンもパフォーマンスがよかったように見えるが、条件を増やしてゆくとBETWEENのほうが有利だと思っている。その根拠はまた次回。

次回のネタ

毎月25日の総アクセス数を調べたいときのSQL文はどう書くか。
もちろん 「LIKE %-%-25 %」って書けば楽だけど・・・・


.

0 件のコメント:

コメントを投稿