2011/08/02

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

前々回「 [MySQL]日付時刻型の列を年月日指定で選択する
そして前回「 [MySQL]日付時刻型の列から「毎月○○日」指定で選択する
の続き。

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

前回までの「BETWEEN最強説」を裏付けられるかどうかに注目だ!






前提条件

ここは前回と同じ。
バージョン: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が貼ってあるのとないのとでは検証結果が大きく変わるので注意。

今回はこのレコードの中から「毎日09時台」の行の数を数えるSQLを試す。

結果の見かた

SQL
実行するSQL文
EXPLAIN
上記SQL文にEXPLAINをつけて実行した結果。
EXPLAINについては @nippondanji 氏のブログエントリ「 MySQLのEXPLAINを徹底解説!! 」 を参考に。
本文中では横幅が収まるよう、文字を小さくしている。
RESULT
実行結果。ここでは特に実行時間に注目。
PROFILE
参考までに。
PROFILEについては @nippondanji 氏のブログエントリ「プロファイリングで快適MySQLチューニング生活」 を参考に。
今回からここはデフォルトでは非表示にした。「表示」をクリックすると多分ぬるっと表示されるはず。
解説
評価など。
試したものの中からパフォーマンスの悪いもの順に紹介して、BETWEENは最後に紹介する。

1. 「REGEXP」を使うパターン

SQL
SELECT COUNT(*) AS CNT
  FROM `access`
 WHERE `access_time` REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2} 09:[0-9]{2}:[0-9]{2}$';
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE access index access_time 8 6191705 Using where; Using index

RESULT
CNT
925781
8.745326 Sec

PROFILE
表示
Status Duration
starting 0.000015
checking query cache for query 0.000014
Opening tables 0.000004
System lock 0.000001
Table lock 0.000019
init 0.000022
optimizing 0.000003
statistics 0.000004
preparing 0.000003
executing 0.000003
Sending data 8.745156
end 0.000005
query end 0.000003
freeing items 0.000064
storing result in query cache 0.000004
logging slow query 0.000001
cleaning up 0.000005


解説
遊びでやってみた。今では反省している。
これはMySQLで使える正規表現を利用した検索である。ちなみにマッチした部分を返すのではなく、マッチする行を返す。
9秒近い時間がかかってしまう。まぁ遊びなので。


2. 「REGEXP」を使うパターン その2

SQL
SELECT COUNT(*) AS CNT
  FROM `access`
 WHERE `access_time` REGEXP '.* 09.*';
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE access index access_time 8 6191705 Using where; Using index

RESULT
CNT
925781
5.573544 Sec

PROFILE
表示
Status Duration
starting 0.000015
checking query cache for query 0.000012
Opening tables 0.000004
System lock 0.000001
Table lock 0.000019
init 0.000009
optimizing 0.000003
statistics 0.000004
preparing 0.000003
executing 0.000002
Sending data 5.573394
end 0.000005
query end 0.000003
freeing items 0.000060
storing result in query cache 0.000005
logging slow query 0.000002
cleaning up 0.000003


解説
同じく正規表現の検索をよりシンプルにしてみた。
こちらの方がまだマシだったが、まだ5秒もかかっている。
シンプルな検索にREGEXPはもったいない。


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

SQL
SELECT COUNT(*) AS CNT
   FROM `access`
  WHERE DATE_FORMAT( `access_time`, '%k') = '9';
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE access index access_time 8 6191705 Using where; Using index

RESULT
CNT
925781
3.769677 Sec

PROFILE
表示
Status Duration
starting 0.000015
checking query cache for query 0.000017
Opening tables 0.000004
System lock 0.000002
Table lock 0.000019
init 0.000006
optimizing 0.000003
statistics 0.000004
preparing 0.000004
executing 0.000003
Sending data 3.769520
end 0.000005
query end 0.000002
freeing items 0.000062
storing result in query cache 0.000005
logging slow query 0.000001
cleaning up 0.000005


解説
LIKE検索の前にDATE_FORMATが来た。文字列が返る関数なので文字列と比較している。
時間はだいぶ改善されて3秒台後半になった。まだいける。


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

SQL
 SELECT COUNT(*) AS CNT
   FROM `access`
  WHERE `access_time` LIKE '% 09%';
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE access index access_time 8 6191705 Using where; Using index

RESULT
CNT
925781
3.592092 Sec

