database-optimization

データベース最適化

EXPLAIN 分析

最適化する前に、必ず EXPLAIN ANALYZE を実行してください。出力は下から上に向かって読みます。

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- MySQL
EXPLAIN ANALYZE SELECT ...;

確認すべき主な指標：

• 大規模なテーブルでの Seq Scan ＝ インデックスが不足しています
• 行数の多い Nested Loop ＝ ハッシュ結合またはマージ結合を検討してください
• インデックスなしの Sort ＝ ソート列にインデックスを追加してください
• Rows estimated vs actual の乖離 ＝ 統計情報が古くなっています。ANALYZE を実行してください

インデックス戦略

B-tree（デフォルト、ほとんどのケース）

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

用途：等価検索、範囲検索、ソート。複合インデックスでは列の順序が重要です。等価検索の列を先に、範囲検索やソートの列を後に配置してください。

部分インデックス（PostgreSQL）

CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

クエリが常に特定の条件でフィルターする場合に使用します。通常のインデックスより大幅に小さくなります。

GIN（PostgreSQL — 配列、JSONB、全文検索）

CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_docs_search ON documents USING GIN (to_tsvector('english', content));

GiST（PostgreSQL — 空間データ、範囲型）

CREATE INDEX idx_locations_point ON locations USING GiST (coordinates);
CREATE INDEX idx_events_period ON events USING GiST (tsrange(start_at, end_at));

カバリングインデックス（インデックスのみのスキャン）

-- PostgreSQL
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name);

-- MySQL
CREATE INDEX idx_users_email_name ON users (email, name);

N+1 クエリの検出

症状：親を取得するクエリが1つあり、子ごとにN個のクエリが実行されます。

# BAD: N+1
users = db.query(User).all()
for user in users:
    print(user.orders)  # triggers query per user

# GOOD: eager load
users = db.query(User).options(joinedload(User.orders)).all()

// BAD: N+1
const users = await User.findAll();
for (const user of users) {
  const orders = await Order.findAll({ where: { userId: user.id } });
}

// GOOD: batch load
const users = await User.findAll({ include: [Order] });

検出方法：クエリログを有効にし、リクエストごとのクエリ数を数えます。単一のエンドポイントで10件を超えるクエリが発生している場合は、要注意です。

コネクションプーリング

Rule of thumb: pool_size = (core_count * 2) + disk_count
Typical web app: 10-20 connections per app instance

PostgreSQL：

• サーバーレス環境や接続数の多い環境では、PgBouncer をトランザクションモードで使用します
• idle_in_transaction_session_timeout = '30s' を設定します
• pg_stat_activity で監視します

MySQL：

• 利用可能な RAM に応じて max_connections を設定します（接続1つあたり約10MBを使用します）
• 接続の多重化には ProxySQL を使用します
• SHOW PROCESSLIST で監視します

読み取りレプリカ

• すべての SELECT クエリをレプリカにルーティングします
• すべての書き込みをプライマリにルーティングします
• レプリケーション遅延を考慮します（通常10～100ms）
• レプリカから書き込み直後に読み取らないでください。一貫性が重要な読み取りにはプライマリを使用します
• クエリ単位ではなく、接続単位でルーティングします

# SQLAlchemy read replica routing
class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None):
        if self._flushing or self.is_modified():
            return engines["primary"]
        return engines["replica"]

パーティション戦略

レンジパーティショニング（時系列データ）

-- PostgreSQL
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL,
    data jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_q1 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE events_2025_q2 PARTITION OF events
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

ハッシュパーティショニング（均等分散）

CREATE TABLE sessions (
    id uuid PRIMARY KEY,
    user_id bigint NOT NULL
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);

テーブルが50～100GBを超える場合、または古いデータをすばやく削除する必要がある場合にパーティショニングを行います。

クエリ最適化チェックリスト

1. EXPLAIN ANALYZE を実行して実行計画を読みます
2. 1万行を超えるテーブルでシーケンシャルスキャンが発生していないか確認します
3. インデックスの使用状況を確認します（pg_stat_user_indexes の idx_scan を確認します）
4. インデックスの使用を妨げる暗黙的な型変換がないか確認します
5. SELECT * を必要な列の指定に置き換えます
6. 一部のデータだけが必要なクエリには LIMIT を追加します
7. COUNT(*) > 0 の代わりに EXISTS を使用します
8. INSERT／UPDATE 操作をバッチ処理します（1バッチあたり500～1000行）
9. WHERE 句でインデックス対象の列に関数を適用しないようにします
10. スロークエリログを監視します（pg：log_min_duration_statement = 100）

危険なパターン

• インデックスのない列に対する LIKE '%term%'（代わりに全文検索を使用します）
• ORDER BY RANDOM()（代わりに TABLESAMPLE またはアプリケーション側でのランダム化を使用します）
• 結合の問題を覆い隠す SELECT DISTINCT
• UPDATE／DELETE の WHERE 句の欠落（必ず先に SELECT で確認します）
• ロックを保持する長時間実行トランザクション
• 深いページネーションでの OFFSET の使用（代わりにキーセット／カーソルページネーションを使用します）