MySQL インデックスによる最適化

MySQLデータベースにおいてのインデックスについてです。

インデックスとはデータテーブルのカラムに対し検索を行う際、データの並び替えを行い、検索の高速化をはかり、データ抽出においてのパフォーマンスアップを実現してくれるものです。

インデックスの有無は、検索にかかる時間に数秒の開きが生じるくらい、その差は顕著に表れます。

当然ながら検索に時間がかかればページリロードも遅れ、ユーザビリティの低下はおろか、検索エンジンクロール、あるいは評価そのものにまで影響を与えかねません。

効率よくインデックス化をはかり、データの有効活用を実現するための手順をご紹介します。

まずはインデックス化が行われているかの確認を行いましょう。

インデックスの確認

SHOW INDEX FROM テーブル名;

上記コマンドを実行すると設定されているインデックス名を参照することができます。
「Key_name」のカラムにインデックス名が表示されます。

インデックス効果が発揮されているかの確認

explain select * from テーブル名 where インデックスを持つカラム名="値";

「type」と「rows」の値を確認します。

「type」の値について
const: 最速
ref: 早い
INDEX: フルインデックススキャンなので遅い
ALL: 全検索なので遅くインデックス効果なし

「ALL」を除いては、とりあえず「key」に表示されるインデックスを使った検索には成功しています。

「rows」はデータ抽出を行ううえにおいて何レコードを検索したかで、この値が「1」もしくはインデックス化を行う前より減少していれば間違いなくインデックス効果は発揮されています。

インデックスの作成

ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);

可変長カラムのインデックス化
カラムのデータ型が「text」や「blob」の場合、キー長を指定しないとインデックス化は行われません。
可変長カラムの場合はキー長を指定したうえでインデックス化を行います。

ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名(255));

マルチカラムインデックス(複合インデックス)
複数のカラムに検索をかける場合、複合インデックスが効果的です。
気をつける点としては、カラム1、カラム2、カラム3の三つのカラムをまとめて複合インデックス化したばあいの検索順で、下記のSQL文で指定するカラムの並び順どおりに検索をかけないとインデックス効果はないという点です。
カラム1のみに検索をかけた場合はインデックス効果はありますが、カラム1とカラム3、あるいはカラム2とカラム3のみに検索をかけた場合もインデックス効果は発揮されません。

複合インデックスの作成

CREATE INDEX インデックス名 ON テーブル名 (カラム1, カラム2, カラム3);

あるいは

ALTER TABLE テーブル名 ADD INDEX インデックス名 (カラム1, カラム2, カラム3);

インデックス削除

DROP INDEX インデックス名 ON テーブル名;

あるいは

ALTER TABLE テーブル名 DROP インデックス名;