Zenn
📘

自分がSQL改善で行なっていること

2024/12/11に公開
MySQL
tech

この記事について

最近SQLの改善対応をしていたので、
原因となるSQLの調査から改善まで、
自分が行ったことをまとめていこうと思います。

注意点

[1]
DBの専門家ではないので、
本記事で、DBに関する詳細な説明は致しません。

[2]
誤った情報が含まれている可能性があるため、参考程度にしていただければと思います。

原因となるSQLの調査

1. アラートの通知を確認

CPUやメモリに負荷がかかり続けている場合、
どこかしらに通知がされると思いますので、まずは内容を確認します。

2. 右肩上がりの時間帯の中のSQLを確認

通知から該当のモニタリング画面を確認しにいきます。

例えば、CPUに負荷がかかっている場合、
以下のような図になると思います。

確認する時間帯は、
CPUの負荷が上がり始め 〜 一番高くなるまでの時間帯を確認します。

理由としては、
何かが原因で負荷が上昇し始めているので、
その時間帯で発行中のSQLに原因があると考えているためです。

また、
一番高い時間帯ではどんなSQLでもリソース不足により、遅くなっていると考えているためです。

時間帯を確認後、ClowdWatch Logsなどから、
以下のような感じで、問題となるSQLを検索します。
https://tech.excite.co.jp/entry/2023/02/17/114538

3. 再現性の確認

再現性の確認のために、
実際に該当のSQLを実行し、負荷をかけつつ、モニタリング画面の確認をします。

負荷が上昇していることが確認できた場合、実際に改善を模索していきます。

負荷のかけ方は、
MySQLであれば、mysqlslapを使えば効率的にできると思います。

mysqlslapとは

MySQLが公式でサポートしている負荷検証が簡単にできるツールです。

https://dev.mysql.com/doc/refman/8.0/ja/mysqlslap.html

mysqlコマンドのインストール時に、
同時にインストールされます。

使い方としては以下のような感じです。

$mysqlslap \
  --host= 127.0.0.1 \
  --port= 3306 \
  --user= sample\
  --password= test\
  --concurrency=10 \ # 同時接続数
  --iterations=1 \ # テストを繰り返す回数
  --create-schema= \ # DBの指定
  --query=./sample.sql

実行すると下記のような結果が出力されるので、
SQLの発行数を増やしていき、遅くなるか簡単に検証できます。

Benchmark
	Average number of seconds to run all queries: 22.254 seconds
	Minimum number of seconds to run all queries: 22.254 seconds
	Maximum number of seconds to run all queries: 22.254 seconds
	Number of clients running queries: 10
	Average number of queries per client: 1

SQLの改善

1. EXPLAINの確認

EXPLAINを用いてindexの適用を確認します。
自分は大体下記の項目を確認します。

  • type
  • key
  • rows
  • filtered
  • Extra

また、
MySQL Workbenchの「Visual Explain Plan」を使って、
SQL の EXPLAIN を図で可視化して、問題となっている箇所を確認します。
※ 赤色やオレンジ色の図が改善の検討箇所になります。

type

対象のテーブルに対してどのような方法でアクセスするかを示すものです。

typeが「index」や「ALL」の場合、
フルスキャンなので、基本的には改善の対象になります。

key

実際に使用したindexを示すものです。

想定通りのindexを使用しているかを確認します。

rows,filtered

rowsは、そのテーブルから取得される行数の見積もりを示し、
filteredは、 テーブル条件によってフィルタ処理される行の割合を示します。

この2つの項目から、
取得された推定行から、どれだけフィルタリングされるかを確認できます。

例えば、以下の条件の場合、

  • rows: 1,000
  • filtered: 50.00 (50%)

1,000 * 0.5 = 500(行)までフィルタリングされたということになります。

※ filteredが、100%の場合は、フィルタリングは実行されていないということになります。

そのため、
rowsが大きい、かつ、filteredが10%など少ない場合、
フィルタリングのコストがかかり、CPUの負荷がかかっているかもと想定することができます。

Extra

クエリーを解決する方法に関する追加情報が示されています。

大体以下のような追加情報がある場合は、
indexをうまく適用できないので、改善できないか考えます。

  • Using temporary
  • Using filesort

2.SQLの修正

SQLの修正は大体同じことをすると思いますが、
自分が普段試すのは下記の通りです

  • indexの変更
  • force indexによるindexの強制
  • 条件文の確認
  • JOINやサブクエリを使ったデータ取得の方法の変更
  • 改善が難しい場合

indexの変更

問題となるSQLを確認し、
WHERE句で複数条件を指定しているが、
適用中のindexが単一インデックスの場合、複合indexを作成し改善できないか模索します。

複合インデックスが適用されている場合、
indexの指定順を変更して改善できないか試します。

