SQLインデックス: B-Tree、カバリング、部分インデックス

SQLインデックス: B-Tree、カバリング、部分インデックス

インデックスは、追加のストレージと書き込み操作の低速化と引き換えに、データ取得を劇的に高速化するデータ構造です。適切なインデックスの選択は、最も影響の大きいパフォーマンス最適化の1つです。

B-Treeインデックス（デフォルト）

CREATE INDEX idx_users_email ON users(email);

B-treeインデックスはデータをソートされたツリー構造に格納し、効率的な検索、範囲スキャン、ソートを可能にします。ほとんどのデータベースのデフォルトのインデックス型です。

最適な用途: 等値検索（=）、範囲検索（<、>、BETWEEN）、ソート（ORDER BY）、前方一致（LIKE 'abc%'）。

複合（マルチカラム）インデックス

CREATE INDEX idx_orders_status_date ON orders(status, order_date);

カラムの順序が重要です。このインデックスは status でのフィルタリング、または status + order_date でのフィルタリングには有効ですが、order_date 単独では使用されません（最左プレフィックスルール）。

カバリングインデックス

カバリングインデックスはクエリに必要なすべてのカラムを含み、テーブルにアクセスせずにインデックスだけで応答できます:

CREATE INDEX idx_orders_covering
ON orders(customer_id, order_date, total);

-- このクエリは完全にカバーされます:
SELECT order_date, total FROM orders WHERE customer_id = 42;

部分（フィルタ付き）インデックス

条件に一致する行のサブセットのみをインデックス化します:

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

部分インデックスは、クエリが一貫して同じ条件でフィルタリングする場合、より小さく高速です。

インデックスを作成すべき場合

• WHERE、JOIN、ORDER BY句で頻繁に使用されるカラム
• 外部キーカラム（見落とされがちだがJOINパフォーマンスに重要）
• カーディナリティの高いカラム（多数の異なる値を持つ）

インデックスを作成すべきでない場合

• 小さなテーブル（フルスキャンの方がインデックス検索より速い）
• カーディナリティの低いカラム（少数の異なる値しかないブールフラグなど）
• インデックスのメンテナンスコストが読み取りの利益を上回る書き込みが多いテーブル
• クエリでほとんど使用されないカラム

インデックス使用状況の監視

-- PostgreSQL: 未使用のインデックスを検出
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

ストレージの無駄と書き込みの低下を招く未使用のインデックスを除去するため、定期的にインデックスを監査しましょう。適切に選択されたインデックスは、ミリ秒と数分の差を生みます。