PROFILE
表示
Status Duration
starting 0.000014
checking query cache for query 0.000013
Opening tables 0.000004
System lock 0.000002
Table lock 0.000019
init 0.000006
storing result in query cache 0.000010
optimizing 0.000002
statistics 0.000004
preparing 0.000003
executing 0.000003
Sending data 3.591937
end 0.000005
query end 0.000003
freeing items 0.000061
logging slow query 0.000002
cleaning up 0.000004


解説
時間は少し短縮されて3秒台中盤になった。
DATE_FORMATより早いのは意外だが、レコード数が多いとそういうものなのかもしれない。
ちなみに「LIKE '%-%-% 09:%:%'」とかやると少しパフォーマンスが悪くなる。シンプルに書くのが一番よい。


5. 「HOUR()」を使うパターン

SQL
 SELECT COUNT(*) AS CNT
   FROM `access`
  WHERE HOUR( `access_time`) = 9;
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE access index access_time 8 6191705 Using where; Using index

RESULT
CNT
925781
3.002918 Sec

PROFILE
表示
Status Duration
starting 0.000014
checking query cache for query 0.000013
Opening tables 0.000004
System lock 0.000002
Table lock 0.000017
init 0.000005
optimizing 0.000003
statistics 0.000004
preparing 0.000003
executing 0.000003
Sending data 3.002773
end 0.000005
query end 0.000003
freeing items 0.000060
storing result in query cache 0.000005
logging slow query 0.000001
cleaning up 0.000003


解説
HOUR()は引数の日付時刻型から時刻だけを数字で返すMySQL独自関数である。
意外にもこれが3秒前後とパフォーマンスが悪くない。(他と比較すると)

だがしかし、我々にはまだBETWEENが残っている。


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

SQL
 SELECT COUNT(*) AS CNT
   FROM `access`
  WHERE `access_time` BETWEEN '2005-01-01 09:00:00' AND '2005-01-01 09:59:59'
     OR `access_time` BETWEEN '2005-01-02 09:00:00' AND '2005-01-02 09:59:59'
     OR `access_time` BETWEEN '2005-01-03 09:00:00' AND '2005-01-03 09:59:59'
     OR `access_time` BETWEEN '2005-01-04 09:00:00' AND '2005-01-04 09:59:59'
    -- 中略
     OR `access_time` BETWEEN '2011-07-28 09:00:00' AND '2011-07-28 09:59:59'
     OR `access_time` BETWEEN '2011-07-29 09:00:00' AND '2011-07-29 09:59:59'
     OR `access_time` BETWEEN '2011-07-30 09:00:00' AND '2011-07-30 09:59:59'
     OR `access_time` BETWEEN '2011-07-31 09:00:00' AND '2011-07-31 09: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 1097677 Using where; Using index

RESULT
CNT
925781
195.034381 Sec

PROFILE
表示
Status Duration
starting 0.000133
checking query cache for query 0.006543
Opening tables 0.000011
System lock 0.000002
Table lock 0.001042
init 0.001136
optimizing 0.000606
statistics 0.060034
preparing 0.001015
executing 0.000004
Sending data 194.963414
end 0.000011
query end 0.000003
freeing items 0.000380
storing result in query cache 0.000005
logging slow query 0.000002
logging slow query 0.000002
cleaning up 0.000038


解説
3分以上かかるという、驚きの結果だった。
年数分×365行のWHERE句になってしまい、評価するのに時間がかかるのだろうか。
それにしても他と比較してあまりに時間がかかっていて逆に驚く。



まとめ

とりあえず「BETWEEN最強説」はもろくも崩れ去った。時と場合によるのである。
また、「EXPLAINがrangeになってるからといってindexより早いとは限らない」というのも実証してしまった。

「一概にいえない」というのは困る場合が多いと思うが、ある程度の傾向は見て取れたのではなかろうか。
少なくとも条件式が短く済む場合は迷わず「BETWEEN」を使うのがパフォーマンスがよいだろう。

日付時刻型から日付だけ、時刻だけで集計するのはそれなりに重い処理になるので、そもそもそういう要件があるのであれば、データモデルの設計をしなおした方がよいかもしれない。
例えば、集計する項目を別カラムで持ってしまい、行挿入時にその値も作って入れる。冗長になるが集計時のパフォーマンスは圧倒的に改善されるはずだ。

ではまた。


.

1 件のコメント:

  1. あとから気づいたんですが、BETWEENのパターンはきっとメモリスワップが発生してるんですわ。
    極端なメモリ割り振りはしていないと思うんですがね。

    ちゃんとしたデータを出しなおす必要があるかもしれないです。

    返信削除