S08-A23 · 索引的选择性：为什么"加了索引还是全扫"

一张 500 万行的订单表，status 列加了索引，而 WHERE status = 3 仍然全表扫。原因是：status 只有 3 个取值，而 status=3 占了 70% 的行——优化器算出来"走索引要回表 350 万次"，不如直接扫全表。

索引的"选择性（selectivity）"是"不同值的比例"。它不是"加了索引就有效"的开关，而是"条件筛掉多少行"的算术。

1. 选择性的三个度量

度量	含义
基数（cardinality）	不同值的个数
选择性 = 基数 / 行数	越接近 1 越好
命中比例 = 命中行数 / 总行数	越接近 0 越好

第三项才是"优化器关心的"："这个条件筛掉多少"。于是"基数高"不等于"某次查询的选择性高"——这个区分解释了"为什么加了索引还是全扫"："列上有索引"是"能力"，而"这次查询是否用得上"是"算术"——两件事。
因为"具体值"可能占大多数。

2. "走不走索引"的三个判据

判据	说明
命中比例（通常 < 10–20% 才走）	回表成本
是否覆盖索引	不回表 → 高比例也走
排序/分组是否需要	可用于避免排序

第一项是"经验阈值"：命中比例低（筛掉大部分）时，走索引才划算——因为"每命中一行要回表一次"。

第二项是"绕过阈值的方法"：如果索引里已有查询需要的所有列（覆盖索引），就不必回表这个"不回表"的价值可以量化：回表一次约等于"再查一次主键索引"，于是"命中 100 万行、回表 100 万次"可能比"全扫 100 万行"更慢。
第二项的"代价"是"索引体积"：覆盖索引把"查询会用的列"都放进去，于是索引变大、写入变慢——它是"读优化换写代价"。
——于是"即使命中 50% 也可能走索引"。

第三项常被忽略：ORDER BY create_time LIMIT 20 走索引能"只读 20 行"，而全扫要"扫完再排"。

-- 三种情况的实际差异（status 只有 3 个值，status=3 占 70%）
EXPLAIN SELECT * FROM orders WHERE status = 3;            -- ALL（全扫，正常）
EXPLAIN SELECT * FROM orders WHERE status = 1;            -- ref（占 5%，走索引）
EXPLAIN SELECT id, status FROM orders WHERE status = 3;   -- index（覆盖索引，可走）
-- 关键：同一个索引，三种查询三种计划

3. "低选择性列"的三种用法

用法	是否有效
单独作为条件	通常无效
与高选择性列组合（联合索引）	有效（前缀是高选择性列）
作为"覆盖索引"的一部分	有效

第二项是"最常被搞反的"：联合索引 (status, create_time) 与 (create_time, status) 效果完全不同第二项的"细节"是"联合索引只能"从最左列开始"用"：于是 (status, create_time) 能"单独按 status 查"，而"单独按 create_time 查"用不上它。
——前者"按 status 过滤后再按时间排"，后者"按时间范围过滤"。

4. "联合索引"的顺序怎么定

原则	说明
高选择性列放前	让前缀筛得更狠
等值条件放前、范围放后	范围列会"截断"后续列
覆盖查询列放最后	支持"仅索引扫描"

第二项的"原因"是"索引是"按列排序的字典""：一旦某列是"范围"，后续列就"不再全局有序"——于是"只能用于覆盖，不能用于过滤"。
第二项是关键规则：WHERE a = ? AND b > ? 的联合索引应是 (a, b)——因为"范围条件之后的列"在索引里无法"用于过滤"（只能"用于覆盖"）。

-- 两种顺序的差别（a 等值、b 范围）
CREATE INDEX idx_ab ON t (a, b);     -- 好：a 定位后再 b 范围扫描
CREATE INDEX idx_ba ON t (b, a);     -- 差：b 是范围，a 无法用于过滤
-- 判据：等值在前、范围在后

"选择性"可以直接算，一条 SQL 就够，别靠猜：

