SQL 优化器与执行计划：Explain 深度解读

敖正炀
4 阅读47分钟

概述

衔接前文段落

前文《行锁、间隙锁与 Next-Key Lock：加锁规则深度解析与死锁排查实战》揭示了索引对锁行为的根本性影响——缺少索引时，一条 UPDATE 可能锁住全表。但索引的作用远不止于此：它更是优化器做出高效执行计划的基石。优化器每天在后台做出成千上万个决策——选哪个索引、以什么顺序 Join、用 Nested-Loop 还是 Hash Join——这些决策直接决定了 SQL 的性能。本文将从优化器内核出发，深度拆解 EXPLAIN 的输出，帮助你验证优化器的选择是否最优，并在必要时进行干预。

总结性引言

你是否曾遇到过这样的场景：一条 SQL 在测试环境跑得飞快，上生产却变成了慢查询；或者 EXPLAIN 显示 type=ALL，但明明索引存在？问题的根源往往不在 SQL 本身，而在于优化器的决策。MySQL 优化器通过代价估算模型来评估每个候选执行计划的成本，但这个模型依赖于统计信息的准确性、索引的区分度、以及 Join 顺序的选择。本文将从 SQL 执行链路到 EXPLAIN 每个字段的精确含义，从代价常数到 EXPLAIN ANALYZE 的真实世界执行，为你建立 SQL 优化的系统方法论。

核心要点

  • 优化器核心机制：代价估算模型、索引选择、Join 优化、子查询转换。
  • EXPLAIN 各字段深度解读：type/key_len/Extra 等字段的精确含义与实战分析。
  • EXPLAIN ANALYZE：估算 vs 实际，识别统计信息问题和错误索引。
  • optimizer_trace：跟踪优化器的完整决策过程。

文章组织架构图

flowchart TD
    1[1. SQL 执行全链路与优化器内核] --> 2[2. EXPLAIN 各字段深度解读]
    2 --> 3[3. 访问类型 type 的完整解析]
    3 --> 4[4. Extra 字段的算法内涵]
    4 --> 5[5. EXPLAIN ANALYZE 实战]
    5 --> 6[6. optimizer_trace 辅助分析]
    6 --> 7[7. 面试高频专题]

架构图说明

  • 总览说明：全文 7 个模块从优化器内核出发，逐一深入 EXPLAIN 字段、访问类型和 Extra 的算法，再补充 EXPLAIN ANALYZE 和 optimizer_trace 的实战，最后以面试题收尾。
  • 逐模块说明：模块 1 建立优化器决策的理论基础；模块 2-4 是全文核心，逐字段拆解 EXPLAIN 输出；模块 5-6 提供生产级诊断工具；模块 7 面试巩固。
  • 关键结论：SQL 优化不是玄学，而是理解优化器决策后对执行计划的验证与纠正。EXPLAIN 是你的显微镜，EXPLAIN ANALYZE 是实测数据，optimizer_trace 则是优化器的大脑日志。

1. SQL 执行全链路与优化器内核

1.1 解析器：词法/语法分析 → 语法树

一条 SQL 进入 MySQL 后，首先由解析器进行词法分析和语法分析，生成一棵抽象语法树（AST）。词法分析将 SQL 文本切分为 Token（关键字、标识符、操作符等），语法分析则根据 MySQL 的语法规则（由 sql/sql_yacc.yy 定义，使用 Bison 生成）将这些 Token 组织成树状结构。解析器不检查表或索引是否存在，只保证 SQL 语法合法性。语法树中的每个节点代表一个查询元素，例如 SELECT 列表中的列引用、FROM 子句的表引用、WHERE 条件的表达式树。如果语法错误，立即报错；否则语法树将传递给优化器。

1.2 优化器：查询重写与执行计划生成

优化器位于 sql/sql_optimizer.cc，是 MySQL 查询处理的灵魂。它的目标是在可接受的时间内找到一个成本最低的执行计划。整个流程分为逻辑优化和物理优化两个阶段，逻辑优化主要通过查询重写等价变换简化查询，物理优化则生成具体的访问路径和 Join 顺序。

1.2.1 查询重写（逻辑优化）

优化器首先对语法树进行一系列的查询重写（Rule-based Rewriting），这一阶段不涉及代价估算，仅根据规则进行等价转换：

  • IN → EXISTS 改写：WHERE a IN (SELECT b FROM t) 会被改写为 EXISTS (SELECT 1 FROM t WHERE b = a) 的形式，这有利于后续的半连接（semijoin）优化。
  • 外连接转内连接：对于 LEFT JOIN，如果 WHERE 子句中包含对右表列的非 NULL 约束（如 WHERE t2.col IS NOT NULL 或 WHERE t2.col > 0），优化器可以安全地将外连接转为内连接，从而扩大驱动表的选择范围。
  • DISTINCT 消除：如果查询结果已经被唯一索引或 GROUP BY 保证唯一，DISTINCT 关键字可以直接移除。
  • 常数折叠与谓词推导：WHERE a=1 AND b=a+1 会被简化为 WHERE a=1 AND b=2；WHERE a=b AND b=5 推导出 a=5。
  • 视图合并：对于不是 ALGORITHM=TEMPTABLE 的视图，优化器会将其定义合并到主查询中，从而能整体优化。
  • 子查询优化策略：
    • semijoin（半连接）：将 IN 子查询转换为类似 Join 的操作，但同时保证结果不重复。MySQL 实现了多种半连接策略：FirstMatch（驱动表一行在被驱动表中找到第一匹配即返回，不再继续查找）、LooseScan（使用索引跳过重复组）、MaterializeLookup（物化子查询并建立索引，外层表逐行查找）、Duplicate Weedout（使用临时表去除重复行）。
    • materialization（物化）：将子查询结果存入临时表并添加索引，然后外层查询与临时表进行 Join。
    • FirstMatch：与 semijoin 的 FirstMatch 策略类似，但针对不能转换为 semijoin 的场景（如 NOT IN）。

1.2.2 物理优化：索引选择与 Join 规划

查询重写之后，优化器开始生成具体的物理执行计划，这依赖统计信息和代价估算模型。

统计信息：InnoDB 持久化统计信息默认开启（innodb_stats_persistent=ON），存储在 mysql.innodb_table_stats 和 mysql.innodb_index_stats 中。索引的基数（Cardinality）通过采样页估算，采样页数由 innodb_stats_persistent_sample_pages（默认 20）控制。ANALYZE TABLE t; 会重新采样并更新统计信息。准确的基数对于区分度（selectivity）计算至关重要，直接决定索引的吸引力。

代价估算模型：优化器通过成本公式计算每种访问路径和 Join 顺序的总代价。成本单位是一个抽象的数字，没有直接的时间单位，但互相可比。总代价主要由两部分组成：

总代价 = CPU代价 + IO代价

CPU 代价是处理行数的函数（如行求值、键比较），IO 代价是读取页面数的函数。MySQL 8.0 的成本模型由两个系统表 mysql.server_cost 和 mysql.engine_cost 中的常数参数化。

查询并解读主要代价常数：

-- 查看 Server 层代价常数
SELECT cost_name, cost_value, default_value, comment
FROM mysql.server_cost;
-- 查看引擎层代价常数
SELECT cost_name, cost_value, default_value, comment
FROM mysql.engine_cost;

关键常数解释（默认值基于 MySQL 8.0.35）：

