MySQLのIndexをはるコツ

Indexを作成すべき列の特長

1. テーブル内のデータ量が多く、少量のレコードを検索する場合

2. WHERE句の条件、結合の条件、ORDER BY句の条件として頻繁に利用する

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

3. NULL値が多いデータから、NULL値以外の検索をする

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

逆に、Indexを作成すべきでない列の特徴

• 表の規模が小さいか、表から大部分のレコードを検索する場合

• WHERE句等の条件としてあまり仕様されないもの

• 列の値が頻繁に挿入、更新、削除される

  • Indexは表に新規行が挿入されればIndexにも自動的に値が格納される。そのためデータの検索速度は向上するが挿入、更新、削除の処理では索引のメンテナンス作業が発生し、速度低下につながる

• WHERE句の条件として使用されるが、列が式の一部として参照される

Indexの付け方

通常

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つのインデックスを作成してしまうとどちらか一つを選択する。

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

（参考）http://blog.xao.jp/blog/mysql/mysql%E3%81%AE%E8%B6%85%E9%81%85%E3%81%84select%E3%81%8C%E5%8A%87%E7%9A%84%E3%81%AB%E6%97%A9%E3%81%8F%E3%81%AA%E3%81%A3%E3%81%9F/

ユニークインデックス

ALTER TABLE landing_pages ADD UNIQUE(id)

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

Indexの削除

ALTER TABLE table_name DROP INDEX index_name;

Indexの使われるとき使われない時

使われるとき

• フィールド値を定数と比較するとき (WHERE name = "hogehoge" )
• フィールド値全体でJOINするとき ( WHERE a.name = b.name )
• フィールド値の範囲を求める時 >,
• LIKEで文字列の先頭が固定な時
• MIN(), MAX() (複数要素indexの同一first fieldでsecond fieldのmin,max でも有効)
• 文字列のプレフィックスをもとにしたORDER BY, GROUP BY
• WHEREのすべてのフィールドがindexの一部の場合 (DBまったく参照されず)

使われない時

• LIKEがワイルドカードで始まる時
• DB全体を読んだ方が早いとMySQLが判断した時
• 通常はindexはORDER BYには使われない
• WHERE と ORDER BYのフィールドが違う時にはどちらかしか使われない

Indexが利用されているか調査

EXPLAINとは

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

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

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

見方

typeを確認

• 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が表示されたら改善必須

（参考）MySQLでクエリチューニングことはじめ
（参考）漢のコンピュータ道
（参考）MySQL初級者を脱するために勉強してること -INDEX編-MySQL初級者を脱するために勉強してること -INDEX編-

注意点

• Indexを使用すると、検索性能は向上するが、容量が犠牲になる