【SIer向けSQLパフォーマンスチューニングの基本】

業務システムのパフォーマンスにおいて、SQLのチューニングは非常に重要な要素です。本記事では、SIerの現場で実際に使えるSQLパフォーマンスチューニングの手法について、具体例を交えながら解説します。

1. SQLパフォーマンスチューニングの基本

1.1 パフォーマンス問題の特定

パフォーマンスチューニングの第一歩は、問題の特定と計測です：

1.1.1 実行計画の確認

-- PostgreSQLでの実行計画の確認
EXPLAIN ANALYZE
SELECT c.customer_id,
       c.customer_name,
       COUNT(o.order_id) as order_count,
       SUM(o.total_amount) as total_sales
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id, c.customer_name;

-- Oracleでの実行計画の確認
EXPLAIN PLAN FOR
SELECT /*+ GATHER_PLAN_STATISTICS */
       c.customer_id,
       c.customer_name,
       COUNT(o.order_id) as order_count,
       SUM(o.total_amount) as total_sales
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id, c.customer_name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

1.1.2 パフォーマンス指標の計測

主要なパフォーマンス指標：

• 実行時間: クエリの完了までにかかる時間
• 論理読み込み: バッファキャッシュからの読み込み回数
• 物理読み込み: ディスクからの読み込み回数
• CPU使用率: クエリ実行時のCPU使用率
• メモリ使用量: クエリ実行時のメモリ使用量

1.2 インデックス設計の基本

効果的なインデックス設計は、SQLパフォーマンスの要です：

1.2.1 基本的なインデックス作成

-- 単一列インデックス
CREATE INDEX idx_customers_status ON customers(status);

-- 複合インデックス
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 関数インデックス
CREATE INDEX idx_customers_name_upper ON customers(UPPER(customer_name));

-- 部分インデックス（PostgreSQL）
CREATE INDEX idx_orders_active ON orders(order_date)
WHERE status = 'active';

1.2.2 インデックス選定の基準

インデックスを作成する際の判断基準：

1. カーディナリティ: 列の値の種類の多さ
2. 選択性: WHERE句での絞り込み効果
3. 更新頻度: INSERT/UPDATE/DELETEの頻度
4. 結合条件: JOINで使用される列

-- カーディナリティの確認
SELECT COUNT(DISTINCT column_name) / COUNT(*) as selectivity
FROM table_name;

-- インデックスの使用状況確認（PostgreSQL）
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes;

2. クエリチューニングの実践テクニック

2.1 JOINの最適化

2.1.1 結合順序の最適化

-- 結合順序を指定（Oracle）
SELECT /*+ LEADING(c o i) USE_NL(o i) */
       c.customer_name,
       o.order_date,
       i.item_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items i ON o.order_id = i.order_id
WHERE c.status = 'active';

-- 結合順序を指定（PostgreSQL）
SET join_collapse_limit = 1;
SELECT c.customer_name,
       o.order_date,
       i.item_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items i ON o.order_id = i.order_id
WHERE c.status = 'active';

2.1.2 結合アルゴリズムの選択

-- Nested Loop Joinの強制（Oracle）
SELECT /*+ USE_NL(o i) */
       c.customer_name,
       o.order_date,
       i.item_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items i ON o.order_id = i.order_id;

-- Hash Joinの強制（PostgreSQL）
SET enable_nestloop = off;
SET enable_mergejoin = off;
SELECT c.customer_name,
       o.order_date,
       i.item_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items i ON o.order_id = i.order_id;

2.2 サブクエリの最適化

2.2.1 相関サブクエリの書き換え

-- 相関サブクエリ（非効率）
SELECT customer_id,
       customer_name,
       (SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.customer_id) as order_count
FROM customers c;

-- 結合に書き換え（効率的）
SELECT c.customer_id,
       c.customer_name,
       COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

2.2.2 EXISTS vs IN の使い分け

-- INの使用（大量データの場合は非効率）
SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'premium'
);

-- EXISTSの使用（より効率的）
SELECT *
FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM customers c
    WHERE c.customer_id = o.customer_id
    AND c.status = 'premium'
);

