【Mysql】执行计划的分析

执行计划你可以理解成：MySQL 在真正执行 SQL 前，先给这条 SQL 制定的一份“执行路线图”。

比如一条 SQL：

select*fromuserwhereage=18orderbycreate_time;

MySQL 不会直接傻傻执行，而是会先想：

“我要不要用索引？”
“用哪个索引？”
“先查哪张表？”
“预计要扫描多少行？”
“排序能不能用索引完成？”
“需不需要临时表？”

这些信息就会体现在EXPLAIN的结果里。

一、为什么要看执行计划？

优化 SQL 的第一步，就是先知道这条 SQL到底是怎么执行的。

比如你以为 SQL 走了索引：

select*fromuserwherename='张三';

但通过执行计划发现：

type = ALL key = NULL rows = 1000000

这就说明它根本没走索引，而是在全表扫描 100 万行。

所以执行计划的作用就是帮你判断：

1. 有没有走索引；
2. 走的是哪个索引；
3. 扫描的数据量大不大；
4. 有没有全表扫描；
5. 有没有文件排序；
6. 有没有临时表；
7. 多表查询时，表的执行顺序是什么。

二、执行计划怎么看？

一般这样使用：

EXPLAINselect*fromuserwhereage=18;

MySQL 会返回一张表，这张表通常有 12 列。

重点不需要每一列都背得特别死，面试和实际优化时，重点看这几个：

id select_type table type possible_keys key key_len rows Extra

其中最重要的是：

type、key、rows、Extra

三、核心字段解释

1. id：SQL 的执行顺序

id表示 SELECT 的执行顺序。

规则：

id 相同：从上往下执行 id 不同：id 越大，越先执行

比如：

select*fromuserwheredept_id=(selectidfromdeptwherename='技术部');

这里有外层查询和子查询。

执行计划里可能会出现两个id：

id = 1 外层 user 查询 id = 2 子查询 dept 查询

因为id = 2更大，所以子查询先执行。

你可以理解为：

MySQL 先查出技术部的 id，再拿这个 id 去 user 表里查员工。

2. select_type：查询类型

select_type表示当前这一行对应的 SELECT 是什么类型。

常见的有：

SIMPLE

简单查询，不包含子查询和 UNION。

select*fromuserwhereid=1;

执行计划中：

select_type = SIMPLE

PRIMARY

如果 SQL 中包含子查询，那么最外层的 SELECT 就是PRIMARY。

select*fromuserwheredept_id=(selectidfromdeptwherename='技术部');

外层这个：

select*fromuser...

就是：

select_type = PRIMARY

SUBQUERY

子查询中的 SELECT。

selectidfromdeptwherename='技术部'

这一部分就是：

select_type = SUBQUERY

DERIVED

FROM 后面的子查询，也叫派生表。

select*from(select*fromuserwhereage>18)t;

这个子查询结果会被当成一张临时表t，所以它是：

select_type = DERIVED

3. table：正在访问哪张表

这个字段表示当前这一行执行计划访问的是哪张表。

例如：

select*fromuserujoindept donu.dept_id=d.id;

执行计划中可能有两行：

table = u table = d

表示 MySQL 需要分别访问user表和dept表。

4. type：访问类型，最重要字段之一

type表示 MySQL 是用什么方式访问表的。

它非常重要，因为它直接反映 SQL 的性能。

从好到差大致是：

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

实际看执行计划时，重点记住这些就够了：

const 很好 eq_ref 很好 ref 较好 range 可以接受 index 一般 ALL 较差

四、type 常见值解释

1. system

system是最特殊的一种，表中只有一行数据，直接返回。

这个很少见，了解即可。

2. const

const表示通过主键索引或唯一索引，一次就能定位到一条数据。

例如：

select*fromuserwhereid=1;

如果id是主键，那么执行计划可能是：

type = const key = PRIMARY

因为主键唯一，id = 1最多只能查出一条记录。

所以 MySQL 认为这张表可以当成一个常量来处理。

3. eq_ref

eq_ref常见于多表 JOIN。

它表示：前一张表查出一行数据后，拿这一行的数据去当前表中匹配，当前表最多只匹配一行。

例如：

select*fromorder_info ojoinuseruono.user_id=u.id;

如果u.id是主键，那么对于订单表中的每一条订单记录，都只能匹配到一个用户。

所以访问user表时可能是：

type = eq_ref

你可以理解为：

每一条订单只对应一个用户。

这是非常好的 JOIN 方式。

4. ref

ref表示使用了普通索引，但可能匹配多行。

例如：

select*fromuserwhereage=18;

如果age上有普通索引，那么执行计划可能是：

type = ref key = idx_age

因为年龄为 18 的用户可能有很多个，所以不是唯一匹配。

它比全表扫描好很多，但不如主键或唯一索引查询。

5. range

range表示范围查询。

例如：

select*fromuserwhereage>18;

或者：

