mysql的執(zhí)行策劃解釋

2024-07-24 12:32:43

字體：大中小

供稿：網(wǎng)友

關(guān)于MySQL的執(zhí)行計(jì)劃，做個(gè)筆記，可以做為優(yōu)化的依據(jù)，盡量將第四列 type優(yōu)化到ref，至少要保證range方式，能用覆蓋索引的要使用覆蓋索引，然后possible_keys顯示null不代表不使用索引，覆蓋索引的時(shí)候，可能只在key列顯示，possible_keys顯示null；然后注意當(dāng)分組和排序的時(shí)候可能會(huì)使用臨時(shí)表的時(shí)候，盡量不使用磁盤(pán)臨時(shí)表;
一：首先生成執(zhí)行計(jì)劃：
Explain語(yǔ)法
EXPLAIN SELECT ……
變體：
1. EXPLAIN EXTENDED SELECT ……
將執(zhí)行計(jì)劃“反編譯”成SELECT語(yǔ)句，運(yùn)行SHOW WARNINGS 可得到被MySQL優(yōu)化器優(yōu)化后的查詢語(yǔ)句
例如：
mysql> explain EXTENDED select CUST_ID ,count(*) from biz_member_info group by CUST_ID limit 10;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+-------------+
| 1 | SIMPLE | biz_member_info | index | CUST_ID | CUST_ID | 768 | NULL | 10 | 17665850.00 | Using index |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+-------------+
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `cms`.`biz_member_info`.`CUST_ID` AS `CUST_ID`,count(0) AS `count(*)` from `cms`.`biz_member_info` group by `cms`.`biz_member_info`.`CUST_ID` limit 10 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2. EXPLAIN PARTITIONS SELECT ……
用于分區(qū)表的EXPLAIN
二：執(zhí)行計(jì)劃的解析
mysql> explain select CUST_ID ,count(*) from biz_member_info group by CUST_ID limit 10;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | biz_member_info | index | CUST_ID | CUST_ID | 768 | NULL | 10 | Using index |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
2.1、第一列 id
id列數(shù)字越大越先執(zhí)行，如果說(shuō)數(shù)字一樣大，那么就從上往下依次執(zhí)行，id列為null的就表示這是一個(gè)結(jié)果集，不需要使用它來(lái)進(jìn)行查詢。
2.2、第二列 select_type
A：simple：表示不需要union操作或者不包含子查詢的簡(jiǎn)單select查詢。有連接查詢時(shí)，外層的查詢?yōu)閟imple，且只有一個(gè)
B：primary：一個(gè)需要union操作或者含有子查詢的select，位于最外層的單位查詢的select_type即為primary。且只有一個(gè)
C：union：union連接的兩個(gè)select查詢，select .... from table1 union select ..... from table2;第一個(gè)查詢(即select .... from table1）是dervied派生表，除了第一個(gè)表外，第二個(gè)以后的表select_type都是union
D：dependent union：與union一樣，出現(xiàn)在union 或union all語(yǔ)句中，但是這個(gè)查詢要受到外部查詢的影響
E：union result：包含union的結(jié)果集，在union和union all語(yǔ)句中,因?yàn)樗恍枰獏⑴c查詢，所以id字段為null
F：subquery：除了from字句中包含的子查詢外，其他地方出現(xiàn)的子查詢都可能是subquery
G：dependent subquery：與dependent union類(lèi)似，表示這個(gè)subquery的查詢要受到外部表查詢的影響
H：derived：from字句中出現(xiàn)的子查詢，也叫做派生表，其他數(shù)據(jù)庫(kù)中可能叫做內(nèi)聯(lián)視圖或嵌套select
2.3、第三列 table-----顯示的查詢表名
1）如果查詢使用了別名，那么這里顯示的是別名，
2）如果不涉及對(duì)數(shù)據(jù)表的操作，那么這顯示為null，
3）如果顯示為尖括號(hào)括起來(lái)的<derived N>就表示這個(gè)是臨時(shí)表，后邊的N就是執(zhí)行計(jì)劃中的id，表示結(jié)果來(lái)自于這個(gè)查詢產(chǎn)生。
4）如果是尖括號(hào)括起來(lái)的<union M,N>，與<derived N>類(lèi)似，也是一個(gè)臨時(shí)表，表示這個(gè)結(jié)果來(lái)自于union查詢的id為M,N的結(jié)果集。
2.4、第四列 type-----顯示查詢數(shù)據(jù)的途徑，很重要的一個(gè)值！！！！
依次從好到差：system>const>eq_ref>ref>fulltext>ref_or_null>unique_subquery>index_subquery>range>index_merge>index>ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個(gè)索引
A：system：表中只有一行數(shù)據(jù)或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在這個(gè)情況通常都是all或者index
B：const：使用唯一索引或者主鍵，返回記錄一定是1行記錄的等值where條件時(shí)，通常type是const。其他數(shù)據(jù)庫(kù)也叫做唯一索引掃描

