mysql中如何查看優化器優化后的執行計劃

在開始演示之前，我們先介紹下兩個概念。

康馬網站制作公司哪家好，找成都創新互聯！從網頁設計、網站建設、微信開發、APP開發、響應式網站等網站項目制作，到程序開發，運營維護。成都創新互聯公司2013年成立到現在10年的時間，我們擁有了豐富的建站經驗和運維經驗，來保證我們的工作的順利進行。專注于網站建設就選成都創新互聯。

概念一，數據的可選擇性基數，也就是常說的cardinality值。

查詢優化器在生成各種執行計劃之前，得先從統計信息中取得相關數據，這樣才能估算每步操作所涉及到的記錄數，而這個相關數據就是cardinality。簡單來說，就是每個值在每個字段中的唯一值分布狀態。

比如表t1有100行記錄，其中一列為f1。f1中唯一值的個數可以是100個，也可以是1個，當然也可以是1到100之間的任何一個數字。這里唯一值越的多少，就是這個列的可選擇基數。

那看到這里我們就明白了，為什么要在基數高的字段上建立索引，而基數低的的字段建立索引反而沒有全表掃描來的快。當然這個只是一方面，至于更深入的探討就不在我這篇探討的范圍了。

概念二，關于HINT的使用。

這里我來說下HINT是什么，在什么時候用。

HINT簡單來說就是在某些特定的場景下人工協助MySQL優化器的工作，使她生成最優的執行計劃。一般來說，優化器的執行計劃都是最優化的，不過在某些特定場景下，執行計劃可能不是最優化。

比如：表t1經過大量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經很不準確了，這時候剛好執行了一條SQL，那么有可能這條SQL的執行計劃就不是最優的。為什么說有可能呢？

來看下具體演示

譬如，以下兩條SQL，

A：

select * from t1 where f1 = 20;

B：

select * from t1 where f1 = 30;

如果f1的值剛好頻繁更新的值為30，并且沒有達到MySQL自動更新cardinality值的臨界值或者說用戶設置了手動更新又或者用戶減少了sample page等等，那么對這兩條語句來說，可能不準確的就是B了。

這里順帶說下，MySQL提供了自動更新和手動更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊。

那回到正題上，MySQL 8.0 帶來了幾個HINT，我今天就舉個index_merge的例子。

示例表結構：

mysql desc t1;+------------+--------------+------+-----+---------+----------------+| Field ? ? ?| Type ? ? ? ? | Null | Key | Default | Extra ? ? ? ? ?|+------------+--------------+------+-----+---------+----------------+| id ? ? ? ? | int(11) ? ? ?| NO ? | PRI | NULL ? ?| auto_increment || rank1 ? ? ?| int(11) ? ? ?| YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|| rank2 ? ? ?| int(11) ? ? ?| YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|| log_time ? | datetime ? ? | YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|| prefix_uid | varchar(100) | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|| desc1 ? ? ?| text ? ? ? ? | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|| rank3 ? ? ?| int(11) ? ? ?| YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

表記錄數：

mysql select count(*) from t1;+----------+| count(*) |+----------+| ? ?32768 |+----------+1 row in set (0.01 sec)

這里我們兩條經典的SQL：

SQL C：

select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

SQL D：

select * from t1 where rank1 =100 ?and rank2 =100 ?and rank3 =100;

表t1實際上在rank1,rank2,rank3三列上分別有一個二級索引。

那我們來看SQL C的查詢計劃。

顯然，沒有用到任何索引，掃描的行數為32034，cost為3243.65。

