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或调整成本常数。