そのクエリ大丈夫？クエリチューニングで処理速度改善

こんにちは、KKです。
最近暖かくなってきて、スノボが趣味で花粉症の自分にとってはつらい春がやってきてしまいました。
（スノボのシーズンもう終わっちゃいそうだし毎日目がかゆすぎる...）

さて、今回は初めてクエリチューニングを行う機会があり、勉強になったのでご紹介させていただければと思います。

背景

とある社内システムにてデータを検索する際に少し時間がかかってました。（約35万件のレコードがあるテーブルから約1,000件を抽出する処理）実際に操作してみるとだいたい30秒ほどかかりました。（確かにこれはちょっと遅い...）
1人当たり1日20回前後は使用する機能だったので、もう少し検索結果表示されるまで早くしたいなと思い調査、対応を進めていくことにしました。

調査

スロークエリログ

SQL実行する以外は特に時間かかりそうな処理がなかったので、クエリに問題がありそう...と思い、スロークエリログを確認しました。
スロークエリログとは、名前の通りSQLの実行にかかった時間が設定した閾値を超えたクエリのログになります。
スロークエリログをみることで、重たいクエリの一覧を見ることができ、さらにクエリ実行にどれだけ時間がかかっているか等を確認することができます。

実際のスロークエリログは下記になります。

スロークエリログ加工後

MySQL、PostgeSQLのスロークエリログ確認方法についての詳細は下記をご覧いただければと思います。

MySQL（公式リファレンス）
https://dev.mysql.com/doc/refman/8.0/ja/slow-query-log.html

PostgreSQL（記事）
https://ptune.jp/tech/how-to-check-postgresql-slow-query/

スロークエリログ確認してみたら、今回の抽出時に動くクエリが複数あり、そこでもやはり30秒ほど処理に時間がかかってました。
（やっぱりクエリの問題か...）

問題箇所特定できたので、つづいて該当のクエリについて調査を進めます。

実行計画

まずはクエリの実行計画を確認していきます。

実行計画とは、ユーザが問い合わせたクエリ（SQL文）を実行する手順書になります。「どの順番にテーブルを結合するか」「ソート方法」「検索方法』などの作業内容や、想定される実行コストが記述されています。
実行計画の出力の仕方はとてもシンプルで、PostgreSQL、MYSQLの場合は実行したいクエリの先頭に「EXPLAIN」を付け加えるだけです。
「SELECT * FROM TABLE_NAME;」というクエリの実行計画を出力するには、「EXPLAIN SELECT * FROM TABLE_NAME;」でクエリを実行するだけで実行計画が取得できます。

実行計画で出力されるカラムについては下記になります。（MySQLの場合）

項目	意味
id	クエリの実行順番
select_type	クエリの種類
table	アクセス対象のテーブル
partitions	利用したpartisionテーブル
type	レコードへのアクセス種別
possible_keys	オプティマイザが候補として挙げたインデックス一覧
key	実際に利用されたインデックス
key_len	選択されたキーの長さ
ref	検索条件でkeyと比較される対象
rows	選択されるレコード数の見積もり
filtered	検索条件によって絞り込まれるレコードの割合
Extra	オプティマイザがクエリ実行で採用した戦略

このなかでも今回はtype、possible_keys、keyに焦点を当ててお話ししていきます。
今回の該当のクエリの実行計画は下記のようになっていました。

クエリチューニング前実行計画

現状だと使用できるkeyがなく、typeがALLになっているためフルスキャンになっているかつ、レコード数も多いテーブルだったので実行に時間がかかってしまっていました。（テーブルのレコード数多くてフルスキャンなら重たいのも納得...）
今回の検索機能では全件抽出する必要はないため、使用できるkeyがないことが問題なので、新しくインデックスを作成すれば実行時間改善されるんじゃないかと考えました。

インデックスについて

インデックスの作成

インデックスとは、データの検索速度を向上させるために、どの行がどこにあるかを示した索引のことです。
データを検索するときに、目的のデータが見つかるまですべての行を一行ずつ調べていくよりも、索引を利用して目的の行の場所を見つけてからその行のデータを読み取る方が効率的だという考えにより、非常によく用いられる方法です。

先ほど実行計画の確認結果から、現状すべての行を調べに行ってしまっていたため、インデックス（索引）を作成することで検索速度を向上していきたいと思います。