常数名默认值含义影响方向
row_evaluate_cost0.1处理一行的 CPU 代价增大则更倾向于索引扫描，减少全表扫描
key_compare_cost0.05比较一个键值的代价增大则降低索引 join 的吸引力
memory_temptable_create_cost1.0创建内存临时表的固定代价增大则更少使用临时表
disk_temptable_create_cost20.0创建磁盘临时表的固定代价增大则更少使用磁盘临时表
memory_temptable_row_cost0.1内存临时表写入一行的代价影响 GROUP BY 或 ORDER BY 策略
disk_temptable_row_cost1.0磁盘临时表写入一行的代价
io_block_read_cost1.0从磁盘随机读取一个页的代价增大则更倾向顺序扫描
memory_block_read_cost0.25从内存缓冲池读取一个页的代价影响索引扫描与全表扫描的比较

优化器通过访问路径的估算行数（rows）和所需读取的页数来计算成本。例如，全表扫描的成本粗略计算为：

cost = pages * io_block_read_cost + rows * row_evaluate_cost

索引扫描（如 range 扫描）的成本则会加上 key_compare_cost 乘以键值比较次数等。这些计算细节可通过 optimizer_trace 查看。

Join 顺序与算法选择：对于多表连接，MySQL 主要使用左深树（left-deep join tree），优化器通过穷举搜索找到最佳的驱动表顺序。搜索空间受 optimizer_search_depth 参数限制（默认 62，即几乎不限制），当表数量很大时，可通过减小此值避免优化时间过长。optimizer_prune_level=1（默认）会基于已找到的最优成本剪枝后续搜索，大幅减少探索路径数。

Join 算法有三种，从老到新分别为：

  • Nested-Loop Join (NLJ)：驱动表每行对驱动表索引查找，要求被驱动表 Join 列有索引。
  • Block Nested-Loop Join (BNL)：当被驱动表无可用索引时，将驱动表行缓存到 Join Buffer（由 join_buffer_size 控制）中，然后批量扫描被驱动表进行匹配。MySQL 8.0.20 起，BNL 已被移除，被 Hash Join 完全取代。
  • Hash Join (HJ)（MySQL 8.0.18+）：将驱动表（构建端）的行放入内存哈希表，然后扫描被驱动表（探测端），每行在哈希表中查找匹配。如果内存不足以容纳构建端，会溢出到磁盘分片处理。Hash Join 极大地改善了无索引 Join 的性能。

1.3 执行器：调用存储引擎接口

优化器输出的是一个迭代器树（Iterator Tree）。执行器以火山模型（Volcano Model）驱动迭代器，通过 Init()、Read() 接口获取数据。对于 InnoDB 表，执行器调用 handler 接口，如 index_read()、rnd_next() 等，最终触发 B+Tree 的索引查找或全表扫描。在扫描过程中，根据事务隔离级别和查询类型，InnoDB 会对行记录加锁（参见第 4 篇）。执行器还负责完成 WHERE 过滤、排序、分组等操作，这些操作可能借助临时表或内存排序缓冲区。

1.4 SQL 执行全链路流程图

flowchart TD
    A[客户端发送 SQL] --> B[解析器 词法/语法分析]
    B --> C[语法树 AST]
    C --> D[优化器]
    D --> E{查询重写}
    E --> F{索引选择}
    F --> G{Join 顺序与算法}
    G --> H{子查询优化}
    H --> I[生成执行计划]
    I --> J[执行器 迭代器模型]
    J --> K[调用存储引擎接口]
    K --> L[InnoDB B+Tree/锁]
    L --> M[返回结果]

图 1-1 SQL 执行全链路流程图

  • 图表意图：展示一条 SQL 语句在 MySQL 内部从解析到执行的完整路径，突出优化器的核心步骤。
  • 关键节点说明：解析器生成语法树后，优化器进行查询重写、索引选择、Join 策略和子查询优化，最终输出物理执行计划交由执行器与存储引擎交互。
  • 与索引/锁的关系：执行器调用存储引擎时会涉及 B+Tree 索引遍历和行锁加锁（前文第 4 篇），因此优化器的索引选择直接影响加锁范围。
  • 实践启示：当发现执行计划异常时，可从优化器的四个核心步骤入手排查，尤其是统计信息导致的索引选择错误。

1.5 Join 算法对比图

flowchart TD
    subgraph NLJ[1. Nested-Loop Join]
        A1[驱动表 逐行] --> B1[被驱动表 按索引查找]
        B1 --> C1{匹配?}
    end
    subgraph BNL[2. Block Nested-Loop Join 已废弃]
        A2[驱动表行读入 join buffer] --> B2[批量扫描被驱动表]
        B2 --> C2{逐行匹配}
    end
    subgraph HJ[3. Hash Join]
        A3[构建端 读入内存建哈希表] --> B3[探测端 逐行查哈希表]
        B3 --> C3{匹配返回}
    end

图 1-2 Join 算法对比图

  • 图表意图：对比三种连接算法的执行流程，直观展示从简单嵌套循环到基于哈希的优化。
  • NLJ 特点：依赖被驱动表的索引，若索引缺失退化为全表扫描，性能极差；锁行为逐行加锁，详见第 4 篇。
  • BNL 与 Hash Join：BNL（8.0.20 后默认禁用）使用缓冲区减少全表扫描次数，但仍较慢；Hash Join 将驱动表构建为内存哈希表，探测端 O(1) 查找，大幅提升无索引连接性能。
  • 查看方式：EXPLAIN 的 Extra 中 Using join buffer (hash join) 表示使用了 Hash Join。

2. EXPLAIN 各字段深度解读

EXPLAIN 语句用于查看优化器生成的执行计划。其输出格式可以是传统表格、JSON 或 TREE 格式。本章以传统输出为主，并配合 FORMAT=TREE 展示树形结构。

2.1 id：查询执行顺序

id 是 SELECT 标识符，数字越大，优先级越高，先执行；id 相同则从上到下执行。id 为 NULL 表示该行是 UNION 操作后的最终结果合并（UNION RESULT）。示例：

EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;

可能输出：

idselect_typetable...
1PRIMARYt1...
2UNIONt2...
NULLUNION RESULT<union1,2>...

该输出表明：先执行 t2（id=2），再执行 t1（id=1），最后合并结果（id=NULL）。在子查询中，id 可能不同，帮助识别复杂查询的执行顺序。

2.2 select_type：查询类型

select_type 指明查询的类型，直接影响优化器对子查询的处理策略：

  • SIMPLE：简单查询，不包含子查询或 UNION。
  • PRIMARY：最外层查询。
  • SUBQUERY：非相关子查询，子查询不引用外层列，可独立执行一次。
  • DEPENDENT SUBQUERY：相关子查询，引用外层列，对外层每一行都要执行一次子查询，性能极差。这是优化的重点目标。
  • DERIVED：派生表（FROM 子句中的子查询），MySQL 会将其结果物化到一个临时表，然后再与外层查询交互。在 MySQL 8.0 中，如果可能，优化器会尝试将派生表合并到外层（Derived Merge）或物化并加索引。
  • UNION：UNION 中的第二个或后续 SELECT。
  • UNION RESULT：UNION 临时表的结果。
  • DEPENDENT UNION：UNION 中的相关子查询。
  • MATERIALIZED：子查询被物化为临时表（MySQL 8.0 新增类型，旧版显示为 SUBQUERY）。

DEPENDENT SUBQUERY 是性能杀手，因为它可能导致外层每行都执行一次完整的子查询，复杂度 O(N*M)。在 MySQL 8.0 中，很多相关子查询可通过 semijoin 自动转化为高效的 Join。

2.3 table 与 partitions

