一、閱讀前
其實錄制了一個視訊專門講解這篇文章，視訊中講的比下文還全哦
視訊連結：
https://mp.weixin.qq.com/s/wGcODm6gfy6JOvkcKp-8MA
https://mp.weixin.qq.com/s/wGcODm6gfy6JOvkcKp-8MA
Oow！
想白嫖explain實戰部分用到的建庫SQL、儲存過程等指令碼可以通過上面的視訊連結關注，後臺回覆：explain即可領取哦～
二、explain 實戰
2.1、初識執行計劃：
獲取sql執行計劃的語法：explain yoursql
explain select * from t1;
| 欄位名 | 作用 |
|---|---|
| id | sql中的每一個select都有一個未對應的id，對子查詢來說，有多個select，就有多個id。 |
| select_type | sql執行計劃對應的查詢型別。如： 1.針對單表查詢或者是多表連線查詢的select type是simple。 2.union語句針對前半部分sql的select_type為primary，針對後半部分sql的select_type為union，做去重時的select_type是union result。3.sql中出現子查詢時，外層的select_type為primary，內層的select_type一般為subquery |
| table | 你的sql要查詢哪個表 |
| partitions | 分割槽表 |
| type | 針對該表查詢時的查詢方式。如： 1.聚簇索引的const 2.二級索引的查詢ref（ref、eq_ref、ref_or_null）、range 3.對二級索引的全表掃描index 4.對聚簇索引的全表掃描的all |
| possible_keys | 有哪些索引可以選擇 |
| key | 實際選擇的索引 |
| key_len | 索引的長度 |
| ref | 和上面選中key進行比較時，是等值匹配（const）還是其他的欄位（庫名.表名.列名） |
| rows | 估算的可能會讀取的資料條數 |
| filtered | 過濾比例，真實資料*過濾比例為預計將讀取出來的資料 |
| extra | 額外的說明資料，如： 1.sql中有where條件時，Extra為：Using Where 2.sql中使用二級索引時，Extra為：Using Index 3.sql中有join語句多表關聯時：Extra為：Using join buffer(Block Nested Loop) 4.sql操作產生臨時表時，Extra為：Using temporary 5.sql需要根據某個欄位排序，且記憶體不夠時（不管是不是索引）：Extra為：Using filesort |
2.2、分析聯表SQL的執行計劃
SQL如下：
mysql> explain select * from t1 join t2;
SQL執行時，會先將驅動表t1中的資料以全表掃描的方式檢索出來放在記憶體中，一共檢索4行。然後在將t2表中的資料檢索出來，和t1中的資料join在一起作為返回值。由於我們沒有加任何where條件，這裡還會存在一個笛卡爾積，也就是說結果中會有16條資料
執行計劃解析：
id：每一個select 關鍵字對應一個id，這條SQL中只有一個select，所以這兩行執行計劃的id都是1
select_type：均是simple 簡單的查詢方式。
table：查詢了哪張表
Partitions：分割槽
type：ALL表示全表掃描
possible_keys：可能使用到的索引，null表示，沒有任何索引 key：null表示實際上也沒有使用到索引
key_len：最長的索引的長度
ref：當你使用到索引時，索引列是等值匹配還是其他的連線方式，由於我們都沒有索引，所以直接為null
rows：估算的掃描行數
filterd：過濾的比例，實際數量*過濾比例 ≈ 本次查詢返回的行數
Extra：其他的資訊
2.3、分析子查詢SQL的執行計劃
SQL如下：
explain select * from t1 where x1 in (select x1 from t2) or x3 = 890;
id：有兩個select 語言，所以執行計劃的id有兩個
table、partitions 不再贅述
select_type：第一行查詢語句的查詢型別是Primary，主查詢。第二條查詢SQL的型別是Subquery，自查詢。
對於主查詢而言，where條件中有or x3 = 123 （x3我們建立了索引）說明他有可用的索引：t1_x3_index 。但是它最終並沒有使用這個索引，它使用的全表掃描ALL的查詢方式。所以對應的key_len（最長的索引長度為null）。預估全表掃描出9987條資料。
主查詢之所以有x3索引卻不用，是因為MYSQL認為，使用這個索引和不用索引的層本擦不多。
對於自查詢來說，它的查詢型別是自查詢。它選擇使用t2表的x1這個二級索引，最長的索引長度為515byte，預估掃描14948條資料。
2.4、分析union SQL的執行計劃
# 聯合t1、t2並對union的結果進行去重！
explain select * from t1 union select * from t2;
同樣執行計劃有有3行
針對t1的查詢為主查詢，因為sql中沒有任何查詢條件，所以Extra、possible_key、key都為NULL，並且預計全表掃描9987行資料。
第二行不再贅述。
第三行的查詢型別為：Union result，針對表<union1,2>進行操作，Extra中的Using tmporary表明這是一個臨時表。也就是說，結果集放到臨時表中進行去重。
2.5、分析複雜SQL的執行計劃
# 查詢x1列重複次數超過1次以上的x1列以及它的重複次數。
explain select * from (select x1,count(*) as cnt from t1 group by x1) as _t1 where cnt > 1;
先看id為2的執行計劃，它是針對t1表的查詢，並且最終選擇索引：t1_x1_index。注意它的select_type是Derived表示派生，意思是它的查詢結果是會被物化成一個臨時表給外層的sql使用。
再看外層的id為1的SQL，它是型別為primary的主查詢，查詢的自查詢生成的臨時表<drived2> 查詢的方式是全表掃描。
2.6、常見的執行計劃的type
2.6.1、const
查詢聚簇索引
explain select * from t1 where id = 5000;
查詢唯一的二級索引，執行計劃的type同樣是const
# t3.x1 是unique key
explain select * from t3 where x1 = 'qweqwe';
2.6.2、ref
ref 對非唯一的二級索引進行檢索
mysql> explain select * from t1 where x1 = 'ucshiuhdaiusd';
多個二級索引的等值匹配的type也是ref
mysql> explain select * from t1 where x1 = 'ucshiuhdaiusd' and x3 = 'qdasdsadas';
使用普通索引的做join操作，被join的表的查詢type也是ref
mysql> explain select * from t1 inner join t2 on t1.x1 = t2.x1;
2.6.3、eq_ref
eq_ref 使用主鍵進行join，被join的表的查詢type為eq_ref
mysql> explain select * from t1 inner join t2 on t1.id = t2.id;
2.6.4、eq_or_null
對普通二級索引進行檢索，並且二級索引允許存在null的情況，那麼查詢計劃的type為eq_or_null
mysql> explain select * from t1 where x1 = 'ucshiuhdaiusd' or x1 is null;
2.6.5、range
基於二級索引進行對一個範圍進行檢索，查詢型別為：range
mysql> explain select * from t1 where id > 5000;
2.6.6、index
type 為 index 型別的執行計劃
# t3表中有3個索引，如下：
# id:聚簇索引
# x1:唯一的二級索引
# x1_x3_x2:聯合索引
explain select * from t3 where x2 = 'fdc1a9f7d94ece2b68b7d3e3be1b0f3b';
可以看到，x2列沒有單獨的索引。但是sql的執行計劃選擇去聯合索引樹中掃全表，也不會去聚簇索引中全表掃描
補充：索引的選擇邏輯
Case1: 比如現有索引：KEY(x1,x3)、KEY(x2,x4)
SQL如下：
select * from t where x1=xxx and x2>yyy;
那麼問題來了，x1、x2都有對應的索引，那MYSQL該如何選擇索引呢？
一般來說MYSQL會優先選擇一個掃描行數少的KEY，作為最終的索引，比如x1是等值條件，x2是range條件，所以最終大概率是通過x1索引查詢一次取id，再去回表找到x2列，使用x2>yyy的條件進行過濾。
Case2:在執行SQL語句時有可能會同時查詢多個索引。
比如：現有索引：KEY(x1)、KEY(x2)
SQL如下：
select * from table where x1=xxx and x2=yyy;
那它的執行計劃可能就是：先從x1索引數中取出x1=xxx的資料行，再從x2的索引樹中取出x2=yyy的資料行，這兩部分資料根據主鍵進行一次交集，再使用intersection交集後的結果去聚簇索引中回表。
之前的例子中說的是，在x1和x2這兩個索引中優先選一個掃描行數少的索引，先使用它查詢，在拿著查詢到的結果去回表。
那，之所以示例9中的情況會出現，是因為可能存在如下的情況：
（掃描x1的行數+掃描x2的行數）+ merge之後的行數 < 掃描x1或者x2的最少行數+回表的行數
此外：如果想讓一個SQL使用多個索引，也有硬性的條件：
- 如果使用聯合索引，那聯合索引中的每一個欄位都需要出現在sql中，且必須是等值匹配。
- 通過主鍵查詢+其他二級索引的等值匹配，也可能做一次多索引查詢做交集後再回表。
補充：如果你的SQL如下：
select * from table where x1=xxx or x2=yyy;
依然有可能使用多個索引，然後對多個索引的結果取union並集
Case3:如下SQL
select * from t1,t2 where t1.x1 = xxx and t1.x2 = t2.x2 and t2.x3 = yyy;
sql中的有t1、t2兩個表，前面的t1為驅動表，後面的t2為被驅動表。
sql中的t1.x2 = t2.x2為關聯條件。
SQL在執行時會先使用t1.x1=xxx為條件先撈出一部分資料，此次操作會根據x1的索引情況而不同，可能是const、ref、index或者全表掃描all。
假設第一次根據x1=xxx條件找到了2行資料（稱這兩行資料為A）。接下來會分別拿著這兩行資料中的x2為新的條件，去t2表中查詢，假設第一行資料的x2列值為123。他就會使用t2.x2=123 and t2.x3=yyy為新的條件在t2表找到匹配到的資料，和第一行資料關聯起來。
補充：
這種聯表查詢的方式也叫做內連線，比如t2中有3行符合
x2=123，那麼它們都會被和A中的第一行拼接起來，作為最終的返回值。如果t2表中沒有找到t2.x2=123的列，那麼A中的第一行資料也不會被保留作為最終的返回值。
那如果不想因為t2.x2中沒有符合條件的列也將A中的第一行保留下來，那麼可以使用外連線實現。如 left join on + 條件
接著處理A中的第二行資料。
2.7、詳解ref列
Case1 ref之: 庫名.表名.列名
explain select * from t1 inner join t2 on t1.id = t2.id;
當執行第二個查詢計劃時，對主鍵ID進行等值匹配，而且是使用test.t1.id 來和ID進行等值匹配（而不是某個常量）
Case2 ref之:const
explain select * from t1 where x1 = 'qwdasdas';
2.8、Extra 列
關於這一列上面其實提到過了
1、Using where
explain select * from t1 where x1 = 'ucshiuhdaiusd' and x3 = 'qdasdsadas';
2、Using Index
explain select x1 from t1;
3、Using filesort ,其中的x1是二級索引，x2是普通索引。
4、Using temporary，表示：使用了中間表
Notice，當你的sql中針對非索引的列進行 group by、distinct、union操作時，它都會通過一張中間表去完成指定操作。而如果對索引列進行group by、distinct、union時，會直接使用索引完成我們的操作。
Case1:
# x1是二級索引
mysql> explain select distinct x1 from t1;
# x2是普通列
mysql> explain select distinct x2 from t1;
Case2:
# x1是索引列
mysql> explain select x1,count(*) from t1 group by x1;
# x2是普通列
explain select x2,count(*) from t1 group by x2;
三、成本計算
SQL的執行是有成本的，比如讀取磁碟的操作遠比記憶體中的操作高的多。而執行計劃的作用就是選擇一個低成本執行方式去執行我們的SQL。
下面看一種簡單的計算SQL執行成本的方式：
通過如上面的命令可以得到mysql替我們維護的表的統計資料。
其中的rows並不準確，為估算值。data_length/1024/16 為資料頁的數量。
全表掃描的成本計算方法如下：
SQL的執行成本 約為 資料頁的數量*1.0 + 資料行數*0.2
四、MySQL會改寫你的SQL
如果MYSQL認為你的SQL寫的不是很好，它會改寫優化你的SQL，目的是為了更好的分析得到最優執行計劃。
Case1： mysql會去除sql中多餘的括號
Case2： 常量替換，如果 `where i=5 and jCase3： 對於如下SQL：
select * from t1 join t2 where t1.x1 = t2.x2 and t1.id = 1
比如t1表中有id、x1、x2、x3四列
MySQL的可能按照如下的順序執行
1.反正t1表中就有1行資料符合預期，那先執行如下SQL，將資料查詢出來
select * from t1 where id = 1;
比如查詢結果如下
id x1 x2 x3
1 2 3 4
2.使用第一步中查詢出來的值，當做常量替換、改寫原始sql
select 1,2,3,4,t2.* from t1 join t2 where t2.x2 = 2;
Case4: 如下SQL：
select * from t1 where x1 = (select x1 from t2 where id = xxx);
MySQL執行該SQL時，首先會執行子查詢語句，先根據id找到一條資料，取出其x1的值，作為外層sql的常量。然後再執行外層SQL
Case5: ```bash select * from t1 where x1 = (select x1 from t2 where t2.x2 = t1.x2); ```
子查詢中的條件又依賴t1表的x2列的值，所以想執行該SQL，就得先遍歷t1表，將每一行t1中的x2的值放入到子查詢中作為條件，得到子查詢的x1後再當做常量作為外層查詢的條件。
Case6: 對in語句的優化，如下SQL：
select * from t1 where x1 in (select x1 from t1 where x2 = xxx);
假設t1、t2中各有10萬條資料。
MySQL可能會將SQL的執行流程優化成下面這樣：
Step1：先執行子查詢語句，假設我們從10w條資料中過濾出500條。
Step2：基於memory儲存引擎，再記憶體中，將過濾出的500條資料寫入臨時表，也叫做物化表，併為他建立索引。如果資料很多記憶體不夠用，也可能以普通B+Tree的方式將其放在磁碟中。
Step3：遍歷這個臨時表，並將遍歷出的x1的值作為條件放在外層sql中，這樣就避免了遍歷外層sql時需要的10萬次遍歷了！