この記事は トラストバンクAdventCalendar2024 23日目の記事になります。
担当は、ふるさとチョイスバックエンド@takashi_naが、年末負荷対策で実施したSQLチューニング事例をご紹介します。
2024年の年末の負荷対策メンバーになりまして、チーム一丸となって負荷対策に取り組んでまいりました。
その中で数時間かかる重いバッチを数分で終わらすことができる改善をしたので記事にしたいと思います。
何をしたか？
年末は寄付件数が大幅に増加し、負荷対策が必須です。その中で、実行に数時間かかる「重いバッチ処理」がありました。
調査したところ、バッチで使用しているSQLの WHERE 句に以下のようなNULL判定が含まれていたため、
t_pd.parent_pd_id IS NULL
これを
IFNULL(t_pd.parent_pd_id, 'isnull') = 'isnull'
に変更したところ、クエリ速度が15倍に向上し、処理時間が5時間→20分へ激減しました。
NULL判定の専用ロジックではなくて、通常の文字列比較にしたんですね
修正前後の実行計画
本件のクエリチューニングをしてくれたのは、同じく年末負荷撲滅メンバーのS氏でして、
S氏に教えを乞いつつ自分なりに確認していきます。
それでは修正前後のexplainを見てみましょう（少しマスキングしています）
-- 修正前
+------+--------------+--------------+-------+------------------------------------+----------------+---------+--------------------------------------------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+--------------+-------+------------------------------------+----------------+---------+--------------------------------------------+------+--------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 34 | Using temporary; Using filesort |
| 2 | DERIVED | t_ci | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | DERIVED | t_pd | ref | idx_ci,idx_ci_weight,idx_parent_pd | idx_parent_pd | 5 | const | 17 | Using index condition; Using where |
| 2 | DERIVED | t_tf | ref | idx_ci_form,idx_form_phase | idx_ci_form | 9 | mydb.t_pd.ci_id,mydb.t_pd.form_num,const | 1 | NULL |
| 3 | UNION | t_ci | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 3 | UNION | t_pd | ref | PRIMARY,idx_id_phase,idx_parent_pd | idx_parent_pd | 5 | const | 17 | Using index condition; Using where |
| 3 | UNION | t_spd | ref | idx_pd_ci_form | idx_pd_ci_form | 8 | mydb.t_pd.id,const | 1 | Using index |
| 3 | UNION | t_tf | ref | idx_ci_form,idx_form_phase | idx_ci_form | 9 | mydb.t_spd.ci_id,mydb.t_spd.form_num,const | 1 | NULL |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+------+--------------+--------------+-------+------------------------------------+----------------+---------+--------------------------------------------+--------+------------------------------------+
-- 修正後
+------+--------------+--------------+-------+------------------------------------+----------------+---------+--------------------------------------------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+--------------+-------+------------------------------------+----------------+---------+--------------------------------------------+------+--------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 141738 | Using temporary; Using filesort |
| 2 | DERIVED | t_ci | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | DERIVED | t_pd | ref | idx_ci,idx_ci_wt | idx_ci | 4 | const | 2685 | Using where |
| 2 | DERIVED | t_tf | ref | idx_ci_form,idx_form_phase | idx_ci_form | 9 | mydb.t_pd.ci_id,mydb.t_pd.form_num,const | 1 | NULL |
| 3 | UNION | t_ci | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 3 | UNION | t_spd | index | idx_pd_ci_form | idx_pd_ci_form | 12 | NULL | 185403 | Using where; Using index |
| 3 | UNION | t_tf | ref | idx_ci_form,idx_form_phase | idx_ci_form | 9 | mydb.t_spd.ci_id,mydb.t_spd.form_num,const | 1 | NULL |
| 3 | UNION | t_pd | eq_ref| PRIMARY,idx_pd_phase | PRIMARY | 4 | mydb.t_spd.product_detail_id | 1 | Using where |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+------+--------------+--------------+-------+------------------------------------+----------------+---------+--------------------------------------------+--------+------------------------------------+
目につくのは一部テーブルで処理されるデータ数が大幅に増加している点です。
- の行数が 34 → 141,738 に増加
- t_spd の行数が 1 → 185,403 に増加
これだけスキャンするデータが増えると、パフォーマンスが上がることはなさそうです。
むしろ悪くなりそうです…
考察
ちょっと条件を変えただけでもexplainを見るとスキャンする行が増えていたり、Using index conditionが数ヶ所で使われなくなったりと所々変化がありましたが、結局どこが作用してより良い最適化がされたかわかりませんでした。
ですので、今回修正を加えたカラムに関わるt_pdにフォーカスして確認します。
+-------+-------+------------------------------------+----------------+---------+------------------------------+------+------------------------------------+
| | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+------------------------------------+----------------+---------+------------------------------+------+------------------------------------+
| 修正前 | ref | PRIMARY,idx_id_phase,idx_parent_pd | idx_parent_pd | 5 | const | 17 | Using index condition; Using where |
| 修正後 | eq_ref| PRIMARY,idx_pd_phase | PRIMARY | 4 | mydb.t_spd.product_detail_id | 1 | Using where |
ざっと見ると
- ref → eq_ref
- 主キーもしくはユニークキーに対して等価結合されるようになった
- idx_parent_pd → PRIMARY
- 1対1でマッチする状態になり、不要な行が読まれなくなった
- rows 17 → 1
- Using index condition; Using where → Using where
- 主キーを使用して1行を直接取得することになり、ICPの処理が不要になった
おぉ、結構どころかすごい改善されていました。
結果的に、eq_ref で主キー結合が成立し、不要な行読み込みが大幅に削減されていました。
そのため、処理時間短縮につながったと考えられます。
また、is nullという専用ロジックで比較することがなくなったのも改善ポイントかと思います。
最後に
EXPLAIN で見た感じでは、一部のテーブルで rows が増えるなどの変化があり、総合的な最適化をもっと深掘りして確認すべき点もあります。
しかし、実測の処理時間が大幅に減ったのは事実で、今回の年末負荷対策に大きく貢献できました。
ただし、今後テーブルのレコード数の増減やMySQLのバージョン変更よるオプティマイザ変更などで再びパフォーマンス低下が起きるかもしれません。
そのためにも、継続的な監視や適切な対応を素早く行える体制づくりが重要だと思っています。
今はSREと共同でAPMを使ったパフォーマンス監視、改善を取り組んでいるところでございます。
トラストバンクでは様々な職種のメンバーを募集中です。
気になる方はお気軽にお問い合わせください。
https://www.wantedly.com/companies/trustbank/projects