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

  • 289
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

書いてあること

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

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

INDEXって?

超ざっくりいうと

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

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

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