一、分析

數(shù)據(jù)規(guī)模限制查詢速度。在查詢前能否預(yù)先估計(jì)究竟要涉及多少行、使用哪些索引、運(yùn)行時(shí)間呢？答案是肯定的，MySQL 提供了 EXPLAIN 語法來進(jìn)行查詢分析，在 sql 語句前加一個(gè)“EXPLAIN”即可。比如要分析如下 SQL 語句：

explain select * from table where id = 1 

執(zhí)行結(jié)果如下：

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

二、EXPLAIN列的解釋

1. 【id 列】
   數(shù)字越大越先執(zhí)行，如果說數(shù)字一樣大，那么就從上往下依次執(zhí)行，id 列為 null 的就表示這是一個(gè)結(jié)果集，不需要使用它來進(jìn)行查詢。

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。
• dependent union：與 union 一樣，出現(xiàn)在 union 或 union all 語句中，但是這個(gè)查詢要受到外部查詢的影響。
• union result：包含 union 的結(jié)果集，在 union 和 union all 語句中，因?yàn)樗恍枰獏⑴c查詢，所以 id 字段為 null。
• subquery：除了 from 字句中包含的子查詢外，其他地方出現(xiàn)的子查詢都可能是subquery。
• dependent subquery：與 dependent union 類似，表示這個(gè) subquery 的查詢要受到外部表查詢的影響。
• derived：from 字句中出現(xiàn)的子查詢，也叫做派生表，其他數(shù)據(jù)庫中可能叫做內(nèi)聯(lián)視圖或嵌套 select。

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 列】
   這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為：system > const > eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > ALL，一般來說，得保證查詢至少達(dá)到 range 級(jí)別，最好能達(dá)到 ref。除了 all 之外，其他的 type 都可以使用到索引，除了 index_merge 之外，其他的 type 只可以用到一個(gè)索引。

說明：不同連接類型的解釋(按照效率高低的順序排序)

• system：表中只有一行數(shù)據(jù)或者是空表，且只能用于 myisam 和 memory 表。如果是 Innodb 引擎表，type 列在這個(gè)情況通常都是 all 或者 index。這是 const 連接類型的特殊情況。
• const：表中的一個(gè)記錄的最大值能夠匹配這個(gè)查詢(索引可以是主鍵或唯一索引)。因?yàn)橹挥幸恍校@個(gè)值實(shí)際就是常數(shù)，因?yàn)?MySQL 先讀這個(gè)值然后把它當(dāng)做常數(shù)來對(duì)待。
• eq_ref：出現(xiàn)在要連接過個(gè)表的查詢計(jì)劃中，驅(qū)動(dòng)表只返回一行數(shù)據(jù)，且這行數(shù)據(jù)是第二個(gè)表的主鍵或者唯一索引，且必須為 not null，唯一索引和主鍵是多列時(shí)，只有所有的列都用作比較時(shí)才會(huì)出現(xiàn) eq_ref。
• ref：不像 eq_ref 那樣要求連接順序，也沒有主鍵和唯一索引的要求，只要使用相等條件檢索時(shí)就可能出現(xiàn)，常見與輔助索引的等值查找。或者多列主鍵、唯一索引中，使用第一個(gè)列之外的列作為等值查找也會(huì)出現(xiàn)，總之，返回?cái)?shù)據(jù)不唯一的等值查找就可能出現(xiàn)。
• fulltext：全文索引檢索。全文索引優(yōu)先級(jí)很高，若全文索引和普通索引同時(shí)存在，MySQL 不管代價(jià)，優(yōu)先選擇使用全文索引。
• ref_or_null：與 ref 方法類似，只是增加了 null 值的比較。實(shí)際用的不多。
• unique_subquery：用于 where 中的 in 形式子查詢，子查詢返回唯一值。
• index_subquery：用于 in 形式子查詢使用到了輔助索引或者 in 常數(shù)列表，子查詢可能返回重復(fù)值，可以使用索引將子查詢?nèi)ブ亍?/li>
• range：索引范圍掃描，常見于使用 >、<、is null、between、in、like 等運(yùn)算符的查詢中。
• index_merge：表示查詢使用了兩個(gè)以上的索引，最后取交集或者并集，常見 and、or 的條件使用了不同的索引，官方排序這個(gè)在 ref_or_null 之后，但實(shí)際上由于要讀取所個(gè)索引，性能可能大部分時(shí)間都不如 range。
• index：索引全表掃描，把索引從頭到尾掃一遍，常見于使用索引列就可以處理不需要讀取數(shù)據(jù)文件的查詢、可以使用索引排序或者分組的查詢。(比 ALL 更好，因?yàn)樗饕话阈∮诒頂?shù)據(jù))。
• all：這個(gè)就是全表掃描數(shù)據(jù)文件，然后再在 server 層進(jìn)行過濾返回符合要求的記錄。比較糟糕，應(yīng)該盡量避免。

