インデックスの基礎知識

インデックスについて一言で説明するのは難しいので、まずはMySQLが目的のデータをどのようにして見つけるのかを説明します。MySQLに次のSQLクエリを発行したとします。

SELECT * FROM users WHERE name = 'foo';

これはnameというカラムに'foo'という値を持つデータを取得するSQLクエリです。このSQLクエリを発行したとき、まずMySQLはusersテーブル内の全てのデータを取得します。つぎに、取得したデータを1件ずつ順番に見ていき、nameが'foo'であるデータだけを抽出します。最後に、抽出したデータを結果として返します。これがMySQLが目的のデータを見つけるときの動作の流れになります。

途中で「テーブル内の全てのデータを1件ずつ順番に見ていき、条件に合うデータだけを抽出する」という動作が行われましたが、この動作のことを「フルテーブルスキャン」と呼びます。検索対象のテーブルのデータ数が少なければ、フルテーブルスキャンにかかる時間は短いですが、データ数が多くなればなるほど、フルテーブルスキャンにかかる時間は長くなっていきます。仮に1件のデータの値を確認するのに1秒かかるとすると、10,000件のデータから目的のデータを抽出するのには、単純計算で10,000秒かかることになります。アプリを運用していけばデータは徐々に増えていきますので、抽出にかかる時間はさらに長くなることが予想されます。

この問題を解決するために、データを保存したときに「指定したカラムの値」と「そのデータの保存位置情報」を本の索引のような構造で保存しておき、目的のデータを抽出するときにその索引を使って探すことができる仕組みがデータベースに用意されました。この索引のことを「インデックス」と呼びます。インデックスを使うことで、フルテーブルスキャンを行うよりも早くデータを抽出することができます。インデックスは下記のSQLクエリで定義することができます。

ALTER TABLE users ADD INDEX(name);

検索時にインデックスが使われているかはSQLのEXPLAIN句で調べることができます。

EXPLAIN SELECT * FROM users WHERE name = 'foo';

結果がこのように出力されます。

keyの項目が検索に使われたインデックス名になります。ここがNULLでなければ、インデックスが正しく使われていると考えて良さそうです。

インデックスには様々な種類があります。インデックスの種類によって、インデックスを使うことができる状況や、検索にかかる時間が変わります。先ほど紹介したALTER TABLE構文ではインデックスの種類を指定していませんでしたので、MySQLのInnoDBストレージエンジンのデフォルトである「Bツリーインデックス」という種類のインデックスが作られました。Bツリーインデックスの構造を説明するにはいくつかの前提知識が必要ですので、ここでは触れないことにします。

複合インデックス

下記のように記述することで、複数のカラムの値を組み合わせたインデックスを作ることできます。

ALTER TABLE users ADD INDEX(last_name, first_name);

このようなインデックスを「複合インデックス」と呼びます。複合インデックスは、ADD INDEXの中で指定したカラムの順番に従って、キーが作られます。last_nameが「まさや」、first_nameが「さとう」でデータベースに登録した場合、このデータのキーは「まさや-さとう」になります。インデックスが使われるかどうかに、カラムの順番が影響することがありますので、この仕様は覚えておいたほうが良いと思います。

ユニークインデックス

通常のインデックスは主にデータの検索時に使われますが、データの登録時に値がユニークであるかを確認するためにも使われるインデックスがあります。それが「ユニークインデックス」です。ユニークインデックスは下記のように記述することで作ることができます。

ALTER TABLE users ADD UNIQUE(first_name, last_name);

ユニークインデックスでは、キーが同じになるデータを登録したときにエラーが発生します。

INSERT INTO `users` (`id`, `last_name`, `first_name`) VALUES ('2', 'まさや', 'さとう');
/* => Duplicate entry 'まさや-さとう' for key 'last_name' */

Bツリーインデックスを使うことができる状況

作ったインデックスは使われなければ意味がありません。どのようなクエリを発行したときにインデックスが使われるのか、まとめていきます。例として、下記の構造のテーブルを使います。

CREATE TABLE users (
  id VARCHAR(50) PRIMARY KEY,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  gender enum('male', 'female') NOT NULL,
  key(last_name, first_name)
);

基本は、キーの前方部分に一致する条件で検索を行ったときにインデックスが使われます。インデックスが使われる具体的なケースをいくつか紹介します。

SELECT * FROM users WHERE last_name = 'まさや' AND first_name = 'かとう';

SELECT * FROM users WHERE last_name = 'まさや';

/* インデックスは使われない */
SELECT * FROM users WHERE first_name = 'かとう';

SELECT * FROM users WHERE last_name LIKE 'まさ%';

/* インデックスは使われない */
SELECT * FROM users WHERE last_name LIKE '%や';

SELECT * FROM users WHERE last_name BETWEEN 'あきら' AND 'みつる';

SELECT * FROM users GROUP BY last_name;

まとめ

• インデックスとは、MySQLが目的のデータを効率的に探すために作られたデータ構造
• インデックスが使われない場合は全てのデータを一つ一つ確認していく「フルテーブルスキャン」が行われる
• インデックスが使われているか確認したいときは、EXPLAIN句を使ってkeyの値を見る
• 複数のカラムを組み合わせたインデックスを作成できる（複合インデックス）
• 複合インデックスを作るときは、カラムを指定する順番が重要になる
• ユニークインデックスを使うことで、データの登録時に対象の値がユニークであるかを確認することができる
• Bツリーインデックスを使っていて、キーの前方に一致する検索を行ったときは、インデックスは使われる
• Bツリーインデックスを使っていて、キーの後方に一致する検索を行ったときは、インデックスは使われない