我們?cè)谑褂肧QL語(yǔ)句查詢表數(shù)據(jù)時(shí)，提前用explain進(jìn)行語(yǔ)句分析是一個(gè)非常好的習(xí)慣。通過(guò)explain輸出sql的詳細(xì)執(zhí)行信息，就可以針對(duì)性的進(jìn)行sql優(yōu)化。

今天我們來(lái)分析一下，在explain中11種不同type代表的含義以及其應(yīng)用場(chǎng)景。

1、system

應(yīng)用場(chǎng)景：表中只有一條數(shù)據(jù)，且存儲(chǔ)引擎可以準(zhǔn)確的統(tǒng)計(jì)到這條數(shù)據(jù)。

system一般出現(xiàn)在MyISAM、memory類型的表查詢中。

由于我們一般使用的存儲(chǔ)引擎都是InnoDB，所以system這種類型很少會(huì)用到。

2、const

應(yīng)用場(chǎng)景：通過(guò)主鍵或者唯一索引等值查詢來(lái)定位一條數(shù)據(jù)。

比如：select * from test where id = 1。

我們知道，MySQL底層使用B+樹(shù)來(lái)保存數(shù)據(jù)，其結(jié)構(gòu)大體可類似下圖，

那么我們?cè)趍字段上創(chuàng)建唯一索引約束，如果想找到m=103的記錄，通過(guò)二分法只需簡(jiǎn)單兩步就可以定位到m=103。

即100->102->103。

即使對(duì)于一張記錄很多的真正的業(yè)務(wù)表，因?yàn)锽+樹(shù)矮胖的結(jié)構(gòu)，定位一條唯一索引中的記錄，速度也是非常快的。

可以粗略的認(rèn)為，這種查詢速度是常數(shù)級(jí)的。

所以，MySQL就把這種唯一索引或主鍵（主鍵也是一種唯一索引）等值匹配的查詢定義為const（常數(shù)級(jí)）。

需要注意的是，由于唯一索引中允許存在多個(gè)null值，所以如果對(duì)唯一索引進(jìn)行null值查詢，是沒(méi)法用const的。

3、eq_ref

應(yīng)用場(chǎng)景：在進(jìn)行多表連接查詢時(shí)，被驅(qū)動(dòng)表通過(guò)主鍵或唯一索引鍵進(jìn)行等值查詢。

比如：SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id。

4、ref

應(yīng)用場(chǎng)景：普通二級(jí)索引等值查詢。

比如：select * from t2 where key2 =4。

除了唯一索引，我們更多的會(huì)使用普通的二級(jí)索引。

由于通過(guò)二級(jí)索引，可能會(huì)查詢到多個(gè)匹配值，相比const性能差那么一點(diǎn)。

MySQL就把這種類型的查詢定義為了ref。

在上面我們說(shuō)到，由于唯一索引可能存在多個(gè)null，所以用不了const。

那對(duì)于 select * from t2 where key2 is null 來(lái)說(shuō)，不管是唯一索引還是普通索引，其最多用到ref這種類型。

5、ref_or_null

?應(yīng)用場(chǎng)景：命中索引時(shí)，查詢條件除了等值查詢，還包含null值查詢。

比如：select * from t2 where key2 =4 or key2 is null。

其實(shí)看名字就很容易理解，MySQL會(huì)在B+樹(shù)上，找到key2=1和key2 is null 這兩種記錄范圍值，然后拿到主鍵id去回表查詢相關(guān)信息。

6、index_merge

應(yīng)用場(chǎng)景：查詢條件可以命中多個(gè)索引的情況。

比如：select * from t3 where key1 =3 or key2 =4、

索引合并其實(shí)也很好理解，當(dāng)查詢條件可以命中多個(gè)索引時(shí)，MySQL會(huì)嘗試在兩個(gè)索引樹(shù)查找匹配的條件，然后將結(jié)果其合并起來(lái)。

7、unique_subquery