5. 【possible_keys 列】
   顯示可能應(yīng)用在這張表中的索引。如果為空，沒有可能的索引?？梢詾橄嚓P(guān)的域從 where 語句中選擇一個(gè)合適的語句。

6. 【key 列】
   實(shí)際使用的索引。select_type 為 index_merge 時(shí)，這里可能出現(xiàn)兩個(gè)以上的索引，其他的 select_type 這里只會(huì)出現(xiàn)一個(gè)。如果為 NULL，則沒有使用索引。很少的情況下，MySQL 會(huì)選擇優(yōu)化不足的索引。這種情況下，可以在 select 語句中使用 use index (indexname) 來強(qiáng)制使用一個(gè)索引或者用 ignore index (indexname) 來強(qiáng)制 MySQL 忽略索引。

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 列】
   顯示索引的哪一列被使用了。如果是使用的常數(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 認(rèn)為必須檢查的用來返回請(qǐng)求數(shù)據(jù)的行數(shù)。這里是執(zhí)行計(jì)劃中估算的掃描行數(shù)，不是精確值。

10. 【Extra 列】
    關(guān)于 MySQL 如何解析查詢的額外信息。這里可以看到的壞的例子是 Using temporary 和 Using filesort，意思 MySQL 根本不能使用索引，結(jié)果是檢索會(huì)很慢。

說明：extra 列返回的描述的意義。這個(gè)列可以顯示的信息非常多，有幾十種，常用的有：

• Distinct ：在 select 部分使用了 distinc 關(guān)鍵字。一旦 MySQL 找到了與行相聯(lián)合匹配的行，就不再搜索了。
• no tables used：不帶 from 字句的查詢或者 From dual 查詢
• Not exists ：MySQL 優(yōu)化了 left join，一旦它找到了匹配 left join 標(biāo)準(zhǔn)的行，就不再搜索了。
• 使用 not in() 形式子查詢或 not exists 運(yùn)算符的連接查詢，這種叫做反連接。即，一般連接查詢是先查詢內(nèi)表，再查詢外表，反連接就是先查詢外表，再查詢內(nèi)表。
• Range checked for each Record (index map:#)：沒有找到理想的索引，因此對(duì)從前面表中來的每一個(gè)行組合，MySQL 檢查使用哪個(gè)索引，并用它來從表中返回行。這是使用索引的最慢的連接之一。
• Using filesort ：排序時(shí)無法使用到索引時(shí)，就會(huì)出現(xiàn)這個(gè)，常見于 order by 和 group by 語句中。看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。MySQL 需要進(jìn)行額外的步驟來發(fā)現(xiàn)如何對(duì)返回的行排序。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來排序全部行。
• Using index ：列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候。
• Using temporary ：表示使用了臨時(shí)表存儲(chǔ)中間結(jié)果。臨時(shí)表可以是內(nèi)存臨時(shí)表和磁盤臨時(shí)表，執(zhí)行計(jì)劃中看不出來，需要查看 status 變量，used_tmp_table，used_tmp_disk_table 才能看出來?？吹竭@個(gè)的時(shí)候，查詢需要優(yōu)化了。這里，MySQL 需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來存儲(chǔ)結(jié)果，這通常發(fā)生在對(duì)不同的列集進(jìn)行 order by 上，而不是 group by 上。
• Where used ：使用了 where 從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型 ALL 或 index，這就會(huì)發(fā)生，或者是查詢有問題。
• 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)行排序合并后，才能讀取記錄并返回。
• 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ù)。

弄明白 explain 語法返回的每一項(xiàng)結(jié)果，就能知道 sql 語句大致的運(yùn)行時(shí)間，如果查詢里沒有用到索引、或者需要掃描的行過多，那么可以感到明顯的延遲。因此需要改變查詢方式或者新建索引。MySQL 中的 explain 語法可以幫助改寫查詢，優(yōu)化表的結(jié)構(gòu)和索引的設(shè)置，從而最大地提高查詢效率。當(dāng)然，在大規(guī)模數(shù)據(jù)量時(shí)，索引的建立和維護(hù)的代價(jià)也是很高的，往往需要較長的時(shí)間和較大的空間，如果在不同的列組合上建立索引，空間的開銷會(huì)更大。因此索引最好設(shè)置在需要經(jīng)常查詢的字段中。