MySQL的explain analyze命令有什么用

原创

半月无霜

修改于 2025-12-26 14:28:14

2420

文章被收录于专栏：半月无霜半月无霜

一、前言

在以前，我们分析一段慢SQL往往需要使用explain命令，输出的结果仅仅为统计信息的预估结果，走了什么索引，影响的行数是多少。

那么现在有一个explain analyze命令，它由MySQL 8.0.18这个版本引入的，为传统explain的增强版本。

与普通的explain不同，explain analyze会真实的执行查询，并报告每个操作的实际执行时间和实际处理行数，让性能分析更加准确可靠。

所以，如果你的MySQL版本符合的话，请使用explain analyze来分析慢SQL，相信我这十分有用。

那么下面，将会介绍explain analyze的使用。

二、explain analyze 输出解析

1）典型输出示例

 explain analyze 
 SELECT u.name, o.order_no 
 FROM users u 
 JOIN orders o ON u.id = o.user_id 
 WHERE u.age > 25;

输出结果：

 -> Nested Loop Inner Join  (actual time=0.098..45.678 rows=1234 loops=1)
     -> Index Scan on u using idx_age  (actual time=0.045..2.345 rows=5000 loops=1)
     -> Index Lookup on o using idx_user_id (user_id=u.id)  (actual time=0.032..0.089 rows=2 loops=5000)

上面的情况分为两部分，那么下面就好好介绍下里面有什么

2）核心字段说明

操作类型
- Table Scan：全表扫描（性能最差）
- Index Scan：索引扫描
- Index Range Scan：索引范围扫描
- Nested Loop Join：嵌套循环连接
- Hash Join：哈希连接
- Filter：过滤操作
执行指标
- actual time：实际执行时间（单位：毫秒）
  - 格式：开始时间..结束时间，总时间 = 结束时间 - 开始时间
- rows：实际处理的行数
- loops：操作执行的次数（重要！用于计算总成本）

总成本计算

 总时间 = actual time × loops
 总行数 = rows × loops

上面示例的成本计算

 # Nested Loop Inner Join
 总时间 = 45.678 × 1 = 45.678 ms
 总行数 = 1234 × 1 = 1234
 
 # Index Scan on u using idx_age
 总时间 = 2.345 × 1 = 2.345 ms
 总行数 = 5000 × 1 = 5000
 
 # Index Lookup on o using idx_user_id
 总时间 = 0.089 × 5000 = 445 ms
 总行数 = 2 × 5000 = 10000

三、异常诊断与 SQL 调优

1）全表扫描问题

特征表现：

 -> Table scan on large_table  (actual time=0.023..5678.901 rows=1000000 loops=1)

原因分析：

缺少合适索引
使用了函数或计算导致索引失效
数据类型不匹配

调优方案：

 -- 添加合适索引
 CREATE INDEX idx_column ON large_table(column);
 
 -- 优化查询条件（避免函数操作）
 -- ❌ WHERE YEAR(create_time) = 2023
 -- ✅ WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'

2）嵌套循环连接性能问题

特征表现：

 -> Nested Loop Inner Join  (actual time=0.100..678.901 rows=1000 loops=1)
     -> Index Scan on table1  (actual time=0.050..2.100 rows=1000 loops=1)
     -> Index Lookup on table2  (actual time=0.080..0.120 rows=1 loops=1000)

问题：外表 rows=1000，内表 loops=1000，总查找次数 = 1000×1 = 1000次

调优方案：

 -- 1. 确保内表有足够的索引
 CREATE INDEX idx_fk ON table2(foreign_key);
 
 -- 2. 考虑改用 Hash Join（当连接行数较大时）
 SELECT /*+ HASH_JOIN(u, o) */ * FROM users u JOIN orders o ON u.id = o.user_id;
 
 -- 3. 或者改写查询使用子查询
 explain analyze 
 SELECT u.name, o.order_count
 FROM users u 
 LEFT JOIN (
     SELECT user_id, COUNT(*) as order_count 
     FROM orders 
     GROUP BY user_id
 ) o ON u.id = o.user_id;

3）排序性能问题

特征表现：

 -> Sort: create_time DESC  (actual time=123.456..234.567 rows=50000 loops=1)
     -> Using temporary; Using filesort

原因分析：

缺少覆盖排序字段的索引
排序数据量过大
无法利用索引避免排序

调优方案：

 -- 创建覆盖索引
 CREATE INDEX idx_covering ON orders(user_id, create_time DESC);
 
 -- 减少排序数据量
 explain analyze 
 SELECT * FROM orders 
 WHERE user_id = 12345  -- 先过滤再排序
 ORDER BY create_time DESC;
 
 -- 避免大结果集排序
 explain analyze 
 SELECT * FROM orders 
 ORDER BY create_time DESC 
 LIMIT 100;  -- 限制返回行数

4）索引失效问题

特征表现：

 -> Filter: (column LIKE '%value%')  (actual time=0.123..789.123 rows=5000 loops=1)
     -> Index Scan on idx_column  -- 索引未充分利用

调优方案：

 -- 前缀匹配（可以使用索引）
 WHERE column LIKE 'value%';
 
 -- 全文检索（需要全文索引）
 CREATE FULLTEXT INDEX idx_column ON table(column);
 WHERE MATCH(column) AGAINST('value');
 
 -- 避免类型转换
 -- ❌ WHERE phone = 12345678901  (phone是VARCHAR)
 -- ✅ WHERE phone = '12345678901'

5）IN 转 JOIN 优化

特征表现：

 -> Nested Loop  (actual time=0.123..987.654 rows=1000 loops=1)
     -> Index Lookup using subquery  (actual time=0.080..0.120 rows=1000 loops=1)

调优方案：

 -- 原始查询（性能较差）
 explain analyze 
 SELECT * FROM users 
 WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
 
 -- 优化后（改写为 JOIN）
 explain analyze 
 SELECT u.* FROM users u 
 JOIN orders o ON u.id = o.user_id
 WHERE o.amount > 100;
 
 -- 或使用 EXISTS（当子查询返回行数较少时）
 explain analyze 
 SELECT * FROM users u 
 WHERE EXISTS (
     SELECT 1 FROM orders o 
     WHERE o.user_id = u.id AND o.amount > 100
 );

6）聚合查询优化

特征表现：

 -> Stream Aggregate: count(o.id)  (actual time=123.456..890.123 rows=50000 loops=1)
     -> Nested Loop Left Join  (actual time=0.123..678.901 rows=100000 loops=1)

调优方案：

 -- 原始查询（先 JOIN 再聚合）
 explain analyze 
 SELECT u.name, COUNT(o.id) as order_count
 FROM users u 
 LEFT JOIN orders o ON u.id = o.user_id
 GROUP BY u.id;
 
 -- 优化后（先聚合再 JOIN）
 explain analyze 
 SELECT u.name, COALESCE(o.order_count, 0) as order_count
 FROM users u 
 LEFT JOIN (
     SELECT user_id, COUNT(*) as order_count
     FROM orders 
     GROUP BY user_id
 ) o ON u.id = o.user_id;

四、最后

相信大家在以后遇到慢SQL也能够使用explain analyze进行分析解决了，祝你以后不用再遇上SQL调优。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

腾讯技术创作特训营S16

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

腾讯技术创作特训营S16

登录后参与评论

0 条评论

热度