MySQL5.5.3-m3のDATETIME型のバグ。あとMySQLのDATETIME型は本当に遅いのか検証してみた

バグの話

近々ふぁぼったーDBのInnoDB化を企てているので、それに伴いMySQL5.0.67(Tritonn)から、先日リリースされたばかりのMySQL5.5.3-m3に乗り換えてみた。RC(リリース候補)版ということで、GA版とほぼ変わらない品質と聞いたので、割と軽い気持ちでインストールしたんだけど、いきなりバグにハマった。
バグとは、DATETIME, TIMESTAMP, DATE, TIME型と文字列定数との結合でインデックスが使われない、というもの。
以下のような、date(DATE型)の結合しかしていないクエリでも、dateインデックスが使われず昇順フルテーブルスキャンされ、20秒くらい掛かった。

select date from STATUS force index(date) where date='2010-01-19' limit 10;

この現象は、5.5.3,5.5.4での現象としてバグ報告がなされ、すでにパッチ待ちになっていた。
MySQL Bugs: #52849: datetime index not work
MySQL Bugs: #53149: MySQL doesn't use indexes on date column properly because of collation
よって5.5.5がリリースされれば解消されているのだけど、バグ報告中で報告されていた回避方法を紹介。

• 文字列をBINARYにキャストする
  • 本事象は、文字列がlaten1かbinary以外の文字コードとして処理された時に起こるらしいので、文字列にbinary属性を与え、キャストしてやる。
  • ex) select date from STATUS force index(date) where date= binary '2010-01-19' limit 10;
• 整数形式で結合する
  • 文字列ではなく整数形式で値を与えてやる。こっちの方がすっきりしてるけど、ライブラリのプレースホルダで値を設定するときはデータ型に注意が必要そうだなー。
  • ex) select date from STATUS force index(date) where date= 20100119 limit 10;

あとDATETIME型が遅いって本当？

INT型の方がデータ取得の処理スピードが150倍高速の圧倒的効果である。INT型はINDEXを最適に使い目的の結果を返してくれるためここまでのパフォーマンス結果がでたものと思われる。面白い副産物結果として、DATETIME型ではINDEX有り・無しかかわらず処理結果値が同じということで、DATETIME型はINDEXの恩恵を受ける事があまりできないのである

http://blog.fukaoi.org/2009/03/19/mysql_datetime

fukaoi.org
以前、時刻の保存形式としてDATETIME型は低速でイケていない、unix_timestamp()関数で値を設定したINT型で保存すべき、というを話を上の記事で読み、なるほどそうしておこうかなと漠然と思っていたのだけど、ちょうどいい機会なので、MySQL5.5でも通じるTipsなのか検証してみました。ちなみに、MySQLのバイブル実践ハイパフォーマンスMySQL 第2版にはこうある。

3.1 最適なデータ型の選択

• 通常は小さい方がよい

一般に、データの格納と表現を正しく行えるデータ型のうち、最も小さいものを使用するように心がける。データ型が小さいほどディスク、メモリ、CPUキャッシュで使用する領域が少なくなるため、通常はその方が高速である。また、処理に必要なCPUサイクルも通常は少なくなる。(略)

• 単純なものがよい

(略)たとえば、文字セットとその照合順序(並び替えルール)は文字の比較を複雑にしているため、文字よりも整数を比較するほうがコストがかからない。ここに例が２つある。日付と時刻は文字列ではなくMySQLの組み込み型として格納すべきであり、IPアドレスには整数型を使用すべきである。
(強調は引用者)

DATETIMEないしTIMESTAMPを推奨されました。じゃあ、日付表現のための最も小さくて単純なデータ型ってなんでしょう。DATETIME型とTIMESTAMP型の解説は以下のようになってます。

3.1.4 日付と時刻型

DATETIME
　1001-9999年までの値を格納する事ができ、精度は1秒である。タイムゾーンとは無関係に、日付と時刻をYYYYMMDDHHMMSS形式で整数にパックする。これには8バイトの記憶域が使用される。(略)

TIMESTAMP
　1970年1月1日午前0時(グリニッジ標準時)からの経過時間を秒数で格納する。つまり、UNIXのタイムスタンプと同じである。記憶域を4バイトしか使用しない(略)

はい、DATETIMEもTIMESTAMPも、形式が違うだけでどちらも整数で管理されているんですね。ここら辺は公式ドキュメントに詳しいです。unix_timestamp()したINT型とTIMESTAMPはデータの格納方式として等価と考えていいということでしょうか。

