Java、PHP、C++、MySQL、Apache、Linux、UnixなどのTechを紹介

MySQLでDATETIME型のカラムを追加して、YYYY-MM-DD HH:MM:SSの形式でデータを保存することはよくあるケースだと 思う。DATETIME型のカラムをWHERE以降の条件句に指定して、年月日時でデータの絞込みを行い目的のデータを抽出 するという時に、より高速にデータを取得できないかと考えてみた。

前提条件

• テストはクエリを10回発行した平均値より比較する
• テストパターンとして、INDEX有り・無しの2パターンで比較を行う
• テストデータは100万レコード用意する
• INT型のデータの作成にはMySQLのUNIX_TIMESTAMP()関数を使う
• Query CacheはOFFにして行う

今回テストで使用したサーバのスペック

OS ：CentOS release 4.5 (Final)
DB ：MySQL5.0.77
CPU：Core 2 Duo 1.86GHz 
Mem：1GByte

今回テストで使用したテーブルスキーマ、データ構造

(DATETIME型のデータ構造　テーブル名：test_datetime)

mysql> desc test_datetime;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| datetime  | datetime | NO   | MUL | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
+----+---------------------+
| id | datetime            |
+----+---------------------+
|  1 | 2008-08-23 19:03:26 |
|  2 | 2008-08-23 19:03:26 |
|  3 | 2008-08-23 19:03:26 |
|  4 | 2008-08-23 19:03:26 |
|  5 | 2008-08-23 19:03:26 |
|  6 | 2008-08-23 19:03:26 |
|  7 | 2008-08-23 19:03:26 |
|  8 | 2008-08-23 19:03:26 |
|  9 | 2008-08-23 19:03:26 |
| 10 | 2008-08-23 19:03:26 | 
| ・ |         ・   　     |
| ・ |         ・   　     |
| ・ |         ・   　     |
+----+---------------------+

================================================

(INT型のデータ構造　テーブル名：test_unixtime)

mysql> desc test_unixtime;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| unixtime  | int(10)  | NO   |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
+----+------------+
| id | unixtime   |
+----+------------+
|  1 | 1219485806 |
|  2 | 1219485806 |
|  3 | 1219485806 |
|  4 | 1219485806 |
|  5 | 1219485806 |
|  6 | 1219485806 |
|  7 | 1219485806 |
|  8 | 1219485806 |
|  9 | 1219485806 |
| 10 | 1219485806 |
| ・ |     ・     |
| ・ |     ・     |
| ・ |     ・     |
+----+------------+

INDEX無しの状態でのベンチマークテスト

比較演算子を使ったクエリ

(DATETIME型の場合)

SELECT * FROM test_datetime WHERE datetime = '2008-08-23 19:10:11';

処理結果(2.99 sec)

(INT型の場合)

SELECT * FROM test_unixtime WHERE unixtime = 1219486211;

処理結果(0.74 sec)

圧倒的にINT型にしたクエリ条件のほうがデータ取得の処理スピードが4倍高速の結果になった。
(※年月日時を＝(イコール、等値)を使った検索条件句を使うケースは実際のシステム設計の中ではあまりないようなケースだと思う。実際によく使われるのはやはり、次に紹介したBETWEENを使った期間指定検索であろう)

BETWEENを使った期間指定を使ったクエリ

(DATETIME型の場合)

SELECT * FROM test_datetime WHERE datetime 
              BETWEEN '2008-08-23 19:00:00' AND '2008-08-23 19:05:00';

処理結果(5.20 sec)

(INT型の場合)

SELECT * FROM test_unixtime WHERE unixtime 
              BETWEEN 1219485600 AND 1219485900;

処理結果(1.64 sec)

BETWEENを使った期間指定クエリでもINT型の方がデータ取得の処理スピードが3倍高速の結果である

INDEX有りの状態でのベンチマークテスト

比較演算子を使ったクエリ

(DATETIME型の場合)

SELECT * FROM test_datetime WHERE datetime = '2008-08-23 19:10:11';

処理結果(2.99 sec)

(INT型の場合)

SELECT * FROM test_unixtime WHERE unixtime = 1219486211;

処理結果(0.02 sec)

INT型の方がデータ取得の処理スピードが150倍高速の圧倒的効果である。INT型はINDEXを最適に使い目的の結果を返してくれるためここまでのパフォーマンス結果がでたものと思われる。面白い副産物結果として、DATETIME型ではINDEX有り・無しかかわらず処理結果値が同じということで、DATETIME型はINDEXの恩恵を受ける事があまりできないのである (※ただしINDEX無しの結果内容の所でも記載したが＝(イコール、等値)を使った検索条件句を使うケースはあまりないかもしれない・・・)

期間指定を使ったクエリ

(DATETIME型の場合)

SELECT * FROM test_datetime WHERE datetime 
              BETWEEN '2008-08-23 19:00:00' AND '2008-08-23 19:05:00';

処理結果(5.12 sec)

(INT型の場合)

SELECT * FROM test_unixtime WHERE unixtime
              BETWEEN 1219485600 AND 1219485900;

処理結果(1.64 sec)

DATETIME型、INT型もINDEXを設定しても、期間検索というB-tree型のINDEXの恩恵の受けにくい検索条件句であるため、INDEXが無い場合と比較してそこまで処理スピードの向上には影響を及ぼさなかったが、ここでもINT型の方がデータ取得の処理スピードが3倍高速の結果にはなった

結論

DATETIME型で保存するのではなく、UNIX_TIMESTAMP関数を使いINT型にコンバートした形式で保存することにより、DATETIME型より3倍～4倍高速化が実現できた。その際に注意しなければならないのが、INT型の年月日時を指定したカラムを生成しても実際にクエリを生成する際に、'1219485900'(シングルクォート)して文字列(STRING型)として指定しないこと。うっかりこのように指定してしまうと、カラムがINT型であっても処理スピードが遅くなってしまう。ただし、このINT型で年月日時データを保存する方法にもデメリットがあって、UNIX_TIMESTAMPなので直感的に見て人間が理解しずらいのである。そのためサーバのコマンドラインよりMySQL Clientを使いダイレクトにアクセスしデータを取得する必要があるケース(exp. 人間の手による緊急的な集計オペレーション)では、YYYY-MM-DD HH:MM:SSの形式にコンバートする必要があるかと思う。その場合は人手間だが以下のようにFROM_UNIXTIME関数を使用して結果を出力してほしい

SELECT id, FROM_UNIXTIME(unixtime) FROM test_unixtime  
               WHERE unixtime BETWEEN 1219485600 AND 1219485900;