データベースインデックス設計の基礎

この記事の内容

データベースのインデックス設計は、アプリケーションのパフォーマンスに直結する重要なスキルです。本記事では、インデックスの基本的な仕組みから、実戦で使える設計原則までを体系的に解説します。

インデックスとは何か

図書館の目録との類似

データベースインデックスは、図書館の目録カードに例えられます。

• 本棚全体を総当たりで探す（フルスキャン） = 非効率
• 目録で検索して棚番号を知る（インデックス検索） = 効率的

この仕組みにより、データ検索速度が劇的に向上します。

インデックスのトレードオフ

「インデックスは読込を高速化するが、書込を遅くする」——このトレードオフを理解することが設計の出発点です。

B ツリーの仕組み

多分木構造

リレーショナルデータベース（MySQL, PostgreSQL など）のデフォルトインデックスは、**B ツリー（Balanced Tree）**と呼ばれるデータ構造を使用しています。

        [50]
       /    \
    [20]    [80]
   /   \    /   \
 [10] [30] [60] [90]

各ノードは複数のキーを持ち、子ノードへのポインタを持ちます。この構造により：

• 平衡状態が保たれ、ツリーの高さが一定に
• 対数時間 O(log n) で検索が可能に

例：100 万レコードの場合

• フルスキャン：最大 100 万回の比較
• B ツリー（枝分かれ 100）：最大 3 回の比較（log₁₀₀ 1,000,000 = 3）

インデックスの種類

単一カラムインデックス

CREATE INDEX idx_users_email ON users(email);

1 つのカラムに張る基本的なインデックス。等値検索に効果的。

複合インデックス（複数列インデックス）

CREATE INDEX idx_users_name_age ON users(last_name, first_name, age);

複数のカラムをまとめてインデックス化。カラムの順序が重要です。

左端一致の原則

複合インデックス (A, B, C) がある場合：

「インデックスは左端から連続したカラムでのみ機能する」

カバリングインデックス

クエリで必要なカラムがすべてインデックスに含まれている場合、テーブル本体にアクセスせずインデックスだけで結果を返せます。

-- インデックス: (user_id, created_at, status)
SELECT user_id, created_at, status
FROM orders
WHERE user_id = 123 AND status = 'active';

この場合、インデックスだけで完結するため、Index Only Scanと呼ばれる最適化が働きます。

インデックス設計の原則

1. WHERE 句と ORDER BY を分析する

頻繁に実行されるクエリの WHERE 句と ORDER BY 句を特定：

-- 頻出クエリ
SELECT * FROM orders
WHERE user_id = ?
  AND status = ?
ORDER BY created_at DESC;

-- 推奨インデックス
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);

2. カーディナリティを考慮する

カーディナリティ（一意の値の数）が高いカラムほど、インデックスの効果が高い：

例：gender（2 値）にインデックスを張っても、データが 50% ずつの場合、フルスキャンと変わらない。

3. 部分的なインデックス（Partial Index）

条件付きインデックスで、特定条件下的の検索を最適化：

-- PostgreSQL の場合
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';

「未処理の注文だけ高速に検索したい」場合に有効。

4. 関数インデックス

カラムに関数を適用した結果でインデックス：

-- 大文字小文字を区別しない検索
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- クエリ
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

インデックスが効かないケース

1. 関数適用（インデックス未整備時）

-- インデックスが効かない
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- インデックスが効く
SELECT * FROM users
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

2. 前方一致しない LIKE 検索

-- インデックス使用
SELECT * FROM users WHERE name LIKE '田中%';

-- インデックス未使用（フルスキャン）
SELECT * FROM users WHERE name LIKE '%太郎%';

3. OR 条件の誤用

-- インデックスが効きにくい
SELECT * FROM orders WHERE user_id = 1 OR status = 'pending';

-- UNION で分離
SELECT * FROM orders WHERE user_id = 1
UNION
SELECT * FROM orders WHERE status = 'pending';

4. NOT 条件

-- インデックス未使用
SELECT * FROM users WHERE status != 'deleted';

-- 代替案
SELECT * FROM users WHERE status IN ('active', 'pending', 'suspended');

インデックスのメンテナンス

断片化の検出

長期的な書き込みでインデックスが断片化：

-- PostgreSQL
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan;

-- MySQL
SHOW INDEX FROM table_name;

不要なインデックスの特定

-- ほとんど使われていないインデックス
SELECT index_name, user_scans, user_updates
FROM sys.dm_db_index_usage_stats
WHERE user_scans < 10 AND user_updates > 1000;

「読まれないインデックスは、ただの書き込みペナルティ」——定期的に整理しましょう。

パフォーマンス測定

EXPLAIN の活用

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123
  AND status = 'active'
ORDER BY created_at DESC;

実行プランから読み取るべき情報：

• Index Scan または Index Seek：インデックス使用（良）
• Seq Scan または Table Scan：フルスキャン（要改善）
• cost: 相対的な実行コスト
• actual time: 実際の実行時間（ms）

実践的チェックリスト

インデックスを設計する際の最終チェックリスト：

• 頻出クエリを特定している
• WHERE 句のカラムを左側に配置している
• ORDER BY カラムもインデックスに含めている
• カーディナリティが低いカラムのインデックスを避けている
• 書き込み頻度の高いテーブルではインデックス数を絞っている
• 複合インデックスの順序をクエリパターンに合わせ
• EXPLAIN で実行プランを確認している
• 不要になったインデックスを削除する運用がある

まとめ

インデックス設計の核心は：

1. トレードオフの理解——読込高速化と書込低下のバランス
2. クエリパターンの把握——実際のクエリに基づいた設計
3. 左端一致の原則——複合インデックスは順序が命
4. 継続的な観測——EXPLAIN で検証し、不要なものは削除

適切なインデックス設計は、データベースのパフォーマンスを 10 倍〜100 倍向上させる可能性があります。まずは頻出クエリの分析から始めましょう。