Blogs

InnoDBの主キーはクラスターインデックスだということを意識しよう

InnoDBについて一番大事なことは主キーがクラスターインデックスだということです。

クラスターインデックスでは、主キー(B-tree)のリーフページにデータが直接格納されています。

以下の図のようなイメージです。

クラスターインデックス


一方、主キー以外のインデックス(副次インデックス)はリーフページに主キーの値を格納
していて、データにアクセスするためにそれを使用します。
以下の図のようなイメージです。

非クラスターインデックス


主キーがクラスターインデックスであることの必然的結果 NO1

副次インデックスでデータにアクセスする場合に、
1)副次インデックスのB-treeより主キーを取得する
2)その主キーからデータをアクセスする
という2段階のアクセス経路になるというこです。

よって、副次キーでのアクセスは、たとえそれがユニークキーであったとしても、
主キーでのアクセスに比べて、倍近くのI/Oが発生することになります。

以下が計測した結果です。
InnoDB vs MyISAM パフォーマンス比較 PrimaryKEY、UniqueIndex、非UniqueIndex

「1.PrimaryKeyで一件検索」と「2.UniqueIndexで一件検索」のInnoDBどうしの結果を比較すると主キーに比べると、ユニークな副次インデックスは55%程度の性能しか出ていません。

主キーがクラスターインデックスであることの必然的結果 NO2

データの格納場所が主キーの値でクラスター化されているので、
・データを連続してアクセスする場合(同一ページに存在する確率が高いので)、
バッファが有効に使用される
・同じ理由から主キーの昇順等でアクセスする場合に性能が高い
ということになります。

これも
「3.PrimaryKeyで範囲検索」と「4.非UniqueIndexで範囲検索」のInnoDBどうしの結果を比較すると
5倍程度の違いが出ています。

主キーがクラスターインデックスであることの必然的結果 NO3

主キーでアクセスすると、必然的にデータにアクセスしてしまう。
よく、InnoDBはMyISAMに比較して、
SELECT COUNT(*) FROM テーブル ※where条件なし
が圧倒的に遅いと言われますが、その原因の一部が上記にあたります。
MyISAMの条件無しカウント処理が速いのは統計情報から結果を
取得するので当たり前なのですが、InnoDBがこの処理に関して
遅すぎ!なのは、条件無しカウント処理を主キーでアクセスするため
全データページにアクセスしてしまうためです。
SELECT COUNT(*) FROM テーブルの後ろに副次インデックスの
ヒントを付ければ速くなります。

主キーがクラスターインデックスであることの必然的結果 NO4

主キーの値を変更する場合に、データ自体の格納場所を変更するためにコストが高い

主キーがクラスターインデックスであることの必然的結果 NO5

副次インデックスのリーフページのすべてに主キーが格納されるため
主キーの項目長が長くなった場合の悪影響が大きい。

その他 InnoDBのクラスターインデックスについて重要なこと

明示的に主キーを指定しない場合は以下の暗黙の主キーがMySQLによって設定されます
1)NOT NULL指定のユニークキーを指定している場合、それが主キーになる
2)それも無ければ、6byteのROWIDが主キーになる

kajiwaraInnoDBの主キーはクラスターインデックスだということを意識しよう