大人のためのInnoDBテーブルとの正しい付き合い方。

InnoDB関連でよくある質問のひとつに「テーブルのメンテナンスは何をすればいいんですか？」というものがある。InnoDBはMySQL 5.5でデフォルトストレージエンジンとなるため、InnoDBのテーブルメンテナンス計画を立ようと思う機会も増えることだろう。そこで、今日はInnoDBのテーブルメンテナンスの各種方法となぜそうしなければいけないかという理由を解説しようと思う。

ANALYZE TABLE

テーブルメンテナンスの代名詞といえば、インデックス統計情報の更新ではなかろうか。運用を続けるうちに、知らず知らずインデックス統計情報が狂ってしまい、思うような性能が出ない。RDBMSにはそのような問題がつきものであるが、InnoDBの場合、ANALYZE TABLEは不要である。なぜなら、InnoDBが自発的に統計情報を更新するからだ。InnoDBは以下の条件に適合すると、ANALYZE TABLEを自動的に行う仕組みになっている。

• 前回インデックス統計情報を更新してから、テーブルの行数全体の1/16が更新された。
• 前回インデックス統計情報を更新してから、20億行以上更新された。

というわけで、平均的なユーザーであればあまりインデックス統計情報の更新については気にする必要ないだろう。（ここでやる夫のAA「こまけぇこたぁいいんだよ！！ 」を挿入したいところだが、あえて自重しておくことにする。）

鋭い人はここで「なにっ？！勝手にANALYZE TABLEが走ってしまうと処理が止まってしまうのでは！？」と思われるかも知れない。だが、それも心配無用である。

InnoDBのインデックス統計情報更新処理は比較的（というかかなり）軽い処理である。テーブル全体をスキャンするというようなことはせず、ランダムにページを８回抽出し、そのページ内に含まれる行データを検査して、インデックスの統計情報を近似する。近似なので正確な統計情報ではないが、MySQLのオプティマイザが利用するには十分な精度なのである。８回のサンプリングを各インデックスごとに行うので、インデックスが多くなるとインデックス統計情報の更新は重い処理になるかも知れない。特にページがInnoDBバッファプールに乗っていない場合には、ディスクのReadまで発生してしまう。されどたかだか８ページなのである。（とはいえ、Facebookともなるとさすがに影響は出ているようである。）

MySQL 5.1のビルトインInnoDBでは、この「８回のサンプリング」というのは固定である。InnoDB Pluginではサンプル回数が調整できるようになっている。ページをランダムに抽出するため、たまたま偏りの大きいページばかりを抽出してしまう確率は０ではない。近似である以上それは仕方ないのだが、MySQLが立てる実行計画に影響が出るケースは確率的に皆無ではないのである。もし実行計画が思わしくなくなってしまった場合には、ANALYZE TABLEコマンドを手動で実行し、InnoDB Pluginであればinnodb_stats_sample_pagesオプションを調整するといいだろう。

余談であるが、インデックス統計情報はテーブルスペース内には記録されない。テーブルをopenしたときに計算されるのである。そのため、テーブルキャッシュが少ない場合には頻繁にインデックス統計情報が再計算されることになるので注意しよう。

参考：Controlling Optimizer Statistics Estimation

OPTIMIZE TABLE

ANALYZE TABLEと並んでテーブルメンテナスの双璧をなすのが、このOPTIMIZE TABLEであろう。InnoDBの場合、フラグメンテーションが発生する頻度はそれほど高くはないが、やはり発生してしまうケースというのは存在する。

InnoDBは追記型ではないのでVACUUMは必要ないのだが、MVCC（マルチ・バージョン・コンカレンシー・コントロール）という構造になっているためパージ処理が必要になる。以下はInnoDBが行を更新したときの様子を模式的に表したものである。