C：eq_ref：出現(xiàn)在連接多個(gè)表的查詢計(jì)劃中，驅(qū)動(dòng)表每次連接只返回一行數(shù)據(jù)，且這行數(shù)據(jù)是第二個(gè)表的主鍵或者唯一索引，且必須為not null，唯一索引和主鍵是多列時(shí)，只有所有的列都用作比較時(shí)才會(huì)出現(xiàn)eq_ref ，（只有這樣才能保證唯一性）總之一般情況下使用唯一鍵或者主鍵進(jìn)行單表等值查詢時(shí)，一般是const，當(dāng)多表連接的時(shí)候，第一個(gè)表每次在連接條件下只返回一行數(shù)據(jù)，并且這行數(shù)據(jù)可以通過(guò)第二個(gè)表的主鍵或者唯一索引檢索出來(lái)，并且唯一鍵值不為空，一般為eq_ref；如下所示：

D：ref：沒(méi)有主鍵和唯一索引的要求，只要使用相等條件檢索時(shí)就可能出現(xiàn)，常見(jiàn)與輔助索引的等值查找。或者多列主鍵、多列唯一索引中，使用第一個(gè)列之外的列作為等值查找也會(huì)出現(xiàn)，總之，返回?cái)?shù)據(jù)不唯一的等值查找就可能出現(xiàn)。

E：fulltext：全文索引檢索，要注意，全文索引的優(yōu)先級(jí)很高，若全文索引和普通索引同時(shí)存在時(shí)，mysql不管代價(jià)，優(yōu)先選擇使用全文索引
F：ref_or_null：與ref方法類(lèi)似，只是增加了null值的比較。實(shí)際用的不多。
G：unique_subquery：用于where中的in形式子查詢，子查詢返回不重復(fù)值唯一值
H：index_subquery：用于in形式子查詢使用到了輔助索引或者 in常數(shù)列表，子查詢可能返回重復(fù)值，可以使用索引將子查詢?nèi)ブ亍?br />I：range：索引范圍掃描，常見(jiàn)于使用>,<,is null,between ,in ,like等運(yùn)算符的查詢中。
J：index_merge：表示查詢使用了兩個(gè)以上的索引，最后取交集或者并集，常見(jiàn)and ，or的條件使用了不同的索引，官方排序這個(gè)在ref_or_null之后，但是實(shí)際上由于要讀取所個(gè)索引，性能可能大部分時(shí)間都不如range
K：index：索引全表掃描，把索引從頭到尾掃一遍，常見(jiàn)于使用索引列就可以處理不需要讀取數(shù)據(jù)文件的查詢、可以使用索引排序或者分組的查詢。
L：all：這個(gè)就是全表掃描數(shù)據(jù)文件，然后再在server層進(jìn)行過(guò)濾返回符合要求的記錄。
2.5、第五列possible_keys
顯示可能使用到的索引都會(huì)在這里列出來(lái)，查詢涉及到的字段上存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用，注意這里為null不代表一定不會(huì)走索引，比如覆蓋索引；如下所示表biz_member_info有個(gè)組合索引cust_id_2 (CUST_ID ,CUST_NAME),我們知道組合索引使用的時(shí)候遵循最左匹配原則，where cust_id=可以使用索引，但是where cust_name=不會(huì)使用索引，但是如果可以使用覆蓋索引查出所需要的數(shù)據(jù)列時(shí)，就會(huì)選擇index的方式，也就是掃描所有的索引塊，而不去掃描全部的數(shù)據(jù)塊；所以說(shuō)where cust_name=不會(huì)使用索引這個(gè)說(shuō)法，個(gè)人覺(jué)得有點(diǎn)問(wèn)題，因?yàn)樗梢酝ㄟ^(guò)掃描全部的索引塊來(lái)得到結(jié)果，也可以理解為使用了索引，畢竟掃描全部的索引塊大部分情況是比掃描所有的數(shù)據(jù)塊要效率高，

