建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
我們?cè)谑褂肧QL語(yǔ)句查詢(xún)表數(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類(lèi)型的表查詢(xún)中。
由于我們一般使用的存儲(chǔ)引擎都是InnoDB，所以system這種類(lèi)型很少會(huì)用到。
2、const
應(yīng)用場(chǎng)景：通過(guò)主鍵或者唯一索引等值查詢(xún)來(lái)定位一條數(shù)據(jù)。
比如：select * from test where id = 1。
我們知道，MySQL底層使用B+樹(shù)來(lái)保存數(shù)據(jù)，其結(jié)構(gòu)大體可類(lèi)似下圖，
那么我們?cè)趍字段上創(chuàng)建唯一索引約束，如果想找到m=103的記錄，通過(guò)二分法只需簡(jiǎn)單兩步就可以定位到m=103。
即100->102->103。
即使對(duì)于一張記錄很多的真正的業(yè)務(wù)表，因?yàn)锽+樹(shù)矮胖的結(jié)構(gòu)，定位一條唯一索引中的記錄，速度也是非?？斓?。
可以粗略的認(rèn)為，這種查詢(xún)速度是常數(shù)級(jí)的。
所以，MySQL就把這種唯一索引或主鍵（主鍵也是一種唯一索引）等值匹配的查詢(xún)定義為const（常數(shù)級(jí)）。
需要注意的是，由于唯一索引中允許存在多個(gè)null值，所以如果對(duì)唯一索引進(jìn)行null值查詢(xún)，是沒(méi)法用const的。
3、eq_ref
應(yīng)用場(chǎng)景：在進(jìn)行多表連接查詢(xún)時(shí)，被驅(qū)動(dòng)表通過(guò)主鍵或唯一索引鍵進(jìn)行等值查詢(xún)。
比如：SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id。
4、ref
應(yīng)用場(chǎng)景：普通二級(jí)索引等值查詢(xún)。
比如：select * from t2 where key2 =4。
除了唯一索引，我們更多的會(huì)使用普通的二級(jí)索引。
由于通過(guò)二級(jí)索引，可能會(huì)查詢(xún)到多個(gè)匹配值，相比const性能差那么一點(diǎn)。
MySQL就把這種類(lèi)型的查詢(xún)定義為了ref。
在上面我們說(shuō)到，由于唯一索引可能存在多個(gè)null，所以用不了const。
那對(duì)于 select * from t2 where key2 is null 來(lái)說(shuō)，不管是唯一索引還是普通索引，其最多用到ref這種類(lèi)型。
5、ref_or_null
?應(yīng)用場(chǎng)景：命中索引時(shí)，查詢(xún)條件除了等值查詢(xún)，還包含null值查詢(xún)。
比如：select * from t2 where key2 =4 or key2 is null。
其實(shí)看名字就很容易理解，MySQL會(huì)在B+樹(shù)上，找到key2=1和key2 is null 這兩種記錄范圍值，然后拿到主鍵id去回表查詢(xún)相關(guān)信息。
6、index_merge
應(yīng)用場(chǎng)景：查詢(xún)條件可以命中多個(gè)索引的情況。
比如：select * from t3 where key1 =3 or key2 =4、
索引合并其實(shí)也很好理解，當(dāng)查詢(xún)條件可以命中多個(gè)索引時(shí)，MySQL會(huì)嘗試在兩個(gè)索引樹(shù)查找匹配的條件，然后將結(jié)果其合并起來(lái)。
7、unique_subquery
應(yīng)用場(chǎng)景：查詢(xún)條件包含子查詢(xún)，并且子查詢(xún)的列可以進(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子查詢(xún)優(yōu)化為了exist語(yǔ)句，并且在主鍵索引上進(jìn)行了等值查詢(xún)。
MySQL優(yōu)化后的語(yǔ)句：/* select#1 */ select `dbs`.`t2`.`id` AS `id`,`dbs`.`t2`.`key2` AS `key2` from `dbs`.`t2` where (
8、index_subquery
應(yīng)用場(chǎng)景：查詢(xún)條件包含子查詢(xún)，并且子查詢(xún)的列可以通過(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ū)別在于子查詢(xún)中的列是唯一索引還是普通的二級(jí)索引。
9、range
應(yīng)用場(chǎng)景：命中索引時(shí)，查詢(xún)某一個(gè)范圍內(nèi)的結(jié)果。
比如：select * from t3 where t3.key1 >1 and t3.key1<3。
在實(shí)際的業(yè)務(wù)場(chǎng)景中，對(duì)某個(gè)列進(jìn)行范圍查詢(xú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í)，其雖然不滿(mǎn)足索引的最左前綴原則，但是我們可以遍歷idx_key1_key2這顆索引樹(shù)，找到key2=6的記錄即可。
由于查詢(xún)結(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)全表掃描。
這種方式也是最不推薦的。
最后
總得來(lái)說(shuō)，我們?cè)谶M(jìn)行查詢(xún)時(shí)，查詢(xún)類(lèi)型可分為兩大類(lèi)：全部掃描和索引查詢(xún)。
索引查詢(xún)又可以細(xì)分：
- 唯一索引等值查詢(xún)。
- 普通索引等值查詢(xún)。
- 普通索引范圍查詢(xún)。
- 掃描整個(gè)索引樹(shù)。
對(duì)于一條查詢(xún)sql來(lái)說(shuō)，不同的查詢(xún)類(lèi)型雖然結(jié)果可能是一樣的，但是其性能卻可能天差地別。
不同類(lèi)型性能從強(qiáng)到差：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all。
建議大家在平時(shí)書(shū)寫(xiě)sql時(shí)，多用explain進(jìn)行分析，嘗試去優(yōu)化代碼，只有不斷的實(shí)踐，才能讓自己的sql能力越來(lái)越強(qiáng)。
分享標(biāo)題：MySQL：連Explain的Type類(lèi)型都沒(méi)搞清楚，怎敢說(shuō)精通SQL優(yōu)化？
地址分享：http://www.dlmjj.cn/article/djseopj.html