-- 三个数：该列基数、某个值的占比、索引是否被选中
SELECT COUNT(DISTINCT status) AS card, COUNT(*) AS rows FROM orders;
SELECT SUM(status=3)/COUNT(*) AS ratio_3 FROM orders;          -- 0.70 → 全扫正常
EXPLAIN SELECT * FROM orders WHERE status = 3;                  -- 看 type/key
-- 判据：ratio > 0.2 且 type=ALL → 不是索引问题，是数据分布问题

5. "为什么统计信息会让计划变"的三种情形

情形	说明
数据分布变化	`status=3` 从 5% 涨到 70%
统计信息过期	优化器按旧分布估算
直方图缺失	无法识别"数据倾斜"

第一项解释了大量"上线后突然变慢"：某次业务变化让"某个值"占了大多数第一项的"识别"是"看这个值的行数占比随时间的变化"：从 5% 涨到 70% 就是"索引失效"的时刻——而 SQL 一个字都没改。
，于是"原本走索引"的查询变成"全扫"——而 SQL 没变。

"覆盖索引"要"正好覆盖"，多一列都不行——而它可以用 EXPLAIN 的 Extra 判：

-- Extra 里出现 "Using index" 才是"仅索引扫描"
EXPLAIN SELECT id, status FROM orders WHERE status = 3;
-- Extra: Using where; Using index    ← 覆盖生效
EXPLAIN SELECT * FROM orders WHERE status = 3;
-- Extra: Using where                 ← 必须回表
-- 判据：有 "Using index" 才能"不回表"

6. 排错：五种"索引不生效"

现象	成因	处理方向
有索引但全扫	命中比例高	看选择性
换了值就慢	数据倾斜	加直方图/改条件
联合索引只用了第一列	顺序/范围截断	调列序
覆盖索引没生效	`SELECT *` 带出非索引列	只取需要的列
昨天快今天慢	统计信息过期	`ANALYZE`

第四项的"修法"很直接：把 SELECT * 改成"列出必需的列"——而它顺便减少了"网络传输"，一举两得。
第四项最常见："覆盖索引"要求"查询的列都在索引里"，而 SELECT * 一写就破坏了它。

"索引顺序"的调整应当"按查询集合"定，而不是"按单个查询"：

// 三步定列序（用"查询清单"而不是"单个 SQL"）
1 列出"用到这个索引"的全部查询（通常是 3–8 个）
2 找"共同的最左列"（出现最多、且多为等值）
3 把"最常用的范围列"放在等值列之后
// 反例：为一个查询把顺序调成"最优"，结果另外五个查询都用不上
// 补充：调完顺序要"逐个查询跑 EXPLAIN"，而不是"只看新的那个"

7. 边界：什么情况不必纠结选择性

场景	为什么	做法
表很小（万行以内）	全扫也快	不管
查询总是"取少量"（按主键）	主键选择性最高	不管
只做写入（无范围查询）	索引只为唯一约束	不管
已有"分区裁剪"	分区已筛掉大部分	看分区

判断口径一句话："这个条件会筛掉多少行"。最后一条与"写代价"有关的边界：每个索引都在"写入时维护"：一张表 8 个索引，插入一行要改 8 处——于是"索引数量"也要算进"写性能"的账。
筛掉很多（< 10%）才值得"单独建索引"；筛掉很少（> 30%）时，它更适合"作为联合索引的后缀"或"干脆不建"。

最后一个"经验"补充："复合条件里既无高选择性列、又无排序需求"时，索引通常只是负担——它让写入变慢、占用空间，而查询仍是全扫。这类索引应当被识别并删除，而不是"留着以防万一"。

8. 自测

选择性与"命中比例"的差别是什么？为什么"基数高"不等于"这次查询走索引"？
"走不走索引"的三个判据里，哪一个是"绕过阈值"的方法？
为什么"同一索引、三种查询、三种计划"？
联合索引为什么"等值在前、范围在后"？"范围列之后的列"还能用于什么？
"上线后突然变慢而 SQL 没变"最可能是哪种原因？
判断"是否值得单独建索引"的那个问题是什么？