select*fromuserwhereagebetween18and30;

如果age有索引，执行计划可能是：

type = range key = idx_age

它表示 MySQL 会在索引树上找一个范围，而不是扫描全表。

6. index

index表示扫描整棵索引树。

例如：

selectidfromuser;

如果id是索引列，MySQL 可能只扫描索引，不扫描整张表。

执行计划可能是：

type = index

它和ALL有点像，都是“全量扫描”。

区别是：

index：扫描整棵索引树 ALL：扫描整张表

一般来说，索引比表小，所以index通常比ALL快。

但是它依然不是特别理想，因为还是扫描了很多数据。

7. ALL

ALL表示全表扫描。

例如：

select*fromuserwherename='张三';

如果name没有索引，就可能是：

type = ALL key = NULL

这表示 MySQL 要从第一行扫到最后一行。

如果表数据很大，性能就会很差。

优化 SQL 时，一般要重点关注：

type = ALL

五、possible_keys 和 key

这两个字段经常一起看。

possible_keys：可能用到的索引

表示 MySQL 觉得这条 SQL可能可以使用哪些索引。

比如：

possible_keys = idx_name, idx_age

说明优化器认为这两个索引都有可能用。

key：实际使用的索引

表示 MySQL 最终真正选择了哪个索引。

例如：

possible_keys = idx_name, idx_age key = idx_age

说明可能用idx_name或idx_age，但最终选择了idx_age。

如果：

key = NULL

说明没有使用索引。

这是一个非常危险的信号，尤其是大表查询时。

六、key_len：索引使用长度

key_len表示 MySQL 实际使用了索引的多少字节。

它可以用来判断联合索引到底用了几个字段。

例如有联合索引：

indexidx_name_age(name,age)

如果 SQL 是：

select*fromuserwherename='张三';

可能只用到了联合索引的name部分。

如果 SQL 是：

select*fromuserwherename='张三'andage=18;

可能用到了name + age两个部分。

key_len越长，通常说明使用的索引字段越多。

不过面试里一般不用你手算字节数，知道它能判断索引使用程度即可。

七、rows：预计扫描行数

rows表示 MySQL 预计为了找到结果，需要扫描多少行。

例如：

rows = 10

说明预计扫描 10 行。

rows = 100000

说明预计扫描 10 万行。

这个值越小越好。

不过注意，它是 MySQL 估算的，不一定百分百准确。

优化 SQL 时，通常希望：

rows 越小越好

如果你看到：

type = ALL rows = 5000000

那基本说明这条 SQL 很危险，可能会扫 500 万行。

八、filtered：过滤后剩余比例

filtered表示经过条件过滤后，预计剩下多少百分比的数据。

例如：

rows = 10000 filtered = 10

意思是 MySQL 预计扫描 10000 行，然后经过 WHERE 条件过滤后，保留大约 10%。

也就是最终大概剩下：

10000 * 10% = 1000 行

这个字段一般辅助看，不是最核心。

九、Extra：额外信息，非常重要

Extra表示 MySQL 执行 SQL 时的一些额外操作。

它非常关键，因为很多性能问题都能从这里看出来。

1. Using index

这是比较好的情况。

表示使用了覆盖索引，不需要回表。

例如有索引：

indexidx_name_age(name,age)

执行：

selectname,agefromuserwherename='张三';

因为查询的字段name、age都在索引里面，所以 MySQL 直接从索引中拿数据，不需要再回到主键索引找完整记录。

执行计划中可能出现：

Extra = Using index

这通常是好事。

2. Using where

表示 MySQL 需要使用 WHERE 条件进行过滤。

例如：

select*fromuserwhereage=18;

执行计划可能出现：

Extra = Using where

它不一定是坏事。

但是如果同时出现：

type = ALL Extra = Using where

那就说明：

MySQL 正在全表扫描，然后一行一行用 WHERE 条件过滤。

这种情况性能可能较差。

3. Using filesort

这个要重点注意。

Using filesort表示 MySQL 无法利用索引完成排序，需要额外排序。

例如：

select*fromuserwhereage=18orderbycreate_time;

如果没有合适的索引支持order by create_time，就可能出现：

Extra = Using filesort

注意，filesort不一定真的在磁盘文件中排序，它表示 MySQL 使用了额外的排序算法。

但是它通常意味着排序成本较高。

优化方向通常是给排序字段建立合适索引，比如：

indexidx_age_create_time(age,create_time)

这样可以先按age定位，再按create_time的索引顺序返回。

4. Using temporary

这个也要重点注意。

Using temporary表示 MySQL 需要创建临时表保存中间结果。

常见于：

groupbyorderbydistinctunion

例如：

selectage,count(*)fromusergroupbyage;

如果没有合适索引，可能会出现：

Extra = Using temporary

这说明 MySQL 需要先把中间结果放到临时表里，再继续处理。

如果数据量大，性能可能比较差。

