执行计划

导学

当你觉得一条 SQL 很慢时，第一步不是盲目加索引，而是先看它的执行计划。EXPLAIN 是 MySQL 提供的"诊断神器"，它告诉你 MySQL 准备如何执行你的 SQL。

定义

执行计划（Execution Plan）：MySQL 优化器根据表结构、索引统计信息和 SQL 语句，生成的查询执行方案。EXPLAIN 语句可以展示这个方案，帮助开发者理解查询的性能特征。

EXPLAIN 核心列

EXPLAIN 输出包含多列关键信息，下面通过完整示例逐一理解。

示例：准备测试数据

-- 建表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept VARCHAR(20),
    score DECIMAL(5,2),
    INDEX idx_dept (dept),
    INDEX idx_dept_score (dept, score)
);

-- 插入测试数据
INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前数据状态：

EXPLAIN 输出列详解

执行以下语句查看执行计划：

EXPLAIN SELECT * FROM employees WHERE dept = '技术部';

输出结果：

各列含义解读：

结果解读：

• type = ref：使用了非唯一索引，性能较好
• possible_keys 列出了两个可用索引：idx_dept 和 idx_dept_score
• key = idx_dept：优化器选择了 idx_dept 索引
• rows = 2：估算需要扫描约 2 行（技术部有 2 人）
• Extra = Using index condition：使用了索引条件下推（ICP）优化

type 列：访问类型（从优到劣）

优化目标：至少达到 range，最好是 ref 及以上。尽量避免 ALL（全表扫描）。

示例：各种 type 的实际演示

1. const 类型（最优）

操作语句：

EXPLAIN SELECT * FROM employees WHERE emp_id = 1;

执行计划结果：

结果解读：

• type = const：通过主键 emp_id = 1 直接定位到唯一一行
• rows = 1：只扫描 1 行，最优情况
• 这是单表查询中最好的访问类型

2. ref 类型（使用非唯一索引）

操作语句：

EXPLAIN SELECT * FROM employees WHERE dept = '技术部';

执行计划结果：

结果解读：

• type = ref：使用了非唯一索引 idx_dept
• ref = const：与常量 '技术部' 进行比较
• rows = 2：估算扫描 2 行（技术部有 2 人：大翔、白歌）

3. range 类型（索引范围扫描）

操作语句：

EXPLAIN SELECT * FROM employees WHERE emp_id BETWEEN 1 AND 2;

执行计划结果：

结果解读：

• type = range：使用主键进行范围扫描
• rows = 2：估算扫描 2 行（emp_id 为 1、2）
• 范围查询包括：BETWEEN、IN、>、>=、<、<=

4. ALL 类型（全表扫描，需优化）

操作语句：

EXPLAIN SELECT * FROM employees WHERE score = 100;

执行计划结果：

结果解读：

• type = ALL：全表扫描，没有使用任何索引
• possible_keys = NULL：没有可用的索引
• rows = 2：需要扫描表中所有 2 行数据
• 优化方向：给 score 列添加索引：CREATE INDEX idx_score ON employees(score);

添加索引后再次执行：

CREATE INDEX idx_score ON employees(score);
EXPLAIN SELECT * FROM employees WHERE score = 100;

优化后的执行计划：

• type 从 ALL 优化为 ref
• rows 从 2 降到 1

Extra 列：关键信息

示例：Extra 列的各种情况

1. Using index（覆盖索引，最优）

操作语句：

EXPLAIN SELECT dept, score FROM employees WHERE dept = '技术部';

执行计划结果：

结果解读：

• Extra = Using index：表示覆盖索引，查询所需的所有列（dept、score）都在索引中，无需回表查询聚簇索引
• key = idx_dept_score：使用了复合索引
• 这是查询优化的最佳结果之一

2. Using filesort（需要优化）

操作语句：

EXPLAIN SELECT * FROM employees WHERE dept = '技术部' ORDER BY emp_name;

执行计划结果：

结果解读：

• Extra 包含 Using filesort：MySQL 需要额外的排序操作（不是在索引中完成的排序）
• 原因：idx_dept 索引只包含 dept 列，不包含 emp_name，所以获取数据后需要额外排序
• 优化方向：创建复合索引 (dept, emp_name)

CREATE INDEX idx_dept_name ON employees(dept, emp_name);
EXPLAIN SELECT * FROM employees WHERE dept = '技术部' ORDER BY emp_name;

