MySQL中explain執(zhí)行計劃中額外信息字段(Extra)詳解

xfxyxh 2019-02-20

展開全文

SQL執(zhí)行計劃是經(jīng)過優(yōu)化器決策，產(chǎn)生的SQL在數(shù)據(jù)庫內(nèi)部執(zhí)行的訪問路徑計劃；
由如下語法得到：

explain select col1,col2 from t1..;
desc select col1,col2 from t1..;

理解輸出各個列的含義
這里寫圖片描述

id:每個select子句的標(biāo)識id
select_type:select語句的類型
table:當(dāng)前表名
顯示查詢將訪問的分區(qū)，如果你的查詢是基于分區(qū)表
type：當(dāng)前表內(nèi)訪問方式
possible_keys:可能使用到的索引
key:經(jīng)過優(yōu)化器評估最終使用的索引
key_length:使用到的索引長度
ref:引用到的上一個表的列
rows:rows_examined，要得到最終記錄索要掃描經(jīng)過的記錄數(shù)
filtered:表示存儲引擎返回的數(shù)據(jù)在server層過濾后，剩下多少滿足查詢的記錄數(shù)量的比例，注意是百分比，不是具體記錄數(shù)。
Extra:額外的信息說明

接下來主要針對extra字段進(jìn)行詳細(xì)解釋，EXPLAIN輸出的Extra列包含有關(guān)MySQL如何解析查詢的其他信息。此字段能夠給出讓我們深入理解執(zhí)行計劃進(jìn)一步的細(xì)節(jié)信息，比如是否使用ICP，MRR等。
首先說明下在extra字段進(jìn)行測試過程中使用到的表和MySQL版本：

