こんにちは、インフラのテックリードの沼沢です。
皆さんも日々生成 AI を活用して開発業務や運用作業を爆速化していることでしょう。
弊社でも生成 AI を日々活用し、業務の効率化などに取り組んでいます。
(^q^) <「ﾃﾞｰﾀﾍﾞｰｽﾅﾝﾓﾜｶﾗﾝ」な私ですが、数ヶ月ほど前に、生成 AI の力を借りてスロークエリを改善したことがあったので、そのことについてお伝えします。
本稿は、MySQL のクエリパフォーマンス改善における生成 AI (本稿では Gemini ) の活用事例の紹介記事です。
クエリが遅かった原因やパフォーマンス改善方法の解説は含まれていません。
弊社では Google Workspace を利用しており、学習に利用されない Gemini の利用が可能です。
そこで、Gemini に以下の情報を渡して糸口を探ることにしました。
- 実際の SQL 文
- WHERE 条件などもそのまま貼り付け
- クエリに記載されている全てのテーブルの情報
- SHOW CREATE TABLE 文で取得したものをそのまま貼り付け
この SQL が5秒ほどかかってしまっています。
改善の余地はありますか？
```
{実際の SQL 文}
```
なお、各テーブルの構造は以下の通りです。
```
CREATE TABLE `table_a` (
...(column や index 情報)
```
```
CREATE TABLE `table_b` (
...(column や index 情報)
```
```
CREATE TABLE `table_c` (
...(column や index 情報)
```
このプロンプトを渡した回答は以下のようなものでした。
クエリの時間がかかっているとのことですね。
提供いただいたクエリとテーブル構造を見て、考えられるパフォーマンスのボトルネックと改善策を分析します。
まず、実行されているクエリと WHERE/ORDER BY 句、そして JOIN 条件を整理します。
**クエリの主な処理:**
1. ...
2. ...
3. ...
**考えられるボトルネック:**
5秒という時間は、データ量やインデックスの状態によっては、以下のいずれか、あるいは複数の要因で発生することが多いです。
1. **`WHERE table_a.created >= '...'` とその他の条件の組み合わせ:**
- ...(説明)...
2. **結合処理:**
- ...(説明)...
3. **ソート処理 (`ORDER BY`):**
- ...(説明)...
**改善の余地と具体的なアクション:**
最も可能性の高いボトルネックは**ソート (Filesort)** です。これを解消することが最優先です。
1. **`EXPLAIN` 結果の確認（最重要）:**
- ...(説明)...
2. **インデックスの追加:**
- ...(説明)...
3. **JOIN 条件の整理:**
- ...(説明)...
...
**最も推奨するアクション:**
1. **`EXPLAIN` 結果を実際に確認する。**
2. `Using filesort` が確認できれば、... といった**複合インデックスを追加**する。
3. ...
これらの改善を行うことで、クエリのパフォーマンスが大幅に向上する可能性が高いです。
特に複合インデックスによるソートの最適化が効くかどうかが鍵となります。
もし `EXPLAIN` の出力結果を共有いただければ、より詳細な分析と具体的な改善提案が可能です。
何やら色々と説明してくれましたが、とにかく EXPLAIN の結果を確認せよ、とのことで、この次に EXPLAIN 結果を取得して渡してみることにしました。
EXPLAIN の結果は以下の通りです。 +----+-------------+---------+------------+--------+---------------+-----+---------+---------------------+------+----------+-----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+--------+---------------+-----+---------+---------------------+------+----------+-----------------------------------------------------------+ | 1 | SIMPLE | table_a | NULL | index | ... | ... | 6 | NULL | 4 | 25.00 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | table_b | NULL | eq_ref | ... | ... | 4 | hoge_db.table_a.xxx | 1 | 10.00 | Using where | | 1 | SIMPLE | table_c | NULL | ref | ... | ... | 4 | hoge_db.table_a.xxx | 10 | 0.47 | Using where | +----+-------------+---------+------------+--------+---------------+-----+---------+---------------------+------+----------+-----------------------------------------------------------+
EXPLAIN を渡したことで、より詳細な分析をしてくれました。
EXPLAIN 結果をご提供いただきありがとうございます。
この結果から、パフォーマンスが遅い原因が明確になりました。
**EXPLAIN 結果の分析:**
EXPLAIN 結果から、以下の重要な情報が読み取れます。
1. **ジョイン順序:**
- ...(説明)...
2. **`table_a` の Extra カラム:**
- ...(説明)...
3. **`table_c` のアクセス方法:**
- ...(説明)...
**問題点:**
オプティマイザは、... という実行計画を選択しました。
しかし、この計画では ...。結果として、条件に合う可能性のある多くの中間行が生成され、
それらを全て取得してから Filesort でソートしているため、パフォーマンスが著しく低下しています。
**改善策:**
遅延の最も大きな原因は Filesort です。
これを回避するためのインデックス戦略が中心となります。
1. **複合インデックスの追加（最重要）:**
- 最も効果が期待できるのは、ソート順を先行させ、その後にフィルタ条件を配置する以下のインデックスです。
```
ALTER TABLE `table_c`
ADD INDEX `idx_xxxx_yyyy_created` (`xxxx` DESC, `yyyy` DESC, `created`);
```
- このインデックスが使われると、...(中略)... Filesort が不要になり、劇的に速くなることが期待されます。
2. **JOIN 条件の整理 (任意):**
- ...(説明)...
**実施ステップ:**
1. **推奨の複合インデックス `idx_xxxx_yyyy_created` を `table_c1` テーブルに追加します。** (本番環境への追加は影響を考慮して慎重に行ってください)
2. インデックス追加後、再度元のクエリを実行し、速度が改善したか確認します。
3. 改善が見られない場合、再度 EXPLAIN を実行し、追加したインデックスが `key` カラムで使われているか、`Extra` から `Using temporary; Using filesort` が消えているかを確認します。もし使われていない場合は、オプティマイザがこのインデックスを最適と判断しなかった別の理由がある可能性があります（データ分布など）。
4. 必要に応じて、JOIN 条件を WHERE 句に移動するなどの整理も行います。
最も重要なのは、Filesort を回避するために、ソート順とフィルタ条件を効率的にカバーできる複合インデックスを追加することです。
この改善で大きく速度が向上する可能性が非常に高いです。
複合インデックスを追加すると Filesort が解消され、劇的なパフォーマンス改善が見込まれるとのことでした。
そこで、本番データベース (Amazon Aurora) のクローンを作成し、指定された複合インデックス作成後に再度 EXPLAIN を取得しましたが、特に結果が変わったようには見えなかったため、結果を Gemini に渡しました。
指定のインデックス追加後、EXPLAIN を実行しましたが、特に差がありませんでした。 以下がその EXPLAIN 結果です。 ``` +----+-------------+---------+------------+--------+---------------+-----+---------+---------------------+-------+----------+-----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+--------+---------------+-----+---------+----------------------------------------------------------------------------------------------------+ | 1 | SIMPLE | table_a | NULL | index | ... | ... | 6 | NULL | 23908 | 10.00 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | table_b | NULL | eq_ref | ... | ... | 4 | hoge_db.table_a.xxx | 1 | 10.00 | Using where | | 1 | SIMPLE | table_c | NULL | ref | ... | ... | 4 | hoge_db.table_a.xxx | 13 | 0.36 | Using where | +----+-------------+---------+------------+--------+---------------+-----+---------+---------------------+-------+----------+-----------------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) ``` また、クエリの実行時間も大して変化はありませんでした。 他に改善できそうなことはありますか？
※rows が増えているのは、本番のデータベースで実行したためです。
これまでは検証環境のデータベースで実施していました。
EXPLAIN 結果ありがとうございます。
提供いただいた新しい EXPLAIN 結果を見ました。
インデックスを追加しても変化がなかったとのこと、承知いたしました。
EXPLAIN 結果は前回のものとほぼ同じで、特に以下の点が確認できます。
1. **ジョイン順序が変わっていない:**
- ...(説明)...
2. **Filesort が継続している:**
- `table_a` の行に `Using temporary; Using filesort` が引き続き表示されています。これは、やはりソートがボトルネックであることを明確に示しています。
今回の EXPLAIN 結果から、問題はインデックスの有無だけでなく、**オプティマイザが選択した不適切なジョイン順序**にある可能性が非常に高いです。
**さらなる改善策:**
オプティマイザが意図した最適なジョイン順序を選択しない場合、明示的にそれを誘導する必要があります。
最も効果的な方法は、`table_c` を最初に処理させることです。
1. **`STRAIGHT_JOIN` を使用してジョイン順序を強制する:**
- ...(説明や改善後の SQL 文)...
- 期待される結果：
- EXPLAIN の行の順序が `table_c` -> `table_a` -> `table_b` と変わっている。
- `table_c` の行で、`key` に追加した `idx_xxxx_yyyy_created` が表示されている。
- `table_c` の行の `type` が `range` (created の範囲検索にインデックスが使われている場合) または `index` になっている。
- `Extra` カラムから `Using temporary; Using filesort` が消えている。
2. **`table_c` 上の複合インデックスの見直し（STRAIGHT_JOIN でも改善しない場合）:**
- もし `STRAIGHT_JOIN` を使っても追加したインデックスが使われず Filesort が解消しない場合、インデックス構成や WHERE 句の他の条件が問題になっている可能性があります。
- 現時点では、まず `STRAIGHT_JOIN` でジョイン順序を強制し、追加したインデックスが有効に使われるかを確認するのが最優先です。
**次のステップ:**
上記の手順で、**`STRAIGHT_JOIN` を使用したクエリで `EXPLAIN` を実行し、その結果を再度お見せいただけますでしょうか。
** この結果を見ることで、オプティマイザが新しいジョイン順序でどのように振る舞っているか、追加したインデックスを使おうとしているかどうかが分かります。
この `STRAIGHT_JOIN` を試すことで、ボトルネックである Filesort が解消され、パフォーマンスが大幅に改善する可能性が非常に高いです。
インデックス追加後の EXPLAIN を見て、STRAIGHT_JOIN の導入という次のステップを提案してくれました。
Gemini に提案してもらった改善後の SQL 文の EXPLAIN を取得して、再度結果を渡しました。
STRAIGHT_JOIN を使用したクエリでの EXPLAIN 結果です。 ``` +----+-------------+---------+------------+--------+---------------+-----+---------+---------------------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+--------+---------------+-----+---------+---------------------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | table_c | NULL | range | ... | ... | 6 | NULL | 116732 | 0.90 | Using index condition; Using where; Using filesort | | 1 | SIMPLE | table_a | NULL | eq_ref | ... | ... | 4 | hoge_db.table_c.xxx | 1 | 10.00 | Using where | | 1 | SIMPLE | table_b | NULL | eq_ref | ... | ... | 4 | hoge_db.table_c.xxx | 1 | 10.00 | Using where | +----+-------------+---------+------------+--------+---------------+-----+---------+---------------------+--------+----------+----------------------------------------------------+ ```
STRAIGHT_JOIN によって順序が変わり、type にも変化がありました。
このクエリを実際に実行したところ、速度も 0.69 sec まで下がり、80%以上の高速化が実現できました。
しかし、依然として Using filesort は残っています。
この結果を見た Gemini の回答は以下の通りです。
EXPLAIN 結果の共有ありがとうございます。 `STRAIGHT_JOIN` を使用することで、期待通りジョイン順序が `table_c` -> `table_a` -> `table_b` と変更されました。 また、`table_c` のアクセスに `type: range` が使用され、... が使われていることが分かります。これは良い兆候です。 しかし、EXPLAIN の `Extra` カラムに **`Using filesort`** が依然として表示されています。 **現在の問題点:** ...(説明)... **さらなる改善策: インデックスヒントの使用** オプティマイザが自動で最適なインデックスを選択しない場合、**インデックスヒント (Index Hint)** を使って、特定のインデックスを使うように強制することができます。 今回は、`table_c` に対して、Filesort を回避するために追加した `idx_xxxx_yyyy_created` インデックスを使うように指示してみましょう。 クエリを以下のように修正し、`EXPLAIN` を再度実行してください。 **SQL** ```sql ...(改善後の SQL 文)... ``` **期待される EXPLAIN 結果:** - `table_c` の行の `key` が `idx_xxxx_yyyy_created` になっている。 - `table_c` の行の `type` が `index` あるいは `range` になっている。 - `table_c` あるいは他のテーブルの `Extra` カラムから **`Using temporary; Using filesort` が消えている。** もしこの `EXPLAIN` 結果が得られれば、Filesort が回避され、クエリの実行速度が大幅に改善される可能性が非常に高いです。 **もしこのインデックスヒントでも改善しない場合:** その場合は、以下の点をさらに検討する必要があります。 - ... - ... - ... まずは、上記で提案したインデックスヒントを使用した EXPLAIN を試して、結果をご確認ください。
まだ改善できる余地があると言っているので、提案された「インデックスヒントが追加された SQL 文」で再度 EXPLAIN を取得し、結果を渡しました。
インデックスヒントを足して EXPLAIN を実行した結果です。 ``` +----+-------------+---------+------------+--------+---------------+-----------------------+---------+---------------------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+--------+---------------+-----------------------+---------+---------------------+-------+----------+-------------+ | 1 | SIMPLE | table_c | NULL | index | ... | idx_xxxx_yyyy_created | 15 | NULL | 19999 | 0.30 | Using where | | 1 | SIMPLE | table_a | NULL | eq_ref | ... | ... | 4 | hoge_db.table_c.xxx | 1 | 10.00 | Using where | | 1 | SIMPLE | table_b | NULL | eq_ref | ... | ... | 4 | hoge_db.table_c.xxx | 1 | 10.00 | Using where | +----+-------------+---------+------------+--------+---------------+-----------------------+---------+---------------------+-------+----------+-------------+ ``` かなり良くなったように思います。
ついに Using filesort が無くなりました。
実行速度も 0.01 sec になり、最終的に99%以上の高速化が実現できました。
この結果に、Gemini もご満悦のご様子でした。
EXPLAIN 結果のご提供、ありがとうございます！
素晴らしいです！この新しい EXPLAIN 結果は、パフォーマンスの問題が大きく改善されたことを示しています。
**新しい EXPLAIN 結果の分析:**
1. **ジョイン順序:** `STRAIGHT_JOIN` の指示通り、`table_c` -> `table_a` -> `table_b` の順になっています。
2. **`table_c` のアクセス:**
- `type: index`: これが重要です！テーブル全体や範囲をフルスキャンするのではなく、**インデックスの順序でアクセス**しています。
- `key: idx_xxxx_yyyy_created`: **意図した複合インデックスが使用されています！**
- **`Using temporary; Using filesort` が消えている！**: これが最も大きな改善点です。一時テーブルでのソートが不要になったことを意味します。
**何が起こったか？**
...(改善内容の説明)...
**結論:**
この EXPLAIN 結果は、クエリのパフォーマンスボトルネックであった Filesort が解消され、意図した複合インデックスが有効活用されていることを明確に示しています。
実行時間も改善されたとのことですので、この実行計画が効率的なものであることが確認できました。
**最終的な推奨:**
1. **追加した複合インデックス `idx_xxxx_yyyy_created` はそのまま維持してください。** これがパフォーマンス改善の鍵です。
2. **クエリは `STRAIGHT_JOIN` と `USE INDEX (idx_xxxx_yyyy_created)` ヒントを含めた形を使用してください。** これにより、オプティマイザに最も効率的な実行計画を選択させることができます。
これで、当初 5秒かかっていたクエリは、応答速度が大幅に改善されているはずです。
問題解決へのステップ、お疲れ様でした！
Gemini とのやり取りで得た結果をもとに、インデックス追加 + プログラムが生成する SQL 文の修正を施した上で再度検証を実施し、無事に本番環境へリリース。
スロークエリの1つを撃退することに成功したのでした。
初手で小難しいプロンプトを用意することもなく、Gemini と会話形式で少しずつ改善に向かう体験は、まるでデータベースのエキスパートなパイセンと一緒に問題解決に取り組んでいるような感覚でした。
Gemini に限らず他の AI でも同様のことはできるかと思いますので、皆さんのお抱えの AI で改善を試みてはいかがでしょうか。
asken ではエンジニアを絶賛募集中です。
まずはカジュアルにお話しできればと思いますので、ぜひお気軽にご連絡ください！
https://hrmos.co/pages/asken/jobs
asken techのXアカウントで、askenのテックブログやイベント情報など、エンジニアリングに関する最新情報を発信していますので、ぜひフォローをお願いします！
コメント