MySQL InnoDB のインデックスについて再入門

アプリケーション開発する上で、MySQL をよく使っているけれど DB設計やパフォーマンスチューニングを行う際にインデックスについてふわっとした理解だったので再入門した。

インデックス

DB から特定の条件でデータを検索する時に効率よく、条件に一致するデータを取得できるようにする仕組みである。

インデックスが効いていないクエリを投げた場合、フルテーブルスキャンが行われ条件に合ったデータを探索するため、非常に検索に時間がかかってしまう。
InnoDB のインデックスは B+Tree というアルゴリズムで実装されている。

B+Tree に関しては cookpad さんのブログの記事が大変オススメ。
MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ

クラスタインデックス

1. 主にプライマリキー（の別名）が定義されている要素を指す
2. プライマリキーの定義がない場合、列の全てが NOT NULL の UNIQUE なカラムをクラスタインデックスとする
3. それでも存在しない場合は、内部的に6バイトのフィールドを作成してクラスタインデックスを作成する

クラスタインデックスで検索を行うと、内部的には一致したクラスタインデックス以外のデータも取得できるため、高速に効率よくアクセスすることができる。

CREATE TABLE `t1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `t2_id` int(10) NOT NULL,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_t2_id` (`t2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT id, name FROM t1 WHERE id = 100;

このようなSELECT文を発行すると、内部的には、t1テーブルのクラスタインデックスである id に一致したレコードのデータ全てを1度で取得でき IO を抑えることができる。
したがって、後述する セカンダリインデックス より基本的には高速にアクセスすることができる。

セカンダリインデックス

クラスタインデックス以外のインデックスを指す。

CREATE TABLE `t1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `t2_id` int(10) NOT NULL,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_t2_id` (`t2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT name FROM t1 WHERE t2_id = 100;

クラスタインデックスとは違い、内部的にはセカンダリインデックスに一致したレコードのセカンダリインデックスとクラスタインデックスしか取得することができない。

その為、その他のデータを取得するには内部的に再度クラスタインデックスでの IO が走るので、クラスタインデックスでの検索より速度が劣る。

カバリングインデックス

例を挙げた方がわかりやすいので、先に書く。

CREATE TABLE `t1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `t2_id` int(10) NOT NULL,
  `t3_id` int(10) NOT NULL,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_t2_id_and_t3_id` (`t2_id`, `t3_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT t2_id, t3_id FROM t1 WHERE t2_id = 100;

このテーブルでは、t2_id, t3_id で複合インデックスが張られている。

そこでセカンダリインデックスの t2_id で検索するようなクエリを投げると、t3_id にもインデックスが張られているため、内部的には クラスタインデックスで再度検索が行われず IO を減らしてデータを取得することができる。

このようにインデックスだけで検索が完結することを カバリングインデックス という。

以上まだまだ不足していることもあると思うが、
少なくともこれだけでも意識するだけでパフォーマンスを考えた DB 設計に近づくことができそう。

一応、今回参考にした書籍を紹介すると、

MySQL5.0 に書かれている書籍なので対象バージョンは新しくないが、概念や運用ノウハウなどとても優しく書かれているのでオススメ。
また今でも全然通用するし、運用でもよく行われている技術が書かれている。
適宜アップデートが必要なところは、新しいバージョンの変更点と比較しながら読み進めて行けば良いと思う。

次はトランザクションやロックをまとめたいかな〜。