table 显示访问的表名或别名。若为派生表，则显示 <derivedN>；UNION 结果则显示 <unionM,N>。partitions 显示查询涉及的分区，仅分区表有效。如果分区过滤良好，partitions 列会显示确切的分区名；如果未过滤，则显示全部分区。

2.4 possible_keys 与 key

  • possible_keys：优化器认为可能用到的索引列表。如果为 NULL，表示没有可用的索引，需要检查 WHERE/JOIN 条件是否引用了合适的列。
  • key：优化器实际选择的索引。若 possible_keys 有值但 key 为 NULL，说明优化器认为全表扫描代价更低。这通常是统计信息不准导致的——Cardinality 估算错误使得优化器低估了索引的区分度，或高估了全表扫描的效率。
  • 索引选错常见原因：
    1. 统计信息过期：ANALYZE TABLE 可更新。
    2. 单列索引与复合索引的抉择：优化器可能选择更窄的索引以减少 IO，即使另一个索引过滤性更好。
    3. FORCE INDEX / USE INDEX 的干预：这些提示会覆盖优化器选择，但需谨慎使用，避免绑定过时计划。
    4. 隐式类型转换：WHERE varchar_col = 123 可能使索引失效，possible_keys 仍会列出，但 key 为 NULL。

2.5 key_len：精确解读复合索引使用

key_len 是索引字段使用的字节数总和，可精确反映复合索引“最左前缀”原则的实际使用情况。计算方法如下（InnoDB 存储引擎）：

  • 整数类型：TINYINT=1, SMALLINT=2, MEDIUMINT=3, INT=4, BIGINT=8。注意无符号和有符号长度相同。
  • 浮点数/双精度：FLOAT=4, DOUBLE=8。
  • 日期时间类型：DATE=3, TIME=3+小数秒, DATETIME=5+小数秒, TIMESTAMP=4+小数秒。如果未指定小数秒，DATETIME 占 5 字节，TIMESTAMP 占 4 字节。
  • 字符串类型：
    • CHAR(n)：n × 字符集最大字节数（utf8mb4 下每个字符 4 字节）。
    • VARCHAR(n)：n × 字符集最大字节数 + 2（2 字节存储实际长度）。例如 VARCHAR(10) 在 utf8mb4 下最多 10*4+2=42 字节。
  • NULL 标记：索引列允许 NULL 额外占 1 字节。
  • 复合索引累计：key_len 是所有能用上的索引列的字节数之和。若查询条件中某一列使用了范围条件（>, <, LIKE 'prefix%' 等），则从该列开始的后续索引列无法继续用于等值匹配，key_len 只会累加到该列之前。

实战案例：索引 idx_a_b_c (a INT, b VARCHAR(20) NOT NULL, c CHAR(4))，表字符集 utf8mb4。

  • 查询 WHERE a=1 AND b='x'：使用 a 和 b，key_len = 4 + (20*4+2) = 86。
  • 查询 WHERE a=1 AND b > 'x'：由于 b 是范围，只能用 a，key_len = 4。
  • 查询 WHERE a=1 AND b='x' AND c='y'：三列全等值，key_len = 4 + 86 + (4*4) = 106。

通过 key_len 可以诊断出索引使用的具体情况，进而调整查询条件顺序或索引设计。

2.6 ref：索引匹配的列或常量

ref 表示索引查找时与哪些列或常量进行匹配。常见值：

  • const：与常量比较。
  • db.table.column：与另一个表的列比较。
  • func：与函数结果比较，例如 UPPER(col)。这种情况应尽量避免。
  • NULL：不使用索引，例如 type=ALL 时。

在 type=ref 或 eq_ref 时，ref 会明确指出匹配来源，这有助于理解 Join 的驱动关系。

2.7 rows 与 filtered：估算扫描行数与过滤效果

  • rows：优化器估算的需要扫描的行数（对于 InnoDB 是统计信息估算的行数）。这个数字是近似值，受采样影响，可能与实际偏差较大。
  • filtered：经过表自身 WHERE 条件过滤后剩余行的百分比，最大值 100。rows × filtered% 近似等于该表输出到下一步的行数。例如 rows=1000, filtered=10.00，代表预计有 100 行参与后续的连接或输出。

影响因素：

  • 如果 WHERE 条件中有一部分列没有索引，优化器通过统计信息估算这些条件的过滤性，filtered 会反映出来。
  • 当 filtered 很低但 rows 很大时，说明索引无法有效过滤，需要扫描大量行但最终只保留一小部分，此时建立合适的索引可以显著降低 rows。

2.8 Extra：执行计划的信息宝库（摘要）

Extra 字段包含执行计划的额外信息，是定位性能问题的关键。

  • Using index：覆盖索引，查询所需的所有列都在索引中，不需要回表。性能最优。
  • Using index condition：索引条件下推（ICP），将部分 WHERE 条件下推到引擎层过滤，减少回表次数。常与 range 或 ref 同时出现。
  • Using where：Server 层过滤，说明有列未走索引，引擎返回的行在 Server 层进一步过滤。如果与 Using index 一起出现，表示覆盖索引中的列被用于过滤但未用于定位。
  • Using temporary：使用临时表，常见于 GROUP BY、DISTINCT、UNION 等操作。如果临时表写磁盘，性能问题严重。
  • Using filesort：无法利用索引排序，需要额外排序操作。排序可能在内存或磁盘完成。
  • Using join buffer：Join 使用了连接缓冲区（Block Nested-Loop 或 Hash Join）。在 MySQL 8.0.20+ 中通常指 Hash Join。
  • Start temporary / End temporary：半连接 Duplicate Weedout 策略的临时表标记。

2.9 图表：EXPLAIN 输出与索引结构对应关系图

flowchart TB
    subgraph SQL[查询]
        Q["WHERE a = 5 AND b > 10"]
    end
    subgraph BTree["B+Tree 索引 idx_a_b (a,b)"]
        N1((根节点)) --> L1["叶子: (5,8) -> (5,11) -> (5,20)"]
        L1 --> L2["(5,11) 起始扫描"]
    end
    subgraph EXPLAIN_OUT["EXPLAIN 输出"]
        E["type: range"] --> E1["key: idx_a_b"]
        E1 --> E2["key_len: 4  (仅 a 列)"]
        E2 --> E3["rows: 3"]
    end
    SQL --> BTree
    BTree --> EXPLAIN_OUT

图 2-1 EXPLAIN 输出与索引结构对应关系图

  • 图表意图：将查询条件在 B+Tree 上的扫描路径对应到 EXPLAIN 输出中的 type、key 和 key_len，建立直观映射。
  • 扫描路径说明：复合索引 (a,b) 上，条件 a=5 AND b>10 需先在 B+Tree 中定位 a=5 的第一个 b 值，然后沿叶子链向右扫描直到 b 不满足条件。因为 b 是范围条件，所以只能使用 a 列等值，因此 key_len 只有 4。
  • EXPLAIN 字段联动：type=range 表明索引范围扫描；key 指明使用索引；key_len 精确揭示了最左前缀的使用程度；rows 估算扫描行数。
  • 优化启示：若希望范围条件也走索引，可考虑将 b 放在更前面，但通常查询模式决定了索引列顺序。

3. 访问类型 type 的完整解析

优化器通过 type 字段表示每张表的访问方法，从优到劣依次为：

3.1 NULL

无需访问表，如 SELECT 1 或从索引即可计算结果的聚合查询（如 COUNT(*) 在有覆盖索引且无 WHERE 时可能显示 index，非 NULL）。

