MySQLでインデックスを貼る時に読みたいページまとめ(初心者向け）

#書いてあること
インデックス…?INDEXって何それ(ﾟДﾟ)？レベルの人が
最低限、MySQLのクエリチューニングなどできるようになるための取っ掛かり／リンク集です。

初心者が取っ掛かりにする程度（あるいはそれ以下）にしか書いてないので、
深掘りしたい場合は、各リンクを読むことを推奨します。

INDEXって？

超ざっくりいうと

作るとデータ参照が速くなるやつ
大量のレコードが入っているテーブルから1行のレコードを検索するのに
頭から順番に検索したら時間がかかります。
INDEXを作成すると、データテーブルとは別に検索用に最適化された状態でデータが保存されます。
このINDEXを使うことで、目的のレコードを迅速に見つけて取り出すことが可能になります。

※デメリットもあるので注意！

---- 2018.03.16 追記(コメントにて指摘いただいた内容を転写) ----

MySQL 5.6 で index condition pushdown という最適化戦略が導入されており，以前のよう> に covering index のためにインデックスを貼りまくるというのが必ずしも正解とは言えなくなっているので，一言追記しておいたほうがいいと思います。

MySQL 5.6で追加されたICPを追ってみました。

INDEXの効果

• MySQLのインデックスの効果を理解する

INDEXについてちゃんと理解するために

• MySQL初級者を脱するために勉強してること -INDEX編-
• [MySQLパフォーマンスチューニングのためのインデックスの基礎知識]
  (http://kiyotakakubo.hatenablog.com/entry/20101117/1289952549)

INDEXの種類など更に理解するために

• [知って得するInnoDBセカンダリインデックス活用術！]
  (http://nippondanji.blogspot.jp/2010/10/innodb.html)
• [MySQLでインデックスを使って高速化するならCovering Indexが使えそう]
  (http://blog.livedoor.jp/sasata299/archives/51336006.html)
• [2011-12-17 INDEX FULL SCANを狙う - MySQL Casual Advent]
  (http://d.hatena.ne.jp/sh2/20111217)

どのようにINDEXを貼ればいいのか？

INDEXはDBのパフォーマンスチューニングの際に設定したり、見直します。
基本的にはデータ構造に対してというより
そのテーブルに走るSQLに対応してINDEXは設定します。
というわけで、SQLのパフォーマンスを見ながらINDEXを貼りましょう。

パフォーマンスの見方(EXPLAINの使い方)

SELECT文の先頭にEXPLAINをつけて実行するとそのクエリーパフォーマンスが分かります。
こんな感じです。

EXPLAIN

EXPLAIN SELECT カラム名1, カラム名2... FROM テーブル名 WHERE 条件...;

EXPLAINでのパフォーマンスの見方

• [MySQLのEXPLAINを徹底解説!!]
  (http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html)
• MySQLのEXPLAIN

補足：INDEXが全く効いてない状態のEXPLAIN

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|:---|:---|:---|:---|:---|:---|:---|:-----|:---|:---|:---|
|1|SIMPLE|a|ALL|NULL|NULL|NULL|NULL|123456|Using where; Using filesort|
|1|SIMPLE|b|ref|hoge_id,fuga_id|hoge_id|5|demo.a.hoge_id|1|Using where|
|1|SIMPLE|c|ref|idx_fuga_id|idx_fuga_id|5|demo.b.fuga_id|1|Using index|

INDEXを貼るためのSQL

• MySQLでインデックスを追加するSQLと削除するSQLを実行しました

indexを追加する基本
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);

※もちろんPhpMyAdminでも設定できます。

INDEXを貼るノウハウ

EXPLAINするのはわかったけどどこから手をつけたら？って言う時は…

• mysqlでindexの貼り方の検討がつかない時にやる事

一応、INDEXを貼るときのメモを書きました

SQLチューニングのノウハウ

• [オトコのソートテクニック2008]
  (http://nippondanji.blogspot.jp/2008/12/2008.html)
• [6.2.1. EXPLAINを使用して、クエリを最適化する]
  (http://mysql.stu.edu.tw/doc/refman/5.1/ja/explain.html)

時間があるときに読もう

• [MySQLをさらに理解するために読んだ記事まとめ]
  (http://shibayu36.hatenablog.com/entry/2012/06/02/210848)
• [MySQLノウハウ(リンク切れしてたのでwebページアーカイブから）]
  (https://web.archive.org/web/20150416222432/http://txqz.net/blog/2006/12/13/0943)

チューニングする際のメモ

※あくまでメモ！
チューニング方針は、人（組織）それぞれあるので一概には言えません。

INDEXがちゃんと出来てるかの見方

• using where using index で出来ればOK
• Explainでindexが出てきていれば　Covering index成功　よく出来てる

※非効率なINDEXが使われているケースもある

• keyのところに書かれているのがINDEX名
• どのINDEXが使われているのか意識すること！
• INDEXが効いていても件数が多い場合(rowsがx万件とか)はあまり効果が出ていないので気をつける

例）INDEXが効いているけどパフォーマンスが良くない状態のEXPLAIN（赤字:5万件以上row）

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|:---|:---|:---|:---|:---|:---|:---|:-----|:---|:---|:---|
|1|SIMPLE|a|ref|idx_d|idx_d|18|const|54321|Using index condition; Using where|
|1|SIMPLE|b|ref|hoge_id,fuga_id|fuga_id|5|demo.a.fuga_id|1|Using where|
|1|SIMPLE|c|ref|idx_hoge_id|idx_hoge_id|5|demo.b.hoge_id|1|Using index|

INDEXを貼るコツ

• 複合INDEXを貼るときは　極力UNIQUEになるように（なったらいいな）
• selectで使わない項目でも（参照しなくても)where句で使うなら含める方がいい
• より絞り込める順番でINDEX貼ること

INDEXを貼るときの注意点

• 大きいデータではINDEXは設定した方が良い
• とはいえ、データ量が極端に多いテーブルに無闇にINDEXを貼ると
  更新時に大変なことになる＆サーバー容量を圧迫するのでよーーく考えること
• データが1000件未満の時は、INDEXを貼っても使用されないことがある
• 更新頻度が多いものは気をつける（あまりはらない）
• 問題がなければ無理してINDEXは貼らない
  
  ※それぞれの方針にもよるので一概には言えない
• INDEXを作成するだけではなく、場合によってはカラムの型の見直し,SQLチューニングも合わせてやる

注意！

• INDEXが効かないケースもちゃんと把握する必要がある