概述

對(duì)于MySQL執(zhí)行計(jì)劃的獲取，我們可以通過(guò)explain方式來(lái)查看，explain方式看似簡(jiǎn)單，實(shí)際上包含的內(nèi)容很多，尤其是輸出結(jié)果中的type類(lèi)型列。理解這些不同的類(lèi)型，對(duì)于我們SQL優(yōu)化舉足輕重。今天主要介紹一下常見(jiàn)的type結(jié)果及代表的含義，并且通過(guò)同一個(gè)SQL語(yǔ)句的性能差異，說(shuō)明建對(duì)索引多么重要。

explain結(jié)果中的type字段代表什么意思？

MySQL的官網(wǎng)解釋非常簡(jiǎn)潔，只用了3個(gè)單詞：連接類(lèi)型(the join type)。它描述了找到所需數(shù)據(jù)使用的掃描方式。

一、EXPLAIN 語(yǔ)句中type列的值

效率總結(jié)

1)依次從好到差：

system，const，index ， range，index_merge，ALL 單獨(dú)查詢(xún)

eq_ref，ref，ref_or_null all 多表join 查詢(xún)

index_subquery unique_subquery 子查詢(xún)

2)index_merge之外，其他的type只可以用到一個(gè)索引

二、system

這里我在多個(gè)mysql測(cè)試過(guò)，如果是5.7以上版本的話(huà)就不是system了，而是all，即使只有一條記錄。

舉一個(gè)例子，內(nèi)層嵌套(const)返回了一個(gè)臨時(shí)表，外層嵌套從臨時(shí)表查詢(xún)，其掃描類(lèi)型是system，也不需要走磁盤(pán)IO，速度超快。

三、const

const掃描的條件為：

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

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

1、數(shù)據(jù)準(zhǔn)備：

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、查看執(zhí)行計(jì)劃

explain select * from t2 where id=1;

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

這類(lèi)掃描效率極高，返回?cái)?shù)據(jù)量少，速度非常快。

四、eq_ref

eq_ref掃描的條件為，對(duì)于前表的每一行(row)，后表只有一行被掃描。

再細(xì)化一點(diǎn)：

(1)join查詢(xún)；

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

(3)等值連接；

1、數(shù)據(jù)準(zhǔn)備：

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、查看執(zhí)行計(jì)劃

explain select * from t3,t4 where t3.id=t4.id;

如上例，id是主鍵，該join查詢(xún)?yōu)閑q_ref掃描。

這類(lèi)掃描的速度也異常之快。

五、ref

1、數(shù)據(jù)準(zhǔn)備

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降級(jí)為了ref，此時(shí)對(duì)于前表的每一行(row)，后表可能有多于一行的數(shù)據(jù)被掃描。

explain select * from t5,t6 where t5.id=t6.id;

當(dāng)id改為普通非唯一索引后，常量的連接查詢(xún)，也由const降級(jí)為了ref，因?yàn)橐部赡苡卸嘤谝恍械臄?shù)據(jù)被掃描。

ref掃描，可能出現(xiàn)在join里，也可能出現(xiàn)在單表普通索引里，每一次匹配可能有多行數(shù)據(jù)返回，雖然它比eq_ref要慢，但它仍然是一個(gè)很快的join類(lèi)型。

六、range

range掃描就比較好理解了，它是索引上的范圍查詢(xún)，它會(huì)在索引上掃碼特定范圍內(nèi)的值。

1、數(shù)據(jù)準(zhǔn)備：

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、查看執(zhí)行計(jì)劃

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)查詢(xún)。

注意：必須是索引，否則不能批量"跳過(guò)"。

七、index

index類(lèi)型，需要掃描索引上的全部數(shù)據(jù)。

explain select count(*) from t7;

如上例，id是主鍵，該count查詢(xún)需要通過(guò)掃描索引上的全部數(shù)據(jù)來(lái)計(jì)數(shù)。

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

八、ALL

1、數(shù)據(jù)準(zhǔn)備：

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、查看執(zhí)行計(jì)劃

explain select * from t8,t9 where t8.id=t9.id;

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

今天介紹的實(shí)驗(yàn)中，這個(gè)相同的join語(yǔ)句出現(xiàn)了三次：

(1)掃描類(lèi)型為eq_ref，此時(shí)id為主鍵；

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

(3)掃描類(lèi)型為ALL，全表掃描，此時(shí)id上無(wú)索引；

有此可見(jiàn)，建立正確的索引，對(duì)數(shù)據(jù)庫(kù)性能的提升是多么重要。另外，不正確的SQL語(yǔ)句，可能導(dǎo)致全表掃描。

總結(jié)

1、explain結(jié)果中的type字段，表示(廣義)連接類(lèi)型，它描述了找到所需數(shù)據(jù)使用的掃描方式；

2、常見(jiàn)的掃描類(lèi)型有：

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

其掃描速度由快到慢；

3、各類(lèi)掃描類(lèi)型的要點(diǎn)是：

system最快：不進(jìn)行磁盤(pán)IO

const：PK或者unique上的等值查詢(xún)

eq_ref：PK或者unique上的join查詢(xún)，等值匹配，對(duì)于前表的每一行(row)，后表只有一行命中

ref：非唯一索引，等值匹配，可能有多行命中

range：索引上的范圍掃描，例如：between/in/>

index：索引上的全集掃描，例如：InnoDB的count

ALL最慢：全表掃描(full table scan)

4、建立正確的索引(index)，非常重要；

5、使用explain了解并優(yōu)化執(zhí)行計(jì)劃，非常重要；

轉(zhuǎn)發(fā)到朋友圈是最高的贊賞

因?yàn)椴皇翘焐愘|(zhì)，所以必須天生勵(lì)志！

總結(jié)

以上是生活随笔為你收集整理的mysql explain字段含义_史上最全的explain常见结果含义分析，值得收藏的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。