3.2 system / const

  • system：表只有一行数据，是 const 的特例，极少出现。
  • const：主键或唯一索引的等值查询，最多返回一行，查询开始时即可通过唯一索引直接定位。性能极佳，加锁也最精确。

3.3 eq_ref

Join 时，被驱动表使用主键或唯一索引进行等值匹配，对于驱动表的每一行，被驱动表最多返回一行。这是除了 const 外最好的 Join 类型。需要 Join 条件完全匹配唯一索引的所有列。

3.4 ref

非唯一索引的等值匹配，可能返回多行。出现在 Join 条件或普通索引等值查询中。例如 WHERE indexed_col = value。ref 的扫描行数取决于索引的区分度。

3.5 range

索引范围扫描，如 >、<、BETWEEN、IN 等。通常只扫描索引的一部分，比全索引扫描快。注意 IN 对于大量离散值可能退化为多次 ref 或范围扫描，type 仍然显示 range。

3.6 index

全索引扫描，扫描整个索引树。虽然比全表扫描（ALL）轻量（索引通常比表小），但仍是扫描全部索引条目，在大表上开销大。常见于以下场景：

  • 查询需要排序，利用索引顺序避免 filesort，但无过滤条件。
  • 覆盖索引全扫描（Extra 显示 Using index），此时仍然扫描全部索引条目。
  • 某些聚合查询（如 COUNT(*)）可能选择较窄的索引全扫描。

3.7 ALL

全表扫描，从聚簇索引中逐行读取，是大表查询性能最差的访问方式。通常意味着需要添加索引。

性能提升策略：通过添加合适的索引，可将 ALL → range 或 ref。例如，为 WHERE 子句中的列建立单列索引或复合索引。

3.8 类型与锁的关系

回顾前文（第 4 篇），访问类型直接影响锁行为：

  • type=ALL 且没有索引可利用时，在 REPEATABLE READ 隔离级别下，执行器会在扫描过程中对所有行加 Next-Key Lock，相当于锁表，极易引发锁冲突。
  • type=range 使用索引时，仅对索引扫描范围内的记录加锁，锁范围更小。
  • 因此，提升访问类型不仅能加速查询，还能显著缩小锁范围，减少死锁概率。

4. Extra 字段的算法内涵

4.1 Using index：覆盖索引

表示查询可以直接从索引树获取所需全部列，无需回表访问聚簇索引。这是索引优化的黄金标准。例如：

-- idx_b 仅包含列 b 和主键 id，查询列 a 不在索引中，无法覆盖
SELECT a FROM t WHERE b = 1;  -- Extra: NULL 或 Using where
-- 如果 idx_b 是 (b, a) 或 (b) 加上主键，且查询 a, b，则可能覆盖
SELECT b FROM t WHERE b = 1;  -- Extra: Using index

要达成覆盖索引，需将查询涉及的所有列都包含在索引中（可以是复合索引或包含主键的二级索引）。

4.2 Using index condition：索引条件下推（ICP）

当 WHERE 包含无法完全被索引定位的条件时（如部分列没有索引，但属于同一个索引中的列），存储引擎可以在索引层面进行条件过滤，减少回表次数。例如：

-- 索引 idx_a 包含列 a 和主键 id，查询条件是 a > 10 AND b = 5，b 不在索引中。
-- 传统方式：引擎通过 idx_a 找到所有 a>10 的记录，逐一回表读取 b 并判断。
-- ICP 方式：引擎在 idx_a 的索引条目上即可判断 a>10，但 b 不在索引，无法下推。
-- 实际的 ICP 需要条件中的列属于同一个索引。
-- 假设索引 idx_a_b 为 (a, b)，查询条件为 a > 10 AND b = 5。
-- 优化器可下推 b=5 到引擎，引擎在索引扫描 a>10 的同时检查 b=5，从而减少回表。
SELECT * FROM t WHERE a > 10 AND b = 5; -- 使用 idx_a_b，Extra 显示 Using index condition

ICP 由优化器根据代价模型自动选择，默认开启（optimizer_switch='index_condition_pushdown=on'）。

4.3 Using where：Server 层过滤

表示有行在存储引擎返回后，由 Server 层进一步过滤。通常出现在 WHERE 中引用了无索引列，或者索引条件不能完全覆盖 WHERE 子句的情况。如果同时出现 Using index 和 Using where，说明覆盖索引被用于过滤，但过滤条件可能不是等值或范围定位，而是对索引条目进行额外检查。

4.4 Using temporary 与 Using filesort

  • Using temporary：创建临时表来保存中间结果，通常出现在 GROUP BY、DISTINCT、UNION、某些 ORDER BY 与 GROUP BY 不同列等情况。临时表可能使用内存（MEMORY 引擎）或磁盘（InnoDB 磁盘临时表，MySQL 8.0 起默认配置）。磁盘临时表代价高昂。优化方式是通过索引消除临时表，例如为 GROUP BY 列建立索引，或将 DISTINCT 转化为 GROUP BY 并利用索引。
  • Using filesort：当 ORDER BY 无法利用索引有序性时，MySQL 需要额外的排序步骤。filesort 并不一定使用磁盘文件，如果排序数据量小于 sort_buffer_size，则在内存中通过快速排序完成；超出则使用磁盘归并排序。filesort 的成本可以通过建立符合排序顺序的索引来消除。

4.5 Using join buffer

表示连接使用了连接缓冲区，意味着被驱动表没有可用的索引。在 MySQL 8.0.20 之前，Using join buffer (Block Nested Loop) 代表 BNL；从 8.0.18 起引入 Hash Join，8.0.20 起 BNL 被完全移除，因此 Using join buffer (hash join) 表示 Hash Join。如果看到此标志，应考虑为被驱动表添加索引，以转化为 NLJ 从而提升性能。

4.6 Start temporary / End temporary

这两个标记成对出现，表示使用了 Duplicate Weedout 半连接策略。优化器将半连接（IN 子查询）转换为一种 Join，并创建一个临时表来去除重复行。例如：

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);

可能在 EXPLAIN 输出中看到 t1 表有 Start temporary，t2 表有 End temporary，表示创建临时表存放 t1 的行，与 t2 连接后去重。

5. EXPLAIN ANALYZE 实战

5.1 输出格式与解读

EXPLAIN ANALYZE（MySQL 8.0.18+）会真实执行 SQL 并收集运行统计，以树形结构输出每个迭代器的实际时间、行数和循环次数。一个典型输出片段：

-> Limit: 10 row(s)  (cost=102.49 rows=10) (actual time=2.345..2.346 rows=10 loops=1)
    -> Sort: t.c, limit input to 10 row(s) per chunk  (cost=102.49 rows=100) (actual time=2.344..2.344 rows=10 loops=1)
        -> Table scan on t  (cost=102.49 rows=100) (actual time=0.123..1.987 rows=1000 loops=1)

解读：

  • Limit：迭代器类型，表示 LIMIT 操作。
  • cost：优化器估算的总代价和预估行数。
  • actual time：第一个数字是返回第一行的平均时间（毫秒），第二个是所有行的总时间。
  • rows：该迭代器输出的实际行数。
  • loops：该节点被执行的次数，体现嵌套循环中的驱动关系。如果 loops 大于 1，actual time 是每次循环的平均时间。
  • 最内层的 Table scan 显示全表扫描，估算 rows=100，但实际扫描了 1000 行，且实际扫描总时间为 1.987ms，第一行返回很快，说明扫描大量行但未匹配时延迟体现在后面。

5.2 估算与实际差异过大

