一逊桦、分析
數據規(guī)模限制查詢速度谈喳。在查詢前能否預先估計究竟要涉及多少行、使用哪些索引钮孵、運行時間呢骂倘？答案是肯定的，MySQL 提供了 EXPLAIN 語法來進行查詢分析巴席，在 sql 語句前加一個“EXPLAIN”即可恼蓬。比如要分析如下 SQL 語句：
explain select * from table where id = 1
執(zhí)行結果如下：
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
二、EXPLAIN列的解釋
【id 列】
數字越大越先執(zhí)行螺捐，如果說數字一樣大吆录，那么就從上往下依次執(zhí)行，id 列為 null 的就表示這是一個結果集毡证，不需要使用它來進行查詢电爹。【select_type列】常見的有：
- simple：表示不需要 union 操作或者不包含子查詢的簡單 select 查詢。有連接查詢時料睛，外層的查詢?yōu)?simple丐箩，且只有一個。
- primary：一個需要 union 操作或者含有子查詢的 select恤煞，位于最外層的單位查詢的 select_type 即為 primary屎勘。且只有一個。
- union：union 連接的兩個 select 查詢居扒，第一個查詢是 dervied 派生表概漱，除了第一個表外，第二個以后的表 select_type 都是 union喜喂。
- dependent union：與 union 一樣瓤摧，出現在 union 或 union all 語句中，但是這個查詢要受到外部查詢的影響玉吁。
- union result：包含 union 的結果集照弥，在 union 和 union all 語句中，因為它不需要參與查詢进副，所以 id 字段為 null这揣。
- subquery：除了 from 字句中包含的子查詢外，其他地方出現的子查詢都可能是subquery。
- dependent subquery：與 dependent union 類似给赞，表示這個 subquery 的查詢要受到外部表查詢的影響机打。
- derived：from 字句中出現的子查詢，也叫做派生表塞俱，其他數據庫中可能叫做內聯視圖或嵌套 select姐帚。
- 【table 列】顯示查詢的表
- 如果查詢使用了別名，這里顯示別名障涯；
- 如果不涉及對數據表的操作罐旗，那么這顯示為 null；
- 如果顯示為尖括號括起來的 <derived N> 就表示這個是臨時表唯蝶，后邊的 N 就是執(zhí)行計劃中的 id九秀，表示結果來自于這個查詢產生；
- 如果是尖括號括起來的 <union M,N>粘我，與 <derived N> 類似鼓蜒，也是一個臨時表，表示這個結果來自于 union 查詢的 id 為 M,N 的結果集征字。
- 【type 列】
這是重要的列都弹，顯示連接使用了何種類型。從最好到最差的連接類型為：system > const > eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > ALL匙姜，一般來說畅厢，得保證查詢至少達到 range 級別，最好能達到 ref氮昧。除了 all 之外框杜，其他的 type 都可以使用到索引，除了 index_merge 之外袖肥，其他的 type 只可以用到一個索引咪辱。
說明：不同連接類型的解釋(按照效率高低的順序排序)
- system：表中只有一行數據或者是空表，且只能用于 myisam 和 memory 表椎组。如果是 Innodb 引擎表油狂，type 列在這個情況通常都是 all 或者 index。這是 const 連接類型的特殊情況寸癌。
- const：表中的一個記錄的最大值能夠匹配這個查詢(索引可以是主鍵或唯一索引)选调。因為只有一行，這個值實際就是常數灵份，因為 MySQL 先讀這個值然后把它當做常數來對待。
- eq_ref：出現在要連接過個表的查詢計劃中哮洽，驅動表只返回一行數據填渠，且這行數據是第二個表的主鍵或者唯一索引，且必須為 not null，唯一索引和主鍵是多列時氛什，只有所有的列都用作比較時才會出現 eq_ref莺葫。
- ref：不像 eq_ref 那樣要求連接順序，也沒有主鍵和唯一索引的要求枪眉，只要使用相等條件檢索時就可能出現捺檬，常見與輔助索引的等值查找∶惩或者多列主鍵堡纬、唯一索引中，使用第一個列之外的列作為等值查找也會出現蒿秦，總之烤镐，返回數據不唯一的等值查找就可能出現。
- fulltext：全文索引檢索棍鳖。全文索引優(yōu)先級很高炮叶，若全文索引和普通索引同時存在，MySQL 不管代價渡处，優(yōu)先選擇使用全文索引镜悉。
- ref_or_null：與 ref 方法類似，只是增加了 null 值的比較医瘫。實際用的不多侣肄。
- unique_subquery：用于 where 中的 in 形式子查詢，子查詢返回唯一值登下。
- index_subquery：用于 in 形式子查詢使用到了輔助索引或者 in 常數列表茫孔，子查詢可能返回重復值，可以使用索引將子查詢去重被芳。
- range：索引范圍掃描缰贝，常見于使用 >、<畔濒、is null剩晴、between、in侵状、like 等運算符的查詢中赞弥。
- index_merge：表示查詢使用了兩個以上的索引，最后取交集或者并集趣兄，常見 and绽左、or 的條件使用了不同的索引，官方排序這個在 ref_or_null 之后艇潭，但實際上由于要讀取所個索引拼窥，性能可能大部分時間都不如 range戏蔑。
- index：索引全表掃描，把索引從頭到尾掃一遍鲁纠，常見于使用索引列就可以處理不需要讀取數據文件的查詢总棵、可以使用索引排序或者分組的查詢。(比 ALL 更好改含，因為索引一般小于表數據)情龄。
- all：這個就是全表掃描數據文件，然后再在 server 層進行過濾返回符合要求的記錄捍壤。比較糟糕骤视，應該盡量避免。
【possible_keys 列】
顯示可能應用在這張表中的索引白群。如果為空尚胞，沒有可能的索引≈穆可以為相關的域從 where 語句中選擇一個合適的語句笼裳。【key 列】
實際使用的索引。select_type 為 index_merge 時粱玲，這里可能出現兩個以上的索引躬柬，其他的 select_type 這里只會出現一個。如果為 NULL抽减，則沒有使用索引允青。很少的情況下，MySQL 會選擇優(yōu)化不足的索引卵沉。這種情況下颠锉，可以在 select 語句中使用 use index (indexname) 來強制使用一個索引或者用 ignore index (indexname) 來強制 MySQL 忽略索引。【key_len 列】
用于處理查詢的索引長度史汗，如果是單列索引琼掠，那就整個索引長度算進去；如果是多列索引停撞，那么查詢不一定都能使用到所有的列瓷蛙，具體使用到了多少個列的索引，這里就會計算進去戈毒，沒有使用到的列艰猬，這里不會計算進去。留意下這個列的值埋市，算一下你的多列索引總長度就知道有沒有使用到所有的列了冠桃。要注意，MySQL 的 ICP 特性使用到的索引不會計入其中道宅。另外腊满，key_len 只計算 where 條件用到的索引長度套么，而排序和分組就算用到了索引，也不會計算到 key_len 中碳蛋。在不損失精確性的情況下，長度越短越好省咨。【ref 列】
顯示索引的哪一列被使用了肃弟。如果是使用的常數等值查詢，這里會顯示 const零蓉，如果是連接查詢笤受，被驅動表的執(zhí)行計劃這里會顯示驅動表的關聯字段，如果是條件使用了表達式或者函數敌蜂，或者條件列發(fā)生了內部隱式轉換箩兽，這里可能顯示為 func。【rows 列】
MySQL 認為必須檢查的用來返回請求數據的行數章喉。這里是執(zhí)行計劃中估算的掃描行數汗贫，不是精確值。【Extra 列】
關于 MySQL 如何解析查詢的額外信息秸脱。這里可以看到的壞的例子是 Using temporary 和 Using filesort落包，意思 MySQL 根本不能使用索引，結果是檢索會很慢摊唇。
說明：extra 列返回的描述的意義咐蝇。這個列可以顯示的信息非常多，有幾十種巷查，常用的有：
- Distinct ：在 select 部分使用了 distinc 關鍵字有序。一旦 MySQL 找到了與行相聯合匹配的行，就不再搜索了岛请。
- no tables used：不帶 from 字句的查詢或者 From dual 查詢
- Not exists ：MySQL 優(yōu)化了 left join旭寿，一旦它找到了匹配 left join 標準的行，就不再搜索了髓需。
- 使用 not in() 形式子查詢或 not exists 運算符的連接查詢许师，這種叫做反連接。即僚匆，一般連接查詢是先查詢內表微渠，再查詢外表，反連接就是先查詢外表咧擂，再查詢內表逞盆。
- Range checked for each Record (index map:#)：沒有找到理想的索引，因此對從前面表中來的每一個行組合松申，MySQL 檢查使用哪個索引云芦，并用它來從表中返回行俯逾。這是使用索引的最慢的連接之一。
- Using filesort ：排序時無法使用到索引時舅逸，就會出現這個桌肴，常見于 order by 和 group by 語句中×鹄看到這個的時候坠七，查詢就需要優(yōu)化了。MySQL 需要進行額外的步驟來發(fā)現如何對返回的行排序旗笔。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行彪置。
- Using index ：列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候蝇恶。
- Using temporary ：表示使用了臨時表存儲中間結果拳魁。臨時表可以是內存臨時表和磁盤臨時表，執(zhí)行計劃中看不出來撮弧，需要查看 status 變量潘懊，used_tmp_table，used_tmp_disk_table 才能看出來想虎∝宰穑看到這個的時候，查詢需要優(yōu)化了舌厨。這里岂却，MySQL 需要創(chuàng)建一個臨時表來存儲結果，這通常發(fā)生在對不同的列集進行 order by 上裙椭，而不是 group by 上躏哩。
- Where used ：使用了 where 從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行揉燃，并且連接類型 ALL 或 index扫尺，這就會發(fā)生，或者是查詢有問題炊汤。
- using join buffer (block nested loop)正驻，using join buffer (batched key accss)：5.6.x之后的版本優(yōu)化關聯查詢的 BNL，BKA 特性抢腐。主要是減少內表的循環(huán)數量以及比較順序地掃描查詢姑曙。
- using sort_union，using_union迈倍，using intersect伤靠，using sort_intersection：
using intersect：表示使用 and 的各個索引的條件時，該信息表示是從處理結果獲取交集 - using union：表示使用 or 連接各個使用索引的條件時啼染，該信息表示從處理結果獲取并集
- using sort_union 和 using sort_intersection：與前面兩個對應的類似宴合，只是他們是出現在用 and 和 or 查詢信息量大時焕梅，先查詢主鍵，然后進行排序合并后卦洽，才能讀取記錄并返回贞言。
- using where：表示存儲引擎返回的記錄并不是所有的都滿足查詢條件，需要在 server 層進行過濾阀蒂。查詢條件中分為限制條件和檢查條件蜗字，5.6之前，存儲引擎只能根據限制條件掃描數據并返回脂新，然后server層根據檢查條件進行過濾再返回真正符合查詢的數據。5.6.x之后支持ICP特性粗梭，可以把檢查條件也下推到存儲引擎層争便，不符合檢查條件和限制條件的數據，直接不讀取断医，這樣就大大減少了存儲引擎掃描的記錄數量滞乙。extra列顯示using index condition
- firstmatch(tb_name)：5.6.x開始引入的優(yōu)化子查詢的新特性之一，常見于where字句含有in()類型的子查詢鉴嗤。如果內表的數據量比較大斩启，就可能出現這個
- loosescan(m..n)：5.6.x之后引入的優(yōu)化子查詢的新特性之一，在in()類型的子查詢中醉锅，子查詢返回的可能有重復記錄時兔簇，就可能出現這個
- 【filtered 列】
使用 explain extended 時會出現這個列，5.7 之后的版本默認就有這個字段硬耍，不需要使用 explain extended 了垄琐。這個字段表示存儲引擎返回的數據在 server 層過濾后，剩下多少滿足查詢的記錄數量的比例经柴，注意是百分比狸窘，不是具體記錄數。
弄明白 explain 語法返回的每一項結果坯认，就能知道 sql 語句大致的運行時間翻擒，如果查詢里沒有用到索引、或者需要掃描的行過多牛哺，那么可以感到明顯的延遲陋气。因此需要改變查詢方式或者新建索引。MySQL 中的 explain 語法可以幫助改寫查詢荆隘，優(yōu)化表的結構和索引的設置恩伺，從而最大地提高查詢效率。當然椰拒，在大規(guī)模數據量時晶渠，索引的建立和維護的代價也是很高的凰荚，往往需要較長的時間和較大的空間，如果在不同的列組合上建立索引褒脯，空間的開銷會更大便瑟。因此索引最好設置在需要經常查詢的字段中。
