数据库

MySQL 基于成本的优化器 (CBO) 深度解析

核心概念：成本 (Cost) 的衡量标准

MySQL 查询优化器的核心目标是在众多可能的执行计划中，选择成本最低（最经济高效）的那个。这就是所谓的基于成本的优化 (Cost-Based Optimization, CBO)。

成本是一个抽象概念，用于量化执行查询计划所需的资源消耗。它综合考虑了多种因素，而不仅仅是执行时间。

I/O 成本 (I/O Cost)

磁盘 I/O 是数据库操作的主要性能瓶颈。I/O 成本主要衡量读取数据页 (Page) 的数量。

  • 随机 I/O vs. 顺序 I/O: 随机 I/O 成本远高于顺序 I/O。CBO 会给随机 I/O 更高的权重。

  • Buffer Pool 的影响: MySQL 的 Buffer Pool 缓存热点数据页。如果数据页已在 Buffer Pool 中，逻辑 I/O 成本会大大降低。

  • 全表扫描 vs. 索引扫描:

    • 全表扫描 (Full Table Scan): 读取所有数据页，I/O 成本高，但通常是顺序 I/O。

    • 索引扫描 (Index Scan): 读取索引页和少量数据页 (回表)，I/O 成本较低，但可能涉及随机 I/O。

CPU 成本 (CPU Cost)

CPU 成本衡量执行查询计划所需的 CPU 资源消耗, 默认0.2/行，例如：

  • 记录比较 (WHERE 子句条件判断、连接条件判断)

  • 数据排序 (ORDER BY、GROUP BY)

  • 函数调用 (内置函数、用户自定义函数)

  • 数据类型转换

  • 正则表达式匹配

其他成本因素

  • 网络成本 (Network Cost): 分布式数据库系统中，跨节点数据传输的成本。

  • 内存成本 (Memory Cost): 排序、Join Buffer、临时表等操作的内存使用。

  • 临时表创建成本: 创建和删除临时表的开销。

  • 锁 (Lock) 成本 (间接影响): 高效的执行计划可以减少锁持有时间，降低锁竞争。

CBO 如何进行优化决策

生成候选执行计划

对于一个 SQL 查询，MySQL 优化器会生成多个可能的执行计划，例如：

  • 不同的访问方法选择 (全表扫描 vs. 各种索引扫描)

  • 不同的连接类型选择 (INNER JOIN, LEFT JOIN, HASH JOIN, Nested Loop Join)

  • 不同的连接顺序 (Join Order)

  • 是否使用索引合并 (Index Merge)

估算每个执行计划的成本

这是 CBO 的核心步骤。优化器根据统计信息 (Statistics) 和成本模型 (Cost Model)，估算每个计划的 I/O 成本、CPU 成本等。

  • 统计信息 (Statistics) 的重要性: 统计信息是 CBO 成本估算的基础，描述了表和索引的属性：

    • 表总行数 (TABLES.TABLE_ROWS)

    • 索引的 Cardinality (基数) (STATISTICS.CARDINALITY)

    • 数据分布 (例如，直方图)

    • 数据页数量、索引页数量、平均行长度等

  • 基数估算（Cardinality Estimation）

  • 通过SHOW INDEX看到的Cardinality值

  • 采样统计：innodb_stats_persistent_sample_pages（默认20）

  • 计算公式：总行数 / 不同键值数量

  • 成本模型 (Cost Model) 的计算: 成本模型是一系列公式和规则，将统计信息转换为成本值。

    • 数据页读取数量

    • 索引的使用效率

    • 算法复杂度

    • 硬件环境参数 (部分)

选择成本最低的执行计划

CBO 会比较所有候选执行计划的成本估算值，选择总成本最低的执行计划。

连接顺序优化

  • 穷举算法：4表连接会尝试4!种顺序

  • 贪心算法：当表数量>optimizer_search_depth(默认62)时启用

