一、Explain 概述
使用 EXPLAIN 關鍵字可以模拟優化器來執行SQL查詢語句，進而知道MySQL是如何處理我們的SQL語句的。分析出查詢語句或是表結構的性能瓶頸。
1、MySQL查詢過程
通過explain我們可以獲得以下資訊：
- 表的讀取順序；
- 資料讀取操作的操作類型；
- 哪些索引可以被使用；
- 哪些索引真正被使用；
- 表的直接引用；
- 每張表的有多少行被優化器查詢了；
Explain使用方式: explain+sql語句, 通過執行explain可以獲得sql語句執行的相關資訊。
explain select * from L1; 
二、Explain 詳解
1) 資料準備
-- 建立資料庫
CREATE DATABASE test_explain CHARACTER SET 'utf8';
-- 建立表
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
-- 每張表插入3條資料
INSERT INTO L1(title) VALUES('zhang001'),('zhang002'),('zhang003');
INSERT INTO L2(title) VALUES('zhang004'),('zhang005'),('zhang006');
INSERT INTO L3(title) VALUES('zhang007'),('zhang008'),('zhang009');
INSERT INTO L4(title) VALUES('zhang010'),('zhang011'),('zhang012'); 2) ID字段說明
select查詢的序列号，包含一組數字，表示查詢中執行select子句或操作表的順序
id相同，執行順序由上至下
EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id; id不同，如果是子查詢，id的序号會遞增，id值越大優先級越高，越先被執行
EXPLAIN SELECT * FROM L2 WHERE id = ( SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'zhang')); 3) select_type和table字段說明
表示查詢類型，主要用于差別普通查詢，聯合查詢，子查詢等的複雜查詢
simple : 簡單的select查詢，查詢中不包含子查詢或者UNION
EXPLAIN SELECT * FROM L1 where id = 1; primary : 查詢中若包含任何複雜的子部分，最外層查詢被标記
subquery : 在select或where清單中包含了子查詢
EXPLAIN SELECT * FROM L2 WHERE id = ( SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'zhang')); union : union連接配接的兩個select查詢，第一個查詢是dervied派生表，除了第一個表外，第二個以後的表select_type都是union
derived : 在from清單中包含的子查詢被标記為derived（派生表），MySQL會遞歸執行這些子查詢，把結果放到臨時表中
union result : UNION 的結果
EXPLAIN SELECT * FROM (select * from L3 union select * from L4)a; 4) type字段說明
type字段在 MySQL 官網文檔描述如下：
The join type. For descriptions of the difffferent types.
type字段顯示的是連接配接類型 ( join type表示的是用什麼樣的方式來擷取資料)，它描述了找到所需資料所使用的掃描方式, 是較為重要的一個名額。
下面給出各種連接配接類型,按照從最佳類型到最壞類型進行排序:
-- 完整的連接配接類型比較多
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
-- 簡化之後,我們可以隻關注一下幾種
system > const > eq_ref > ref > range > index > ALL 一般來說,需要保證查詢至少達到 range級别,最好能到ref,否則就要就行SQL的優化調整
下面介紹type字段不同值表示的含義:
system: 表中就僅有一行資料的時候. 這是const連接配接類型的一個特例,很少出現。
const: const表示命中主鍵索引(primary key) 或者唯一索引(unique),表示通過索引一次就找到資料記錄。因為隻比對一條記錄,是以被連接配接的部分是一個常量。(如果将主鍵放在 where條件中, MySQL就能将該查詢轉換為一個常量) 這種類型非常快。
例如以下查詢:
explain select * from L1 where id = 3;
-- 為L1表的title字段添加唯一索引
alter table L1 add unique(title);
explain select * from L1 where title = 'zhang'; eq_ref : 對于前一個表中的每個一行，後表隻有一行被掃描。除了system和const類型之外，這是最好的連接配接類型。隻有當聯接使用索引的部分都是主鍵或惟一非空索引時，才會出現這種類型。
例如以下查詢：
EXPLAIN SELECT L1.id,L1.title FROM L1 left join L2 on L1.id = L2.id; ref : 非唯一性索引掃描(使用了普通索引), 對于前表的每一行(row)，後表可能有多于一行的資料被掃描,它傳回所有比對某個單獨值的行。
例如以下查詢:
-- 為L1表的title字段添加普通索引
alter table L1 add index idx_title (title) ;
EXPLAIN SELECT * FROM L1 inner join L2 on L1.title = L2.title; range : 索引上的範圍查詢,檢索給定範圍的行,between，in函數，> 都是典型的範圍(range)查詢。
例如以下查詢:
EXPLAIN SELECT * FROM L1 WHERE L1.id between 1 and 10; 注: 當in函數中的資料很大時,可能會導緻效率下降,最終不走索引
index : 出現index 是 SQL 使用了索引, 但是沒有通過索引進行過濾,需要掃描索引上的全部資料 (查找所有索引樹,比ALL快一些,因為索引檔案要比資料檔案小 ), 一般是使用了索引進行排序分組。
EXPLAIN SELECT * FROM L2 group by id order by id;
-- 該count查詢需要通過掃描索引上的全部資料來計數
EXPLAIN SELECT count(*) FROM L2; ALL : 沒有使用到任何索引, 連接配接查詢時對于前表的每一行，後表都要被全表掃描。
EXPLAIN SELECT * FROM L3 inner join L4 on L3.title = L4.title; 總結各類type類型的特點:
5) possible_keys 與 key說明
possible_keys
顯示可能應用到這張表上的索引, 一個或者多個. 查詢涉及到的字段上若存在索引, 則該索引将被列出, 但不一定被查詢實際使用.
key
實際使用的索引，若為null，則沒有使用到索引。（兩種可能，1.沒建立索引, 2.建立索引，但索引失效）。查詢中若使用了覆寫索引，則該索引僅出現在key清單中。
1. 理論上沒有使用索引,但實際上使用了
EXPLAIN SELECT L3.id FROM L3; 2. 理論和實際上都沒有使用索引
EXPLAIN SELECT * FROM L3 WHERE title = 'zhang007'; 3. 理論和實際上都使用了索引
EXPLAIN SELECT * FROM L2 WHERE title = 'zhang004'; 6) key_len字段說明
表示索引中使用的位元組數, 可以通過該列計算查詢中使用索引的長度。
key_len 字段能夠幫你檢查是否充分利用了索引， ken_len 越長, 說明索引使用的越充分。
建立表
CREATE TABLE L5(
a INT PRIMARY KEY,
b INT NOT NULL,
c INT DEFAULT NULL,
d CHAR(10) NOT NULL
); 使用explain 進行測試
EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1; 觀察key_len的值, 索引中隻包含了1列 是int類型 ,是以,key_len是4位元組。
為b字段添加索引,進行測試
ALTER TABLE L5 ADD INDEX idx_b(b);
-- 執行SQL,這次将b字段也作為條件
EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1; 為c、d字段添加聯合索引,然後進行測試
ALTER TABLE L5 ADD INDEX idx_c_b(c,d);
explain select * from L5 where c = 1 and d = ''; c字段是int類型 4個位元組, d字段是 char(10)代表的是10個字元相當30個位元組
資料庫的字元集是utf8 一個字元3個位元組,d字段是 char(10)代表的是10個字元相當30個位元組,多出的一個位元組用來表示是聯合索引
下面這個例子中,雖然使用了聯合索引,但是可以根據ken_len的長度推測出該聯合索引隻使用了一部分,沒有充分利用索引,還有優化空間。
explain select * from L5 where c = 1 ; 7) ref 字段說明
顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用于查找索引列上的值
L1.id='1'; 1是常量 , ref = const
EXPLAIN SELECT * FROM L1 WHERE L1.id=1; 8) rows 字段說明
表示MySQL根據表統計資訊及索引選用情況,估算的找到所需的記錄所需要讀取的行數。
L3中的title沒有添加索引, 是以L3中有3條記錄,就需要讀取3條記錄進行查找。
EXPLAIN SELECT * FROM L3,L4 WHERE L3.id = L4.id AND L3.title LIKE 'zhang007'; 需要注意的是 rows隻是一個估算值,并不準确 .是以rows行數過大的問題并不值得過多考慮，主要分析的還是索引是否使用正确了
9) fifiltered 字段說明
它指傳回結果的行占需要讀到的行(rows列的值)的百分比。
10) extra 字段說明
Extra 是 EXPLAIN 輸出中另外一個很重要的列，該列顯示MySQL在查詢過程中的一些詳細資訊。
準備資料
CREATE TABLE users (
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(20),
age INT(11)
);
INSERT INTO users VALUES(NULL, 'lisa',10);
INSERT INTO users VALUES(NULL, 'lisa',10);
INSERT INTO users VALUES(NULL, 'rose',11);
INSERT INTO users VALUES(NULL, 'jack', 12);
INSERT INTO users VALUES(NULL, 'sam', 13); Using fifilesort
執行結果Extra為 Using filesort ，這說明，得到所需結果集，需要對所有記錄進行檔案排序。這類SQL語句性能極差，需要進行優化。
典型的，在一個沒有建立索引的列上進行了order by，就會觸發fifilesort，常見的優化方案是，在order by的列上添加索引，避免每次查詢都全量排序。
EXPLAIN SELECT * FROM users ORDER BY age; Using temporary
表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢
EXPLAIN SELECT COUNT(*),uname FROM users GROUP BY uname; 需要注意的是：
- 傳回所有記錄的SQL，不使用where條件過濾資料，大機率不符合預期，對于這類SQL往往需要進行優化；
- 使用了where條件的SQL，并不代表不需要優化，往往需要配合explain結果中的type（連接配接類型）來綜合判斷。例如本例查詢的 age 未設定索引，是以傳回的type為ALL，仍有優化空間，可以建立索引優化查詢。
EXPLAIN SELECT * FROM users WHERE age=10; Using index
表示直接通路索引就能夠擷取到所需要的資料(覆寫索引) , 不需要通過索引回表。
-- 為uname建立索引
alter table users add index idx_uname(uname);
EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa'; Using join buffffer
使用了連接配接緩存, 會顯示join連接配接查詢時,MySQL選擇的查詢算法。
EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE age = 1)u2 ON u1.age = u2.age; Using join buffer (Block Nested Loop) 說明，需要進行嵌套循環計算, 這裡每個表都有五
條記錄，内外表查詢的type都為ALL。
問題在于 兩個關聯表的關聯使用了字段 age，并且age字段未建立索引，就會出現這種情況。
Using index condition
查找使用了索引 (但是隻使用了一部分,一般是指聯合索引)，但是需要回表查詢數.
explain select * from L5 where c > 10 and d = ''; Extra主要名額的含義(有時會同時出現)
- using index ：使用覆寫索引的時候就會出現；
- using where ：在查找使用索引的情況下，需要回表去查詢所需的資料；
- using index condition ：查找使用了索引，但是需要回表查詢資料；
- using index & using where ：查找使用了索引，但是需要的資料都在索引列中能找到，是以不需要回表查詢資料；