生成AIと学ぶクエリチューニング

こんにちは、estieでSWEをしている上久保です。突然ですが皆さんはプロダクトのパフォーマンス改善に取り組んだことはありますか。プロダクトは機能追加やデータ拡充などによってパフォーマンスが悪化する場合があり、時としてUXの低下を招いたり、障害につながることもあります。

最近ではソフトウェア開発における生成AIの活用が当たり前になってきていますが、その影響でボトルネックの特定やチューニングといったソフトウェアのパフォーマンス改善の取り組みに関しても、以前より敷居が下がっていると感じています。例えば私はソフトウェアエンジニアとして一定レベルのSQLは実装できますが、MySQLのオプティマイザの仕組みを深く理解した上で、クエリの最適化をすることはあまりできていませんでした。

しかし、今回ChatGPTに都度相談しながらクエリのボトルネックを調査し、結果としてパフォーマンス改善を達成するとともに、効率的な学習も行うことができました。これはとても良い体験だと感じたので、具体的な実装を交えつつSQLクエリのチューニングについての学びと生成AIに対するプロンプトエンジニアリングについて感じたことを書いていきます。

なお、登場するクエリやプロンプト等の情報は説明のために一部省略・修正をしています。また今回使用しているAIはChatGPT 4oモデルです。モデルの違いによる結果の比較は行なっていません。

ケース1: 複雑なCTEを伴うクエリの高速化

それでは早速みていきましょう。こちらは過去１年間で物件の取引を行った所有者の一覧を取得するクエリです。

クエリを見ると物件の取得日(acquisition_date)と売却日(sale_date)から所有者ごとに「直近１年間で行われた」物件取得の回数と物件売却の回数を計算するnumber_of_transactionsという名前付きサブクエリ(Common Table Expression: CTE)を作成しています。

-- 過去１年間で物件の取引を行った所有者の一覧を取得する
WITH number_of_transactions AS (
  SELECT
    owners.id as owner_id,
    COUNT(
      CASE
        WHEN acquisition_date > (NOW() - INTERVAL 1 YEAR) THEN 1
      END
    ) as number_of_acquisitions, -- 直近１年間で行われた物件取得の回数
    COUNT(
      CASE
        WHEN sale_date > (NOW() - INTERVAL 1 YEAR) THEN 1
      END
    ) as number_of_sales, -- 直近１年間で行われた物件売却の回数
  FROM owners
  LEFT OUTER JOIN properties ON owners.id = properties.owner_id
  GROUP BY owners.id
),
SELECT
  *
FROM owners
LEFT OUTER JOIN number_of_transactions USING(owner_id)
WHERE number_of_transactions.number_of_acquisitions > 0
  OR number_of_transactions.number_of_sales > 0;

今回の目的はこのクエリの高速化です。まずは雑にAIに質問してみました。

私：このクエリが遅いので改善したいです、改善案を提案してください

AIはインデックスや実行計画などの確認も含めてさまざまな提案をしてくれました。その中で以下の指摘が気になりました。

## number_of_transactions CTEの条件付き集約を2つに分離
- 集約処理を簡潔にしてオプティマイザが最適化しやすい形に

<< 改善後のクエリ >>

なるほど、では改善後のクエリをコピペして試してみよう！とすぐに取りかかりたくなりますが、ちょっと待ってください。せっかくなので自分の理解のために詳しく教えてもらうことにしましょう。

私：この対応がなぜオプティマイザの最適化の促進につながるのか説明してください。

🤖AIの回答はこちら

✅ 改善方法：条件ごとに分けてCTE化
以下のように条件ごとに分けたCTEにすることで、オプティマイザが以下のような恩恵を受けられます：

WITH acquisitions AS (
  SELECT owner_id, COUNT(*) AS number_of_acquisitions
  FROM properties
  WHERE acquisition_date > (NOW() - INTERVAL 1 YEAR)
  GROUP BY owner_id
),
sales AS (
  SELECT owner_id, COUNT(*) AS number_of_sales
  FROM properties
  WHERE sale_date > (NOW() - INTERVAL 1 YEAR)
  GROUP BY owner_id
)

分離した集約（2ステップ）	効果
WHERE で条件が明確化	インデックスが活きる（acquisition_date、sale_date など）
集約ごとの行数が減る	GROUP BYのコストが軽減
オプティマイザが実行順序を選べる	クエリプランの選択肢が増え、JOIN前に絞り込みや最適化が可能になる
計算を分割してキャッシュ可能	DBによってはCTEの部分結果をメモリキャッシュできる場合もある

知りたいことに対してあまり期待通りの説明がされませんでした。実行計画に絞り込んでさらに質問してみます。

私：クエリプラン比較の例を提示して

🤖AIの回答はこちら

🧪 実行結果比較（MySQL/PostgreSQL 共通傾向）