2.3 集約関数の最適化

2.3.1 集約の効率化

-- 事前集計テーブルの利用
CREATE TABLE daily_sales_summary AS
SELECT order_date,
       product_id,
       COUNT(*) as order_count,
       SUM(quantity) as total_quantity,
       SUM(amount) as total_amount
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
GROUP BY order_date, product_id;

-- マテリアライズドビューの作成（Oracle）
CREATE MATERIALIZED VIEW mv_monthly_sales
REFRESH ON DEMAND
AS
SELECT TRUNC(order_date, 'MM') as month,
       product_id,
       COUNT(*) as order_count,
       SUM(amount) as total_amount
FROM orders
GROUP BY TRUNC(order_date, 'MM'), product_id;

2.3.2 HAVING句の最適化

-- 非効率なHAVING句の使用
SELECT customer_id,
       COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'active'
);

-- 最適化したクエリ
SELECT o.customer_id,
       COUNT(*) as order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.status = 'active'
GROUP BY o.customer_id;

3. パーティショニングとシャーディング

3.1 テーブルパーティショニング

3.1.1 範囲パーティショニング

-- PostgreSQLでの範囲パーティショニング
CREATE TABLE orders (
    order_id SERIAL,
    order_date DATE,
    customer_id INTEGER,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2024q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Oracleでの範囲パーティショニング
CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    customer_id NUMBER,
    amount NUMBER(10,2)
)
PARTITION BY RANGE (order_date) (
    PARTITION orders_2024q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
    PARTITION orders_2024q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD'))
);

3.1.2 リストパーティショニング

-- PostgreSQLでのリストパーティショニング
CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE,
    region VARCHAR(50),
    amount DECIMAL(10,2)
) PARTITION BY LIST (region);

CREATE TABLE sales_east PARTITION OF sales
    FOR VALUES IN ('東京', '神奈川', '千葉', '埼玉');

CREATE TABLE sales_west PARTITION OF sales
    FOR VALUES IN ('大阪', '京都', '兵庫');

-- Oracleでのリストパーティショニング
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    region VARCHAR2(50),
    amount NUMBER(10,2)
)
PARTITION BY LIST (region) (
    PARTITION sales_east VALUES ('東京', '神奈川', '千葉', '埼玉'),
    PARTITION sales_west VALUES ('大阪', '京都', '兵庫')
);

3.2 パーティションの管理

3.2.1 パーティションの追加と削除

-- PostgreSQLでのパーティション管理
-- 新しいパーティションの追加
CREATE TABLE orders_2024q3 PARTITION OF orders
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

-- 古いパーティションの削除（アーカイブ後）
DROP TABLE orders_2023q1;

-- Oracleでのパーティション管理
-- 新しいパーティションの追加
ALTER TABLE orders ADD PARTITION orders_2024q3
    VALUES LESS THAN (TO_DATE('2024-10-01', 'YYYY-MM-DD'));

-- 古いパーティションの削除
ALTER TABLE orders DROP PARTITION orders_2023q1;

4. 統計情報の管理

4.1 統計情報の収集

-- PostgreSQLでの統計情報収集
ANALYZE customers;
ANALYZE orders;

-- より詳細な統計情報の収集
ALTER TABLE customers ALTER COLUMN status SET STATISTICS 1000;
ANALYZE customers;

-- Oracleでの統計情報収集
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'CUSTOMERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

4.2 統計情報の監視と更新

-- PostgreSQLでの統計情報の確認
SELECT schemaname,
       tablename,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables;

-- Oracleでの統計情報の確認
SELECT owner,
       table_name,
       last_analyzed,
       num_rows,
       blocks
FROM dba_tables
WHERE owner = 'SCHEMA_NAME';

5. メモリ管理とバッファキャッシュ

5.1 共有バッファの設定

-- PostgreSQLでのバッファ設定
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '256MB';

-- Oracleでのバッファ設定
ALTER SYSTEM SET sga_target = 4G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE=SPFILE;

