データベースのインデックスといえば B-tree が第一の選択肢で、ほとんどのケースで B-tree を作っておけば十分です。ただし用途によっては B-tree では効率が悪い、もしくは原理的に対応できないケースがあり、そのときに各種専用インデックスを検討します。
本記事では B-tree 以外のインデックス種別とそれぞれの使いどころを整理します。
まずは B-tree（最も汎用的）
ほぼすべての RDBMS でデフォルト。等価検索・範囲検索・ソート・先頭一致 LIKE で利用可能で、最初に検討すべきインデックスです。詳細は なぜインデックスは速いのか？B-treeの内部構造を図解する を参照してください。
以下では「B-tree では足りない／不向き」なケースで使うインデックスを紹介します。
ハッシュインデックス
ハッシュ値を保存することで 等価検索を高速化 します。B-tree より単純な構造で、O(1) でエントリにアクセスできます。
ただし以下の制約があります。
- 範囲検索やソートには使えない（ハッシュ値は元の値の順序を保たないため）
- 前方一致 LIKE も不可
各 RDBMS の対応:
| RDBMS | 対応 |
|---|---|
| PostgreSQL | USING HASH で作成可能。PostgreSQL 10 以降クラッシュセーフ |
| MySQL InnoDB | 明示的なハッシュインデックスは作れない。内部で「適応的ハッシュインデックス」を自動使用 |
| MySQL MEMORY | デフォルトがハッシュインデックス |
実務では「ほぼ B-tree でよい」「ハッシュが必要な場面は限定的」と思っておけば十分です。MySQL の InnoDB は自動で適応的ハッシュを使うので、開発者が意識することは少ないです。
全文検索インデックス
LIKE '%キーワード%' のような中間一致・後方一致では B-tree が効きません。文章中のキーワード検索が必要な場合は 全文検索インデックス を使います。
| RDBMS | ネイティブ機能 | 演算子 |
|---|---|---|
| MySQL | FULLTEXT インデックス | MATCH AGAINST |
| PostgreSQL | GIN + tsvector | @@ |
| Oracle / SQL Server | Full-Text Index | CONTAINS |
日本語対応
MySQL 標準の FULLTEXT は CJK圏（日本語・中国語・韓国語）の解析精度に課題があります。本格的な日本語全文検索は以下の追加プラグインを検討します。
| プラグイン | 対象 RDBMS | 特徴 |
|---|---|---|
| Mroonga | MySQL | 日本語形態素解析、N-gram対応 |
| PGroonga | PostgreSQL | 日本語形態素解析、PostgreSQL拡張 |
| pg_bigm | PostgreSQL | 2-gram 全文検索 |
| pg_trgm | PostgreSQL | 3-gram、LIKE '%xxx%' も高速化可能 |
検索負荷が大きく RDBMS の全文検索機能では性能要件を満たせない場合は、Elasticsearch / OpenSearch などの専用全文検索エンジンを別途用意するのが定石です。
PostgreSQL 固有のインデックス
PostgreSQL は B-tree 以外のインデックスを充実させており、データ型に応じて使い分けできます。
GiST（Generalized Search Tree）
汎用検索ツリー。地理空間データ（PostGIS）、全文検索、範囲型、配列 など、B-tree では扱えない複雑なデータ型をサポートします。
-- 地理空間データの例（PostGIS）
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);GIN（Generalized Inverted Index）
転置インデックス。配列、jsonb、全文検索（tsvector）など、1つの値が複数の要素を持つデータ に強いインデックスです。
-- jsonb の検索を高速化
CREATE INDEX idx_users_data ON users USING GIN (data);
-- 全文検索
CREATE INDEX idx_articles_tsv ON articles USING GIN (to_tsvector('japanese', body));BRIN（Block Range Index）
連続的な物理ブロック範囲ごとに値の要約（min/max など）を保存する超軽量インデックス。
- テーブルの物理順序とよく相関する列 に効果的（例: 時系列の
sale_date、IoT センサーログ） - 超巨大テーブルでもインデックスサイズが極めて小さい
- ピンポイント検索より範囲検索に向く
SP-GiST（Space-partitioned GiST）
空間分割 GiST。GiST に似ていますが、より平衡が悪い分割木をサポート。電話番号や IP アドレスなど、データ分布に偏りがある場合に有効。
Bloom
拡張モジュール（CREATE EXTENSION bloom）。多列の等価検索 を1つの Bloom インデックスで近似します。データウェアハウスで「複数列のいずれかを使う検索パターンが多数ある」ケースで有効。
関数インデックス・式インデックス
CREATE INDEX ... ON table (UPPER(col)) のように 関数や式の結果 にインデックスを張る機能。
-- 大文字小文字を無視した検索を高速化
CREATE INDEX emp_up_name ON employees (UPPER(last_name));
-- インデックスを使うクエリ
SELECT * FROM employees WHERE UPPER(last_name) = 'WINAND';| RDBMS | 対応状況 |
|---|---|
| Oracle | 古くからサポート |
| PostgreSQL | 古くからサポート |
| MySQL | 8.0 以降 で正式サポート（5.7 以前は生成列＋インデックスで代用） |
| SQL Server | 計算列にインデックスを張る形でエミュレート |
MySQL 5.7 以前で関数インデックス相当を実現する場合:
ALTER TABLE employees ADD COLUMN last_name_upper VARCHAR(255) GENERATED ALWAYS AS (UPPER(last_name));
CREATE INDEX emp_up_name ON employees (last_name_upper);部分インデックス／フィルタリングされたインデックス
特定の条件を満たす行だけをインデックスに含める機能です。
-- PostgreSQL: 未処理メッセージだけをインデックス
CREATE INDEX queue_unprocessed
ON message_queue (created_at)
WHERE processed = 'N';
-- SQL Server: フィルタ選択されたインデックス
CREATE INDEX queue_unprocessed
ON message_queue (created_at)
WHERE processed = 'N';利点:
- 未処理行が常に少数なら、テーブルがいくら大きくなっても インデックスサイズはほぼ一定
- メンテナンスコスト（書き込みオーバーヘッド）も低く抑えられる
- 「処理済みは見ない」「論理削除フラグが立っていない行だけ見る」という典型パターンに最適
| RDBMS | 対応 |
|---|---|
| PostgreSQL | 部分インデックス（古くから対応） |
| SQL Server | フィルタ選択されたインデックス |
| Oracle | 関数インデックスで NULL を返す式と組み合わせて代用可能 |
| MySQL | 対応なし |
クラスタ化インデックス（索引構成表）
テーブル本体（ヒープ）を捨てて、インデックスのリーフに 全列を入れた構造。
| RDBMS | 仕様 |
|---|---|
| MySQL InnoDB | 主キーが常にクラスタ化インデックス（強制） |
| SQL Server | 主キーがデフォルトでクラスタ化インデックス（NONCLUSTERED で外せる） |
| Oracle | CREATE TABLE ... ORGANIZATION INDEX で IOT を作成 |
| PostgreSQL | 持たない（CLUSTER で物理並べ替えはできるが永続的ではない） |
利点:
- クラスタリングキー経由のアクセスが常に インデックスのみのスキャン に近い（テーブルアクセス不要）
弊害:
- セカンダリインデックス で本体への参照がクラスタリングキー（ROWID より長い）になる
- セカンダリ → クラスタの 2 回ツリー走査 が必要
- セカンダリインデックスが多いほどコストがかさむ
原則として「単一のインデックスしか持たないテーブルは IOT 向き、複数インデックスがあるテーブルはヒープ向き」と覚えておくとよいです。
ビットマップインデックス
複数の独立条件の AND/OR の組み合わせ に有利なインデックス。各値の出現を bitmap で管理し、ビット演算で複数列の条件を高速に解決します。
| 用途 | 評価 |
|---|---|
| データウェアハウス（読み取り中心） | ◎ |
| OLTP（書き込み多め） | × 書き込みのスケーラビリティが極端に悪く使えない |
対応 RDBMS: Oracle / Db2 など。MySQL や PostgreSQL は持ちません（PostgreSQL は実行時に Bitmap Heap Scan で類似の効果を得る場合があります）。
どれを選ぶか — 簡易フローチャート
- 等価／範囲検索／ソート／前方一致 LIKE → B-tree
- 中間一致 LIKE / 全文検索 → 全文検索インデックス（FULLTEXT / GIN+tsvector / Mroonga / pg_bigm）
- 配列 / JSON / 範囲型 / 地理空間データ（PostgreSQL のみ）→ GiST / GIN
- 時系列の超巨大テーブル＋範囲検索（PostgreSQL のみ）→ BRIN
- 関数や式の結果で検索する → 関数インデックス
- 特定条件の行だけインデックス化したい（PostgreSQL / SQL Server）→ 部分インデックス
- DWH の多列 AND/OR（Oracle / Db2）→ ビットマップインデックス
それ以外（ほとんどの OLTP）は B-tree で十分 です。
まとめ
- B-tree がほぼ常に第一選択。「足りない」場面でだけ専用インデックスを検討
- ハッシュは等価のみ／範囲やソートは不可
- 中間一致 LIKE は B-tree 不可。全文検索インデックスを使う
- PostgreSQL は GiST/GIN/BRIN/SP-GiST/Bloom など多様。データ型ごとに使い分け
- 関数インデックスは MySQL 8.0+ で正式サポート
- 部分インデックスは PostgreSQL / SQL Server。MySQL は非対応
- クラスタ化インデックスはセカンダリインデックスとのトレードオフがある
- ビットマップインデックスは DWH 専用。OLTP では使わない