應(yīng)用場(chǎng)景：查詢條件包含子查詢，并且子查詢的列可以進(jìn)行主鍵等值匹配。

比如：SELECT * FROM t2 WHERE t2.key2 IN ( SELECT id FROM t3 WHERE t2.key2 = t3.key2 ) OR t2.key2 = 1。

通過(guò)查看MySQL優(yōu)化的執(zhí)行sql，可以看到MySQL將in子查詢優(yōu)化為了exist語(yǔ)句，并且在主鍵索引上進(jìn)行了等值查詢。

MySQL優(yōu)化后的語(yǔ)句：/* select#1 */ select `dbs`.`t2`.`id` AS `id`,`dbs`.`t2`.`key2` AS `key2` from `dbs`.`t2` where (<in_optimizer>(`dbs`.`t2`.`key2`,<exists>(<primary_index_lookup>(<cache>(`dbs`.`t2`.`key2`) in t3 on PRIMARY where ((`dbs`.`t2`.`key2` = `dbs`.`t3`.`key2`) and (<cache>(`dbs`.`t2`.`key2`) = `dbs`.`t3`.`id`))))) or (`dbs`.`t2`.`key2` = 1))。

8、index_subquery

應(yīng)用場(chǎng)景：查詢條件包含子查詢，并且子查詢的列可以通過(guò)索引進(jìn)行等值匹配。

比如：SELECT * FROM t2 WHERE t2.key2 IN ( SELECT key1 FROM t3 WHERE t2.key2 = t3.key2 ) OR t2.key2 = 1。

index_subquery和unique_subquery的區(qū)別在于子查詢中的列是唯一索引還是普通的二級(jí)索引。

9、range

應(yīng)用場(chǎng)景：命中索引時(shí)，查詢某一個(gè)范圍內(nèi)的結(jié)果。

比如：select * from t3 where t3.key1 >1 and t3.key1<3。

在實(shí)際的業(yè)務(wù)場(chǎng)景中，對(duì)某個(gè)列進(jìn)行范圍查詢還是很常見(jiàn)的需求。

10、index

應(yīng)用場(chǎng)景：直接在某個(gè)索引樹(shù)上做條件判斷，并且不需要回表。

比如：select t3.key1 from t3 where t3.key2 =6。

當(dāng)我們創(chuàng)建了聯(lián)合索引idx_key1_key2(key1,key2)時(shí)，判斷條件key2=6時(shí)，其雖然不滿足索引的最左前綴原則，但是我們可以遍歷idx_key1_key2這顆索引樹(shù)，找到key2=6的記錄即可。

由于查詢結(jié)果需要的key1在這個(gè)聯(lián)合索引上，也不需要回表，此時(shí)就可以使用index。

相對(duì)來(lái)說(shuō)，index的性能是比較慢的。

11、all

應(yīng)用場(chǎng)景：直接遍歷整個(gè)聚簇索引。

比如： select * from t1。

當(dāng)MySQL無(wú)法通過(guò)where條件匹配到合適的索引或者因?yàn)槿繏呙璧拇鷥r(jià)更小時(shí)，MySQL就會(huì)選擇all這種類型來(lái)全表掃描。

這種方式也是最不推薦的。

最后

總得來(lái)說(shuō)，我們?cè)谶M(jìn)行查詢時(shí)，查詢類型可分為兩大類：全部掃描和索引查詢。

索引查詢又可以細(xì)分：

1. 唯一索引等值查詢。
2. 普通索引等值查詢。
3. 普通索引范圍查詢。
4. 掃描整個(gè)索引樹(shù)。

對(duì)于一條查詢sql來(lái)說(shuō)，不同的查詢類型雖然結(jié)果可能是一樣的，但是其性能卻可能天差地別。

不同類型性能從強(qiáng)到差：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all。

建議大家在平時(shí)書(shū)寫sql時(shí)，多用explain進(jìn)行分析，嘗試去優(yōu)化代碼，只有不斷的實(shí)踐，才能讓自己的sql能力越來(lái)越強(qiáng)。