建站
咨詢
售后
建站咨詢
新聞中心
mysql中如何查看優(yōu)化器優(yōu)化后的執(zhí)行計(jì)劃
在開始演示之前，我們先介紹下兩個(gè)概念。
成都創(chuàng)新互聯(lián)長(zhǎng)期為超過千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為武安企業(yè)提供專業(yè)的成都網(wǎng)站建設(shè)、做網(wǎng)站，武安網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。
概念一，數(shù)據(jù)的可選擇性基數(shù)，也就是常說的cardinality值。
查詢優(yōu)化器在生成各種執(zhí)行計(jì)劃之前，得先從統(tǒng)計(jì)信息中取得相關(guān)數(shù)據(jù)，這樣才能估算每步操作所涉及到的記錄數(shù)，而這個(gè)相關(guān)數(shù)據(jù)就是cardinality。簡(jiǎn)單來說，就是每個(gè)值在每個(gè)字段中的唯一值分布狀態(tài)。
比如表t1有100行記錄，其中一列為f1。f1中唯一值的個(gè)數(shù)可以是100個(gè)，也可以是1個(gè)，當(dāng)然也可以是1到100之間的任何一個(gè)數(shù)字。這里唯一值越的多少，就是這個(gè)列的可選擇基數(shù)。
那看到這里我們就明白了，為什么要在基數(shù)高的字段上建立索引，而基數(shù)低的的字段建立索引反而沒有全表掃描來的快。當(dāng)然這個(gè)只是一方面，至于更深入的探討就不在我這篇探討的范圍了。
概念二，關(guān)于HINT的使用。
這里我來說下HINT是什么，在什么時(shí)候用。
HINT簡(jiǎn)單來說就是在某些特定的場(chǎng)景下人工協(xié)助MySQL優(yōu)化器的工作，使她生成最優(yōu)的執(zhí)行計(jì)劃。一般來說，優(yōu)化器的執(zhí)行計(jì)劃都是最優(yōu)化的，不過在某些特定場(chǎng)景下，執(zhí)行計(jì)劃可能不是最優(yōu)化。
比如：表t1經(jīng)過大量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經(jīng)很不準(zhǔn)確了，這時(shí)候剛好執(zhí)行了一條SQL，那么有可能這條SQL的執(zhí)行計(jì)劃就不是最優(yōu)的。為什么說有可能呢？
來看下具體演示
譬如，以下兩條SQL，
A：
select * from t1 where f1 = 20;
B：
select * from t1 where f1 = 30;
如果f1的值剛好頻繁更新的值為30，并且沒有達(dá)到MySQL自動(dòng)更新cardinality值的臨界值或者說用戶設(shè)置了手動(dòng)更新又或者用戶減少了sample page等等，那么對(duì)這兩條語(yǔ)句來說，可能不準(zhǔn)確的就是B了。
這里順帶說下，MySQL提供了自動(dòng)更新和手動(dòng)更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊(cè)。
那回到正題上，MySQL 8.0 帶來了幾個(gè)HINT，我今天就舉個(gè)index_merge的例子。
示例表結(jié)構(gòu)：
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)
表記錄數(shù)：
mysql select count(*) from t1;+----------+| count(*) |+----------+| ? ?32768 |+----------+1 row in set (0.01 sec)
這里我們兩條經(jīng)典的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實(shí)際上在rank1,rank2,rank3三列上分別有一個(gè)二級(jí)索引。
那我們來看SQL C的查詢計(jì)劃。
顯然，沒有用到任何索引，掃描的行數(shù)為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給相同的查詢，再次看看查詢計(jì)劃。
這個(gè)時(shí)候用到了index_merge,union了三個(gè)列。掃描的行數(shù)為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)
我們?cè)倏聪耂QL D的計(jì)劃：
不加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)
對(duì)比下以上兩個(gè)，加了HINT的比不加HINT的cost小了100倍。
總結(jié)下，就是說表的cardinality值影響這張的查詢計(jì)劃，如果這個(gè)值沒有正常更新的話，就需要手工加HINT了。相信MySQL未來的版本會(huì)帶來更多的HINT。
如何查看MySQL執(zhí)行計(jì)劃
使用show status命令
mysql會(huì)給出一個(gè)很長(zhǎng)的列表
含義如下:
aborted_clients 客戶端非法中斷連接次數(shù)
aborted_connects 連接mysql失敗次數(shù)
com_xxx xxx命令執(zhí)行次數(shù),有很多條
connections 連接mysql的數(shù)量
Created_tmp_disk_tables 在磁盤上創(chuàng)建的臨時(shí)表
Created_tmp_tables 在內(nèi)存里創(chuàng)建的臨時(shí)表
Created_tmp_files 臨時(shí)文件數(shù)
Mysql執(zhí)行計(jì)劃功能詳解
在Mysql調(diào)優(yōu)過程中其中最關(guān)鍵的一點(diǎn)，就是正確使用執(zhí)行計(jì)劃，從而查看SQL語(yǔ)句的具體執(zhí)行過程和參數(shù)指標(biāo)，來具體場(chǎng)景具體分析，來達(dá)到優(yōu)化SQL語(yǔ)句的執(zhí)行效率的效果
id
select查詢的序列號(hào)，包含一組數(shù)字，表示查詢中執(zhí)行select子句或者操作表的順序
1、如果id相同，那么執(zhí)行順序從上到下
2、如果id不同，如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行
3、id相同和不同的，同時(shí)存在：相同的可以認(rèn)為是一組，從上往下順序執(zhí)行，在所有組中，id值越大，優(yōu)先級(jí)越高，越先執(zhí)行
select_type
主要用來分辨查詢的類型，是普通查詢還是聯(lián)合查詢還是子查詢
table
對(duì)應(yīng)行正在訪問哪張表，表名或者別名，可能是臨時(shí)表或者union合并結(jié)果集
1、如果是具體的表名，則表明從實(shí)際的物理表中獲取數(shù)據(jù)，當(dāng)然也可以是表的別名
2、表名是derivedN的形式，表示使用了id為N的查詢產(chǎn)生的衍生表
3、當(dāng)有union result的時(shí)候，表名是union n1,n2等的形式，n1,n2表示參與union的id
type
type顯示的是訪問類型，訪問類型表示我是以何種方式去訪問我們的數(shù)據(jù)，最容易想的是全表掃描，直接暴力的遍歷一張表去尋找需要的數(shù)據(jù)，效率非常低下，訪問的類型有很多，效率從最好到最壞依次是：
system const eq_ref ref fulltext ref_or_null index_merge
unique_subquery index_subquery range index ALL
possible_keys
顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用
key
實(shí)際使用的索引，如果為null，則沒有使用索引，查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊。
key_len
表示索引中使用的字節(jié)數(shù)，可以通過key_len計(jì)算查詢中使用的索引長(zhǎng)度，在不損失精度的情況下長(zhǎng)度越短越好。
ref
顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)
rows
根據(jù)表的統(tǒng)計(jì)信息及索引使用情況，大致估算出找出所需記錄需要讀取的行數(shù)，此參數(shù)很重要，直接反應(yīng)的sql找了多少數(shù)據(jù)，在完成目的的情況下越少越好
extra
SQL包含額外的信息
怎么去看懂mysql的執(zhí)行計(jì)劃
MySQL 使用 EXPLAIN 后面跟SQL，就直接顯示查詢計(jì)劃 下面是一個(gè)例子： mysql EXPLAIN - SELECT - SALE_DATE, - SUM(SALE_MONEY) AS SUM_MONEY - FROM - SALE_REPORT - GROUP BY - SALE_DATE - ORDER BY - SUM(SALE_MONEY) DESC;
~如果你認(rèn)可我的回答，請(qǐng)及時(shí)點(diǎn)擊【采納為滿意回答】按鈕
~~手機(jī)提問的朋友在客戶端右上角評(píng)價(jià)點(diǎn)【滿意】即可。
~你的采納是我前進(jìn)的動(dòng)力
~~O(∩_∩)O，記得好評(píng)和采納，互相幫助，謝謝。
如何查看mysql中某個(gè)事件的開啟和關(guān)閉
首先在sql中查詢計(jì)劃事件的狀態(tài)：SHOW VARIABLES LIKE 'event_scheduler'
如果返回的是off表示當(dāng)前是關(guān)閉狀態(tài)，如果是on當(dāng)前已經(jīng)開啟了計(jì)劃任務(wù)。
在mysql程序的目錄下找到my.ini文件，添加一個(gè)項(xiàng)：event_scheduler = 1
保存后重啟mysql服務(wù)即可，重啟服務(wù)可以在服務(wù)管理里面找到
也可以用腳本來實(shí)現(xiàn)：
mysql event_scheduler
開啟event_scheduler sql指令：
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
相反，關(guān)閉event_scheduler指令：
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;
Mysql學(xué)會(huì)查看sql的執(zhí)行計(jì)劃
首先在Mysql的服務(wù)中有 連接器、查詢緩存(Mysql8 已經(jīng)刪除)、分析器、優(yōu)化器、執(zhí)行器等，所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)
而一條sql怎么執(zhí)行是由優(yōu)化器決定的,?優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引；或者在一個(gè)語(yǔ)句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序。
而執(zhí)行計(jì)劃就是優(yōu)化器優(yōu)化后的sql的執(zhí)行的詳細(xì)方案
Mysql中查看執(zhí)行計(jì)劃的方式有兩種 : 1. 使用desc? ? 2.使用 explain? 使用它倆的效果是一樣的
接下來要通過執(zhí)行計(jì)劃知道sql是怎么執(zhí)行的
執(zhí)行計(jì)劃中有幾個(gè)重要的字段, 分別是?
id,? table,? type,? possible_keys,? key,? key_len, Extra
id :? 可以通過ID來查看在多表聯(lián)查中sql是先查詢哪張表的 id相同的從上往下依次執(zhí)行,id不同的id大的先執(zhí)行
table :? ?table當(dāng)然就是查詢的表名
type :? 查詢的類型? ?查詢類型分為??ALL,??index,??range,??ref ,?eq_ref,?const(system),??null
? ? ALL: 指的全盤掃描,沒有走任何索引? ?查詢結(jié)果集大于25% 優(yōu)化器可能會(huì)走全盤掃描? ?字符串查詢的時(shí)候一定要加"" 不然可能會(huì)全索引掃描(隱式轉(zhuǎn)換)? ?統(tǒng)計(jì)信息 失效 或者 過舊 也可能走全盤掃描? 因?yàn)閮?yōu)化器會(huì)參考統(tǒng)計(jì)信息來制定執(zhí)行計(jì)劃
???index:?全索引掃描? 就是掃描整顆索引樹
? ? ? ?range: 索引范圍? 查詢索引樹的一部分范圍? ?范圍索引中 ? ? =? =? like? 的效率會(huì)比? or? ?in? 的效率高, 使用like %再前面的不走索引
ref:? ?輔助索引的等值查詢? ??????????
? ? ? ? ? ? ? ? 當(dāng)查詢的數(shù)據(jù)量小,優(yōu)化器也有可能會(huì)走索引的全盤掃描? 這里我就不貼圖了;
???eq_ref : 多表連接查詢中,被連接的表的連接條件列是主鍵或者唯一鍵
???const(system): 主鍵 或者 唯一鍵 的等值查詢
? ? ? ? ? ?null: 沒有數(shù)據(jù)
他們的性能是依次遞增的 全盤掃描性能最差,? const性能最高
possible_keys:? 查詢過程中可能用到的索引
key: 真正使用到的索引
key_len:? 走索引的長(zhǎng)度
? ? 這個(gè)是怎么計(jì)算的呢???
???key_len 的計(jì)算方法 :
int 類型最長(zhǎng)存儲(chǔ)4個(gè)字節(jié)長(zhǎng)度的數(shù)字? 有not null? 是4字節(jié)? 沒有的話會(huì)花1字節(jié)存儲(chǔ)是不是null
tinyint 最大存儲(chǔ)一個(gè)字節(jié)? ? 也會(huì)花1字節(jié)來判斷是不是null
字符串類型 : 字符集 utf8mb4? 1-4字節(jié)
varchar超過255會(huì)預(yù)留2個(gè)字節(jié)存儲(chǔ)長(zhǎng)度 沒超預(yù)留1個(gè)字節(jié)
key_len 永遠(yuǎn)是你設(shè)置的長(zhǎng)度的最大的??
聯(lián)合索引可以通過key_len 來判斷走了幾個(gè)索引
? ? 使用desc format=json select * from table 可以查看詳細(xì)情況
filtered:? 索引掃描過濾掉數(shù)據(jù)的占比
Extra: 額外的信息?
??? Using filesort ：MySQL?對(duì)數(shù)據(jù)在sql層進(jìn)行了排序，而不是按照表內(nèi)的索引進(jìn)行排序讀 取。 效率比較低
??? Using temporary ：使用臨時(shí)表保存中間結(jié)果，也就是說 MySQL 在對(duì)查詢結(jié)果排序時(shí)使用了臨時(shí)表，常見于order by 或 group by。
??? Using index ：表示 SQL 操作中使用了覆蓋索引（Covering Index），避免了訪問表的數(shù)據(jù)行，效率高。
??? Using index condition ：表示 SQL 操作命中了索引，但不是所有的列數(shù)據(jù)都在索引樹上，還需要訪問實(shí)際的行記錄。
??? Using where ：表示 SQL 操作使用了 where 過濾條件。
??? Select tables optimized away ：基于索引優(yōu)化 MIN/MAX 操作或者 MyISAM 存儲(chǔ)引擎優(yōu)化 COUNT(*) 操作，不必等到執(zhí)行階段再進(jìn)行計(jì)算，查詢執(zhí)行計(jì)劃生成的階段即可完成優(yōu)化。
Using join buffer (Block Nested Loop) ：表示 SQL 操作使用了關(guān)聯(lián)查詢或者子查詢，且需要進(jìn)行嵌套循環(huán)計(jì)算
名稱欄目：mysql怎么看查詢計(jì)劃,如何查看數(shù)據(jù)庫(kù)執(zhí)行計(jì)劃
網(wǎng)頁(yè)URL：http://ef60e0e.cn/article/dscsccj.html
