我們在使用SQL語句查詢表數據時，提前用explain進行語句分析是一個非常好的習慣。通過explain輸出sql的詳細執行信息，就可以針對性的進行sql優化。

今天我們來分析一下，在explain中11種不同type代表的含義以及其應用場景。

1、system

應用場景：表中只有一條數據，且存儲引擎可以準確的統計到這條數據。

system一般出現在MyISAM、memory類型的表查詢中。

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

2、const

應用場景：通過主鍵或者唯一索引等值查詢來定位一條數據。

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

我們知道，MySQL底層使用B+樹來保存數據，其結構大體可類似下圖，

那么我們在m字段上創建唯一索引約束，如果想找到m=103的記錄，通過二分法只需簡單兩步就可以定位到m=103。

即100->102->103。

即使對于一張記錄很多的真正的業務表，因為B+樹矮胖的結構，定位一條唯一索引中的記錄，速度也是非?？斓?。

可以粗略的認為，這種查詢速度是常數級的。

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

需要注意的是，由于唯一索引中允許存在多個null值，所以如果對唯一索引進行null值查詢，是沒法用const的。

3、eq_ref

應用場景：在進行多表連接查詢時，被驅動表通過主鍵或唯一索引鍵進行等值查詢。

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

4、ref

應用場景：普通二級索引等值查詢。

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

除了唯一索引，我們更多的會使用普通的二級索引。

由于通過二級索引，可能會查詢到多個匹配值，相比const性能差那么一點。

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

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

那對于 select * from t2 where key2 is null 來說，不管是唯一索引還是普通索引，其最多用到ref這種類型。

5、ref_or_null

?應用場景：命中索引時，查詢條件除了等值查詢，還包含null值查詢。

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

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

6、index_merge

應用場景：查詢條件可以命中多個索引的情況。

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

索引合并其實也很好理解，當查詢條件可以命中多個索引時，MySQL會嘗試在兩個索引樹查找匹配的條件，然后將結果其合并起來。

7、unique_subquery

應用場景：查詢條件包含子查詢，并且子查詢的列可以進行主鍵等值匹配。

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

通過查看MySQL優化的執行sql，可以看到MySQL將in子查詢優化為了exist語句，并且在主鍵索引上進行了等值查詢。

MySQL優化后的語句：/* 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

應用場景：查詢條件包含子查詢，并且子查詢的列可以通過索引進行等值匹配。

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

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

9、range

應用場景：命中索引時，查詢某一個范圍內的結果。

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

在實際的業務場景中，對某個列進行范圍查詢還是很常見的需求。

10、index

應用場景：直接在某個索引樹上做條件判斷，并且不需要回表。

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

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

由于查詢結果需要的key1在這個聯合索引上，也不需要回表，此時就可以使用index。

相對來說，index的性能是比較慢的。

11、all

應用場景：直接遍歷整個聚簇索引。

比如： select * from t1。

當MySQL無法通過where條件匹配到合適的索引或者因為全部掃描的代價更小時，MySQL就會選擇all這種類型來全表掃描。

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

最后

總得來說，我們在進行查詢時，查詢類型可分為兩大類：全部掃描和索引查詢。

索引查詢又可以細分：

1. 唯一索引等值查詢。
2. 普通索引等值查詢。
3. 普通索引范圍查詢。
4. 掃描整個索引樹。

對于一條查詢sql來說，不同的查詢類型雖然結果可能是一樣的，但是其性能卻可能天差地別。

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

建議大家在平時書寫sql時，多用explain進行分析，嘗試去優化代碼，只有不斷的實踐，才能讓自己的sql能力越來越強。