PostgreSQL / 系统课时
判断列选择性
判断列选择性
单列索引是否值得建立,关键不在于列是整数还是文本,而在于查询谓词能过滤掉多少行。选择性高通常意味着索引更容易缩小访问范围;选择性低则可能让优化器认为顺序扫描更划算。
学习目标
- 能用数据分布而不是字段类型判断索引候选列
- 能区分列的基数、选择性和具体查询谓词
- 能结合执行计划验证选择性对索引访问路径的实际影响
用数据分布判断选择性
可以先观察列的不同值数量,再估算某个谓词会返回多少数据。简单场景下,可把列选择性理解为不同值数量与总行数的关系,但真正决定索引收益的是当前查询条件下的过滤效果。
例如订单表中 status 可能只有少数几个值,而 order_id 几乎每行不同。两者即使都能建立 B-Tree 索引,典型等值查询的过滤能力完全不同。
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT status) AS distinct_status,
COUNT(*) FILTER (WHERE status = 'PAID') AS paid_rows
FROM orders;预期结果
total_rows | distinct_status | paid_rows
------------+-----------------+----------
1000000 | 5 | 320000这个结果说明 status = 'PAID' 仍会命中大量行,单列索引的过滤能力可能有限;但这并不意味着它一定没价值,还要看查询是否同时带有其他高选择性条件、是否需要特定顺序,以及实际执行计划。
选择性必须结合谓词来看
不要只看列本身的“高基数”或“低基数”。同一列面对不同谓词,效果可能完全不同。比如时间列通常基数很高,但查询最近一年的数据仍可能返回全表的大部分行;相反,时间范围很窄时索引就可能非常有效。
判断一列是否值得索引,至少要回答三个问题:查询条件是什么、该条件通常返回多少行、结果集是否明显小于表规模。真正有用的是“谓词后的剩余行数”,而不是字段类型带来的先验判断。
用执行计划验证实际过滤效果
在候选列上建立测试索引后,应使用与业务一致的查询验证。EXPLAIN (ANALYZE, BUFFERS) 可以直接观察估算行数与实际行数,并判断优化器是否选择索引。
CREATE INDEX idx_orders_status ON orders (status);
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at
FROM orders
WHERE status = 'PAID';预期结果
Index Scan using idx_orders_status on orders
Index Cond: (status = 'PAID')
rows=320000
Execution Time: <TIME> ms这里的数字只是说明观察方法,实际结果取决于数据分布。若查询返回表中很大比例的行,优化器可能放弃索引而采用顺序扫描,这通常是合理选择,并不意味着索引定义错误。
高选择性不是唯一标准
索引价值还受到查询频率、排序需求、联合条件和写入成本影响。一个低选择性的列,如果经常与高选择性列组成联合查询,仍可能成为有效索引的一部分;反过来,一个高度唯一的列,如果几乎从不出现在过滤条件中,也没有建立索引的业务价值。
因此,选择索引列时应从“数据分布 + 查询谓词 + 实际行数 + 查询频率”一起判断。先测真实 SQL,再决定是否建立索引,比根据字段名称或数据类型直接下结论可靠得多。