導語：
很多情況下，有很多人用各種select語句查詢到了他們想要的數據後，往往便以為工作圓滿結束了。
這些事情往往發生在一些學生亦或剛入職場但之前又沒有很好數據庫基礎的小白身上，但所謂聞道有先後，只要我們小白好好學習，天天向上，還是很靠譜的。
當一個sql查詢語句被寫出來之後，其實你的工作只完成了一小半，接下來更重要的工作是評估你自己寫的sql的質量與效率。mysql為我們提供了很有用的輔助武器explain，它向我們展示了mysql接收到一條sql語句的執行計劃。根據explain返回的結果我們便可以知道我們的sql寫的怎麼樣，是否會造成查詢瓶頸，同時根據結果不斷的修改調整查詢語句，從而完成sql優化的過程。
雖然 explain返回的結果項很多，這裡我們只關注三種，分別是type，key，rows。其中key表明的是這次查找中所用到的索引，rows是指這次查找數據所掃描的行數（這裡可以先這樣理解，但實際上是內循環的次數）。而type則是本文要詳細記錄的連接類型，前兩項重要而且簡單，無需多說。
type -- 連接類型
type意味著類型，這裡的type官方全稱是“join type”，意思是“連接類型”,這樣很容易給人一種錯覺覺得必須需要倆個表以上才有連接類型。事實上這裡的連接類型並非字面那樣的狹隘，它更確切的說是一種數據庫引擎查找表的一種方式，在《高性能mysql》一書中作者更是覺得稱呼它為訪問類型更貼切一些。
mysql5.7中type的類型達到了14種之多，這裡只記錄和理解最重要且經常遇見的六種類型，它們分別是all,index,range,ref,eq_ref，const。從左到右，它們的效率依次是增強的。撇開sql的具體應用環境以及其他因素，你應當儘量優化你的sql語句，使它的type儘量靠右，但實際運用中還是要綜合考慮各個方面的。
接下來，為了演示和重現這幾種連接類型，我新建了一個數據測試表，以方面更好的理解這五種類型。
| 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
這便是所謂的“全表掃描”，如果是展示一個數據表中的全部數據項，倒是覺得也沒什麼，如果是在一個查找數據項的sql中出現了all類型，那通常意味著你的sql語句處於一種最原生的狀態，有很大的優化空間。
為什麼這麼說呢？因為all是一種非常暴力和原始的查找方法，非常的耗時而且低效。用all去查找數據就好比這樣的一個情形：S學校有倆萬人，我告訴你你給我找到小明，然後你怎麼做呢！你當然是把全校倆萬人挨個找一遍，即使你很幸運第一個人便找到了小明，但是你仍然不能停下，因為你無法確認是否有另外一個小明存在，直到你把倆萬人找完為止。所以，基本所有情況，我們都要避免這樣類型的查找，除非你不得不這樣做。
以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 |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
這是因為no列既不是主鍵也不是索引，因此只能採用全表掃描來查找目標no。
index
這種連接類型只是另外一種形式的全表掃描，只不過它的掃描順序是按照索引的順序。這種掃描根據索引然後回表取數據，和all相比，他們都是取得了全表的數據，而且index要先讀索引而且要回表隨機取數據，因此index不可能會比all快（取同一個表數據），但為什麼官方的手冊將它的效率說的比all好，唯一可能的原因在於，按照索引掃描全表的數據是有序的。這樣一來，結果不同，也就沒法比效率的問題了。
如果一定要比效率，只需要獲取這個表的數據並且排序便可以看出來誰比誰效率高了：
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 |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
上面可以看出，根據no列排序的連接類型是all型的，但是注意extra列是用到了排序（Using filesort），而根據rec_id列排序的連接類型是index，而且得到的結果自然是有序的，不許額外的排序。可能正是因為這個緣故，index的效率比all高，但注意這需要相同的條件才成立（既需要排序）。
如果連接類型為type，而且extra列中的值為‘Using index’，那麼稱這種情況為 索引覆蓋；
索引覆蓋意味著什麼呢？想象這樣一種場景，如果說一本新華字典是一張表，當然前面的索引部分（假設按照部首的索引）是這張表的索引，那麼索引覆蓋就相當於根據部首索引獲取第一個字到最後一個字（新華字典的所有字）。我們獲得了字典中所有的字，然而我們並沒有查一次表，因為我們想要的都早索引中，即索引覆蓋。
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剛好為索引列，因此無需回表取數據。
range
range指的是有範圍的索引掃描，相對於index的全索引掃描，它有範圍限制，因此要優於index。關於range比較容易理解，需要記住的是出現了range，則一定是基於索引的。同時除了顯而易見的between，and以及'>','
ref
出現該連接類型的條件是： 查找條件列使用了索引而且不為主鍵和unique。其實，意思就是雖然使用了索引，但該索引列的值並不唯一，有重複。這樣即使使用索引快速查找到了第一條數據，仍然不能停止，要進行目標值附近的小範圍掃描。但它的好處是它並不需要掃全表，因為索引是有序的，即便有重複值，也是在一個非常小的範圍內掃描。下面為了演示這種情形，給employee表中的name列添加一個普通的key（值允許重複）
alter table employee add key I_EMPLOYEE_NAME(`name`);
接下來，在employee表中根據name查找數據的時候，mysql優化器便選擇了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相比牛的地方是，它知道這種類型的查找結果集只有一個？什麼情況下結果集只有一個呢！那便是使用了主鍵或者唯一性索引進行查找的情況，比如根據學號查找某一學校的一名同學，在沒有查找前我們就知道結果一定只有一個，所以當我們首次查找到這個學號，便立即停止了查詢。這種連接類型每次都進行著精確查詢，無需過多的掃描，因此查找效率更高，當然列的唯一性是需要根據實際情況決定的。
在單個表中，曾嘗試了很多方法想出現ref_eq的連接類型，然而很多時候出現的都是const，因此不得不隨手連接了一張表得到了想要的連接類型，該表的建表代買為。（博主比較懶，連接了兩個沒有關係的表，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表中有五條數據，score表中有對應的五條數據，其中employee的rec_id 和score的stu_id 是一一對應的。
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代表外層表循環了五次（因為有五條數據），但是employee表的rows怎麼是1，怎麼可能？剛開始也是很疑惑，這與mysql的查詢原理息息相關，rows實際反映的是查詢的內循環數，針對外層的每一條數據匹配，employee的確一槍就可以命中，因此rows為1。
const
通常情況下，如果將一個主鍵放置到where後面作為條件查詢，mysql優化器就能把這次查詢優化轉化為一個常量。至於如何轉化以及何時轉化，這個取決於優化器。
總結
explain 就像一面鏡子，有事沒事寫完sql記得explain一下。同時，在寫文章也發現，有很多東西和細節，想要明白清楚，也是沒有那麼簡單的，需要對操作系統以及數據庫的底層查詢和運行原理要有一個清楚的理解。同時type的幾種類型幾乎都是基於索引之上的，因此需要對索引有個深入的瞭解，而且explain的結果可以指導我們什麼時候加索引，什麼時候不加索引，從而讓我們更好的使用索引。
閱讀更多 程序猿的易生 的文章
