本文轉載自[1]，對[1]進行了整理

explain結果中的type字段列表:

?

type字段取值	?說明	SQL示范	掃描條件	建表特點
system	系統表，少量數據，往往不需要進行磁盤IO；	explain select * from mysql.time_zone;	這些數據已經加載到內存里，不需要進行磁盤IO。 ? 這類掃描是速度最快的。	帶主鍵
const	常量連接	explain select * from (select * from user where id=1) tmp;（system+const） ? explain select * from user where id=1;(const）	（1）命中主鍵(primary key)或者唯一(unique)索引； （2）被連接的部分是一個常量(const)值；	帶主鍵
eq_ref	主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描	explain select * from user,user_ex where user.id=user_ex.id;(All+eq_ref） ?	對于前表的每一行(row)，后表只有一行被掃描。 ? 再細化一點： （1）join查詢； （2）命中主鍵(primary key)或者非空唯一(unique not null)索引； （3）等值連接；	含主鍵
ref	非主鍵非唯一索引等值掃描	explain select * from user,user_ex where user.id=user_ex.id;(All+ref） -------------------------------------------- explain select * from user where id=1;	把上例eq_ref案例中的主鍵索引，改為普通非唯一(non unique)索引。 ? ? ? ----------- 當id改為普通非唯一索引后，常量的連接查詢，也由const降級為了ref，因為也可能有多于一行的數據被掃描。 ********* ref掃描，可能出現在join里，也可能出現在單表普通索引里，每一次匹配可能有多行數據返回，雖然它比eq_ref要慢，但它仍然是一個很快的join類型。	無主鍵，含普通索引
range	范圍掃描	explain select * from user where id between 1 and 4; explain select * from user where id in(1,2,3); explain select * from user where id>3;	像上例中的between，in，>都是典型的范圍(range)查詢。 畫外音：必須是索引，否則不能批量"跳過"。	含主鍵
index	索引樹掃描	explain select count(*) from type_learn.user; ?	需要掃描索引上的全部數據 ? id是主鍵，該count查詢需要通過掃描索引上的全部數據來計數。 畫外音：此表為InnoDB引擎。 它僅比全表掃描快一點。	含主鍵
ALL	全表掃描(full table scan)	explain select * from user,user_ex where user.id=user_ex.id;(ALL+ALL)	如果id上不建索引，對于前表的每一行(row)，后表都要被全表掃描。	無主鍵

上面各類掃描方式由快到慢：

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

#-------------------------------------------------------------------------------------------------------------------

為type=const創建數據:

mysql> create database type_learn;

mysql> create table user (id int primary key,name varchar(20))engine=innodb;

mysql> insert into user values(1,'shenjian');

mysql> insert into user values(2,'zhangsan');

mysql> insert into user values(3,'lisi');

#-------------------------------------------------------------------------------------------------------------------

為type=eq_ref創建數據:

create table user (id int primary key,name varchar(20))engine=innodb;
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
create table user_ex (id int primary key,age int)engine=innodb;
insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

?

eq_ref用于聯表查詢的情況

#-------------------------------------------------------------------------------------------------------------------

為type=ref創建數據:

create table user (id int,name varchar(20) ,index(id))engine=innodb;
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
create table user_ex (id int,age int,index(id))engine=innodb;
insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

這里的index(id)是增加普通索引，普通索引不是主鍵索引

主鍵索引的葉子結點存放了整行記錄，普通索引的葉子結點存放了主鍵ID，查詢的時候需要做一次回表查詢
一定要回表查詢么？
不一定，當查詢的字段剛好是索引的字段或者索引的一部分，就可以不用回表，這也是索引覆蓋的原理

#-------------------------------------------------------------------------------------------------------------------

為type=range,index創建數據:

create table user (id int primary key,name varchar(20))engine=innodb;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');

#-------------------------------------------------------------------------------------------------------------------

為type=ALL創建數據:

create table user (id int,name varchar(20))engine=innodb;
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
create table user_ex (id int,age int)engine=innodb;
insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

#-------------------------------------------------------------------------------------------------------------------

建立正確的索引，對數據庫性能的提升很重要。

總結

各類掃描類型的要點是：

• 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了解并優化執行計劃，非常重要；

Reference:

[1]同一個SQL語句，為啥性能差異咋就這么大呢？（1分鐘系列）

[2]主鍵索引和普通索引的區別

總結

以上是生活随笔為你收集整理的mysql的explain中type取值与SQL性能优化的关系的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。