3、SQL执行计划基础：explain命令解读、type列详解与rows/filtered分析

3.1 为什么执行计划是性能调优的“第一现场”

在批次追溯数据场景中，采购计划优先级排序往往涉及多表关联（批次表、采购订单表、供应商表、质检表）和大量范围查询（日期、批次号、优先级分数）。SQL执行计划是数据库优化器为一条SQL语句生成的“操作路线图”，它直接决定了查询是秒级返回还是分钟级等待。

通过 EXPLAIN 命令，我们可以透视优化器的选择：是否使用了索引、扫描了多少行、关联顺序如何。对于批次追溯这类数据量大、过滤条件复杂的查询，读懂执行计划是性能调优的起点。

调优铁律： 任何批次追溯查询在上线前，必须用 EXPLAIN 验证其执行计划。如果 type 列出现 ALL（全表扫描），且 rows 超过万级，该查询几乎必然成为性能瓶颈。

3.2 EXPLAIN 命令解读：输出列与核心含义

在 MySQL 中，使用 EXPLAIN SELECT ... 即可查看执行计划。输出结果包含多个关键列，下表列出了与批次追溯性能最相关的列及其含义：

列名 含义 批次追溯场景关注点
id 查询中每个SELECT子句的标识符，id越大越先执行 多表关联时，观察子查询或派生表的执行顺序
select_type 查询类型（SIMPLE、PRIMARY、SUBQUERY、DERIVED等） 避免DEPENDENT SUBQUERY，它会导致逐行执行子查询
table 当前操作的表名或别名 确认是否访问了正确的批次表、订单表
type 访问方法（性能从好到差：system > const > eq_ref > ref > range > index > ALL） 核心关注列，必须达到range及以上
possible_keys 可能使用的索引列表 检查批次号、日期字段的索引是否被识别
key 实际使用的索引名称 确认优化器是否选择了正确的复合索引
key_len 使用的索引字节长度 判断复合索引是否被充分使用（长度越长，用到的索引列越多）
ref 与索引比较的列或常量 关联查询时，确认关联字段是否使用了索引
rows 优化器估算的需要扫描的行数 性能估算核心，值越小越好
filtered 经过表条件过滤后，剩余行数占总行数的百分比 值越低，说明索引过滤性越好；值高（如100%）可能意味着缺少索引
Extra 额外信息（Using index、Using where、Using filesort等） 出现 Using filesort 或 Using temporary 时需警惕
实战口诀： 先看 type 定好坏，再看 rows 估代价，最后 Extra 查隐患。

3.3 type 列详解：从全表扫描到常量查找

type 列是执行计划中最重要的性能指标。它描述了MySQL如何访问表中的数据。下面按性能从最差到最优的顺序逐一解读：

3.3.1 ALL —— 全表扫描（性能灾难）

MySQL 将读取表中的所有数据行，然后通过 WHERE 条件过滤。在批次表数据量达到百万级时，一次 ALL 扫描可能耗时数秒甚至数十秒。

  • 触发场景： 查询条件未使用索引，或优化器认为全表扫描比使用索引更快（通常发生在小表或查询返回大部分数据时）。
  • 批次追溯案例： SELECT * FROM batch WHERE status = 'ACTIVE'，如果 status 字段没有索引，将触发全表扫描。
  • 调优目标： 必须消除 ALL，至少提升到 index 或 range。

3.3.2 index —— 全索引扫描

MySQL 扫描整个索引树来获取数据。虽然比全表扫描快（因为索引通常比数据小），但仍然需要遍历所有索引条目。

  • 触发场景： 查询需要覆盖索引中的所有行，或者查询的列都在索引中但无法利用索引的过滤性。
  • 批次追溯案例： SELECT batch_id, batch_date FROM batch ORDER BY batch_date，如果 batch_date 有索引，但需要返回所有行，则可能使用 index 扫描。
  • 调优建议： 如果查询只需要部分数据，应添加 WHERE 条件将其转为 range。

3.3.3 range —— 索引范围扫描（批次追溯的及格线）

