データベースのパフォーマンスチューニングとは？クエリ最適化の実践

1. パフォーマンス測定とボトルネックの特定

パフォーマンスチューニングの第一歩は、現状を把握することです。クエリの実行時間を測定し、スロークエリログを確認することで、ボトルネックを特定できます。

-- MySQL: 実行時間の測定
SET profiling = 1;
SELECT * FROM users WHERE email = 'user@example.com';
SHOW PROFILES;

-- 詳細なプロファイル
SHOW PROFILE FOR QUERY 1;

-- 各ステップの実行時間を確認
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

-- MySQL: スロークエリログの有効化
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1秒以上
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- スロークエリの確認
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- mysqldumpslowコマンドで分析（コマンドライン）
-- mysqldumpslow /var/log/mysql/slow.log

-- PostgreSQL: pg_stat_statementsの有効化
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- クエリの統計情報を確認
SELECT 
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

2. EXPLAINで実行計画を確認

EXPLAINを使用して、クエリの実行計画を確認することで、インデックスが適切に使用されているか、全件スキャンが発生していないかを確認できます。実行計画を理解することで、クエリの最適化ポイントを特定できます。

-- MySQL: EXPLAINの実行
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- 重要な列の説明
-- type: ALL（全件スキャン）、ref（インデックス使用）、eq_ref（一意インデックス）
-- key: 使用されているインデックス名
-- rows: スキャンされる行数（推定値）
-- Extra: 追加情報（Using index、Using filesort、Using temporaryなど）

-- インデックスが使用されていない場合
-- type: ALL, key: NULL, rows: 1000000

-- インデックスが使用されている場合
-- type: ref, key: idx_email, rows: 1

-- PostgreSQL: EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- 出力例:
-- Seq Scan on users (cost=0.00..1000.00 rows=1 width=64) (actual time=50.123..50.125 rows=1 loops=1)
--   Filter: (email = 'user@example.com'::text)
--   Rows Removed by Filter: 99999
-- Planning Time: 0.123 ms
-- Execution Time: 50.234 ms

-- ❌ 悪い例（全件スキャン）
EXPLAIN SELECT * FROM users WHERE name LIKE '%田中%';
-- type: ALL, rows: 1000000, Extra: Using where

-- ✅ 良い例（インデックス使用）
CREATE INDEX idx_user_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'tanaka@example.com';
-- type: ref, key: idx_user_email, rows: 1

3. クエリ最適化のテクニック

クエリ最適化には、いくつかの基本的なテクニックがあります。必要なカラムのみ選択、LIMITの使用、インデックスの活用、JOINの最適化など、様々な手法を組み合わせることで、パフォーマンスを向上させることができます。

-- ❌ 悪い例
SELECT * FROM users;

-- ✅ 良い例
SELECT id, name, email FROM users;

-- ❌ 悪い例（すべての投稿を取得）
SELECT * FROM posts ORDER BY created_at DESC;

-- ✅ 良い例（LIMITを使用）
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;

-- ✅ カーソルベースのページネーション（OFFSETが大きい場合）
SELECT * FROM posts WHERE id > 1000 ORDER BY id LIMIT 20;

-- WHERE句のカラムにインデックス
CREATE INDEX idx_user_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';

-- ORDER BYのカラムにインデックス
CREATE INDEX idx_post_created ON posts(created_at);
SELECT * FROM posts ORDER BY created_at DESC;

-- WHEREとORDER BYの両方に使用されるカラム
CREATE INDEX idx_post_status_created ON posts(status, created_at);
SELECT * FROM posts WHERE status = 'published' ORDER BY created_at DESC;

-- 小さいテーブルを先にJOIN
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.small_id;

-- インデックス付きカラムでJOIN
CREATE INDEX idx_large_small_id ON large_table(small_id);

-- JOIN条件にインデックスを作成
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = 'user@example.com';

-- ❌ 悪い例（関数を使用）
SELECT * FROM posts WHERE YEAR(created_at) = 2024;

-- ✅ 良い例（範囲検索）
SELECT * FROM posts WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

4. インデックスの最適化

インデックスの最適化は、パフォーマンス向上の重要な要素です。カバリングインデックスや複合インデックスの順序を適切に設定することで、クエリの実行速度を向上させることができます。

-- カバリングインデックス
CREATE INDEX idx_user_covering ON users(id, name, email);

-- このクエリはインデックスのみで実行可能（テーブルアクセス不要）
SELECT id, name, email FROM users WHERE id = 123;

-- EXPLAINの結果: Extra: Using index

-- カーディナリティの高いカラムを先に
CREATE INDEX idx_user_email_status ON users(email, status);

-- WHERE句で使用される順序に合わせる
SELECT * FROM users WHERE email = 'user@example.com' AND status = 'active';

-- ❌ このインデックスは使用されない（最初のカラムが使用されていない）
SELECT * FROM users WHERE status = 'active';

-- ✅ この場合は別のインデックスが必要
CREATE INDEX idx_user_status ON users(status);

-- PostgreSQL: 部分インデックス
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

-- 削除されていないユーザーのみにインデックスが作成される
SELECT * FROM users WHERE email = 'user@example.com' AND deleted_at IS NULL;

-- MySQL: インデックスの統計情報を更新
ANALYZE TABLE users;

-- PostgreSQL: インデックスの統計情報を更新
ANALYZE users;

-- すべてのテーブルを分析
ANALYZE;

5. パーティショニング

