對于MySQL執行計劃的獲取，我們可以通過explain方式來查看，explain方式看似簡單，實際上包含的内容很多，尤其是輸出結果中的type類型列。理解這些不同的類型，對于我們SQL優化舉足輕重。今天主要介紹一下常見的type結果及代表的含義，并且通過同一個SQL語句的性能差異，說明建對索引多麼重要。

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

MySQL的官網解釋非常簡潔，隻用了3個單詞：連接類型(the join 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隻可以用到一個索引

這裡我在多個mysql測試過，如果是5.7以上版本的話就不是system了，而是all，即使隻有一條記錄。

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

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掃描的條件為，對于前表的每一行(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掃描。

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

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掃描就比較好理解了，它是索引上的範圍查詢，它會在索引上掃碼特定範圍内的值。

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類型，需要掃描索引上的全部數據。

explain select count(*) from t7;

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

注意：此表為InnoDB引擎，它僅比全表掃描快一點。

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方面的内容，感興趣的朋友可以關注一下~

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!