SQL 索引Explained

31 of 31 rows

概念↕	说明↕	技巧↕
索引类型
⤢B-tree	用于等值、范围和有序扫描的平衡树。多数数据库的默认选择。	用于 =、<、>、BETWEEN、ORDER BY。不适用于全文搜索。
⤢Clustered	叶子层就是表本身的索引。行按键顺序存储。每表仅一个。	InnoDB 按主键聚集。没有它的 SQL Server 表是堆。
⤢Nonclustered	独立结构，保存键并附带指回基础表的指针。每表可建多个。	在堆上存行 ID；在聚集表上存聚集键。
⤢Hash	仅用于等值查找的哈希表。常数时间的精确匹配。	只用于 = 运算；范围和排序需要 B-tree。SQL Server 内存优化表的哈希索引要让桶数接近行数。
⤢GIN	广义倒排索引。把值映射到行。为数组和全文而生。	用于 jsonb、数组、tsvector、三元组搜索。构建比 B-tree 慢。
⤢GiST	广义搜索树。可扩展支持非标量类型和重叠查询。	用于几何范围、kNN、排他约束。pg_trgm 可加速 LIKE。
⤢SP-GiST	空间分区 GiST。用基数树或四叉树把数据切分成互不重叠的区域。	用于点、IP 前缀和前缀匹配。不适合重叠查询；那类查询请用 GiST。
⤢BRIN	块范围索引。按块范围存储 min/max。占用极小。	用于大规模且物理有序的表。构建便宜，选择性弱。
⤢Columnstore	以列段而非行存储的索引。高压缩比，批量执行。	用于多行少列的分析扫描。单行查找很慢。
⤢Full-text	MySQL 和 SQL Server 中专用的倒排词索引。按文本列构建。	使用 MATCH AGAINST 或 CONTAINS。Postgres 全文改用 tsvector 上的 GIN。
⤢Bitmap	Oracle 索引，每个不同值存一张位图。每个比特标记一行。	用于读多写少仓库中的低基数列。并发写按位图串行化。
⤢Descending	声明为 DESC 的索引列。该列的键按相反顺序存储。	混合排序如 (a ASC, b DESC) 必须用它。纯 DESC 排序也可以反向读取普通索引。
⤢Invisible	MySQL 8 中优化器忽略的索引。每次写入仍会维护它。	删除前先隐藏以测试影响。无需重建即可恢复可见。
设计
⤢Composite	多列索引。前导列必须匹配查询过滤条件的顺序。	等值列在前，范围列在后。两个单列索引替代不了一个复合索引。
⤢Partial	基于 WHERE 谓词的索引。SQL Server 称之为 filtered index。只覆盖匹配的行。	只索引 active='t' 的行。体积和写入成本减半。
⤢Covering (INCLUDE)	附加了非键列的 B-tree。纯索引扫描免去堆查找。	把常取的列加进 INCLUDE。索引仍可排序。
⤢Unique	强制不允许重复值的索引。拒绝冲突的插入。	用于自然键和一对一约束。UNIQUE NULL 允许多个 NULL。
⤢Expression	基于列的函数而非列本身构建的索引。	索引 lower(email) 或 date_trunc('day', created_at)。查询必须重复完全相同的表达式。
⤢Opclass	操作符类。定义索引如何比较和存储某一列的类型。	为 LIKE 'abc%' 设置 varchar_pattern_ops 以避免大小写折叠。默认适合等值和范围。
⤢Index-only scan	仅用索引页就能回答查询的计划。堆完全不被读取。	所取的每一列都必须在索引或 INCLUDE 列表中。Vacuum 保持可见性映射最新。
⤢Foreign keys	外键列在引用表上不会自动获得索引。	给用于连接或删除的每个 FK 列建索引。没有它，级联删除会扫描子表。
维护
⤢EXPLAIN	计划检查器。显示顺序扫描、索引选择、成本估算。	使用 EXPLAIN (ANALYZE, BUFFERS)。千万行表上的 Seq Scan 意味着缺索引。
⤢ANALYZE	对表列采样以构建规划器统计信息。直方图驱动索引选择。	批量加载后运行。过期统计导致索引列上的顺序扫描。
⤢Over-indexing	每个索引都增加写入开销。插入、更新、删除按索引逐个付费。	删除未使用的索引。在 pg_stat_user_indexes 中跟踪使用情况。
⤢pg_stat_user_indexes	Postgres 的逐索引使用视图。统计自重置以来的扫描数和读取的元组数。	筛选 idx_scan = 0 找出未使用的索引。用 pg_stat_reset() 重置计数器。
⤢CREATE INDEX CONCURRENTLY	不阻塞读写地构建索引。需要两次表扫描。	不能在事务内运行。失败的构建会留下待删除的 INVALID 索引。
⤢DROP INDEX	删除索引并释放其存储。默认持有排他锁。	繁忙表上使用 DROP INDEX CONCURRENTLY。删除前先查使用统计。
⤢REINDEX	重建膨胀的索引。从死元组回收空间。	生产环境用 REINDEX CONCURRENTLY。否则锁会阻塞写入。
⤢Bloat	MVCC 表及其索引中累积的死元组和页间隙。	用 pgstattuple 度量。勤做 Vacuum；死空间占主导时 REINDEX。
⤢Fillfactor	写入时页面填充的百分比。空闲空间吸收未来的更新。	在热更新表上降到 70-90。满页会强制页分裂。
⤢CLUSTER	按索引的物理顺序重写表。一次性操作。	与 BRIN 搭配获得有序块。后续写入不维持该顺序。