MySQL 的 explain 語句顯示了 MySQL 如何使用索引來處理 select 語句以及連接表章蚣，可以幫助我們分析如何選擇更好的索引和寫出更優(yōu)性能的查詢語句

查詢示例

explain select * from (select * from `user` limit 10, 20) as t1 
left join `user_profile` as t2 on t1.id = t2.user_id;

mysql_explain.png

1. id（查詢標(biāo)識(shí)符）這是 SELECT 查詢序列號(hào)稠炬，表明 sql 語句執(zhí)行的順序击困，id 列數(shù)字越大越先執(zhí)行咬像，如果數(shù)字一樣大城舞，那么就從上往下依次執(zhí)行槽华，id 列為 null 的就表是這是一個(gè)結(jié)果集，不需要使用它來進(jìn)行查詢杭攻。例如： explain select * from (select * from `user` limit 10, 10) as s;

   id.png

2. select_type（查詢類型）常見類型有：

   • simple: 表示不需要 union 操作或者不包含子查詢的 select祟敛。有連接查詢時(shí)，外層的查詢?yōu)?simple兆解，且只有一個(gè)
   • primary: 一個(gè)需要 union 操作或者含有子查詢的 select馆铁，位于最外層的單位查詢的 select_type 即為primary，且只有一個(gè)
   • union：union 連接的兩個(gè) select锅睛，第一個(gè)查詢是 dervied 派生表埠巨，除了第一個(gè)表外，第二個(gè)以后的表的 select_type 都是 union现拒。例如：explain select * from `user` union (select * from `user` limit 10);
     
     select_type-union.png
     第二條語句就使用了 union辣垒。
   • dependent union：與union一樣，出現(xiàn)在 union 或 union all語句中印蔬，但是這個(gè)查詢要受到外部查詢的影響勋桶。
   • union result：包含 union 的結(jié)果集，在 union 和 union all 語句中,因?yàn)樗恍枰獏⑴c查詢侥猬，所以 id 字段為 null例驹。例如：explain select * from `user` union (select * from `user` limit 10);
     
     select_type-union.png
   • subquery：除了 from 字句中包含的子查詢外，其它地方出現(xiàn)的子查詢都可能是 subquery
   • dependent subquery：與 dependent union 類似退唠，表示這個(gè) subquery 的查詢要受到外部表查詢的影響
   • derived：from 字句中出現(xiàn)的子查詢鹃锈，也叫做派生表，其他數(shù)據(jù)庫中可能叫做內(nèi)聯(lián)視圖或嵌套 select瞧预。例如： explain select * from (select * from `user` limit 10, 10) as s;
     id.png

3. table (查詢表名) 如果查詢使用了別名屎债，那么這里顯示的是別名，如果不涉及對(duì)數(shù)據(jù)表的操作垢油，那么這里顯示為 null扔茅，如果顯示為尖括號(hào)括起來的<derived N>就表示這個(gè)是臨時(shí)表，后邊的 N 就是執(zhí)行計(jì)劃中的 id秸苗，表示結(jié)果來自于這個(gè)查詢產(chǎn)生。如果是尖括號(hào)括起來的<union M,N>运褪，與<derived N>類似惊楼，也是一個(gè)臨時(shí)表，表示這個(gè)結(jié)果來自于 union 查詢的 id為 M,N 的結(jié)果集

