MySQL / 系统课时
判断索引选择性与基数
判断索引选择性与基数
字段出现在 WHERE 条件中,并不代表它就适合单独建立索引。本课通过可计算的数据分布理解 Cardinality 与选择性,并进一步判断重复值、数据倾斜和组合索引前缀怎样改变真实访问成本。
学习目标
- 区分索引基数、全局选择性与具体谓词选择性
- 使用 SQL 检查字段的不同值数量和重复值分布
- 根据数据分布而不是 WHERE 字段列表判断索引价值
基数与选择性分别描述什么
基数通常表示一个列或索引前缀包含多少个不同值。MySQL 的 SHOW INDEX 会在 Cardinality 中给出基于统计信息得到的估算值,它不是实时精确计数,并可能随着采样和统计信息更新而变化。
对于单列,可以用不同值数量与总行数的比值建立一个粗略的全局选择性概念:
一百万行中有九十万个不同的用户 ID 时,该列全局选择性很高;如果只有 PAID、PENDING、CANCELLED 等少量状态,全局选择性就较低。
但真正执行某个谓词时,更重要的是该条件实际匹配多少行。即使一个字段只有四个不同值,其中某个罕见值只占很小比例,查询该值仍可能具有较强的过滤能力。
用 SQL 检查真实数据分布
下面构造一个小型订单表,同时比较状态列与客户列的不同值数量,并计算每个状态实际匹配的记录比例。
DROP TABLE IF EXISTS demo_orders;
CREATE TABLE demo_orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL,
KEY idx_status (status),
KEY idx_customer (customer_id)
) ENGINE = InnoDB;
INSERT INTO demo_orders (id, customer_id, status, created_at) VALUES
(1, 101, 'PAID', '2026-09-01 10:00:00'),
(2, 102, 'PAID', '2026-09-01 10:01:00'),
(3, 103, 'PAID', '2026-09-01 10:02:00'),
(4, 104, 'PAID', '2026-09-01 10:03:00'),
(5, 105, 'PAID', '2026-09-01 10:04:00'),
(6, 106, 'PAID', '2026-09-01 10:05:00'),
(7, 107, 'PAID', '2026-09-01 10:06:00'),
(8, 108, 'PAID', '2026-09-01 10:07:00'),
(9, 109, 'CANCELLED', '2026-09-01 10:08:00'),
(10, 110, 'CANCELLED', '2026-09-01 10:09:00'),
(11, 101, 'PENDING', '2026-09-01 10:10:00'),
(12, 102, 'REFUND', '2026-09-01 10:11:00');
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT status) AS status_distinct,
ROUND(COUNT(DISTINCT status) / COUNT(*), 4) AS status_selectivity,
COUNT(DISTINCT customer_id) AS customer_distinct,
ROUND(COUNT(DISTINCT customer_id) / COUNT(*), 4) AS customer_selectivity
FROM demo_orders;
SELECT
status,
COUNT(*) AS matched_rows,
ROUND(COUNT(*) / (SELECT COUNT(*) FROM demo_orders), 4) AS predicate_selectivity
FROM demo_orders
GROUP BY status
ORDER BY matched_rows DESC, status;预期结果
total_rows status_distinct status_selectivity customer_distinct customer_selectivity
12 4 0.3333 10 0.8333
status matched_rows predicate_selectivity
PAID 8 0.6667
CANCELLED 2 0.1667
PENDING 1 0.0833
REFUND 1 0.0833从全局看,customer_id 比 status 更接近一行一个不同值。但只看全局比例仍不够:status = 'PAID' 匹配三分之二的数据,而 status = 'PENDING' 只匹配十二分之一。相同的索引列,具体谓词的过滤能力可能完全不同。
Cardinality 是统计估计而不是精确答案
可以直接查看 MySQL 为两个索引保存的基数估计。
SHOW INDEX FROM demo_orders;预期结果
结果包含 PRIMARY、idx_status 和 idx_customer 等索引记录。
Cardinality 列给出各索引前缀的不同值数量估计;在这种小表上,估计值可能与 COUNT(DISTINCT ...) 的精确结果不同。优化器使用统计信息估算某个访问路径大概要读取多少行,再把索引遍历、回表、扫描等成本纳入比较。因此 Cardinality 的作用是帮助估算成本,而不是充当业务数据的精确计数器。
当数据变化较快或统计信息不能代表当前分布时,估算可能明显偏离真实情况。排查优化器选错索引时,除了看 SQL 和索引定义,还要检查估算行数是否与实际数据分布相符。
重复值多不等于索引一定无效
低基数字段经常被简单归类为不适合索引,这个结论过于绝对。索引是否值得使用,取决于具体查询匹配的比例、需要读取的列、是否发生回表,以及它是否还能承担排序或组合索引前缀等任务。
以示例数据为例,status = 'PAID' 匹配大量记录,单独走 idx_status 的收益可能有限;status = 'PENDING' 只匹配少量记录,在更大的同类数据集上可能更适合索引访问。这正是数据倾斜影响成本判断的地方。
生产数据中,如果某个状态长期占 99%,另一个只占 1%,仅知道该列有两个不同值无法描述这种差异。工程师应通过分组计数等方法确认高频查询真正命中的数据比例,再判断索引是否有价值。
组合索引要看逐级增加的基数
对于 (tenant_id, status, created_at) 这样的组合索引,不能只说 status 基数低就否定整个索引。先按 tenant_id 缩小到单个租户后,再使用 status 和时间范围继续过滤,最终候选记录可能已经很少。
SHOW INDEX 对组合索引提供索引前缀级的 Cardinality 统计信息。设计索引时,应先确认目标 SQL 的过滤条件,再测量各条件实际匹配比例,最后结合组合索引前缀、回表数量和查询频率判断总成本,而不是简单套用“高基数才建索引”的规则。