5. Using index condition

表示使用了索引条件下推，也叫 ICP，Index Condition Pushdown。

简单理解：

原本一些过滤条件要回表后才能判断，现在可以先在索引层面判断一部分，减少回表次数。

例如有联合索引：

indexidx_name_age(name,age)

SQL：

select*fromuserwherenamelike'张%'andage=18;

MySQL 可以在扫描索引时，尽量先判断索引中已有的条件，减少回表。

执行计划中可能出现：

Extra = Using index condition

这通常是一个优化行为。

6. Using join buffer

这个常见于 JOIN 查询。

例如：

select*fromuserujoinorder_info oonu.id=o.user_id;

如果被驱动表的连接字段没有索引，MySQL 可能会使用 join buffer。

执行计划中可能出现：

Using join buffer

它的意思是：

MySQL 先把一部分驱动表数据放到 join buffer 里，然后再和被驱动表做匹配。

这通常说明 JOIN 的被驱动表没有很好地使用索引。

优化方向一般是：

给被驱动表的关联字段加索引

比如：

order_info.user_id

应该建立索引。

十、用一个例子完整理解

假设有一张用户表：

createtableuser(idintprimarykey,namevarchar(50),ageint,cityvarchar(50),create_timedatetime,indexidx_age(age),indexidx_name_age(name,age));

现在执行：

EXPLAINselect*fromuserwhereage=18;

可能结果：

id: 1 select_type: SIMPLE table: user type: ref possible_keys: idx_age key: idx_age rows: 100 Extra: Using where

解释：

id = 1 表示这是一个简单查询。 select_type = SIMPLE 表示没有子查询，也没有 UNION。 table = user 表示查询 user 表。 type = ref 表示使用普通索引进行等值查询。 possible_keys = idx_age 表示可能使用 idx_age 索引。 key = idx_age 表示实际使用了 idx_age 索引。 rows = 100 表示预计扫描 100 行。 Extra = Using where 表示还需要根据 where 条件进行过滤。

这条 SQL 基本还可以。

再看一个不好的例子：

EXPLAINselect*fromuserwherecity='北京';

如果city没有索引，可能是：

type: ALL possible_keys: NULL key: NULL rows: 1000000 Extra: Using where

解释：

type = ALL 表示全表扫描。 key = NULL 表示没有使用索引。 rows = 1000000 表示预计扫描 100 万行。 Extra = Using where 表示扫描之后再用 where 过滤。

这就说明性能可能很差。

优化方式：

给 city 字段加索引createindexidx_cityonuser(city);

十一、看执行计划的重点顺序

你以后分析执行计划，可以按照这个顺序看：

第一步：看 type

重点判断有没有全表扫描。

比较好的：

const、eq_ref、ref、range

需要警惕的：

index、ALL

尤其是：

ALL

第二步：看 key

判断是否真的用到了索引。

如果：

key = NULL

说明没有走索引。

如果用了索引，也要判断它用的是不是你预期的索引。

第三步：看 rows

判断扫描行数大不大。

rows 越大，风险越高

如果扫描几十行、几百行，一般没太大问题。

如果扫描几十万、几百万行，就需要重点优化。

第四步：看 Extra

重点关注这几个：

Using filesort Using temporary Using join buffer

这几个通常说明 SQL 可能还有优化空间。

比较好的：

Using index

表示覆盖索引，不需要回表。

十二、简单记忆口诀

你可以这样记：

type 看访问方式； key 看是否用索引； rows 看扫描多少行； Extra 看有没有额外代价。

再简单一点：

先看 type，再看 key； rows 越小越好； Extra 避免 filesort 和 temporary。

十三、你这段内容中有一个小点要修正

你写的这句：

Using where：一般在没有使用到索引的时候会出现。

这句话不完全准确。

更准确地说：

Using where表示 MySQL 使用 WHERE 条件进行了过滤，它既可能出现在没用索引时，也可能出现在用了索引之后还需要进一步过滤时。

比如：

select*fromuserwhereage=18andcity='北京';

如果只有age有索引，MySQL 先通过age索引找到一批数据，然后还要判断city = '北京'。

这时即使用了索引，也可能出现：

Extra = Using where

所以Using where本身不一定坏，要结合type、key、rows一起看。

十四、总结

执行计划就是 MySQL 对 SQL 的执行方案说明。

你真正需要重点掌握的是：

type：访问方式，判断性能好坏 key：实际使用的索引 rows：预计扫描行数 Extra：额外执行信息

其中最需要警惕的是：

type = ALL key = NULL rows 很大 Extra = Using filesort Extra = Using temporary Extra = Using join buffer

比较好的情况是：

type = const / eq_ref / ref / range key 有值 rows 较小 Extra = Using index

一句话总结：

执行计划就是用来判断 SQL 有没有正确使用索引、扫描数据量大不大、有没有额外排序或临时表，从而帮助我们定位 SQL 性能问题。