知識星球來自公眾號：譚小譚
這篇文章將給大家介紹如何使用 explain 來分析一條 sql 。
網上其實已經有非常多的文章都很詳細的介紹了 explain 的使用，這篇文章將實體和原理結合起來，儘量讓你有更好的理解，相信我，認真看完你應該會有特別的收穫。
explain 翻譯過來就是解釋的意思， 在 mysql 裡被稱作執行計劃，即可以透過該命令看出 mysql 在經過最佳化器分析後決定要如何執行該條 sql 。
說到最佳化器，再多說一句，mysql 內建了一個強大的最佳化器，最佳化器的主要任務就是把你寫的 sql 再給最佳化一下，盡可能以更低成本去執行，比如掃描更少的行數，避免排序等。執行一條sql陳述句都經歷了什麼？ 我在前面的文章中有介紹過最佳化器相關的。
你可能會問，一般在什麼時候會要用 explain 呢，大多數情況下都是從 mysql 的慢查詢日誌中揪出來一些查詢效率比較慢的 sql 來使用 explain 分析，也有的是就是在對 mysql 進行最佳化的時候，比如新增索引，透過 explain 來分析新增的索引能否被命中，還有的就是在業務開發的時候，在滿足需求的情況下，你可能需要透過 explain 來選擇一個更高效的 sql。
那麼 explain 該怎麼用呢，很簡單，直接在 sql 前面加上 explain 就行了，如下所示。
mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100332 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)
可以看到，explain 會傳回約 10 個欄位，不同版本傳回的欄位有些許差異，每個欄位都代表著具體的意義，這篇文章我不打算把每個欄位都詳細的介紹一遍，東西比較多，怕你也不容易記住，不如先把幾個重要的欄位好好理解了。
其中 type、key、rows、Extra 這幾個欄位我認為是比較重要的，我們接下來透過具體的實體來幫你更好的理解這幾個欄位的含義。
首先有必要簡單介紹下這幾個欄位的字面意思。
type 表示 mysql 訪問資料的方式，常見的有全表掃描（all）、遍歷索引（index）、區間查詢（range）、常量或等值查詢（ref、eq_ref）、主鍵等值查詢（const）、當表中只有一條記錄時（system）。下麵是效率從最好到最差的一個排序。
system > const > eq_ref > ref > range > index > all
key 表示查詢過程實際會用到的索引名稱。
rows 表示查詢過程中可能需要掃描的行數，這個資料不一定準確，是mysql 抽樣統計的一個資料。
Extra 表示一些額外的資訊，通常會顯示是否使用了索引，是否需要排序，是否會用到臨時表等。
好了，接下來正式開始實體分析。
還是沿用前面文章中建立的儲存引擎建立一個測試表，我們這裡插入 10 w 條測試資料，表結構如下：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
然後看下麵這條查詢陳述句，註意這個表目前只有一個主鍵索引，還沒有建立普通索引。
mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100332 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)
其中 type 值為 ALL，表示全表掃描了，大家註意看到 rows 這個欄位顯示有 100332 條，實際上我們一共才 10w 條資料，所以這個欄位只是 mysql 的一個預估，並不一定準確。這種全表掃描的效率非常低，是需要重點被最佳化的。
接下來我們分別給欄位 a 和 b 新增普通索引，然後再看下新增索引後的幾條 sql 。
mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t | 0 | PRIMARY | 1 | id | A | 100332 | NULL | NULL | | BTREE | | |
| t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | YES | BTREE | | |
| t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> explain select * from t where a > 1000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | ALL | a_index | NULL | NULL | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
上面這條 sql 看起來是不是有點疑惑呢，type 竟然顯示剛剛不是給欄位 a 新增索引了麼，而且 possible_keys 也顯示了有 a_index 可用，但是 key 顯示 null，表示 mysql 實際上並不會使用 a 索引，這是為啥？
這裡是因為 select * 的話還需要回到主鍵索引上查詢 b 欄位，這個過程叫回表，這條陳述句會篩選出 9w 條滿足條件的資料，也就是說這 9w 條資料都需要回表操作，全表掃描都才 10w 條資料，所以在 mysql 的最佳化器看來還不如直接全表掃描得了，至少還免去了回表過程了。
當然也不是說只要有回表操作就不會命中索引，用不用索引關鍵還在於 mysql 認為哪種查詢代價更低，我們把上面的 sql 中 where 條件再稍微改造一下。
mysql> explain select * from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
這回 type 值為 range 了，key 為 a_index ，表示命中了 a 索引，是一個不錯的選擇，是因為滿足這條 sql 條件的只有 1000 條資料，mysql 認為 1000 條資料就算回表也要比全表掃描的代價低，所以說 mysql 其實是個很聰明的傢伙。
我們還可以看到 Extra 欄位中值為 Using index condition，這個意思是指用到了索引，但是需要回表，再看下麵這個陳述句。
mysql> explain select a from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
這個 Extra 中的值為 Using where; Using index ，表示查詢用到了索引，且要查詢的欄位在索引中就能拿到，不需要回表，顯然這種效率比上面的要高，所以不要輕易寫 select * ，只查詢業務需要的欄位即可，這樣可以盡可能避免回表。
再來看一個需要排序的。
mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)
這個 Extra 中傳回了一個 Using filesort，意味著需要排序，這種是需要重點最佳化的的，也就是說查到資料後，還需要 mysql 在記憶體中對其進行排序，你要知道索引本身就是有序的，所以一般來講要儘量利用索引的有序性，比如像下麵這樣寫。
mysql> explain select a from t where a > 99990 order by a;
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t | range | a_index,ab_index | a_index | 5 | NULL | 10 | Using where; Using index |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
我們再建立一個複合索引看看。
mysql> alter table t add index ab_index(a,b);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t where a > 1000;
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| 1 | SIMPLE | t | range | a_index,ab_index | ab_index | 5 | NULL | 50166 | Using where; Using index |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
這條 sql 剛剛在上面也有講到過，在沒有建立複合索引的時候，是走的全表掃描，現在其實是利用了改寫索引，同樣是免去了回表過程，即在 （ab_index） 索引上就能找出要查詢的欄位。
這篇文章透過幾個實體介紹瞭如何使用 explain 分析一條 sql 的執行計劃，也提到了一些常見的索引最佳化，事實上還有更多的可能性，你也可以自己去寫一個 sql ，然後使用 explain 分析，看看有哪些是可以被最佳化的。
朋友會在“發現-看一看”看到你“在看”的內容