SQLクエリ最適化の実践テクニック：実行時間を90%短縮する高度な最適化手法
はじめに
データベースのパフォーマンス問題は、アプリケーションの成長とともに必ず直面する課題です。特に大規模なデータを扱う企業では、SQLクエリの最適化が事業の成否を左右することも珍しくありません。
この記事では、実際の現場で使える高度なSQL最適化テクニックを、具体的な事例とともに解説します。
1. 実行計画の深い理解と活用
実行計画の読み方をマスターする
-- PostgreSQLでの実行計画確認
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 100;
実行計画から読み取るべき重要指標
-- コスト分析のポイント
-- 1. Seq Scan vs Index Scan
-- 2. Hash Join vs Nested Loop Join
-- 3. Sort vs Index Sort
-- 4. Buffer Hits vs Buffer Reads
-- 問題のあるクエリの例
EXPLAIN ANALYZE
SELECT * FROM large_table
WHERE UPPER(name) LIKE '%SEARCH%';
-- 最適化後
CREATE INDEX idx_name_upper ON large_table (UPPER(name));
EXPLAIN ANALYZE
SELECT * FROM large_table
WHERE UPPER(name) LIKE '%SEARCH%';
2. インデックス戦略の高度な活用
複合インデックスの効果的な設計
-- 悪い例：個別インデックス
CREATE INDEX idx_user_id ON orders (user_id);
CREATE INDEX idx_status ON orders (status);
CREATE INDEX idx_created_at ON orders (created_at);
-- 良い例：複合インデックス（カーディナリティ順）
CREATE INDEX idx_orders_composite ON orders (status, created_at, user_id);
-- クエリ例
SELECT * FROM orders
WHERE status = 'completed'
AND created_at >= '2024-01-01'
AND user_id = 12345;
部分インデックスによる効率化
-- 全体インデックス（非効率）
CREATE INDEX idx_all_orders ON orders (status);
-- 部分インデックス（効率的）
CREATE INDEX idx_active_orders ON orders (user_id, created_at)
WHERE status IN ('pending', 'processing');
-- 削除済みデータを除外
CREATE INDEX idx_active_users ON users (email)
WHERE deleted_at IS NULL;
関数ベースインデックス
-- 大文字小文字を区別しない検索
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.com');
-- 日付の年月での検索
CREATE INDEX idx_order_year_month ON orders (EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at));
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2024
AND EXTRACT(MONTH FROM created_at) = 7;
3. JOIN最適化の実践テクニック
JOIN順序の最適化
-- 悪い例：大きなテーブルから開始
SELECT u.name, p.title, c.name as category
FROM products p
JOIN users u ON p.user_id = u.id
JOIN categories c ON p.category_id = c.id
WHERE u.active = true
AND p.status = 'published'
AND c.type = 'premium';
-- 良い例：選択性の高い条件から開始
SELECT u.name, p.title, c.name as category
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN users u ON p.user_id = u.id
WHERE c.type = 'premium'
AND p.status = 'published'
AND u.active = true;
EXISTS vs IN vs JOIN の使い分け
-- EXISTS（推奨：大きなサブクエリの場合）
SELECT u.* FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id
AND o.total > 10000
);
-- IN（推奨：小さな結果セットの場合）
SELECT u.* FROM users u
WHERE u.id IN (
SELECT DISTINCT user_id FROM orders
WHERE total > 10000
);
-- JOIN（推奨：結果も必要な場合）
SELECT DISTINCT u.*, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 10000;
4. サブクエリ最適化
相関サブクエリの最適化
-- 悪い例：相関サブクエリ
SELECT u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count,
(SELECT MAX(total) FROM orders o WHERE o.user_id = u.id) as max_order
FROM users u;
-- 良い例：JOINに変換
SELECT u.name,
COALESCE(o.order_count, 0) as order_count,
o.max_order
FROM users u
LEFT JOIN (
SELECT user_id,
COUNT(*) as order_count,
MAX(total) as max_order
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id;
ウィンドウ関数の活用
-- 悪い例：複数のサブクエリ
SELECT u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as total_orders,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id AND o.created_at >= CURRENT_DATE - INTERVAL '30 days') as recent_orders
FROM users u;
-- 良い例：ウィンドウ関数
SELECT DISTINCT u.name,
COUNT(*) OVER (PARTITION BY u.id) as total_orders,
COUNT(*) FILTER (WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days')
OVER (PARTITION BY u.id) as recent_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
5. 大量データ処理の最適化
バッチ処理による効率化
-- 悪い例：一括更新
UPDATE large_table SET status = 'processed'
WHERE created_at < '2024-01-01';
-- 良い例：バッチ処理
DO $$
DECLARE
batch_size INTEGER := 10000;
affected_rows INTEGER;
BEGIN
LOOP
UPDATE large_table
SET status = 'processed'
WHERE id IN (
SELECT id FROM large_table
WHERE created_at < '2024-01-01'
AND status != 'processed'
LIMIT batch_size
);
GET DIAGNOSTICS affected_rows = ROW_COUNT;
EXIT WHEN affected_rows = 0;
COMMIT;
PERFORM pg_sleep(0.1); -- 負荷軽減
END LOOP;
END $$;
パーティショニングの活用
-- 日付ベースのパーティショニング
CREATE TABLE orders_partitioned (
id SERIAL,
user_id INTEGER,
total DECIMAL(10,2),
created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
-- 月別パーティション作成
CREATE TABLE orders_2024_01 PARTITION OF orders_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders_partitioned
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- パーティション除外を活用したクエリ
SELECT * FROM orders_partitioned
WHERE created_at >= '2024-01-15'
AND created_at < '2024-01-20';
6. 統計情報とメンテナンス
統計情報の更新戦略
-- 統計情報の確認
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY last_analyze DESC NULLS LAST;
-- 手動統計更新
ANALYZE orders;
ANALYZE users;
-- 自動統計更新の設定調整
ALTER TABLE large_table SET (autovacuum_analyze_scale_factor = 0.05);
インデックスメンテナンス
-- 未使用インデックスの確認
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- インデックスの再構築
REINDEX INDEX CONCURRENTLY idx_orders_composite;
-- 重複インデックスの確認
SELECT pg_size_pretty(SUM(pg_relation_size(indexrelid))) as total_size,
array_agg(indexname) as duplicate_indexes
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
GROUP BY tablename, indkey
HAVING COUNT(*) > 1;
7. 実践的な最適化事例
事例1：レポート集計クエリの最適化
-- 最適化前（実行時間：45秒）
SELECT
DATE_TRUNC('month', o.created_at) as month,
u.region,
COUNT(*) as order_count,
SUM(o.total) as total_revenue,
AVG(o.total) as avg_order_value
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2023-01-01'
GROUP BY DATE_TRUNC('month', o.created_at), u.region
ORDER BY month, u.region;
-- 最適化後（実行時間：2秒）
-- 1. 適切なインデックス作成
CREATE INDEX idx_orders_reporting ON orders (created_at, user_id, total);
CREATE INDEX idx_users_region ON users (id, region);
-- 2. マテリアライズドビューの活用
CREATE MATERIALIZED VIEW monthly_revenue_by_region AS
SELECT
DATE_TRUNC('month', o.created_at) as month,
u.region,
COUNT(*) as order_count,
SUM(o.total) as total_revenue,
AVG(o.total) as avg_order_value
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2023-01-01'
GROUP BY DATE_TRUNC('month', o.created_at), u.region;
-- 定期更新
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_by_region;
事例2：検索機能の最適化
-- 最適化前：全文検索
SELECT * FROM products
WHERE name ILIKE '%keyword%'
OR description ILIKE '%keyword%';
-- 最適化後：全文検索インデックス
-- 1. 全文検索インデックス作成
ALTER TABLE products ADD COLUMN search_vector tsvector;
UPDATE products SET search_vector =
to_tsvector('japanese', COALESCE(name, '') || ' ' || COALESCE(description, ''));
CREATE INDEX idx_products_search ON products USING GIN(search_vector);
-- 2. 最適化されたクエリ
SELECT * FROM products
WHERE search_vector @@ plainto_tsquery('japanese', 'keyword')
ORDER BY ts_rank(search_vector, plainto_tsquery('japanese', 'keyword')) DESC;
8. モニタリングと継続的改善
パフォーマンス監視
-- 実行時間の長いクエリの特定
SELECT query,
calls,
total_time,
mean_time,
rows
FROM pg_stat_statements
WHERE mean_time > 1000 -- 1秒以上
ORDER BY mean_time DESC
LIMIT 10;
-- ロック待機の監視
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
まとめ
SQLクエリ最適化は、システムパフォーマンス向上の要となる重要なスキルです：
重要なポイント：
- 実行計画の理解：問題の根本原因を特定
- 適切なインデックス設計：クエリパターンに最適化
- JOIN戦略の選択：データ特性に応じた最適化
- 継続的な監視：パフォーマンス劣化の早期発見
これらのテクニックを実践することで、大幅なパフォーマンス改善を実現できます。
次回は、NoSQLデータベースとの使い分けとハイブリッド活用について解説します。
コメント