部分インデックス・式インデックス — deleted_at / テナント / LOWER

部分インデックス（PostgreSQL）/ フィルタードインデックス（SQL Server）は、WHERE 句を付けた索引です。「特定条件を満たす行だけ」に限定した索引を作れます。

-- PostgreSQL / SQL Server: 生存中の行だけに索引
CREATE INDEX idx_users_active_email
  ON users (email)
  WHERE deleted_at IS NULL;

メリット:

• サイズが小さい — 対象行だけなので本体索引より軽い
• 書き込みコストが低い — 条件外の行を更新しても索引は触らない
• UNIQUE の条件付き適用 — 「生存中の行でのみメールアドレスが一意」などの複雑な制約を素直に書ける

MySQL は部分インデックスに対応していません（パーティショニングや generated column で近いことは可能）。使う RDBMS でサポート状況を必ず確認してください。

論理削除（deleted_at IS NULL で生存判定）を採用しているテーブルでは、ほぼすべてのクエリに deleted_at IS NULL が付くのが普通です。ここで部分インデックスは 2 つの問題を一度に解決します。

問題 1: UNIQUE 制約が機能しなくなる
論理削除では「消えた行」がテーブルに残るので、UNIQUE (email) だと削除済みの同名メアドが衝突してしまい、再登録できなくなります。

問題 2: 一般クエリで死んだ行まで索引を走査する
単純な INDEX (email) だと、削除済みの行も索引に載り続け、deleted_at IS NULL を後フィルタで弾く形になります。

両方とも部分 UNIQUE インデックスで解決:

-- PostgreSQL
CREATE UNIQUE INDEX idx_users_email_alive
  ON users (email)
  WHERE deleted_at IS NULL;

-- SQL Server
CREATE UNIQUE INDEX idx_users_email_alive
  ON users (email)
  WHERE deleted_at IS NULL;

削除済みの行は索引に載らず、生存行だけで一意性が担保されます。論理削除の詳細は 論理削除 vs 物理削除 に。

マルチテナント SaaS では全クエリに tenant_id = ? が付きます。基本戦略は (tenant_id, ...) を先頭にした複合索引ですが、「特定テナントだけ大量データを持つ」偏りがある場合は部分インデックスという選択肢もあります。

パターン:

• 通常: CREATE INDEX idx_main ON orders (tenant_id, status, created_at)
• ヘビーテナント向け専用: CREATE INDEX idx_whale ON orders (status, created_at) WHERE tenant_id = 999

ヘビーテナントが全体の大半を占める場合、通常索引の先頭 tenant_id がほとんど絞り込みに役立たなくなります。そのテナントだけ別の索引を用意することで、他テナントに影響を与えずにチューニングできます。これは特殊ケースですが、知っておくと救われる局面があります。

もう 1 つ有用なのが有効行だけの索引。status = 'active' が大半で 'archived' が少数、というテーブルでは WHERE status = 'active' の部分索引で「普段のクエリ」を最適化し、アーカイブは別経路という設計が取れます。

式インデックス（functional index / expression index）は列そのものではなく関数の結果を索引化します。大文字小文字を無視したメール検索、日付のトランケート、JSON フィールドの取り出しなどで威力を発揮します。

-- PostgreSQL: 大文字小文字無視のメール検索
CREATE INDEX idx_users_email_lower
  ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('Foo@Example.com');
-- → インデックスが使われる

-- 日付のトランケート（月別集計を高速化）
CREATE INDEX idx_orders_month
  ON orders (DATE_TRUNC('month', created_at));

-- JSON フィールドの取り出し
CREATE INDEX idx_events_user_id
  ON events ((payload->>'user_id'));

ポイント: 検索クエリ側も同じ式を書く必要があります。LOWER(email) の索引は email 単独の検索では使われません。

式インデックスがない RDBMS や、複雑な式を複数クエリで使い回したい場合は、生成列（generated column / computed column）に通常の索引を貼るのが実務的な代替です。

-- MySQL 5.7+: 生成列 + 索引
ALTER TABLE users
  ADD email_lower VARCHAR(255) GENERATED ALWAYS AS (LOWER(email)) STORED;

CREATE INDEX idx_users_email_lower ON users (email_lower);

SELECT * FROM users WHERE email_lower = LOWER('Foo@Example.com');

STORED の場合はディスクに保存され挿入時に評価、VIRTUAL の場合は毎回計算（MySQL では VIRTUAL 列にも索引を貼れる）。

JSON フィールドの取り出しを高速化する定番パターンでもあります。

-- JSON 内の user_id を索引化
ALTER TABLE events
  ADD user_id BIGINT GENERATED ALWAYS AS
    (JSON_EXTRACT(payload, '$.user_id')) STORED;

CREATE INDEX idx_events_user_id ON events (user_id);

generated column は「式をテーブル定義で明示する」ので、可読性が上がり、アプリ側と SQL 側で式がずれにくいという副次的な利点もあります。

部分・式・generated のどれを使うか、設計時の判断フロー:

1. クエリに固定条件がある（deleted_at IS NULL, status = 'active', tenant_id = X） → 部分インデックス（PostgreSQL / SQL Server）
2. MySQL で同じことをしたい → generated column + 索引、またはパーティショニング
3. 関数の結果で検索・ソートする（LOWER / TRUNC / JSON 抽出） → 式インデックス、または generated column + 索引
4. UNIQUE を条件付きで適用したい → 部分 UNIQUE インデックス（MySQL は generated + 通常 UNIQUE で近似）

使う RDBMS で書けない書き方に頼ると移植コストが上がります。複数 RDBMS を想定する場合は generated column + 通常索引を第一選択にすると、後で困りません。