CREATE TABLE `test_extra1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_number` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
  `age` int(11) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `region` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_empnumber` (`emp_number`),
  KEY `idx_region` (`region`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test_extra2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `emp_number` int(11) NOT NULL,
  `salary` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_empnumber` (`emp_number`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.14    |
+-----------+
1 row in set (0.00 sec)

extra字段詳細(xì)解釋說明：

const row not found
For a query such as SELECT … FROM tbl_name, the table was empty.(類似于select …. from tbl_name，而表記錄為空)
Deleting all rows
For DELETE, some storage engines (such as MyISAM) support a handler method that removes all table rows in a simple and fast way. This Extra value is displayed if the engine uses this optimization. (對于DELETE，一些存儲引擎（如MyISAM）支持一種處理方法，可以簡單而快速地刪除所有的表行。如果引擎使用此優(yōu)化，則會顯示此額外值)
Distinct
MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.(MySQL正在尋找不同的值，因此在找到第一個匹配行后，它將停止搜索當(dāng)前行組合的更多行)
FirstMatch
The semi-join FirstMatch join shortcutting strategy is used for tbl_name. (半連接去重執(zhí)行優(yōu)化策略，當(dāng)匹配了第一個值之后立即放棄之后記錄的搜索。這為表掃描提供了一個早期退出機(jī)制而且還消除了不必要記錄的產(chǎn)生)；如下圖所示：

注：半連接：當(dāng)一張表在另一張表找到匹配的記錄之后，半連接（semi-jion）返回第一張表中的記錄。與條件連接相反，即使在右節(jié)點(diǎn)中找到幾條匹配的記錄，左節(jié)點(diǎn)的表也只會返回一條記錄。另外，右節(jié)點(diǎn)的表一條記錄也不會返回。半連接通常使用IN或EXISTS 作為連接條件。
Start temporary, End temporary
表示半連接中使用了DuplicateWeedout策略的臨時表，具體實現(xiàn)過程如下圖所示：
Full scan on NULL key
This occurs for subquery optimization as a fallback strategy when the optimizer cannot use an index-lookup access method.(子查詢中的一種優(yōu)化方式，主要在遇到無法通過索引訪問null值的使用)
LooseScan(m..n)
The semi-join LooseScan strategy is used. m and n are key part numbers. 利用索引來掃描一個子查詢表可以從每個子查詢的值群組中選出一個單一的值。松散掃描(LooseScan)策略采用了分組，子查詢中的字段作為一個索引且外部SELECT語句可以可以與很多的內(nèi)部SELECT記錄相匹配。如此便會有通過索引對記錄進(jìn)行分組的效果。
如下圖所示：
Impossible HAVING
The HAVING clause is always false and cannot select any rows.(HAVING子句總是為false，不能選擇任何行)
Impossible WHERE
The WHERE clause is always false and cannot select any rows.(WHERE子句始終為false，不能選擇任何行)
Impossible WHERE noticed after reading const tables
MySQL has read all const (and system) tables and notice that the WHERE clause is always false.(MySQL讀取了所有的const和system表，并注意到WHERE子句總是為false)
No matching min/max row
No row satisfies the condition for a query such as SELECT MIN(…) FROM … WHERE condition.(沒有滿足SELECT MIN（…）FROM … WHERE查詢條件的行)
示例中，emp_number最小值為1001，沒有滿足條件的行:

如果此時將select字段改為其他字段，比如salary，則extra如下顯示，使用到ICP優(yōu)化機(jī)制(ICP機(jī)制見https://dev./doc/refman/5.7/en/index-condition-pushdown-optimization.html)
no matching row in const table
For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition.(表為空或者表中根據(jù)唯一鍵查詢時沒有匹配的行)
No matching rows after partition pruning
For DELETE or UPDATE, the optimizer found nothing to delete or update after partition pruning. It is similar in meaning to Impossible WHERE for SELECT statements.(對于DELETE或UPDATE，優(yōu)化器在分區(qū)修剪后沒有發(fā)現(xiàn)任何刪除或更新。對于SELECT語句，它與Impossible WHERE的含義相似)
No tables used
The query has no FROM clause, or has a FROM DUAL clause.(沒有FROM子句或者使用DUAL虛擬表)

.注：DUAL虛擬表純粹是為了方便那些要求所有SELECT語句應(yīng)該有FROM和可能的其他子句的人。 MySQL可能會忽略這些條款。如果沒有引用表，MySQL不需要FROM DUAL(https://dev./doc/refman/5.7/en/select.html)
Not exists
MySQL能夠?qū)Σ樵儓?zhí)行LEFT JOIN優(yōu)化，并且在找到與LEFT JOIN條件匹配的一行后，不會在上一行組合中檢查此表中的更多行。例如：
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;
假設(shè)t2.id被定義為NOT NULL。在這種情況下，MySQL會掃描t1，并使用t1.id的值查找t2中的行。如果MySQL在t2中找到一個匹配的行，它會知道t2.id永遠(yuǎn)不會為NULL，并且不掃描t2中具有相同id值的其余行。換句話說，對于t1中的每一行，MySQL只需要在t2中只執(zhí)行一次查找，而不考慮在t2中實際匹配的行數(shù)。
Range checked for each record (index map: N)
MySQL發(fā)現(xiàn)沒有使用好的索引，但是發(fā)現(xiàn)在前面的表的列值已知之后，可能會使用一些索引。對于上表中的每一行組合，MySQL檢查是否可以使用range或index_merge訪問方法來檢索行。這不是很快，但比執(zhí)行沒有索引的連接更快。
index map N索引的編號從1開始，按照與表的SHOW INDEX所示相同的順序。索引映射值N是指示哪些索引是候選的位掩碼值。例如，0x19（二進(jìn)制11001）的值意味著將考慮索引1,4和5。
其中name屬性為varchar類型；但是條件給出整數(shù)型，涉及到隱式轉(zhuǎn)換。
圖中t2也沒有用到索引，是因為查詢之前我將t2中name字段排序規(guī)則改為utf8_bin導(dǎo)致的鏈接字段排序規(guī)則不匹配。
Select tables optimized away
當(dāng)我們使用某些聚合函數(shù)來訪問存在索引的某個字段時，優(yōu)化器會通過索引直接一次定位到所需要的數(shù)據(jù)行完成整個查詢。在使用某些聚合函數(shù)如min, max的query，直接訪問存儲結(jié)構(gòu)(B樹或者B+樹)的最左側(cè)葉子節(jié)點(diǎn)或者最右側(cè)葉子節(jié)點(diǎn)即可，這些可以通過index解決。Select count(*) from table(不包含where等子句)，MyISAM保存了記錄的總數(shù)，可以直接返回結(jié)果，而Innodb需要全表掃描。Query中不能有g(shù)roup by操作;
Skip_open_table, Open_frm_only, Open_full_table
這些值表示適用于INFORMATION_SCHEMA表查詢的文件打開優(yōu)化；
Skip_open_table：表文件不需要打開。信息已經(jīng)通過掃描數(shù)據(jù)庫目錄在查詢中實現(xiàn)可用。
Open_frm_only：只需要打開表的.frm文件。
Open_full_table：未優(yōu)化的信息查找。必須打開.frm，.MYD和.MYI文件。
unique row not found
對于諸如SELECT … FROM tbl_name的查詢，沒有行滿足表上的UNIQUE索引或PRIMARY KEY的條件。
Using filesort
當(dāng)Query 中包含 ORDER BY 操作，而且無法利用索引完成排序操作的時候，MySQL Query Optimizer 不得不選擇相應(yīng)的排序算法來實現(xiàn)。數(shù)據(jù)較少時從內(nèi)存排序，否則從磁盤排序。Explain不會顯示的告訴客戶端用哪種排序。官方解釋：“MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據(jù)聯(lián)接類型瀏覽所有行并為所有匹配WHERE子句的行保存排序關(guān)鍵字和行的指針來完成排序。然后關(guān)鍵字被排序，并按排序順序檢索行”
Using index
僅使用索引樹中的信息從表中檢索列信息，而不需要進(jìn)行附加搜索來讀取實際行(使用二級覆蓋索引即可獲取數(shù)據(jù))。當(dāng)查詢僅使用作為單個索引的一部分的列時，可以使用此策略。
示例中第一個查詢所有數(shù)據(jù)時，無法通過emp_number的覆蓋索引來獲取整行數(shù)據(jù)，所以需要根據(jù)主鍵id回表查詢表數(shù)據(jù)。
Using index condition
Using index condition 會先條件過濾索引，過濾完索引后找到所有符合索引條件的數(shù)據(jù)行，隨后用 WHERE 子句中的其他條件去過濾這些數(shù)據(jù)行；

因為MySQL的架構(gòu)原因，分成了server層和引擎層，才有所謂的“下推”的說法。所以ICP其實就是實現(xiàn)了index filter技術(shù)，將原來的在server層進(jìn)行的table filter中可以進(jìn)行index filter的部分，在引擎層面使用index filter進(jìn)行處理，不再需要回表進(jìn)行table filter(參考http://www./database/201511/451391.html)。
如下圖描述：
Using index for group-by
數(shù)據(jù)訪問和 Using index 一樣，所需數(shù)據(jù)只須要讀取索引，當(dāng)Query 中使用GROUP BY或DISTINCT 子句時，如果分組字段也在索引中，Extra中的信息就會是 Using index for group-by。注：和Using index一樣，只需讀取覆蓋索引
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)
*注：
Block Nested-Loop Join算法：將外層循環(huán)的行/結(jié)果集存入join buffer, 內(nèi)層循環(huán)的每一行與整個buffer中的記錄做比較，從而減少內(nèi)層循環(huán)的次數(shù)。優(yōu)化器管理參數(shù)optimizer_switch中中的block_nested_loop參數(shù)控制著BNL是否被用于優(yōu)化器。默認(rèn)條件下是開啟，若果設(shè)置為off，優(yōu)化器在選擇 join方式的時候會選擇NLJ(Nested Loop Join)算法。
Batched Key Access原理：對于多表join語句，當(dāng)MySQL使用索引訪問第二個join表的時候，使用一個join buffer來收集第一個操作對象生成的相關(guān)列值。BKA構(gòu)建好key后，批量傳給引擎層做索引查找。key是通過MRR接口提交給引擎的（mrr目的是較為順序）MRR使得查詢更有效率，要使用BKA，必須調(diào)整系統(tǒng)參數(shù)optimizer_switch的值，batched_key_access設(shè)置為on，因為BKA使用了MRR，因此也要打開MRR (參考http://www.cnblogs.com/chenpingzhao/p/6720531.html)。*
Using MRR
使用MRR策略優(yōu)化表數(shù)據(jù)讀取，僅僅針對二級索引的范圍掃描和使用二級索引進(jìn)行 join 的情況；
過程：先根據(jù)where條件中的輔助索引獲取輔助索引與主鍵的集合，再將結(jié)果集放在buffer(read_rnd_buffer_size 直到buffer滿了)，然后對結(jié)果集按照pk_column排序，得到有序的結(jié)果集rest_sort。最后利用已經(jīng)排序過的結(jié)果集，訪問表中的數(shù)據(jù)，此時是順序IO。即MySQL 將根據(jù)輔助索引獲取的結(jié)果集根據(jù)主鍵進(jìn)行排序，將無序化為有序，可以用主鍵順序訪問基表，將隨機(jī)讀轉(zhuǎn)化為順序讀，多頁數(shù)據(jù)記錄可一次性讀入或根據(jù)此次的主鍵范圍分次讀入，減少IO操作，提高查詢效率。
注：MRR原理：Multi-Range Read Optimization，是優(yōu)化器將隨機(jī) IO 轉(zhuǎn)化為順序 IO 以降低查詢過程中 IO 開銷的一種手段，這對IO-bound類型的SQL語句性能帶來極大的提升，適用于range ref eq_ref類型的查詢；
Using sort_union(…), Using union(…), Using intersect(…)
這些指示索引掃描如何合并為index_merge連接類型。
(參考https://dev./doc/refman/5.7/en/index-merge-optimization.html)
索引合并交叉口訪問算法(The Index Merge Intersection Access Algorithm)：
index intersect merge就是多個索引條件掃描得到的結(jié)果進(jìn)行交集運(yùn)算。顯然在多個索引提交之間是 AND 運(yùn)算時，才會出現(xiàn) index intersect merge. 下面兩種where條件或者它們的組合時會進(jìn)行 index intersect merge:
1) 條件使用到復(fù)合索引中的所有字段或者左前綴字段;
2) 主鍵上的任何范圍條件。
intersect merge運(yùn)行方式：多個索引同時掃描，然后結(jié)果取交集。如果所有條件字段都是索引字段，使用索引覆蓋掃描，無需回表
示例：

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;
SELECT * FROM tbl_name  WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

索引合并聯(lián)合訪問算法(The Index Merge Union Access Algorithm)：
index uion merge就是多個索引條件掃描，對得到的結(jié)果進(jìn)行并集運(yùn)算，顯然是多個條件之間進(jìn)行的是 OR 運(yùn)算。以下幾種可能會使用到index merge union: 1) 條件使用到復(fù)合索引中的所有字段或者左前綴字段(對單字段索引也適用)；2) 主鍵上的任何范圍條件；3) 任何符合 index intersect merge 的where條件；
示例：

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;
SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR  (key3='foo' AND key4='bar') AND key5=5;

