Zenn
👏

AIを活用してSQLクエリの品質を担保したい

に公開
SQL
GitHub Actions
ORM
パフォーマンス改善
ai開発
tech

tl;dr

  • AIにSQLクエリのパフォーマンスレビューを担当させた
  • プロダクション環境相当のメタデータも与えると詳細なレビューが受けられるようになった
  • メタデータだけ見るのでそこそこの速度でチェックしてくれる(CIに組み込みやすい）

※ コードはほぼAI、記事は半分くらい

はじめに

ORMは非常に便利ですが、パフォーマンスの落とし穴に気づきにくいことがあります。
「開発中は快適だったのに、本番環境でデータが増えたら途端に遅くなった…」そんな経験をしたことがある方も少なくないのではないでしょうか？

この記事は、CIでAIにクエリパフォーマンスのレビューを担当させる検証を行ったものになります。
※記事中で利用しているモデルはClaude Opus 4です。

なぜこの仕組みが必要になったのか？

プロジェクトでは、TypeScript x Prisma、PHP x Eloquentというような、ORMが利用されています。ORMはSQLを直接書かなくても直感的にデータベースを操作できる協力なツールです。しかし、その裏では意図しない非効率なクエリが生成されていることがあります。（特に経験が浅いメンバーはクエリのイメージが付きづらくい）

例

  • N+1問題: ループ処理の中で、都度クエリを発行してしまう典型的な問題。データ量が増えるにつれて、パフォーマンスが致命的に悪化します。
  • 非効率なJOIN: 不必要なテーブルをJOINしていたり、JOINの順番が最適でなかったりするケース。
  • インデックスの不使用: WHERE句で指定したカラムにインデックスが効いておらず、テーブルをフルスキャンしてしまっている状態。

これらの問題は、コードを一見しただけでは見抜きにくく、コードレビューで指摘するにもレビュワーのスキルや経験に依存してしまい、属人化しやすいという課題がありました。

レビューフローの概要

  • この仕組みは、以下の流れで動作します。
  1. 開発者がソースコードの変更を含むPRを作成します。
  2. PRの作成をトリガーに、GitHub Actionsのワークフローが起動します。
  3. ワークフローは、まず変更されたコードの差分（diff）を取得します。
  4. 同時に、データベースのスキーマ情報（テーブル定義、インデックス、カラム型など）をテキスト形式で準備します。
  5. 「コード差分」と「スキーマなどのメタデータ情報」をLLMに渡し「クエリパフォーマンスのレビュー」を依頼します。
  6. LLMは、与えられた情報を基にコードを分析し、潜在的な問題点と改善案を生成します。
  7. 最後に、生成されたレビュー結果をPR上にコメントとして投稿します。

これにより、開発者はPRを作成するだけで、第三者（AI）からの客観的なパフォーマンスレビューを受け取ることができるのです。

レビューのポイント

LLMに精度の高い分析をしてもらうには、「どのような情報を与えるか」が極めて重要です。
人間がレビューする際も、コードの変更点だけでなく、関連するデータベースのテーブル定義がなければ、的確な指摘はできません。

  • スキーマファイル (schema.prisma) だけでなく、MySQLから直接取得した、詳細なメタデータ情報をLLMに与えています。

作成したメタデータの例

### `inventory` (4581 rows)

#### Columns
| # | Column | Type | Null | Key | 
| --- | --- | --- | --- | --- | 
| 1 | inventory_id | mediumint unsigned | NO | PRI | 
| 2 | film_id | smallint unsigned | NO | MUL | 
| 3 | store_id | tinyint unsigned | NO | MUL | 
| 4 | last_update | timestamp | NO | NULL | 

#### Indexes
| Index | Columns | Type | Unique | Cardinality | Selectivity % | 
| --- | --- | --- | --- | --- | --- | 
| idx_fk_film_id | film_id | BTREE | NO | 958 | 20.91 | 
| idx_store_id_film_id | store_id,film_id | BTREE | NO | 1521 | 33.20 | 
| PRIMARY | inventory_id | BTREE | UNIQUE | 4581 | 100.00 | 

#### Foreign Keys
| FK Name | Column | References | 
| --- | --- | --- | 
| fk_inventory_film | film_id | film.film_id | 
| fk_inventory_store | store_id | store.store_id |

...

## Index Effectiveness Summary

*Indexes with selectivity < 30%*

| Table | Index | Columns | Cardinality | Rows | Selectivity % | 
| --- | --- | --- | --- | --- | --- | 
| inventory | idx_fk_film_id | film_id | 958 | 4581 | 20.91 | 
...

このように、カラムの型、NULL制約、デフォルト値、そしてインデックスの情報をテキストとして渡すことで、LLMは「このクエリはインデックスを使えるか？」といった、より踏み込んだ分析が可能になりました。

レビューの例

対象のコード

export async function sampleCode() {
  const customers = await prisma.customer.findMany({
    where: {
      last_name: {
        contains: 'son',
      }
    },
    select: {
      customer_id: true,
      first_name: true,
      last_name: true,
    }
  })
  
  console.log(`結果: ${customers.length}件`)
}

メタデータなし

⏺ このコードのクエリパフォーマンスに関する主な問題点：

  1. 部分一致検索の使用 (10-12行目):
    - contains: 'son' は SQL で LIKE '%son%' に変換され、先頭ワイルドカードのためインデックスが使用されません
    - last_name カラムにインデックスがあってもフルテーブルスキャンになります
  2. 改善案:
    - 前方一致検索に変更: startsWith: 'son' (SQL: LIKE 'son%')
    - 全文検索インデックスの利用
    - データ量が多い場合はページネーションの実装

  パフォーマンステストで確認すべき点：
  - 実行計画の確認 (EXPLAIN)
  - last_name カラムのインデックス有無
  - テーブルのレコード数による影響

メタデータあり

⏺ このコードのクエリパフォーマンスに関する主な問題点：

  🚨 Performance Issue: インデックスが使用されないLIKEパターン

  Location: src/examples/02-sample-code.ts:11

  Issue: contains: 'son'はLIKE '%son%'に変換され、idx_last_nameインデックスが使用されません

  Impact: Medium

  Suggested Fix:
  // Option 1: 前方一致に変更（インデックス使用可能）
  const customers = await prisma.customer.findMany({
    where: {
      last_name: {
        startsWith: 'son',
      }
    },
    select: {
      customer_id: true,
      first_name: true,
      last_name: true,
    }
  })

  // Option 2: 大文字小文字を考慮した前方一致
  const customers = await prisma.customer.findMany({
    where: {
      OR: [
        { last_name: { startsWith: 'son' } },
        { last_name: { startsWith: 'Son' } },
        { last_name: { startsWith: 'SON' } },
      ]
    },
    select: {
      customer_id: true,
      first_name: true,
      last_name: true,
    }
  })

  Explanation:
  - 現在のLIKE '%son%'は599行のフルテーブルスキャンを実行
  - startsWith（LIKE 'son%'）に変更することでidx_last_nameインデックスが使用可能
  - customerテーブルのidx_last_nameは100%の選択性を持つため、インデックス使用で大幅な性能改善が期待できます

このように、問題点、影響度、そして具体的な改善案まで提示してくれるため、メンバーが自律的に修正を行いやすくなりました。

導入の効果

この仕組みを導入したことで、以下のような効果を実感しています。

効果

  • 品質の向上: 開発の初期段階でパフォーマンスボトルネックを特定・修正できるようになり、手戻りが大幅に減少しました。
  • 属人性の解消: パフォーマンスチューニングの知識がチーム全体で共有され、レビュワーの負担も軽減されました。
  • 開発文化の醸成: 「パフォーマンスは後から考える」のではなく、開発初期から意識する文化が根付き始めました。

展望

  • 実行計画（EXPLAIN）との連携: 実際のクエリ実行計画をLLMに与えることで、さらに分析精度を向上できると考えています。（ただし、CIで実行するには重いかも）

メタデータ生成等のサンプルコード

  • github

Discussion