5.2 バッファキャッシュの監視

-- PostgreSQLでのバッファキャッシュ監視
SELECT c.relname,
       count(*) blocks,
       round(count(*) * 8192.0 / 1024 / 1024, 2) size_mb
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname
ORDER BY blocks DESC
LIMIT 10;

-- Oracleでのバッファキャッシュ監視
SELECT owner,
       object_name,
       subobject_name,
       COUNT(*) blocks,
       COUNT(*) * 8192 / 1024 / 1024 size_mb
FROM v$bh b
JOIN dba_objects o ON o.data_object_id = b.objd
GROUP BY owner, object_name, subobject_name
ORDER BY blocks DESC;

6. 実行計画の制御

6.1 ヒント句の使用

-- PostgreSQLでのヒント句（pg_hint_planが必要）
/*+ IndexScan(customers customers_pkey) */
SELECT *
FROM customers
WHERE customer_id = 1000;

-- Oracleでのヒント句
SELECT /*+ INDEX(customers customers_pkey) */
       *
FROM customers
WHERE customer_id = 1000;

-- 結合方式の指定
SELECT /*+ USE_HASH(c o) */
       c.customer_name,
       o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

6.2 実行計画の固定化

-- PostgreSQLでの実行計画の固定（prepared statement）
PREPARE customer_orders(int) AS
SELECT c.customer_name,
       COUNT(*) as order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = $1
GROUP BY c.customer_name;

-- Oracleでの実行計画の固定（SQL Plan Baseline）
CREATE SQL_PLAN_BASELINE
SELECT c.customer_name,
       COUNT(*) as order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = :1
GROUP BY c.customer_name;

7. 運用面での注意点

7.1 定期メンテナンス

-- PostgreSQLでの定期メンテナンス
-- テーブルの断片化解消
VACUUM FULL customers;
VACUUM ANALYZE orders;

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

-- Oracleでの定期メンテナンス
-- テーブルの断片化解消
ALTER TABLE customers MOVE;
ALTER TABLE orders MOVE;

-- インデックスの再構築
ALTER INDEX customers_pk REBUILD;

7.2 監視と分析

-- PostgreSQLでのクエリ実行統計
SELECT queryid,
       calls,
       total_time,
       mean_time,
       rows,
       query
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- Oracleでのクエリ実行統計
SELECT sql_id,
       executions,
       elapsed_time,
       cpu_time,
       buffer_gets,
       disk_reads,
       rows_processed
FROM v$sqlarea
ORDER BY elapsed_time DESC;

8. パフォーマンスチューニングのベストプラクティス

1. 計測と分析を重視

   • 問題の特定と定量的な評価
   • ボトルネックの正確な把握
   • 改善効果の測定

2. 段階的な改善

   • 最も効果の高い部分から着手
   • 一度に複数の変更を避ける
   • 各変更の効果を確認

3. テスト環境での検証

   • 本番環境と同等のデータ量
   • 実際の負荷パターンの再現
   • 副作用の確認

4. ドキュメント化

   • チューニングの経緯と理由
   • 実施した変更の詳細
   • 効果の測定結果

まとめ

SQLパフォーマンスチューニングは、システムの性能向上において重要な要素です。本記事で紹介した手法を基本として、以下の点に注意しながら実践してください：

1. 問題の正確な把握

   • 実行計画の分析
   • パフォーマンス指標の測定
   • ボトルネックの特定

2. 適切な対策の選択

   • インデックス設計
   • クエリの最適化
   • システム設定の調整

3. 継続的な監視と改善

   • 定期的なメンテナンス
   • パフォーマンスの監視
   • 新たな問題への対応

参考文献

1. 「SQLパフォーマンス詳解」- Baron Schwartz他
2. 「PostgreSQL実践入門」- 松井暢之
3. 「Expert Oracle Database Architecture」- Thomas Kyte
4. 「SQLアンチパターン」- Bill Karwin

パフォーマンスチューニングは継続的な改善プロセスです。システムの特性や要件に応じて、適切な手法を選択し、効果的なチューニングを実施してください。