单表查询的成本

  1. 找出所有可能使用的索引： 分析查询语句中的搜索条件, 确定可能用到的索引。

  2. 计算全表扫描的代价： 通过 SHOW TABLE STATUS 查看表的统计信息。

  3. 计算使用不同索引执行查询的代价：

    1. 计算范围区间的数量和需要回表的记录数。

    2. 计算 I/O 成本和 CPU 成本。

  4. 是否有可能使用索引合并： 根据条件判断。

  5. 对比各种执行方案的代价, 找出成本最低的那一个。

连接查询的成本

  • 两表连接的成本： 连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本。

  • 多表连接的成本： 需要考虑多种连接顺序的成本。MySQL 使用一些策略来减少计算时间。

调节成本常数

成本常数存储在 mysql 数据库的 server_cost 和 engine_cost 表中。

  • server_cost 表： 存储 server 层操作的成本常数。

  • engine_cost 表： 存储存储引擎层操作的成本常数。

可以通过更新表中的记录来修改成本常数，然后使用 FLUSH OPTIMIZER_COSTS; 语句重新加载。

统计信息 (Statistics) 的更新与维护

  • ANALYZE TABLE** 命令:** 手动更新表的统计信息。

    • 表数据大量修改后

    • 表结构变化后

    • 定期维护

  • InnoDB 自动统计信息收集: 默认情况下，InnoDB 会自动异步更新统计信息。

  • 统计信息采样 (Sampling): MySQL 通常会对数据进行采样来估算统计信息。

CBO 优化指导与实践建议

  1. 保持统计信息的准确性: 定期执行 ANALYZE TABLE。

  2. 创建合适的索引: 根据查询的 WHERE 子句、连接条件、排序和分组字段创建索引。

  3. 优化 SQL 查询:

    1. 避免在索引列上使用函数或表达式。

    2. 尽量使用覆盖索引。

    3. 优化连接条件。

    4. 避免不必要的 ORDER BY 和 GROUP BY。

  4. 使用 EXPLAIN** 分析执行计划:** 查看 MySQL 选择的执行计划和 CBO 估算的成本。

  5. 索引提示技巧

SELECT /*+ INDEX(orders idx_status) */ *
FROM orders FORCE INDEX(idx_create_time)
WHERE create_time > '2025-01-01'
AND status = 1;

  1. 关注 optimizer_trace** (MySQL 5.6+):** 了解 CBO 的优化决策过程。

  2. 合理配置优化器相关参数 (谨慎使用): 例如 optimizer_switch、eq_range_optimization_depth、join_buffer_size。

  3. 硬件升级 (最后的手段): 优先考虑软件层面的优化。

高级优化技巧

MRR优化（Multi-Range Read）

  • 工作流程：

  1. 收集索引键值到缓冲区

  2. 按主键排序后批量回表

  • 开启参数：
SET optimizer_switch='mrr=on,mrr_cost_based=off';

ICP优化（Index Condition Pushdown）

  • 将WHERE条件过滤下推到存储引擎层

  • 适用场景：组合索引的非前缀列过滤

ALTER TABLE orders ADD INDEX idx_city_status (city, status);
-- WHERE status=1可下推存储引擎处理

批量键值访问（BKA）

  • 结合MRR和BNL算法优化连接

  • 开启方式：

SET optimizer_switch='batched_key_access=on';

配置参数调优

  • 成本模型权重调整
SET engine_cost.iotable_scan_cost = 2.0;  -- 提高全表扫描成本
SET engine_cost.ioseek_cost = 1.5;        -- 增加索引查找成本
  • 内存相关参数
SET join_buffer_size = 4*1024*1024;      -- 连接缓冲区
SET sort_buffer_size = 2*1024*1024;      -- 排序缓冲区