4. type（連接類型）需要重點(diǎn)掌握秸讹。有多個(gè)參數(shù)檀咙，依次從好到差：system，const璃诀，eq_ref弧可，ref，fulltext劣欢，ref_or_null棕诵，unique_subquery裁良，index_subquery，range校套，index_merge价脾，index，ALL. 除了 ALL 之外笛匙，其他的 type 都可以使用到索引侨把，除了 index_merge 之外，其他的 type 只可以用到一個(gè)索引

   • system：表中只有一行數(shù)據(jù)或者是空表妹孙，且只能用于 MyISAM 和 Memory表秋柄。如果是 InnoDB 引擎表，type 列在這個(gè)情況通常都是 all 或者 index蠢正。
   • const：使用 primary key 或者 unique 索引骇笔，返回記錄一定是 1 行記錄的等值 where 條件時(shí)，通常 type 是 const机隙。其他數(shù)據(jù)庫也叫做唯一索引掃描蜘拉。例如：primary key, explain select * from `user` where id = 1220;
     
     type-const_1.png
     unique, explain select * from `user` where username = 'fanxin';
     
     type-const_2.png
   • eq_ref 對(duì)于 eq_ref 的解釋，MySQL 手冊(cè)是這樣說的:"對(duì)于每個(gè)來自于前面的表的行組合有鹿，從該表中讀取一行旭旭。這可能是最好的聯(lián)接類型，除了 const 類型葱跋。它用在一個(gè)索引的所有部分被聯(lián)接使用并且索引是 UNIQUE 或 PRIMARY KEY"持寄。eq_ref 可以用于使用=比較帶索引的列。例如：primary key, explain select * from `user` as t1, `user_profile` as t2 where t1.id = t2.user_id;
     type-const_1.png
     unique, explain select * from `user` where username = 'fanxin';
     
     type-eq_ref.png
   • ref：不像 eq_ref 那樣要求連接順序娱俺，也沒有主鍵和唯一索引的要求稍味，只要使用相等條件檢索時(shí)就可能出現(xiàn)，常見與輔助索引的等值查找荠卷∧Ｂ或者多列主鍵、唯一索引中油宜，使用第一個(gè)列之外的列作為等值查找也會(huì)出現(xiàn)掂碱，總之，返回?cái)?shù)據(jù)不唯一的等值查找就可能出現(xiàn)慎冤。
   • ref_or_null：與 ref 方法類似疼燥，只是增加了 null 值的比較。實(shí)際用的不多蚁堤。** 上面這五種情況都是很理想的索引使用情況 **
   • fulltext：全文索引檢索醉者，要注意，全文索引的優(yōu)先級(jí)很高，若全文索引和普通索引同時(shí)存在時(shí)撬即，MySQL 不管代價(jià)立磁，優(yōu)先選擇使用全文索引。
   • unique_subquery：用于 where 中的 in 形式子查詢搞莺，子查詢返回不重復(fù)值唯一值息罗。
   • index_subquery：用于 in 形式子查詢使用到了輔助索引或者 in 常數(shù)列表，子查詢可能返回重復(fù)值才沧，可以使用索引將子查詢?nèi)ブ亍?/li>
   • range：索引范圍掃描迈喉，常見于使用 >, <, is null, between, in, like 等運(yùn)算符的查詢中。例如：explain select * from `user` where id > 1220;
     
     type-range.png
   • index_merge：表示查詢使用了兩個(gè)以上的索引温圆，最后取交集或者并集挨摸，常見and ，or的條件使用了不同的索引岁歉，官方排序這個(gè)在ref_or_null之后得运，但是實(shí)際上由于要讀取所個(gè)索引，性能可能大部分時(shí)間都不如 range锅移。
   • index：索引全表掃描熔掺，把索引從頭到尾掃一遍，常見于使用索引列就可以處理不需要讀取數(shù)據(jù)文件的查詢非剃、可以使用索引排序或者分組的查詢置逻。
   • all：這個(gè)就是全表掃描數(shù)據(jù)文件，然后再在server層進(jìn)行過濾返回符合要求的記錄备绽。

5. possible_keys（查詢可能使用到的索引）

6. key（查詢真正使用到的索引）select_type 為 index_merge 時(shí)券坞，這里可能出現(xiàn)兩個(gè)以上的索引，其他的 select_type 這里只會(huì)出現(xiàn)一個(gè)肺素。

7. key_len（用于處理查詢的索引長度）

   • 如果是單列索引恨锚，那么整個(gè)索引長度都算進(jìn)去
   • 如果是多列索引，那么查詢不一定都能使用到所有的列倍靡，具體使用到了多少個(gè)列的索引猴伶，這里就會(huì)計(jì)算進(jìn)去，沒有使用到的列塌西，這里不會(huì)計(jì)算進(jìn)去他挎。留意下這個(gè)列的值，算一下你的多列索引總長度就知道有沒有使用到所有的列了雨让。要注意，MySQL 的ICP 特性使用到的索引不會(huì)計(jì)入其中忿等。另外栖忠，key_len 只計(jì)算where 條件用到的索引長度，而排序和分組就算用到了索引，也不會(huì)計(jì)算到 key_len 中庵寞。

8. ref 列顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行狸相。如果是使用的常數(shù)等值查詢，這里會(huì)顯示 const捐川，如果是連接查詢脓鹃，被驅(qū)動(dòng)表的執(zhí)行計(jì)劃這里會(huì)顯示驅(qū)動(dòng)表的關(guān)聯(lián)字段，如果是條件使用了表達(dá)式或者函數(shù)古沥，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換瘸右，這里可能顯示為 func。