mysql explain ?format=json select * from t1 ?where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "3243.65" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "ALL", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"rows_examined_per_scan": 32034, ? ? ?"rows_produced_per_join": 115, ? ? ?"filtered": "0.36", ? ? ?"cost_info": { ? ? ? ?"read_cost": "3232.07", ? ? ? ?"eval_cost": "11.58", ? ? ? ?"prefix_cost": "3243.65", ? ? ? ?"data_read_per_join": "49K" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

我們加上hint給相同的查詢，再次看看查詢計劃。

這個時候用到了index_merge,union了三個列。掃描的行數為1103，cost為441.09，明顯比之前的快了好幾倍。

mysql explain ?format=json select /*+ index_merge(t1) */ * from t1 ?where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "441.09" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "index_merge", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"key": "union(idx_rank1,idx_rank2,idx_rank3)", ? ? ?"key_length": "5,5,5", ? ? ?"rows_examined_per_scan": 1103, ? ? ?"rows_produced_per_join": 1103, ? ? ?"filtered": "100.00", ? ? ?"cost_info": { ? ? ? ?"read_cost": "330.79", ? ? ? ?"eval_cost": "110.30", ? ? ? ?"prefix_cost": "441.09", ? ? ? ?"data_read_per_join": "473K" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

我們再看下SQL D的計劃：

不加HINT，

mysql explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "534.34" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "ref", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"key": "idx_rank1", ? ? ?"used_key_parts": [ ? ? ? ?"rank1" ? ? ?], ? ? ?"key_length": "5", ? ? ?"ref": [ ? ? ? ?"const" ? ? ?], ? ? ?"rows_examined_per_scan": 555, ? ? ?"rows_produced_per_join": 0, ? ? ?"filtered": "0.07", ? ? ?"cost_info": { ? ? ? ?"read_cost": "478.84", ? ? ? ?"eval_cost": "0.04", ? ? ? ?"prefix_cost": "534.34", ? ? ? ?"data_read_per_join": "176" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

加了HINT，

mysql explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "5.23" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "index_merge", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"key": "intersect(idx_rank1,idx_rank2,idx_rank3)", ? ? ?"key_length": "5,5,5", ? ? ?"rows_examined_per_scan": 1, ? ? ?"rows_produced_per_join": 1, ? ? ?"filtered": "100.00", ? ? ?"cost_info": { ? ? ? ?"read_cost": "5.13", ? ? ? ?"eval_cost": "0.10", ? ? ? ?"prefix_cost": "5.23", ? ? ? ?"data_read_per_join": "440" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

對比下以上兩個，加了HINT的比不加HINT的cost小了100倍。

總結下，就是說表的cardinality值影響這張的查詢計劃，如果這個值沒有正常更新的話，就需要手工加HINT了。相信MySQL未來的版本會帶來更多的HINT。

如何查看MySQL執行計劃

使用show status命令

mysql會給出一個很長的列表

含義如下:

aborted_clients 客戶端非法中斷連接次數

aborted_connects 連接mysql失敗次數

com_xxx xxx命令執行次數,有很多條

connections 連接mysql的數量

Created_tmp_disk_tables 在磁盤上創建的臨時表

Created_tmp_tables 在內存里創建的臨時表

Created_tmp_files 臨時文件數

Mysql學會查看sql的執行計劃

首先在Mysql的服務中有 連接器、查詢緩存(Mysql8 已經刪除)、分析器、優化器、執行器等，所有跨存儲引擎的功能都在這一層實現

而一條sql怎么執行是由優化器決定的,?優化器是在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序。

而執行計劃就是優化器優化后的sql的執行的詳細方案

Mysql中查看執行計劃的方式有兩種 : 1. 使用desc? ? 2.使用 explain? 使用它倆的效果是一樣的

接下來要通過執行計劃知道sql是怎么執行的

執行計劃中有幾個重要的字段, 分別是?

id,? table,? type,? possible_keys,? key,? key_len, Extra

id :? 可以通過ID來查看在多表聯查中sql是先查詢哪張表的 id相同的從上往下依次執行,id不同的id大的先執行

table :? ?table當然就是查詢的表名

type :? 查詢的類型? ?查詢類型分為??ALL,??index,??range,??ref ,?eq_ref,?const(system),??null

? ? ALL: 指的全盤掃描,沒有走任何索引? ?查詢結果集大于25% 優化器可能會走全盤掃描? ?字符串查詢的時候一定要加"" 不然可能會全索引掃描(隱式轉換)? ?統計信息 失效 或者 過舊 也可能走全盤掃描? 因為優化器會參考統計信息來制定執行計劃

???index:?全索引掃描? 就是掃描整顆索引樹

? ? ? ?range: 索引范圍? 查詢索引樹的一部分范圍? ?范圍索引中 ? ? =? =? like? 的效率會比? or? ?in? 的效率高, 使用like %再前面的不走索引

ref:? ?輔助索引的等值查詢? ??????????

? ? ? ? ? ? ? ? 當查詢的數據量小,優化器也有可能會走索引的全盤掃描? 這里我就不貼圖了;

???eq_ref : 多表連接查詢中,被連接的表的連接條件列是主鍵或者唯一鍵

???const(system): 主鍵 或者 唯一鍵 的等值查詢

? ? ? ? ? ?null: 沒有數據

他們的性能是依次遞增的 全盤掃描性能最差,? const性能最高

possible_keys:? 查詢過程中可能用到的索引

key: 真正使用到的索引

key_len:? 走索引的長度

? ? 這個是怎么計算的呢???

???key_len 的計算方法 :

int 類型最長存儲4個字節長度的數字? 有not null? 是4字節? 沒有的話會花1字節存儲是不是null

tinyint 最大存儲一個字節? ? 也會花1字節來判斷是不是null

字符串類型 : 字符集 utf8mb4? 1-4字節

varchar超過255會預留2個字節存儲長度 沒超預留1個字節

key_len 永遠是你設置的長度的最大的??

聯合索引可以通過key_len 來判斷走了幾個索引

? ? 使用desc format=json select * from table 可以查看詳細情況

filtered:? 索引掃描過濾掉數據的占比

Extra: 額外的信息?

??? Using filesort ：MySQL?對數據在sql層進行了排序，而不是按照表內的索引進行排序讀 取。 效率比較低

??? Using temporary ：使用臨時表保存中間結果，也就是說 MySQL 在對查詢結果排序時使用了臨時表，常見于order by 或 group by。

??? Using index ：表示 SQL 操作中使用了覆蓋索引（Covering Index），避免了訪問表的數據行，效率高。

??? Using index condition ：表示 SQL 操作命中了索引，但不是所有的列數據都在索引樹上，還需要訪問實際的行記錄。

??? Using where ：表示 SQL 操作使用了 where 過濾條件。

??? Select tables optimized away ：基于索引優化 MIN/MAX 操作或者 MyISAM 存儲引擎優化 COUNT(*) 操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即可完成優化。

Using join buffer (Block Nested Loop) ：表示 SQL 操作使用了關聯查詢或者子查詢，且需要進行嵌套循環計算

網站欄目：mysql執行計劃怎么找 mysql查看執行計劃
分享鏈接：http://vcdvsql.cn/article6/hehiig.html

成都網站建設公司_創新互聯，為您提供商城網站、自適應網站、微信公眾號、做網站、、網站設計公司

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