キーとインデックス

正しいキー設計はデータの整合性を、正しいインデックス設計はクエリの性能を支えます。 どちらもテーブル設計の初期段階で決めるべき重要項目であり、後から変更するコストは非常に高くなります。 このページでは、キーの体系的な理解からインデックスの実務的な設計指針までを網羅します。

キーの体系

RDB におけるキーは、行を一意に識別したりテーブル間の関係を表現するための仕組みです。 複数のキー概念がどのような包含関係にあるかを正確に理解しておくことが、設計の土台になります。

キーの種類と定義

キーの包含関係

外部キーの役割

外部キーは上記の「行を識別するキー」とは異なり、テーブル間の参照整合性を保証する仕組みです。 外部キー制約を定義すると、参照先に存在しない値を挿入・更新できなくなり、 また参照されている行を不用意に削除できなくなります。

-- 外部キー制約の定義例
CREATE TABLE orders (
    order_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_date  DATE        NOT NULL,
    customer_id BIGINT      NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

自然キーとサロゲートキーの詳細比較

主キーの選び方として、業務上意味のある値（自然キー）を使うか、 システムが自動採番する意味のない値（サロゲートキー）を使うかは、実務で最も議論になるテーマの一つです。

比較表

UUID vs SERIAL/IDENTITY

サロゲートキーの実装方式にも選択肢があります。

-- IDENTITY（SQL標準準拠、推奨）
CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL
);

