概述
对于MySQL执行计划的获取,我们可以通过explain方式来查看,explain方式看似简单,实际上包含的内容很多,尤其是输出结果中的type类型列。理解这些不同的类型,对于我们SQL优化举足轻重。今天主要介绍一下常见的type结果及代表的含义,并且通过同一个SQL语句的性能差异,说明建对索引多么重要。
explain结果中的type字段代表什么意思?
MySQL的官网解释非常简洁,只用了3个单词:连接类型(the join type)。它描述了找到所需数据使用的扫描方式。
一、EXPLAIN 语句中type列的值
![f05222bc41a94e1683b3fcac508de452.png](https://img-blog.csdnimg.cn/img_convert/f05222bc41a94e1683b3fcac508de452.png)
![ad9c2811b7db91c0316e7d1b9cf1b38e.png](https://img-blog.csdnimg.cn/img_convert/ad9c2811b7db91c0316e7d1b9cf1b38e.png)
效率总结
1)依次从好到差:
- system,const,index , range,index_merge,ALL 单独查询
- eq_ref,ref,ref_or_null all 多表join 查询
- index_subquery unique_subquery 子查询
2)index_merge之外,其他的type只可以用到一个索引
二、system
这里我在多个mysql测试过,如果是5.7以上版本的话就不是system了,而是all,即使只有一条记录。
![aca05ee2cd34041a95e91ade3b803701.png](https://img-blog.csdnimg.cn/img_convert/aca05ee2cd34041a95e91ade3b803701.png)
举一个例子,内层嵌套(const)返回了一个临时表,外层嵌套从临时表查询,其扫描类型是system,也不需要走磁盘IO,速度超快。
三、const
const扫描的条件为:
(1)命中主键(primary key)或者唯一(unique)索引;
(2)被连接的部分是一个常量(const)值;
1、数据准备:
create table t2 (id int primary key,name varchar(20)) engine=innodb; insert into t2 values(1,'hwb');insert into t2 values(2,'zhangsan');insert into t2 values(3,'xiaoming');commit;
![78e46eac00405fa776e78dc6f4ccf2af.png](https://img-blog.csdnimg.cn/img_convert/78e46eac00405fa776e78dc6f4ccf2af.png)
2、查看执行计划
explain select * from t2 where id=1;
如上例,id是PK,连接部分是常量1。
![da0ec1fc7e6cf8145961cd70a71f35de.png](https://img-blog.csdnimg.cn/img_convert/da0ec1fc7e6cf8145961cd70a71f35de.png)
这类扫描效率极高,返回数据量少,速度非常快。
四、eq_ref
eq_ref扫描的条件为,对于前表的每一行(row),后表只有一行被扫描。
再细化一点:
(1)join查询;
(2)命中主键(primary key)或者非空唯一(unique not null)索引;
(3)等值连接;
1、数据准备:
create table t3 (id int primary key,name varchar(20))engine=innodb; insert into t3 values(1,'hwb');insert into t3 values(2,'zhangsan');insert into t3 values(3,'xiaoming'); create table t4 (id int primary key,age int)engine=innodb; insert into t4 values(1,18);insert into t4 values(2,20);insert into t4 values(3,30);insert into t4 values(4,40);insert into t4 values(5,50);commit;
![dbd82dc838485aff7c69fde844d2ab72.png](https://img-blog.csdnimg.cn/img_convert/dbd82dc838485aff7c69fde844d2ab72.png)
2、查看执行计划
explain select * from t3,t4 where t3.id=t4.id;
如上例,id是主键,该join查询为eq_ref扫描。
![986ad2b4e40059f3db94019d8ccc8f4c.png](https://img-blog.csdnimg.cn/img_convert/986ad2b4e40059f3db94019d8ccc8f4c.png)
这类扫描的速度也异常之快。
五、ref
1、数据准备
create table t5 (id int ,name varchar(20),index(id))engine=innodb; insert into t5 values(1,'hwb');insert into t5 values(2,'zhangsan');insert into t5 values(3,'xiaoming'); create table t6 (id int,age int,index(id))engine=innodb; insert into t6 values(1,18);insert into t6 values(2,20);insert into t6 values(3,30);insert into t6 values(4,40);insert into t6 values(5,50);commit;
![32ded42767aba3e99d431dc0c6c5b473.png](https://img-blog.csdnimg.cn/img_convert/32ded42767aba3e99d431dc0c6c5b473.png)
如果把上例eq_ref案例中的主键索引,改为普通非唯一(non unique)索引。
就由eq_ref降级为了ref,此时对于前表的每一行(row),后表可能有多于一行的数据被扫描。
explain select * from t5,t6 where t5.id=t6.id;
![788712d75aa78a7185ee55c599b45e19.png](https://img-blog.csdnimg.cn/img_convert/788712d75aa78a7185ee55c599b45e19.png)
当id改为普通非唯一索引后,常量的连接查询,也由const降级为了ref,因为也可能有多于一行的数据被扫描。
ref扫描,可能出现在join里,也可能出现在单表普通索引里,每一次匹配可能有多行数据返回,虽然它比eq_ref要慢,但它仍然是一个很快的join类型。
六、range
range扫描就比较好理解了,它是索引上的范围查询,它会在索引上扫码特定范围内的值。
1、数据准备:
create table t7 (id int primary key,name varchar(20))engine=innodb; insert into user values(1,'hwb');insert into user values(2,'zhangsan');insert into user values(3,'xiaoming');insert into user values(4,'xiaohong');insert into user values(5,'xiaoqiu');commit;
![f6f3c3f1e779ad35341d87507689e658.png](https://img-blog.csdnimg.cn/img_convert/f6f3c3f1e779ad35341d87507689e658.png)
2、查看执行计划
explain select * from t7 where id between 1 and 4;explain select * from t7 where id in(1,2,3);explain select * from t7 where id>3;
![70851f704f3de592d6d6e325f2b46936.png](https://img-blog.csdnimg.cn/img_convert/70851f704f3de592d6d6e325f2b46936.png)
像上面中的between,in,>都是典型的范围(range)查询。
注意:必须是索引,否则不能批量"跳过"。
七、index
index类型,需要扫描索引上的全部数据。
explain select count(*) from t7;
![f44e47973a2fff27cdb6e14bbbd18e82.png](https://img-blog.csdnimg.cn/img_convert/f44e47973a2fff27cdb6e14bbbd18e82.png)
如上例,id是主键,该count查询需要通过扫描索引上的全部数据来计数。
注意:此表为InnoDB引擎,它仅比全表扫描快一点。
八、ALL
1、数据准备:
create table t8 (id int ,name varchar(20))engine=innodb; insert into t8 values(1,'hwb');insert into t8 values(2,'zhangsan');insert into t8 values(3,'xiaoming'); create table t9 (id int,age int)engine=innodb; insert into t9 values(1,18);insert into t9 values(2,20);insert into t9 values(3,30);insert into t9 values(4,40);insert into t9 values(5,50);commit;
![d30af2559eea468678f58592bc44b0bd.png](https://img-blog.csdnimg.cn/img_convert/d30af2559eea468678f58592bc44b0bd.png)
2、查看执行计划
explain select * from t8,t9 where t8.id=t9.id;
![4232e87195d73a3d6efa47b8d0437fc3.png](https://img-blog.csdnimg.cn/img_convert/4232e87195d73a3d6efa47b8d0437fc3.png)
如果id上不建索引,对于前表的每一行(row),后表都要被全表扫描。
今天介绍的实验中,这个相同的join语句出现了三次:
(1)扫描类型为eq_ref,此时id为主键;
(2)扫描类型为ref,此时id为非唯一普通索引;
(3)扫描类型为ALL,全表扫描,此时id上无索引;
有此可见,建立正确的索引,对数据库性能的提升是多么重要。另外,不正确的SQL语句,可能导致全表扫描。
总结
1、explain结果中的type字段,表示(广义)连接类型,它描述了找到所需数据使用的扫描方式;
2、常见的扫描类型有:
system>const>eq_ref>ref>range>index>ALL
其扫描速度由快到慢;
3、各类扫描类型的要点是:
- system最快:不进行磁盘IO
- const:PK或者unique上的等值查询
- eq_ref:PK或者unique上的join查询,等值匹配,对于前表的每一行(row),后表只有一行命中
- ref:非唯一索引,等值匹配,可能有多行命中
- range:索引上的范围扫描,例如:between/in/>
- index:索引上的全集扫描,例如:InnoDB的count
- ALL最慢:全表扫描(full table scan)
4、建立正确的索引(index),非常重要;
5、使用explain了解并优化执行计划,非常重要;
思路比结论重要,希望大家有收获,一定要多动手做实验,不要轻易相信我得出来的结果。
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~
![953a5c828bf207e5c26c2641812788db.gif](https://img-blog.csdnimg.cn/img_convert/953a5c828bf207e5c26c2641812788db.gif)