③ MySQL 是如何根据成本优化选择执行计划的

对应第 94 ~ 96 讲 · IO/CPU 成本模型 · 全表扫描与索引访问的成本公式 · 多表关联方案选择

总览 ① 数据访问方式 ② 多表关联原理 ③ 成本优化 ④ 规则优化 ⑤ EXPLAIN 字段 ⑥ Extra 与调优 架构与实践篇 01~20 实战与高可用篇 109~130
成本模型 —— 所有计算的两块基石 全表扫描的成本计算（三步走） 索引访问的成本计算（四步走） 多表关联的成本估算与执行计划选择 —— 把单表方法用两遍 先懂成本是什么 再逐方案估算 单表方法 同样方法用到每个表 SQL 语句到来 往往存在多种可行方案 找出候选方案 全表扫描 + 每个可用索引 逐方案估算成本 IO 成本 + CPU 成本 选成本最低的方案 确定最终执行计划 IO 成本 · 磁盘读页 把数据页从磁盘读到内存 每读 1 个数据页 = 1.0 CPU 成本 · 数据运算 验证条件 / 排序 / 分组 每检测 1 行 = 0.2 1.0 与 0.2 是 MySQL 自定义的成本约定值，代表"一页 IO"和"一行检测"的相对代价 ① 拿统计信息：show table status like '表名' rows = 记录数（InnoDB 下是估计值）· data_length = 聚簇索引字节数 ② 算数据页数量：data_length ÷ 1024 ÷ 16KB 16KB 是 InnoDB 默认页大小 · 页数即全表扫描的 IO 次数基数 ③ 总成本 = 页数 × 1.0 + rows × 0.2 + 微调值 每个数据页都要 IO · 每行都要 CPU 检测是否符合 WHERE 条件 例：100 页 + 20000 行 100 × 1.0 + 20000 × 0.2 = 100 + 4000 ≈ 4100 ① 查询条件的范围区间数 → 二级索引 IO 成本 等值 = 1 个区间 · IN 两个区间 = 2 → 一般 n × 1.0，个位数级别 ② 估算二级索引会查出多少行（n） 依据索引统计信息粗略估算 · CPU 成本 = n × 0.2 + 微调值 ③ 回表 IO 成本：1 行 ≈ 1 个聚簇索引页 n 条数据 ≈ n × 1.0 · 回表通常是索引访问成本的大头 ④ 回表后的完整数据再过滤：n × 0.2 对拿回的完整行判断其余不在索引里的 WHERE 条件 例：n = 100 → 1 + 20 + 100 + 20 = 141 对比全表扫描的 4100 —— 索引往往便宜一个数量级以上 主键查询 = 只查聚簇索引 · 把同样流程套在聚簇索引上即可 驱动表：按单表方法选最佳访问方式 对 WHERE 条件各索引 + 全表扫描逐一估算 → 取最低成本 被驱动表：按连接条件 + 筛选条件估算 连接列 / 筛选列各索引 + 全表扫描估算 → 取最低成本 成本估算并不精准 —— 执行前精准算出每个方案的成本不现实，简单粗暴地估算再比较，是工程上的必然取舍 图例 成本计算步骤 算例 / 结论 流程起点 局限说明

成本公式速查

  • • IO 成本：1 个数据页 = 1.0（MySQL 约定值）
  • • CPU 成本：检测 1 行 = 0.2（MySQL 约定值）
  • • 全表扫描 = 页数×1.0 + rows×0.2 + 微调值
  • • 索引访问 = 区间数×1.0 + n×0.2 + 回表 n×1.0 + n×0.2
  • • 页数 = data_length ÷ 1024 ÷ 16KB

估算的天生局限

  • • rows 是 InnoDB 的估计值，不是精确值
  • • 索引命中行数 n 靠统计信息粗略估算
  • • 回表按"1 行 = 1 页"粗暴折算
  • • 执行前精准算出所有方案成本不现实
  • • 结论：估算不准也要比 —— 相对大小就够选型

多表关联的选型逻辑

  • • 思路与单表完全一致：逐表算成本、取最低
  • • 驱动表：按 WHERE 筛选条件的各方案估算
  • • 被驱动表：按 ON 连接列 + 筛选列的各方案估算
  • • 多个索引都可用（possible_keys）→ 每个都算一遍
  • • 这解释了 EXPLAIN 里 key 为什么"弃索引选全表"：索引区分度太低时，索引成本 ≈ 全表成本

③ 成本优化 · MySQL 执行计划知识图谱 · 依据第 94 ~ 96 讲整理