《數據庫允許空值，往往是悲劇的開始》一文通過explain來分析SQL的執行計劃，來分析null對索引命中情況的影響，有不少朋友留言，問explain結果中的type字段，ref，ALL等不一樣的值究竟是什么含義。

今天花1分鐘簡單說下，常見的type結果及代表的含義，并且通過同一個SQL語句的性能差異，說明建對索引多么重要。

explain結果中的type字段代表什么意思？

MySQL的官網解釋非常簡潔，只用了3個單詞：連接類型(the join type)。它描述了找到所需數據使用的掃描方式。

最為常見的掃描方式有：

• system：系統表，少量數據，往往不需要進行磁盤IO；

• const：常量連接；

• eq_ref：主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描；

• ref：非主鍵非唯一索引等值掃描；

• range：范圍掃描；

• index：索引樹掃描；

• ALL：全表掃描(full table scan)；

畫外音：這些是最常見的，大家去explain自己工作中的SQL語句，95%都是上面這些類型。

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

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

下面一一舉例說明。

一、system

explain select * from mysql.time_zone;

上例中，從系統庫mysql的系統表time_zone里查詢數據，掃碼類型為system，這些數據已經加載到內存里，不需要進行磁盤IO。

這類掃描是速度最快的。

explain select * from (select * from user where id=1) tmp;

再舉一個例子，內層嵌套(const)返回了一個臨時表，外層嵌套從臨時表查詢，其掃描類型也是system，也不需要走磁盤IO，速度超快。

二、const

數據準備：

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');

const掃描的條件為：

(1)命中主鍵(primary key)或者唯一(unique)索引；

(2)被連接的部分是一個常量(const)值；

explain select * from user where id=1;

如上例，id是PK，連接部分是常量1。

畫外音：別搞什么類型轉換的幺蛾子。

這類掃描效率極高，返回數據量少，速度非常快。

三、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掃描的條件為，對于前表的每一行(row)，后表只有一行被掃描。

再細化一點：

(1)join查詢；

(2)命中主鍵(primary key)或者非空唯一(unique not null)索引；

(3)等值連接；

explain select * from user,user_ex where user.id=user_ex.id;

如上例，id是主鍵，該join查詢為eq_ref掃描。

這類掃描的速度也異常之快。

四、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);

如果把上例eq_ref案例中的主鍵索引，改為普通非唯一(non unique)索引。

explain select * from user,user_ex where user.id=user_ex.id;

就由eq_ref降級為了ref，此時對于前表的每一行(row)，后表可能有多于一行的數據被掃描。

explain select * from user where id=1;

當id改為普通非唯一索引后，常量的連接查詢，也由const降級為了ref，因為也可能有多于一行的數據被掃描。

ref掃描，可能出現在join里，也可能出現在單表普通索引里，每一次匹配可能有多行數據返回，雖然它比eq_ref要慢，但它仍然是一個很快的join類型。

五、range

數據準備：

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');

range掃描就比較好理解了，它是索引上的范圍查詢，它會在索引上掃碼特定范圍內的值。

explain select * from user where id between 1 and 4;

explain select * from user where idin(1,2,3);

explain select * from user where id>3;

像上例中的between，in，>都是典型的范圍(range)查詢。

畫外音：必須是索引，否則不能批量"跳過"。

六、index

index類型，需要掃描索引上的全部數據。

explain count (*) from user;

如上例，id是主鍵，該count查詢需要通過掃描索引上的全部數據來計數。

畫外音：此表為InnoDB引擎。

它僅比全表掃描快一點。

七、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);

explain select * from user,user_ex where user.id=user_ex.id;

如果id上不建索引，對于前表的每一行(row)，后表都要被全表掃描。

今天這篇文章中，這個相同的join語句出現了三次：

(1)掃描類型為eq_ref，此時id為主鍵；

(2)掃描類型為ref，此時id為非唯一普通索引；

(3)掃描類型為ALL，全表掃描，此時id上無索引；

有此可見，建立正確的索引，對數據庫性能的提升是多么重要。

另外，《類型轉換帶來的大坑》中，也提到不正確的SQL語句，可能導致全表掃描。

全表掃描代價極大，性能很低，是應當極力避免的，通過explain分析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了解并優化執行計劃，非常重要；

思路比結論重要，希望大家有收獲。

畫外音：本文測試于MySQL5.6。

架構師之路-分享技術思路

相關推薦：

《緩沖池(buffer pool)，這次徹底懂了！！！》

《寫緩沖(change buffer)，這次徹底懂了！！！》

《兩類非常隱蔽的全表掃描 | 1分鐘系列》

《MyISAM與InnoDB的索引差異 | 1分鐘系列》

《數據庫允許null，悲劇的開始 | 1分鐘系列》