MySQL查询优化器的成本模型：从统计信息到执行计划选择的内幕

MySQL查询优化器的成本模型：从统计信息到执行计划选择的内幕

一、InnoDB统计信息的双轨制

MySQL优化器做出执行计划决策的基础是统计信息。InnoDB提供两种统计信息模式：持久化统计信息（innodb_stats_persistent=ON，默认）和非持久化统计信息。两者的行为差异直接影响执行计划的稳定性。

持久化统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中，服务器重启后依然保留。非持久化统计信息只在内存中，每次重启后需要ANALYZE TABLE重新收集。

控制持久化统计信息的关键参数：

二、Cardinality估算与索引选择

Cardinality（基数）是索引区分度的度量——表示索引列中不同值的估计数量。优化器使用Cardinality来判断使用哪个索引能过滤掉最多的行。

-- 查看表的统计信息 SELECT database_name, table_name, n_rows, clustered_index_size / 1024 AS ibd_size_mb FROM mysql.innodb_table_stats WHERE database_name = 'order_db' AND table_name = 'orders'; -- 查看索引的Cardinality估算 SELECT database_name, table_name, index_name, stat_name, -- n_diff_pfxNN: 前NN列的不同值数量 stat_value, stat_description, sample_size FROM mysql.innodb_index_stats WHERE database_name = 'order_db' AND table_name = 'orders' ORDER BY index_name, stat_name;

Cardinality估算采用随机采样方式——InnoDB随机读取若干个索引页面，根据这些页面中的值分布推算整体基数。采样页面越多，估算越准确，但收集成本越高。这导致一个经典问题：统计信息漂移——随着数据增长，旧的统计信息不再反映真实分布。

一个典型的生产案例：订单表按月分区，每月新增500万条记录。月初统计信息显示order_status='pending'约占5%，执行计划使用该索引。但到月底pending状态实际已占30%，全表扫描反而更快。优化器因为使用旧的统计信息，仍然选择索引扫描，导致查询从50毫秒退化到5秒。

-- 强制更新统计信息 ANALYZE TABLE order_db.orders; -- 查看执行计划中的rows估算偏差 EXPLAIN FORMAT=JSON SELECT * FROM order_db.orders WHERE order_status = 'pending' AND create_time > '2026-07-01';

三、成本常数的含义与影响

MySQL优化器使用成本模型来比较不同执行计划的代价。成本单位并非物理时间，而是一个抽象的度量——由两个核心常数定义：

-- 查看成本模型参数（MySQL 5.7+） SELECT * FROM mysql.server_cost; -- row_evaluate_cost: 0.2 —— 评估一行记录的代价 -- memory_temptable_create_cost: 2.0 -- memory_temptable_row_cost: 0.2 SELECT * FROM mysql.engine_cost WHERE engine_name = 'InnoDB'; -- io_block_read_cost: 1.0 —— 从磁盘随机读取一个页面的代价 -- memory_block_read_cost: 0.5 —— 从内存读取一个页面的代价

io_block_read_cost和row_evaluate_cost是成本模型的两大支柱。全表扫描的成本 = IO读取代价 × 页面数 + 行评估代价 × 行数。索引扫描的成本 = IO读取代价 × 索引页面数 + 行评估代价 × 估计行数 + 回表IO代价。

理解这些常数有助于解释优化器的"反常"决策。例如当row_evaluate_cost为0.2时，扫描100万行的评估成本是20万单位；但如果在机械硬盘上随机读取100万行数据的IO成本远超索引读取。这就是为什么在SSD上优化器更倾向于全表扫描——io_block_read_cost实际上降低了。

""" MySQL成本模型的简化实现，展示执行计划选择的内部逻辑 """ from dataclasses import dataclass @dataclass class CostConstants: io_block_read_cost: float = 1.0 memory_block_read_cost: float = 0.5 row_evaluate_cost: float = 0.2 key_compare_cost: float = 0.1 @dataclass class TableStats: total_rows: int data_pages: int page_size: int = 16384 # 16KB @dataclass class IndexStats: name: str cardinality: int # 不同值的估计数量 pages: int # 索引占用页面数 is_clustered: bool = False class OptimizerCostModel: """MySQL优化器成本模型简化版""" def __init__(self, constants: CostConstants = CostConstants()): self.c = constants def table_scan_cost(self, stats: TableStats) -> float: """全表扫描成本""" io_cost = stats.data_pages * self.c.io_block_read_cost cpu_cost = stats.total_rows * self.c.row_evaluate_cost return io_cost + cpu_cost def index_lookup_cost( self, table: TableStats, index: IndexStats, selectivity: float ) -> float: """ 索引查找成本 Args: selectivity: 过滤选择率 (0.0 ~ 1.0) """ estimated_rows = int(table.total_rows * selectivity) # B+树搜索成本：log2(索引基数) × key_compare_cost tree_traversal = ( __import__('math').log2(max(index.cardinality, 1)) * self.c.key_compare_cost ) # 索引页扫描成本 pages_to_read = int(index.pages * selectivity) + 1 index_io = pages_to_read * self.c.io_block_read_cost # 回表成本（聚簇索引无回表成本） if index.is_clustered: row_fetch_cost = 0 else: # 每条符合条件的记录一次随机IO（估算） row_fetch_cost = estimated_rows * self.c.io_block_read_cost cpu_cost = estimated_rows * self.c.row_evaluate_cost return tree_traversal + index_io + row_fetch_cost + cpu_cost

四、优化器Trace的分析方法

当执行计划不符合预期时，优化器Trace是定位问题的利器。它能展示优化器考虑了哪些候选计划、各计划的成本是多少、最终为何选择了某个方案。

-- 开启优化器Trace SET optimizer_trace="enabled=on"; SET optimizer_trace_max_mem_size=1000000; -- 执行目标查询 SELECT o.order_id, o.user_id, o.amount, o.status FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE o.user_id = 12345 AND o.create_time > '2026-06-01'; -- 获取Trace SELECT trace FROM information_schema.optimizer_trace LIMIT 1\G -- 分析完成后关闭Trace SET optimizer_trace="enabled=off";

Trace输出包含几个关键部分：

• considered_execution_plans：所有被评估的执行计划
• rows_estimation：每个表的行数估算过程
• attached_conditions_summary：条件下推情况
• cost_of_plan：最终选择的计划的成本分解

常见的问题模式："range_scan_alternatives"中出现了"chosen": false而你期望使用该索引，说明Cost估算使优化器认为全表扫描更优。这时需要检查索引Cardinality是否准确，或者row_evaluate_cost是否需要调整。

五、总结

MySQL优化器的成本模型本质上是统计信息加成本常数的函数。Cardinality估算的准确性直接决定索引选择的正确性——而采样机制天然存在偏差风险。理解io_block_read_cost和row_evaluate_cost这两个魔法数字，能帮助你解释为什么优化器在SSD和HDD上的行为截然不同。优化器Trace是解决"为什么走这个索引"问题的终极工具，它把黑盒决策变成了白盒可追溯的过程。统计信息不准确时的标准处理流程是：ANALYZE TABLE → 检查执行计划变化 → 如果统计信息刷新后仍不理想，考虑使用Hint或调整成本常数。