search
LoginSignup
889

More than 3 years have passed since last update.

posted at

updated at

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

書いてあること

インデックス…?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が効かないケースもちゃんと把握する必要がある

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
889