史上最全的explain稀有type后果及代表的涵义–总结,值得保藏
概述
关于MySQL实行方案的获取,我们可以经过explain办法来查察,explain办法看似简便,实践上包含的内容很多,尤其是输入后果中的type典范列。了解这些不同的典范,关于我们SQL优化至关紧张。今天主要先容一下稀有的type后果及代表的涵义,并且经过同一个SQL语句的功能差别,分析建对索引何等紧张。
explain后果中的type字段代表什么意思?
MySQL的官网表明十分简便,只用了3个单词:毗连典范(the join type)。它形貌了找到所需数据使用的扫描办法。
一、EXPLAIN 语句中type列的值
听从总结
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,即使仅有一条纪录。
举一个例子,内层嵌套(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;
2、查察实行方案
explain select * from t2 where id=1;
如上例,id是PK,毗连局部是常量1。
这类扫描听从极高,前往数据量少,速率十分快。
四、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;
2、查察实行方案
explain select * from t3,t4 where t3.id=t4.id;
如上例,id是主键,该join查询为eq_ref扫描。
这类扫描的速率也特别之快。
五、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;
假如把上例eq_ref案例中的主键索引,改为平凡非唯一(non unique)索引。
就由eq_ref升级为了ref,此时关于前表的每一行(row),后表约莫有多于一行的数据被扫描。
explain select * from t5,t6 where t5.id=t6.id;
当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;
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;
像外表中的between,in,>都是典范的范围(range)查询。
注意:必需是索引,不然不克不及批量”跳过”。
七、index
index典范,必要扫描索引上的全部数据。
explain select count(*) from t7;
如上例,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;
2、查察实行方案
explain select * from t8,t9 where t8.id=t9.id;
假如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方面的内容,感兴致的伙伴可以眷注一下~