MVCCとは、簡単にいうと同じ行データに対して複数のバージョンが存在し、実行中のトランザクションによって見える値が異なるというものである。データを上書きする前に古いデータをロールバックセグメントと呼ばれる領域へ退避し、現在のレコードのロールポインタと呼ばれるフィールドが、古いレコードを指すように設定する。ロールポインタを辿っていくことで、履歴のように過去のバージョンの値を参照することが出来るわけだ。どの値が見えるかは、トランザクションを実行したのが何時の時点かということで決定する。このような仕組みによって、REPEATABLE-READの要件を満たすことが出来るのだ。

MVCCによりREPEATABLE-READを実装する利点は、更新が参照をブロックしないことである。あるトランザクションが行の値を更新して「行ロック」をかけた状態になっていても、古いデータへの参照はブロックされることはない。なぜなら、ロールバックセグメント中の該当エントリ（Undoレコードという）はロックされていないからだ。これにより、高い並列性を保つことが出来るのである。

もちろん、このような仕組みになっているため、ロストアップデートと呼ばれる問題が生じることになる。ロストアップデートとは、古い値しか知らないトランザクションが、古い値を元に算出した値で行データを上書きすることによって他のトランザクションが行なった更新が失われてしまう（だからロストアップデートというんだよね）ことである。ロストアップデートは更新をかけたい行に対して、明示的に行ロックをかけること（SELECT ... FOR UPDATE等）で防ぐことが出来る。

DELETEの場合、MVCCになっているため削除された時点では実際に行データは削除されず、該当のレコードには削除のマークがつけられる。と同時に、ロールバックセグメントに行データが退避されるのである。従って、行が削除される以前に開始されたトランザクションは、行が削除された後でも、その行データを参照することが可能なのである。

InnoDBではこのようにロールバックセグメントを活用するため、ロールバックセグメントが元気に成長してしまうことになる。Undoログは、そのログを参照する可能性のある古いトランザクションが終了すれば不要になる。VACUUMは必要ないが、古い不要なUndoログを回収する必要があるのだ。これを担当するのがパージと呼ばれる処理である。パージでは、Undoログの回収や削除された行の削除を実行する。（パージされた時点で本当に削除されるのだ。）この辺の詳しいことについては、著書、エキスパートのためのMySQL[運用+管理]トラブルシューティングガイドで丹念に説明しているので、ぜひそちらを見ていただきたい。

少し話がそれてしまったが、OPTIMIZEが必要になるのはどういう時だろうか？簡単にいうと、「行をDELETEのして無駄な領域がたくさん生じたとき」だけである。これによって、利用されていない領域が回収されることになる。そして、フラグメンテーションが解消するわけである。

実は、InnoDBにはOPTIMIZE TABLEに相当する機能は実装されておらず、代わりにALTER TABLEが実行される。OPTIMIZE TABLE t1は次のコマンドを実行するのと同じなのである。

mysql> ALTER TABLE t1 ENGINE INNODB;

このコマンドを実行すると、テーブルのデータが詰め直される。データの挿入は主キーの順番で行われるため、OPTIMIZE TABLEすると

• フラグメンテーションが解消する。
• テーブルスキャンが高速になる。

という効果が期待できることになる。残念ながら、セカンダリインデックスをセカンダリインデックスの順番で詰め直す方法はない。そのため、セカンダリインデックスの空間効率はあまりよくない場合があるので注意が必要である。

OPTIMIZE TABLEが内部的にALTER TABLEと同じということは、別に悪いことばかりではない。モノホンのOPTIMIZE TABLEだと実行中は参照も更新もブロックしてしまうことになるが、ALTER TABLEであればメンテナンス中も参照は可能なのである。

UPDATE vs DELETE+INSERT

稀に、「UPDATEは重い処理だし行ロックが心配だから、いったん行をDELETEしてからINSERTする」というテクニック（？）を利用しているのを見かけるが、これはバッドノウハウである。上記で説明したとおり、DELETEのはフラグメンテーションの原因になるし、UPDATEもDELETEのも等しくロールバックセグメントにUndoログが作成されるからだ。処理が２回発生する分、DELETE+INSERTのほうが処理が重い。DELETE+INSERTを使っても良いことはひとつもない。わざわざフラグメンテーションを作り出して、テーブルメンテナンスの手間を増やしているようなものである。

