はじめに
こんにちは、crispyでエンジニアインターンをしている壷谷太樹です。
弊社では、Amazonのレビュー調査や価格比較ができるサービス、「サードモール」を運営しています。この度、サードモールの社内向け分析用ダッシュボードの作成を担当しました。
一度ダッシュボードを完成させましたが、分析する内容によっては多くの集計クエリが必要になり、ページ表示が遅くなるパフォーマンスが著しく低いクエリが出てしまいました。
この記事では、スロークエリの原因調査・解決を通して、普段のパフォーマンス改善の流れをお伝えできればと思います！
パフォーマンスが低かったクエリについて
ダッシュボードの実装にあたり、ユーザが分析したAmazonの商品を集計するクエリを作成しました。 クエリは正常に動作したものの、1クエリに平均51.5秒かかっていました。
SELECT product_variants.*, ... FROM product_variants INNER JOIN analysis_counts ON ... INNER JOIN products ON ... INNER JOIN product_prices ON ... INNER JOIN analysis_results ON ... WHERE product_prices.ec_site_id = 1 ORDER BY analysis_counts.analysis_count DESC LIMIT 50;
参考までに、各テーブルの概要は以下のようになります。また現象のイメージが付きやすいよう、実際のテーブル構造から一部変更しています。
| テーブル名 | 概要 |
|---|---|
| products | 商品情報のマスターテーブル |
| product_variants | 商品を（カラー・サイズなどの）バリエーションに分類したテーブル |
| product_prices | 商品価格に関するテーブル |
| analysis_counts | ユーザがAmazonレビュー調査を行った回数に関するテーブル |
| analysis_results | ユーザがAmazonレビュー調査を行った結果に関するテーブル |
なぜ遅くなっていたのか？
クエリを調べてみる
作成したクエリでは、SQLの基本と言える構文のみを使っていたため調査は難航しました。
クエリを調べたところ、
ORDER BYをなくすと200ms程度で動作するJOINするテーブルが1~2つであれば通常の速度で動作する（JOINするテーブルによって速度は変わります）
ということがわかりました。
このことから、ORDER BYとJOINを併用していることでパフォーマンスの低下が起きていると推測し、これらをキーワードに検索していくと原因に繋がる情報を見つけることができました。
ORDER BYするテーブルが駆動表になっていない
調べていくと、ORDER BYとJOINを併用する場合、ORDER BYするカラムのテーブルが駆動表になっていないとパフォーマンスが落ちるということがわかりました。駆動表は外部表ともいい、JOINする順番が最初の軸となる表（テーブル）になります。
ORDER BYするカラムのテーブルが駆動表でないことでクエリのパフォーマンス低下に繋がるのは、MySQLの結合アルゴリズムが関係しています。
MySQLのJOINは、以下のようなループでテーブルを結合していくNested Loop Join（NLJ）アルゴリズムが使われています。
for row in t1 {
for row in t2 {
for row in t3 {
...
}
}
}
NLJ アルゴリズムでは、外側のループから内側のループに一度に 1 つずつ行を渡すため、一般に内側のループで処理されるテーブルを何回も読み取ります。
そのため、何回も読み取られる内側のループ（内部表のテーブル）でORDER BYでソートすることで、速度の低下に繋がるようです。
駆動表になっていないのか確認する
実際にORDER BYするテーブルが駆動表になっていないのか確認してみます。以下はクエリに対するEXPLAINの結果で、一番上にあるテーブルが駆動表になります。
| id | select_type | table | ... |
|---|---|---|---|
| 1 | SIMPLE | analysis_results | |
| 1 | SIMPLE | products | |
| 1 | SIMPLE | product_variants | |
| 1 | SIMPLE | analysis_counts ※駆動表にしたいテーブル | |
| 1 | SIMPLE | product_prices |
今回のクエリでは、
ORDER BY analysis_counts.analysis_count DESC
のようにソートしており、ORDER BYするカラムのテーブルであるanalysis_countsが駆動表であるべきです。しかしこの結果から、駆動表はanalysis_resultsであることがわかります。
テーブルをJOINする順番は結合オプティマイザが自動で判断するため、今回のように意図しないテーブルが駆動表になっているケースがあります。
改善案
パターン① STRAIGHT_JOIN
STRAIGHT_JOINは、左側のテーブルが常に右側のテーブルの前に読み取られるINNER JOINです。
以下の場合、analysis_counts STRAIGHT_JOIN ...のように左側のテーブルに指定することで、結合オプティマイザは駆動表と判断してくれます。
... FROM product_variants INNER JOIN analysis_counts ON ... STRAIGHT_JOIN products ON ... INNER JOIN product_prices ON ... INNER JOIN analysis_results ON ... ...
パターン② USE INDEX
USE INDEX (index_list)を指定することによって、テーブル内の行検索に、指定されたインデックスの 1 つのみを使用するようMySQLに指示できます。また、index_listにPRIMARYを指定することでインデックスとして主キーを指定できます。
今回はanalysis_countsの主キーを検索時のインデックスに指定したいので以下のようなSQLになります。これにより結合オプティマイザは駆動表と判断してくれます。
... FROM product_variants INNER JOIN analysis_counts USE INDEX (PRIMARY) ON ... INNER JOIN products ON ... INNER JOIN product_prices ON ... INNER JOIN analysis_results ON ... ...
パターン③ JOIN_ORDER
MySQL8.0以降では、JOIN_ORDERヒントを使うことができるようです。
今回はバージョン8.0未満の環境であったため詳細は割愛しますが、STRAIGHT_JOINではINNER JOIN（内部結合）にのみ対応しているので、LEFT JOINを使う場合などに有効かと思います。
改善結果
今回のダッシュボードの実装では、「MySQLの言語に依存するSTRAIGHT_JOINより、インデックスを指定することを明示的にする方が駆動表になるテーブルがわかりやすい」という判断で、パターン②のUSE INDEXを使って駆動表を指定する実装をしました。
結果、平均51.5秒かかっていたクエリを平均329.5ms（約156.3倍の速度）にパフォーマンス改善することができました。
おわりに
今回のダッシュボードの実装を通して、意識していないとスロークエリが発生すること、MySQL特有のSTRAIGHT_JOIN、結合アルゴリズムなどについて理解できた良い機会になりました。
またこの実装では、普段より多く仕様の提案・相談をして、要件の決定から実装まで担当しました。裁量を持って担当でき、成長を実感できて楽しかったです！
crispyでは、HEIMや、サードモールの開発を一緒に盛り上げてくれるエンジニアを募集しています！興味がある方は、ぜひWantedlyのページから応募してください！