MySQL执行计划

贪挽懒月

发布于 2021-04-23 14:24:28

1K0

发布于 2021-04-23 14:24:28

文章被收录于专栏：JavaEE

1. 是什么？

它是一个关键字。我们知道我们写的SQL，执行之前要经过优化器的优化，所以我们写的SQL经过SQL优化器之后到底是怎么样的？这就可以通过explain关键字来查看。

2. 能干吗？

可以知道表的读取顺序；
可以知道数据读取操作的操作类型；
可以知道哪些索引被使用；
可以知道表之间的引用；
可以知道每张表有多少行被优化器查询；

3. 怎么用？

语法：explain SQL，explain SQL \G，竖行显示，explain format=json SQL \G，以json形式显示。比如我执行explain select * from tb_emp;，就会出现如下结果：

执行计划

4. 执行计划包含的信息：

如上图，执行计划查出来后包含如下信息：

id：select 查询的序列号，包含一组数字，表示查询中执行select 子句或表的读取顺序。它的值有三种情况，第一种，id相同。比如我执行explain select * from t1,t2,t3 where ……，假如执行结果如下：

id	select_type	table
1	simple	t1
1	simple	t3
1	simple	t2

可以看到t1、t2、t3的id都是1。id相同，表示从上到下执行。即先查t1，再查t3，最后查t2，而并非我们写的SQL的t1、t2、t3的顺序。

第二种，id递增。再比如我执行：

explain select t2.*
        from t2
        where id = ( select id
                     from t1
                     where id = (select t3.id from t3 where ……));

我们看到的顺序应该是t2、t1、t3，执行计划是：

id	select_type	table
1	primary	t2
2	subquery	t1
3	subquery	t3

如果有子查询，id会递增，id越大的优先级越高，越先执行。比如上面顺序就应该是t3、t1、t2。

第三种，有些id相同，有些不同。比如我再执行：

explain select t2.* 
from 
    (select t3.id from t3 where ……) temp1, t2 
where temp1.id = t2.id;

id	select_type	table
1	primary	<derived2>
1	primary	t2
2	derived	t3

首先说一下derived是衍生的意思，就是临时表，即上面SQL语句中的temp1。还是id越大的越先执行，所以还是t3最先执行，然后id相同的从上到下执行，所以接下来是derived2，其实就是temp1，最后才是t2。

select_type：常见的值有六个，主要是用来区别查询类型，是普通查询、联合查询还是子查询等。

select_type	含义
simple	简单查询，不包含子查询、union那些
primary	语句中若包含任何复杂的子查询，最外层则被标记为primary
subquery	子查询
derived	临时表
union	语句中出现了union，那么union后面那个select就被标记为union
union result	从union表中获取结果的select

table：这个是就不用多说了，表名
partitions：匹配到的分区信息
type:显示查询使用了何种类型，从最好到最差的排序依次是：

type	含义
system(最优)	表只有一行记录，所以用户建的表几乎不会出现这个，因为没有哪个表只存一条数据吧。
const	表示通过索引一次就找到了。const用于比较primary key或者union索引，比如where条件后面跟的是主键，那就是const。
eq_ref	唯一索引扫描，对于每个索引键，表中只有一条记录匹配。比如员工表tb_emp有一个字段deptId，表示部门的id，与部门表tb_dept的主键id建立了外键索引，并且tb_emp表中的每一条记录的deptId，在tb_dept表中只能找到一条记录，这种情况就是eq_ref，见下图[eq_ref]
ref	非唯一性索引扫描，返回某个值对应的所有行。比如一个部门有多个员工，用一个部门id去查，就可以查出多个员工，这就是ref。
range	表示用索引在某一个范围搜索，用between或者in，比如，explain select * from tb_emp where id between 1 and 4;
index	全索引扫描，all是遍历全表，index是遍历索引树，所以比all快。explain select id from tb_emp;就是index
all(最差)	表示全表扫描，数据量百万以上必须优化

eq_ref

possible_keys：本次查询可能用到的索引，可能是null，可能是一个，也可能是多个。
key：实际用到的索引。如果是null，则表示没用到索引。如果出现了索引覆盖，那就会出现possible_keys为空而key不为空的情况。索引覆盖就是：select后面跟的字段和我复合索引字段的个数和顺序都一样，如下图：

复合索引

key_len：表示索引中使用的字节数，可以通过这个知道查询中使用的索引长度，这个长度是最大的可能长度，而不是实际长度。长度越短越好。
ref：显示索引的哪一列被使用了，值的格式是：database.table.column，如果是一个常量，那么ref就是const。比如：

ref

rows：根据表统计信息及索引使用情况，大致估算出找到所需记录需要读取的行数。
fltered：按表条件过滤的行百分比
extra：包含了一些十分重要但又不适合在其他列显示的信息。包含以下内容：

extra	含义
using filesort	说明MySQL会对数据使用一个外部的索引排序，而不是用表内的索引顺序进行读取，这种文件内排序是很费时的。比如我建了一个复合索引idx_col1_col2_col3，执行select col1 from t1 where col1 = 'a' order by col3，我们建的复合索引是三个列，而这条sql中只使用了两个，col2没了，看它的执行计划就会发现，有using filesort。假如order by col2, col3，那就不会有。
using temporary	MySQL在对查询结果排序时新建了一个临时表处理数据，常见于order by和group by，这种情况性能更差。比如有张表我新建了索引idx_col1_col2，查询select col1 from t1 where col1 in('a', 'b') group by col2，这个就会出现using temporary，但是如果group by col1,col2，那就没问题了。
using index	表示相应的select操作使用了覆盖索引，避免访问了表的数据行，效率不错。比如刚才的tb_emp表，我建立了idx_id_deptId索引，然后我执行select id, deptId from tb_emp，就是using index。
using where	表明使用了where过滤条件
using join buffer	使用了连接缓存
impossible where	where后面的条件总是false，比如where 1 = 2
select tables optimized away	很少出现
distinct	很少出现