[!info] related notes

• 所属 MOC：数据库 MOC
• 前置：数据库 Page 与 Buffer Pool、聚簇索引与二级索引
• 索引设计：联合索引与覆盖索引
• 验证：EXPLAIN / EXPLAIN ANALYZE

索引选择性、基数与代价

[!abstract] 核心结论 优化器不因“存在索引”就使用索引，而是比较候选执行计划的预计总成本。选择性、数据分布、Page 访问、回表、排序、缓存、并行和统计误差共同决定 Index Scan 是否划算。

基数与选择性

Cardinality

一列或复合列中不同值的数量：

id        → 接近行数
country   → 几十
is_active → 2

Selectivity

谓词筛选后保留的比例：

selectivity ≈ matching rows / total rows

匹配比例越小，索引通常越可能减少 Page 访问；但这不是唯一因素。

低基数列不一定不能建索引

status 只有少数值，但如果某个值极少：

99.9% completed
0.1% failed

查询 status='failed' 选择性仍然很好。还可能通过：

• Partial / Filtered Index；
• 与 Tenant、Time 的联合索引；
• Covering Index；
• Bitmap / Index Combination（按数据库能力）

获得价值。

所以基数只是数据概况，真正重要的是谓词对应的数据分布。

数据分布与相关性

优化器需要知道：

• Most Common Values；
• Histogram；
• Null Fraction；
• Distinct Count；
• 列间相关性；
• 物理顺序与索引顺序相关性。

例如 country 与 currency 高度相关，如果按独立概率相乘，估算会严重偏差。扩展统计或更好的 Schema 可能改善估算。

代价不只是 CPU

典型成本包含：

index traversal
+ index entries scanned
+ heap / table page fetches
+ random vs sequential I/O
+ filter CPU
+ sort / hash / aggregate
+ parallel setup and merge
+ memory spill

高选择性 Index Scan 通常访问少量行；低选择性 Secondary Scan 可能产生大量随机回表，反而不如顺序扫描。

回表改变临界点

Secondary Index
→ many row locators
→ random primary / heap page fetches

如果查询被索引覆盖，回表成本下降，优化器可能在更高匹配比例下仍选择 Index-only Scan。

这就是为什么“同一个 WHERE，加不加返回列”可能改变计划。

聚簇性 / Correlation

如果符合条件的 Row Locator 在物理 Page 上高度聚集，回表接近顺序访问；如果随机分散，成本更高。

递增时间索引与按时间组织的表通常相关性更好；随机 UUID Secondary Index 可能触发更多随机 Page 访问。具体仍应看真实统计。

统计信息为什么会失准

• 大量写入后未更新 Analyze；
• 数据倾斜发生变化；
• 相关列被当成独立；
• 参数化查询在不同参数上选择性差异很大；
• 函数、表达式和类型转换让统计难以使用；
• Join 中间结果估算误差层层放大。

错误计划常不是“优化器不会用索引”，而是优化器基于错误估算做了合理选择。

EXPLAIN 看什么

estimated rows vs actual rows
chosen scan type
loops
filter rows removed
index condition / recheck
sort method and spill
buffers / I/O
join order and join algorithm
planning vs execution time

重点关注估算数量级偏差，而不只看是否出现 Index Scan 字样。

[!warning] Cost 不是毫秒 大多数数据库的 Cost 是内部相对单位，用于比较计划；不能直接把 cost=100 解释成 100ms。

常见反模式

• 每个 WHERE 列都建单列索引；
• 只看 Distinct Count，不看频率分布；
• 强制索引掩盖统计或 Schema 问题；
• 只在小测试数据上验证；
• SELECT * 导致大量回表；
• 忽略写放大和索引维护成本；
• 把一次参数的计划当所有参数都适用。

优化顺序

confirm slow query and workload
→ inspect actual plan and I/O
→ compare estimated vs actual
→ inspect data distribution and statistics
→ reduce rows / columns earlier
→ design query-aligned index
→ retest read and write workload
→ monitor after deployment

掌握标准

• 能区分 Cardinality、Selectivity 和 Distribution。
• 能解释低基数索引何时仍有用。
• 能说明回表、覆盖和物理相关性如何改变成本。
• 能从 estimated vs actual 判断统计问题。
• 知道 Cost 是相对模型而非直接时间。
• 优化后同时评估写入、存储和缓存代价。

Related notes

• 联合索引与覆盖索引
• 数据库 Page 与 Buffer Pool
• EXPLAIN / EXPLAIN ANALYZE