MySQLのインデックスの使い方について、サンプルSQLを紹介しながらまとめています。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。
MySQLのインデックス作成方法
MySQLのインデックス作成の構文は以下のとおりです。
ALTER TABLE employees ADD INDEX `birth_date_index` (`birth_date`);
上記のSQLは、employees（社員）テーブルのbirth_date（誕生日）カラムにbirth_date_indexという名前でインデックスを設定します。
適切にインデックスを設定することによって、テーブルの検索やソートのスピードが速くなります。
SELECT first_name, last_name, birth_date FROM employees a WHERE birth_date = ( select min(birth_date) from employees b )
上記は、employees（社員）テーブルから、誕生日が最小＝最高齢の社員のfirst_name（姓名の名）、last_name（姓名の姓）、birth_date（誕生日）を取得するSQLです。
実行するとこうなります。
実行結果
インデックスを指定していない場合は「6 total, Query took 0.1616 seconds.」と表示されますが、上記インデックスを設定すると「6 total, Query took 0.0004 seconds.」となりました。
404倍の速度差が出ています。
インデックスの種類
MySQLではPRIMARY、UNIQUE、INDEX、FULLTEXT、SPATIALの5種類のインデックスが設定可能です。
SQL内の「ADD INDEX」の部分を「ADD UNIQUE」などに差し替えることでインデックス種類の指定ができます。
PRIMARYは、プライマリキーのインデックスです。プライマリキー指定をすると自動的に設定されます。
UNIQUEは、ユニークキー、外部キー用のインデックスです。
INDEXは一般のインデックスです。基本は「ADD INDEX」でインデックス指定すると良いでしょう。
FULLTEXTは、全文検索用のインデックスです。不定長のtextカラムに設定すると、全文検索の速度が大幅に改善されますが、インデックスに使用する領域を多く使うという欠点もあります。
SPATIALは、空間データ用のインデックスです。空間データとは、都市や山などの立体情報や町や市などの範囲情報で、これらを格納するのに向いている空間データ型に対するのが空間インデックスです。MySQLでは、空間データのソリューション開発に関わる 250 以上の企業、機関、および大学の国際的なコンソーシアムOpen Geospatial Consortium (OGC) の仕様に沿って、空間データが実装されています。
MySQLに設定済みのインデックスを確認するには
show index from employees
実行するとこうなります。
実行結果
一見してもインデックスのタイプがわからないので、インデックス名（Key_name）にインデックス種類がわかるような命名規則で名前をつけるか、コメントに記載するのが良いでしょう。
MySQLのインデックスの使い方
複数のインデックスを設定するには
下記の要領で複数カラムにインデックスを指定できます。
ALTER TABLE `employees` ADD INDEX `birth_hire_index` (`birth_date`, `hire_date`);
あ上記SQLは、employees（社員）テーブルに、birth_date、hire_dateの順でbirth_hire_indexという名前をインデックスを設定するSQLです。
インデックスの再構築方法
照合順序の修正をおこなった場合など、インデックスを再設定するには、単純にインデックスをドロップしてから再作成します。
あまりにもインデックスの数が多すぎて手動で対応しきれない場合は、データベースをリロードする方法もあります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 2.11.4 テーブルまたはインデックスの再作成または修復
コマンドラインから以下を実行します。
mysqldump データベース名 > dump.sql mysql データベース名 < dump.sql
いったんデータベースを全削除してから、データを含めて再作成するため、リロード中はデータベースへのアクセスはできなくなります。
データベースサイズが大きすぎるとファイルにダンプすることができないケースがあるため、比較的サイズが小さめのデータベースに対しては有効です。
mysqldumpに-dオプションを指定すると、構造情報のみがダンプできるため、ダンプ内容のうちインデックス部分のみを編集して、インデックス再生成用スクリプトにする方法もあります。
インデックスを削除するには
以下の構文で、インデックスを削除できます。
ALTER TABLE employees DROP INDEX firstname_index;`
上記SQLは、employees（社員）テーブルから、firstname_indexという名前のインデックスをドロップします。
インデックスが効いているかどうか確認するには
「インデックスを設定したはずなのに、SQLが遅い…」という場合は、せっかく設定したインデックスが使われていないケースがあります。
explainを実行して、インデックスが使われているかどうかを確認しましょう。SQLの先頭に「explain」をつけて実行するだけです。
explain SELECT first_name, last_name, birth_date FROM employees a WHERE birth_date = ( select min(birth_date) from employees b )
実行すると、こうなります。
メインクエリ（select_type=PRIMARY）の検索タイプはref（参照型）で、使用可能なキー（possible_keys）はbirth_date_index、最適化の結果使用されたキー（key）はbirth_date_indexということがわかります。サブクエリのExtraに表示されている「Select tables optimized away」は、「最適化するまでもない」となります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット
簡易的な見方としては「type」が「ALL」になっていたら、テーブルフルスキャンでインデックスが使われていない遅いクエリなので、見直しが必要だと覚えておくと良いでしょう。
まとめ
- インデックスを設定すると、クエリによっては検索速度が大幅に改善する
- インデックスは5種類ある。一般のインデックスには「ADD INDEX」を使用する
- 設定済みインデックスは、show index from テーブル名で確認できる
- インデックスの再構築は、データベースのダンプを利用する
- インデックスが有効に働いているかどうかは、explainで確認可能
