什么是MySQL的執(zhí)行計(jì)劃（Explain關(guān)鍵字）？
本文作者王良辰，京東中臺(tái)架構(gòu)師，擅長(zhǎng)分布式系統(tǒng)及高可用、高并發(fā)系統(tǒng)架構(gòu)與設(shè)計(jì)。曾經(jīng)為企業(yè)開(kāi)發(fā)過(guò)多個(gè)通用腳手架，推崇以技術(shù)手段提升開(kāi)發(fā)效率、約束開(kāi)發(fā)行為。
什么是Explain
Explain被稱為執(zhí)行計(jì)劃，在語(yǔ)句之前增加 explain 關(guān)鍵字，MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記，模擬MySQL優(yōu)化器來(lái)執(zhí)行SQL語(yǔ)句，執(zhí)行查詢時(shí)，會(huì)返回執(zhí)行計(jì)劃的信息，并不執(zhí)行這條SQL。（注意，如果 from 中包含子查詢，仍會(huì)執(zhí)行該子查詢，將結(jié)果放入臨時(shí)表中）。
Explain可以用來(lái)分析SQL語(yǔ)句和表結(jié)構(gòu)的性能瓶頸。通過(guò)explain的結(jié)果，可以了解到如數(shù)據(jù)表的查詢順序、數(shù)據(jù)查詢操作的操作類型、哪些索引可以被命中、哪些索引實(shí)際會(huì)命中、每個(gè)數(shù)據(jù)表有多少行記錄被查詢等信息。
Explain命令擴(kuò)展
explain extended
在explain的基礎(chǔ)上提供一些額外的查詢信息，在explian extended執(zhí)行以后，通過(guò)show warnings命令可以得到優(yōu)化后的查詢語(yǔ)句，可以看出優(yōu)化器做了哪些工作，還可以通過(guò)某些數(shù)據(jù)估算表連接的行數(shù)。
explain partitions
用于分析使用了分區(qū)的表，會(huì)顯示出可能用到的分區(qū)。
兩點(diǎn)重要提示
1. Explain結(jié)果是基于數(shù)據(jù)表中現(xiàn)有數(shù)據(jù)的。
2. Explain結(jié)果與MySQL版本有很大的關(guān)系，不同版本的優(yōu)化器的優(yōu)化策略不同。
?
本文示例使用的數(shù)據(jù)庫(kù)表
Explain命令(關(guān)鍵字)
explain簡(jiǎn)單示例
mysql>explain select * from t_user;
在查詢中的每個(gè)”表”會(huì)輸出一行，這里的“表”的意義非常廣泛，不僅僅是數(shù)據(jù)庫(kù)表，還可以是子查詢、一個(gè)union 結(jié)果等。
explain結(jié)果列說(shuō)明
【id列】
id列是一個(gè)有順序的編號(hào)，是查詢的順序號(hào)，有幾個(gè) select 就顯示幾行。id的順序是按 select 出現(xiàn)的順序增長(zhǎng)的。id列的值越大執(zhí)行優(yōu)先級(jí)越高越先執(zhí)行，id列的值相同則從上往下執(zhí)行，id列的值為NULL最后執(zhí)行。
【select_type列】
select_type列的值標(biāo)明查詢的類型：
1）simple：表明當(dāng)前行對(duì)應(yīng)的select是簡(jiǎn)單查詢，不包含子查詢和union
2）primary：表明當(dāng)前行對(duì)應(yīng)的select是復(fù)雜查詢中最外層的 select
3）subquery：表明當(dāng)前行對(duì)應(yīng)的select是包含在 select 中的子查詢（不在 from 子句中）
4）derived：表明當(dāng)前行對(duì)應(yīng)的select是包含在 from 子句中的子查詢。
MySQL會(huì)創(chuàng)建一個(gè)臨時(shí)表來(lái)存放子查詢的查詢結(jié)果。用如下的語(yǔ)句示例說(shuō)明：
explain select (select 1 fromt_user where user_id=1) from (select * from t_group where group_id=1) tmp;
*注意，在資料收集過(guò)程中，發(fā)現(xiàn)不同版本的MySQL表現(xiàn)不一致，經(jīng)反復(fù)對(duì)比，5.7及以后版本的輸出如下：
很顯然，MySQL在這方面進(jìn)行了優(yōu)化.
*注意，MySQL不同版本Explain表現(xiàn)差異很大，有些場(chǎng)景，從語(yǔ)句層面看，是要使用到索引，但經(jīng)過(guò)優(yōu)化器分析，結(jié)合表中現(xiàn)有數(shù)據(jù)，如果MySQL認(rèn)為全表掃描性能更優(yōu)，則會(huì)使用全表掃描。
5）union：表明當(dāng)前行對(duì)應(yīng)的select是在 union 中的第二個(gè)和隨后的 select
6）union result：表明當(dāng)前行對(duì)應(yīng)的select是從 union 臨時(shí)表檢索結(jié)果的 select
explain select 1 union all select 2 fromdual;
?????? MySQL5.7及以后同樣做了優(yōu)化
【table列】
table列的結(jié)果表明當(dāng)前行對(duì)應(yīng)的select正在訪問(wèn)哪個(gè)表。當(dāng)查詢的
【type列】
type列的結(jié)果表明當(dāng)前行對(duì)應(yīng)的select的關(guān)聯(lián)類型或訪問(wèn)類型，也就是優(yōu)化器決定怎么查找數(shù)據(jù)表中的行，以及查找數(shù)據(jù)行記錄的大概范圍。該列的取值優(yōu)化程度的優(yōu)劣，從最優(yōu)到最差依次為：null>system> const > eq_ref > ref > range > index > ALL。一般來(lái)說(shuō)，要保證查詢達(dá)到range級(jí)別，最好達(dá)到ref。
1）null，MySQL優(yōu)化器在優(yōu)化階段分解查詢語(yǔ)句，在優(yōu)化過(guò)程中就已經(jīng)可以得到結(jié)果，那么在執(zhí)行階段就不用再訪問(wèn)表或索引。
explain select min(user_id) from t_user;
這時(shí)的函數(shù)min，在索引列user_id中選取最小值，可以直接查找索引來(lái)完成，不需要執(zhí)行時(shí)再訪問(wèn)數(shù)據(jù)表。
2）const和system：const出現(xiàn)在用 primary key（主鍵） 或 unique key（唯一鍵） 的所有列與常數(shù)比較時(shí)，優(yōu)化器對(duì)查詢進(jìn)行優(yōu)化并將其部分查詢轉(zhuǎn)化成一個(gè)常量。最多有一個(gè)匹配行，讀取1次，速度非?？臁６鴖ystem是const的特例，表中數(shù)據(jù)只有一條匹配時(shí)為system。此時(shí)可以用explain extended+show warnings查看執(zhí)行結(jié)果。
explain extended select * from (select * from t_user where user_id = 1) tmp;
show warnings;
MySQL5.7及以后版本優(yōu)化后：
3）eq_ref：primary key（主鍵）或 unique key（唯一鍵） 索引的所有構(gòu)成部分被join使用 ，只會(huì)返回一條符合條件的數(shù)據(jù)行。這是僅次于const的連接類型。
explain select * from t_group_user gu left join t_group g ong.group_id = gu.group_id;
4) ref：與eq_ref相比，ref類型不是使用primary key（主鍵） 或 unique key（唯一鍵）等唯一索引，而是使用普通索引或者聯(lián)合唯一性索引的部分前綴，索引和某個(gè)值相比較，可能會(huì)找到符合條件的多個(gè)數(shù)據(jù)行。
1. 如下示例，使用的group_name是普通索引
explain select * from t_group where group_name= 'group1';
2.關(guān)聯(lián)表查詢
explain select g.group_id from t_group gleft join t_group_user gu on gu.group_id = g.group_id;
5）range：出現(xiàn)在 in(),between ,> ,<, >= 等操作符中。使用一個(gè)索引來(lái)查詢給定范圍的行。
6）index：掃描全表索引（index是從索引中讀取的,所有字段都有索引，而all是從硬盤(pán)中讀?。華LL要快。
explain select * from t_group;
7）all：即全表掃描，需要從頭到尾去查找所需要的行。一般這種情況下這需要增加索引來(lái)進(jìn)行查詢優(yōu)化了
explain select * from t_user;
【possible_keys列】
這一列的結(jié)果表明查詢可能使用到哪些索引。但有些時(shí)候也會(huì)出現(xiàn)出現(xiàn)possible_keys 列有結(jié)果，而 后面的key列顯示 null 的情況，這是因?yàn)榇藭r(shí)表中數(shù)據(jù)不多，優(yōu)化器認(rèn)為查詢索引對(duì)查詢幫助不大，所以沒(méi)有走索引查詢而是進(jìn)行了全表掃描。?
如果possible_keys列的結(jié)果是null，則表明沒(méi)有相關(guān)的索引。這時(shí)，可以通過(guò)優(yōu)化where子句，增加恰當(dāng)?shù)乃饕齺?lái)提升查詢性能。
【key列】
這一列表明優(yōu)化器實(shí)際采用哪個(gè)索引來(lái)優(yōu)化對(duì)該表的訪問(wèn)。如果沒(méi)有使用索引，則該列是 null。
【key_len列】
這一列表明了在索引里使用的字節(jié)數(shù)，通過(guò)這個(gè)值可以大致估算出具體使用了聯(lián)合索引中的前幾個(gè)列。?
key_len計(jì)算規(guī)則這里不再贅述，不同的數(shù)據(jù)類型所占的字節(jié)數(shù)是不一致的。
【ref列】
這一列表明了在key列記錄的索引中，表查找值所用到的列或常量，常見(jiàn)的有：const（常量），字段名，如user.user_id
【rows列】
這一列表明優(yōu)化器大概要讀取并檢測(cè)的行數(shù)。跟實(shí)際的數(shù)據(jù)行數(shù)大部分情況是不一致的。
【Extra列】
顧名思義，這一列表明的是額外信息,這一列的取值對(duì)優(yōu)化SQL非常有參考意義。常見(jiàn)的重要取值如下：?
1）using index：所有被查詢的字段都是索引列(稱為覆蓋索引),并且where條件是索引的前導(dǎo)列，出現(xiàn)這樣的結(jié)果，是性能高的表現(xiàn)。
explainselect group_id,group_name from t_group;
2）using where：被查詢的列未被索引覆蓋，where條件也并非索引的前導(dǎo)列，表示 MySQL 執(zhí)行器從存儲(chǔ)引擎接收到查詢數(shù)據(jù),再進(jìn)行“后過(guò)濾”（Post-filter）。所謂“后過(guò)濾”，就是先讀取整行數(shù)據(jù)，再檢查此行是否符合 where 句的條件，符合就留下，不符合便丟棄。
explain select * from t_user whereuser_name='user1';
3）using where Using index：被查詢的列被索引覆蓋，并且where條件是索引列之一但是不是索引的前導(dǎo)列，也就是沒(méi)有辦法直接通過(guò)索引來(lái)查詢到符合條件的數(shù)據(jù)
explain select * from t_group where group_name = 'group1';
4）null：被查詢的列沒(méi)有被索引覆蓋，但where條件是索引的前導(dǎo)列，此時(shí)用到了索引，但是部分列未被索引覆蓋，必須通過(guò)“回表查詢”來(lái)實(shí)現(xiàn)，不是純粹地用到了索引，也不是完全沒(méi)用到索引
explain select * from t_user where user_id='1';
5）using index condition：與using where類似，查詢的列不完全被索引覆蓋，where條件中是一個(gè)前導(dǎo)列的范圍；這種情況未能通過(guò)示例顯現(xiàn)，可能跟MySQL版本有關(guān)系。
6） using temporary：這表明需要通過(guò)創(chuàng)建臨時(shí)表來(lái)處理查詢。出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的，用索引來(lái)優(yōu)化。創(chuàng)建臨時(shí)表的情況：distinct，group by，orderby，子查詢等
explain select distinct user_name from t_user;
explain select distinct group_name fromt_group; --group_name是索引列
7) usingfilesort：在使用order by的情況下出現(xiàn)，mysql會(huì)對(duì)結(jié)果使用一個(gè)外部索引排序，而不是按索引次序從表里讀取行。此時(shí)mysql會(huì)根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字并按順序檢索行信息。這種情況下要考慮使用索引來(lái)優(yōu)化的。
explain select * from t_user orderby user_name;
explain select * from t_group order bygroup_name;? --group_name是索引列
查詢優(yōu)化建議
結(jié)合前面的描述，首先看 type列的結(jié)果，如果有類型是 all 時(shí)，表示預(yù)計(jì)會(huì)進(jìn)行全表掃描（fulltable scan）。通常全表掃描的代價(jià)是比較大的，建議創(chuàng)建適當(dāng)?shù)乃饕?，通過(guò)索引檢索避免全表掃描。
再來(lái)看下 Extra 列的結(jié)果，如果有出現(xiàn) Using temporary 或者 Using filesort 則要多加關(guān)注：
Using temporary，表示需要?jiǎng)?chuàng)建臨時(shí)表以滿足需求，通常是因?yàn)镚ROUP BY的列沒(méi)有索引，或者GROUP BY和ORDER BY的列不一樣，也需要?jiǎng)?chuàng)建臨時(shí)表，建議添加適當(dāng)?shù)乃饕?/span>
Using filesort，表示無(wú)法利用索引完成排序，也有可能是因?yàn)槎啾磉B接時(shí)，排序字段不是驅(qū)動(dòng)表中的字段，因此也沒(méi)辦法利用索引完成排序，建議添加適當(dāng)?shù)乃饕?/span>
Using where，通常是因?yàn)槿頀呙杌蛉饕龗呙钑r(shí)（type 列顯示為 ALL 或index），又加上了WHERE條件，建議添加適當(dāng)?shù)乃饕?/span>
索引使用情況分析
數(shù)據(jù)庫(kù)表
主鍵索引：demo_id
聯(lián)合索引：c1,c2,c3
實(shí)例說(shuō)明
實(shí)例一：
explain select * from t_demo where c1='d1'and c2='d2' and c3='d3';
explain select * from t_demo where c2='d2'and c1='d1' and c3='d3';
explain select * from t_demo where c3='d3'and c1='d1' and c2='d3';
幾個(gè)Sql表現(xiàn)一致
type=ref,ref=const,const,const
執(zhí)行常量等值查詢時(shí)，改變索引列的順序并不會(huì)更改explain的執(zhí)行結(jié)果，優(yōu)化器會(huì)進(jìn)行優(yōu)化，推薦按照索引順序列編寫(xiě)sql語(yǔ)句。
實(shí)列二：
explain select * from t_demo where c1='d1'and c2>'d2' and c3='d3';
explain select * from t_demo where c1='d1'and c3>'d3' and c2='d2';
第一個(gè)例子范圍右側(cè)索引失效，使用到了兩個(gè)索引。
第二個(gè)例子，由于優(yōu)化器優(yōu)化的原因，使用到了全部的三個(gè)索引。
實(shí)例三：
explain select * from t_demo wherec1>'c' and c2='d2' and c3='d3';
explain select * from t_demo wherec1>'e' and c2='d2' and c3='d3';
從上面兩個(gè)實(shí)例可以發(fā)現(xiàn)，同樣使用最左的索引列范圍查詢，有些情況未用到索引，做了全表掃描（第一個(gè)例子）；有些情況使用到了索引（第二個(gè)例子）。
經(jīng)反復(fù)驗(yàn)證，發(fā)現(xiàn)如下規(guī)律（不一定可靠），也可能與數(shù)據(jù)的第一行或最小值相關(guān)。
1. 跟存儲(chǔ)的數(shù)據(jù)有關(guān)
2. 在大于條件下，如果條件數(shù)據(jù)小于列數(shù)據(jù)，則索引無(wú)效；如果條件數(shù)據(jù)大于列數(shù)據(jù)，則索引有效；
在設(shè)計(jì)查詢條件時(shí)，請(qǐng)注意規(guī)避。
針對(duì)第一個(gè)例子，可以采用覆蓋索引的方式優(yōu)化。
實(shí)例四：
explain select * from t_demo where c1='d1'and c2='d2' order by c3;
explain select * from t_demo where c1='d1'order by c3;
explain select * from t_demo where c1='d1'and c3='d3' order by c2;
order by排序使用到索引和沒(méi)使用到索引的情況
實(shí)例五：
explain select * from t_demo where c1='d1'and c4='d4' order by c1,c2;
條件列包含沒(méi)有索引的列，出現(xiàn)了Using filesort
實(shí)例六：
explain select * from t_demo where c1='d1'and c4='d4' group by c1,c2;
性能非常差的場(chǎng)景，同時(shí)出現(xiàn)了Using temporary和Using filesort
總結(jié)
1. 兩種方式的排序filesort和index，Usingindex是指MySQL掃描索引本身完成排序。index效率高，filesort效率低。
2. order by滿足兩種情況會(huì)使用Using index。
1）order by語(yǔ)句使用索引最左前列。
2）使用where子句與order by子句條件列組合滿足索引最左前列。
3. 盡量在索引列上完成排序，遵循索引建立（索引創(chuàng)建的順序）時(shí)的最佳左前綴法則。
4. group by與order by很類似，都是先排序后分組，遵照索引創(chuàng)建順序的最佳左前綴法則。
有道無(wú)術(shù)，術(shù)可成；有術(shù)無(wú)道，止于術(shù)
歡迎大家關(guān)注Java之道公眾號(hào)
好文章，我在看??