query-optimization

クエリの最適化

このスキルでは、AI エージェントが遅いデータベースクエリを診断して修正できます。エージェントは EXPLAIN／EXPLAIN ANALYZE を使ってクエリの実行計画を解釈し、不足しているインデックスや非効率なスキャンパターンを特定します。パフォーマンスのボトルネックを解消するようクエリを書き換え、ORM の N+1 クエリ問題を検出して解決し、クエリのパフォーマンスを継続的に追跡するモニタリングツールを推奨します。実行前後の証拠に基づいた、実用的で測定可能な改善に重点を置いています。

ワークフロー

1. 遅いクエリを特定する： スロークエリログ、アプリケーションパフォーマンスモニタリング（APM）ツール、またはユーザーからの報告から、問題のクエリを収集します。現在の実行時間、対象となるテーブルのサイズ、クエリの実行頻度を記録します。実行頻度の高い遅いクエリは、まれにしか実行されないクエリより優先して対応します。

2. 実行計画を分析する： クエリに対して EXPLAIN ANALYZE（PostgreSQL）または EXPLAIN FORMAT=JSON（MySQL）を実行し、実際の実行計画を取得します。大きなテーブルに対するシーケンシャルスキャン、推定行数が多いネステッドループ結合、インデックスのない列に対するソート処理、推定行数と実際の行数の大きな差に注目します。

3. 最適化の機会を特定する： 実行計画に基づいて、具体的な修正を特定します。WHERE、JOIN、ORDER BY 句で使われる列にインデックスを追加する、サブクエリを JOIN に書き換える、SELECT * を必要な列の指定に置き換える、適切な場合は LIMIT 句を追加する、テーブルのルックアップを避けるためにカバリングインデックスを使う、冗長または重複した条件を取り除く、といった対応が考えられます。

4. 最適化を適用する： 必要なインデックスを作成し、クエリを書き換えるか、ORM の使い方を調整します。N+1 問題には、遅延読み込みから先行読み込みに切り替えます（例：Django の select_related／prefetch_related、Prisma の include、SQLAlchemy の joinedload）。各変更の効果を個別に測定できるよう、一度に適用する変更は1つにします。

5. 測定して検証する： 最適化したクエリに対して EXPLAIN ANALYZE を再実行し、実行時間、スキャンした行数、実行計画の構造を元のクエリと比較します。クエリが同一の結果を返すことを確認します。新しいインデックスによって書き込みパフォーマンスが許容範囲を超えて低下しないことも確認します。

6. 継続的なモニタリングを設定する： 適切なしきい値でスロークエリログを設定します（例：PostgreSQL では log_min_duration_statement を使って 100ms に設定）。pg_stat_statements、Datadog、Grafana などのモニタリングツールと連携し、クエリのパフォーマンスの推移を追跡して、性能の劣化を早期に検知します。

対応技術

• PostgreSQL： EXPLAIN ANALYZE、pg_stat_statements、pg_stat_user_indexes、auto_explain
• MySQL： EXPLAIN FORMAT=JSON、Performance Schema、スロークエリログ、pt-query-digest
• ORM： SQLAlchemy、Django ORM、Prisma、ActiveRecord、Sequelize、TypeORM
• モニタリング： pganalyze、Datadog APM、New Relic、Grafana + Prometheus

使い方

遅い SQL クエリ（または ORM の処理の説明）と、データベースの種類、テーブルのおおよそのサイズを提示してください。可能であれば、現在の EXPLAIN 出力も含めてください。エージェントは実行計画を分析し、具体的な最適化を推奨して、書き換えたクエリとインデックス作成文を提示します。ORM コードを確認して N+1 パターンを検出し、先行読み込みによる修正を提案することもできます。

例

例1：遅い JOIN クエリの最適化

問題： orders と users、products を結合するレポート用クエリが、orders テーブルの行数が50万件の環境で4.2秒かかります。

元のクエリと EXPLAIN：

EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'shipped'
  AND o.ordered_at >= '2025-01-01';

Nested Loop  (cost=0.00..98452.30 rows=12340 width=892) (actual time=0.08..4201.33 rows=11842 loops=1)
  -> Seq Scan on orders o  (cost=0.00..15420.00 rows=24500 width=64) (actual time=0.04..1823.12 rows=24312 loops=1)
       Filter: ((status = 'shipped') AND (ordered_at >= '2025-01-01'))
       Rows Removed by Filter: 475688
  -> Index Scan using order_items_order_id_idx on order_items oi  (...)
Planning Time: 0.45 ms
Execution Time: 4201.88 ms

診断： orders（50万行）に対するシーケンシャルスキャンで、status と ordered_at による絞り込みが行われています。これらの絞り込み列を組み合わせた複合インデックスはありません。また、必要な列が一部だけであるにもかかわらず、すべての列を取得しています。

修正 — 複合インデックスを追加してクエリを書き換える：

-- Create composite index matching the WHERE clause
CREATE INDEX idx_orders_status_ordered_at ON orders(status, ordered_at);

-- Rewrite query with specific columns
EXPLAIN ANALYZE
SELECT o.id AS order_id, u.full_name, u.email,
       p.name AS product_name, oi.quantity, oi.unit_price,
       o.ordered_at
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'shipped'
  AND o.ordered_at >= '2025-01-01';

最適化後の EXPLAIN：

Nested Loop  (cost=1.12..3842.56 rows=12340 width=198) (actual time=0.06..87.42 rows=11842 loops=1)
  -> Index Scan using idx_orders_status_ordered_at on orders o  (cost=0.42..892.15 rows=24500 width=24) (actual time=0.03..12.68 rows=24312 loops=1)
       Index Cond: ((status = 'shipped') AND (ordered_at >= '2025-01-01'))
  -> Index Scan using order_items_order_id_idx on order_items oi  (...)
