MySQL

发布日期: 2024-07-17

作者: 小乐

文章字数: 2.1k

阅读时长: 9 分

阅读次数:

MySQL执行计划分析Explain

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、EXPLAIN基础用法

1.1 基本语法

EXPLAIN SELECT * FROM user WHERE id = 1;

-- MySQL 8.0.18+ 支持EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM user WHERE id = 1;

1.2 输出字段说明

字段	含义
id	查询标识符
select_type	查询类型
table	访问的表
partitions	匹配的分区
type	访问类型（重要）
possible_keys	可能使用的索引
key	实际使用的索引
key_len	使用索引的长度
ref	与索引比较的列
rows	扫描的行数（估算）
filtered	过滤后剩余比例
Extra	额外信息（重要）

二、关键字段详解

2.1 id字段

标识SELECT的执行顺序：

EXPLAIN SELECT * FROM user WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 100
);

+----+-------------+---------+
| id | select_type | table   |
+----+-------------+---------+
|  1 | PRIMARY     | user    |
|  2 | SUBQUERY    | orders  |
+----+-------------+---------+

id相同：从上到下执行
id不同：id大的先执行
id有相同有不同：先执行id大的，相同的从上到下

2.2 select_type字段

类型	说明
SIMPLE	简单查询，无子查询或UNION
PRIMARY	最外层查询
SUBQUERY	SELECT中的子查询
DERIVED	FROM中的子查询（派生表）
UNION	UNION中的第二个及后续查询
UNION RESULT	UNION的结果合并
DEPENDENT SUBQUERY	依赖外部查询的子查询
MATERIALIZED	物化子查询（MySQL 5.6+）

2.3 type字段（重点）

性能从好到差排序：

system > const > eq_ref > ref > range > index > ALL

system：表只有一行，是const的特例

EXPLAIN SELECT * FROM mysql.proxies_priv;
-- type: system（系统表通常只有一行）

const：通过主键或唯一索引一次命中

EXPLAIN SELECT * FROM user WHERE id = 1;
-- type: const

eq_ref：JOIN中通过主键或唯一索引关联，对于前表的每一行，后表只有一行匹配

EXPLAIN SELECT * FROM user u 
JOIN orders o ON u.id = o.user_id;
-- type: eq_ref（假设orders.user_id有唯一索引）

ref：使用非唯一索引

EXPLAIN SELECT * FROM user WHERE name = 'Alice';
-- type: ref（name是普通索引）

range：索引范围扫描

EXPLAIN SELECT * FROM user WHERE id BETWEEN 1 AND 100;
EXPLAIN SELECT * FROM user WHERE id IN (1, 2, 3);
-- type: range

index：全索引扫描

EXPLAIN SELECT name FROM user;
-- type: index（覆盖索引，扫描整个索引树）

ALL：全表扫描

EXPLAIN SELECT * FROM user WHERE age > 0;
-- type: ALL（无索引或索引未使用）

优化目标：至少达到range级别，最好达到ref或更高。

2.4 key和key_len

key：实际使用的索引。NULL表示未使用索引。

key_len：索引使用的字节数。可用于判断是否使用了索引的全部列：

-- 联合索引(name, age)，name是VARCHAR(20)
EXPLAIN SELECT * FROM user WHERE name = 'Alice';
-- key_len: 63（name列长度：20*3 + 3字节变长开销）

EXPLAIN SELECT * FROM user WHERE name = 'Alice' AND age = 20;
-- key_len: 68（加上age的INT 4字节 + 1字节NULL标志）

2.5 rows字段

MySQL估算的扫描行数。这个值越小越好。

2.6 Extra字段（重点）

值	含义	是否优化
Using index	覆盖索引，无需回表	优
Using where	使用WHERE过滤	正常
Using index condition	索引条件下推	优
Using filesort	需要额外排序	差，需优化
Using temporary	使用临时表	差，需优化
Using join buffer	使用连接缓存	正常
Impossible WHERE	WHERE条件永远为false	正常
Select tables optimized away	优化器确定只返回一行	优

Using filesort示例与优化：

-- 无索引或索引不满足排序需求
EXPLAIN SELECT * FROM user ORDER BY age LIMIT 10;
-- Extra: Using filesort

-- 优化：添加索引
CREATE INDEX idx_age ON user(age);
-- Extra: Using index

Using temporary示例与优化：

-- GROUP BY + 排序导致临时表
EXPLAIN SELECT status, COUNT(*) FROM user GROUP BY status;
-- Extra: Using temporary; Using filesort

-- 优化：复合索引
CREATE INDEX idx_status ON user(status);
-- Extra: Using index

三、EXPLAIN ANALYZE（MySQL 8.0.18+）

不仅显示计划，还显示实际执行时间：

EXPLAIN ANALYZE SELECT * FROM user WHERE id > 100 LIMIT 10;

输出示例：

-> Limit: 10 row(s)  (cost=11.25..11.35 rows=10) (actual time=0.089..0.095 rows=10 loops=1)
    -> Index range scan on user using PRIMARY, with index condition: (user.id > 100)  (cost=11.25..46.25 rows=350) (actual time=0.087..0.091 rows=10 loops=1)

关键信息：

cost：预估成本
actual time：实际耗时
rows：实际返回/扫描行数
loops：循环次数

四、实战案例分析

4.1 案例一：索引失效分析

EXPLAIN SELECT * FROM user 
WHERE YEAR(create_time) = 2024;

输出：

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

问题诊断：

type=ALL：全表扫描
key=NULL：未使用索引
原因：函数操作导致索引失效

优化方案：

