SQLクエリ最適化：パフォーマンスベストプラクティスガイド

1-- 実行プランを表示 (PostgreSQL)
2EXPLAIN ANALYZE 
3SELECT u.name, COUNT(o.id) as order_count
4FROM users u
5LEFT JOIN orders o ON u.id = o.user_id
6WHERE u.created_at > '2023-01-01'
7GROUP BY u.id, u.name
8ORDER BY order_count DESC;
9
10-- 出力の解釈:
11-- Seq Scan = テーブルスキャン (大きなテーブルでは遅い)
12-- Index Scan = インデックスを使用 (高速)
13-- Hash Join = 効率的な結合方法
14-- Sort = 高コストな操作

1-- ❌ SELECT *を避ける
2SELECT * FROM products WHERE category = 'electronics';
3
4-- ✅ 必要な列のみ選択
5SELECT id, name, price FROM products WHERE category = 'electronics';
6
7-- ❌ 非効率なWHERE句
8SELECT * FROM orders WHERE YEAR(order_date) = 2024;
9
10-- ✅ 範囲条件を使用
11SELECT * FROM orders 
12WHERE order_date >= '2024-01-01' 
13AND order_date < '2025-01-01';

1-- ❌ 相関サブクエリ (各行で実行)
2SELECT u.name, u.email
3FROM users u
4WHERE EXISTS (
5    SELECT 1 FROM orders o 
6    WHERE o.user_id = u.id 
7    AND o.order_date > '2024-01-01'
8);
9
10-- ✅ JOINの方がしばしば効率的
11SELECT DISTINCT u.name, u.email
12FROM users u
13INNER JOIN orders o ON u.id = o.user_id
14WHERE o.order_date > '2024-01-01';
15
16-- ✅ 注文データが不要ならEXISTSがさらに良い
17SELECT u.name, u.email
18FROM users u
19WHERE u.id IN (
20    SELECT DISTINCT o.user_id 
21    FROM orders o 
22    WHERE o.order_date > '2024-01-01'
23);

1-- ✅ インデックス化された列で結合
2SELECT u.name, o.total
3FROM users u
4INNER JOIN orders o ON u.id = o.user_id  -- 両方ともインデックス化されているべき
5WHERE u.status = 'active';
6
7-- ✅ 結合サイズを減らすために早期フィルタリング
8SELECT u.name, o.total
9FROM (
10    SELECT id, name FROM users WHERE status = 'active'
11) u
12INNER JOIN orders o ON u.id = o.user_id
13WHERE o.order_date > '2024-01-01';
14
15-- ✅ 適切な結合順序を使用 (小さなテーブルを先に)
16-- データベースオプティマイザーが通常処理するが、意識しておく
17
18-- ❌ 不要なJOINを避ける
19SELECT u.name, u.email, COUNT(o.id)
20FROM users u
21LEFT JOIN orders o ON u.id = o.user_id
22WHERE u.created_at > '2024-01-01'
23GROUP BY u.id, u.name, u.email;
24
25-- ✅ 適切な場合はウィンドウ関数を使用
26SELECT u.name, u.email,
27       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
28FROM users u
29WHERE u.created_at > '2024-01-01';

1-- PostgreSQL: クエリログを有効化
2-- postgresql.conf内:
3log_statement = 'all'
4log_min_duration_statement = 1000  -- 1秒超えのクエリをログ
5
6-- MySQL: スロークエリログを有効化
7-- my.cnf内:
8slow_query_log = 1
9long_query_time = 1
10log_queries_not_using_indexes = 1
11
12-- スロークエリを検索 (PostgreSQL)
13SELECT query, calls, total_time, mean_time
14FROM pg_stat_statements
15ORDER BY total_time DESC
16LIMIT 10;
17
18-- スロークエリを検索 (MySQL)
19SELECT * FROM mysql.slow_log
20ORDER BY start_time DESC
21LIMIT 10;

1-- Application-level caching
2const getCachedUserOrders = async (userId) => {
3  const cacheKey = `user_orders_${userId}`;
4  let result = await cache.get(cacheKey);
5  
6  if (!result) {
7    result = await db.query(`
8      SELECT * FROM orders 
9      WHERE user_id = ? 
10      ORDER BY created_at DESC
11    `, [userId]);
12    
13    await cache.set(cacheKey, result, 300); // 5 minute cache
14  }
15  
16  return result;
17};
18
19-- Database-level query caching (MySQL)
20SET query_cache_type = ON;
21SET query_cache_size = 268435456; -- 256MB

1-- PostgreSQL table partitioning by date
2CREATE TABLE orders_2024 PARTITION OF orders
3FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
4
5CREATE TABLE orders_2023 PARTITION OF orders
6FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
7
8-- Queries automatically use appropriate partition
9SELECT * FROM orders 
10WHERE order_date >= '2024-06-01' 
11AND order_date < '2024-07-01';