今天偶然看到一篇关于SQL执行计划的文章,刚好记录总结一下
数据库版本:MySQL 5.7.28
数据库测试表信息:
create table a_table
(
id int auto_increment
primary key,
create_time bigint null,
name varchar(10) null
);
create index a_table_name_index
on a_table (name);
create table b_table
(
id int not null
primary key,
create_time bigint null,
name varchar(10) null
);
create table c_table
(
id int auto_increment
primary key,
create_time bigint null
);
数据库测试数据:
INSERT INTO pmis.a_table (id, create_time, name) VALUES (1, 122222, 'a');
INSERT INTO pmis.a_table (id, create_time, name) VALUES (2, 23, 'b');
INSERT INTO pmis.a_table (id, create_time, name) VALUES (3, 44221, null);
INSERT INTO pmis.b_table (id, create_time, name) VALUES (1, 100000, 'bb');
INSERT INTO pmis.b_table (id, create_time, name) VALUES (4, 222, 'bc');
1、SQL执行计划Explain是什么
Explain 与SQL语句一起执行可以看到该SQL语句执行的相关信息,并可以根据执行计划分析出表连接、引用索引和查询类型等情况。
2、Explain有哪些信息
Explain 执行计划包含字段信息如下:分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。
3、Explain执行计划字段
3.1 id
表示查询中执行select子句或者操作表的顺序,id
的值越大,代表优先级越高,越先执行。
id会有三种情况:
id相同,执行优先级相同,执行顺序由上至下,具体顺序有优化器决定。
id不相同,如在SQL中存在子查询,那么id会递增,id越大优先级越高,越先被执行。
以上两种情况都存在,依照id越大优先级越高,相同id顺序执行,具体看优化器决定。
3.2 select_type
显示select查询类型,主要区别各种复杂查询,比如:普通查询、联合查询、子查询等。
3.2.1 SIMPLE
表示最简单的 select 查询语句,也就是在查询中不包含子查询或者 union
交并差集等操作。
3.2.2 PRIMARY
当查询语句中包含任何复杂的子部分,最外层查询则被标记为PRIMARY
。
3.2.3 UNION
如果UNION后面出现select语句则会被标记为UNION
EXPLAIN
select *
from a_table
union all
select *
from b_table
union all
select *
from c_table
3.2.4 DEPENDENT UNION
这个类型出现在union和union all 形成的集合查询中,这里的dependent表示union或者union all 查询受外部影响。
EXPLAIN
select *
from a_table
where id in (
select id
from b_table
union all
select id
from c_table
)
3.2.5 UNION RESULT
UNION的结果,id值通常为NULL
EXPLAIN
select *
from a_table where name>1
union
select *
from b_table where create_time>0
3.2.6 SUBQUERY
子查询中首个SELECT(如果有多个子查询存在)
EXPLAIN
select *
from a_table
where id = (select MAX(id) from a_table)
3.2.7 DEPENDENT SUBQUERY
子查询中首个select,受到外部影响(如果有多个子查询存在)
EXPLAIN
select *
from a_table
where id = (
select MAX(id) from b_table
where b_table.name = a_table.name
)
3.2.8 DERIVED
被驱动的子查询(from的子查询)
EXPLAIN
select id
from (
select *
from a_table
union
select *
from b_table
) as a
3.3 table
查询的表名,并不一定是真实存在的表,有别名显示别名,也可能为临时表,例如上边的DERIVED
、 <union1,4>
等。
3.4 partitions
查询时匹配到的分区信息,对于非分区表值为NULL
,当查询的是分区表时,partitions
显示分区表命中的分区情况。
3.5 type
查询使用了何种类型,它在 SQL
优化中是一个非常重要的指标,以下性能从好到坏依次是:system
> const
> eq_ref
> ref
> ref_or_null
> index_merge
> unique_subquery
> index_subquery
> range
> index
> ALL
3.5.1
system
当表仅有一行记录时(系统表),数据量很少,往往不需要进行磁盘IO,速度非常快。
3.5.2 const
表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
EXPLAIN
select *
from a_table
where id = 1
3.5.3
eq_ref
关联查询时命中主键primary key
或者 unique key
索引, type
就是 eq_ref
。
EXPLAIN
select *
from a_table , b_table
where a_table.id = b_table.id
3.5.4
ref
区别于eq_ref
,ref
表示使用非唯一性索引,会找到很多个符合条件的行。
EXPLAIN
select a_table.id
from a_table
where a_table.name = 'a'
3.5.5
ref_or_null
这种连接类型类似于 ref,区别在于 MySQL
会额外搜索包含NULL
值的行。
EXPLAIN
select a_table.id
from a_table
where a_table.name = 'a' OR a_table.name IS NULL
3.5.6
index_merge
使用了索引合并优化方法,查询使用了两个以上的索引。
EXPLAIN
select *
from a_table
where a_table.id >1 and a_table.name = 'a'
3.5.7
unique_subquery
替换下面的 IN
子查询,子查询返回不重复的集合。(ps:这个我没能调出来)
value IN (SELECT primary_key FROM single_table WHERE some_expr)
3.5.8
index_subquery
区别于unique_subquery
,用于非唯一索引,可以返回重复值。(ps:这个我没能调出来)
value IN (SELECT key_column FROM single_table WHERE some_expr)
3.5.9
range
使用索引选择行,仅检索给定范围内的行。简单点说就是针对一个有索引的字段,给定范围检索数据。在where
语句中使用 bettween...and
、<
、>
、<=
、in
等条件查询 type
都是 range
。
explain
select *
from a_table
where id >1
3.5.10
index
Index
与ALL
其实都是读全表,区别在于index
是遍历索引树读取,而ALL
是从硬盘中读取。
explain
select id
from a_table
3.5.11
ALL
将遍历全表以找到匹配的行,性能最差。
explain
select *
from a_table
3.6 possible_keys
表示在MySQL
中通过哪些索引,能让我们在表中找到想要的记录,一旦查询涉及到的某个字段上存在索引,则索引将被列出,但这个索引并不定一会是最终查询数据时所被用到的索引。
3.7 key
区别于possible_keys
,key是查询中实际使用到的索引,若没有使用索引,显示为NULL
。
3.8 key_len
表示查询用到的实际使用索引长度(字节数),原则上长度越短越好 。
-
单列索引,那么需要将整个索引长度算进去;
-
多列索引,不是所有列都能用到,需要计算查询中实际用到的列。
-
注意:
key_len
只计算where
条件中用到的索引长度,而排序和分组即便是用到了索引,也不会计算到key_len
中。
3.9 ref
显示使用哪个列或常数与 key一起从表中选择行。常见的有:const
,func
,null
,字段名。
-
当使用常量等值查询,显示
const
, -
当关联查询时,会显示相应关联表的
关联字段
-
如果查询条件使用了
表达式
、函数
,或者条件列发生内部隐式转换,可能显示为func
-
其他情况
null
3.10 rows
显示 MySQL 认为它执行查询时必须检查的行数。这是评估SQL
性能的一个比较重要的数据,mysql
需要扫描的行数,很直观的显示 SQL
性能的好坏,一般情况下 rows
值越小越好。
3.11 filtered
过滤器记录。filtered
这个是一个百分比的值,表里符合条件的记录数的百分比。简单点说,这个字段表示存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例。(ps:在MySQL.5.7
版本以前想要显示filtered
需要使用explain extended
命令。MySQL.5.7
后,默认explain
直接显示partitions
和filtered
的信息。)
3.12 Extra
该列包含 MySQL 解决查询的详细信息。
3.12.1 Using index
只从索引中查询信息,查询速度会非常快,SQL优化中理想的状态。;
explain
select id,name
from a_table
3.12.2 Using where
查询时未找到可用的索引,进而通过where
条件过滤获取所需数据,但要注意的是并不是所有带where
语句的查询都会显示Using where
。
explain
select *
from a_table
where id >1
3.12.3 Using temporary
表示查询后结果需要使用临时表来存储,一般在排序或者分组查询时用到。
explain
select *
from a_table
where id >1
union
select *
from b_table
where id >1
3.12.4 Using filesort
MySQL 需要额外的一次传递,以找出如何按排序顺序检索行。
explain
select id
from a_table
order by create_time
3.12.5 Using join buffer
联表查询的时候,未使用索引关联查询,需要有一个连接缓冲区来存储中间结果。
explain
select *
from a_table,b_table
3.12.6 Impossible where
表示在我们用不太正确的where
语句,导致没有符合条件的行。
explain
select *
from a_table
where 1 =2
3.12.7 No tables used
查询语句中没有FROM
子句,或者有 FROM DUAL
子句。
explain
select now()
Extra 列的信息非常多,就不一一举例了,详见 MySQL官方文档
4.总结
系统的整理了MySQL执行计划内的知识点,能够帮助我们了解Explain
执行计划各个列的含义,通过这些含义我们就能发现SQL中很多慢查询的’坏味道‘,从而优化我们对数据库的使用,使我们在开发中更加优秀。