MySQLのインデックス

インデックスとは何か

テーブルの中身を検索するときに、任意のカラムの値で検索しやすいようにしておくための機能です。

たとえばnameというカラムを持つメンバーリストのテーブルがあるとします。
それはid順では並んでいますが、name順では並んでいません。

where name=Yamadaでselectすると、テーブル内の行全てを順に辿っていって、条件に合致するものを抽出していきます。テーブルに保存されている行数が多いほど、無駄な捜索が生まれることは想像できます。

nameに対してインデックスを指定すると、idとnameをカラムとして持つインデックステーブルが作成されます。しかもこのテーブルはnameの昇順に並んでいるので、where name=Yamada で検索した場合、先ほどよりも無駄な捜索がグッと減る可能性が高いです。

ただし、インデックスを指定するということは、インデックステーブルを作成する、ということであり、元のテーブルへの追加・更新・削除をする場合には、インデックステーブルにも同じ作業をする必要があります。インデックスの数が増えるだけ、その作業は増えていきます。

なので、使う可能性や頻度が低いようなインデックスをむやみやたらに指定することは避けたほうがいいでしょう。

データアクセスの仕組み

MySQLでは、SQLが実行されたあと、次のような手順で、データにアクセスされます。

SQLの実行
データベースエンジンへアクセス
ストレージエンジンへアクセス

データベースエンジンでは、オプティマイザと呼ばれる（プランナとも呼ばれる）機能がSQLをパースします。
実行されたSQLが、SelectなのかUpdateなのかなどの命令の解析、インデックスが指定されているか、指定されている場合はそのインデックスを使用したほうがいいのか、またテーブルの結合方法を決めます。いわゆる「実行計画」です。

解析されたSQLを元にストレージエンジンへアクセスします。
ストレージエンジンは、テーブルをどのようなファイル形式で保存しているのか、という情報を持っています。

テーブルの情報は、１つのファイルで構成されているとは限りません。サイズなどによって複数ファイルに分割されて保存されていることもあります。テーブルの情報にアクセスするには、そのテーブルがどのように、そしてどこに保存されているかを知る必要があります。その情報を提供する役目を果たすのがストレージエンジンです。

ストレージエンジンにはいくつか種類がありますが、MySQLではテーブルごとにストレージエンジンを指定することができます。Create時のENGINE句、またはALTER文でストレージエンジンを変更することができます。

ストレージエンジンを明示的に指定することは稀かもしれません。指定をしなくてもデフォルトで指定されます。MySQL5.5以降では、デフォルトに指定されるのはInnoDBです（5.4以前ではMyISAM）。

ストレージエンジンにはそれぞれ特性があり、アプリケーションの要件に応じて適切なストレージエンジンを選択することで、パフォーマンスを向上できる可能性があります。

MySQLによってデフォルトに指定されるInnoDBにも優れた点があり、またどのようにSQL文を発行すると最大限に活用できるのかが、こちらにまとめられています。

MySQLでインデックスを使う

インデックスを使うには、SQL上で下記のkeyを指定します。単独でも、組み合わせてもOKです。

key
primary key
unique key
foreign key

primary keyは主キーを指定します。uniquey keyに指定された組み合わせは、そのテーブル内で重複ができません。foreign keyは外部制約キーです。deleteなどの指定によって、参照されている親テーブルのカラムの同じデータを削除することができなくなったりします。

たとえば、a、b、cの3つのカラムがあるテーブルで、次のようなkeyが指定されている場合、

key: b
unique key: a、b

where句を次のように指定すればインデックスの効果が発揮されます。

where a = 1 and b = 1

where a = 1

where b = 1

keyは順序が大切なので、下記に指定した場合インデックスは効きません。

where b = 1 and a = 1

keyの指定が下記である場合、

unique key: a、b

下記はインデックスが効きません。

where b = 1

MySQL Explain

指定したIndexが効いているかなど、SQLのチューニングをする上で役に立つのが、explainです。

実行したいSQL文の最初に explain と記述するだけです。

$ explain select * from table_a where a = 1;

explain結果のチェックポイントは下記3つです。

id、select_type、tableフィールド
- どのテーブルが、どの順序でアクセスしたかを知ることができます。
type、key、ref、rowsフィールド
- 各テーブルからどのようにレコードが取得されたかがわかります。
- どのテーブルへのアクセスがもっとも重いかがわかります。
Extraフィールド
- オプティマイザの挙動を確認することができます。
- それぞれのテーブルがなぜその順序でアクセスされているかがわかります。
- explain結果の全体見通しを立てるため、最初に確認すると良いでしょう。

各フィールドにはそれぞれ決められた値が存在するので、explainの実行結果に応じて調べ、覚えていくのが良いかと思います。
こちらの記事が有名です。

インデックスがなぜ重要か

インデックスを効果的に使うことができれば、１つのクエリにかかるコストを抑えることができます。ストレージエンジンの性能には限界があります。ストレージエンジンの1秒あたりに処理できる行数が100万行である場合、仮に1つのクエリが100行取得するとすると、1秒間に最大1万回のクエリを実行できます。

限られた時間内での最大可能実行数は、アプリケーションレベルによって重要度が変わりますが、1回のクエリにかかる時間はどのようなレベルのアプリケーションであっても人が体感できる場合にはできるだけ速いほうがいいです。

MySQLのクエリ実行を速くするためにできることは、こちらでも紹介されている通り、クエリのチューニングだけではありません。しかし、最低限無駄のないクエリを記述しておくこと、テーブルを設計する際にクエリを実行しやすいかなどをよく考えることは大切かと思います。