書いてあること
インデックス…?INDEXって何それ(゚Д゚)?レベルの人が
最低限、MySQLのクエリチューニングなどできるようになるための取っ掛かり/リンク集です。
初心者が取っ掛かりにする程度(あるいはそれ以下)にしか書いてないので、
深掘りしたい場合は、各リンクを読むことを推奨します。
INDEXって?
超ざっくりいうと
作るとデータ参照が速くなるやつ
大量のレコードが入っているテーブルから1行のレコードを検索するのに
頭から順番に検索したら時間がかかります。
INDEXを作成すると、データテーブルとは別に検索用に最適化された状態でデータが保存されます。
このINDEXを使うことで、目的のレコードを迅速に見つけて取り出すことが可能になります。
※デメリットもあるので注意!
---- 2018.03.16 追記(コメントにて指摘いただいた内容を転写) ----
MySQL 5.6 で index condition pushdown という最適化戦略が導入されており,以前のよう> に covering index のためにインデックスを貼りまくるというのが必ずしも正解とは言えなくなっているので,一言追記しておいたほうがいいと思います。
INDEXの効果
INDEXについてちゃんと理解するために
INDEXの種類など更に理解するために
- 知って得するInnoDBセカンダリインデックス活用術!
- MySQLでインデックスを使って高速化するならCovering Indexが使えそう
- 2011-12-17 INDEX FULL SCANを狙う - MySQL Casual Advent
どのようにINDEXを貼ればいいのか?
INDEXはDBのパフォーマンスチューニングの際に設定したり、見直します。
基本的にはデータ構造に対してというより
そのテーブルに走るSQLに対応してINDEXは設定します。
というわけで、SQLのパフォーマンスを見ながらINDEXを貼りましょう。
パフォーマンスの見方(EXPLAINの使い方)
SELECT文の先頭に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するのはわかったけどどこから手をつけたら?って言う時は…
SQLチューニングのノウハウ
時間があるときに読もう
チューニングする際のメモ
※あくまでメモ!
チューニング方針は、人(組織)それぞれあるので一概には言えません。
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が効かないケースもちゃんと把握する必要がある