MySQL 只扫描索引中满足指定范围的行。这是批次追溯查询的最低可接受标准。

  • 触发场景： 使用 BETWEEN、>、<、IN()、LIKE 'prefix%' 等范围操作符，且对应字段有索引。
  • 批次追溯案例： SELECT * FROM batch WHERE batch_date BETWEEN '2024-01-01' AND '2024-01-31'，如果 batch_date 有索引，则使用 range。
  • 调优要点： 确保范围查询的字段在复合索引中位于最左侧，或单独建立索引。

3.3.4 ref —— 非唯一索引等值匹配

MySQL 使用非唯一索引或唯一索引的前缀进行等值匹配，返回可能匹配的多行。

  • 触发场景： 使用 = 操作符关联或过滤，且关联字段是非唯一索引。
  • 批次追溯案例： SELECT * FROM batch WHERE supplier_id = 100，如果 supplier_id 有非唯一索引。
  • 性能特点： 比 range 更快，因为等值匹配的扫描范围更精确。

3.3.5 eq_ref —— 唯一索引等值匹配（最优关联）

MySQL 使用唯一索引或主键进行等值匹配，对于每个驱动表的行，最多返回一行匹配数据。这是多表关联查询中最理想的关联类型。

  • 触发场景： 多表 JOIN 时，被驱动表的关联字段是主键或唯一索引。
  • 批次追溯案例： SELECT * FROM purchase_order po JOIN batch b ON po.batch_id = b.id，其中 b.id 是主键。
  • 调优目标： 所有基于主键或唯一键的关联查询，都应达到 eq_ref。

3.3.6 const —— 常量查找（性能巅峰）

MySQL 将查询条件优化为常量，最多返回一行数据，且读取速度极快（通常只需读取一次索引）。

  • 触发场景： 使用主键或唯一索引的等值查询，且条件值是常量。
  • 批次追溯案例： SELECT * FROM batch WHERE id = 12345。
  • 性能特点： 几乎零成本，是性能调优的终极目标。

3.4 type 列性能对比速查表

type 值 性能等级 扫描方式 典型场景 批次追溯可接受度
ALL ★☆☆☆☆ 全表扫描 无索引过滤 不可接受，必须优化
index ★★☆☆☆ 全索引扫描 覆盖索引但无过滤 仅小表可接受
range ★★★☆☆ 索引范围扫描 日期范围、IN列表 最低及格线
ref ★★★★☆ 非唯一索引等值 供应商ID、状态码 良好
eq_ref ★★★★★ 唯一索引等值 主键关联 最优关联
const ★★★★★ 常量查找 主键单行查询 最优单行

3.5 rows 与 filtered 分析：估算查询代价

rows 和 filtered 两列共同决定了查询的实际扫描代价。

3.5.1 rows —— 优化器估算的扫描行数

rows 是优化器根据索引统计信息估算的、需要读取的行数。这个值越接近实际返回的行数，说明索引的过滤性越好。

  • 解读方法： 如果 rows 为 100000，但实际查询只返回 10 行，说明索引过滤性差，需要优化索引设计。
  • 批次追溯案例： 查询 SELECT * FROM batch WHERE status = 'PENDING' AND priority_score > 80，如果 rows 显示 50000，但实际只有 500 行满足条件，说明 status 索引的区分度太低。
  • 调优动作： 考虑建立 (status, priority_score) 复合索引，让 priority_score 的范围过滤提前生效。

3.5.2 filtered —— 过滤后的剩余百分比

filtered 表示在 rows 估算的行数中，经过 WHERE 条件过滤后，实际需要返回的行数占比。值越低，说明索引过滤效果越好。

  • 计算公式： 实际扫描行数 ≈ rows × filtered / 100。
  • 解读方法： filtered = 100% 通常意味着索引没有起到过滤作用，所有扫描的行都返回了；filtered = 10% 意味着索引过滤掉了 90% 的行。
  • 批次追溯案例： 执行计划显示 rows = 10000，filtered = 5%，则实际扫描行数约为 500 行，性能良好。