-- UUID v7（PostgreSQL 17+ では gen_random_uuid() の代わりに uuidv7 拡張も利用可能）
CREATE TABLE api_tokens (
    token_id   UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    issued_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

複合主キーの設計

複合主キー（2つ以上の列の組み合わせで行を識別する主キー）は、 特定の場面で自然かつ効果的な設計になります。一方で、デメリットも存在するため、適材適所の判断が必要です。

複合主キーが適する場面

• 明細テーブル: 親テーブルの ID + 明細番号（例: 受注明細）
• 関連テーブル（中間テーブル）: 多対多を解決するテーブル（例: 学生ID + 科目ID）
• 履歴・バージョンテーブル: 本体 ID + バージョン番号 or 有効開始日

受注明細の具体例

-- 複合主キーの例: 受注明細
CREATE TABLE order_details (
    order_id      BIGINT  NOT NULL,
    detail_number INT     NOT NULL,  -- 受注内の連番（1, 2, 3...）
    product_id    BIGINT  NOT NULL,
    quantity      INT     NOT NULL CHECK (quantity > 0),
    unit_price    NUMERIC(10, 2) NOT NULL,

    PRIMARY KEY (order_id, detail_number),
    FOREIGN KEY (order_id)   REFERENCES orders (order_id),
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);

この設計により、(order_id = 1001, detail_number = 1) のように受注内の各明細行が一意に特定されます。

メリットとデメリット

インデックスの仕組み

インデックスは、テーブルの特定列に対して検索を高速化するためのデータ構造です。 書籍の索引（index）と同じ発想で、目的のデータが格納されている場所をすばやく見つけます。

フルスキャン vs インデックススキャン

インデックスがない場合、DB は目的の行を見つけるためにテーブル全体を先頭から末尾まで走査します。 これをフルテーブルスキャン（Seq Scan）と呼びます。 100万行のテーブルから 1 行を探す場合でも、最悪 100万行すべてを読む必要があります。

インデックスがある場合、B-tree の木構造を辿ることで、数回のページ読み取りだけで目的の行に到達できます。 100万行でも、B-tree の高さはおよそ 3〜4 レベル程度であり、3〜4 回のアクセスで目的のデータを特定できます。

B-tree インデックスの概念図

B-tree（Balanced Tree）は、RDB で最も一般的に使われるインデックス構造です。 すべての葉ノードが同じ深さにあるため、どのキー値を検索しても一定のアクセス回数で到達できます。

インデックスの種類

B-tree が最も汎用的ですが、データの特性やクエリの種類に応じて最適なインデックスタイプは異なります。 以下は主要な RDBMS（PostgreSQL を中心に）でサポートされるインデックスの種類です。

-- B-tree（デフォルト。CREATE INDEX は B-tree になる）
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- GIN（JSONB 列への検索を高速化）
CREATE INDEX idx_events_payload ON events USING gin (payload);

-- BRIN（時系列ログの created_at 列向け。非常に小さいインデックスサイズ）
CREATE INDEX idx_logs_created ON access_logs USING brin (created_at);

複合インデックスと列順

複合インデックス（複数列で構成されるインデックス）では、列の定義順序が検索性能を決定的に左右します。 これは B-tree が「左端の列から順にソートする」構造であるためです。

左端一致の原則（Leftmost Prefix Rule）

複合インデックス (A, B, C) を定義した場合、B-tree 内部のデータは まず A の昇順、A が同じなら B の昇順、B も同じなら C の昇順に並んでいます。 そのため、検索条件に A が含まれていなければ、このインデックスは使えません。

-- 複合インデックスの定義
CREATE INDEX idx_abc ON sales (region, category, sale_date);
--                              A        B          C

どの検索パターンでインデックスが効くか

-- 良い列順: 等価条件の列が先、範囲条件の列が後
CREATE INDEX idx_good ON orders (status, customer_id, order_date);
-- WHERE status = 'shipped' AND customer_id = 42 AND order_date >= '2025-01-01'
-- → 3 列すべてが有効に利用される

-- 悪い列順: 範囲条件の列が先
CREATE INDEX idx_bad ON orders (order_date, status, customer_id);
-- WHERE order_date >= '2025-01-01' AND status = 'shipped' AND customer_id = 42
-- → order_date の範囲スキャン後、status と customer_id はフィルタのみ

カバリングインデックス

通常のインデックス検索では、B-tree でキーを見つけた後、テーブル本体（ヒープ）に行データを取りに行きます。 この「テーブルへの戻り読み」をなくし、インデックスだけでクエリを完結させるのがカバリングインデックスです。

INCLUDE の使い方

PostgreSQL 11 以降では、INCLUDE 句を使って「検索キーではないが、SELECT で必要な列」を インデックスに含めることができます。INCLUDE 列はソートには使われませんが、 インデックスのリーフノードに格納されるため、テーブルへの戻り読みが不要になります。

-- 通常のインデックス: customer_id で検索し、テーブルから order_date, total を取得
CREATE INDEX idx_orders_cust ON orders (customer_id);

-- カバリングインデックス: インデックスだけで SELECT を完結
CREATE INDEX idx_orders_cust_covering
    ON orders (customer_id)
    INCLUDE (order_date, total_amount);

-- このクエリはテーブルへの戻り読みが不要（Index Only Scan）
SELECT order_date, total_amount
  FROM orders
 WHERE customer_id = 42;

効果と注意点

インデックス設計の実務指針

選択度（カーディナリティ）の考え方

選択度（Selectivity）は、あるインデックスキー値でどれだけ行を絞り込めるかを示す指標です。 計算式は「ユニークな値の数 / 全行数」で、1 に近いほど選択度が高く、インデックスの効果が大きくなります。

インデックスを付けすぎるとどうなるか

インデックスはコストのないデータ構造ではありません。以下のデメリットが蓄積します。

• INSERT / UPDATE / DELETE が遅くなる: 行の変更のたびに、対象列を含むすべてのインデックスも更新が必要
• ストレージ消費の増大: 大きなテーブルでは、インデックスの合計サイズがテーブル本体を超えることもある
• オプティマイザの判断が複雑化: インデックスが多すぎると最適な実行計画の選択に時間がかかる
• VACUUM / メンテナンスの負荷増大: PostgreSQL では各インデックスに対して VACUUM が走る

EXPLAIN の読み方の基本

インデックスが実際に使われているかどうかは、推測ではなく EXPLAIN で確認します。

-- 実行計画の確認（PostgreSQL）
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

-- 出力例:
-- Index Scan using idx_orders_customer on orders  (cost=0.43..8.45 rows=5 width=64)
--                                                  (actual time=0.023..0.031 rows=5 loops=1)
--   Index Cond: (customer_id = 42)
-- Planning Time: 0.085 ms
-- Execution Time: 0.052 ms

注目すべきポイント:

アンチパターン

インデックス設計でよく見られる誤りを整理します。これらを避けるだけで、多くの性能問題を未然に防げます。

アンチパターン 1: 全列にインデックス

「検索が速くなるならすべての列にインデックスを付ければよい」という発想は、 更新性能とストレージの両方を犠牲にします。

-- アンチパターン: 全列にインデックス
CREATE INDEX idx_1 ON users (user_id);     -- PK で自動作成されるので不要
CREATE INDEX idx_2 ON users (email);       -- UNIQUE 制約で自動作成される場合は不要
CREATE INDEX idx_3 ON users (first_name);
CREATE INDEX idx_4 ON users (last_name);
CREATE INDEX idx_5 ON users (phone);
CREATE INDEX idx_6 ON users (address);
CREATE INDEX idx_7 ON users (created_at);
CREATE INDEX idx_8 ON users (updated_at);
CREATE INDEX idx_9 ON users (is_active);   -- カーディナリティが低すぎて無意味
CREATE INDEX idx_10 ON users (role);       -- 同上

アンチパターン 2: 低カーディナリティ列への単独インデックス

is_active（TRUE / FALSE）や gender（M / F / Other）のように、 ユニーク値が極端に少ない列への単独インデックスは、ほとんど効果がありません。

インデックスで絞り込んでも全体の 50% や 33% の行がヒットするため、 オプティマイザはフルスキャンの方がコストが低いと判断し、インデックスを無視することが多いです。

-- ほぼ無意味なインデックス
CREATE INDEX idx_users_active ON users (is_active);
-- WHERE is_active = true → 全体の 95% がヒットする場合、Seq Scan の方が速い

-- 改善案: 高カーディナリティ列との複合インデックスにする
CREATE INDEX idx_users_active_created ON users (is_active, created_at);
-- WHERE is_active = false AND created_at < '2024-01-01'
-- → is_active = false の少数行をさらに created_at で絞り込む場合は有効

-- 改善案: 部分インデックス（該当行が少数の場合に特に有効）
CREATE INDEX idx_users_inactive ON users (created_at) WHERE is_active = false;
-- is_active = false が全体の 5% なら、インデックスサイズも 5% で済む

アンチパターン 3: 使われていないインデックスの放置

開発初期に作成したインデックスが、仕様変更やクエリ修正で使われなくなっているケースは頻繁に発生します。 定期的に未使用インデックスを確認し、不要なら削除しましょう。

-- PostgreSQL: 使われていないインデックスの確認
SELECT schemaname, relname AS table_name,
       indexrelname AS index_name,
       idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
 ORDER BY pg_relation_size(indexrelid) DESC;