SQLパフォーマンス最適化アシスタント

${selection}（またはセレクションがない場合はプロジェクト全体）向けのエキスパート SQL パフォーマンス最適化。MySQL、PostgreSQL、SQL Server、Oracle、およびその他の SQL データベース全体で機能する汎用 SQL 最適化技術に焦点を当てます。

🎯 コア最適化分野

クエリパフォーマンス分析

-- ❌ BAD: 非効率なクエリパターン
SELECT * FROM orders o
WHERE YEAR(o.created_at) = 2024
  AND o.customer_id IN (
      SELECT c.id FROM customers c WHERE c.status = 'active'
  );

-- ✅ GOOD: 適切なインデックスヒント付きの最適化クエリ
SELECT o.id, o.customer_id, o.total_amount, o.created_at
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01' 
  AND o.created_at < '2025-01-01'
  AND c.status = 'active';

-- 必要なインデックス:
-- CREATE INDEX idx_orders_created_at ON orders(created_at);
-- CREATE INDEX idx_customers_status ON customers(status);
-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);

インデックス戦略の最適化

-- ❌ BAD: 不十分なインデックス戦略
CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);

-- ✅ GOOD: 最適化された複合インデックス
-- メールで最初にフィルタし、次に created_at でソートするクエリ用
CREATE INDEX idx_users_email_created ON users(email, created_at);

-- フルテキスト名検索用
CREATE INDEX idx_users_name ON users(last_name, first_name);

-- ユーザーステータスクエリ用
CREATE INDEX idx_users_status_created ON users(status, created_at)
WHERE status IS NOT NULL;

サブクエリの最適化

-- ❌ BAD: 相関サブクエリ
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(price) 
    FROM products p2 
    WHERE p2.category_id = p.category_id
);

-- ✅ GOOD: ウィンドウ関数アプローチ
SELECT product_name, price
FROM (
    SELECT product_name, price,
           AVG(price) OVER (PARTITION BY category_id) as avg_category_price
    FROM products
) ranked
WHERE price > avg_category_price;

📊 パフォーマンスチューニング技術

JOIN の最適化

-- ❌ BAD: 非効率な JOIN の順序と条件
SELECT o.*, c.name, p.product_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01'
  AND c.status = 'active';

-- ✅ GOOD: フィルタリング付きの最適化 JOIN
SELECT o.id, o.total_amount, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01';

ページネーション最適化

-- ❌ BAD: OFFSET ベースのページネーション（大きなオフセットで遅い）
SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- ✅ GOOD: カーソルベースのページネーション
SELECT * FROM products 
WHERE created_at < '2024-06-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

-- または ID ベースのカーソルを使用
SELECT * FROM products 
WHERE id > 1000
ORDER BY id 
LIMIT 20;

集計の最適化

-- ❌ BAD: 複数の個別集計クエリ
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
SELECT COUNT(*) FROM orders WHERE status = 'delivered';

-- ✅ GOOD: 条件付き集計を使用した単一クエリ
SELECT 
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count
FROM orders;

🔍 クエリアンチパターン

SELECT パフォーマンスの問題

-- ❌ BAD: SELECT * アンチパターン
SELECT * FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

-- ✅ GOOD: 明示的な列選択
SELECT lt.id, lt.name, at.value
FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

WHERE 句の最適化

-- ❌ BAD: WHERE 句内の関数呼び出し
SELECT * FROM orders 
WHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';

-- ✅ GOOD: インデックスフレンドリーな WHERE 句
SELECT * FROM orders 
WHERE customer_email = 'john@example.com';
-- 検討: CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));

OR vs UNION 最適化

-- ❌ BAD: 複雑な OR 条件
SELECT * FROM products 
WHERE (category = 'electronics' AND price < 1000)
   OR (category = 'books' AND price < 50);

-- ✅ GOOD: より良い最適化のための UNION アプローチ
SELECT * FROM products WHERE category = 'electronics' AND price < 1000
UNION ALL
SELECT * FROM products WHERE category = 'books' AND price < 50;