电商网站数据存储应用示例 (CBO 的应用场景)

  • 商品搜索: CBO 选择最佳执行计划，保证搜索响应速度。

  • 订单查询: CBO 选择最优的连接顺序和连接算法。

  • 报表统计: CBO 选择合适的执行计划，降低资源消耗。

  • 个性化推荐: CBO 在复杂查询场景下选择高效执行计划。

总结

MySQL 成本优化器 (CBO) 是数据库性能优化的核心。理解 CBO 的工作原理，并结合以下几点，可以充分发挥 CBO 的优化能力：

  • 准确的统计信息

  • 合理的索引设计

  • 高效的 SQL 查询语句

附：优化检查清单

  • 定期执行ANALYZE TABLE更新统计信息

  • 监控慢查询日志

  • 检查索引使用率：

SELECT * FROM sys.schema_unused_indexes;
  • 验证执行计划稳定性：
EXPLAIN FORMAT=JSON 
SELECT /*+ MAX_EXECUTION_TIME(1000) */ ...

extend

关于 MySQL CBO 成本优化器的成本计算频率

  1. CBO 的执行机制MySQL 的 CBO（基于成本的优化器）不会为每条 SQL 都重新计算所有可能的执行计划成本。优化器会通过以下机制减少重复计算：

    1. 执行计划缓存：对于参数化查询（例如 WHERE id=?），MySQL 会缓存执行计划。当后续查询结构相同时（仅参数不同），直接复用缓存的计划，避免重复计算成本。

    2. 统计信息缓存：CBO 依赖表的统计信息（如索引基数、数据分布）。这些统计信息通过 ANALYZE TABLE 定期更新，而非实时计算。即使表数据变化，统计信息短期内仍可能被复用。

    3. 启发式规则：对于简单查询（如主键查询），优化器会直接选择固定执行计划，无需复杂成本计算。

  2. 高并发或类似 SQL 的影响

    1. 如果大量 SQL 结构相同（仅参数不同），优化器会利用缓存，成本计算频率大幅降低。

    2. 若 SQL 结构差异较大（例如不同 WHERE 条件组合），优化器会为每条 SQL 单独计算成本。此时可通过以下方式优化：

      • 使用绑定变量（Prepared Statements）提升执行计划复用率。

      • 定期更新统计信息（避免因数据分布变化导致成本估算偏差）。

关于 MRR 优化（Multi-Range Read）

  1. MRR 的默认状态与开启方式

    1. 默认状态：在 MySQL 5.6+ 中，MRR 功能默认开启（optimizer_switch='mrr=on'），但优化器默认基于成本决定是否使用（mrr_cost_based=on）。

    2. 强制启用 MRR：若希望绕过成本估算直接使用 MRR，需手动设置：

      SET optimizer_switch='mrr=on,mrr_cost_based=off';

  2. 适用场景MRR 主要优化以下场景的 IO 性能：

    1. 范围查询：例如 WHERE age BETWEEN 10 AND 20，通过二级索引获取主键后，按主键顺序回表，减少磁盘随机访问。

    2. 多键值查询：例如 WHERE id IN (1, 5, 3, ...)，对主键排序后批量回表，将随机 IO 转为顺序 IO。

    3. JOIN 操作：当关联查询需要多次回表时，MRR 可减少临时表的随机访问开销。

  3. 性能对比

    1. 优势场景：当二级索引筛选出的主键分散在磁盘不同位置时，MRR 可减少磁头移动，提升 IO 效率（尤其适用于机械硬盘）。

    2. 劣势场景：若数据已缓存在内存（InnoDB Buffer Pool），或 SSD 随机读性能较高，MRR 的收益可能不明显。

总结

  • CBO 成本计算频率：通过执行计划缓存和统计信息复用机制，优化器会尽量减少重复计算，通常不会成为性能瓶颈。

  • MRR 使用建议：对于机械硬盘上的范围查询或多键值回表操作，建议强制启用 MRR；SSD 或内存命中率高时，可依赖成本自动选择。