数据库索引

数据库索引是为特定访问模式维护的辅助数据结构，用额外存储与写放大换取更低查询成本；是否真正使用取决于谓词、排序、选择性、统计信息与优化器成本估算。

#concept #growing #backend #database

[!info] related notes

所属 MOC: 数据库 MOC

前置概念: SQL, SQL 查询执行流程

B+Tree: B+Tree 数据库索引

联合/覆盖: 联合索引、最左前缀与覆盖索引

选择性: 索引选择性、基数与代价

聚簇/二级: 聚簇索引与二级索引

验证: EXPLAIN

数据库索引

一句话定义

索引不是“让查询变快的开关”，而是：

数据库为某些访问模式额外维护的数据结构。

它把部分查询从“大范围扫描”变成更便宜的定位、范围遍历或有序读取。

代价是：

存储空间；
INSERT / UPDATE / DELETE 维护成本；
vacuum/rebuild/maintenance；
更多 planner 选择复杂度。

Access Path

没有合适索引时，数据库可能：

scan many/all rows
→ evaluate predicate

有索引时可能：

navigate index
→ locate candidate rows/pages
→ fetch required data

但“存在索引”不等于 optimizer 一定选择它。

常见结构

不同数据库支持不同 index access method。

常见：

B-Tree / B+Tree family；
Hash；
GIN；
GiST；
BRIN；
full-text / vector-specific index。

结构适不适合，取决于查询算子。

例如：

equality；
range；
prefix；
containment；
spatial；
full-text；
vector nearest neighbor；

需要不同能力。

选择性

如果条件匹配表中绝大多数行：

WHERE enabled = true

即使 enabled 有索引，planner 也可能认为：

直接扫描更便宜。

因此索引收益与：

cardinality；
selectivity；
table size；
clustering/locality；
cache state；

都相关。

详见索引选择性、基数与代价。

联合索引

例如：

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);

它不是两个独立单列索引的简单拼接。

列顺序会影响：

哪些 predicate 可高效定位；
range 之后还能怎样利用后续列；
ORDER BY 是否可复用 index order；
covering 能否成立。

详见联合索引、最左前缀与覆盖索引。

Covering / Index-only

如果查询所需列都能从 index entry 获取，某些数据库可以避免额外读取 heap/table row。

这就是 covering / index-only 的价值之一。

但能否真正做到，还依赖具体 DB 的 storage engine 和 visibility 机制。

Index 与 ORDER BY

有序索引有时可以直接按 index order 输出结果，从而避免额外 sort。

但是否成立取决于：

filter；
column order；
ASC/DESC；
NULL ordering；
query shape。

不能看到 ORDER BY 就机械加索引。

Write Amplification

每新增一个 index，写入一行数据时可能都要额外更新这个 index。

因此“给所有 WHERE 列都建索引”通常会造成：

更慢写入；
更大磁盘；
更多 cache pressure；
更长维护时间。

Optimizer 与统计信息

数据库依据 cost model 和 statistics 选择计划。

如果统计信息过旧或数据分布极度倾斜，可能出现：

明明有索引却不使用；
选择错误 join order；
错估 row count。

所以诊断索引问题时应该看实际 plan，而不是猜。

EXPLAIN

索引优化最终要回到：

query
→ EXPLAIN / EXPLAIN ANALYZE
→ actual rows/cost/time
→ identify bottleneck
→ change index/query/schema
→ rerun

详见 EXPLAIN。

学习检查

为什么“有索引”不等于“查询一定走索引”？
selectivity 为什么会影响 planner 是否使用索引？
联合索引为什么不能简单等价于多个单列索引？
covering index 的价值是什么？
ORDER BY 在什么情况下可能复用索引顺序？
索引为什么会增加写放大？
统计信息失真为什么会导致错误 plan？
给一个慢查询，说明如何用 EXPLAIN 判断问题到底在缺索引、索引顺序还是选择性太差。