如下Extra顯示 Using index表示使用了覆蓋索引，可以看出覆蓋索引確實(shí)使用的是index的方式，并且possible_keys為null；但是key顯示使用了覆蓋索引的名字，

如下這種情況不能使用覆蓋索引查詢出需要的數(shù)據(jù)列（原因自己百度），所以選擇了all的方式，也就是掃描全部數(shù)據(jù)塊，當(dāng)然這時(shí)候possible_keys和key都是null

2.6、第六列key
查詢真正使用到的索引，select_type為index_merge時(shí)，這里可能出現(xiàn)兩個(gè)以上的索引，其他的select_type這里只會(huì)出現(xiàn)一個(gè)。并且如果為NULL，則表示沒(méi)有使用索引。
查詢中如果使用了覆蓋索引，則該索引可能僅出現(xiàn)在key列表中，可能不會(huì)出現(xiàn)在前面的possible_keys，前面介紹了；
2.7、key_len
用于處理查詢的索引長(zhǎng)度，單位字節(jié)，需要注意：
1）如果是單列索引，那就整個(gè)索引長(zhǎng)度算進(jìn)去，如果是多列索引，那么查詢不一定都能使用到所有的列，具體使用到了多少個(gè)列的索引，這里就會(huì)計(jì)算進(jìn)去，沒(méi)有使用到的列，這里不會(huì)計(jì)算進(jìn)去。留意下這個(gè)列的值，算一下你的多列索引總長(zhǎng)度就知道有沒(méi)有使用到所有的列了。
2）mysql的ICP特性（后面會(huì)介紹）使用到的索引不會(huì)計(jì)入其中。
3）key_len只計(jì)算where條件用到的索引長(zhǎng)度，而排序和分組就算用到了索引，也不會(huì)計(jì)算到key_len中。
4）查詢中使用的索引的長(zhǎng)度（最大可能長(zhǎng)度），并非實(shí)際使用長(zhǎng)度，理論上長(zhǎng)度越短越好。key_len是根據(jù)表定義計(jì)算而得的，不是通過(guò)表內(nèi)檢索出的。
2.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
2.9、rows
這里是執(zhí)行計(jì)劃中估算的掃描行數(shù)，不是精確值
3.0、extra
這個(gè)列可以顯示的信息非常多，有幾十種，常用的有
A：distinct在select部分使用了distinc關(guān)鍵字；
B：no tables used：不帶from字句的查詢或者From dual查詢；
D：using filesort：排序時(shí)無(wú)法使用到索引時(shí)，就會(huì)出現(xiàn)這個(gè)。常見(jiàn)于order by和group by語(yǔ)句中，需要注意的是filesort不代表一定是使用文件排序，其實(shí)也是內(nèi)存中的一種算法；如果sort buffer可以存放所有滿足條件需要排序的數(shù)據(jù)，則進(jìn)行排序；否則sort buffer滿后，進(jìn)行排序并固化到臨時(shí)文件中。(排序算法采用的是快速排序算法)；
E：using index：查詢時(shí)不需要回表查詢，直接通過(guò)索引就可以獲取查詢的數(shù)據(jù)，也就是使用了覆蓋索引查詢到了結(jié)果；
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ù)量以及比較順序地掃描查詢。
G：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)的類(lèi)似，只是他們是出現(xiàn)在用or和and查詢信息量大時(shí)，先查詢主鍵，然后進(jìn)行排序合并后，才能讀取記錄并返回。
H：using temporary：表示使用了臨時(shí)表存儲(chǔ)中間結(jié)果。
一：MySQL在以下幾種情況會(huì)創(chuàng)建臨時(shí)表：
1、UNION查詢；
2、用到TEMPTABLE算法或者是UNION查詢中的視圖；
3、ORDER BY和GROUP BY的子句不一樣時(shí)；
4、表連接中，ORDER BY的列不是驅(qū)動(dòng)表中的；
5、DISTINCT查詢并且加上ORDER BY時(shí)；
6、SQL中用到SQL_SMALL_RESULT選項(xiàng)時(shí)；
7、FROM中的子查詢；
二：臨時(shí)表可以是內(nèi)存臨時(shí)表和磁盤(pán)臨時(shí)表，執(zhí)行計(jì)劃中看不出來(lái)，需要查看status變量；
mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 65 |
| Created_tmp_tables | 142 |
+-------------------------+-------+
Created_tmp_disk_tables :MySQL server在磁盤(pán)上產(chǎn)生的內(nèi)部臨時(shí)表的個(gè)數(shù)；
Created_tmp_tables ： MySQL server產(chǎn)生的所有的內(nèi)部臨時(shí)表的數(shù)量；
三：MySQL是如何選擇內(nèi)存臨時(shí)表和磁盤(pán)臨時(shí)表
當(dāng)我們進(jìn)行一些特殊操作如需要使用臨時(shí)表才能完成的Order By,Group By 等等，MySQL可能需要使用到臨時(shí)表。當(dāng)我們的臨時(shí)表較小（小于tmp_table_size 參數(shù)所設(shè)置的大小）的時(shí)候，MySQL會(huì)將臨時(shí)表創(chuàng)建成內(nèi)存臨時(shí)表，只有當(dāng)tmp_table_size所設(shè)置的大小無(wú)法裝下整個(gè)臨時(shí)表的時(shí)候，MySQL才會(huì)將該表創(chuàng)建成MyISAM存儲(chǔ)引擎的表存放在磁盤(pán)上。不過(guò)，當(dāng)另一個(gè)系統(tǒng)參數(shù) max_heap_table_size 的大小還小于 tmp_table_size 的時(shí)候，MySQL將使用 max_heap_table_size 參數(shù)所設(shè)置大小作為最大的內(nèi)存臨時(shí)表大小，而忽略tmp_table_size 所設(shè)置的值。而且 tmp_table_size 參數(shù)從 MySQL 5.1.2 才開(kāi)始有，之前一直使用 max_heap_table_size；
I：using where表示存儲(chǔ)引擎返回的記錄并不是所有的都滿足查詢條件，需要在server層進(jìn)行過(guò)濾。如果沒(méi)有使用索引，僅僅是表明使用了過(guò)濾條件；
J：firstmatch(tb_name)：5.6.x開(kāi)始引入的優(yōu)化子查詢的新特性之一，常見(jiàn)于where字句含有in()類(lèi)型的子查詢。如果內(nèi)表的數(shù)據(jù)量比較大，就可能出現(xiàn)這個(gè)；
K：loosescan(m..n)：5.6.x之后引入的優(yōu)化子查詢的新特性之一，在in()類(lèi)型的子查詢中，子查詢返回的可能有重復(fù)記錄時(shí)，就可能出現(xiàn)這個(gè)；
L：Using index condiction: 代表使用了ICP優(yōu)化，主要是針對(duì)where條件過(guò)濾的優(yōu)化，ICP是5.6.x之后引入的可以優(yōu)化 range、ref、eq_ref、ref_or_null類(lèi)型的查詢，ICP是index condition pushdown的縮寫(xiě)，在5.6之前的MySQL版本中不支持ICP,當(dāng)進(jìn)行索引查詢的時(shí)候，首先存儲(chǔ)引擎層根據(jù)索引來(lái)查找記錄，然后在server層再根據(jù)where條件來(lái)過(guò)濾記錄，在支持ICP后，MySQL數(shù)據(jù)庫(kù)會(huì)在存儲(chǔ)引擎層取出索引的同時(shí)，判斷是否可以進(jìn)行where條件的過(guò)濾，也就是將where的部分過(guò)濾操作放在了存儲(chǔ)引擎層，在某些查詢下，可以大大減少上層sql層對(duì)記錄的索取，從而提高數(shù)據(jù)庫(kù)整體性能；
3.1、filtered
使用explain extended時(shí)會(huì)出現(xiàn)這個(gè)列，5.7之后的版本默認(rèn)就有這個(gè)字段，不需要使用explain extended了。這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過(guò)濾后，剩下多少滿足查詢的記錄數(shù)量/存儲(chǔ)引擎返回的數(shù)據(jù)的比例，注意是百分比，不是具體記錄數(shù)；也就是filtered=最后的結(jié)果數(shù)量 /存儲(chǔ)引擎層返回的數(shù)據(jù)量；

（編輯：武林網(wǎng)）

上一篇：mysql sql語(yǔ)句教學(xué)

下一篇：MySQL 5.5 FLUSH TABLES WITH READ LOCK語(yǔ)句簡(jiǎn)介