indexを作成後、速度改善されているか確認します。
もし異なるindexを使用した場合は、
force indexでindexを強制して検証します。

force indexによるindexの強制

force indexを使うと使用するindexを強制できます。

使い方は以下のような感じです。

select * from test force index(idx_1) where name = 'aaa';

実装時にindexを登録したとしても、
実際のSQLでは想定外のindexを使用している場合があります。

なので、
EXPLAINを確認し、想定外のindexを使用していた場合は、
force indexを使用して、強制します。

条件文の確認

具体的には、以下の条件を指しています。

  • 結合時(JOIN)の条件
  • WHERE句の条件
  • ORDER BY句の条件

これは、
登録中のindexを確認し、
条件文を修正することによって、indexを適用できないか、
あるいは、新規indexを作成して改善できないか模索します。

結合先の取得方法の変更

取得条件によりますが、
主にINNER JOINやLEFT JOINで結合している場合、
EXISTSやIS NOT NULLに変更できる場合は、試します。

また、
EXISTSを使っている場合、IS NOT NULLに変更してみます。

JOIN句を変更し、サブクエリを試す理由は、
indexを上手く使って、速度改善が見込める場合があるためです。

また、
EXISTSを使う場合、サブクエリを想定している思いますが、
EXPLAINを確認すると、サブクエリではなく、結合として実行されることもあったので、
想定した挙動になっているかも含め、確認します。
※ 自分は最近初めて知りました。

3.Explain Analyzeによる詳細確認

下記のような場合に、Explain Analyzeを使って内容を確認します。

  • EXPLAINを確認しても問題が見当たらない
  • indexを適用しても早くならない

Explain Analyzeとは

EXPLAINはあくまで、実行前の計画を表示してくれるものです。

それに対して、
EXPLAIN ANALYZEは実際にクエリを実行し、各ステップでの実行時間を測定してくれます。

そのため、
クエリのどのステップに時間がかかっているかをより正確に掴むことができます。

Explain Analyzeの読み方に関しては↓が参考になります。
https://qiita.com/Nyokki/items/c2d95cb2a75d3c0acb64

使い方はEXPLAINと同じように使います。

explain analyze
select * from test_products
inner join test_product_images on test_products.id = test_product_images.product_id 
where test_products.id in (1,2,3);

出力は下記ような形で出力されます。

-> Nested loop inner join  (cost=2.95 rows=6) (actual time=0.069..0.0825 rows=6 loops=1)
    -> Filter: ((test_product_images.product_id in (1,2,3)) and (test_product_images.product_id is not null))  (cost=0.85 rows=6) (actual time=0.0218..0.0302 rows=6 loops=1)
        -> Table scan on test_product_images  (cost=0.85 rows=6) (actual time=0.0192..0.0262 rows=6 loops=1)
    -> Single-row index lookup on test_products using PRIMARY (id=test_product_images.product_id)  (cost=0.267 rows=1) (actual time=0.00305..0.00308 rows=1 loops=6)

(cost=2.95 rows=6)は推定(EXPLAIN)の測定で、
(actual time=0.069..0.0825 rows=6 loops=1)は実際にかかった時間を示します。

actualの中で、一番時間がかかっている箇所が原因とみなすことができます。

また、
上記の出力にはないですが、
例えば結合条件時に、左側と右側のテーブルのカラムの型が不一致の場合(左: varchar, 右: int)、
↓のような出力でcastがされていることも確認できるので、原因の特定がしやすいです。

-> Hash antijoin (cast(test_a.id as double) = cast(test_b.test_a_id as double))  (cost=2.95 rows=6) (actual time=0.069..0.0825 rows=6 loops=1)

原因が判明したら、改善できないか再度模索します。

改善が難しい場合

既存のSQLを修正するだけでは難しい場合があります。
そうなった場合は、下記のような代替案をいくつか考えて、
メンバーや上長に相談し、修正案を模索します。

  • DBのスペックを上げるか
  • SQLを分割できないか
  • 不要なデータを削除できないか
  • 期間を指定して絞り込めるか
  • 結合(JOIN)が原因の場合は、非正規化やカラム追加で改善が見込めないか

4.速度検証

SQLの修正後は、再度速度検証を行います。
検証は、mysqlslapを使って検証します。

複数の対象で、速度改善が確認できたら、
実際にPR作成してリリースに向けた対応を行います。

仮に速度が遅くなってい場合は、
再度調査を行い、原因を模索、あるいは、関係者に結果を共有し対応方針を相談します。

まとめ

ざっと
SQLの改善まで自分が行った対応をまとめてみました。

今後、
SQLの調査から改善まで対応をすることになった際に、
参考になればと思います。🙇

Discussion