(2015年までの)odaillyjp blog

イベント参加記録とプログラミング系の雑記

MySQLのインデックスについて学びました

前回の記事に続き、MySQLに関するネタを書きます。今回の内容のほとんどは『実践ハイパフォーマンスMySQL』の読みながらまとめた内容になりますので、より詳しく知りたい方はそちらをご一読ください。

実践ハイパフォーマンスMySQL 第3版

実践ハイパフォーマンスMySQL 第3版

確認したMySQLのバージョン: 5.6.22

インデックスの基礎知識

インデックスについて一言で説明するのは難しいので、まずは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';

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

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users ref name name 767 const 1 Using index condition


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

インデックスには様々な種類があります。インデックスの種類によって、インデックスを使うことができる状況や、検索にかかる時間が変わります。先ほど紹介したALTER TABLE構文ではインデックスの種類を指定していませんでしたので、MySQLInnoDBストレージエンジンのデフォルトである「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)
);

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

キーと完全一致する検索

last_nameとfirst_nameの2つの条件に完全一致する検索では、インデックスが使わます。

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

キーの先頭に指定したカラムでの検索

今回は「last_name、first_name」の順番で複合インデックスを作成しています。1番目に記述したlast_nameだけでの検索であれば、このインデックスを使うことはできます。

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

しかし、2番目に記述したfirst_nameだけでの検索では、このインデックスを使うことはできません。

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

先ほど「インデックスが使われるかどうかに、カラムの順番が影響することがある」と言いましたが、このような場面のときに影響するということです。

キーの前方に一致する検索

LIKEを使って前方に一致する検索を行ったときもインデックスを使うことはできます。

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

しかし、後方に一致する検索ではインデックスを使うことはできません。

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

範囲を指定した検索

BETWEEN句などを使った範囲を条件にした検索でもインデックスは使うことができます。

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

上記のSQLクエリのように文字列の範囲検索を行うことは少ないと思いますが、数値の範囲検索を行うことは多いと思います。そのときは対象のカラムのインデックスを作成しておくと良さそう。

GROUP BY を使った検索

対象のカラムをグルーピングした検索でも、インデックスを使うことができます。

SELECT * FROM users GROUP BY last_name;

その他

他にも ORDER BY を使った検索でもインデックスが使われることがあるようです。(必ず使われるというわけではなく、いくつか制約があるようですので、今回は紹介できませんでした。)

まとめ

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