2011/08/03

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

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


主に「日付時刻型の列に対して検索をかけるときに WHERE句をどう書くのか」というテーマ。
BETWEENが強いよねって言う流れから前回は「毎日○○時台」指定で行を抽出する場合について、色々試したらBETWEENはダメだった。

ダメだったんだけど、黒メガネに定評のあることで有名な @slumbers99氏 がこのボロブログを見て、
「これひと月毎の SELECT を UNION したら 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万レコード

BETWEENでの検索を1月ごとに分けて、それをUNIONして集計するパターン

SQL
 SELECT SUM(CNT)
   FROM (
(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-30 09:00:00' AND '2005-01-30 09:59:59' 
     OR `access_time` BETWEEN '2005-01-31 09:00:00' AND '2005-01-31 09:59:59'
) UNION ALL (
 SELECT COUNT(*) AS CNT
   FROM `access`
  WHERE `access_time` BETWEEN '2005-02-01 09:00:00' AND '2005-02-01 09:59:59'
     OR `access_time` BETWEEN '2005-02-02 09:00:00' AND '2005-02-02 09:59:59'
  -- 中略
     OR `access_time` BETWEEN '2011-08-01 09:00:00' AND '2011-08-01 09:59:59'
     OR `access_time` BETWEEN '2011-08-02 09:00:00' AND '2011-08-02 09:59:59'
)
) AS UNION_TABLE;
EXPLAIN
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYALL80
ほとんど表示がないのは、これが一番外側のSQL集計しか分析してないからであって、実際には内側のSQLごとに以下のような結果になっている。
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEaccessrangeaccess_timeaccess_time831Using where; Using index

RESULT
CNT
925781
2.517543 Sec

PROFILE
UNIONするテーブルごとにPROFILEが出てしまうので省略


解説
前回の最良だった 3.002918 秒を上回るパフォーマンス。検索のパーティショニングのような効果が現れている。
また、前回スワップが発生していたのと同じ環境なのに今回それがないのは、メモリの使い方がよいのだろう。
今回は月単位で分けてみたが、分解する長さを変えればもっとよい結果がでるのかもしれない。



まとめ

思わぬところから「BETWEEN最強説」を復活させることになった。
こんな発想ができた @slumber99氏の脳みそに嫉妬。

表面的な分析しかできていないことにやきもきしながらも、結果の秒数が変わることで一喜一憂したりしてる自分がいるのでした。
やっぱデータペースは面白いなー、なんて。
(そしてブログを書くことで自分の抜けている知識も再認識したりすることに。)

今回の分析のために作ったPHPファイルをどっかに置こうかな...



0 件のコメント:

コメントを投稿