SQL クエリ最適化の基礎——実行計画の読み方、インデックス設計、パフォーマンスチューニング

Lab AI SQL クエリ最適化の基礎——実行計画の読み方、インデックス設計、パフォーマンスチューニング

この記事の内容

SQL クエリのパフォーマンスは、アプリケーションの応答速度に直結します。本記事では、実行計画の読み解き方から、インデックス設計、具体的なチューニング手法までを解説します。

クエリ最適化の基本概念

クエリ最適化とは

クエリ最適化とは、SQL クエリを効率的に実行するための変換プロセスです。

【クエリ最適化のステップ】
1. パース（構文解析）
   SELECT * FROM users WHERE id = 1

2. 意味解析（スキーマ検証）
   └─ users テーブル存在確認、id カラム存在確認

3. クエリ変換（最適化ルール適用）
   WHERE id = 1 → インデックス使用

4. 実行計画生成（コストベース）
   └─ インデックススキャン vs フルスキャン

5. 実行

最適化の重要性

【パフォーマンス差の例】

最適化前：
・1 クエリ：2 秒
・100 クエリ/秒 → 200 秒（3 分以上）
・ユーザー離脱、DB 負荷増大

最適化後：
・1 クエリ：10ms
・100 クエリ/秒 → 1 秒
・快適なユーザー体験

実行計画の読み方

実行計画とは

**実行計画（Execution Plan）**は、データベースがクエリをどのように実行するかを示す「設計図」です。

PostgreSQL の場合（EXPLAIN）

-- 基本構文
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 実際のコストを含める
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

出力例：

Seq Scan on users  (cost=0.00..35.50 rows=1 width=100)
  Filter: (email = 'test@example.com'::text)
  Actual time=0.050..0.050 rows=1 loops=1
  Planning Time: 0.100 ms
  Execution Time: 0.060 ms

実行計画のキー項目

項目	説明	見方
Seq Scan	フルテーブルスキャン	遅い、避けたい
Index Scan	インデックススキャン	良い
Index Only Scan	インデックスのみ（高速）	最適
cost=0.00..35.50	開始〜終了コスト	低いほど良い
rows=1	推定行数	実際との差を確認
Actual time	実際の実行時間	推定との乖離を確認

MySQL の場合（EXPLAIN）

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

出力例：

| id | select_type | table | type  | key     | key_len | ref   | rows | Extra       |
|----|-------------|-------|-------|---------|---------|-------|------|-------------|
|  1 | SIMPLE      | users | index | idx_email | 255    | const |    1 | Using where |

type の見方（重要度順）

type	説明	速度
system/const	主キー等価検索	最速
eq_ref	主キー結合	非常に速い
ref	非一意インデックス	速い
range	インデックス範囲検索	良い
index	インデックスフルスキャン	やや遅い
ALL	フルテーブルスキャン	遅い

Extra の見方

Extra	説明	評価
Using index	カバリングインデックス	最適
Using where	WHERE フィルタ適用	普通
Using filesort	ソート発生	要注意
Using temporary	一時表使用	要注意
Using index condition	インデックス条件プッシュダウン	良い

インデックス設計の原則

インデックスの仕組み

B ツリーインデックスが一般的です。

        [50]
       /    \
    [20]    [80]
    /  \    /  \
  [10][30][60][90]

・検索：O(log n)
・挿入：O(log n)
・削除：O(log n)

インデックス設計 5 つの原則

原則 1: WHERE 句で使用される列に作成

-- ✅ 良い
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'test@example.com';

-- ❌ 遅い（インデックス無効）
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ 良い（インデックス有効）
SELECT * FROM users
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

原則 2: 複合インデックスは列順序が重要

-- テーブル
orders(user_id, status, created_at, ...)

-- ✅ 良い（WHERE 句の順序に合わせる）
CREATE INDEX idx_user_status ON orders(user_id, status);
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- ❌ 悪い（user_id しか使われない）
CREATE INDEX idx_status_user ON orders(status, user_id);
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

左端原則: 複合インデックス (A, B, C) は、A、A+B、A+B+C の組み合わせでのみ効果的。

原則 3: 選択度の高い列を前に

-- 選択度 = 固有値の数 / 総行数

-- 性別（2 値）: 選択度低い
-- 会員 ID（一意）: 選択度高い

-- ✅ 良い
CREATE INDEX idx_user_id_gender ON orders(user_id, gender);

-- ❌ 悪い
CREATE INDEX idx_gender_user_id ON orders(gender, user_id);

原則 4: ORDER BY / GROUP BY も考慮

-- ✅ 良い（ORDER BY もカバー）
CREATE INDEX idx_created_at ON orders(created_at DESC);
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;

-- ✅ 良い（複合でソートも最適化）
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

原則 5: カバリングインデックスを検討

-- カバリングインデックス：必要なデータ全てをインデックスでカバー