如果 EXPLAIN 的 rows 估算与 EXPLAIN ANALYZE 的 actual rows 差距悬殊（例如估算 100，实际 10 万），几乎可以肯定是统计信息不准确。优化器可能因此低估了扫描成本，选择了错误的索引或 Join 顺序。应立即执行 ANALYZE TABLE t; 更新统计信息。若问题持续，可考虑增大 innodb_stats_persistent_sample_pages（如设置为 200 或更高）以提升采样精度，然后重新分析。

5.3 索引选择错误的诊断与修正案例

-- 假设 orders 表有 idx_status 和 idx_create_time
EXPLAIN SELECT * FROM orders WHERE status=1 AND create_time > '2025-01-01';

-- EXPLAIN 显示 key: idx_create_time, rows: 1000000 (实际 status=1 只有 1000 行)
-- 使用 EXPLAIN ANALYZE 确认实际时间很高
EXPLAIN ANALYZE SELECT * FROM orders WHERE status=1 AND create_time > '2025-01-01';
-- 输出显示: Table scan on orders ... (actual rows=1000 ...) 或者索引扫描却花了很长时间
-- 进一步通过 optimizer_trace 分析

诊断步骤：

  1. 使用 SHOW INDEX FROM orders 查看 Cardinality，发现 idx_status 的基数估算严重偏低。
  2. 执行 ANALYZE TABLE orders;
  3. 如果优化器仍选择错误索引，考虑临时使用 FORCE INDEX (idx_status) 验证效果。
  4. 若 FORCE INDEX 后性能显著提升，可能需调整代价常数，但优先排查统计信息持久化配置。

5.4 图表：EXPLAIN ANALYZE 输出与估算对比示例图

flowchart TB
    subgraph Estimated["EXPLAIN (估算)"]
        E["rows: 100, cost: 101.00"]
    end
    subgraph Actual["EXPLAIN ANALYZE (实际)"]
        A["actual rows: 10000, actual time: 500ms"]
    end
    Estimated -- "严重偏差" --> Actual
    Actual --> Diagnose[诊断: 统计信息过时]
    Diagnose --> Fix1[ANALYZE TABLE]
    Fix1 --> Fix2[重新估算后 rows 接近 10000]

图 5-1 EXPLAIN ANALYZE 对比示例图

  • 图表意图：展示估算行数与实际行数的巨大偏差，并给出诊断路径。
  • 偏差影响：rows=100 估算下优化器选择索引嵌套循环，但实际 10000 行时可能全表扫描反而更快，导致性能灾难。
  • 诊断工具：EXPLAIN ANALYZE 直接暴露真相，引导 DBA 检查统计信息。
  • 修复方法：ANALYZE TABLE，必要时调整采样页数，甚至应用 FORCE INDEX 临时干预。

6. optimizer_trace 辅助分析

6.1 启用与读取

optimizer_trace 是 MySQL 提供的深度诊断工具，可以记录优化器生成执行计划的全部细节。

-- 开启 trace（注意：只在当前会话有效）
SET optimizer_trace='enabled=on';

-- 执行目标查询
SELECT ...;

-- 查看 trace 内容
SELECT * FROM information_schema.optimizer_trace\G

-- 关闭 trace
SET optimizer_trace='enabled=off';

输出是一个巨大的 JSON 文档，主要分为三个阶段：join_optimization（连接优化）、join_execution（连接执行）等。我们重点关注 join_optimization。

6.2 关键段解读

在 join_optimization 中，主要的子段：

  • condition_processing：条件的初始处理，如 WHERE 条件优化。
  • rows_estimation：对每张表的每个候选访问路径的代价估算。包含 table_scan 和各个索引的扫描代价（index scan）。每个索引会有一个 cost 和估算的 rows，以及是否被选中的原因。
  • considered_execution_plans：列出优化器考虑的所有连接顺序的计划，每个计划有总代价和具体表顺序。带有 chosen 标记的是最终选中的计划。
  • attaching_conditions_to_tables：显示条件如何附加到表上。

例如，当优化器拒绝某个索引时，可以在对应索引的估算部分找到 cause 字段，比如 "not a covering index" 或 "rows estimated to be too large"。

6.3 案例：分析优化器拒绝正确索引

SET optimizer_trace='enabled=on';
SELECT * FROM orders WHERE status=1 AND create_time > '2025-01-01';
SELECT * FROM information_schema.optimizer_trace\G

在输出中搜索 "range_scan_alternatives"，可以看到：

"range_scan_alternatives": [
  {
    "index": "idx_status",
    "ranges": ["(1) AND (1)"],
    "index_dives_for_eq_ranges": true,
    "rowid_ordered": false,
    "using_mrr": false,
    "index_only": false,
    "rows": 500000,
    "cost": 600000,
    "chosen": false,
    "cause": "cost too high"
  },
  {
    "index": "idx_create_time",
    "ranges": ["(2025-01-01, +inf)"],
    ...
    "rows": 100000,
    "cost": 120000,
    "chosen": true
  }
]

分析：idx_status 被拒绝是因为估算返回 50 万行，成本 60 万，而 idx_create_time 估算 10 万行，成本更低。但实际情况是 status=1 可能只有 1 万行，由于统计信息不准确导致估算偏差。此时应对表执行 ANALYZE TABLE orders; 后再试。

7. 面试高频专题

7.1 MySQL 优化器的核心职责是什么？它如何选择索引和 Join 顺序？

  • 一句话回答：优化器负责为 SQL 生成最低代价的执行计划，基于统计信息和代价模型选择索引和确定表连接顺序。
  • 详细解释：优化器首先进行查询重写，然后对每个表的候选访问路径（全表扫描、各索引扫描）计算成本，并在多表连接时穷举所有可能的表排列（受 optimizer_search_depth 限制），通过代价模型估算 CPU 和 IO 消耗，选出总代价最小的计划。索引选择依赖 Cardinality 估算区分度。
  • 多角度追问：
    1. 如果统计信息不准，优化器选错索引怎么办？→ 执行 ANALYZE TABLE 更新统计信息，或调整采样页数 innodb_stats_persistent_sample_pages，紧急时可用 FORCE INDEX 强制。
    2. 如何查看优化器考虑过的其他计划？→ optimizer_trace 记录完整决策过程，包括每个索引的成本和为何被抛弃。
    3. 为什么有时优化器不选择任何索引而走全表扫描？→ 可能是全表扫描的成本估算低于索引扫描，通常因为索引区分度低或统计信息高估索引返回行数。
    4. 搜索深度参数如何影响计划质量？→ optimizer_search_depth 默认 62，几乎不限制；减小可避免多表连接时优化耗时过长，但可能错过最优顺序。
    5. 优化器如何处理子查询？→ 通过 semijoin、物化、FirstMatch 等策略将子查询转为 Join，避免逐行执行。
  • 加分回答：优化器内部的 Join 顺序搜索采用深度优先加剪枝算法，optimizer_prune_level=1 启用后，一旦某个部分计划的成本已超过当前最优计划，便剪枝后续探索，极大减少搜索空间。代价模型中的常数可通过 mysql.server_cost 表调整，但这会影响全局。

