知って得するInnoDBセカンダリインデックス活用術！

InnoDBはクラスタインデックスという構造になっている。今日はクラスタインデックスがどういうことかということを、皆さんに理解して頂きたい。もっとも理解して頂きたいポイントは「セカンダリインデックスのリーフノードには主キーの値が含まれている」ということだ。

主キーの構造

InnoDBの主キーは次の図のように「データが主キーのリーフノードに含まれる」という構造になっている。このような構造をクラスタインデックスという。

このような構造になっていることには利点と欠点があるが、大きな利点は主キーの値で検索をすると非常に高速だということだ。主キーのリーフノードにたどり着いたときには、既にデータのフェッチも完了している。データとインデックスが別々に格納されているタイプのストレージエンジンでは、インデックスからデータの位置を読み取って、その後データファイルからデータをフェッチする。このように二段階の操作が必要であると、キャッシュが効いていない場合には余分なディスクI/Oが生じてしまうだろう。だが、クラスタインデックスになっていると、インデックスを検索するだけでデータもフェッチできるのである。

クラスタインデックスのデメリットとしては、インデックスのサイズが大きくなってしまうこと、セカンダリインデックスを用いた検索が遅くなってしまうことなどが挙げられる。後者については次の項で解説しよう。

セカンダリインデックスの構造

クラスタインデックスを用いる場合、データはすべて主キーに格納されているので、セカンダリインデックスは特殊な構造にならざるを得ない。セカンダリインデックスの値からデータを取得するにはどうすればいいのだろうか？ご存知の方も多いだろうが、セカンダリインデックスのリーフノードには主キーの値が格納されている。例えば次のようなテーブルがあるとしよう。

mysql [localhost] {msandbox} (test) > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(10) unsigned NOT NULL,
  `b` int(11) DEFAULT NULL,
        :
   その他のカラム
        :
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

カラムaが主キー、bがセカンダリインデックスである。このテーブルに対して、b=2の条件で検索をかけたとき、たまたま主キーの値a=3という検索結果が得られたとする。他のカラムの値（＝データ）にアクセスするためには、そこからさらに主キーで検索をしなければならないのである。

もちろん、これは検索のコストが高くつく。単純計算では主キーの倍である。

見えざるCovering Index

クラスタインデックスにおけるセカンダリインデックスは、悪いことばかりではない。そもそも検索がセカンダリインデックスだけで済むようにCovering Indexにすれば非常に高速だ。その辺の事情については、過去記事「InnoDBでCOUNT()を扱う際の注意事項あれこれ。」にも書いてあるので参照して頂きたい。

セカンダリインデックスには主キーの値が格納されていることは先程述べた通りである。それは、即ちセカンダリインデックスの定義には、最後のカラムとして主キーが必ず含まれているようなものである。例えば、次のテーブル定義は全く同一なのである。

mysql [localhost] {msandbox} (test) > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(10) unsigned NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(10) unsigned NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`,`a`)                      # <==== インデックスにカラムaを追加しても同じ！
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

このことは、InnoDBテーブルモニターを使うとよく分かる。

--------------------------------------
TABLE: name test/t1, id 0 25, columns 5, indexes 2, appr.rows 0
  COLUMNS: a: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; b: DATA_INT DATA_BINARY_TYPE len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name PRIMARY, id 0 25, fields 1/4, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  a DB_TRX_ID DB_ROLL_PTR b
  INDEX: name b, id 0 26, fields 1/2, uniq 2, type 0
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  b a
--------------------------------------
TABLE: name test/t2, id 0 26, columns 5, indexes 2, appr.rows 0
  COLUMNS: a: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; b: DATA_INT DATA_BINARY_TYPE len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name PRIMARY, id 0 27, fields 1/4, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  a DB_TRX_ID DB_ROLL_PTR b
  INDEX: name b, id 0 28, fields 2/2, uniq 2, type 0
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  b a

両方のテーブルにおいて、２つ目のインデックスのFIELDSという項目には「b a」という２つのカラムが含まれているのがお分かりだろうか。テーブルt1のセカンダリインデックスには明示的にカラムaを追加していないのだが、暗黙的に主キーの値が含まれるのである。そのため、次のような検索は自ずとCovering Indexとなるため高速である。そして、ソートにもインデックスが利用される。

mysql> select a, b from t1 where b = 123 order by a;

まとめ

クラスタインデックスがどのような構造になっているかということ、その影響でInnoDBではセカンダリインデックスによる検索の特性が主キーとは異なるということについて説明した。そして、セカンダリインデックスには主キーの値が必ず含まれているので、Covering Indexとして利用できるということを説明した。クエリのチューニングを行う場合にはセカンダリインデックスの特性を考慮に入れて行うと、さらに一歩進んだ考察ができることだろう。