9. rows MySQL 執(zhí)行查詢的行數(shù)岩齿，不是精確值太颤。簡單且重要，數(shù)值越大越不好盹沈，說明沒有用好索引龄章。

10. Extra 該列包含 MySQL 解決查詢的詳細(xì)信息。

    • distinct：在 select 部分使用了 distinc 關(guān)鍵字
    • no tables used：不帶 from 字句的查詢或者 From dual 查詢
    • 使用not in()形式子查詢或not exists運(yùn)算符的連接查詢乞封，這種叫做反連接做裙。即，一般連接查詢是先查詢內(nèi)表肃晚，再查詢外表锚贱，反連接就是先查詢外表，再查詢內(nèi)表陷揪。
    • using filesort：排序時(shí)無法使用到索引時(shí)惋鸥，就會(huì)出現(xiàn)這個(gè)。常見于 order by 和 group by 語句中悍缠。例如：explain select id,username from user order by created_at;
      extra-using-filesort.png
    • using index：查詢時(shí)不需要回表查詢卦绣，直接通過索引就可以獲取查詢的數(shù)據(jù)。
    • using join buffer（block nested loop）飞蚓，using join buffer（batched key accss）：5.6.x之后的版本優(yōu)化關(guān)聯(lián)查詢的BNL滤港，BKA特性。主要是減少內(nèi)表的循環(huán)數(shù)量以及比較順序地掃描查詢趴拧。
    • using sort_union溅漾，using_union，using intersect著榴，using sort_intersection：using intersect：表示使用 and 的各個(gè)索引的條件時(shí)添履，該信息表示是從處理結(jié)果獲取交集 using union：表示使用 or 連接各個(gè)使用索引的條件時(shí)，該信息表示從處理結(jié)果獲取并集 using sort_union 和 using sort_intersection：與前面兩個(gè)對(duì)應(yīng)的類似脑又，只是他們是出現(xiàn)在用 and 和 or 查詢信息量大時(shí)暮胧，先查詢主鍵锐借，然后進(jìn)行排序合并后，才能讀取記錄并返回往衷。
    • 表示使用了臨時(shí)表存儲(chǔ)中間結(jié)果钞翔。臨時(shí)表可以是內(nèi)存臨時(shí)表和磁盤臨時(shí)表，執(zhí)行計(jì)劃中看不出來席舍，需要查看status變量布轿，used_tmp_table，used_tmp_disk_table才能看出來来颤。
    • using where：表示存儲(chǔ)引擎返回的記錄并不是所有的都滿足查詢條件汰扭，需要在 server 層進(jìn)行過濾。查詢條件中分為限制條件和檢查條件脚曾，5.6 之前东且，存儲(chǔ)引擎只能根據(jù)限制條件掃描數(shù)據(jù)并返回，然后 server 層根據(jù)檢查條件進(jìn)行過濾再返回真正符合查詢的數(shù)據(jù)本讥。5.6.x 之后支持 ICP 特性珊泳，可以把檢查條件也下推到存儲(chǔ)引擎層，不符合檢查條件和限制條件的數(shù)據(jù)拷沸，直接不讀取色查，這樣就大大減少了存儲(chǔ)引擎掃描的記錄數(shù)量。extra 列顯示 using index condition撞芍。
    • firstmatch(tb_name)：5.6.x 開始引入的優(yōu)化子查詢的新特性之一秧了，常見于 where 字句含有 in() 類型的子查詢。如果內(nèi)表的數(shù)據(jù)量比較大序无，就可能出現(xiàn)這個(gè)验毡。
    • loosescan(m..n)：5.6.x 之后引入的優(yōu)化子查詢的新特性之一，在 in() 類型的子查詢中帝嗡，子查詢返回的可能有重復(fù)記錄時(shí)晶通，就可能出現(xiàn)這個(gè)。

11. filtered 使用explain extended時(shí)會(huì)出現(xiàn)這個(gè)列哟玷，5.7之后的版本默認(rèn)就有這個(gè)字段狮辽，不需要使用explain extended了。這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過濾后巢寡，剩下多少滿足查詢的記錄數(shù)量的比例喉脖，注意是百分比，不是具體記錄數(shù)抑月。

參考來源：
mysql explain執(zhí)行計(jì)劃詳解
MYSQL explain詳解