mysql的執行計劃解釋

2024-07-24 12:34:32

字體：大中小

來源：轉載

供稿：網友

　　關于MySQL的執行計劃，做個筆記，可以做為優化的依據，盡量將第四列 type優化到ref，至少要保證range方式，能用覆蓋索引的要使用覆蓋索引，然后possible_keys顯示null不代表不使用索引，覆蓋索引的時候，可能只在key列顯示，possible_keys顯示null；然后注意當分組和排序的時候可能會使用臨時表的時候，盡量不使用磁盤臨時表;
　　一：首先生成執行計劃：
　　Explain語法
　　EXPLAIN SELECT ……
　　變體：
　　1. EXPLAIN EXTENDED SELECT ……
　　將執行計劃“反編譯”成SELECT語句，運行SHOW WARNINGS 可得到被MySQL優化器優化后的查詢語句
　　例如：
　　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 ……
　　用于分區表的EXPLAIN
　　二：執行計劃的解析
　　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列數字越大越先執行，如果說數字一樣大，那么就從上往下依次執行，id列為null的就表示這是一個結果集，不需要使用它來進行查詢。
　　2.2、第二列 select_type
　　A：simple：表示不需要union操作或者不包含子查詢的簡單select查詢。有連接查詢時，外層的查詢為simple，且只有一個
　　B：primary：一個需要union操作或者含有子查詢的select，位于最外層的單位查詢的select_type即為primary。且只有一個
　　C：union：union連接的兩個select查詢，select .... from table1 union select ..... from table2;第一個查詢(即select .... from table1）是dervied派生表，除了第一個表外，第二個以后的表select_type都是union
　　D：dependent union：與union一樣，出現在union 或union all語句中，但是這個查詢要受到外部查詢的影響
　　E：union result：包含union的結果集，在union和union all語句中,因為它不需要參與查詢，所以id字段為null
　　F：subquery：除了from字句中包含的子查詢外，其他地方出現的子查詢都可能是subquery
　　G：dependent subquery：與dependent union類似，表示這個subquery的查詢要受到外部表查詢的影響
　　H：derived：from字句中出現的子查詢，也叫做派生表，其他數據庫中可能叫做內聯視圖或嵌套select
　　2.3、第三列 table-----顯示的查詢表名
　　1）如果查詢使用了別名，那么這里顯示的是別名，
　　2）如果不涉及對數據表的操作，那么這顯示為null，
　　3）如果顯示為尖括號括起來的<derived N>就表示這個是臨時表，后邊的N就是執行計劃中的id，表示結果來自于這個查詢產生。
　　4）如果是尖括號括起來的<union M,N>，與<derived N>類似，也是一個臨時表，表示這個結果來自于union查詢的id為M,N的結果集。
　　2.4、第四列 type-----顯示查詢數據的途徑，很重要的一個值！?。?！
　　依次從好到差：system>const>eq_ref>ref>fulltext>ref_or_null>unique_subquery>index_subquery>range>index_merge>index>ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個索引
　　A：system：表中只有一行數據或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在這個情況通常都是all或者index
　　B：const：使用唯一索引或者主鍵，返回記錄一定是1行記錄的等值where條件時，通常type是const。其他數據庫也叫做唯一索引掃描

　　C：eq_ref：出現在連接多個表的查詢計劃中，驅動表每次連接只返回一行數據，且這行數據是第二個表的主鍵或者唯一索引，且必須為not null，唯一索引和主鍵是多列時，只有所有的列都用作比較時才會出現eq_ref ，（只有這樣才能保證唯一性）總之一般情況下使用唯一鍵或者主鍵進行單表等值查詢時，一般是const，當多表連接的時候，第一個表每次在連接條件下只返回一行數據，并且這行數據可以通過第二個表的主鍵或者唯一索引檢索出來，并且唯一鍵值不為空，一般為eq_ref；

　　D：ref：沒有主鍵和唯一索引的要求，只要使用相等條件檢索時就可能出現，常見與輔助索引的等值查找?；蛘叨嗔兄麈I、多列唯一索引中，使用第一個列之外的列作為等值查找也會出現，總之，返回數據不唯一的等值查找就可能出現。

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

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

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

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

（編輯：武林網）

上一篇：MySQL每秒57萬的寫入快還是慢

下一篇：MySQL 5.5 FLUSH TABLES WITH READ LOCK語句介紹