MySQLの変な癖、あるいはPostgreSQL使いがMySQLと接するときの心構え
はじめに
PostgreSQLのクエリプランナ/クエリオプティマイザは非常に優秀です。ユーザーはただ自分が欲しいデータをSQLで記述し、酷使するカラムに対してインデックスを張ってやればいいだけです（DB自体が酷使されてるなら細かいチューニングは必須です）。MySQLはそれら一連の最適化を人間が担当することになります。
後で詳しく書きますが、たとえば、"SELECT * FROM foo WHERE id IN (1,2)"と"SELECT * FROM foo WHERE id=1 OR id=2"の2つのクエリを、PostgreSQLはまったく同じように処理します。意味が同じなので当然といえば当然です。MySQLもたぶんここまでは同じです。問題は次。
- "SELECT * FROM foo WHERE aid=1 OR bid=2 ORDER BY cid DESC LIMIT 10"
- "SELECT * FROM (SELECT * FROM foo WHERE aid=1 UNION SELECT * FROM foo WHERE bid=2) as tmp ORDER BY cid DESC LIMIT 10"
aidとbidとcidの3カラムが登場しました。どちらのクエリも意味は同じです。さて、aid,bid,cidともにインデックスが張られているとして、MySQLでは後者のほうが圧倒的に高速です（コメント欄１つめ）。PostgreSQLではどちらも同じか、2つ目のごちゃごちゃしたやつのほうが遅いかだと思います。ちなみに、2つ目のSQLにおいてcidのインデックスは使われません。
本稿では、このようなMySQLの癖についてとりあえずまとめたいと思います。PostgreSQLは8.2以降、MySQLは5.1を想定しています。
MySQLはSELECT文の中で１つのインデックスしか使わない
たとえaid,bid,cidにそれぞれインデックスが張られていようとも、SELECT (略) WHERE aid=1 OR bid=2 ORDER BY cid DESCという一文において使われるのはひとつだけです。aid=1を探すのにインデックスを使うと、bidの絞り込みは全スキャン（MySQLのEXPLAINでいうとALL）、cidでのソートもまた全スキャン（同Filesort）になります。こういうクエリをEXPLAINすると、負け惜しみのようにpossible_keysとかいって候補一覧を出してくるところが苛立たしいです。わかってんなら使えよ。
この欠点というか怠惰なところに対処したのが、さっきのUNIONとか出てきたほうのクエリです。UNIONの前後でそれぞれ独立した文なのでインデックスを計2回使えます。UNIONで動的に作ったテーブルなのでfooに張られたcidのインデックスは出番がありません（Filesortになります）。いちおう次のようにすることで、すべてインデックスを使うクエリにできます。
- CREATE TEMPORARY TABLE tmp (KEY(cid)) (SELECT * FROM foo WHERE aid=1 UNION SELECT * FROM foo WHERE bid=2)
- SELECT * FROM tmp ORDER BY cid DESC LIMIT 10
UNIONの結果集合（WHEREで絞り込んだ部分集合）をテンポラリテーブルにして、cidにインデックスを張っていったん終わり、次のクエリで改めてORDER BYして取り出す形です。tmpの集合をこれ以外の用途（たとえばSELECT count(*)するとか）にも使う予定があるなら、テンポラリテーブルにして使いまわしたほうがいいと思います。
さて。ORがUNION（和）ならANDはINTERSECT（積）だな、と思うかもしれませんが、MySQLはINTERSECTに対応してません。このようにサブクエリをJOINしまくることで一応実現はできますが、インデックスは不完全燃焼です。ORのときのようにテンポラリテーブルを作ってもいいですが、一般的にはインデックスの張り方を工夫して対処します。
たとえばWHERE did=5 AND eid=6という条件式であれば、didとeidの複合キーを作ります。ここでWHERE eid=6 AND did=5と書くとdid_eidなインデックスは使われないので注意です。意味は同じですが解釈が変わり、MySQLは黙々と全スキャンし始めます。
O/Rマッパのようなものを使っているのならWHERE句になるべきものは配列か何かで保持しているはずなので、SQLへ変換する前に辞書順でソートするようにしておけば順番については気にしなくてもいいかもしれません。
しかし調子に乗ってaid,bid,aid_bid,cid,did_eidなどなど片っ端からインデックスを張るのは美学的にも実用的にもおすすめできません。実テーブルの数倍の容量を持つインデックスがいい仕事してくれるときもありますが、なんというか、効果も感想も微妙です。
DATE型、DATETIME型のインデックスは信用しない。TIMESTAMP型は忘れる。
言いたいことはここで全部言われてますが、補足すると、DATETIME型のカラムをBETWEENしたいときは+ INTERVAL 1 DAYとかするといいらしいです（未検証）。たぶんdtが文字列系の何かにキャストされて張られたインデックスがはがれ落ちるのを防ぐ意味があるんだと思う。ていうかそもそもDATE型の範囲が1000-01-01から9999-12-31ってとこからして、内部的には中途半端に文字列として扱われてるような気もする。
あ、PostgreSQL使いに向けての説明だってことを思い出したので書いておきます。CREATE TABLE foo ( dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)は使えません。CREATE TABLE時にCURRENT_TIMESTAMPが解釈され、テーブルを作った時刻がデフォルト値になります。INSERT時に毎回CURRENT_TIMESTAMPを指定してください。それを自動でやってくれるのがTIMESTAMP型とのことですが、まったく関係ないUPDATEが走っても勝手にカラムの値が更新されます。つまり、updated_atには使えるけどcreated_atには使えないということです。
この微妙な状況を打開するため、日付情報を整数型として使う手が出てきます。20090615みたいなやつですね。秒まで記録するにはINTじゃ桁が足りないのでBIGINTを使って20090615232425のようにします。
こいつにインデックスを張り、仮にカラム名をtsとすると、ts >= 20090615000000 AND ts < 20090616000000とすることでインデックスを使いつつ特定日時・時刻の結果が得られます。ts LIKE '20090615%'のように手抜きしないこと。tsが文字列になってインデックスが使われなくなりますので。FLOOR(ts / 1000000) = 20090615も同様にダメです。
JOINはなるべくしない。サブクエリは丁寧に。
「RDBMSはJOINが遅いから〜」という言説を聞いたことがあるかと思います。これも上で述べた「１ SELECTで使われるインデックスは１つ」がその理由です。ていうかRDBMSじゃなくてMySQL特有の現象だと思います。
JOINをなるべく控えるのは、もちろん「１ SELECTで使われるインデックスは１つ」の法則により、貴重なインデックス使用枠がひとつ減るからです。逆にサブクエリは、インデックスの使用枠をひとつ増やせるので積極的に使っていきましょう。
PostgreSQLのような賢いクエリプランナが居ないことを思い出してください。WHERE posts.writer_id IN (SELECT id FROM writer WHERE name LIKE 'john')と書けば勝手にWHERE EXISTS(SELECT 1 FROM writer WHERE name LIKE 'john' AND id=posts.writer_id)と同じ意味だと理解してよしなにやってくれますが、MySQLでは後者のほうが速いです。サブクエリの中のSELECTも*やidなどではなく1などの定数を使ったほうが若干速いようです。
LIMITは処理時間をほとんど低減しない
ORDER BY foo DESC LIMIT 10と書けば上から10件見つかった時点で処理を停止するので、LIMIT 100やLIMITなしよりも速いはずだと思いがちですが、別にそういうわけでもありません(細かいことをいえばLIMIT 1よりLIMIT 1000000のほうが遅いに決まってますが、フェッチの時間を無視すれば誤差です)。例外はLIMIT 0で、これはクエリの解析を終えた時点で終了するので（実データを見にいかないので）高速です。文法チェッカーとしてLIMIT 0を使いましょう、みたいなことをMySQLのサイトで見た気がします。
PostgreSQLはたしか8.2か8.3あたりでORDER BY 〜 LIMIT構文の最適化がなされて、LIMIT 10ならまず上位10件を特定し、その10件だけをソートすることで高速化してる（つまりLIMITしたあとORDER BYしてる）のでLIMITの数はけっこう影響しますが、MySQLは全部ORDER BYしたあと改めてLIMITなので、1でも1000でもそんなに差はないです。
集約関数はそれほど遅くない
MySQLはGROUP BYやcount(),sum()などの集約関数が遅いと言われていたので身構えてましたが、いろいろ触るうちに言うほど遅くないなと思うようになりました。集約関数よりも、インデックスがひとつしか使われない影響の方が大です。JOINがインデックス枠をひとつ消費するようにGROUP BYもひとつ消費するので、それに気をつけていれば大丈夫かと。
MySQLのGROUP BYはPostgreSQLのような厳格さがなくて個人的には使いやすいです。
他にも何かあった気がするけど思い出せない
とりあえず第一部はこれで終了ということで。
まとめ：そりゃみんなRDBMSよりDHTに興味持つわ
こめんと