Edited at

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

More than 1 year has passed since last update.


書いてあること

インデックス…?INDEXって何それ(゚Д゚)?レベルの人が

最低限、MySQLのクエリチューニングなどできるようになるための取っ掛かり/リンク集です。

初心者が取っ掛かりにする程度(あるいはそれ以下)にしか書いてないので、

深掘りしたい場合は、各リンクを読むことを推奨します。


INDEXって?


超ざっくりいうと

作るとデータ参照が速くなるやつ

大量のレコードが入っているテーブルから1行のレコードを検索するのに

頭から順番に検索したら時間がかかります。

INDEXを作成すると、データテーブルとは別に検索用に最適化された状態でデータが保存されます。

このINDEXを使うことで、目的のレコードを迅速に見つけて取り出すことが可能になります。

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

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


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


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




INDEXの効果


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


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


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

INDEXはDBのパフォーマンスチューニングの際に設定したり、見直します。

基本的にはデータ構造に対してというより

そのテーブルに走るSQLに対応してINDEXは設定します。

というわけで、SQLのパフォーマンスを見ながらINDEXを貼りましょう。


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

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

こんな感じです。


EXPLAIN

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



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


indexを追加する基本

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


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


INDEXを貼るノウハウ

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

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


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


時間があるときに読もう


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

※あくまでメモ!

チューニング方針は、人(組織)それぞれあるので一概には言えません。


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



  • using where using index で出来ればOK


  • Explainindexが出てきていれば 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が効かないケースもちゃんと把握する必要がある