素直にUPDATE文を利用するように心がけよう。

VARCHAR vs CHAR

VARCHARの代わりに固定長のCHARを利用するのはフラグメンテーションを軽減するのに有効だろうか？実は、答えはNo!!である。確かにCHARを使うとデータサイズが統一されるので、InnoDBが空き領域（DELETEで空いた穴）にそのまま行データを突っ込むことが可能であり、フラグメンテーションの改善に役立つようなイメージがあるかも知れない。だが、InnoDBは細切れの空き領域にそのまま行データを突っ込めない場合、ページ内のデータをいったんキレイに詰め直してから挿入する。（もちろん領域を空けてもデータがはいりきらない場合にはページが分割される。）そのため、VARCHARの代わりにCHARを使うのは、空間効率を犠牲にしてCPU効率を稼ぐ程度の効果しかなく、フラグメンテーションの軽減には繋がらないのである。

また、そもそも主キーの場合、新た追加する場合（つまりINSERT時）、主キーの値は最も大きな値であることが多い。そのため、そのレコードが該当するページはインデックスの最後部であって、DELETEにより空いた穴の存在するページあることは稀であろう。つまり、主キーにおいては行が固定長になっても全然メリットがないのである。

行を固定長にするメリットは、

• 主キー以外のフィールドをUPDATEで変更する。
• 変更後のデータ長が変更前よりも長くなり、元の場所に収まらない。

という場合に、

• ページを分割する必要がない場合だけ、
• CPUリソースを節約できる。

という軽微なものなのである。メリットを享受できる機会など確率的にどれだけあるだろうか？しかも、すべてのカラムを固定長かつNOT NULLにしなければ効果がないので、空間的コストはかなり大きい。従って、CPUがボトルネックになっており、なおかつメモリが十二分に余ってるのであれば少しばかりの性能改善は見られるかも知れないが、大きな効果は期待できないだろう。また、空間効率が犠牲になるためディスクI/Oがボトルネックになっている場合、CHARの利用は絶対にオススメしないのである。

InnoDBに文字列を格納するならVARCHAR一択でキマリだ！！

監視

蛇足であるが、InnoDBテーブルの監視項目についても述べておこう。

• SHOW ENGINE INNODB STATUS ・・・ InnoDB全体のステータスを確認。
• SHOW TABLE STATUS ・・・ テーブルのデータ容量や行数など。
• SHOW INDEX ・・・ インデックス統計情報。
• テーブルモニター ・・・ InnoDBテーブルのさらに詳細な情報。

どんな情報が採取できるかということについては、著書、エキスパートのためのMySQL[運用+管理]トラブルシューティングガイドでもっと詳しく説明しているので、ぜひそちらを見ていただきたい。

バックアップ

蛇足その２。データのバックアップについても触れておこう。バックアップには様々なアプローチがあり、それぞれ利点・欠点が存在する。

• mysqldump ・・・ 論理バックアップをオンラインで取得する。他の方法に比べると速度は遅め。
• コールドバックアップ ・・・ データのコピーをオフライン時に取得する。
• スナップショット ・・・ LVMやZFS、またはストレージのスナップショット機能を利用してバックアップをとる。（参照：MySQLバックアップ頂上決戦！！ LVMスナップショット vs InnoDB Hot Backup）
• InnoDB Hot Backup ・・・ 商用のバックアップ製品。オンラインで採取でき、OSやストレージのスナップショット機能を必要としない。

各種バックアップ方法の手順やリストアの方法については、著書、エキスパートのためのMySQL[運用+管理]トラブルシューティングガイドで詳しく(ry

まとめ

いかがだろう？恐らく多くの方が「InnoDBは手間がかからないなあ」と感じられたのではないだろうか。まさにその通り。あんまり手間暇をかけなくても良い性能が出るというのは、InnoDBの最大のメリットなのである。

ぜひ幸せなInnoDBライフを満喫していただきたい！！