MySqlのインデックスについて
インデックスってレコードに対して情報が追加されてちょっと早くなるやつってくらいの認識だったので、
インデックスが適用される条件など調べてみる
テーブルあたりの最大インデックス数とインデックスの最大長は、ストレージエンジンごとに定義されます。
(MySqlはinnoDBというデータベースエンジン)
メリットとデメリット
WHERE句によるレコードの絞り込みが早くなる。
SELECTに限らず更新や、削除処理で対象のレコードを見つける(WHEREで絞り込む)速度が速くなる。
が、更新削除処理そのものは遅くなる。
INSERT, UPDATE ,DELETEのコストが増える
また、インデックスによる速度低下の影響を受けないor少なくすることも出来る
- UPDATE
- インデックスが張られているカラムを更新した際に、インデックスも更新されるので遅くなる
- 速度は、更新するデータ量とインデックス数に影響する
- データの変更のないインデックスは更新されない（のでインデックスによる速度の低下の影響はない)
- DELETE
- レコードの削除に伴いテーブル全体のインデックスの更新が行われるため遅い
- 削除に必要な時間は、インデックスの数に正確に比例する
- INSERT
- レコードの挿入時にインデックスの挿入の動作も増えるため遅くなる
- 1つのINSERT分で複数のVALUESリストを用いてまとめて挿入処理を実行することで早くなる。
- 個別で単一のINSERT分を複数実行するより大幅に早くなる(場合によっては数倍)
インデックスの種類と使い方
プレフィックスインデックス
// インデックス追加
ALTER TABLE HOGE ADD INDEX idx_hoge(first_name(5));
インデックスの指定する際に[col_name(4)]のように指定することで、
文字列カラムの先頭から指定した文字のみを使用するインデックスを作成でき、インデックスファイルを小さくすることができる。
BlobやTextカラムにインデックスを指定する場合はプレフィックス長を指定する必要があるとのこと。
ユニークインデックス
// 単一ユニークインデックス追加
ALTER TABLE hoge ADD UNIQUE (first_name);
// 複合ユニークインデックス追加
ALTER TABLE hoge ADD UNIQUE (first_name, last_name);
一意制約付きのインデックスです。
指定したカラム、または複数のカラムの組み合わせで一意となり、データの重複ができないようになる。
マルチカラム(複合)インデックス
// 複合インデックス追加
ALTER TABLE hoge ADD INDEX idx_huga (first_name, last_name, age);
- 複合インデックスが働くパターンは複数ある
(複合インデックスに指定したカラムを全て検索条件にいれないと動かないと思っていた)
- 指定したカラムのプレフィックスの一番左から順に検索条件に含まれていればインデックスが活用される
例:(fist_name, last_name, age)と3つのカラムを指定し複合インデックスを設定した場合
1. select * from where first_name = 'hoge'
2. select * from where first_name = 'hoge' AND last_name = 'huga'
3. select * from where first_name = 'hoge' AND last_name = 'huga' and age = 30
インデックスが使用される条件
- 対象のカラムと定数を比較するとき
- 比較演算子[=、>、>=、<、<=]
- BETWEEN句や、IN句もあてはまる
- LIKEに関しては、先頭がワイルドカード文字から始まらない定数文字列であること
- JOINするとき
- WHERE a.hoge = b.hoge
- 他にもたくさんあります。。。
インデックスが使用されない場合
- ORDER BY
- ASCとDESCを混在させる etc..
- MySqlがフルテーブルスキャンをしたほうが早いと判断したとき
- テーブル全体のレコード数の30%以上をフェッチするとき等
- LIKEの比較の頭にワイルドカードが使われたとき
- 他にも...
以上です。
基本的にMySQL 5.6 リファレンスマニュアルを参考にしました。
参考
MySQL 5.6 リファレンスマニュアル / 最適化 / 最適化とインデックス
MySQL 5.6 リファレンスマニュアル / 最適化 / SQL ステートメントの最適化