-- テーブル
orders(user_id, product_id, quantity, price, created_at)

-- ✅ カバリングインデックス
CREATE INDEX idx_user_product_qty ON orders(user_id, product_id, quantity);

-- インデックスのみで処理完了（Index Only Scan）
SELECT user_id, product_id, quantity FROM orders WHERE user_id = 123;

具体的なチューニング手法

1. N+1 問題の解消

-- ❌ N+1 問題（アプリケーション層でループ）
users = User.all
users.each do |user|
  orders = Order.where(user_id: user.id)  -- N 回クエリ
end

-- ✅ 1 クエリ（JOIN）
SELECT users.*, orders.*
FROM users
JOIN orders ON users.id = orders.user_id;

-- ✅ Rails なら includes
User.includes(:orders).all

2. EXISTS vs IN の選択

-- 状況によるが、EXISTS が有利な場合が多い

-- ✅ EXISTS（相関サブクエリ、短絡評価）
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- ⚠️ IN（サブクエリ結果を全て評価）
SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
);

3. LIMIT の活用

-- ❌ 全件取得
SELECT * FROM logs WHERE created_at > '2024-01-01';

-- ✅ 必要件数だけ
SELECT * FROM logs
WHERE created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;

4. インデックスヒント（最終手段）

-- MySQL
SELECT * FROM users USE INDEX (idx_email)
WHERE email = 'test@example.com';

-- PostgreSQL（通常不要、オプティマイザが優秀）
-- SET enable_seqscan = off; -- デバッグ用

パフォーマンス測定とモニタリング

PostgreSQL: pg_stat_statements

-- 拡張有効化
CREATE EXTENSION pg_stat_statements;

-- 実行統計確認
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

MySQL: Slow Query Log

-- 設定確認
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 設定変更
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.0;  -- 1 秒以上をログ

一般的なパフォーマンス目標

メトリクス	目標値	許容値
単純クエリ	< 10ms	< 50ms
結合クエリ	< 50ms	< 200ms
集計クエリ	< 100ms	< 1s
バッチ処理	-	< 1 時間

アンチパターン

❌ 1: 関数インデックスの不使用

-- ❌ インデックス使われない
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- ✅ 関数インデックス作成
CREATE INDEX idx_email_lower ON users(LOWER(email));

❌ 2: 部分一致検索の濫用

-- ❌ 先頭ワイルドカード（インデックス無効）
SELECT * FROM products WHERE name LIKE '%phone%';

-- ✅ 全文検索インデックス使用
-- PostgreSQL: tsvector
-- MySQL: FULLTEXT INDEX

❌ 3: OR の誤用

-- ❌ 両方の列にインデックスあっても使われない場合
SELECT * FROM orders
WHERE user_id = 123 OR status = 'pending';

-- ✅ UNION ALL で分離
SELECT * FROM orders WHERE user_id = 123
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 123;

❌ 4: インデックスの張りすぎ

インデックスのトレードオフ:
・ SELECT 高速化 ✅
・ INSERT/UPDATE/DELETE 遅延 ❌
・ディスク容量増加 ❌
・メンテナンスコスト増 ❌

目安：1 テーブルあたり 5-10 個まで

チューニングワークフロー

ステップ 1: ベンチマーク

-- 現在のパフォーマンス測定
EXPLAIN ANALYZE [問題のクエリ];

ステップ 2: 仮説立案

フルスキャンが発生している？
インデックスが古い？
統計情報が古い？
結合順序が最適でない？

ステップ 3: 対策実施

-- 統計情報更新
ANALYZE users;

-- インデックス再構築
REINDEX TABLE users;

-- インデックス追加
CREATE INDEX CONCURRENTLY idx_target ON table(column);

ステップ 4: 検証

-- 改善確認
EXPLAIN ANALYZE [改善後のクエリ];

まとめ

SQL クエリ最適化の核心：

実行計画を読む: EXPLAIN/EXPLAIN ANALYZE を活用
インデックス設計: 列順序、選択度、カバリングを考慮
アンチパターン回避: N+1、関数使用、部分一致に注意
継続的モニタリング: スロークエリログで問題検知
計測と仮説: 推測ではなく、データに基づき最適化

「最適化は計測から始まる。推測で最適化するな」

パフォーマンスチューニングは、継続的なプロセスです。定期的なモニタリングと改善が、安定したシステム運用を支えます。

参考資料

PostgreSQL ドキュメント - EXPLAIN
MySQL ドキュメント - EXPLAIN
「SQL パフォーマンステューニング」オライリー
「Database Internals」O'Reilly
Use The Index, Luke: https://use-the-index-luke.com/

#SQL #パフォーマンス #データベース #クエリ最適化

免責事項 — 掲載情報は執筆時点のものです。料金・機能は変更される場合があります。最新情報は各公式サイトをご確認ください。