項目	単一ステップ（CASE式）	条件別2ステップ（CTE）
実行時間（例）	850ms	310ms
Scan Type	Full Table Scan	Index Range Scan（条件別に走査）
Buffer Usage / Temp Usage	高（1回のGROUP BYで複数条件集計）	低（各CTEは単純なGROUP BY）
Parallelismの有効性（PGの場合）	低（並列化しにくい）	高（CTE単位で並列評価可能）
JOIN計画	Nested Loopのコストが高くなる	CTEによる事前集約でJOIN対象がスリムになる
Planner Note（PG）	「Hash Aggregate」 + 「Filter」	「GroupAggregate」 + 「Index Only Scan」

ここまできてようやくMySQLでは複数CTEの並列実行が条件次第で可能であるという本質的な知見に辿り着きました。この説明で自分の中で腑に落ちたのでもっと深掘りするのは別の機会にします。

それでは戻って実装と評価をしてみましょう。

クエリを以下のように修正して実行時間を比較してみます。

WITH acquisitions AS (
  SELECT owner_id, COUNT(*) AS number_of_acquisitions
  FROM properties
  WHERE acquisition_date > (NOW() - INTERVAL 1 YEAR)
  GROUP BY owner_id
),
sales AS (
  SELECT owner_id, COUNT(*) AS number_of_sales
  FROM properties
  WHERE sale_date > (NOW() - INTERVAL 1 YEAR)
  GROUP BY owner_id
),
SELECT
  owners.id AS owner_id,
  COALESCE(acquisitions.number_of_acquisitions, 0) AS number_of_acquisitions,
  COALESCE(sales.number_of_sales, 0) AS number_of_sales
FROM owners
LEFT JOIN acquisitions ON owners.id = acquisitions.owner_id
LEFT JOIN sales ON owners.id = sales.owner_id;

結果として1秒以上の高速化を達成できました。めでたし、めでたし。

修正前	3.310 sec
修正後	2.041 sec

ケース2: 地理情報を検索するクエリの最適化

続いての例は、ある地点から一定以内の距離に存在する建物の一覧を返すクエリです。このクエリではMySQLのGIS機能を使っており、詳細の説明はここでは省略しますがlocationというカラムは物件の位置情報を意味していて緯度、経度の情報を持ちます。

-- 指定した地点から800m以内にある物件の一覧を取得する
-- ST_BUFFER: 中心点から指定距離の円形ポリゴンを生成
-- ST_WITHIN: ポリゴンに含まれているか判定
-- 4326: 座標系のSRID
-- 800 * 180.0 / 3.141592653589793 / 6378137.0: 800mを角度に変換、6378137.0mは赤道半径
SELECT
  building_id,
  address,
  ...
FROM
  buildings
WHERE
  ...
  AND ST_WITHIN( -- ポリゴンに含まれているか判定
    location, -- 物件の座標（緯度・経度）
    ST_GEOMFROMTEXT(
      ST_ASTEXT(ST_BUFFER( -- ST_BUFFER
        ST_GEOMFROMTEXT(
          ST_ASTEXT(
            ST_GEOMFROMTEXT(
              CONCAT('POINT(', '35.682078', ' ', '139.773516', ')'), 4326) -- 中心座標
          )
        ),
        800 * 180.0 / 3.141592653589793 / 6378137.0
      )),
      4326
    )
  )
;

こちらも同様にAIに対して高速化のための改善提案を依頼してみます。

🤖AIの回答はこちら

クエリが遅い理由と改善案

1. 過度にネストされたジオメトリ関数
   • ST_ASTEXT(ST_GEOMFROMTEXT(...)) → ST_GEOMFROMTEXT(ST_ASTEXT(...)) のような二重処理がされており、無駄なオーバーヘッドがあります。
2. ジオメトリのインデックスが効かない可能性
   • ST_WITHIN(..., ST_GEOMFROMTEXT(...)) のように、右辺に動的生成されたジオメトリを渡すと、空間インデックスがうまく使えないことがあります。
3. 関数がWHERE句でラップされていて最適化されづらい
   • ST_WITHIN や ST_BUFFER の中身が関数ネストされていると、PostgreSQLがインデックスを使えずフルスキャンになります。

不要なGIS関数のネストによって、このテーブルに張ってある空間インデックスが効いていない可能性がありますね。ここは言われた通りに直してみます（改善案１）

-- 改善案 1: GIS関数の過度なネストの解消
...
WHERE
  ...
  AND ST_Within(
    location,
    ST_SRID(
      ST_BUFFER(
        ST_GeomFromText('POINT(139.700464 35.689729)'),
        '0.007186522272965172' -- application側で事前に計算して埋め込む
      ),
      4326
    )
  )
...

