MySQL　InnoDBストレージエンジンのチューニング（前編）

004

更新日: 2012/07/26
公開日: 2012/03/09

通知

InnoDBのアーキテクチャおさらい

　クエリは、物理レイヤーの特性を意識して書かなければいけないものの、基本的にはロジック（論理）を記述するレイヤーである。前回まで解説したオプティマイザは、論理と物理の中間に位置する（または両者をつなぐ)レイヤーであると言える。つまり、オプティマイザにとっての至上命題は、如何に効率良く下位のレイヤーを使うというものであると言える。一方、下位のレイヤーであるストレージエンジンはまさに実装（物理）の領域である。ユーザーが必要とするクエリに対していかに素早く応答するかが、物理レイヤーたるInnoDBのチューニングにおける最終目標となる。

　物理レイヤーのチューニングをするには、そのアーキテクチャを知ることが何よりも重要だ。アーキテクチャこそが物理レイヤーそのものだと言っても過言ではないからだ。以下では、チューニングを行う上で必須となる、基本的な構成部品について説明する。

全てはインデックス

　InnoDBはクラスタインデックスという構造になっており、データは全て主キーのリーフノードに格納されている。明示的な主キーがない場合には、内部的に6バイトのサイズを持つROWIDフィールドが追加され、主キーとして利用される。このROWIDはユーザーがアクセスすることはできない。セカンダリインデックスには、リーフノードに主キーの値が格納されている。セカンダリインデックスを用いた検索を行うと、セカンダリインデックスから主キーの値を導き出し、その値を用いて主キーが検索されることになるため、2回のインデックス操作が行われることになる。そのため、主キーとセカンダリインデックスでは主キーによる検索のほうが高速になる。次の図は、セカンダリインデックスと主キーを模式的に表したものである。

　このような構造になっているため、セカンダリインデックスは必然的に主キーの値を含んだマルチカラムインデックスとなる。例えば次のようなテーブルがあるとする。

CREATE TABLE t (
id INT UNSIGNED NOT NULL,
col1 INT NOT NULL,
...中略...,
PRIMARY KEY(id),
INDEX ix1 (col1)
) ENGINE INNODB;

　ix1という名前のインデックスは、col1しかカラムが指定されていないが、構造的には(col1, id)というマルチカラムインデックスと同じになる。そのため、Coverying Indexになる機会が増える。

　また、InnoDBではテーブルスキャンは主キーのインデックススキャンと同じである。そのため、レコードが主キーの順序でフェッチされることになり、ORDER BYを指定しなくてもソートされているという思わぬ副作用が生じてしまう。これは思わぬ落とし穴になることがある。これ幸いとORDER BYをつけずにSQLを記述していると、後で他のストレージエンジンやRDBMSへの移植が必要な時に大変な思いをすることになるので注意されたい。

　なお、多くのRDBMSがそうであるように、InnoDBのインデックスはB+ツリー構造になっている。全てのデータがB+ツリーで管理されているといえよう。ただしBLOBは例外である。

テーブルスペース、バッファプール、ログ

　InnoDBのデータは、テーブルスペースと呼ばれる領域に格納される。テーブルスペースは16KBのページ単位で管理されており、このページを用いてB+ツリーが構成される（次期バージョンであるMySQL 5.6では、4KBまたは8KBのページサイズをオプションで選択可能だ）。テーブルスペースにはinnodb_file_per_tableオプションの指定がない限り、共有テーブルスペースに全てのデータが格納される。共有テーブルスペースは、デフォルトでは自動的に拡張するようになっている。固定サイズのものやRAWデバイスを利用するものを作成することも可能だ。innodb_file_per_tableオプションを使用すると、テーブルごとにひとつのテーブルスペースが作成されるようになる。これは便利なオプションで、テーブルスペースは基本的に自動拡張となるが、テーブルをDROPしたりOPTIMIZE TABLEをすることでファイルシステムの領域を回収することが可能だ。

　テーブルへの参照や更新が行われた際、直接テーブルスペースへアクセスされるわけではない。全てのデータは、バッファプール上でキャッシュされる。参照時にはキャッシュに該当するデータがなければ、ディスクからキャッシュへデータがステージングされる。もちろんキャッシュされるのは16KBのページ単位だ。スキャンなどの場合には、64ページ単位（エクステントという）でステージングが行われる。また、データを更新するとそれがいきなりファイルが更新されるわけではなく、全てはキャッシュ上で操作が行われる。テーブルスペースの更新はランダムアクセスになりがちで時間がかかるため、後から非同期で行なわれる。すると、キャッシュとテーブルスペースに差異が生じることになるが、キャッシュ上だけにしか更新が反映されていない状態でMySQLサーバーがクラッシュすると、テーブルスペースにはデータが残らない（消えてしまう？！）ことになる。そのようなデータの消失を防ぐのがログだ。ログへの更新はテーブルスペースとは違い、トランザクションのコミット時に更新が行われるが、常にシーケンシャルになるので高速である。とはいえ、直接ログファイルへ更新すると遅いので、ログへ書き込む内容はログバッファ上でいったんまとめられる。

　InnoDBは再起動時にログの内容を読み込んで更新をREDOする。そうすると、バッファプール上のデータは最新の状態まで復帰することになる。突然クラッシュした場合には、再起動後にトランザクションの状態も復元されるため、それらはREDO後にUNDO（ロールバック）される。

　テーブルスペース、バッファプール、ログは切り離すことができない構成要素なのである。