7.2 EXPLAIN 的 type 列有哪些常见取值？如何从 ALL 优化到 ref？

  • 一句话回答：性能从优到劣为 NULL、const、eq_ref、ref、range、index、ALL，可通过创建合适的索引并将查询条件匹配索引最左前缀，将 ALL 提升为 ref 或 range。
  • 详细解释：ALL 代表全表扫描，需为 WHERE 条件中的列建立索引，并确保条件中使用索引列且无函数或隐式类型转换。如果是多条件，建议建立复合索引以覆盖更多条件，达到 ref（等值）或 range（范围）。如果是 Join 查询，被驱动表的 Join 列应建立索引，以达到 eq_ref 或 ref。
  • 多角度追问：
    1. eq_ref 和 ref 的核心区别是什么？→ eq_ref 使用唯一索引，对于每个驱动表行，至多返回一行；ref 使用非唯一索引，可能返回多行。
    2. range 类型在使用 IN 多个值时会怎样？→ 仍然显示 range，但可能实际进行多次索引查找，rows 是总估算行数。
    3. 覆盖索引能改变 type 吗？→ 不能，覆盖索引反映在 Extra 中，type 仍可能是 index（全索引扫描）或 ref/range。
    4. type=index 是坏事吗？→ 如果查询需要全表索引扫描来避免排序且结果集小，可以接受；但通常意味着过滤不佳，需优化。
    5. 如何查看实际扫描了多少行？→ EXPLAIN ANALYZE 提供实际行数，可与 rows 对比。
  • 加分回答：优化 type 还能降低锁范围。在 REPEATABLE READ 下，type=ALL 会导致全表所有行被加 Next-Key Lock，极易锁冲突。提升至 ref 或 range 后，仅索引范围内的行被加锁，大幅提升并发。

7.3 key_len 是如何计算的？它如何反映最左前缀的使用程度？

  • 一句话回答：key_len 是所用索引列的字节数总和，精确反映了复合索引中哪些列被使用，等值匹配的列越少，字节数越小。
  • 详细解释：计算方法为数据类型字节数加上字符集乘长度，加上可能的 NULL 标记和变长长度前缀。对于复合索引，若查询条件中某些列使用了范围查询或未出现在条件中，则索引使用停止在该列之前，key_len 仅累加能用到的列。通过 key_len 可以判断 key 字段显示的索引是否被完全利用。
  • 多角度追问：
    1. 为什么 DATETIME 类型在索引中占 5 字节？→ MySQL 5.6.4 后，DATETIME 加上小数秒存储，无小数秒时占 5 字节，有小数秒则更大。
    2. key_len 如何帮助诊断隐式类型转换？→ 如果 key_len 比预期小很多，可能因为某些条件发生了类型转换导致索引部分失效。
    3. 为什么有时 key_len 很大但查询很慢？→ 可能是索引条目太大，导致索引树高度增加或缓存效率低。
    4. 如何判断范围条件截断了索引使用？→ 范围条件（>, <, LIKE 'prefix%'）后的列不参与等值，key_len 仅累加到范围列之前。
    5. key_len 计算是否受字符集影响？→ 是的，utf8mb4 下每字符最大 4 字节，latin1 为 1 字节，因此相同的 VARCHAR 列在 key_len 中差异巨大。
  • 加分回答：在优化时可以通过 SHOW CREATE TABLE 结合 EXPLAIN 的 key_len，手动复算核实索引使用情况。如果发现查询只用到了复合索引的前缀部分，可考虑调整索引列顺序，让过滤性更好的列靠前。

7.4 Extra 中的 Using index、Using index condition 和 Using where 分别代表什么？有何区别？

  • 一句话回答：Using index 表示覆盖索引无回表；Using index condition 表示索引条件下推过滤（ICP）；Using where 表示 Server 层过滤。
  • 详细解释：
    • Using index：查询列都在使用的索引中，无需回表，效率最高。
    • Using index condition：引擎层利用索引中的列做部分 WHERE 条件过滤，但查询列需要回表（除非同时也覆盖）。这是 MySQL 5.6 引入的优化，默认开启。
    • Using where：引擎返回行后，Server 层检查非索引条件或进行额外过滤。常常伴随回表，效率相对较低。
  • 多角度追问：
    1. 这三种可以同时出现吗？→ Using index; Using where 表示覆盖索引被用于过滤，但过滤条件可能不是索引查找（如索引中的某些列作为过滤而非访问）。
    2. ICP 对哪些查询提升明显？→ 当索引过滤性一般且回表成本高时，ICP 可显著减少回表次数。
    3. 如何确认 ICP 是否真的生效？→ 通过 EXPLAIN 的 Extra 字段，或查看 Handler_icp_attempts 和 Handler_icp_match 状态变量。
    4. 什么情况下 Using where 是不可避免的？→ 如果查询条件中包含了没有索引的列，且该列无法通过索引下推，则必须由 Server 层过滤。
    5. Using index condition 和 Using index 可以同时出现吗？→ 不能，如果完全覆盖且无回表，不需要 ICP，只显示 Using index。
  • 加分回答：ICP 只能下推与所用索引相关的条件，不能下推引用其他表或其他索引的条件。理解这些信息有助于判断索引设计是否合理——如果 Extra 中频繁出现 Using where 而 filtered 很低，说明索引过滤性不足，需要调整。

7.5 EXPLAIN ANALYZE 与 EXPLAIN 的根本区别是什么？如何识别统计信息不准确？

  • 一句话回答：EXPLAIN 仅展示优化器估算的执行计划，不实际执行；EXPLAIN ANALYZE 实际执行并收集真实时间与行数，通过对比 rows 估算值和 actual rows 可以识别统计信息是否准确。
  • 详细解释：EXPLAIN ANALYZE 会实际运行查询，输出每个步骤的迭代器实际返回行数、执行时间和循环次数。当发现估算行数（EXPLAIN 的 rows 或 ANALYZE 中的 cost 部分显示的行数）与实际行数相差巨大（如数量级差异），几乎可以断定统计信息过期或不准确。此时应执行 ANALYZE TABLE 更新统计。
  • 多角度追问：
    1. EXPLAIN ANALYZE 会产生副作用吗？→ 会，与普通查询一样修改数据，因此最好在只读从库或事务中执行并回滚。
    2. actual time 的两个数字代表什么？→ 第一个是产生第一行输出的平均时间（毫秒），第二个是产生所有行的总时间。对于嵌套循环，需要注意 loops 次数，时间表示每次循环的平均值。
    3. 估算行数很少但实际很多，会导致什么问题？→ 优化器可能选择 NLJ 而不是 Hash Join，导致多次索引查找开销巨大。
    4. 如何避免统计信息频繁不准？→ 开启 innodb_stats_auto_recalc（默认开启，表变化 10% 自动更新），增大采样页数 innodb_stats_persistent_sample_pages。
    5. EXPLAIN ANALYZE 会影响查询缓存吗？→ MySQL 8.0 已移除查询缓存，无影响。
  • 加分回答：EXPLAIN ANALYZE 的输出中也包含优化器的 cost 信息，可以直接对比每个步骤的估算行数和实际行数。此外，对于复杂查询，可以先使用 EXPLAIN FORMAT=TREE 查看逻辑计划，再用 ANALYZE 确认。