优化后的执行计划：

• Using filesort 消失了，排序直接在索引中完成

3. Using temporary（需要优化）

操作语句：

EXPLAIN SELECT dept, COUNT(*) FROM employees GROUP BY dept ORDER BY COUNT(*) DESC;

执行计划结果（取决于数据和索引情况，可能包含）：

结果解读：

• Using temporary：MySQL 需要创建临时表来处理 GROUP BY
• Using filesort：还需要额外排序
• 优化方向：确保 GROUP BY 的列有索引，且 ORDER BY 与 GROUP BY 一致

EXPLAIN FORMAT=JSON

MySQL 5.7 支持 JSON 格式的执行计划，信息更丰富。

示例

操作语句：

EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE dept = '技术部';

输出示例（节选关键部分）：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.41"
    },
    "table": {
      "table_name": "employees",
      "access_type": "ref",
      "possible_keys": ["idx_dept", "idx_dept_score"],
      "key": "idx_dept",
      "used_key_parts": ["dept"],
      "key_length": "63",
      "ref": ["const"],
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.01",
        "eval_cost": "0.40",
        "prefix_cost": "1.41",
        "data_read_per_join": "2K"
      },
      "used_columns": ["emp_id", "emp_name", "dept", "score"]
    }
  }
}

结果解读：

• query_cost：查询成本估算值（越小越好）
• rows_examined_per_scan：每次扫描检查的行数
• filtered：过滤后的百分比（100% 表示所有扫描的行都符合条件）
• read_cost 和 eval_cost：详细的成本分解

多表 JOIN 的执行计划

示例：准备第二张表

CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL,
    score DECIMAL(5,2),
    INDEX idx_emp_id (emp_id)
);

INSERT INTO scores (emp_id, score) VALUES
(1, 90),
(2, 85);

当前 scores 表数据：

JOIN 执行计划分析

操作语句：

EXPLAIN SELECT e.emp_name, sc.score
FROM employees e
JOIN scores sc ON e.emp_id = sc.emp_id
WHERE e.dept = '技术部';

执行计划结果：

结果解读：

• 两行输出表示两个表的 JOIN 执行顺序
• 第一行 table = e：先访问 employees 表（驱动表），使用 idx_dept 索引过滤出 dept = '技术部' 的记录（约 2 行）
• 第二行 table = sc：对每一行驱动表记录，通过 emp_id 到 scores 表查找匹配，使用 idx_emp_id 索引
• sc 表的 ref = test.e.emp_id：使用 employees 表的 emp_id 作为查找值
• sc 表的 rows = 1：对每个员工，平均在 scores 表中找到 1 条匹配记录

常见误区

示例：EXPLAIN 不执行查询的验证

操作语句：

-- 查看当前最大 emp_id
SELECT MAX(emp_id) FROM employees;

-- 执行 EXPLAIN（不会真的插入数据）
EXPLAIN INSERT INTO employees (emp_name, dept) VALUES ('测试', '测试部门');

执行计划结果：

结果解读：

• EXPLAIN 对 INSERT/UPDATE/DELETE 同样有效，但只显示计划，不会实际修改数据
• 执行后再查 SELECT MAX(emp_id) FROM employees;，值不会变化，证明数据未被插入

面试考点

Q：EXPLAIN 中 type 列从优到劣的顺序？

system > const > eq_ref > ref > range > index > ALL。优化目标至少达到 range。

Q：Using filesort 是什么意思？如何优化？

表示 MySQL 需要额外的排序操作（不是用索引排序）。优化方法：给 ORDER BY 的列添加索引，或调整复合索引顺序让排序列在索引中。

Q：Using temporary 是什么意思？

表示 MySQL 需要创建临时表来处理查询（如 GROUP BY、DISTINCT、某些 ORDER BY）。这通常意味着查询较复杂或缺少合适的索引。

小结

• EXPLAIN 是分析 SQL 性能的第一步
• 关注 type（访问类型）和 Extra（额外操作）
• 目标是避免 ALL（全表扫描）、Using filesort 和 Using temporary
• 覆盖索引（Using index）是查询优化的最佳结果

下一章引子：EXPLAIN 告诉你计划怎么执行，而慢查询日志则告诉你哪些查询实际执行得很慢。