MyISAMとInnoDBのどちらを使うべきか
Twitterで話題になってたので簡単にまとめました。

●MyISAMにしか無い機能を使いたい場合はMyISAMを使うしかない
・全文検索 (TritonnやSphinx)
・GIS

●InnoDBの利点(MyISAMの欠点)
▲障害対応系
・クラッシュしても再起動するだけでリカバリができる
・クラッシュリカバリにかかる時間はテーブルサイズに比例するようなことはな
く、コミット済みのデータは修復できる (巨大なMyISAMテーブルのREPAIRには数
日単位で時間がかかることがある)
・オンラインバックアップができる
・INSERTやLOAD DATAなどを実行している途中でCtrl+Cでその更新系SQL文を止め
ても、テーブルは壊れないし、中途半端な状態で更新されることも無いし、ス
レーブが止まることも無い

▲性能系
・行レベルロックなので並列性が高い(MyISAMはテーブルロック)。またSELECTと
更新系SQL文が競合しない。
・主キー検索が高速 (クラスタ索引のため)
・ダイレクトI/O(innodb_flush_method=O_DIRECT)を使えるためキャッシュ効率
が高い
・インデックスの追加/削除をするにあたってテーブルを再編成する必要がな
く、そのインデックスだけを再構築するので効率が良い(InnoDB Plugin)
・Insert Bufferという仕組みにより、セカンダリインデックスへのINSERT処理
の効率がMyISAMよりも良い

▲従来は欠点だったが、InnoDB Pluginによって改善されたもの
・グループコミットが無効化されるため同時更新性能が著しく低下していた
・I/Oスレッドが事実上読み書き1本ずつしか無かったため並列性が低く、RAIDや
SSDを有効活用できなかった
・CPUスケーラビリティが悪く、4CPUコアくらいまでしかスケールしなかった
・同じ量のデータを投入してもテーブルサイズがMyISAMよりも倍以上大きくなる
ことがある(InnoDB Pluginの圧縮機能を使うことで緩和する手がある)

▲ほか
・InnoDBでは外部キーが使える


●MyISAMの利点
・WHERE条件無しのSELECT COUNT(*)が一瞬で返る
(InnoDBの場合はテーブルをなめる必要がある)
・メモリにおさまらないほど巨大なテーブルのフルテーブルスキャン系の処理効
率が良い
(InnoDBではこうしたバッチ処理でバッファプールの中身が追い出されてしまう
し、バッファプールの管理オーバーヘッドもあるが、MyISAMは専用のバッファ
プールを持たないので効率が良い)
・OSコピーによってテーブルの移動が極めて簡単にできる
・MERGEテーブルを使うことができる (InnoDBでも5.1のレンジパーティショニン
グを使えば十分なことは多い)
・リードオンリーのテーブルであれば圧縮できる
・ALTER TABLE ENABLE/DISABLE KEYSを使える。例えばインデックスなしの状態
で高速にロードして、後からインデックスを有効化とかができる (InnoDB
Pluginではインデックス単位の再構築ができるのでかなり緩和できる)
・InnoDBはテーブルのオープン処理がシリアライズされるため、大量の数のテー
ブルを初回オープンするような処理がきつい

▲ソリューションによって緩和できるもの
・クラッシュ時に壊れる問題やリカバリ(REPAIR TABLE)の遅さは、生きているス
レーブを使って復旧すれば解決できる
・テーブルが巨大になることで引き起こされる性能問題は、小さなテーブルに分
割することで解決できる


 自分は、特別な事情が無い限り、5.1最新版に含まれるInnoDB Pluginを勧めて
います(*注)。ログ蓄積系のテーブルではMyISAMが良いと考えている方が結構多
いのですが、MyISAMでは複数のクライアントから同じテーブルに対してINSERTを
すれば競合してしまいますし、InnoDBのInsert BufferのようなI/O最適化の仕組
みが無い(詳しくは「Linux-DBシステム構築/運用入門」の9章あたりを参考にし
てください)ので、InnoDBに比べても処理効率が悪いです。MyISAMを活用する場
合は、上に挙げたようなテクニックを使って問題を緩和するのが効果的です。
 ちなみに、マスターをInnoDBにして、スレーブをMyISAMにするのは以前Postし
たように特別な注意が必要です。

(*注追加) InnoDB Pluginの現時点での位置づけはベータです。ただし、
Dynamic/Compressedという特別なテーブルフォーマットを使わない限り、既存の
InnoDBテーブルと互換性があるので、InnoDB Pluginを使いつつ、不安定な現象
に遭遇したらパラメータを変えて通常のInnoDBに戻すことが可能です。追加イン
ストールやインストールし直し等が必要ないという手軽さが魅力です。