MySQL优化查询速度，explain分析执行计划

前言

SQL查得慢，不知道为什么慢。用EXPLAIN看MySQL怎么执行这条SQL，就能找到问题在哪。

一、EXPLAIN怎么用

EXPLAIN SELECT * FROM users WHERE name = '张三';

输出很多列，重点看这几个：id、select_type、table、type、key、rows、Extra。

二、重点列详解

1. type（访问类型）

最重要的一列，看SQL查得快不快。

从好到差：

type	说明	速度
system	表只有一行	最快
const	主键或唯一索引查一次	很快
eq_ref	联表时主键关联	很快
ref	非唯一索引查	快
range	索引范围扫描	中
index	扫整个索引树	慢
ALL	全表扫描	最慢

目标： 至少达到range，最好是ref以上。

如果type是ALL，就是全表扫描，必须优化。

2. key（实际用到的索引）

key: NULL

说明没用索引。

key: idx_name

说明用到了idx_name索引。

3. rows（扫描行数）

MySQL预估要扫描多少行。

rows越大，越慢。

4. Extra（额外信息）

常见的：

• Using where：WHERE过滤
• Using index：覆盖索引，不用回表，好
• Using filesort：需要额外排序，不好
• Using temporary：用了临时表，不好

三、常见问题

问题1：type=ALL，全表扫描

原因： 没加索引，或者索引失效。

解决： 给WHERE条件的字段加索引。

问题2：key=NULL，没用索引

原因：

1. 没建索引
2. 索引失效了（函数、类型转换、前导模糊查询等）

解决： 建正确的索引，避免索引失效写法。

问题3：Using filesort

原因： ORDER BY的字段没走索引。

解决： 给ORDER BY的字段加索引。

问题4：Using temporary

原因： GROUP BY或DISTINCT用了临时表。

解决： 给GROUP BY的字段加索引。

四、实战案例

慢SQL

SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10;

EXPLAIN结果：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	orders	ALL	NULL	100000	Using where; Using filesort

问题：

1. type=ALL，全表扫描
2. key=NULL，没用索引
3. rows=100000，扫了10万行
4. Using filesort，还要额外排序

优化

加联合索引：

ALTER TABLE orders ADD INDEX idx_userid_createtime (user_id, create_time);

再EXPLAIN：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	orders	ref	idx_userid_createtime	10	Using where

优化效果：

1. type变成ref
2. 用到了索引
3. rows从10万变成10
4. 没有filesort了

五、常用优化技巧

1. 避免SELECT *

只查需要的列，能用上覆盖索引。

2. WHERE条件加索引

经常查询的字段加索引。

3. 联合索引最左前缀

联合索引 (a,b,c)，查询条件要从最左列开始。

4. 避免索引失效

• 不要对索引字段用函数
• 不要隐式类型转换
• 不要前导模糊查询 like '%xxx'

总结

EXPLAIN看这三点：

1. type：是不是全表扫描
2. key：有没有用到索引
3. rows：扫描了多少行

记住： 慢SQL先EXPLAIN，找到问题再优化。

遇到问题加QQ23979811 协助处理