MySQLのMyISAM型テーブルは、レコードの追加・削除を繰り返しているとどんどん効率が悪くなっていく。そこで定期的にoptimize tableを発行して統計情報の更新や、インデックスページのソートを行ってやることでテーブルの状態を最適化することが出来る。のだが…そこで思いっきり嵌った。
全部のテーブルをoptimizeして、今までslow-logに出ていたあるSQLを実行すると7秒かかっていた処理が1秒かからないまでに実行時間が短縮されたため、効果が出たと喜んでいたのだが。しばらく経って普通にシステムを使ってみると、時折異様に処理が遅いことがある。いままでDBの構成はMaster1+Slave1の２台構成だったのだが、メンテナンスでSlaveを2台増やした。どうも増設したSlaveにアクセスすると重いことがわかった。既存のSlaveと増設Slaveでの違いは、optimizeしたかどうか。optimizeしたSlaveのほうが、ある特定のSQLで200倍も遅くなっていることが判明した…200倍って！
問題になったSQLは、outer joinやunionを使いまくった、一目見ただけでは何をしているのかサッパリわからないほど複雑なSQLだった。保守性を考えれば、そういう複雑なSQLは使わずに、プログラム側でループを使うなりハッシュを使うなりしてわかりやすくするべきだ。しかし今回はあくまで処理速度重視というかアプリサーバとDBサーバ間の通信を極力減らす必要があったため、複雑なSQL1発で済ませなければならない事情があった。
最適化していないDBでは、explainしてみるときちんとインデックスが使われているのに対し、最適化したほうのDBではkeyがnullになっており、あるテーブルがフルスキャンされていた。そのフルスキャンされているテーブルはレコードが100万近く入っており、そりゃ時間かかるわなって話。2000秒以上テーブルロックして（MyISAM型は行ロックではなくテーブルロックがかかる…その間readもwriteも不可）、そのせいでアプリサーバもウェブサーバもコネクション使い切って死ぬ。
結局その問題はforce indexを使うことで無理矢理解決。あるいはstraight joinを使うことでも同様の効果が得られた。たまたま同じデータで最適化したものとしてないものがあったので、explainでどのindexを使えば速くなるのかすぐわかったので助かった…
実際のところforce joinよりもstraight joinのほうが、optimizerが順序を考えなくて済む分若干速いのかな？その辺はちゃんと調べる余裕がなかったので謎。まぁ複雑なクエリを投げるときはoptimizerに頼っちゃダメってことですな…