Zenn
✨

スロークエリ改善の備忘録

2025/01/10に公開
SQL
パフォーマンス改善
MySQL 8
tech

大まかな手順

  1. スロークエリを特定: ログや監視ツールで問題箇所を把握。
  2. EXPLAINで実行計画を確認: クエリのボトルネックを特定
  3. インデックスの適切な利用: 必要な場所に適切なインデックスを設計。
  4. クエリの最適化: 必要なデータだけを取得する。

EXPLAIN ANALYZEの結果に対してどのようにアプローチするか

上から順に優先

  • cost, rows, actual timeをみてボトルネックになっている箇所を特定する
  • ボトルネックになっている箇所のindexを確認・検討する
    • indexが効いていない、indexを追加すべき等
  • 処理に対して不要なjoinやfilterがないか確認する
  • 設計として改善できないか検討する

INDEXに関してメモ

  • IN句であってもindexであればBtree探索分の計算量になるので何も貼らないよりはマシ
  • 複合インデックスの順番
    • 複合インデックスを作成する際、インデックスは左から順番に評価されるため、クエリで最も絞り込みが強い（＝最も選択性が高い）カラムを最初に配置します。
      • 選択性が高いカラム（データの分布が均等で、値の種類が多いカラム）を最初に。
      • 選択性が低いカラム（例えば、true/falseのように値が少ないカラム）は後に。
  • 単一カラムと複合インデックスの使い分け
    • 単一カラムインデックス: 一つのカラムだけを対象。
      • 使用例: WHERE user_id = 123
    • 複合インデックス: 複数のカラムをまとめたインデックス。
      • 使用例: WHERE user_id = 123 AND created_at > '2023-01-01'
    • 注意: 複合インデックスは、カラムの順序も重要（例: [:user_id, :created_at]ならuser_idが最初に評価される必要がある）。

具体例

（自分の実体験を固有名詞にマスキングしながら書いています）
今回はhuga_tableにおける特定のスロークエリの改善を依頼された。
クエリはわかっていたのでAWSのスナップショットから一時的なテストDBを作成してEXPLAIN ANALYZEを実行。

以下EXPLAIN ANALYZE実行結果

-> Aggregate: count(0)  (cost=107064 rows=1) (actual time=1001..1001 rows=1 loops=1)
    -> Nested loop inner join  (cost=107016 rows=483) (actual time=1001..1001 rows=0 loops=1)
        -> Filter: (huga_table.`hoge_type` = 'SomeType')  (cost=96386 rows=9667) (actual time=1001..1001 rows=0 loops=1)
            -> Index lookup on huga_table using index_huga_table_on_some_index (huga_id=3900, hoge_readed_at=NULL, hoge_deleted_at=NULL), with index condition: ((huga_table.hoge_deleted_at is null) and (huga_table.hoge_readed_at is null))  (cost=96386 rows=96668) (actual time=5.15..997 rows=56547 loops=1)
        -> Filter: ((hoge_entries.hoge_pre_deleted = false) and (hoge_entries.hoge_auth_id in (3900,39189,40254)) and (hoge_entries.hoge_id = huga_table.hoge_messageable_id))  (cost=1 rows=0.05) (never executed)
            -> Single-row index lookup on hoge_entries using PRIMARY (hoge_id=huga_table.hoge_messageable_id)  (cost=1 rows=1) (never executed)

今回であればFilter: (huga_table.hoge_type = 'SomeType') (cost=96386 rows=9667) (actual time=1001..1001 rows=0 loops=1)がかなりコストが高かった。

スロークエリを発生させる処理、それに類似した処理を調べたところ全てindex_huga_table_on_some_indexに加えてhuga_table.hoge_typeも検索条件に加えていることが分かった。

したがってindex_huga_table_on_some_indexのインデックスにhuga_table.hoge_typeを追加して再実行してみた。

-> Aggregate: count(0)  (cost=2.21 rows=1) (actual time=2.36..2.37 rows=1 loops=1)
    -> Nested loop inner join  (cost=2.2 rows=0.05) (actual time=0.848..0.848 rows=0 loops=1)
        -> Index lookup on huga_table using index_huga_table_on_some_index (huga_id=3900, hoge_type='SomeType', hoge_readed_at=NULL, hoge_deleted_at=NULL), with index condition: ((huga_table.hoge_deleted_at is null) and (huga_table.hoge_readed_at is null))  (cost=1.1 rows=1) (actual time=0.536..0.536 rows=0 loops=1)
        -> Filter: ((hoge_entries.hoge_pre_deleted = false) and (hoge_entries.hoge_auth_id in (3900,39189,40254)) and (hoge_entries.hoge_id = huga_table.hoge_messageable_id))  (cost=1.01 rows=0.05) (never executed)
            -> Single-row index lookup on hoge_entries using PRIMARY (hoge_id=huga_table.hoge_messageable_id)  (cost=1.01 rows=1) (never executed)

filterが消えてIndex lookupのみになり、actual timeも劇的に短くなった。
よってこれを一旦適用することとなった。（終わり）

Discussion