PostgreSQL / 系统课时

判断列选择性

判断列选择性

单列索引是否值得建立,关键不在于列是整数还是文本,而在于查询谓词能过滤掉多少行。选择性高通常意味着索引更容易缩小访问范围;选择性低则可能让优化器认为顺序扫描更划算。

学习目标

  • 能用数据分布而不是字段类型判断索引候选列
  • 能区分列的基数、选择性和具体查询谓词
  • 能结合执行计划验证选择性对索引访问路径的实际影响

用数据分布判断选择性

可以先观察列的不同值数量,再估算某个谓词会返回多少数据。简单场景下,可把列选择性理解为不同值数量与总行数的关系,但真正决定索引收益的是当前查询条件下的过滤效果。

例如订单表中 status 可能只有少数几个值,而 order_id 几乎每行不同。两者即使都能建立 B-Tree 索引,典型等值查询的过滤能力完全不同。

sql
SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT status) AS distinct_status,
    COUNT(*) FILTER (WHERE status = 'PAID') AS paid_rows
FROM orders;

预期结果

text
 total_rows | distinct_status | paid_rows
------------+-----------------+----------
   1000000  |               5 |   320000

这个结果说明 status = 'PAID' 仍会命中大量行,单列索引的过滤能力可能有限;但这并不意味着它一定没价值,还要看查询是否同时带有其他高选择性条件、是否需要特定顺序,以及实际执行计划。

选择性必须结合谓词来看

不要只看列本身的“高基数”或“低基数”。同一列面对不同谓词,效果可能完全不同。比如时间列通常基数很高,但查询最近一年的数据仍可能返回全表的大部分行;相反,时间范围很窄时索引就可能非常有效。

判断一列是否值得索引,至少要回答三个问题:查询条件是什么、该条件通常返回多少行、结果集是否明显小于表规模。真正有用的是“谓词后的剩余行数”,而不是字段类型带来的先验判断。

用执行计划验证实际过滤效果

在候选列上建立测试索引后,应使用与业务一致的查询验证。EXPLAIN (ANALYZE, BUFFERS) 可以直接观察估算行数与实际行数,并判断优化器是否选择索引。

sql
CREATE INDEX idx_orders_status ON orders (status);

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at
FROM orders
WHERE status = 'PAID';

预期结果

text
Index Scan using idx_orders_status on orders
  Index Cond: (status = 'PAID')
  rows=320000
Execution Time: <TIME> ms

这里的数字只是说明观察方法,实际结果取决于数据分布。若查询返回表中很大比例的行,优化器可能放弃索引而采用顺序扫描,这通常是合理选择,并不意味着索引定义错误。

高选择性不是唯一标准

索引价值还受到查询频率、排序需求、联合条件和写入成本影响。一个低选择性的列,如果经常与高选择性列组成联合查询,仍可能成为有效索引的一部分;反过来,一个高度唯一的列,如果几乎从不出现在过滤条件中,也没有建立索引的业务价值。

因此,选择索引列时应从“数据分布 + 查询谓词 + 实际行数 + 查询频率”一起判断。先测真实 SQL,再决定是否建立索引,比根据字段名称或数据类型直接下结论可靠得多。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

判断列选择性

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通