文章目录
- 一、什么是explain
- 二、explain基础用法
- 三、执行计划字段深度解析
- 3.1、id 列
- 3.2、select_type 类型
- 3.3、type 访问类型（性能关键指标）
- 3.4、key_len 计算规则
- 3.5、Extra 重要信息
- 四、实战案例分析
- 五、高级技巧与最佳实践
- 5.1、JSON格式输出分析
- 5.2、执行计划可视化工具
- 5.3、优化器提示
- 5.4、统计信息管理
- 六、常见误区与注意事项
- 七、EXPLAIN 执行计划优化路线图
一、什么是explain
EXPLAIN 是 MySQL 提供的用于分析 SQL 查询执行计划的工具，通过该命令可以获取查询优化器选择的执行路径。
总结： explain可以查看sql执行时是否有使用到索引，关联查询时可以查看sql的执行顺序。
Explain被称为执行计划，在语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。（注意，如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）。
在 MySQL 8.0 版本后，新增 EXPLAIN ANALYZE 功能，可提供实际执行统计信息。
二、explain基础用法
1. 基本语法
EXPLAIN [FORMAT = {TRADITIONAL|JSON|TREE}] SELECT ...;
解释：
explain+sql语句，执行后就可以查看该sql的执行顺序，是否使用索引等信息。
2. 常用参数
- FORMAT：指定输出格式（默认传统表格）
- PARTITIONS：显示分区信息
- ANALYZE：实际执行并收集统计（8.0+）
3. 输出示例
EXPLAIN SELECT * FROM employees WHERE last_name = 'tom';
输出结果示例：
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3000 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
典型输出包含以下核心列：
| 列名 | 说明 | 关键值 |
|---|---|---|
| id | 查询序列号 | 相同id按顺序执行，不同id从大到小执行 |
select_type | 查询类型 | SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION |
| table | 访问的表名 | <unionM,N> 表示联合查询结果 |
| partitions | 匹配的分区 | 分区表可见具体分区 |
type | 访问类型 | system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能使用的索引 | 显示候选索引列表 |
key | 实际使用的索引 | NULL 表示未使用索引 |
| key_len | 索引长度 | 复合索引实际使用部分长度 |
| ref | 索引比较的列 | const/库名.表名.列名 |
| rows | 预估扫描行数 | 基于统计信息估算 |
| filtered | 过滤百分比 | 100% 表示完全匹配索引 |
| Extra | 附加信息 | Using filesort/Using temporary/Using index |
三、执行计划字段深度解析
3.1、id 列
- 查询执行顺序标识
- 相同 id 表示同级别执行
- 数字越大优先级越高
- NULL 表示结果集合并
3.2、select_type 类型
| 类型 | 描述 |
|---|---|
SIMPLE | 简单查询（无子查询或UNION） |
| PRIMARY | 外层查询 |
| SUBQUERY | 子查询中的第一个SELECT |
| DERIVED | 派生表（FROM子句中的子查询） |
| UNION | UNION中的第二个或之后的SELECT |
| UNION RESULT | UNION的结果 |
3.3、type 访问类型（性能关键指标）
按性能从优到劣排序：
system > const > eq_ref > ref > range > index > ALL
| 类型 | 扫描方式 | 出现场景 |
|---|---|---|
| system | 系统表单行记录 | MyISAM引擎统计表 |
| const | 主键/唯一索引等值查询 | WHERE id = 1 |
| eq_ref | 唯一索引关联查询 | JOIN使用主键或唯一索引 |
| ref | 非唯一索引查找 | 普通二级索引查询 |
| range | 索引范围扫描 | BETWEEN、IN、> 等范围查询 |
| index | 全索引扫描 | 覆盖索引但需扫描全部索引 |
| ALL | 全表扫描 | 无可用索引或需要读取大部分数据 |
1. type 访问类型详解（性能从优到劣）
- system：系统表单行访问（内存表）
- const：主键或唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE id = 1;
- eq_ref：关联查询主键匹配
EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id;
- ref：非唯一索引等值查询
CREATE INDEX idx_age ON users(age);EXPLAIN SELECT * FROM users WHERE age = 30;
- range：索引范围扫描
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
- index：全索引扫描
EXPLAIN SELECT COUNT(*) FROM users USE INDEX(idx_age);
- ALL：全表扫描（需重点优化）
3.4、key_len 计算规则
索引使用长度的计算方法：
key_len = (字符列长度 * 字符集bytes) + (是否NULL? 1:0) + (变长类型? 2:0)
示例：VARCHAR(255) utf8mb4 可为NULL列
(255*4) + 1 + 2 = 1023 bytes
3.5、Extra 重要信息
| 值 | 含义 |
|---|---|
| Using index | 覆盖索引扫描（无需回表） |
| Using where | 存储引擎返回数据后在Server层过滤 |
| Using temporary | 使用临时表（常见于GROUP BY/ORDER BY） |
| Using filesort | 额外排序操作（需优化索引或调整排序方式） |
| Using index condition | 索引条件下推（ICP优化） |
| Select tables optimized away | 通过索引直接获取统计信息（如MIN/MAX） |
四、实战案例分析
案例1：索引失效分析
EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2023;
输出结果：
type: ALL
key: NULL
Extra: Using where
问题诊断：对列使用函数导致索引失效
优化方案：
ALTER TABLE orders ADD INDEX idx_order_date (order_date);
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
案例2：连接查询优化
EXPLAIN
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
输出显示：
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| 1 | SIMPLE | e | ALL | dept_id | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | company.e.dept_id | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
优化建议：为 employees 表的 dept_id 字段添加索引
五、高级技巧与最佳实践
5.1、JSON格式输出分析
EXPLAIN FORMAT=JSON SELECT ...;
可获取更详细的成本估算信息：
{"query_block": {"cost_info": {"query_cost": "1.20"},"table": {"access_type": "range","rows_examined_per_scan": 500,"rows_produced_per_join": 500,"filtered": "100.00","cost_info": {"read_cost": "0.50","eval_cost": "0.70","prefix_cost": "1.20"}}}
}
5.2、执行计划可视化工具
推荐使用：
- MySQL Workbench Visual Explain
- Percona Toolkit 的 pt-visual-explain
- 在线工具：https://explain.dalibo.com/
5.3、优化器提示
强制使用指定索引：
SELECT * FROM table USE INDEX (index_name) ...
5.4、统计信息管理
ANALYZE TABLE table_name; -- 更新统计信息
SHOW INDEX FROM table_name; -- 查看索引基数
六、常见误区与注意事项
- rows 列是估算值，实际值可能偏差较大
- 索引覆盖不代表高效，需结合扫描行数判断
- 并非所有 Using filesort 都需要优化，小数据量排序是正常现象
- 强制索引可能适得其反，需结合数据分布考虑
- 连接顺序不一定按书写顺序，优化器会自动选择最佳顺序
七、EXPLAIN 执行计划优化路线图
- 检查 type 列是否达到 range 级别以上
- 确认 possible_keys 和 key 是否合理
- 分析 key_len 是否充分利用索引
- 检查 rows 估算值是否过大
- 查看 Extra 列是否有警告信息
- 验证 filtered 百分比是否过低
- 对比优化前后的执行计划差异
创作不易，欢迎打赏，你的鼓励将是我创作的最大动力。
