本篇內(nèi)容主要講解“MySQL中的查詢優(yōu)化器怎么用”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL中的查詢優(yōu)化器怎么用”吧!
公司主營(yíng)業(yè)務(wù)：成都做網(wǎng)站、網(wǎng)站制作、移動(dòng)網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。成都創(chuàng)新互聯(lián)公司是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來驚喜。成都創(chuàng)新互聯(lián)公司推出開化免費(fèi)做網(wǎng)站回饋大家。
對(duì)于一個(gè)SQL語句，查詢優(yōu)化器先看是不是能轉(zhuǎn)換成JOIN，再將JOIN進(jìn)行優(yōu)化
優(yōu)化分為：1. 條件優(yōu)化，2.計(jì)算全表掃描成本，3. 找出所有能用到的索引，4. 針對(duì)每個(gè)索引計(jì)算不同的訪問方式的成本，5. 選出成本最小的索引以及訪問方式
-- 開啟 set optimizer_trace="enabled=on"; -- 執(zhí)行sql -- 查看日志信息 select * from information_schema.OPTIMIZER_TRACE; -- 關(guān)閉 set optimizer_trace="enabled=off";
1、常量傳遞（constant_propagation）
a = 1 AND b > a
上面這個(gè)sql可以轉(zhuǎn)換為：
a = 1 AND b > 1
2、等值傳遞（equality_propagation）
a = b and b = c and c = 5
上面這個(gè)sql可以轉(zhuǎn)換為：
a = 5 and b = 5 and c = 5
3、移除沒用的條件（trivial_condition_removal）
a = 1 and 1 = 1
上面這個(gè)sql可以轉(zhuǎn)換為：
a = 1
4、基于成本
一個(gè)查詢可以有不同的執(zhí)行方案，可以選擇某個(gè)索引進(jìn)行查詢，也可以選擇全表掃描，查詢優(yōu)化器會(huì)選擇其中成本最低的方案去執(zhí)行查詢。
1）I/O成本
InnoDB存儲(chǔ)引擎都是將數(shù)據(jù)和索引都存儲(chǔ)到磁盤上的，當(dāng)我們想查詢表中的記錄時(shí)，需要先把數(shù)據(jù)或者索引加載到內(nèi)存中然后再操作。這個(gè)從磁盤到內(nèi)存這個(gè)加載的過程損耗的時(shí)間稱之為I/O成本
2）CPU成本
讀取以及檢測(cè)記錄是否滿足對(duì)應(yīng)的搜索條件、對(duì)結(jié)果集進(jìn)行排序等這些操作損耗的時(shí)間稱之為CPU成本。
InnoDB存儲(chǔ)引擎規(guī)定讀取一個(gè)頁(yè)面花費(fèi)的成本默認(rèn)是1.0，讀取以及檢測(cè)一條記錄是否符合搜索條件的成本默認(rèn)是0.2。
在一條單表查詢語句真正執(zhí)行之前，MySQL的查詢優(yōu)化器會(huì)找出執(zhí)行該語句所有可能使用的方案，對(duì)比之后找出成本最低的方案，這個(gè)成本最低的方案就是所謂的執(zhí)行計(jì)劃，之后才會(huì)調(diào)用存儲(chǔ)引擎提供的接口真正的執(zhí)行查詢。
下邊我們就以一個(gè)實(shí)例來分析一下這些步驟，單表查詢語句如下：
select * from employees.titles where emp_no > '10101' and emp_no < '20000' and to_date = '1991-10-10';
1、根據(jù)搜索條件，找出所有可能使用的索引
? emp_no > ‘10101’，這個(gè)搜索條件可以使用主鍵索引PRIMARY。
? to_date = ‘1991-10-10’，這個(gè)搜索條件可以使用二級(jí)索引idx_titles_to_date。
綜上所述，上邊的查詢語句可能用到的索引，也就是possible keys只有PRIMARY和idx_titles_to_date。
2、計(jì)算全表掃描的代價(jià)
對(duì)于InnoDB存儲(chǔ)引擎來說，全表掃描的意思就是把聚簇索引中的記錄都依次和給定的搜索條件做一下比較，把符合搜索條件的記錄加入到結(jié)果集，所以需要將聚簇索引對(duì)應(yīng)的頁(yè)面加載到內(nèi)存中，然后再檢測(cè)記錄是否符合搜索條件。由于查詢成本=I/O成本+CPU成本，所以計(jì)算全表掃描的代價(jià)需要兩個(gè)信息：
1）聚簇索引占用的頁(yè)面數(shù)
2）該表中的記錄數(shù)
MySQL為每個(gè)表維護(hù)了一系列的統(tǒng)計(jì)信息，SHOW TABLE STATUS語句來查看表的統(tǒng)計(jì)信息。
SHOW TABLE STATUS LIKE 'titles';
Rows
表示表中的記錄條數(shù)。對(duì)于使用MyISAM存儲(chǔ)引擎的表來說，該值是準(zhǔn)確的，對(duì)于使用InnoDB存儲(chǔ)引擎的表來說，該值是一個(gè)估計(jì)值。
Data_length
表示表占用的存儲(chǔ)空間字節(jié)數(shù)。使用MyISAM存儲(chǔ)引擎的表來說，該值就是數(shù)據(jù)文件的大小，對(duì)于使用InnoDB存儲(chǔ)引擎的表來說，該值就相當(dāng)于聚簇索引占用的存儲(chǔ)空間大小，也就是說可以這樣計(jì)算該值的大?。?/p>
Data_length = 聚簇索引的頁(yè)面數(shù)量 x 每個(gè)頁(yè)面的大小
我們的titles使用默認(rèn)16KB的頁(yè)面大小，而上邊查詢結(jié)果顯示Data_length的值是20512768，所以我們可以反向來推導(dǎo)出聚簇索引的頁(yè)面數(shù)量：
聚簇索引的頁(yè)面數(shù)量 = Data_length ÷ 16 ÷ 1024 = 20512768 ÷ 16 ÷ 1024 = 1252
我們現(xiàn)在已經(jīng)得到了聚簇索引占用的頁(yè)面數(shù)量以及該表記錄數(shù)的估計(jì)值，所以就可以計(jì)算全表掃描成本了。但是MySQL在真實(shí)計(jì)算成本時(shí)會(huì)進(jìn)行一些微調(diào)。
I/O成本：12521 = 1252。1252指的是聚簇索引占用的頁(yè)面數(shù)，1.0指的是加載一個(gè)頁(yè)面的成本常數(shù)。
CPU成本：4420700.2=88414。442070指的是統(tǒng)計(jì)數(shù)據(jù)中表的記錄數(shù)，對(duì)于InnoDB存儲(chǔ)引擎來說是一個(gè)估計(jì)值，0.2指的是訪問一條記錄所需的成本常數(shù)
總成本：1252+88414 = 89666。
綜上所述，對(duì)于titles的全表掃描所需的總成本就是89666。
我們前邊說過表中的記錄其實(shí)都存儲(chǔ)在聚簇索引對(duì)應(yīng)B+樹的葉子節(jié)點(diǎn)中，所以只要我們通過根節(jié)點(diǎn)獲得了最左邊的葉子節(jié)點(diǎn)，就可以沿著葉子節(jié)點(diǎn)組成的雙向鏈表把所有記錄都查看一遍。也就是說全表掃描這個(gè)過程其實(shí)有的B+樹內(nèi)節(jié)點(diǎn)是不需要訪問的，但是MySQL在計(jì)算全表掃描成本時(shí)直接使用聚簇索引占用的頁(yè)面數(shù)作為計(jì)算I/O成本的依據(jù)，是不區(qū)分內(nèi)節(jié)點(diǎn)和葉子節(jié)點(diǎn)的。
3、計(jì)算PRIMARY需要成本
計(jì)算PRIMARY需要多少成本的關(guān)鍵問題是：需要預(yù)估出根據(jù)對(duì)應(yīng)的where條件在主鍵索引B+樹中存在多少條符合條件的記錄。
范圍區(qū)間數(shù)
當(dāng)我們從索引中查詢記錄時(shí)，不管是=、in、>、<這些操作都需要從索引中確定一個(gè)范圍，不論這個(gè)范圍區(qū)間的索引到底占用了多少頁(yè)面，查詢優(yōu)化器粗暴的認(rèn)為讀取索引的一個(gè)范圍區(qū)間的I/O成本和讀取一個(gè)頁(yè)面是相同的。
本例中使用PRIMARY的范圍區(qū)間只有一個(gè)：(10101, 20000)，所以相當(dāng)于訪問這個(gè)范圍區(qū)間的索引付出的I/O成本就是：1 x 1.0 = 1.0
預(yù)估范圍內(nèi)的記錄數(shù)
優(yōu)化器需要計(jì)算索引的某個(gè)范圍區(qū)間到底包含多少條記錄，對(duì)于本例來說就是要計(jì)算PRIMARY在(10101, 20000)這個(gè)范圍區(qū)間中包含多少條數(shù)據(jù)記錄，計(jì)算過程是這樣的：
步驟1：先根據(jù)emp_no > 10101這個(gè)條件訪問一下PRIMARY對(duì)應(yīng)的B+樹索引，找到滿足emp_no > 10101這個(gè)條件的第一條記錄，我們把這條記錄稱之為區(qū)間最左記錄。
步驟2：然后再根據(jù)emp_no < 20000這個(gè)條件繼續(xù)從PRIMARY對(duì)應(yīng)的B+樹索引中找出第一條滿足這個(gè)條件的記錄，我們把這條記錄稱之為區(qū)間最右記錄。
步驟3：如果區(qū)間最左記錄和區(qū)間最右記錄相隔不太遠(yuǎn)（只要相隔不大于10個(gè)頁(yè)面即可），那就可以精確統(tǒng)計(jì)出滿足emp_no > '10101' and emp_no < '20000'條件的記錄條數(shù)。否則只沿著區(qū)間最左記錄向右讀10個(gè)頁(yè)面，計(jì)算平均每個(gè)頁(yè)面中包含多少記錄，然后用這個(gè)平均值乘以區(qū)間最左記錄和區(qū)間最右記錄之間的頁(yè)面數(shù)量就可以了。那么問題又來了，怎么估計(jì)區(qū)間最左記錄和區(qū)間最右記錄之間有多少個(gè)頁(yè)面呢？計(jì)算它們父節(jié)點(diǎn)中對(duì)應(yīng)的目錄項(xiàng)記錄之間隔著幾條記錄就可以了。
根據(jù)上面的步驟可以算出來PRIMARY索引的記錄條數(shù)，所以讀取記錄的CPU成本為：26808*0.2=5361.6，其中26808是預(yù)估的需要讀取的數(shù)據(jù)記錄條數(shù)，0.2是讀取一條記錄成本常數(shù)。
PRIMARY的總成本
確定訪問的IO成本+過濾數(shù)據(jù)的CPU成本=1+5361.6=5362.6
4、計(jì)算idx_titles_to_date需要成本
因?yàn)橥ㄟ^二級(jí)索引查詢需要回表，所以在計(jì)算二級(jí)索引需要成本時(shí)還要加上回表的成本，而回表的成本就相當(dāng)于下面這個(gè)SQL執(zhí)行：
select * from employees.titles where 主鍵字段 in （主鍵值1，主鍵值2，。。。，主鍵值3）;
所以idx_titles_to_date的成本 = 輔助索引的查詢成本 + 回表查詢的成本
5、比較各成本選出最優(yōu)者
選擇成本最小的索引
有時(shí)候使用索引執(zhí)行查詢時(shí)會(huì)有許多單點(diǎn)區(qū)間，比如使用IN語句就很容易產(chǎn)生非常多的單點(diǎn)區(qū)間，比如下邊這個(gè)查詢：
重慶分公司
重慶分公司
select * from employees.titles where to_date in ('a','b','c','d', ..., 'e');很顯然，這個(gè)查詢可能使用到的索引就是idx_titles_to_date，由于這個(gè)索引并不是唯一二級(jí)索引，所以并不能確定一個(gè)單點(diǎn)區(qū)間對(duì)應(yīng)的二級(jí)索引記錄的條數(shù)有多少，需要我們?nèi)ビ?jì)算。計(jì)算方式我們上邊已經(jīng)介紹過了，就是先獲取索引對(duì)應(yīng)的B+樹的區(qū)間最左記錄和區(qū)間最右記錄，然后再計(jì)算這兩條記錄之間有多少記錄（記錄條數(shù)少的時(shí)候可以做到精確計(jì)算，多的時(shí)候只能估算）。這種通過直接訪問索引對(duì)應(yīng)的B+樹來計(jì)算某個(gè)范圍區(qū)間對(duì)應(yīng)的索引記錄條數(shù)的方式稱之為index pe。
如果只有幾個(gè)單點(diǎn)區(qū)間的話，使用index pe的方式去計(jì)算這些單點(diǎn)區(qū)間對(duì)應(yīng)的記錄數(shù)也不是什么問題，可是如果很多呢，比如有20000次，MySQL的查詢優(yōu)化器為了計(jì)算這些單點(diǎn)區(qū)間對(duì)應(yīng)的索引記錄條數(shù)，要進(jìn)行20000次index pe操作，那么這種情況下是很耗性能的，所以MySQL提供了一個(gè)系統(tǒng)變量eq_range_index_pe_limit，我們看一下這個(gè)系統(tǒng)變量的默認(rèn)值：SHOW VARIABLES LIKE ‘%pe%’;為200。
也就是說如果我們的IN語句中的參數(shù)個(gè)數(shù)小于200個(gè)的話，將使用index pe的方式計(jì)算各個(gè)單點(diǎn)區(qū)間對(duì)應(yīng)的記錄條數(shù)，如果大于或等于200個(gè)的話，可就不能使用index pe了，要使用所謂的索引統(tǒng)計(jì)數(shù)據(jù)來進(jìn)行估算。像會(huì)為每個(gè)表維護(hù)一份統(tǒng)計(jì)數(shù)據(jù)一樣，MySQL也會(huì)為表中的每一個(gè)索引維護(hù)一份統(tǒng)計(jì)數(shù)據(jù)，查看某個(gè)表中索引的統(tǒng)計(jì)數(shù)據(jù)可以使用SHOW INDEX FROM 表名的語法。
Cardinality屬性表示索引列中不重復(fù)值的個(gè)數(shù)。比如對(duì)于一個(gè)一萬行記錄的表來說，某個(gè)索引列的Cardinality屬性是10000，那意味著該列中沒有重復(fù)的值，如果Cardinality屬性是1的話，就意味著該列的值全部是重復(fù)的。不過需要注意的是，對(duì)于InnoDB存儲(chǔ)引擎來說，使用SHOW INDEX語句展示出來的某個(gè)索引列的Cardinality屬性是一個(gè)估計(jì)值，并不是精確的?？梢愿鶕?jù)這個(gè)屬性來估算IN語句中的參數(shù)所對(duì)應(yīng)的記錄數(shù)：
1）使用SHOW TABLE STATUS展示出的Rows值，也就是一個(gè)表中有多少條記錄。
2）使用SHOW INDEX語句展示出的Cardinality屬性。
3）根據(jù)上面兩個(gè)值可以算出idx_key1索引對(duì)于的key1列平均單個(gè)值的重復(fù)次數(shù)：Rows/Cardinality
4）所以總共需要回表的記錄數(shù)就是：IN語句中的參數(shù)個(gè)數(shù)*Rows/Cardinality。
NULL值處理
上面知道在統(tǒng)計(jì)列不重復(fù)值的時(shí)候，會(huì)影響到查詢優(yōu)化器。
對(duì)于NULL，有三種理解方式：
NULL值代表一個(gè)未確定的值，每一個(gè)NULL值都是獨(dú)一無二的，在統(tǒng)計(jì)列不重復(fù)值的時(shí)候應(yīng)該都當(dāng)作獨(dú)立的。
NULL值在業(yè)務(wù)上就是代表沒有，所有的NULL值代表的意義是一樣的，所以所有的NULL值都一樣，在統(tǒng)計(jì)列不重復(fù)值的時(shí)候應(yīng)該只算一個(gè)。
NULL完全沒有意義，在統(tǒng)計(jì)列不重復(fù)值的時(shí)候應(yīng)該忽略NULL。
innodb提供了一個(gè)系統(tǒng)變量：
show global variables like '%innodb_stats_method%';
這個(gè)變量有三個(gè)值：
nulls_equal：認(rèn)為所有NULL值都是相等的。這個(gè)值也是innodb_stats_method的默認(rèn)值。如果某個(gè)索引列中NULL值特別多的話，這種統(tǒng)計(jì)方式會(huì)讓優(yōu)化器認(rèn)為某個(gè)列中平均一個(gè)值重復(fù)次數(shù)特別多，所以傾向于不使用索引進(jìn)行訪問。
nulls_unequal：認(rèn)為所有NULL值都是不相等的。如果某個(gè)索引列中NULL值特別多的話，這種統(tǒng)計(jì)方式會(huì)讓優(yōu)化器認(rèn)為某個(gè)列中平均一個(gè)值重復(fù)次數(shù)特別少，所以傾向于使用索引進(jìn)行訪問。
nulls_ignored：直接把NULL值忽略掉。
最好不在索引列中存放NULL值才是正解
InnoDB提供了兩種存儲(chǔ)統(tǒng)計(jì)數(shù)據(jù)的方式：
? 統(tǒng)計(jì)數(shù)據(jù)存儲(chǔ)在磁盤上。
? 統(tǒng)計(jì)數(shù)據(jù)存儲(chǔ)在內(nèi)存中，當(dāng)服務(wù)器關(guān)閉時(shí)這些這些統(tǒng)計(jì)數(shù)據(jù)就都被清除掉了。
MySQL給我們提供了系統(tǒng)變量innodb_stats_persistent來控制到底采用哪種方式去存儲(chǔ)統(tǒng)計(jì)數(shù)據(jù)。在MySQL 5.6.6之前，innodb_stats_persistent的值默認(rèn)是OFF，也就是說InnoDB的統(tǒng)計(jì)數(shù)據(jù)默認(rèn)是存儲(chǔ)到內(nèi)存的，之后的版本中innodb_stats_persistent的值默認(rèn)是ON，也就是統(tǒng)計(jì)數(shù)據(jù)默認(rèn)被存儲(chǔ)到磁盤中。
不過InnoDB默認(rèn)是以表為單位來收集和存儲(chǔ)統(tǒng)計(jì)數(shù)據(jù)的，也就是說我們可以把某些表的統(tǒng)計(jì)數(shù)據(jù)（以及該表的索引統(tǒng)計(jì)數(shù)據(jù)）存儲(chǔ)在磁盤上，把另一些表的統(tǒng)計(jì)數(shù)據(jù)存儲(chǔ)在內(nèi)存中。我們可以在創(chuàng)建和修改表的時(shí)候通過指定STATS_PERSISTENT屬性來指明該表的統(tǒng)計(jì)數(shù)據(jù)存儲(chǔ)方式。
1、基于磁盤的永久性統(tǒng)計(jì)數(shù)據(jù)
當(dāng)我們選擇把某個(gè)表以及該表索引的統(tǒng)計(jì)數(shù)據(jù)存放到磁盤上時(shí)，實(shí)際上是把這些統(tǒng)計(jì)數(shù)據(jù)存儲(chǔ)到了兩個(gè)表里：
? innodb_table_stats存儲(chǔ)了關(guān)于表的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對(duì)應(yīng)著一個(gè)表的統(tǒng)計(jì)數(shù)據(jù)
? innodb_index_stats存儲(chǔ)了關(guān)于索引的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對(duì)應(yīng)著一個(gè)索引的一個(gè)統(tǒng)計(jì)項(xiàng)的統(tǒng)計(jì)數(shù)據(jù)
2、定期更新統(tǒng)計(jì)數(shù)據(jù)
? 系統(tǒng)變量innodb_stats_auto_recalc決定著服務(wù)器是否自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，它的默認(rèn)值是ON，也就是該功能默認(rèn)是開啟的。每個(gè)表都維護(hù)了一個(gè)變量，該變量記錄著對(duì)該表進(jìn)行增刪改的記錄條數(shù)，如果發(fā)生變動(dòng)的記錄數(shù)量超過了表大小的10%，并且自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)的功能是打開的，那么服務(wù)器會(huì)重新進(jìn)行一次統(tǒng)計(jì)數(shù)據(jù)的計(jì)算，并且更新innodb_table_stats和innodb_index_stats表。不過自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)的過程是異步發(fā)生的，也就是即使表中變動(dòng)的記錄數(shù)超過了10%，自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)也不會(huì)立即發(fā)生，可能會(huì)延遲幾秒才會(huì)進(jìn)行計(jì)算。
?如果innodb_stats_auto_recalc系統(tǒng)變量的值為OFF的話，我們也可以手動(dòng)調(diào)用ANALYZE TABLE語句來重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)。ANALYZE TABLE single_table;
3、控制執(zhí)行計(jì)劃
Index Hints
?USE INDEX：限制索引的使用范圍，在數(shù)據(jù)表里建立了很多索引，當(dāng)MySQL對(duì)索引進(jìn)行選擇時(shí)，這些索引都在考慮的范圍內(nèi)。但有時(shí)我們希望MySQL只考慮幾個(gè)索引，而不是全部的索引，這就需要用到USE INDEX對(duì)查詢語句進(jìn)行設(shè)置。
?IGNORE INDEX ：限制不使用索引的范圍
?FORCE INDEX：我們希望MySQL必須要使用某一個(gè)索引(由于 MySQL在查詢時(shí)只能使用一個(gè)索引，因此只能強(qiáng)迫MySQL使用一個(gè)索引)。這就需要使用FORCE INDEX來完成這個(gè)功能。
基本語法格式：
SELECT * FROM table1 USE|IGNORE|FORCE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3
到此，相信大家對(duì)“MySQL中的查詢優(yōu)化器怎么用”有了更深的了解，不妨來實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
