小野マトペの業務日誌(アニメ制作してない篇)

introduction

俺こと小野マトペが、大学生活と折り合いをつけつつショートアニメを制作していく事に関する制作日誌でしたが、今はグダグダです。始まりは9/11から。旧HP(廃棄)

レビューライン.jp稼動中。
「平面男(完全版)」20041224  ■「平面男」20041018/20041020
■Javaアプレット: Conna-Gonna20050203/ 20050208
■はてなブックマーク改造用Greasemonkey集20061002
■はてなQuickClip(firefox用機能拡張)20060906

2010年04月30日 金曜日

[][] MySQL5.5.3-m3のDATETIME型のバグ。あとMySQLのDATETIME型は本当に遅いのか検証してみた 06:00  MySQL5.5.3-m3のDATETIME型のバグ。あとMySQLのDATETIME型は本当に遅いのか検証してみたを含むブックマーク  MySQL5.5.3-m3のDATETIME型のバグ。あとMySQLのDATETIME型は本当に遅いのか検証してみたのブックマークコメント

バグの話

近々ふぁぼったーDBInnoDB化を企てているので、それに伴い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がリリースされれば解消されているのだけど、バグ報告中で報告されていた回避方法を紹介。

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

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

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

no title

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

3.1 最適なデータ型の選択

  • 通常は小さい方がよい

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

  • 単純なものがよい

(略)たとえば、文字セットとその照合順序(並び替えルール)は文字の比較を複雑にしているため、文字よりも整数比較するほうがコストがかからない。ここに例が2つある。日付と時刻は文字列ではなく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フルボッコです。でも実際のところはどうなのでしょう。計測してみました。

計測条件
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

スペック

OSCentOS release 5.3

DB :MySQL5.5.3

CPUAMD 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

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

ono_matopeono_matope 2011/04/02 17:51 削除依頼はメールにてお願いします。

ono_matopeono_matope 2011/07/10 22:43 matope.ono [ at ] gmail.comになります。

ono_matopeono_matope 2011/11/15 04:15 だいたい月一間隔で対応してます。

r3333r3333 2011/11/16 16:22 14日と本日削除依頼をGmailに送らせていただいた者です。ご多忙な中誠に申し訳ないのですが、何卒早急なご対応をお願いできませんでしょうか。不利益を被っております。何卒、お早いご対応を切にお願い申し上げます。

yukitasoyukitaso 2011/12/26 09:13 何度もふぁぼったー削除以来をGmailに送らせていただいております。
早急な対応をお願いいたします。

トラックバック - http://d.hatena.ne.jp/ono_matope/20100430
プロフィール

ono_matope

ono_matope

もうなんか全然アニメ描いてません。もっぱらプログラミングばかりしてます。ごめんなさい。レビューライン.jp管理人

    << 2010/04 >>
    1 2 3
    4 5 6 7 8 9 10
    11 12 13 14 15 16 17
    18 19 20 21 22 23 24
    25 26 27 28 29 30
    統計情報
    pv:1650983
    counter:
    この日記のはてなブックマーク数
    参照した本/DVD
    • 実践ハイパフォーマンスMySQL 第2版
    • Wacom Cintiq 21UX DTZ-2100D/G0
    • Linux-DB システム構築/運用入門 (DB Magazine SELECTION)
    • ふしぎの海のナディア VOL.01 [DVD]
    • WEB+DB PRESS Vol.45