MySQL 的查询优化器如何选择执行计划

MySQL 的查询优化器通过分析 SQL 语句的多种执行路径，结合表的统计信息、索引的可用性、查询条件等因素，评估每种路径的成本，选择代价最低的执行计划。优化器会考虑使用索引、表连接顺序、排序和分组等操作，以提高查询效率。最终，优化器生成的执行计划将被传递给执行器执行。

一、优化器工作流程

1. 解析与重写阶段

   • SQL解析：将SQL语句解析为语法树（Parse Tree），验证语法和语义正确性。

   • 查询重写：简化表达式（如常量计算）、展开视图、转换子查询为连接（如IN转JOIN）、消除外连接等。

2. 生成候选执行计划

   • 访问路径选择：为每个表选择数据访问方式（全表扫描、索引扫描、范围扫描等）。

   • 连接顺序与算法：

     • 连接顺序：多表连接时，优化器评估不同表的连接顺序（如(A JOIN B) JOIN C vs (B JOIN C) JOIN A），使用贪心算法减少计算量。

     • 连接算法：根据数据量和索引选择嵌套循环连接（Nested Loop）、哈希连接（Hash Join，MySQL 8.0+）或排序合并连接（Sort-Merge）。

3. 成本估算与计划选择

   • 成本模型：计算每个候选计划的成本（I/O + CPU + 内存开销）：

     • I/O成本：从磁盘/内存读取数据页的代价（io_block_read_cost vs memory_block_read_cost）。

     • CPU成本：处理数据（如比较、排序）的代价（row_evaluate_cost、key_compare_cost）。

   • 选择最低成本计划：比较所有候选计划的总成本，选择最优方案。

二、影响执行计划选择的因素

1. 统计信息

   • 包括表行数、索引基数（不同值的数量）、数据分布直方图（MySQL 8.0+）。

   • 重要性：统计信息不准确可能导致优化器误判（如低估行数导致全表扫描）。

   • 维护命令：ANALYZE TABLE更新统计信息。

2. 索引可用性与选择性

   • 索引选择：优化器优先选择能减少扫描行数的索引：

     • 覆盖索引（索引包含查询所有字段）直接避免回表，成本最低。

     • 最左前缀原则：复合索引需匹配最左列才能生效（如索引(a,b,c)，条件b=1无法利用）。

   • 选择性：高选择性（如唯一索引）的索引成本更低。

3. 数据分布与表大小

   • 数据倾斜时（如某值占比90%），索引可能失效（优化器认为全表扫描更快）。

   • 小表优先连接：优化器倾向先过滤数据量小的表，减少后续连接的行数。

4. 优化器配置

   • 开关参数：通过optimizer_switch控制优化策略（如index_merge=on启用索引合并）。

   • 成本常数表：

     • mysql.server_cost（通用操作成本）

     • mysql.engine_cost（存储引擎操作成本），可动态调整（如SSD环境下降低I/O成本）。

三、人工干预执行计划的方法

1. 索引提示（Hints）

   • USE INDEX：建议优化器使用特定索引。

   • FORCE INDEX：强制使用索引（可能因成本过高失效）。

   • 适用场景：优化器因统计信息偏差选错索引时。

2. 查询重写

   • 拆分复杂查询、避免SELECT *、用JOIN替代子查询。

   • 示例：将 OR 条件改写为UNION，避免全表扫描。

3. 更新统计信息与配置

   • 定期执行 ANALYZE TABLE 确保统计准确。

   • 调整join_buffer_size、sort_buffer_size等参数优化连接和排序操作。

四、诊断工具：分析执行计划

1. EXPLAIN命令

   • 查看执行计划关键列：

     • type：访问类型（ref > range > index > ALL）。

     • key：实际使用的索引。

     • rows：预估扫描行数。

     • Extra：额外信息（如 Using filesort需优化）。

2. OPTIMIZER_TRACE（深度诊断）

   • 启用：SET optimizer_trace="enabled=on";

   • 查看：SELECT * FROM information_schema.OPTIMIZER_TRACE;

   • 输出内容：展示所有候选计划的成本计算细节，定位优化器决策依据。

总结与最佳实践

• 默认行为：MySQL使用CBO模型，优先选择预估成本最低的计划。

• 优化核心：

  1. 统计信息准确 → 定期ANALYZE TABLE。

  2. 索引设计合理 → 覆盖高频查询，避免冗余索引。

  3. 避免成本陷阱 → 大表避免全表扫描，优先索引过滤。

• 调试流程：

  EXPLAIN SELECT ...;  -- 初步分析
  SET optimizer_trace="enabled=on";
  SELECT ...;  -- 执行查询
  SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 查看决策细节