SQLインデックスExplained

31 of 31 rows

概念↕	説明↕	ヒント↕
インデックスの種類
⤢B-tree	等値・範囲・ソート済みスキャン用の平衡ツリー。多くのデータベースのデフォルト。	=、<、>、BETWEEN、ORDER BY に使う。全文検索には使わない。
⤢Clustered	葉レベルがテーブル自体のインデックス。行はキー順に格納される。テーブルに1つ。	InnoDB は主キーでクラスタ化する。持たない SQL Server テーブルはヒープ。
⤢Nonclustered	キーとベーステーブルへのポインタを保持する独立した構造。テーブルに多数作れる。	ヒープでは行 ID を格納する。クラスタ化テーブルではクラスタリングキーを格納する。
⤢Hash	等値検索専用のハッシュテーブル。定数時間の完全一致。	= 演算のみに使う。範囲とソートには B-tree が必要。SQL Server のメモリ最適化ハッシュは行数に近いバケット数が必要。
⤢GIN	Generalized Inverted Index。値から行へのマッピング。配列と全文検索用に作られた。	jsonb、配列、tsvector、トライグラム検索に使う。B-tree より構築が遅い。
⤢GiST	Generalized Search Tree。非スカラー型と重なりクエリへ拡張可能。	幾何範囲、kNN、排他制約に使う。pg_trgm は LIKE を高速化する。
⤢SP-GiST	空間分割 GiST。基数木または四分木でデータを重ならない領域に分割する。	ポイント、IP プレフィックス、前方一致に使う。重なりクエリには不可。GiST を使うこと。
⤢BRIN	Block Range Index。ブロック範囲ごとに min/max を格納。フットプリントが極小。	物理的にソートされた大きなテーブルに使う。構築は安価、選択性は弱い。
⤢Columnstore	行ではなく列セグメントとして格納されるインデックス。高圧縮、バッチ実行。	多数の行と少数の列に対する分析スキャンに使う。単一行検索は遅い。
⤢Full-text	MySQL と SQL Server の専用転置語インデックス。テキスト列ごとに構築される。	MATCH AGAINST か CONTAINS を使う。Postgres の全文検索は代わりに tsvector 上の GIN を使う。
⤢Bitmap	個別の値ごとに1つのビットマップを格納する Oracle インデックス。各ビットが1行を示す。	読み取り中心のウェアハウスで低カーディナリティ列に使う。同時書き込みはビットマップごとに直列化される。
⤢Descending	DESC 宣言されたインデックス列。その列のキーは逆順で格納される。	(a ASC, b DESC) のような混合ソートに必要。単純な DESC ソートは通常インデックスの逆読みでも処理できる。
⤢Invisible	オプティマイザーが無視する MySQL 8 のインデックス。書き込みのたびに維持される。	削除前にインデックスを隠して影響を試す。リビルドなしで再表示できる。
設計
⤢Composite	複数列インデックス。先頭列はクエリのフィルタ順に一致させる必要がある。	等値列を先に、次に範囲列。単一インデックス2つでは複合インデックス1つの代わりにならない。
⤢Partial	WHERE 述語上のインデックス。SQL Server ではフィルター選択されたインデックスと呼ぶ。一致する行のみをカバー。	active='t' の行のみをインデックス化。サイズと書き込みコストが半分になる。
⤢Covering (INCLUDE)	非キー列を追加した B-tree。インデックスオンリースキャンはヒープ検索を避ける。	頻繁に取得する列を INCLUDE で追加。インデックスのソート可能性を保つ。
⤢Unique	重複値を禁止するインデックス。衝突する挿入を拒否する。	自然キーと1対1制約に使う。UNIQUE NULL は多数の NULL を許す。
⤢Expression	列そのものではなく列の関数上に構築されたインデックス。	lower(email) や date_trunc('day', created_at) をインデックス化。クエリは完全に同じ式を繰り返す必要がある。
⤢Opclass	演算子クラス。インデックスが1つの列型をどう比較・格納するかを定義する。	ケースフォールディングなしの LIKE 'abc%' には varchar_pattern_ops を設定。デフォルトは等値と範囲向き。
⤢Index-only scan	インデックスページだけからクエリに答えるプラン。ヒープは決して読まれない。	取得する全列がインデックスか INCLUDE リストに必要。Vacuum が可視性マップを最新に保つ。
⤢Foreign keys	外部キー列は参照元テーブルに自動インデックスされない。	結合や削除に使う FK 列はすべてインデックス化する。ないとカスケード削除は子テーブルをスキャンする。
メンテナンス
⤢EXPLAIN	プラン検査ツール。シーケンシャルスキャン、インデックス選択、コスト見積りを表示。	EXPLAIN (ANALYZE, BUFFERS) を使う。1000万行テーブルでの Seq Scan はインデックス不足を意味する。
⤢ANALYZE	テーブル列をサンプリングしてプランナー統計を作る。ヒストグラムがインデックス選択を左右する。	一括ロード後に実行。古い統計はインデックス済み列で seq scan を引き起こす。
⤢Over-indexing	各インデックスは書き込みオーバーヘッドを追加。挿入・更新・削除はインデックスごとに支払う。	未使用インデックスは削除する。使用状況は pg_stat_user_indexes で追跡。
⤢pg_stat_user_indexes	インデックスごとの使用状況を示す Postgres のビュー。統計リセット以降のスキャン数と読み取りタプル数を数える。	idx_scan = 0 で絞り込んで未使用インデックスを見つける。カウンターは pg_stat_reset() でリセット。
⤢CREATE INDEX CONCURRENTLY	読み書きをブロックせずにインデックスを構築する。テーブルスキャンが2回必要。	トランザクション内では実行できない。失敗した構築は INVALID インデックスを残すので削除する。
⤢DROP INDEX	インデックスを削除しストレージを解放する。デフォルトで排他ロックを取る。	繁忙テーブルでは DROP INDEX CONCURRENTLY を使う。削除前に使用統計を確認。
⤢REINDEX	肥大化したインデックスを再構築する。デッドタプルから領域を回収。	本番では REINDEX CONCURRENTLY を使う。ないとロックが書き込みをブロックする。
⤢Bloat	MVCC テーブルとそのインデックスに蓄積するデッドタプルとページの隙間。	pgstattuple で測定。こまめに Vacuum。デッド領域が支配的なら REINDEX。
⤢Fillfactor	書き込み時にページを詰める割合。空き領域は将来の更新を吸収する。	更新頻発テーブルでは 70-90 に下げる。満ページはページ分割を強制する。
⤢CLUSTER	テーブルをインデックス順に物理的に並べ替えて書き直す。一回限りの操作。	BRIN と組み合わせるとソート済みブロックになる。以降の書き込みでは順序は維持されない。