mysql中优化器的成本模型与执行计划选择

MySQL优化器通过成本模型估算执行计划优劣，将IO_cost、CPU_cost、memory_cost加权为抽象cost值，选最小者；成本依赖统计信息和系统变量，不准会导致误选计划。

MySQL优化器怎么估算“哪个执行计划更便宜”

MySQL优化器不靠猜，它有一套成本模型（Cost Model），把每个可能的执行计划换算成一个数字——cost，选cost最小的那个。这个cost不是时间，而是抽象的“资源开销单位”，主要由三部分构成：IO_cost（读页次数）、CPU_cost（行处理开销）、memory_cost（临时内存使用）。其中IO_cost通常占大头，尤其在磁盘表场景下。

关键点在于：这些成本值不是固定常量，而是依赖统计信息（如INFORMATION_SCHEMA.STATISTICS、mysql.innodb_table_stats）动态计算的。如果ANALYZE TABLE没跑过，或者数据剧烈变化后没更新统计信息，优化器就容易算错——比如低估了某索引的回表行数，结果选了index_merge而不是更优的ref扫描。

如何查看优化器实际用了哪些成本参数

MySQL 5.7+ 支持通过optimizer_trace看到每一步的成本估算细节。开启后执行查询，再查information_schema.OPTIMIZER_TRACE就能看到完整推演过程，包括各访问路径的cost、rows预估、是否用到索引等。

• 开启方式：

  SET optimizer_trace="enabled=on,one_line=off";

• 执行目标SQL（比如SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';）
• 查结果：

  SELECT trace FROM information_schema.OPTIMIZER_TRACE;

• 重点关注considered_execution_plans里的cost字段和rows字段是否合理；若rows比实际结果大几个数量级，大概率是统计信息过期

为什么FORCE INDEX有时反而让查询变慢

强制索引会跳过成本计算，但优化器原本放弃该索引，往往是因为它算出来走这个索引的总cost更高——比如二级索引+回表的IO开销，大于全表扫描的顺序读开销（尤其当WHERE条件匹配度高、需要返回大量行时）。这时硬加FORCE INDEX，等于让优化器“闭眼执行”，可能触发大量随机IO。

典型误用场景：

• 对宽表（几十列）用SELECT * + FORCE INDEX查非覆盖索引 → 每行都要回主键查找，IO_cost爆炸
• 在ORDER BY字段上强制索引，但排序结果集很大，导致sort_buffer溢出写磁盘 → memory_cost失控
• 复合索引顺序和WHERE条件不匹配，强制后仍无法跳过范围扫描前缀 → 实际rows远超预估

影响成本模型判断的几个隐藏开关

除了统计信息，还有几个系统变量会直接修改成本权重，进而改变执行计划选择：

• eq_range_index_d

  

  ive_limit：控制优化器对IN列表是否做索引下潜（index dive）统计。设为0会禁用下潜，改用采样估算，可能导致IN (1,2,3,...,100)被当成低选择性条件
• range_optimizer_max_mem_size：限制范围扫描时用于计算区间数的最大内存。太小会导致优化器放弃精确区间分析，退化为粗略估算
• join_cache_level：影响嵌套循环连接中是否启用块嵌套循环（BNL）或哈希连接，改变CPU_cost与memory_cost权衡
• use_stat_tables：决定是否使用持久化统计信息（innodb_table_stats），设为preferably且表有持久统计时，会绕过ANALYZE TABLE临时采样

这些变量不常调，但一旦线上出现“同一条SQL在不同实例上执行计划不同”，优先检查它们是否一致。成本模型本身不神秘，但它极度依赖输入质量——统计不准、配置偏移、数据分布突变，任何一个都可能让cost从参考值变成误导值。