7.6 Nested-Loop Join、Block Nested-Loop Join 和 Hash Join 的适用场景与性能差异？

  • 一句话回答：NLJ 依赖被驱动表索引，适合索引完善的小数据量 Join；BNL（已废弃）和 Hash Join 适用于无索引或大表 Join，Hash Join 将驱动表构建为哈希表，在内存充足时性能远优于 BNL。
  • 详细解释：NLJ 对于驱动表的每一行，通过索引查找被驱动表，复杂度为 O(N * log M)，索引查找本身很快。当被驱动表无索引时，之前使用 BNL，将驱动表行放入 Join Buffer，然后顺序扫描被驱动表进行匹配，复杂度近似 O(N * M)。Hash Join 则是将构建端（驱动表）的行按连接列哈希到内存哈希表，然后扫描探测端（被驱动表）每行去哈希表查找匹配，复杂度 O(N + M)。如果哈希表无法全部放入内存，会溢出到磁盘分片处理。
  • 多角度追问：
    1. 如何强制使用 Hash Join？→ 优化器在无索引时自动选择 Hash Join；不能直接通过 hint 强制，但可以通过 BNL 已移除，所以无索引的 Join 必然是 Hash Join。
    2. Hash Join 在内存和磁盘溢出时的性能差异大吗？→ 溢出磁盘（Grace Hash Join）会多次读写，性能显著下降，但仍优于 BNL。
    3. Join Buffer 大小（join_buffer_size）对 Hash Join 有何影响？→ 决定一次能构建的哈希表大小，增大可以避免溢出。
    4. 为什么 MySQL 8.0.20 移除了 BNL？→ Hash Join 在所有场景下都等于或优于 BNL，并且实现更现代。
    5. 如何通过 EXPLAIN 看出使用的是哪种 Join 算法？→ Extra 中 Using join buffer (hash join) 表示 Hash Join；NLJ 则没有此类字样，只显示 eq_ref 或 ref。
  • 加分回答：Hash Join 的哈希表构建端选择：优化器通常将较小的表作为构建端，因为内存占用少。EXPLAIN FORMAT=TREE 会显示 Hash inner join 和具体的构建端、探测端。

7.7 优化器的代价估算模型是如何工作的？如何通过修改代价常数影响索引选择？

  • 一句话回答：模型基于 server_cost 和 engine_cost 表中的 CPU 和 IO 常数，结合统计信息估算的扫描行数和页面数，计算出总代价；可调高全表扫描相关常数（如 io_block_read_cost）让优化器更倾向于使用索引。
  • 详细解释：代价计算公式大致为：全表扫描 cost = 聚簇索引页数 × io_block_read_cost + 表行数 × row_evaluate_cost。索引扫描 cost = 索引页数 × io_block_read_cost + 索引行数 × row_evaluate_cost + 回表成本。通过增大 io_block_read_cost（磁盘读取代价）可以使全表扫描成本上升，优化器更可能选择索引。但所有常数都是全局的，影响所有查询，因此应谨慎。
  • 多角度追问：
    1. 修改后需要重启吗？→ 不需要，执行 FLUSH OPTIMIZER_COSTS; 重新加载即可。
    2. 哪些常数影响最直接？→ row_evaluate_cost 和 io_block_read_cost 对全表扫描与索引扫描的权衡影响最大。
    3. 代价常数可以按会话设置吗？→ 不可以，仅全局有效。
    4. 为什么不直接使用 hint 而调整代价常数？→ hint 只影响单个查询，全局调整可能导致其他查询计划变差，应作为最后手段。
    5. 如何验证常数修改的效果？→ 使用 optimizer_trace 观察修改前后同一查询的 cost 变化。
  • 加分回答：成本模型中的 memory_block_read_cost 默认 0.25，反映从缓冲池读取页的代价。如果服务器的缓冲池非常大，大部分索引数据在内存中，实际 IO 成本很低，但优化器可能仍高估了索引扫描的代价，适当调低该值可鼓励索引使用。

7.8 什么是 semijoin 优化？它如何提升 IN 子查询的性能？

  • 一句话回答：semijoin 将 IN 子查询转换为类似 Join 的操作，当找到第一行匹配即可停止，避免了子查询的全量物化或逐行执行，从而大幅提升性能。
  • 详细解释：MySQL 实现了四种半连接策略：
    • FirstMatch：驱动表每行在被驱动表中找到第一条匹配行后立即停止当前行的查找。
    • LooseScan：利用索引顺序，扫描驱动表时跳过重复值，适用于 SELECT DISTINCT 或 GROUP BY。
    • MaterializeLookup：物化子查询结果并建立索引，外层表逐行探测。
    • Duplicate Weedout：使用临时表去除可能产生的重复行。 优化器根据查询结构、索引情况和成本选择最佳策略。
  • 多角度追问：
    1. 如何确认使用了 semijoin？→ EXPLAIN 的 Extra 会出现 Start temporary/End temporary（Duplicate Weedout）或 FirstMatch 等标志。
    2. semijoin 一定能提升性能吗？→ 大多数情况下是，但如果子查询结果极小且外层表也小，直接子查询或物化差距不大。
    3. 可以关闭 semijoin 吗？→ 通过 SET optimizer_switch='semijoin=off' 可以关闭所有半连接策略，或单独关闭某个策略。
    4. IN 子查询和 EXISTS 子查询优化有区别吗？→ 优化器会将 IN 转为 EXISTS，因此实质相同。
    5. 在 MySQL 5.7 中 semijoin 支持如何？→ 5.7 已支持，但 8.0 中策略选择和实现更成熟。
  • 加分回答：optimizer_trace 中的 semijoin 段会详细列出每种策略的成本和可行性，以及最终选择。如果发现性能问题，可以检查该段来理解优化器的决策。

7.9 如何通过 optimizer_trace 分析优化器拒绝某个索引的原因？

  • 一句话回答：查看 trace 中 rows_estimation 部分的每个候选索引的扫描成本及 cause 字段，对比不同索引的估算行数和成本，即可定位拒绝原因。
  • 详细解释：在 trace 的 join_optimization.rows_estimation 中，每个索引会有一个字典，包含 index、rows、cost 和 chosen 等字段。如果 chosen 为 false，通常有一个 cause 键，描述原因，例如 "cost too high"、"not covering index"、"requires truncation" 等。根据这些信息可以判断是统计信息不准确（rows 估算偏差大）还是索引本身不适合（如无法覆盖）。
  • 多角度追问：
    1. trace 输出太大怎么办？→ 可以用 JSON 路径提取，例如在 MySQL 8.0 中使用 JSON_EXTRACT(TRACE, ...)，或直接搜索 "cause" 关键字。
    2. 为什么有时索引的 rows 估算值比实际小很多？→ 可能是采样不足，导致 Cardinality 偏高，估算行数偏低。
    3. 如果 cause 显示 "cost too high" 但实际查询快怎么办？→ 可能是统计信息或代价常数不合适，可尝试 FORCE INDEX 验证。
    4. 能否看到索引扫描和全表扫描的具体成本比较？→ 是的，trace 会同时提供 table_scan 和各个索引的 cost，直观展示差距。
    5. trace 是否包含 Join 顺序决策？→ 是的，considered_execution_plans 中包含各种连接顺序及总代价。
  • 加分回答：除了 rows_estimation，在 attaching_conditions_to_tables 部分还能看到哪些条件被附加到索引上，以及是否进行了索引条件下推。

