詳解mysql中explain的type

導(dǎo)語(yǔ)：

很多情況下，有很多人用各種select語(yǔ)句查詢到了他們想要的數(shù)據(jù)后，往往便以為工作圓滿結(jié)束了。
這些事情往往發(fā)生在一些學(xué)生亦或剛?cè)肼殘?chǎng)但之前又沒(méi)有很好數(shù)據(jù)庫(kù)基礎(chǔ)的小白身上，但所謂聞道有先后，只要我們小白好好學(xué)習(xí)，天天向上，還是很靠譜的。

當(dāng)一個(gè)sql查詢語(yǔ)句被寫出來(lái)之后，其實(shí)你的工作只完成了一小半，接下來(lái)更重要的工作是評(píng)估你自己寫的sql的質(zhì)量與效率。mysql為我們提供了很有用的輔助武器explain，它向我們展示了mysql接收到一條sql語(yǔ)句的執(zhí)行計(jì)劃。根據(jù)explain返回的結(jié)果我們便可以知道我們的sql寫的怎么樣，是否會(huì)造成查詢瓶頸，同時(shí)根據(jù)結(jié)果不斷的修改調(diào)整查詢語(yǔ)句，從而完成sql優(yōu)化的過(guò)程。

雖然 explain返回的結(jié)果項(xiàng)很多，這里我們只關(guān)注三種，分別是type，key，rows。其中key表明的是這次查找中所用到的索引，rows是指這次查找數(shù)據(jù)所掃描的行數(shù)（這里可以先這樣理解，但實(shí)際上是內(nèi)循環(huán)的次數(shù)）。而type則是本文要詳細(xì)記錄的連接類型，前兩項(xiàng)重要而且簡(jiǎn)單，無(wú)需多說(shuō)。

type -- 連接類型

type意味著類型，這里的type官方全稱是“join type”，意思是“連接類型”,這樣很容易給人一種錯(cuò)覺(jué)覺(jué)得必須需要倆個(gè)表以上才有連接類型。事實(shí)上這里的連接類型并非字面那樣的狹隘，它更確切的說(shuō)是一種數(shù)據(jù)庫(kù)引擎查找表的一種方式，在《高性能mysql》一書(shū)中作者更是覺(jué)得稱呼它為訪問(wèn)類型更貼切一些。

mysql5.7中type的類型達(dá)到了14種之多，這里只記錄和理解最重要且經(jīng)常遇見(jiàn)的六種類型，它們分別是all,index,range,ref,eq_ref，const。從左到右，它們的效率依次是增強(qiáng)的。撇開(kāi)sql的具體應(yīng)用環(huán)境以及其他因素，你應(yīng)當(dāng)盡量?jī)?yōu)化你的sql語(yǔ)句，使它的type盡量靠右，但實(shí)際運(yùn)用中還是要綜合考慮各個(gè)方面的。

接下來(lái)，為了演示和重現(xiàn)這幾種連接類型，我新建了一個(gè)數(shù)據(jù)測(cè)試表，以方面更好的理解這五種類型。

