データモデル設計ガイド

インデックス種別の選び方 — B-Tree / Hash / GIN / GiST / BRIN

B-Tree だけでは JSONB・全文検索・時系列を支えられない。種別ごとの得意分野と RDBMS 対応を用途別に把握する。

B-Tree — 迷ったらこれ

RDBMS のデフォルトインデックスはほぼすべて B-Tree（正確には B+Tree）です。等値検索・範囲検索・ソート・LIKE 前方一致 と、実務で使うクエリの大半を 1 種類でカバーします。

B-Tree が得意な述語:

WHERE col = ?（等値）
WHERE col >= ? AND col < ?（範囲）
WHERE col IN (?, ?, ?)（列挙）
WHERE col LIKE 'abc%'（前方一致。後方・中間一致は不可）
ORDER BY col [ASC|DESC]（ソート済み順で読める）

逆に苦手なのは 部分文字列検索（LIKE '%abc%'）・全文検索・JSON のキー検索・配列要素検索・地理空間・非常に低いカーディナリティの列です。これらは以下の特殊インデックスの出番です。

用途別第一選択マトリクス

やりたいこと	第一選択	PostgreSQL	MySQL	SQL Server
等値・範囲・ソート	B-Tree	○	○	○
完全一致だけ（等値）を極限まで速く	Hash	○ (PG 11+ で WAL 対応)	MEMORY テーブルのみ	× (clustered の代替で対応)
全文検索（日英を含む）	GIN + tsvector / pg_trgm	○	FULLTEXT INDEX	Full-Text Search
JSONB のキー検索 / 含有判定	GIN	○	Multi-Valued Index (8.0.17+)	JSON インデックス
配列の要素検索 (`ANY`, `@>`)	GIN	○	×（配列型なし）	×
部分一致 `LIKE '%abc%'`	GIN + pg_trgm	○	× (全文検索で代用)	× (全文検索で代用)
地理空間・範囲型	GiST / SP-GiST	○ (PostGIS)	SPATIAL INDEX	Spatial Index
巨大な時系列テーブル（数億行）	BRIN	○	×	Columnstore で代替
IP アドレス・階層データ	GiST / SP-GiST	○	×	×

PostgreSQL は拡張インデックスが豊富で、MySQL は B-Tree + 全文検索 + 空間 + JSON 向け Multi-Valued 中心、SQL Server は列ストアやフルテキストがやや別体系です。RDBMS を選ぶ段階で「必要な索引種別が揃っているか」を確認しておくと後悔が減ります。

GIN — JSON / 配列 / 全文検索の主役

GIN（Generalized Inverted Index）は「1 行が複数の値を持つ列」に対する逆引き索引です。JSON のキー、配列の要素、文書のトークンなど、列の中身が複数値のときに B-Tree では表現できない「ある値を含む行を探す」操作を高速化します。

代表的なユースケース:

JSONB の @>（含有判定） — data @> '{"tag":"sql"}'
配列の &&（重なり判定）/ @>（含有判定）
tsvector の全文検索 — to_tsvector('japanese', body) @@ to_tsquery(...)
pg_trgm 拡張による LIKE '%abc%' — 任意位置の部分一致も索引で高速化できる

欠点は 書き込みコストが B-Tree より重い こと。GIN は挿入時にトークン単位で追記されるため、write-heavy なテーブルでは fastupdate = on（PostgreSQL のデフォルト）でバッファに溜めて、定期的にマージする運用になります。

sql

-- PostgreSQL: JSONB 含有検索
CREATE INDEX idx_events_payload ON events USING GIN (payload);
SELECT * FROM events WHERE payload @> '{"user_id": 42}';

-- 配列の要素検索
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql'];

-- pg_trgm で LIKE '%abc%' を高速化
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm
  ON users USING GIN (name gin_trgm_ops);

BRIN — 巨大な時系列テーブルの切り札

BRIN（Block Range INdex）は PostgreSQL 固有のインデックスで、「物理順序が列の値と相関している」テーブルで威力を発揮します。各ブロック範囲（128 ページなど）の min/max だけを保持する、非常に軽量な索引です。

使いどころは 時系列ログ・イベントテーブル。created_at で append-only に増えるテーブルは物理順序 ≒ 時刻順なので、BRIN は「この時刻範囲はこのブロックにだけある」を数 KB のサイズで保持できます。数億行でも B-Tree の数百 MB に対して BRIN は数 MB で済みます。

向く: append-only / 物理順序と相関がある列（created_at, 自動採番 id, センサーデータの timestamp）
向かない: 更新が散らばるテーブル、物理順序と関係ない列

MySQL / SQL Server には BRIN はありませんが、SQL Server は Columnstore Index、MySQL はパーティショニング＋B-Tree の組み合わせで似た効果を狙うことになります。

sql

-- PostgreSQL: 数億行のログテーブルに BRIN
CREATE INDEX idx_logs_created_brin
  ON logs USING BRIN (created_at)
  WITH (pages_per_range = 64);

-- 時刻範囲クエリが数 MB の索引で完結
SELECT * FROM logs
WHERE created_at >= '2026-04-01' AND created_at < '2026-04-07';

Hash / GiST / SP-GiST — 脇役の出番

実務で登場頻度は落ちますが、知っておくと困ったときに助かる 3 種。

Hash: 等値検索専用。B-Tree より一定サイズで O(1) を狙えるが、範囲・ソートは不可。PostgreSQL 11 以降は WAL 対応で本番利用可能になったが、B-Tree がほぼ常に同等以上なので採用機会は限定的
GiST: 地理空間（PostGIS）、範囲型（tsrange, int4range）、類似文字列検索。「重なり判定 &&」「含有 @>」など、2 次元以上の関係を索引化できる
SP-GiST: 空間分割木（Quad-Tree, k-d Tree ベース）。IP アドレス、電話番号のプレフィクス、階層的データに強い

PostgreSQL 以外では GiST / SP-GiST に相当する汎用機構はなく、SPATIAL INDEX（MySQL / SQL Server）が地理空間の限定用途を担います。