特殊な振る舞いはともかく、TIMESTAMPはDATETIMEよりもストレージ効率がよいため、TIMESTAMPを使用できる場合一般にそれを使用すべきである。UNIXのタイムスタンプを整数値として格納することもあるが、通常はそうしたところで何の特もない。その形式は何かと扱いにくいので、お勧めしない。
(強調は引用者)

うーん、INTのTipsをフルボッコです。でも実際のところはどうなのでしょう。計測してみました。

計測条件

• 比較対象はcreated_at (DATETIME型)と、created_at_timestamp (TIMESTAMP型)と、created_at_int(INT型)。
• インデックスを使用したクエリ、使用しないクエリで、Range結合をテストする
• テストデータは2000万行
• データベースエンジンはInnoDB
• クエリキャッシュを無効にし、同一クエリを何度か実行し代表的な値を採用した
• 利用テーブルは以下

CREATE TABLE `STATUS` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `text` varchar(256) NOT NULL DEFAULT '',
  `point` int(11) NOT NULL DEFAULT '0',
  `created_at_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_at_int` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `c_` (`created_at`),
  KEY `ct_` (`created_at_timestamp`),
  KEY `ci_` (`created_at_int`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

スペック

OS ：CentOS release 5.3
DB ：MySQL5.5.3
CPU： AMD Phenom(tm) 9350e Quad-Core
Mem：8GByte

インデックス無し

DATETIME型

mysql> select SQL_NO_CACHE * FROM STATUS ignore index(c_) where 20090701000000 <= created_at and created_at < 20090701235959 limit 1;
1 row in set (5.92 sec)

TIMESTAMP型

select SQL_NO_CACHE * FROM STATUS ignore index(ct_) where 20090701000000 <= created_at_timestamp and created_at_timestamp < 20090701235959 limit 1;
1 row in set (8.40 sec)

INT型

mysql> select SQL_NO_CACHE * FROM STATUS ignore index(ci_) where unix_timestamp(20090701000000) <= created_at_int and created_at_int < unix_timestamp(20090701235959) limit 1;
1 row in set (6.13 sec)

DATETIME型とINT型がほぼ同程度の速度で、TIMESTAMP型だけなぜか一周り遅いという結果に。えー、上でTIMESTAMP型とINT型はデータ長以外等価って言い切ったばかりなのに…。実際には等価ではなく、扱うロジックに差があるために速度差が出ているんでしょう。でもTIMESTAMP型よりもデータ長が長く格納方式の複雑そうなDATETIME型がTIMESTAMP型よりも速いっていうのが謎…。何故に…

インデックス有り

DATETIME型

mysql> select SQL_NO_CACHE * FROM STATUS where 20090701000000 <= created_at and created_at < 20090701235959 limit 1;
1 row in set (0.00 sec)

TIMESTAMP型

mysql> select SQL_NO_CACHE * FROM STATUS where 20090701000000 <= created_at_timestamp and created_at_timestamp < 20090701235959 limit 1;
1 row in set (0.00 sec)

INT型

mysql> select SQL_NO_CACHE * FROM STATUS where unix_timestamp(20090401000000) <= created_at_int and created_at_int < unix_timestamp(20090401235959) limit 1;
1 row in set (0.00 sec)

えーと、どのデータ型でもきちんとインデックスが適用されていて、データ型の差異が計測出来ない程度に高速ですね。2000万行でこの速度ならどれ使ってもパフォーマンス上全く問題ないといえるでしょう。インデックス無しでは速度に差がありますが、どっちみちインデックス無しではどんなデータ型だろうと実用的なクエリ実行は行えません。これなら実践ハイパフォーマンスMySQLの教えに従って組み込みの時間型を使ってもなにも大丈夫そうです。自前のINT型、必要なし！
しかし深追いさんのベンチマークとの差はどこから来たんでしょう…。MySQL5.0.67から 5.5.3へのアップデートのどこかでDATETIMEが改良されたのでしょうか。ただ、深追いさんのベンチマークは、そもそもDATETIMEに対してインデックスが効いている気配がないので、今回僕がハマったようなバグかCardinalityの破損であのような結果になったのではないかという気も少ししています。自分の環境では以前の5.0.67でもDATETIMEのインデックスは効いていたような気がするので…。ちなみに今回のテストはInnoDBで行いましたが、MyISAMテーブルでもdatetimeインデックスは適用されました。

まとめ

時間のデータは素直にDATETIME型かTIMESTAMP型を使おう！*1

実践ハイパフォーマンスMySQL 第2版

*1:TIMESTAMP型は環境のタイムゾーンに依存し、4byteとコンパクトだが2037年問題を抱えている