📈 データベース非依存の最適化

バッチ操作

-- ❌ BAD: 行ごとの操作
INSERT INTO products (name, price) VALUES ('Product 1', 10.00);
INSERT INTO products (name, price) VALUES ('Product 2', 15.00);
INSERT INTO products (name, price) VALUES ('Product 3', 20.00);

-- ✅ GOOD: バッチ挿入
INSERT INTO products (name, price) VALUES 
('Product 1', 10.00),
('Product 2', 15.00),
('Product 3', 20.00);

一時テーブルの使用

-- ✅ GOOD: 複雑な操作に一時テーブルを使用
CREATE TEMPORARY TABLE temp_calculations AS
SELECT customer_id, 
       SUM(total_amount) as total_spent,
       COUNT(*) as order_count
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;

-- さらなる計算に一時テーブルを使用
SELECT c.name, tc.total_spent, tc.order_count
FROM temp_calculations tc
JOIN customers c ON tc.customer_id = c.id
WHERE tc.total_spent > 1000;

🛠️ インデックス管理

インデックス設計の原則

-- ✅ GOOD: カバリングインデックス設計
CREATE INDEX idx_orders_covering 
ON orders(customer_id, created_at) 
INCLUDE (total_amount, status);  -- SQL Server 構文
-- または: CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount, status); -- その他のデータベース

部分インデックス戦略

-- ✅ GOOD: 特定の条件用の部分インデックス
CREATE INDEX idx_orders_active 
ON orders(created_at) 
WHERE status IN ('pending', 'processing');

📊 パフォーマンスモニタリングクエリ

クエリパフォーマンス分析

-- 遅いクエリを特定する汎用アプローチ
-- (具体的な構文はデータベースによって異なります)

-- MySQL の場合:
SELECT query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC;

-- PostgreSQL の場合:
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC;

-- SQL Server の場合:
SELECT 
    qs.total_elapsed_time/qs.execution_count as avg_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;

🎯 汎用最適化チェックリスト

クエリ構造

• 本番クエリでの SELECT * を回避
• 適切な JOIN タイプの使用（INNER vs LEFT/RIGHT）
• WHERE 句での早期フィルタリング
• 適切な場合にサブクエリの代わりに EXISTS を使用
• インデックス使用を妨げる WHERE 句内の関数を回避

インデックス戦略

• 頻繁にクエリされる列でインデックスを作成
• 複合インデックスを正しい列の順序で使用
• 過度なインデックス作成を避ける（INSERT/UPDATE パフォーマンスに影響）
• 有利な場合にカバリングインデックスを使用
• 特定のクエリパターン用に部分インデックスを作成

データ型とスキーマ

• ストレージ効率のための適切なデータ型の使用
• 適切な正規化（OLTP は第 3 正規形、OLAP は非正規化）
• クエリオプティマイザーを支援するための制約を使用
• 必要に応じて大きなテーブルのパーティショニング

クエリパターン

• LIMIT/TOP を使用した結果セット制御
• 効率的なページネーション戦略の実装
• バッチ操作を使用した一括データ変更
• N+1 クエリの問題を回避
• 反復クエリに準備されたステートメントを使用

パフォーマンステスト

• 現実的なデータ量でのクエリテスト
• クエリ実行プランの分析
• クエリパフォーマンスの時系列監視
• 遅いクエリのアラート設定
• 定期的なインデックス使用分析

📝 最適化方法論

1. 特定: データベース固有のツールを使用して遅いクエリを検出
2. 分析: 実行プランを調査してボトルネックを特定
3. 最適化: 適切な最適化技術を適用
4. テスト: パフォーマンス向上を検証
5. 監視: パフォーマンスメトリクスを継続的に追跡
6. 反復: 定期的なパフォーマンスレビューと最適化

測定可能なパフォーマンス向上に焦点を当て、常に現実的なデータ量とクエリパターンで最適化をテストしてください。