まずユーザにも普段の検索方法のヒアリングを行い、該当のクエリにおいてwhere句に使われている検索条件を整理して、どのカラムにインデックスを作成するのかを決めていきました。
条件整理したところ日付の範囲を必ず指定していることが分かったので、日付のカラム（ここではdateとします）にインデックスを作成することにしました。
インデックスについては下記例のようにSQLを実行することで作成することができます。

MySQL,PostgreSQLの場合
CREATE INDEX index_date ON TABLE_NAME (date);

上記クエリを実行し、インデックス作成できたので、実際に検証していきます。

インデックス作成後の検証

index作成後にEXPLAINで再度実行計画を確認したところ下記のようになっていました。

クエリチューニング後実行計画

possible_key、keyをみるとさきほど作成した「index_date」となっているかつ、typeがrangeになっていて、rangeは「インデックスを用いた範囲検索」のことなので、indexが使用されて、もともとのALL（フルスキャン）よりも検索範囲が小さくなりました。（ちゃんとindex使用されていて一安心...）

処理時間を計測してみたところ、もともと30秒近くかかっていたのが約2秒ほどになりました。
（正直10秒くらいになったらいいなーと思ってたので、期待以上の効果でびっくり...！）インデックスを作成することで問題の検索速度の改善することができました。

インデックスのデメリット

インデックスを作成して検索速度改善されたので、「これ全部インデックス作成しちゃえばいいんじゃないか！？」って思いました。
（絶対そんなわけない...）
そこでインデックスのデメリットについても調べてみました。

まず前提として、インデックスではテーブルとは別にデータを管理することになります。
インデックスを作成しているカラムに登録、更新、削除が発生するたびにインデックスにもその変更が発生します。
そのため頻繁に登録、更新、削除があるカラムにインデックスを作成してしまうと、検索時の速度は改善されていても、登録、更新、削除の処理速度が低下するので、トータルで見たらインデックスが負荷になってしまいます。（何も考えずにむやみやたらにインデックス作成してしまうと検索以外の処理低下してしまうってことか...）

頻繁に登録、更新、削除があるカラムにインデックスを作成する以外で無駄なインデックスになってしまうもの調べてまとめました。

・WHERE句やJOINで使われないカラムで作る
→インデックス作成しても使われることないので検索速度変わらず、登録、更新、削除の処理速度が低下してしまう...

・規模が小さいテーブルで作る
→フルスキャンでも処理速度ほぼ変わらず、登録、更新、削除の処理速度が低下してしまう...

インデックスのご利用は計画的に...

Query Insightsについて

今回はバージョンの問題などで使用できなかったのですが、CloudSQLを使用している場合はQuery Insightsを使用することでクエリごとの統計情報やDB全体でのCPUの負荷を見ることができます。
QueryInsightsを使えばDBへのCPU負荷が高まっている時間のクエリ一覧を見れば、レスポンスが遅く頻繁に実行されている問題がありそうなクエリをすぐ見つけることができます。
今回の記事でいうスロークエリログを見て問題ありそうなクエリ調べるところまでを画面から簡単に確認できます。かなり便利…

Query Insightsの公式リファレンス、参考になる記事を下記に記載しております。

Query Insights（MySQL）のリファレンス
https://cloud.google.com/sql/docs/mysql/using-query-insights?hl=ja

Query Insights参考記事URL
https://medium.com/sakajunlabs/sre-incident-response-with-cloudsql-query-insights-a122a1ed4a8c

最後に

本記事ではクエリチューニングを行った流れをご紹介させていただきました。
レコード数によってはインデックスがないとクエリ重たくなってしまうことは知っていましたが、実際にインデックスを作成することでどのくらい効果があるのかを身をもって感じるいい機会になりました。

また、本記事の最後にCloudSQLに少し触れましたが、過去にメタサイトのKHが執筆していた記事で、CloudSQLとBigQueryの年齢算出の違いについてまとめられた記事がございますので、ぜひこちらの記事もご覧ください！

BigQueryの年齢算出でハマった話(2023年) - CrossMarketing Group Tech Blog
https://tech.cm-group.co.jp/posts/bigquery-age-2023

ぜひとも次にクエリチューニングの調査を行う際はQuery Insightsを使ってみたいです！