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)SelectivityCardinalityTotal Rows \text{Selectivity} \frac{\text{Cardinality}}{\text{Total Rows}}SelectivityTotal 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 使用两种模式来更新 Cardinality1. 持久化统计 (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 genderM 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 变化及执行计划是否回归正常。思维升级在设计索引时不仅考虑“查什么”还要考虑“区分度如何”。将高基数列前置提升过滤效率。