InnoDBのプライマリキーとセカンダリキー

InnoDBのテーブルから、プライマリキーを取得するクエリを書いたのに、なぜかセカンダリインデックスが使われることがある。この仕組みを、InnoDBのインデックスの格納方法から解説する。

[MySQL]

原文: InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN « ChriSQL (English)
翻訳依頼者
翻訳者: doublemarket
原著者への翻訳報告: 未報告

今日、EXPLAINの結果を色々と試してみている時に、興味深い問題にぶち当たったので、ドキュメントには載っていないこの現象をここで共有しておこう。とても単純なInnoDBのテーブルを考えるところから始めよう。2つのINT型のカラムを持ち、最初のカラムがプライマリキーで、2番目のカラムに普通のインデックスが張ってある。

CREATE TABLE `t1` (
  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB;

クエリはこれだ。

SELECT id1 FROM t1;

WHERE句を持たない、何のひねりもないクエリだ。 WHERE句がないと、フルテーブルスキャン、あるいはフルインデックススキャンが行われることが知られている。EXPLAINの結果を見てみよう。

mysql> EXPLAIN SELECT id1 FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id2
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index

﹁possible_keys﹂がNULLになっている。これは、関係しているインデックスが存在しないという意味だ(しかし、キーが使われているとも表示されている)。 possible_keysに表示されていなくても、インデックスがkeyに挙げられることはあり得えます。これは、possible_keysに挙げられるインデックスが、どれも行を見つけるのに適していないにもかかわらず、別のインデックスによってクエリが要求する全てのカラムを取得できてしまった場合に起こります。つまり、挙げられたインデックスが指定するカラムをカバーしているけれども、行を取得するのに使われず、行のスキャンよりインデックススキャンの方が効率がよいと判断された場合です。 http://dev.mysql.com/doc/refman/5.6/en/explain-output.html このため、﹁possible_keys﹂がNULLなのに、keyがid2を返している。上の話から考えると、id2はカバリングインデックスだということだ。しかしこれは少々おかしなことだ。直感的にそうなるとは考えづらい。クエリはid1を要求しているのに、EXPLAINの結果によるとid2がカバリングインデックスだ、と言っているわけだ。こうなってしまったのはどうしてなのか？そして、なぜプライマリキーが選ばれないのだろうか？これは、InnoDBのセカンダリインデックスの保存方法に関係がありそうだ。InnoDBでは、セカンダリインデックスは、対応するプライマリキーの値と一緒に格納されている。そのため、セカンダリインデックスを見る時には、プライマリキーの値も見ることになる。なぜプライマリキーの代わりにセカンダリキーが選ばれるかというと、より深く調査が必要だったが、ソースコード(sql/sql_select.cc)のfind_shortest_key()関数にぴったりのコメントがあることに気付いた。 1) クラスタ化されたプライマリキーのデータセットが、全レコードのフィールド(インデックスに指定されているか否かを問わない)を持っており、 2) セカンダリインデックスのデータが、そのインデックスのフィールドとプライマリキーのフィールドの和集合である(少なくともInnoDBとその派生ストレージエンジンでは、プライマリキーとセカンダリキーが共通の部分集合を持っていたとしても、プライマリキーの重複フィールドを許容しない) これらの条件がそろった時、セカンダリインデックスのデータは、常にプライマリキーのエントリの部分集合である。残念ながら、key_info[nr].key_lengthは、インデックスのフィールド長の合計であって、インデックスとポインタのペアの長さではないので、セカンダリインデックスとプライマリキーのkey_length値を比較して、IOの量を推測することまではできない。従って、セカンダリキーをまず使ってみて、カバリングインデックスになっているセカンダリインデックスが使えないか、全てのフィールドを含むセカンダリインデックス(つまりプライマリキーと同じ)がない時に限って、プライマリキーを使うように。つまり、セカンダリインデックスのエントリのデータは、常にプライマリキーのエントリの部分集合なので、プライマリキーをスキャンするより、セカンダリインデックスをスキャンする方が、若干でもIOが少なくなるはずなのだ。﹁USE INDEX (プライマリキー)﹂をつけて、セカンダリインデックスを強制的に使うようにした時も、key_lenが短くなってしまうことがあるのも、同様に説明ができる。

mysql> EXPLAIN SELECT id1 FROM t1 USE INDEX (Primary)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index

上のEXPLAINの結果だとkey_lenが5だったのが、ここでは4になっているのが分かるだろう。

次の記事

InnoDBのプライマリキーとセカンダリキー

MySQL Utilitiesでフェイルオーバ - mysqlrpladmin

MySQLのALTER TABLEをモニタリングする

MySQLのrootパスワードを、無停止でリセットする

MySQLを使ったアプリケーションを作るエンジニアが知るべきMySQLの内部構造とは？

MySQLおよびMariaDBに大きなサイズの行を挿入する