パーティショニングは、大きなテーブルを複数の小さなパーティションに分割する技術です。範囲パーティションやハッシュパーティションを使用することで、特定の期間のデータのみをスキャンできるため、パフォーマンスを向上させることができます。

-- MySQL: 日付で範囲パーティション
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    user_id INT,
    created_at DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 特定のパーティションのみスキャン
SELECT * FROM orders WHERE created_at >= '2024-01-01';

-- パーティションの追加
ALTER TABLE orders ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

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

-- PostgreSQL: 範囲パーティション
CREATE TABLE orders (
    id SERIAL,
    user_id INT,
    created_at DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (created_at);

-- パーティションの作成
CREATE TABLE orders_2022 PARTITION OF orders
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- MySQL: ハッシュパーティション
CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(255),
    PRIMARY KEY (id)
) PARTITION BY HASH(id) PARTITIONS 4;

-- 4つのパーティションに均等に分散される

6. 接続プールとキャッシング

接続プールは、データベース接続を効率的に管理する技術です。また、アプリケーションレベルのキャッシングにより、データベースへの負荷を減らし、パフォーマンスを向上させることができます。

// Node.jsでの接続プール
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

// 接続の再利用
const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [userId]);

// 接続プールの統計情報
console.log(pool.pool._allConnections.length); // 総接続数
console.log(pool.pool._freeConnections.length); // 空き接続数

import redis
from functools import wraps
import json

# Redis接続
redis_client = redis.Redis(host='localhost', port=6379, db=0)

def cache_result(expiration=3600):
    """クエリ結果をキャッシュするデコレータ"""
    def decorator(func):
        @wraps(func)
        async def wrapper(*args, **kwargs):
            # キャッシュキーの生成
            cache_key = f"{func.__name__}:{args}:{kwargs}"
            
            # キャッシュから取得
            cached = redis_client.get(cache_key)
            if cached:
                return json.loads(cached)
            
            # データベースから取得
            result = await func(*args, **kwargs)
            
            # キャッシュに保存
            redis_client.setex(cache_key, expiration, json.dumps(result))
            
            return result
        return wrapper
    return decorator

@cache_result(expiration=3600)
async def get_user(user_id):
    # データベースからユーザー情報を取得
    return await db.get_user(user_id)

7. データベース設定の最適化

データベースサーバーの設定を最適化することで、パフォーマンスを向上させることができます。バッファサイズ、接続数、クエリキャッシュなどの設定を調整します。

# MySQL設定例（my.cnf）
[mysqld]
# InnoDBバッファプールサイズ（メモリの70-80%）
innodb_buffer_pool_size = 4G

# 最大接続数
max_connections = 200

# クエリキャッシュ（MySQL 5.7まで）
query_cache_size = 64M
query_cache_type = 1

# スロークエリログ
slow_query_log = 1
long_query_time = 1

# バイナリログ
log_bin = /var/log/mysql/mysql-bin.log

# PostgreSQL設定例（postgresql.conf）
# 共有バッファ（メモリの25%）
shared_buffers = 1GB

# 作業メモリ（複雑なクエリ用）
work_mem = 16MB

# 有効キャッシュサイズ（OS + PostgreSQLのメモリ合計）
effective_cache_size = 4GB

# 最大接続数
max_connections = 200

# ログ設定
log_min_duration_statement = 1000  # 1秒以上

-- MySQL: 現在の設定を確認
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'query_cache_size';

-- PostgreSQL: 現在の設定を確認
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

8. ベストプラクティス

データベースのパフォーマンスチューニングには、いくつかのベストプラクティスがあります。EXPLAINで実行計画を確認し、インデックスを適切に使用し、定期的にメンテナンスを実施することで、パフォーマンスを維持できます。

• EXPLAINで実行計画を確認: クエリの最適化前に現状を把握します。EXPLAINを使用して、クエリがどのように実行されるかを確認し、ボトルネックを特定します。
• インデックスを適切に使用: 検索頻度の高いカラムにインデックスを作成します。WHERE句やORDER BY句で使用されるカラムにインデックスを作成することで、パフォーマンスを向上させることができます。
• 必要なカラムのみ選択: SELECT *を避け、必要なカラムのみを選択します。不要なカラムを取得することで、ネットワーク転送量が増加し、パフォーマンスに影響を与える可能性があります。
• LIMITを使用: 大量データの取得を制限します。LIMITを使用することで、取得するデータの件数を制限し、パフォーマンスを向上させることができます。
• JOINを最適化: 小さいテーブルを先に、インデックス付きカラムでJOINします。JOINの順序を最適化し、JOIN条件に使用されるカラムにインデックスを作成することで、パフォーマンスを向上させることができます。
• パーティショニング: 大きなテーブルを分割します。パーティショニングを使用することで、特定の期間のデータのみをスキャンできるため、パフォーマンスを向上させることができます。
• 接続プール: 接続を効率的に管理します。接続プールを使用することで、接続の作成と破棄のオーバーヘッドを減らし、パフォーマンスを向上させることができます。
• キャッシング: アプリケーションレベルのキャッシングを実装します。変更頻度の低いデータのクエリ結果をキャッシュすることで、データベースへの負荷を減らし、パフォーマンスを向上させることができます。
• 定期的なメンテナンス: ANALYZE TABLE、OPTIMIZE TABLEを実行します。定期的にメンテナンスを実施することで、インデックスの統計情報を更新し、パフォーマンスを維持できます。
• 設定の最適化: データベースサーバーの設定を最適化します。バッファサイズ、接続数などの設定を調整することで、パフォーマンスを向上させることができます。