MySQLのインデックス
INDEXとは
- ソート済みのデータの複製(RDBMSからデータを高速に取り出すための仕組み)
- 複数のインデックスを作成していても、1回のクエリ実行に使用できるインデックスは1つ(オプティマイザが決定)
- 複数の検索条件がある場合はマルチカラムインデックスが役立つ
- 参照時の性能向上するが、更新時はオーバーヘッド
INDEXを貼る際の注意点
- BLOB と TEXTには必ず length を指定する
ERROR 1170 (42000): BLOB/TEXT column 'XXX' used in key specification without a key length
INDEXが効かないケース
- 全体の件数が少ない(フルスキャンした方が効率的だとオプティマイザが判断する )
- 否定条件(検索範囲が広すぎる)
- INDEXの指定順序を間違えている
- {col1 ,col2} のインデックスに対して、WHERE col2 = ? AND col1 = ?
- 関数や演算の利用(インデックスで保持されている値は素の値)
- 暗黙的な型変換(明示的に書くと回避できる)
- WHERE句にINDEXが貼られていないカラムが含まれている場合
- 部分一致または後方一致
- OR条件
適切なインデックスを貼るために...
- このテーブルは1年後に何行くらいになるだろうか(データが少ない場合は不要)
- 今必要だろうか(リリース後に傾向を見て貼ることも考える)
- Mentorの原則
- Measure:スロークエリログの参照など
- Explain:クエリの解析
- Nominate:犯人特定
- Test:修正したクエリのテスト
- Optimize:最適化(?)
- Rebuild:再構築(?)
EXPLAINの見方
公式参照
- typeがALLになっている場合、すごく小さいことが保証されるテーブルの参照以外は改善が必要
- Extraが Using filesort だと改善が必要(インデックスを利用してソートできないか考える)
WHERE句狙い vs ORDER BY句狙い
- WHEREによるデータの絞り込みを高速化するためのインデックス
- WHERE句で十分絞り込める場合はWHERE狙い
- ORDER BYによる並び替えの性能を向上するためのインデックス
- WHERE句がほとんど機能しないような場合はORDER BY狙い
参考
- https://www.konosumi.net/entry/2020/03/15/234810
- https://qiita.com/aidy91614/items/f17ab862986e9e5cdea6
- https://free-engineer.life/mysql-explain/
- https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html