索引合并排序聯(lián)合訪問算法(The Index Merge Sort-Union Access Algorithm):
多個條件掃描進(jìn)行 OR 運(yùn)算，但是不符合 index union merge算法的，此時可能會使用 sort_union算法；

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;
SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

Using temporary
要解決查詢，MySQL需要創(chuàng)建一個臨時表來保存結(jié)果。如果查詢包含不同列的GROUP BY和ORDER BY子句，則通常會發(fā)生這種情況。官方解釋：”為了解決查詢，MySQL需要創(chuàng)建一個臨時表來容納結(jié)果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。很明顯就是通過where條件一次性檢索出來的結(jié)果集太大了，內(nèi)存放不下了，只能通過家里臨時表來輔助處理；
Using where
表示Mysql將對storage engine提取的結(jié)果進(jìn)行過濾，過濾條件字段無索引；
Using where with pushed condition
僅用在ndb上。Mysql Cluster用Condition Pushdown優(yōu)化改善非索引字段和常量之間的直接比較。condition被pushed down到cluster的數(shù)據(jù)節(jié)點(diǎn)，并在所有數(shù)據(jù)節(jié)點(diǎn)同時估算，把不合條件的列剔除避免網(wǎng)絡(luò)傳輸

參考：https://dev./doc/refman/5.7/en/explain-output.html

本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： xfxyxh > 《MySQL》

舉報/認(rèn)領(lǐng)