MySQLのIndexをはるコツ #MySQL

Indexを作成すべき列の特長

ただし全表走査が目的のテーブルであれば索引は不要

IndexはNULLを含まないのでNULL値以外の検索には効果がある

表の規模が小さいか、表から大部分のレコードを検索する場合
WHERE句等の条件としてあまり仕様されないもの
列の値が頻繁に挿入、更新、削除される
- Indexは表に新規行が挿入されればIndexにも自動的に値が格納される。そのためデータの検索速度は向上するが挿入、更新、削除の処理では索引のメンテナンス作業が発生し、速度低下につながる
WHERE句の条件として使用されるが、列が式の一部として参照される

ALTER TABLE landing_pages ADD INDEX index_name(user_id)

ALTER TABLE landing_pages ADD INDEX index_name(user_id, created)

MySQLでは1つのクエリを実行するとき1つのテーブルにつき1つのインデックスしか使用できない。よって、単に2つのインデックスを作成してしまうとどちらか一つを選択する。

マルチカラムインデックスを使えば、複数のカラムにインデックスを使用できる

ALTER TABLE landing_pages ADD UNIQUE(id)

これをつけておくことで、当該カラムにおいて、値が重複して出現しなくなる（NULLは除く）。すなわちレコード挿入、更新時にすべての値を調べて同じ値が既に存在しないことを確認することになる。

ALTER TABLE table_name DROP INDEX index_name;

EXPLAINコマンドを使って調査する。EXPLAINは、「mysqlがどのように判断してクエリを実行したかという情報」

使い方は SELECTの直前に EXPLAIN をつけるだけ！

EXPLAINは大量データが存在する、本番（もしくはステージング）で実行する事が非常に大事。

const・・・PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。
eq_ref・・・JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。
ref・・・ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ。
range・・・インデックスを用いた範囲検索。
index・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
ALL・・・フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。

indexまたはALLを見かけたらすかさずクエリをチューニングしよう。

確認する項目は「possible_keys」「key」「Extra」
- possible_keys: SQLを実行する上で利用可能なINDEXとして候補にあがったINDEX
- key: 実際に選択されたINDEX
- Extra: 追加情報 Using filesortやUsing temporaryが出たら赤信号
Using filesortとUsing temporaryは最悪な組み合わせ
Using filesortはソートに必要な領域がメモリ上に乗り切らずに物理ファイルに書き出しソートを行う。
Using temporaryはクエリを実行するのにテンポラリテーブルが作られる。
リアルタイム処理を行うようなシステムの場合はUsing filesortとUsing temporaryが表示されたら改善必須