-- 改写为范围查询
SELECT * FROM user 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2025-01-01';

-- EXPLAIN结果
-- type: range
-- key: idx_create_time
-- Extra: Using index condition

4.2 案例二：隐式转换分析

-- phone字段是VARCHAR类型
EXPLAIN SELECT * FROM user WHERE phone = 13800138000;

输出：

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_phone     | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

问题诊断：

possible_keys有idx_phone，但key=NULL
发生隐式类型转换，索引未使用

优化方案：

SELECT * FROM user WHERE phone = '13800138000';

4.3 案例三：ORDER BY优化

EXPLAIN SELECT * FROM user 
WHERE status = 1 
ORDER BY create_time DESC 
LIMIT 10;

输出：

Extra: Using where; Using filesort

优化方案：

-- 添加复合索引
CREATE INDEX idx_status_time ON user(status, create_time);

-- 优化后Extra: Using index

4.4 案例四：JOIN优化

EXPLAIN SELECT u.name, o.amount 
FROM user u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE o.create_time > '2024-01-01';

输出：

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------+
|  1 | SIMPLE      | o     | ALL    | idx_user_time | NULL    | NULL    | NULL            | 500000 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 8       | test.o.user_id  | 1      |             |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------+

问题诊断：

orders表全表扫描（type=ALL）
原因：LEFT JOIN + WHERE条件在右表，导致优化器将LEFT JOIN转为INNER JOIN

优化方案：

-- 改为INNER JOIN
SELECT u.name, o.amount 
FROM user u 
JOIN orders o ON u.id = o.user_id 
WHERE o.create_time > '2024-01-01';

-- 或确保orders表有合适的索引
CREATE INDEX idx_time_user ON orders(create_time, user_id);

五、SHOW WARNINGS

EXPLAIN后执行SHOW WARNINGS可以看到优化器改写后的SQL：

EXPLAIN SELECT * FROM user WHERE id = 1;
SHOW WARNINGS \G;

输出：

Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`user`.`id` AS `id`,...
from `test`.`user`
where (`test`.`user`.`id` = 1)

六、优化流程总结

6.1 EXPLAIN分析步骤

看type：是否达到range及以上，避免ALL
看key：是否使用了预期的索引
看rows：扫描行数是否合理
看Extra：是否存在Using filesort、Using temporary
看key_len：索引是否充分利用

6.2 优化决策树

EXPLAIN分析
    │
    ├── type = ALL?
    │       ├── 是 → 是否必要？
    │       │       ├── 小表 → 可接受
    │       │       └── 大表 → 添加索引/改写SQL
    │       └── 否 → 继续
    │
    ├── key = NULL?（possible_keys有值）
    │       ├── 是 → 索引失效？
    │       │       ├── 函数操作 → 改写SQL
    │       │       ├── 隐式转换 → 统一类型
    │       │       ├── LIKE '%x%' → 改为前缀匹配
    │       │       └── OR条件 → 改用UNION
    │       └── 否 → 继续
    │
    ├── Extra含filesort?
    │       ├── 是 → 添加排序列到索引
    │       └── 否 → 继续
    │
    └── Extra含temporary?
            ├── 是 → 简化GROUP BY/ORDER BY
            └── 否 → 优化完成

七、常用优化技巧速查

问题	EXPLAIN表现	解决方案
全表扫描	type=ALL	添加索引，改写SQL
索引失效	possible_keys有值但key=NULL	避免函数、隐式转换、前导%
文件排序	Extra=Using filesort	索引包含排序列
临时表	Extra=Using temporary	简化GROUP BY，添加索引
回表多	非覆盖索引	添加查询列到索引（覆盖索引）
JOIN性能差	驱动表rows大	确保关联列有索引，小表驱动

八、总结

EXPLAIN是SQL优化的”X光机”。掌握以下要点：

重点关注：type、key、rows、Extra四个字段
优化目标：type至少range，避免ALL；Extra避免filesort和temporary
结合实践：用EXPLAIN ANALYZE验证实际执行时间
持续监控：定期用慢查询日志发现需要优化的SQL

熟练使用EXPLAIN，能大幅提升SQL优化效率，快速定位性能瓶颈。

核心要点

索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型
SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN
事务隔离级别的选择：根据业务需求选择合适的级别
常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。

转载规则

《MySQL执行计划分析Explain》由小乐采用知识共享署名 4.0 国际许可协议进行许可。

接口鉴权和权限校验的区别

详解接口鉴权和权限校验的区别的核心概念与实践方法，结合真实开发场景说明使用注意事项。

2024-07-17 安全

学习笔记安全

SQL 注入原理和防护方法

详解SQL 注入原理和防护方法的核心概念与实践方法，结合真实开发场景说明使用注意事项。

2024-07-16 安全

学习笔记安全

MySQL执行计划分析Explain

一、EXPLAIN基础用法

1.1 基本语法

1.2 输出字段说明

二、关键字段详解

2.1 id字段

2.2 select_type字段

2.3 type字段（重点）

2.4 key和key_len

2.5 rows字段

2.6 Extra字段（重点）

三、EXPLAIN ANALYZE（MySQL 8.0.18+）

四、实战案例分析

4.1 案例一：索引失效分析

4.2 案例二：隐式转换分析

4.3 案例三：ORDER BY优化

4.4 案例四：JOIN优化

五、SHOW WARNINGS

六、优化流程总结

6.1 EXPLAIN分析步骤

6.2 优化决策树

七、常用优化技巧速查

八、总结

核心要点

总结

写作不易，客官能否打赏一杯Cooffee？