2011/07/29

[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='アクセスログ';
検証時レコード数:前回より少し増えて約650万レコード
access_time にINDEXが貼ってあるのとないのとでは検証結果が大きく変わるので注意。

今回はこのレコードの中から「毎月25日」の行の数を数える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 '%-%-25 %';
EXPLAIN
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows    | Extra                    |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | access | index | NULL          | access_time | 8       | NULL | 6212994 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+

RESULT
+--------+
| CNT    |
+--------+
| 223726 |
+--------+
1 row in set, 1 warning (3.75 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000030 |
| checking query cache for query | 0.000073 |
| Opening tables                 | 0.000017 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000069 |
| init                           | 0.000019 |
| storing result in query cache  | 0.000049 |
| optimizing                     | 0.000006 |
| statistics                     | 0.000010 |
| preparing                      | 0.000007 |
| executing                      | 0.000013 |
| Sending data                   | 3.757297 |
| end                            | 0.000007 |
| query end                      | 0.000003 |
| freeing items                  | 0.000129 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000023 |
+--------------------------------+----------+
解説
こんなSQLがエラーもなく実行できるのはMySQLだけなんではなかろうか。かなり強引である。
これだけ見ても比較ができないのでわかりづらいが、3.75秒もかかるようではちょっと・・と思われただろう。
推測だが、datetimeの列に対して文字列比較を行うため、access_timeの列をいちいち値変換(型変換)しているように思う。
WHERE句の左辺で値変換(型変換)が起こるとクエリの実行が致命的に遅くなる。
そしてINDEXはといえば EXPLAINのtypeがindex(フルインデックススキャン)になってしまっている。
(以上大部分前回のコピペ)

2-1. 「DATE_FORMAT()」を使うパターン

SQL
SELECT COUNT(*) AS CNT
  FROM `access`
 WHERE DATE_FORMAT(`access_time`, '%e') = '25';
EXPLAIN
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows    | Extra                    |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | access | index | NULL          | access_time | 8       | NULL | 6212994 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
RESULT
+--------+
| CNT    |
+--------+
| 223726 |
+--------+
1 row in set (3.78 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000029 |
| checking query cache for query | 0.000069 |
| Opening tables                 | 0.000354 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000024 |
| init                           | 0.000019 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000009 |
| preparing                      | 0.000015 |
| executing                      | 0.000003 |
| Sending data                   | 3.794425 |
| end                            | 0.000005 |
| query end                      | 0.000002 |
| freeing items                  | 0.000058 |
| storing result in query cache  | 0.000005 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000015 |
+--------------------------------+----------+
解説
DATE_FORMAT(?, %e) は日付部分を添え字なしで文字列として取得する関数である。
EXPLAINのtypeはやはりindex。左辺の型変換が起こっているであろうことも想像に難くない。


2-2. 「DATE_FORMAT()」を使うパターン その2

SQL
SELECT COUNT(*) AS CNT
  FROM `access`
 WHERE DATE_FORMAT(`access_time`, '%e') = 25;
EXPLAIN
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows    | Extra                    |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | access | index | NULL          | access_time | 8       | NULL | 6212994 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
RESULT
+--------+
| CNT    |
+--------+
| 223726 |
+--------+
1 row in set (4.19 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000032 |
| checking query cache for query | 0.000072 |
| Opening tables                 | 0.000016 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000036 |
| init                           | 0.000020 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000012 |
| preparing                      | 0.000016 |
| executing                      | 0.000003 |
| Sending data                   | 4.194004 |
| end                            | 0.000005 |
| query end                      | 0.000003 |
| freeing items                  | 0.000057 |
| storing result in query cache  | 0.000005 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000012 |
+--------------------------------+----------+
解説
同じSQLなのにパフォーマンスに差が?
いや、説明をすっ飛ばしてしまって申し訳ないが、今回は右辺を文字列ではなく数値にしてみた。更にパフォーマンスが悪くなったのはDATE_FORMAT(?, %e) は文字列として取得する関数であるため、再度型変換が発生しているのではなかろうか。



3. 「DAY()」を使うパターン

SQL
SELECT COUNT(*) AS CNT
  FROM `access`
 WHERE DAY(`access_time`) = 25;
EXPLAIN
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows    | Extra                    |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | access | index | NULL          | access_time | 8       | NULL | 6212994 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
RESULT
+--------+
| CNT    |
+--------+
| 223726 |
+--------+
1 row in set (2.95 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000030 |
| checking query cache for query | 0.000055 |
| Opening tables                 | 0.000011 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000035 |
| init                           | 0.000018 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000011 |
| preparing                      | 0.000008 |
| executing                      | 0.000003 |
| Sending data                   | 2.948833 |
| end                            | 0.000006 |
| query end                      | 0.000003 |
| freeing items                  | 0.000057 |
| storing result in query cache  | 0.000004 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000002 |
+--------------------------------+----------+
解説
DATE_FORMATよりはマシになった。が、MySQLは出来る子。まだがんばれるはず。
ちなみに同様な関数で「DAYOFMONTH()」というのがあるが、結果は「DAY()」とほぼ同じだった。


4. 「EXTRACT()」を使うパターン

SQL
SELECT COUNT(*) AS CNT
  FROM `access`
 WHERE EXTRACT(day FROM `access_time`) = 25;
EXPLAIN
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows    | Extra                    |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | access | index | NULL          | access_time | 8       | NULL | 6212994 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+---------+--------------------------+
RESULT
+--------+
| CNT    |
+--------+
| 223726 |
+--------+
1 row in set (2.99 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000029 |
| checking query cache for query | 0.000055 |
| Opening tables                 | 0.000013 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000034 |
| init                           | 0.000018 |
| optimizing                     | 0.000009 |
| statistics                     | 0.000010 |
| preparing                      | 0.000007 |
| executing                      | 0.000003 |
| Sending data                   | 2.980023 |
| end                            | 0.000005 |
| query end                      | 0.000003 |
| freeing items                  | 0.000059 |
| storing result in query cache  | 0.000004 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
解説
やはり変わらない。まぁ内部的には同じことだろうから仕方ない。
ちなみにEXTRACT(? FROM ?)はDAY()などと違い、SQL標準の関数なので使うなら「どちらかと言えば」こちらがお勧め。

5. 「BETWEEN」を使うパターン

SQL
SELECT COUNT(*) AS CNT
   FROM `access` 
  WHERE `access_time` BETWEEN '2005-01-25 00:00:00' AND '2005-01-25 23:59:59'
     OR `access_time` BETWEEN '2005-02-25 00:00:00' AND '2005-02-25 23:59:59'
     OR `access_time` BETWEEN '2005-03-25 00:00:00' AND '2005-03-25 23:59:59'
     OR `access_time` BETWEEN '2005-04-25 00:00:00' AND '2005-04-25 23:59:59'
   -- 中略
     OR `access_time` BETWEEN '2011-04-25 00:00:00' AND '2011-04-25 23:59:59'
     OR `access_time` BETWEEN '2011-05-25 00:00:00' AND '2011-05-25 23:59:59'
     OR `access_time` BETWEEN '2011-06-25 00:00:00' AND '2011-06-25 23:59:59'
     OR `access_time` BETWEEN '2011-07-25 00:00:00' AND '2011-07-25 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 | 499934 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+--------+--------------------------+
RESULT
+--------+
| CNT    |
+--------+
| 223726 |
+--------+
1 row in set (1.72 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000034 |
| checking query cache for query | 0.000344 |
| Opening tables                 | 0.000094 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000104 |
| init                           | 0.000053 |
| optimizing                     | 0.000026 |
| statistics                     | 0.002157 |
| preparing                      | 0.000026 |
| executing                      | 0.000003 |
| Sending data                   | 1.712924 |
| end                            | 0.000006 |
| query end                      | 0.000004 |
| freeing items                  | 0.000064 |
| storing result in query cache  | 0.000004 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000074 |
+--------------------------------+----------+
解説
前回の本命BETWEEN。やはり今回も本命だった。EXPLAINのtypeもしっかりrangeになっている。
だがしかし1秒かかってしまっているのはスペック的限界なのかメモリ量なのか。
またMySQL5.5ならもっとよい数字になるのか。いずれ試したいところ。
ちょっとSQL文を書き出すのが面倒だけど、一回書き出す仕組みを作っておけば大丈夫だろう。

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






SQL
SELECT COUNT(*) AS CNT
   FROM `access` 
  WHERE `access_time` >= '2005-02-25 00:00:00' AND `access_time` <= '2005-02-25 23:59:59'
     OR `access_time` >= '2005-03-25 00:00:00' AND `access_time` <= '2005-03-25 23:59:59'
     OR `access_time` >= '2005-04-25 00:00:00' AND `access_time` <= '2005-04-25 23:59:59'
     OR `access_time` >= '2005-05-25 00:00:00' AND `access_time` <= '2005-05-25 23:59:59'
    -- 中略
     OR `access_time` >= '2011-04-25 00:00:00' AND `access_time` <= '2011-04-25 23:59:59'
     OR `access_time` >= '2011-05-25 00:00:00' AND `access_time` <= '2011-05-25 23:59:59'
     OR `access_time` >= '2011-06-25 00:00:00' AND `access_time` <= '2011-06-25 23:59:59'
     OR `access_time` >= '2011-07-25 00:00:00' AND `access_time` <= '2011-07-25 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 | 499938 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+--------+--------------------------+
RESULT
+--------+
| CNT    |
+--------+
| 223726 |
+--------+
1 row in set (1.97 sec)
PROFILE
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000052 |
| checking query cache for query | 0.000439 |
| Opening tables                 | 0.000016 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000115 |
| init                           | 0.000144 |
| optimizing                     | 0.000068 |
| statistics                     | 0.002149 |
| preparing                      | 0.000085 |
| executing                      | 0.000003 |
| Sending data                   | 1.964780 |
| end                            | 0.000006 |
| query end                      | 0.000003 |
| freeing items                  | 0.000078 |
| storing result in query cache  | 0.000004 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000096 |
+--------------------------------+----------+
解説
前回前フリした箇所である。
EXPLAINのtypeはしっかりrangeになっているが、BETWEENと比較して2割ほどパフォーマンスが落ちている。
勝手な推測だが、BETWEENと違って、>=,<=それぞれを別に評価するためなのではなかろうか。
こんな風にたくさん使う場合はBETWEENの方がパフォーマンスがいいのかもしれない。

結論

1.EXCELは優秀なSQLビルダー
2.日付時刻の値をもとに選択する場合はBETWEEN最強 ってことでいい?

次回

まだ続く。本当にBETWEEN最強なのか?
今度は「毎日○時台」の行集計にチャレンジ。


.

1 件のコメント:

  1. DATE_FORMATで文字列比較を当たり前にやってたけど、BETWEEN強すぎ!!!

    なるほどこれは参考になります。

    返信削除