MySQL的Cardinality的庖丁解牛
MySQL 的 Cardinality（基数）是数据库优化器（Optimizer）做出“走索引”还是“全表扫描”决策的核心依据。
它的本质是：索引列中唯一值数量的估算值。
- 高基数 (High Cardinality)：唯一值很多（如身份证号、UUID）。索引区分度高，查询效率高。
- 低基数 (Low Cardinality)：唯一值很少（如性别、状态码）。索引区分度低，查询效率低，甚至不如全表扫描。
如果把索引比作图书馆的目录卡片：
- Cardinality就是这张卡片背后对应了多少本不同的书。
- 如果卡片是“书名”，每本书名都不同，Cardinality 很高 -> 查书名很快。
- 如果卡片是“出版社”，只有几家出版社，Cardinality 很低 -> 查出版社可能比直接去书架找还慢。
一、定义与计算：它不是精确计数
1. 公式
Cardinality≈总行数 (Rows)平均重复次数 \text{Cardinality} \approx \frac{\text{总行数 (Rows)}}{\text{平均重复次数}}Cardinality≈平均重复次数总行数(Rows)
或者更直观地理解：
Cardinality= 索引列中不同值 (Distinct Values)的数量。- 例如：表有 1000 行。
id列：1000 个不同值 -> Cardinality ≈ 1000。gender列：2 个不同值 (M/F) -> Cardinality ≈ 2。
2. 关键特性：估算值 (Estimate)
- InnoDB 不实时计算 Cardinality。因为每次插入/删除都重新统计全表太慢了。
- 它通过采样 (Sampling)算法估算。
- 结果：
SHOW INDEX看到的 Cardinality 可能每次都不一样，也可能不准确。
💡 核心洞察：Cardinality 是一个“概率统计值”，而非“精确计数值”。优化器依赖这个近似值来做代价模型（Cost Model）判断。
二、对优化器的影响：决定执行计划
优化器使用 Cardinality 来计算选择性 (Selectivity)：
Selectivity=CardinalityTotal Rows \text{Selectivity} = \frac{\text{Cardinality}}{\text{Total Rows}}Selectivity=Total RowsCardinality
1. 高选择性 (High Selectivity) -> 走索引
- 场景：
SELECT * FROM users WHERE id = 1001; - Cardinality: 接近总行数。
- 优化器判断：通过这个索引，我能迅速定位到极少数的行（甚至 1 行）。
- 动作：使用
ref或eq_ref访问类型，走索引。
2. 低选择性 (Low Selectivity) -> 全表扫描
- 场景：
SELECT * FROM users WHERE gender = 'M'; - Cardinality: 2 (假设只有男女)。
- 优化器判断：通过这个索引，我要回表查询50% 的数据。随机 I/O 开销巨大。
- 动作：忽略索引，使用
ALL访问类型，全表扫描。
3. 范围查询 (Range)
- 场景：
SELECT * FROM orders WHERE create_time > '2023-01-01'; - 优化器判断：根据直方图或索引统计，估算满足条件的行数比例。
- 动作：如果比例小，走索引；如果比例大（如超过 20%-30%），全表扫描。
三、统计机制：InnoDB 是如何“猜”的？
InnoDB 使用两种模式来更新 Cardinality：
1. 持久化统计 (Persistent Statistics) -默认推荐
- 存储位置：
mysql.innodb_index_stats系统表。 - 更新时机：
- 表结构变更 (
ALTER TABLE)。 - 手动执行
ANALYZE TABLE。 - 当表数据变化超过一定阈值（默认 10%）。
- 表结构变更 (
- 优点：重启后统计信息不丢失，稳定。
- 缺点：如果数据剧烈变化未及时更新，统计信息会过时。
2. 瞬时统计 (Transient Statistics)
- 存储位置：内存中。
- 更新时机：每次打开表时，或定期后台线程更新。
- 算法：随机潜入 (Random Dive)。
- InnoDB 随机选取索引树中的 N 个叶子页（默认 8-20 个）。
- 统计这些页中的不同值数量。
- 推算整棵树的 Cardinality。
- 优点：无需磁盘 I/O 读取统计表。
- 缺点：波动大，不同次查询可能得到不同结果。
💡 核心洞察：当你发现执行计划不稳定（有时走索引，有时不走），往往是瞬时统计波动导致的。切换到持久化统计并定期
ANALYZE可解决此问题。
四、认知陷阱与最佳实践
陷阱 1：认为 Cardinality 必须精确
- 误区：“为什么
SHOW INDEX显示的 Cardinality 和COUNT(DISTINCT col)不一样？” - 真相：不一样是正常的。只要数量级正确，优化器就能做出大致正确的决定。不要为了追求精确而频繁
ANALYZE TABLE，这会锁表并消耗资源。
陷阱 2：忽略联合索引的最左前缀
- 场景：联合索引
(A, B, C)。 - 真相：
Cardinality(A)：A 列的唯一值数。Cardinality(A, B)：A 和 B 组合后的唯一值数。Cardinality(A, B, C)：三者组合后的唯一值数。- 规律：越往后，Cardinality 通常越高（或相等）。
- 优化：将区分度最高的列放在联合索引的前面，有助于优化器更早地过滤数据。
陷阱 3：低基数列建索引无用？
- 误区：“性别只有两个值，建索引没用。”
- 真相：单独建索引确实没用。但如果是联合索引的一部分，就有用。
- 例如：
(gender, age)。虽然gender区分度低，但它能把数据分成两半，然后在每一半里按age排序。对于WHERE gender='M' ORDER BY age这样的查询，非常有效。
- 例如：
陷阱 4：统计信息过期导致性能骤降
- 现象：昨天还快的 SQL，今天突然慢了。
- 原因：大量数据导入/删除后，Cardinality 未更新，优化器误判。
- 解决：
或者开启自动更新：ANALYZETABLEyour_table;innodb_stats_persistent = ON innodb_stats_auto_recalc = ON
🚀 总结：Cardinality 全景图
| 维度 | 高基数 (High) | 低基数 (Low) |
|---|---|---|
| 示例 | ID, UUID, 手机号 | 性别, 状态, 布尔值 |
| 选择性 | 高 (> 0.1) | 低 (< 0.01) |
| 索引效果 | 极佳(快速定位) | 差(可能全表扫描) |
| 优化器行为 | 倾向于走索引 (ref/range) | 倾向于全表扫描 (ALL) |
| 建议 | 适合做主键或独立索引 | 适合做联合索引的前缀或后缀 |
终极心法：
Cardinality 的本质，是“数据的区分度”。
它是优化器眼中的“地图精度”。精度高，路径规划就准；精度低，就容易迷路。
不要迷信精确值，要关注趋势和量级。
定期维护统计信息，让优化器始终拥有最新的“地图”。
于统计中见分布，于估算中见决策；以选择性为尺，解索引之牛，于查询优化中，求精准之真。
行动指令：
- 检查现状：对核心大表执行
SHOW INDEX FROM table_name;，观察各索引的 Cardinality。 - 对比验证：运行
SELECT COUNT(DISTINCT col) FROM table;，与SHOW INDEX的结果对比，理解误差范围。 - 强制更新：如果发现执行计划异常，执行
ANALYZE TABLE，观察 Cardinality 变化及执行计划是否回归正常。 - 思维升级：在设计索引时，不仅考虑“查什么”，还要考虑“区分度如何”。将高基数列前置，提升过滤效率。