7.10 DEPENDENT SUBQUERY 为何性能差？如何改写？

  • 一句话回答：相关子查询对外层每一行都要执行一次子查询，复杂度 O(N*M)，应改写为 JOIN 或使用 EXISTS 子查询并确保索引。
  • 详细解释：DEPENDENT SUBQUERY 出现在子查询引用了外层列时，例如 SELECT ... FROM t1 WHERE t1.a = (SELECT t2.b FROM t2 WHERE t2.c = t1.c)。如果外层返回 1 万行，子查询就要执行 1 万次。改写方法：
    • 使用内连接：SELECT t1.* FROM t1 JOIN t2 ON t1.c = t2.c AND t1.a = t2.b
    • 使用派生表物化子查询：SELECT t1.* FROM t1 JOIN (SELECT DISTINCT b, c FROM t2) AS dt ON t1.c = dt.c AND t1.a = dt.b
    • 利用 MySQL 8.0 的 semijoin 自动优化（对于 IN 类相关子查询）。
  • 多角度追问：
    1. 如何识别是 DEPENDENT SUBQUERY？→ EXPLAIN 的 select_type 列。
    2. 所有相关子查询都慢吗？→ 如果外层结果集极小（如 1 行），影响很小。
    3. MySQL 8.0 对相关子查询的优化有何改进？→ 更多场景下可自动转换为 semijoin 或使用物化策略，消除 DEPENDENT 标记。
    4. 改写为 JOIN 需要处理重复行问题吗？→ 是的，INNER JOIN 可能导致行数膨胀，需使用 SELECT DISTINCT 或分析是否真的需要去重。
    5. NOT IN 相关子查询如何优化？→ 通常使用 LEFT JOIN ... IS NULL 或 NOT EXISTS 改写。
  • 加分回答：optimizer_trace 可显示子查询是否被成功转换为 semijoin 或物化，从而确认是否还需手动改写。

7.11 Using filesort 是否一定很慢？什么情况下可以接受？

  • 一句话回答：不一定慢；当排序数据量小于 sort_buffer_size 时在内存完成，很快；只有数据量大到溢出磁盘或占用大量 CPU 时才成为瓶颈。
  • 详细解释：filesort 的名字有误导性，实际优先使用内存排序（快速排序算法）。如果 sort_buffer_size 不足以容纳排序数据，则使用磁盘文件归并排序，此时性能急剧下降。可接受的情况：
    • 排序结果集很小（例如 LIMIT 10 配合 ORDER BY）。
    • 排序列的数据类型紧凑，比较开销低。
    • 应用确实需要这种排序，且无合适索引覆盖。
  • 多角度追问：
    1. 如何判断是否发生了磁盘排序？→ EXPLAIN ANALYZE 的 Sort 迭代器会显示 disk 或 memory；或者查看状态变量 Sort_merge_passes。
    2. sort_buffer_size 调多大合适？→ 按需设置，建议不要超过 1M-4M（会话级），避免大量并发排序耗尽内存。
    3. 通过索引消除 filesort 的原理？→ 索引本身有序，如果 ORDER BY 列顺序与索引列顺序一致且无反向扫描，可直接利用索引避免排序。
    4. 能不能通过覆盖索引消除 filesort？→ 可以，如果排序和选择的列都在一个索引中，既避免回表也避免排序。
    5. 什么情况下 filesort 比索引排序更快？→ 极少见，当索引扫描成本过高而排序数据极少时可能出现。
  • 加分回答：MySQL 8.0 对 filesort 进行了优化，支持 packed addon fields，排序时直接携带行数据，减少回表。可以通过 max_sort_length 限制携带的列长度。

7.12 场景分析题：线上一个复杂查询 EXPLAIN 显示 type=ALL 且 rows=1000万，但实际表只有100万行且索引正常，分析可能的原因并给出排查步骤。

  • 一句话回答：最可能的原因是统计信息失真，或查询条件中使用了函数/隐式类型转换导致索引失效，或者是分区表且未进行分区裁剪。
  • 详细解释与排查步骤：
    1. 检查索引失效原因：对比实际 SQL 和 EXPLAIN 的 key 列。查看 WHERE 条件是否包含对索引列的函数操作（如 DATE(create_time)、LEFT(col,3)）或隐式类型转换（字符串列与数字比较）。通过 SHOW WARNINGS 查看优化器改写后的 SQL，可能暴露类型转换。
    2. 检查统计信息：执行 SHOW INDEX FROM 表名，查看 Cardinality 是否接近实际值。如果偏差极大，执行 ANALYZE TABLE 表名; 重新采样。同时检查 innodb_stats_persistent_sample_pages 是否设置过低（默认20），适当增大（如200）后再次 ANALYZE。
    3. 使用 EXPLAIN ANALYZE 确认实际扫描行数：如果也显示扫描了 1000 万行，而表只有 100 万行，极度异常，可能表损坏，需要 CHECK TABLE。
    4. 检查是否存在视图或派生表被物化且统计信息错误：如果查询涉及派生表，派生表的统计信息可能与实际不符。
    5. 查看 optimizer_trace：开启 trace 获取优化器为何选择全表扫描的详细原因，比如全表扫描成本估算极低。
    6. 检查分区表：如果表是分区表，EXPLAIN 的 partitions 列应显示具体分区，若显示所有分区，则分区裁剪失败，需检查分区条件。
    7. 临时验证：使用 FORCE INDEX 强制使用某个索引，观察实际性能是否提升，以确认是优化器选择错误。
    8. 检查 MySQL 版本：某些 8.0 早期版本存在直方图或统计信息 bug，考虑升级小版本。
  • 多角度追问：
    1. 为什么 rows 会显示比实际表行数还大？→ 可能是统计信息错误地将少量行高估，或者表经历过大量删除后未重建统计信息。
    2. 如何防止统计信息过期？→ 开启 innodb_stats_auto_recalc，并设置合理的 innodb_stats_persistent_sample_pages。
    3. 索引不可见（ALTER TABLE ... ALTER INDEX ... INVISIBLE）会不会导致？→ MySQL 8.0 支持不可见索引，通过 SHOW CREATE TABLE 检查索引可见性。
    4. 直方图统计（MySQL 8.0）是否可能误导？→ 是的，直方图只影响 filtered 估算，如果为某列创建了错误的直方图，也可能导致计划偏差。
    5. 如果优化器仍选错，除了 hint 还能做什么？→ 可以考虑调整代价常数，或使用 STATISTICS 提示（MySQL 8.0 支持 /*+ SET_VAR(optimizer_switch='...') */）。
  • 加分回答：结合锁问题，type=ALL 在 RR 隔离级别下会导致全表加 Next-Key Lock，可能瞬间阻塞大量并发，应尽快优化或降级隔离级别为 RC（前提是业务可接受）。

附录：EXPLAIN 字段速查表

字段含义常见问题优化方向
id查询执行顺序多个 id 表示复杂查询关注大 id 先执行的子查询，优化瓶颈部分
select_type查询类型DEPENDENT SUBQUERY 极差改写为 JOIN 或启用 semijoin
type访问方式ALL 或 index 警告创建/调整索引，覆盖索引
possible_keys候选索引为 NULL 表示无索引可用按 WHERE/JOIN 列建索引
key实际使用索引可能为空或非最优分析统计信息，必要时 hint
key_len索引使用长度长度短说明未用全复合索引优化条件顺序，匹配最左前缀
ref匹配列/常量func 表示使用函数避免函数，保持常量匹配
rows估算扫描行数与实际偏差大ANALYZE TABLE，调整采样页
filtered过滤百分比值低但 rows 大，浪费扫描加索引提高过滤性能
Extra额外信息Using filesort, Using temporary加索引消除排序/临时表，或优化查询

延伸阅读

  • 《高性能MySQL》第4版：优化器与查询优化章节
  • MySQL 8.0 官方文档：Optimizer Cost Model, EXPLAIN, EXPLAIN ANALYZE, optimizer_trace
  • MySQL Server Team Blog: Hash Join in MySQL 8.0.18

SQL 优化不是玄学，而是理解优化器决策后对执行计划的验证与纠正。EXPLAIN 是你的显微镜，EXPLAIN ANALYZE 是实测数据，optimizer_trace 则是优化器的大脑日志。

avatar
文章
阅读
粉丝