Planning Time: 0.52 ms
Execution Time: 88.04 ms

結果： シーケンシャルスキャンをインデックススキャンに置き換え、必要な列だけを指定して転送データを減らしたことで、実行時間は4,201msから88msに短縮されました（48倍の改善）。

例2：Django ORM アプリケーションの N+1 クエリを修正する

問題： ユーザー名と商品詳細を含む 100 件の注文を一覧表示するビューが、201 件の SQL クエリ（注文に 1 件＋ユーザーに 100 件＋商品に 100 件）を生成し、処理に 1.8 秒かかります。

修正前 — N+1 パターン：

# views.py — Triggers N+1 queries
def order_list(request):
    orders = Order.objects.filter(status="shipped").order_by("-ordered_at")[:100]
    results = []
    for order in orders:
        results.append({
            "id": order.id,
            "customer": order.user.full_name,       # Lazy load: 1 query per order
            "items": [
                {"product": item.product.name, "qty": item.quantity}
                for item in order.items.all()        # Lazy load: 1 query per order
            ],
        })
    return JsonResponse(results, safe=False)

Django Debug Toolbar の出力： 1,823ms で 201 件のクエリ。

修正後 — select_related と prefetch_related による先行読み込み：

# views.py — Fixed with eager loading
def order_list(request):
    orders = (
        Order.objects
        .filter(status="shipped")
        .select_related("user")                      # JOIN for user (1:1/FK)
        .prefetch_related("items__product")           # Prefetch items + products (1:N)
        .order_by("-ordered_at")[:100]
    )
    results = []
    for order in orders:
        results.append({
            "id": order.id,
            "customer": order.user.full_name,         # No extra query
            "items": [
                {"product": item.product.name, "qty": item.quantity}
                for item in order.items.all()          # No extra query
            ],
        })
    return JsonResponse(results, safe=False)

Django Debug Toolbar の出力： 42ms で 3 件のクエリ。

結果： クエリ数は 201 件から 3 件に減少し、応答時間は 1,823ms から 42ms に短縮されました（43 倍の改善）。select_related はユーザー FK に対して SQL JOIN を使用し、prefetch_related はすべての注文項目とその商品を取得するために単一の IN クエリを発行します。

ベストプラクティス

• EXPLAIN だけでなく、必ず EXPLAIN ANALYZE を使用してください — ANALYZE 付きの形式はクエリを実行し、実際の行数と所要時間を表示します。これらは推定値と大きく異なることが多く、実際のボトルネックを明らかにします。
• WHERE + ORDER BY のパターンに合った複合インデックスを作成してください — (status, ordered_at) の複合インデックスは、各列に個別のインデックスを作成するよりはるかに効果的です。データベースが単一のインデックス範囲スキャンを使用できるためです。
• 本番環境のクエリでは SELECT * を避けてください — すべての列を選択すると、データベースは幅の広い行を読み取る必要があり、I/O が増加し、カバリングインデックスを使用できなくなります。必要な列だけを必ず指定してください。
• ORM レベルで N+1 問題を修正してください — select_related（Django）、joinedload（SQLAlchemy）、include（Prisma）、または includes（ActiveRecord）を使用して、関連オブジェクトの読み込みを数百件ではなく 1〜2 件のクエリにまとめてください。
• クエリのパフォーマンスを継続的に監視してください — PostgreSQL では pg_stat_statements、MySQL では Performance Schema を有効にして、個々のクエリの実行時間だけでなく、合計実行時間に基づいて最も時間のかかるクエリを追跡してください。
• インデックスが書き込みに与える影響をテストしてください — インデックスを追加すると読み取りは速くなりますが、書き込み（INSERT、UPDATE、DELETE）は遅くなります。インデックスを追加した後、書き込み負荷の高い操作をベンチマークし、トレードオフが許容できることを確認してください。

エッジケース

• 統計情報のずれによる不適切な実行計画： テーブルのデータが大きく変化した場合（たとえば、大規模なデータインポート後）、クエリプランナーが古い統計情報を使用することがあります。統計情報を更新して正確な実行計画を得るため、ANALYZE（PostgreSQL）または ANALYZE TABLE（MySQL）を実行してください。
• 更新頻度の高いテーブルでのインデックスの肥大化： 更新や削除が頻繁なテーブルでは、インデックスが肥大化してパフォーマンスが低下することがあります。領域を再利用するために、定期的に REINDEX（PostgreSQL）または OPTIMIZE TABLE（MySQL）をスケジュールしてください。
• ビューに隠れた相関サブクエリ： 単純に見えるクエリでも、行ごとに 1 回実行される相関サブクエリを含むビューを参照している場合があります。完全な実行計画を確認するため、EXPLAIN の分析では必ずビューの定義を展開してください。
• パラメーターのスニッフィング／実行計画のキャッシュ： あるパラメーター値用にキャッシュされた実行計画が、別の値では低いパフォーマンスになることがあります。PostgreSQL では、パラメーターの選択性が大きく変動するクエリに対し、PREPARE／EXECUTE を使用するか、plan_cache_mode = force_custom_plan を設定してください。
• 不要な JOIN を含む ORM 生成クエリ： ORM は、INNER JOIN で十分な場合にも LEFT JOIN を生成したり、不要なサブクエリを追加したりすることがあります。実際の SQL を確認するには QuerySet.query（Django）または .toSQL()（Knex）を使用し、ORM の出力が最適でない場合は生のクエリで置き換えてください。