Zenn
🐢

スロークエリ解決について

2024/04/03に公開
MySQL
SQL
スロークエリ
tech

こんにちは。株式会社ココナラDevOpsチームのソクです。
入社1年になりました。最近、前に紹介したレガシー移行と共に既存のシステムに存在する問題解決のタスクが多いです。
その中で今回はパフォーマンス改善として行っている「スロークエリ改善」を紹介します。

スロークエリ

データベースで実行したクエリが基準より遅いものです。基準はそれぞれ違うはずですがココナラでは「1秒以上」かかっているクエリをスロークエリと呼びます。

なぜ解決が必要？

Webサービスの品質として応答時間は大事な要素です。サーバサイド処理時間中、DBの処理時間が比較的長いし最初には問題なかったけどデータが増えると遅くなる可能性があるところです。
そのボトルネックを解決して応答時間を短くしてサービスの品質を上げることが目標です。

環境

Aurora MySQLバージョン3
互換性を提供しているのでMySQL 8.0と同じ感じで使っています。

スロークエリを探す

DB設定でslow_query_logの出力を設定して管理しているのでログから探しています。
以下の感じでログが記録されます。

# Time: 2024-02-12T21:12:32.447813Z
# User@Host: coconala[coconala] @  [0.0.0.0]  Id: 1111
# Query_time: 12.595313  Lock_time: 0.000056 Rows_sent: 30  Rows_examined: 15863
SET timestamp=1707772339;
SELECT `table_a.`id`, `table_b`.`name` FROM `table_a` LEFT JOIN `table_b` ON (`table_a`.`table_b_id` = `table_b`.`id`) WHERE `table_a.`table_c_id` = 1234 ORDER BY `table_a`.`id` DESC LIMIT 30;

実際のログ見るとスロークエリの件数が多いので以下の基準で優先度を決めます。

  • Query_timeが長い
  • 回数が多い
  • ユーザーへの影響有無

上のログは最大12秒以上かかっているし1秒以上かかった件数が月3000件頻度で発生してユーザー画面表示にも関連しているので高い優先度で解決することにしました。

実行計画

調査するスロークエリの実行計画を確認します。

EXPLAIN
SELECT
    `table_a.`id`,
    `table_b`.`name`
FROM
    `table_a`
    LEFT JOIN `table_b` ON (`table_a`.`table_b_id` = `table_b`.`id`)
WHERE
    `table_a.`table_c_id` = 1234
ORDER BY
    `table_a`.`id` DESC
LIMIT
    30;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE table_a ref idx_table_c_created,idx_table_b_date idx_table_c_created 5 const 4906 100 Using filesort
1 SIMPLE table_b eq_ref PRIMARY PRIMARY 4 table_a.table_b_id 1 100

table_aのExtraを見るとUsing filesortがあるので追加でソートしている処理をしていることが確認できます。
table_aに必要なインデックスはtable_c_idとidですが、idは降順にならないとORDER BYでソートする処理が発生することになります。
mysql8.0から降順インデックスが使えることをドキュメントで確認できたのでインデックス追加で解決できそうです。(https://dev.mysql.com/doc/refman/8.0/ja/descending-indexes.html)

解決

早速インデックス追加します。

CREATE INDEX idx_desc_id_table_c ON table_a (table_c_id, id DESC)

インデックス追加しましたが、

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE table_a ref idx_table_c_created,idx_table_b_date,idx_desc_id_table_c idx_table_c_created 5 const 4906 100 Using filesort
1 SIMPLE table_b eq_ref PRIMARY PRIMARY 4 table_a.table_b_id 1 100

possible_keysには追加されましたが実際利用していない状態です。

調査結果、条件によってインデックスではなくUsing filesortでソートする動きになることを確認しました。(https://dev.mysql.com/doc/refman/8.0/ja/order-by-optimization.html#order-by-index-use）

今回はそんな条件を無視して全体に適用したいのでindex hintを追加します。

USE INDEX (idx_desc_id_table_c)

を入れて再び確認すると

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE table_a ref idx_table_c_created,idx_table_b_date,idx_desc_id_table_c idx_desc_id_table_c 5 const 4906 100
1 SIMPLE table_b eq_ref PRIMARY PRIMARY 4 table_a.table_b_id 1 100

Using filesortなしで終わりです。
10秒かかった処理が1秒以内になりました。

本番リリース後のスロークエリログが0件になってグラフで見ると気持ちも良くなります。
グラフ

最後に

たくさんの課題が存在します。簡単に解決できない複雑なクエリもあるし仕様の調整が必要な件などクエリだけじゃなくもっと広めにサービス全体を見ないといけない課題なので難しいけど解決できるとやりがいがあります。
調査と解決までの流れを記録して他のメンバーも解決できる情報共有になればいいなと思います。

最後の最後に

ココナラではいろんな課題を解決するエンジニアを絶賛募集しています！
気になった方は採用ページをご覧いただければと思います。

https://coconala.co.jp/recruit/engineer/

Discussion