最近有個需求，要修改現有儲存結構，涉及查詢條件和查詢效率的考量，看了幾篇索引和HBase相關的文章，回憶了相關知識，結合專案需求，說說自己的理解和總結。
前2篇介紹了索引的優點、索引結構的演化過程以及SQL的執行過程，重點分析了SQL的執行順序和資料的定位過程，錯過的朋友可以先回顧下：
這篇進入正題，介紹如何檢視和分析SQL執行情況、排查SQL的效能問題，通過本篇介紹，你會了解到：
- explain命令概述
- select_type欄位詳細介紹
- type欄位詳細介紹
- extra欄位詳細介紹
部分內容摘錄了幾個博友的文章，最後會給出文章連結，感謝他們的精彩分析。
explain命令概述
工作中，MySQL會記錄執行時間比較久的SQL語句，找出這些SQL語句是第一步，重要的是檢視SQL語句的執行計劃，對於MySQL執行計劃的獲取，可以通過explain方式來檢視，這條命令的輸出結果能夠讓我們瞭解MySQL優化器是如何執行SQL語句的。
MySQL優化器是基於開銷來工作的，是在每條SQL語句執行的時候動態地計算出來的，命令用法十分簡單, 在 SELECT 語句前加上 Explain 就可以了。
先來個示例
以基本的員工表為例，表的結構如下：
mysql> show create table employee \G;
*************************** 1. row ***************************
Table: mcc_employee
Create Table: CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userId` varchar(50) DEFAULT NULL COMMENT '員工編號',
`userName` varchar(50) DEFAULT NULL COMMENT '員工名稱',
`nickName` varchar(50) DEFAULT NULL COMMENT '暱稱',
`gender` varchar(10) DEFAULT NULL COMMENT '性別',
`mobilePhone` varchar(20) DEFAULT NULL COMMENT '手機號',
`miliao` varchar(100) DEFAULT NULL COMMENT '米聊號',
`email` varchar(100) DEFAULT NULL COMMENT '郵箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
複製程式碼一個簡單的查詢：
mysql> explain select * from employee where id =1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
複製程式碼select_type為simple說明是一個普通的查詢，不包含子查詢和union查詢。
欄位概述
id欄位：select查詢的識別符號. 每個select都會自動分配一個唯一的識別符號，id數值越大的優先執行，id相同的從上往下順序執行。
select_type：select查詢的型別，當沒有子查詢或union查詢時為simple，有子查詢或union查詢時，有幾種情況，後面會詳細介紹。
table：標識查詢的是哪個表，顯示這一行的資料是關於哪張表的，有時不是真實的表名字,看到的是derived（n是個數字，為id欄位）
mysql> explain select * from (select * from (select * from employee where id =76) table1 ) table2 ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 3 | DERIVED | mcc_inform | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
複製程式碼type：資料訪問、讀取操作型別，對效能影響比較大，後面會詳細介紹。
possible_keys：此次查詢中可能選用的索引，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用。
key：此次查詢中確切使用到的索引，如果沒有選擇索引，鍵是NULL。
key_len：表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度。
ref: 哪個欄位或常數與key一起被使用。
rows: 此查詢一共掃描了多少行，這個是一個估計值。
filtered: 表示此查詢條件所過濾的資料的百分比。
extra: 額外的資訊，後面會詳細介紹。
select_type欄位詳細介紹
表示查詢的型別，是簡單查詢或複雜查詢，如果是複雜查詢，包含SIMPLE、SIMPLE、UNION、UNION RESULT、SUBQUERY、DEPENDENT、DEPENDENT UNION、DEPENDENT SUBQUERY、DERIVED等，瞭解這些，可以識別在執行那部分。
SIMPLE
簡單select，不使用union或子查詢等:
mysql> explain select * from employee where id =1 ;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
複製程式碼PRIMARY
如果是複雜查詢，表示是最外層的select：
mysql> explain select * from (select * from employee where id =1) a ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
複製程式碼UNION & UNION RESULT
UNION中的第二個或後面的SELECT語句，UNION RESULT為UNION的結果：
mysql> explain select * from employee where id =1 union all select * from employee where id=2;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1 | PRIMARY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
| 2 | UNION | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
複製程式碼SUBQUERY
子查詢中的第一個SELECT：
mysql> explain select * from employee where id = (select id from employee where id =1);
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
| 2 | SUBQUERY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
複製程式碼DEPENDENT UNION & DEPENDENT SUBQUERY
DEPENDENT UNION，UNION中的第二個或後面的SELECT語句，但結果取決於外面的查詢； DEPENDENT SUBQUERY，子查詢中的第一個SELECT，但結果取決於外面的查詢：
mysql> explain select * from employee where id in (select id from employee where id =1 union all select id from employee where id=2);
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1 | PRIMARY | employee | ALL | NULL | NULL | NULL | NULL | 26 | Using where |
| 2 | DEPENDENT SUBQUERY | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 3 | DEPENDENT UNION | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
複製程式碼DERIVED
派生表的SELECT，FROM子句的子查詢：
mysql> explain select * from (select * from employee where id =1) a ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
複製程式碼type欄位詳細介紹
type表示資料訪問/讀取的操作型別，顯示了連線使用了哪種類別,有無使用索引，它提供了判斷查詢是否高效的重要依據依據。
常見的型別有常用有，效能從差到好排序：ALL, index, range, ref, eq_ref, const, system, NULL
NULL
不用訪問表或者索引就可以直接得到結果：
mysql> explain select sysdate();
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
複製程式碼const、system
如將主鍵或者唯一索引置於where列表中，MySQL就能將該查詢轉換為一個常量，當表中只有一行記錄時，型別為system。
mysql> explain select * from (select id from mcc_inform where id =1) a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | employee | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
複製程式碼eq_ref
此型別通常出現在多表的 join 查詢, 表示對於前表的每一個結果, 都只能匹配到後表的一行結果. 並且查詢的比較操作通常是 =, 查詢效率較高：
mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
複製程式碼ref
與eq_ref類似，不同的是ref不是唯一索引，此型別通常出現在多表的 join 查詢, 針對於非唯一或非主鍵索引, 或者是使用了最左字首規則索引的查詢，可以用於使用=或<=>操作符的帶索引的列：
index_merge
該聯接型別表示使用了索引合併優化方法， where中可能有多個條件(或者join)涉及到多個欄位，它們之間進行 AND 或者 OR，那麼此時就有可能會使用到 index merge 技術。
index merge 技術如果簡單的說，其實就是：對多個索引分別進行條件掃描，然後將它們各自的結果進行合併(intersect/union)。
range
表示使用索引範圍查詢, 通過索引欄位範圍獲取表中部分資料記錄， 這個型別通常出現在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中
index
表示全索引掃描(full index scan), 和 ALL 型別類似, 只不過 ALL 型別是全表掃描, 而 index 型別則僅僅掃描所有的索引, 而不掃描資料。
ALL
表示全表掃描, 這個型別的查詢是效能最差的查詢之一，一般不會出現。
extra欄位詳細介紹
EXplain中的很多額外的資訊會在Extra欄位顯示，此欄位能夠給出讓我們深入理解執行計劃進一步的細節資訊，介紹下常見的幾個。
Using where
在查詢使用索引的情況下，需要回表去查詢所需的資料。
Using index
表示查詢在索引樹中就可查詢所需資料, 不用掃描表資料檔案, 說明效能不錯。
Using filesort
當SQL中包含ORDER BY 操作，而且無法利用索引完成排序操作的時候，查詢優化器不得不選擇相應的排序演算法來實現。
filesort主要用於查詢資料結果集的排序操作，首先MySQL會使用sort_buffer_size大小的記憶體進行排序，如果結果集超過了sort_buffer_size大小，會把這一個排序後的chunk轉移到file上，最後使用多路歸併排序完成所有資料的排序操作。
filesort只能應用在單個表上，如果有多個表的資料需要排序，那麼MySQL會先使用using temporary儲存臨時資料，然後再在臨時表上使用filesort進行排序，最後輸出結果。
Using temporary
查詢有使用臨時表, 一般出現於排序, 分組和多表join的情況, 查詢效率不高, 建議優化.
下一篇文章會介紹索引優化原則以及案例分析。
參考文章：
歡迎掃描下方二維碼，關注我的個人微信公眾號，檢視更多文章 ~