| employee | CREATE TABLE `employee` (
 `rec_id` int(11) NOT NULL AUTO_INCREMENT,
 `no` varchar(10) NOT NULL,
 `name` varchar(20) NOT NULL,
 `position` varchar(20) NOT NULL,
 `age` varchar(2) NOT NULL,
 PRIMARY KEY (`rec_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

all

這便是所謂的“全表掃描”，如果是展示一個(gè)數(shù)據(jù)表中的全部數(shù)據(jù)項(xiàng)，倒是覺(jué)得也沒(méi)什么，如果是在一個(gè)查找數(shù)據(jù)項(xiàng)的sql中出現(xiàn)了all類型，那通常意味著你的sql語(yǔ)句處于一種最原生的狀態(tài)，有很大的優(yōu)化空間。
為什么這么說(shuō)呢？因?yàn)閍ll是一種非常暴力和原始的查找方法，非常的耗時(shí)而且低效。用all去查找數(shù)據(jù)就好比這樣的一個(gè)情形：S學(xué)校有倆萬(wàn)人，我告訴你你給我找到小明，然后你怎么做呢！你當(dāng)然是把全校倆萬(wàn)人挨個(gè)找一遍，即使你很幸運(yùn)第一個(gè)人便找到了小明，但是你仍然不能停下，因?yàn)槟銦o(wú)法確認(rèn)是否有另外一個(gè)小明存在，直到你把倆萬(wàn)人找完為止。所以，基本所有情況，我們都要避免這樣類型的查找，除非你不得不這樣做。
以employee表為例，下面一種情形便是all類型的查找：

mysql> explain select * from employee where `no` = '20150001';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra    |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE   | employee | ALL | NULL     | NULL | NULL  | NULL |  5 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

這是因?yàn)閚o列既不是主鍵也不是索引，因此只能采用全表掃描來(lái)查找目標(biāo)no。

index

這種連接類型只是另外一種形式的全表掃描，只不過(guò)它的掃描順序是按照索引的順序。這種掃描根據(jù)索引然后回表取數(shù)據(jù)，和all相比，他們都是取得了全表的數(shù)據(jù)，而且index要先讀索引而且要回表隨機(jī)取數(shù)據(jù)，因此index不可能會(huì)比all快（取同一個(gè)表數(shù)據(jù)），但為什么官方的手冊(cè)將它的效率說(shuō)的比all好，唯一可能的原因在于，按照索引掃描全表的數(shù)據(jù)是有序的。這樣一來(lái)，結(jié)果不同，也就沒(méi)法比效率的問(wèn)題了。
如果一定要比效率，只需要獲取這個(gè)表的數(shù)據(jù)并且排序便可以看出來(lái)誰(shuí)比誰(shuí)效率高了：

mysql> explain select * from employee order by `no` ;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra     |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE   | employee | ALL | NULL     | NULL | NULL  | NULL |  5 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
mysql> explain select * from employee order by rec_id ;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key   | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE   | employee | index | NULL     | PRIMARY | 4    | NULL |  5 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

上面可以看出，根據(jù)no列排序的連接類型是all型的，但是注意extra列是用到了排序（Using filesort），而根據(jù)rec_id列排序的連接類型是index，而且得到的結(jié)果自然是有序的，不許額外的排序?？赡苷且?yàn)檫@個(gè)緣故，index的效率比all高，但注意這需要相同的條件才成立（既需要排序）。

如果連接類型為type，而且extra列中的值為‘Using index'，那么稱這種情況為 索引覆蓋；
索引覆蓋意味著什么呢？想象這樣一種場(chǎng)景，如果說(shuō)一本新華字典是一張表，當(dāng)然前面的索引部分（假設(shè)按照部首的索引）是這張表的索引，那么索引覆蓋就相當(dāng)于根據(jù)部首索引獲取第一個(gè)字到最后一個(gè)字（新華字典的所有字）。我們獲得了字典中所有的字，然而我們并沒(méi)有查一次表，因?yàn)槲覀兿胍亩荚缢饕?，即索引覆蓋。

mysql> explain select rec_id from employee ;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key   | key_len | ref | rows | Extra    |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE   | employee | index | NULL     | PRIMARY | 4    | NULL |  5 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

上例獲取的rec_id剛好為索引列，因此無(wú)需回表取數(shù)據(jù)。

range

range指的是有范圍的索引掃描，相對(duì)于index的全索引掃描，它有范圍限制，因此要優(yōu)于index。關(guān)于range比較容易理解，需要記住的是出現(xiàn)了range，則一定是基于索引的。同時(shí)除了顯而易見(jiàn)的between，and以及'>',''外，in和or也是索引范圍掃描。

ref

出現(xiàn)該連接類型的條件是： 查找條件列使用了索引而且不為主鍵和unique。其實(shí)，意思就是雖然使用了索引，但該索引列的值并不唯一，有重復(fù)。這樣即使使用索引快速查找到了第一條數(shù)據(jù)，仍然不能停止，要進(jìn)行目標(biāo)值附近的小范圍掃描。但它的好處是它并不需要掃全表，因?yàn)樗饕怯行虻?，即便有重?fù)值，也是在一個(gè)非常小的范圍內(nèi)掃描。下面為了演示這種情形，給employee表中的name列添加一個(gè)普通的key（值允許重復(fù)）

 alter table employee add key I_EMPLOYEE_NAME(`name`); 

接下來(lái)，在employee表中根據(jù)name查找數(shù)據(jù)的時(shí)候，mysql優(yōu)化器便選擇了ref的連接類型。

mysql> explain select * from employee where `name` = '張三';
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref  | rows | Extra         |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
| 1 | SIMPLE   | employee | ref | I_EMPLOYEE_NAM | I_EMPLOYEE_NAM | 62   | const |  1 | Using index condition |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+

ref_eq

ref_eq 與 ref相比牛的地方是，它知道這種類型的查找結(jié)果集只有一個(gè)？什么情況下結(jié)果集只有一個(gè)呢！那便是使用了主鍵或者唯一性索引進(jìn)行查找的情況，比如根據(jù)學(xué)號(hào)查找某一學(xué)校的一名同學(xué)，在沒(méi)有查找前我們就知道結(jié)果一定只有一個(gè)，所以當(dāng)我們首次查找到這個(gè)學(xué)號(hào)，便立即停止了查詢。這種連接類型每次都進(jìn)行著精確查詢，無(wú)需過(guò)多的掃描，因此查找效率更高，當(dāng)然列的唯一性是需要根據(jù)實(shí)際情況決定的。
在單個(gè)表中，曾嘗試了很多方法想出現(xiàn)ref_eq的連接類型，然而很多時(shí)候出現(xiàn)的都是const，因此不得不隨手連接了一張表得到了想要的連接類型，該表的建表代買為。（博主比較懶，連接了兩個(gè)沒(méi)有關(guān)系的表，o(╯□╰)o）

CREATE TABLE `score` (
 `rec_id` INT(11) NOT NULL AUTO_INCREMENT,
 `stu_id` INT(11) NOT NULL,
 `mark` INT(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`rec_id`),
 UNIQUE KEY `UK_SCORE_STU_ID` (`stu_id`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

employee表中有五條數(shù)據(jù)，score表中有對(duì)應(yīng)的五條數(shù)據(jù)，其中employee的rec_id 和score的stu_id 是一一對(duì)應(yīng)的。

mysql> explain select ep.name,sc.mark from employee ep,score sc where ep.rec_id = sc.stu_id;
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
| id | select_type | table | type  | possible_keys  | key   | key_len | ref       | rows | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
| 1 | SIMPLE   | sc  | ALL  | UK_SCORE_STU_ID | NULL  | NULL  | NULL      |  5 | NULL |
| 1 | SIMPLE   | ep  | eq_ref | PRIMARY     | PRIMARY | 4    | my_db.sc.stu_id |  1 | NULL |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+

上面就可以看到score表是全表掃描的類型，rows=5代表外層表循環(huán)了五次（因?yàn)橛形鍡l數(shù)據(jù)），但是employee表的rows怎么是1，怎么可能？剛開(kāi)始也是很疑惑，這與mysql的查詢?cè)硐⑾⑾嚓P(guān)，rows實(shí)際反映的是查詢的內(nèi)循環(huán)數(shù)，針對(duì)外層的每一條數(shù)據(jù)匹配，employee的確一槍就可以命中，因此rows為1。

const

通常情況下，如果將一個(gè)主鍵放置到where后面作為條件查詢，mysql優(yōu)化器就能把這次查詢優(yōu)化轉(zhuǎn)化為一個(gè)常量。至于如何轉(zhuǎn)化以及何時(shí)轉(zhuǎn)化，這個(gè)取決于優(yōu)化器。

總結(jié)

explain 就像一面鏡子，有事沒(méi)事寫完sql記得explain一下。同時(shí)，在寫文章也發(fā)現(xiàn)，有很多東西和細(xì)節(jié)，想要明白清楚，也是沒(méi)有那么簡(jiǎn)單的，需要對(duì)操作系統(tǒng)以及數(shù)據(jù)庫(kù)的底層查詢和運(yùn)行原理要有一個(gè)清楚的理解。同時(shí)type的幾種類型幾乎都是基于索引之上的，因此需要對(duì)索引有個(gè)深入的了解，而且explain的結(jié)果可以指導(dǎo)我們什么時(shí)候加索引，什么時(shí)候不加索引，從而讓我們更好的使用索引。

以上就是詳解mysql中explain的type的詳細(xì)內(nèi)容，更多關(guān)于mysql中explain的type的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！