MySQL 索引基数、统计信息与直方图：优化器为什么会选错索引，又该怎么修

个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL 索引基数、统计信息与直方图优化器为什么会选错索引又该怎么修一、优化器是个 CBO它全靠估算活着二、Cardinality索引基数与选择性2.1 定义2.2 Cardinality 是怎么算出来的随机采样2.3 控制统计信息的关键参数2.4 什么时候会自动重算三、统计信息失真长什么样四、直方图MySQL 8.0 给优化器的真实分布地图4.1 为什么要直方图4.2 创建与查看4.3 两种直方图怎么选4.4 直方图的代价与限制4.5 直方图 vs 索引什么时候用哪个五、其它影响估算的机制5.1 eq_range_index_dive_limitIN 列表太长时怎么办5.2 filtered 与 condition filtering5.3 range_optimizer_max_mem_size六、执行计划突变的排查脚本七、三个常见误区八、小结MySQL 索引基数、统计信息与直方图优化器为什么会选错索引又该怎么修最让人头疼的慢 SQL 不是一直慢而是昨天还好好的今天突然全表扫描。索引没动、SQL 没动、数据量只涨了 3% —— 唯一变的是优化器心里的那本账统计信息。本文把挺像但不该混为一谈的三件事讲清楚索引基数Cardinality、InnoDB 统计信息、MySQL 8.0 直方图并给出一套执行计划突变的标准排查流程。一、优化器是个 CBO它全靠估算活着MySQL 的查询优化器是CBOCost-Based Optimizer基于成本的优化器。它做决策的方式不是有哪些索引而是估算每种执行路径的成本 → 挑成本最低的那条而成本的输入几乎全部来自估算值估算输入用于决定什么表有多少行全表扫描的成本基数索引的 Cardinalityref访问大概会命中多少行条件的过滤率范围扫描的返回行数索引的页数量/深度IO 成本是否内存/磁盘临时表GROUP BY、排序的成本估算错了就一定选错计划。而估算的来源就是本文的主题。一个常见误解以为EXPLAIN里的rows是精确值。它是预估值。看到rows和实际返回行数差一个数量级时先怀疑统计信息别急着怀疑索引。二、Cardinality索引基数与选择性2.1 定义Cardinality基数 索引列中不重复值的数量。SHOWINDEXFROMorders;输出里Cardinality列就是它。注意两件事每张表的每个索引、每个列都有一行Seq_in_index区分它是估算值不是精确值MyISAM 存的是精确值InnoDB 是采样估算基于它派生出最实用的一个指标 ——选择性Selectivity选择性 S Cardinality / 表总行数 N列例子选择性适不适合单列索引主键 id2000 万个不同值 / 2000 万行1.0完美手机号基本不重复接近 1.0适合订单状态 status6 个取值极低不适合放组合索引即可是否删除 is_deleted2 个取值0.00000005千万别单建经验阈值是20%~30%当某个值的命中比例高到一定程度命中行数超过全表约两成优化器一般就会放弃这个索引 —— 因为先查二级索引、再随机 IO 回表的代价已经超过直接顺序扫描整表了。2.2 Cardinality 是怎么算出来的随机采样为什么是估算值因为精确统计太贵 —— 每次 DML 都重新算一遍不同值数量的话写入就别干了。InnoDB 的做法是采样1. 取得 BTree 叶子节点总数 A 2. 随机抽 n 个叶子节点n 默认为 20 3. 统计每个被抽样页面的不同记录数 P1, P2, ..., Pn 4. Cardinality ≈ (P1 P2 ... Pn) × A / n因为抽的是页而不是行所以如下情况特别容易失真值分布不均匀倾斜某些值恰好密集分布在没被抽到的页上刚大批量导入后还没触发重算cardinality 还停留在导入前表很大但抽样页数很少样本代表不了整体2.3 控制统计信息的关键参数参数默认值8.0作用innodb_stats_persistentON统计信息持久化到磁盘5.6.6 起默认开innodb_stats_persistent_sample_pages20持久化模式下采样的页数调大更准但 ANALYZE 更慢innodb_stats_transient_sample_pages8非持久化模式下的采样页数innodb_stats_auto_recalcON变更后是否后台自动重算innodb_stats_on_metadataOFF访问information_schema.TABLES等是否触发重算务必保持 OFFinnodb_stats_methodnulls_equalNULL 如何参与 cardinality 计算持久化之后统计信息存在这两张 MySQL 内部表里可以直接查SELECTdatabase_name,table_name,index_name,stat_name,stat_value,sample_size,last_updateFROMmysql.innodb_index_statsWHEREdatabase_nameshopANDtable_nameorders;-- n_rows 就是优化器认为的表行数SELECTtable_name,n_rows,clustered_index_size,sum_of_other_index_sizesFROMmysql.innodb_table_statsWHEREdatabase_nameshopANDtable_nameorders;stat_name里的n_diff_pfx01表示前 1 列的不同值个数n_diff_pfx02是前 2 列组合的不同值个数依次类推 ——这直接对应组合索引各前缀的 selectivity。2.4 什么时候会自动重算InnoDB 在innodb_stats_auto_recalcON时由后台线程处理触发条件大致是自上次统计以来表中约 1/16≈6%的行发生了变化或者累计修改计数达到20 亿次专治反复更新同一行注意这是异步的批量导入 500 万行的瞬间统计信息更新可能还没发生此时执行 SQL 就容易踩坑。也可以手动触发ANALYZETABLEorders;⚠️ 两个坑ANALYZE 会加读锁并且要采样大表上不是免费的别放进高频定时任务表自上次 ANALYZE 以来完全没有变化的话再执行一次不会真正重算MySQL 会跳过第二点导致一个很经典的现象ANALYZE TABLE跑完了cardinality 一动没动。解决办法是先让它认为有变化比如INSERT再DELETE一行或直接重算相关表。三、统计信息失真长什么样一个真实感很强的场景-- 表 5000 万行其中 amount 100000 的只有 100 行EXPLAINSELECT*FROMordersWHEREamount100000;-- key: NULL, rows: 25000000, Extra: Using where ← 优化器认为命中一半但如果你FORCE INDEX试试EXPLAINSELECT*FROMordersFORCEINDEX(idx_amount)WHEREamount100000;-- type: range, rows: 120差了 20 万倍。原因通常有二统计信息过期ANALYZE TABLE后 cardinality 回归正常范围条件本身估算困难MySQL 对这种开区间的估算能力有限只能基于相邻 bucket / 记录距离线性外推遇到严重倾斜就崩第一种好解决第二种才是需要直方图出马的地方。四、直方图MySQL 8.0 给优化器的真实分布地图4.1 为什么要直方图先理解优化器的一个天真假设列与列之间相互独立且每个值的出现频率均匀。于是两个条件组合时优化器把各自的选择率直接相乘P(city杭州 AND status2) P(city杭州) × P(status2)现实几乎从来不成立。举例条件优化器估算实际占比city 杭州1/100 1%0.8%status 2已取消1/5 20%22%组合1% × 20% 0.2%18%杭州的取消率奇高估算差了 90 倍 → 优化器选了 nested loop 索引实际要回表 900 万行直接打爆。直方图的作用就是告诉优化器某列的真实值分布让它不再假设每个值出现概率相同。4.2 创建与查看-- 单列直方图桶数可省略默认 100ANALYZETABLEordersUPDATEHISTOGRAMONstatusWITH8BUCKETS;-- 多列同时建注意是每列独立的直方图不是联合直方图ANALYZETABLEordersUPDATEHISTOGRAMONstatus,cityWITH16BUCKETS;-- 删除ANALYZETABLEordersDROPHISTOGRAMONstatus;查看SELECTSCHEMA_NAME,TABLE_NAME,COLUMN_NAME,HISTOGRAMFROMinformation_schema.COLUMN_STATISTICSWHERESCHEMA_NAMEshopANDTABLE_NAMEorders\G典型的 JSON等高直方图{buckets:[[2026-01-01,2026-03-14,0.32,412],[2026-03-15,2026-06-30,0.88,907],[2026-07-01,2026-09-30,1.0,255]],data-type:date,null-values:0.0,collation-id:8,last-updated:2026-09-30 11:20:33.000000,sampling-rate:1.0,histogram-type:equi-height,number-of-buckets-specified:8}4.3 两种直方图怎么选类型触发条件bucket 结构适合singleton单值不同值个数少于桶数[值, 累计频率]低基数列状态、类型、省份equi-height等高不同值个数多于桶数[下界, 上界, 累计频率, 该桶 distinct 数]高基数列金额、时间、分数桶数取值范围1~1024官方建议从小开始逐步加大低基数列如 status 有 6 个值桶数 ≥ distinct 值数就够比如 8~16中基数列几百到几千个值从 32 起步配合 EXPLAIN 看filtered是否贴近真实超高基数列身份证、UUID直方图意义不大优化器也用不上判断有没有效果看EXPLAIN的filtered列它表示存储引擎返回的行里估计还有百分之多少能满足 WHERE。建直方图后filtered应该明显贴近真实的百分比。EXPLAINSELECT*FROMordersWHEREshop_id88ANDcreate_timeBETWEEN2026-01-01AND2026-03-01;-- 建直方图前filtered 33.33拍脑袋-- 建直方图后filtered 8.71贴近真实4.4 直方图的代价与限制必须提前知道的三条限制说明不会自动更新建完之后是静态快照数据分布漂移后会逐渐失真需要自己定重算策略只有单列直方图MySQL 8.0不支持多列联合直方图Oracle 有扩展统计PG 有多列统计。对上述city status 相关性问题直方图只能分别修正两列各自的分布估计解决不了组合关联这个根子上的问题有内存上限histogram_generation_max_mem_size默认 20MB超限会随机采样此时sampling-rate 1.0且两次生成的结果可能不一致SHOWVARIABLESLIKEhistogram_generation_max_mem_size;SETSESSIONhistogram_generation_max_mem_size64000000;-- 需要更精确时临时调大构建直方图要把列数据读进内存做排序CPU 和内存开销都不小务必放在低峰执行别放进日常定时任务。4.5 直方图 vs 索引什么时候用哪个维度索引直方图维护成本每次 DML 都要维护建完就固定零 DML 开销数据变化后自动跟上不会自动更新估算手段index dive精确但贵采样直方图便宜主要收益加速数据访问改善行数估算不改变访问路径本身适合场景高频查询条件列不值得建索引的条件列、严重倾斜的列、以及需要考虑组合选择率的查询IN列表很长时index dive 成本高直方图估算更划算一句话索引用来找数据直方图用来估算准。一个经常性的误区是以为建了直方图查询会变快 —— 它只是让优化器不犯错。五、其它影响估算的机制5.1eq_range_index_dive_limitIN 列表太长时怎么办优化器估算col IN (v1, v2, ..., vN)的命中行数有两种办法Index dive真的下潜到 BTree 里数精确但每个值一次 → 成本高基于统计的估算用 cardinality 直接除便宜粗糙eq_range_index_dive_limit默认200IN 列表元素个数超过它优化器就放弃 index dive 改用估算。SHOWVARIABLESLIKEeq_range_index_dive_limit;SETeq_range_index_dive_limit2000;-- IN 列表很大且要求估算准时可调大所以IN里面塞了 500 个值突然全表扫了的罪魁祸首经常是它。5.2filtered与 condition filteringEXPLAIN的filtered是存储引擎返回行中预计还能通过 WHERE 的百分比。它默认依赖一个简单的启发式比如col const给 100%范围给 ~11%只有在有索引或直方图时才会明显变准。这也是判断统计信息 / 直方图有没有起作用的最快入手点。5.3range_optimizer_max_mem_size多值IN/ 多范围条件的组合可能在 range optimizer 里消耗大量内存超过限制时优化器会退化成较粗糙的计划。默认 8MB8.0。SHOWVARIABLESLIKErange_optimizer_max_mem_size;六、执行计划突变的排查脚本遇到昨天还好好的类慢 SQL按这个顺序走-- 第 1 步先看预估和实际差多少EXPLAINSELECT...;-- rowsEXPLAINANALYZESELECT...;-- 8.0.18actual rows-- 第 2 步核对 cardinality 与真实区分度SHOWINDEXFROMorders;SELECTCOUNT(DISTINCTstatus),COUNT(*)FROMorders;-- 第 3 步核对优化器眼里的行数SELECTn_rowsFROMmysql.innodb_table_statsWHEREdatabase_nameshopANDtable_nameorders;SELECTCOUNT(*)FROMorders;-- 第 4 步重算统计信息低峰执行ANALYZETABLEorders;-- 第 5 步仍有严重倾斜的条件列加直方图ANALYZETABLEordersUPDATEHISTOGRAMONstatusWITH16BUCKETS;-- 第 6 步兜底确认优化器就是算不准时EXPLAINSELECT*FROMordersFORCEINDEX(idx_xxx)WHERE...;-- 或者更精细的 optimizer hintMySQL 8.0SELECT/* SET_VAR(eq_range_index_dive_limit2000) */*FROMordersWHEREidIN(...);第 6 步的FORCE INDEX是治标不是治本但它能在不改动索引结构的前提下先把故障止血之后再用统计信息 / 直方图做根治。七、三个常见误区误区 1ANALYZE TABLE可以随便跑反正是优化——大表 ANALYZE 要采样 N 个页并加读锁成本不低。而且 ANALYZE 之后统计信息变了执行计划有可能变得更差。它是纠偏工具不是日常保健药。误区 2Cardinality 是精确值可以直接当字典用——它是抽样估算可能和实际相差很远。要精确 distinct 值得用COUNT(DISTINCT col)。反过来也不要因为SHOW INDEX里 cardinality 显示为 0 就以为索引坏了 ——新建索引时它是 0ANALYZE 之后才会生成。误区 3直方图能让查询变快——直方图不改变访问路径本身它只改变成本估算让优化器不再错误地选择某条路径。如果原本计划就是对的建了直方图一分钱也不会快。八、小结MySQL 是CBO一切都建立在估算之上EXPLAIN的rows/filtered都是预估值Cardinality 索引列不重复值的估算数量由随机采样若干叶子页外推得到选择性 S Cardinality / 总行数命中比例高到约 20%~30% 时优化器通常放弃索引走全表扫描所以区分度太低的列单独建索引没意义持久化统计信息存在mysql.innodb_table_stats/mysql.innodb_index_stats可直接SELECT查关键参数innodb_stats_persistent默认 ON、innodb_stats_persistent_sample_pages默认 20、innodb_stats_auto_recalc默认 ON、innodb_stats_on_metadata务必 OFF自动重算触发线约1/16 行变更或20 亿次修改且是异步的ANALYZE TABLE是重兵器大表成本高、表无变化时会跳过、且可能让计划变更差别天天跑直方图MySQL 8.0 起提供列的真实值分布用于修正均匀分布 列间独立的错误假设桶数 1~1024低基数用 singleton、高基数用 equi-height效果看EXPLAIN的filtered直方图不自动更新、不支持多列联合、受histogram_generation_max_mem_size限制需采样索引用来找数据直方图用来估算准两者互补而非替代IN列表超过eq_range_index_dive_limit默认 200时放弃 index dive 转估算这是 IN 大量值导致计划突变的常见原因排查顺序对比预估/实际 → 核对 cardinality → ANALYZE → 直方图 →FORCE INDEX兜底