常见误区： 不要只看 rows 而忽略 filtered。一个 rows = 1000 但 filtered = 100% 的查询，实际扫描了 1000 行；而一个 rows = 5000 但 filtered = 10% 的查询，实际只扫描了 500 行。后者性能反而更好。

3.6 综合案例：批次追溯查询的执行计划分析

假设我们有一个批次追溯查询，用于计算采购计划优先级排序：

EXPLAIN
SELECT b.batch_id, b.batch_date, b.priority_score, po.order_amount
FROM batch b
JOIN purchase_order po ON b.batch_id = po.batch_id
WHERE b.batch_date BETWEEN '2024-06-01' AND '2024-06-30'
  AND b.status = 'ACTIVE'
  AND po.priority_level > 3
ORDER BY b.priority_score DESC;

假设执行计划输出如下：

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE b range idx_batch_date, idx_status idx_batch_date 3 NULL 12000 10% Using where; Using filesort
1 SIMPLE po ref idx_batch_id, idx_priority idx_batch_id 4 batch.b.batch_id 1 50% Using where

分析解读：

  • 表 b（批次表）： type = range，使用了 idx_batch_date 索引进行日期范围扫描，达到及格线。但 rows = 12000，filtered = 10%，实际扫描约 1200 行，说明日期索引过滤性一般（一个月内仍有大量批次）。Extra 显示 Using filesort，说明排序未使用索引，需要额外排序操作。
  • 表 po（采购订单表）： type = ref，使用 idx_batch_id 索引进行等值关联，性能良好。rows = 1，说明每个批次号在订单表中通常只有一条记录。
  • 优化建议： 为批次表建立复合索引 (batch_date, status, priority_score)，让 status 过滤和 priority_score 排序都能利用索引，消除 Using filesort。

3.7 执行计划分析流程总结

在批次追溯性能调优中，建议按以下步骤分析执行计划：

  1. 第一步：检查 type 列 —— 确保所有表的访问类型至少为 range。如果出现 ALL，立即添加索引。
  2. 第二步：检查 key 列 —— 确认实际使用的索引是否与查询条件匹配。如果 key 为 NULL，说明没有使用索引。
  3. 第三步：计算实际扫描行数 —— 用 rows × filtered / 100 估算真实扫描量。如果超过万级，考虑优化索引或改写查询。
  4. 第四步：检查 Extra 列 —— 消除 Using filesort（通过排序字段建索引）和 Using temporary（通过优化GROUP BY或DISTINCT）。
  5. 第五步：验证优化效果 —— 修改后再次执行 EXPLAIN，对比 rows 和 type 的变化。
SVG 图说明： 下图展示了从执行计划输出到性能调优决策的完整分析流程，帮助你将本章知识点串联为可操作的调优路径。
SQL执行计划分析流程 SQL执行计划分析流程 —— 从EXPLAIN到调优决策 EXPLAIN 输出 第一步：检查 type 列 是否 ≥ range？ type = ALL? 添加索引 继续下一步 第二步：检查 key 列 索引是否匹配查询条件？ 第三步：计算 rows × filtered 实际扫描行数是否超过万级？ 第四步：检查 Extra 列 是否有 filesort / temporary？ 制定索引优化方案

3.8 本章小结

  • EXPLAIN 是性能调优的起点：任何批次追溯查询上线前，必须用 EXPLAIN 验证执行计划。
  • type 列决定访问效率：从 ALL（全表扫描）到 const（常量查找），性能逐级提升。批次追溯查询至少应达到 range 级别。
  • rows 和 filtered 联合估算代价：实际扫描行数 = rows × filtered / 100，这个值才是真正的性能瓶颈指标。
  • Extra 列暴露隐藏问题：Using filesort 和 Using temporary 是性能杀手，应通过索引优化消除。
  • 分析流程可重复：每次索引调整后，重新执行 EXPLAIN 对比变化，形成“分析-优化-验证”的闭环。
← 上一章 📖 返回目录 下一章 →