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

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

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

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

-- ✅ 良い
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';

-- テーブル
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';

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

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

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

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

-- ✅ 良い（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);

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

-- テーブル
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;

-- ❌ 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

-- 状況によるが、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
);

-- ❌ 全件取得
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;

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

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

-- 拡張有効化
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;

-- 設定確認
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 秒以上をログ

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

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

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

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

-- ❌ 両方の列にインデックスあっても使われない場合
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;

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

-- 統計情報更新
ANALYZE users;

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

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

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