MySQL查询优化器的成本模型：从统计信息到执行计划选择的内幕 MySQL查询优化器的成本模型从统计信息到执行计划选择的内幕一、InnoDB统计信息的双轨制MySQL优化器做出执行计划决策的基础是统计信息。InnoDB提供两种统计信息模式持久化统计信息innodb_stats_persistentON默认和非持久化统计信息。两者的行为差异直接影响执行计划的稳定性。持久化统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中服务器重启后依然保留。非持久化统计信息只在内存中每次重启后需要ANALYZE TABLE重新收集。控制持久化统计信息的关键参数参数默认值含义innodb_stats_persistent_sample_pages20持久化统计的采样页面数innodb_stats_auto_recalcON表变更10%后自动重新计算innodb_stats_persistentON是否使用持久化统计innodb_stats_transient_sample_pages8非持久化统计的采样页面数二、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_statuspending约占5%执行计划使用该索引。但到月底pending状态实际已占30%全表扫描反而更快。优化器因为使用旧的统计信息仍然选择索引扫描导致查询从50毫秒退化到5秒。-- 强制更新统计信息 ANALYZE TABLE order_db.orders; -- 查看执行计划中的rows估算偏差 EXPLAIN FORMATJSON 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_traceenabledon; SET optimizer_trace_max_mem_size1000000; -- 执行目标查询 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_traceenabledoff;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或调整成本常数。