しかしこれだけでは満足するレベルの高速化が達成できませんでした。実はこのクエリでは複数の地点を指定することができて、その場合はそれぞれの地点から800m以内に存在する物件の和集合を取得するという仕様になっています（下記参照）。クエリログを調査した結果、このパターンで特に実行時間が長くなることがわかりました。追加のコンテキストを伝えた上で、改善の余地はないか再度AIに質問してみます。

-- 複数のGIS関数が OR句で結合されている例
...
WHERE
  ...
  AND (
    ST_Within(
      ...
    )
    OR ST_Within(
      ...
    )
    OR ST_Within(
      ...
    )
  )
...

🤖AIの回答はこちら

✅ 改善方針

• 改善案2-A：事前に ST_Buffer() を変数に展開し、MBRContains で粗フィルタ＋精密に絞る
• 改善案2-B：完全に矩形検索に切り替える
  • 「距離 ≒800m」での高精度が不要なら、簡単に矩形範囲を指定して高速化できます。
• 改善案2-C：OR を避けて UNION ALL に分解
  • OR を UNION ALL に分解することで、各SELECTが独立して最適化され、インデックスが効く可能性が大幅アップします。

どれも一定の効果はありそうと思いつつ、特に指定する地点の数を増やしたときのパフォーマンス低下が顕著だったことを考慮し、ここでは改善案2-Cを採用して取り組んでみます。

最終的にはクエリはこのようになりました。

-- 改善案2-C GIS関数の処理をCTEに切り出し、UNION ALLを利用して処理の並列化を実現する
WITH station_filtered_buildings AS (
  SELECT * FROM buildings WHERE
    ST_Within(
      location,
      ST_SRID(
        ST_BUFFER(
          ST_GeomFromText('POINT(139.708464 35.689729)'),
          '0.007186522272965172'
        ),
        4326
      )
    )

  UNION ALL

  SELECT * FROM buildings WHERE
    ST_Within(
      location,
      ST_SRID(
        ST_BUFFER(
          ST_GeomFromText('POINT(139.710846 35.735256)'),
          '0.007186522272965172'
        ),
        4326
      )
    )

  UNION ALL

  SELECT * FROM buildings WHERE
    ST_Within(
      location,
      ST_SRID(
        ST_BUFFER(
          ST_GeomFromText('POINT(139.777254 35.713768)'),
          '0.007186522272965172'
        ),
        4326
      )
    )
)
SELECT
  building_id,
  address,
  ...
FROM station_filtered_buildings;

実行時間を比較してみると、UNION ALLにしたことでパフォーマンスが劇的に改善されていることがわかります。

	1地点を指定	3地点を指定
修正前	0.164 sec	55.490 sec
改善案1: GIS関数のネスト解消	0.199 sec	55.879 sec
改善案2-C: UNION ALL 適用	0.061 sec	0.569 sec

まとめ

今回は生成AIの助けを借りてクエリのボトルネックを調査し、パフォーマンス改善を達成した例を２つ紹介しました。得た学びをクエリチューニングと生成AIの二つの軸でそれぞれ振り返ってみました。

クエリチューニングの学び

並列処理が可能なクエリの書き方を学ぶことで、オプティマイザがより良い実行計画を立てやすくなることが大きな学びです。クエリの詳細は、具体のDBの実装にもよるため比較的アドホックな知識ではありますが、こういった事例を集めて自分の中に引き出しを増やしておくことで、パフォーマンスの課題に対して最小限で効果的な選択ができるようになると思います。

生成AIの活用と加速する学習

生成AIの活用が増えていく中で、責任の所在である人間の判断速度がボトルネックになっていくのを感じています。この記事のクエリチューニングでもAIが改善案を提示し、そこから私が選択するという判断を繰り返して前に進んでいます。生成AIの真価を発揮するためには人間も学習し、日々知識と経験のアップデートを高速に行う必要があると感じました。

自然言語による生成AIとの対話は、知りたいことを最短経路で学べるとても良い学習方法です。その一方で、本来知っておくべき知識をAIが言及するとは限らないため、体系的な知識を獲得するのはあまり向いていないかもしれないと思いました。また、知りたいことだけを高速に一通り学び終えると、つい満足して学習を止めてしまう可能性もあります。重要なのは学習する内容についての信頼できる情報源を確保することです。その上で、必要に応じて生成AIを活用して効率よく学び、学習サイクルを繰り返して習慣にしていくことがこれからの学習の仕方として良さそうだと私は思いました。

最後に

estieでは全社で生成AIの活用に取り組んでおり、日々社内のいたる所で生成AIによる新しいビジネス価値の創出や大幅な業務効率化が起きています。生成AIでエンジニアリングの可能性を広げたい方、ぜひ一緒にestieで働きましょう！ご応募お待ちしております。

hrmos.co