技術文章 » 深入解析MySQL中的查詢優化器（工作原理詳解）

深入解析MySQL中的查詢優化器（工作原理詳解）

2022-07-04 22:00:19
本篇文章帶大家深入解析一下MySQL中的查詢優化器，帶大家理解一下mysql查詢優化器工作原理，希望對大家有所幫助！

對於一個SQL語句，查詢優化器先看是不是能轉換成JOIN，再將JOIN進行優化

優化分為：1. 條件優化，2.計算全表掃描成本，3. 找出所有能用到的索引，4. 針對每個索引計算不同的存取方式的成本，5. 選出成本最小的索引以及存取方式

一、開啟查詢優化器紀錄檔

-- 開啟
set optimizer_trace="enabled=on"; 
-- 執行sql
-- 檢視紀錄檔資訊
select * from information_schema.OPTIMIZER_TRACE;
-- 關閉
set optimizer_trace="enabled=off";

二、優化器原則

  • 1、常數傳遞（constant_propagation）
    a = 1 AND b > a
    上面這個sql可以轉換為：
    a = 1 AND b > 1

  • 2、等值傳遞（equality_propagation）
    a = b and b = c and c = 5
    上面這個sql可以轉換為：
    a = 5 and b = 5 and c = 5

  • 3、移除沒用的條件（trivial_condition_removal）
    a = 1 and 1 = 1
    上面這個sql可以轉換為：
    a = 1

  • 4、基於成本
    一個查詢可以有不同的執行方案，可以選擇某個索引進行查詢，也可以選擇全表掃描，查詢優化器會選擇其中成本最低的方案去執行查詢。
    1）I/O成本
    InnoDB儲存引擎都是將資料和索引都儲存到磁碟上的，當我們想查詢表中的記錄時，需要先把資料或者索引載入到記憶體中然後再操作。這個從磁碟到記憶體這個載入的過程損耗的時間稱之為I/O成本
    2）CPU成本
    讀取以及檢測記錄是否滿足對應的搜尋條件、對結果集進行排序等這些操作損耗的時間稱之為CPU成本。
    InnoDB儲存引擎規定讀取一個頁面花費的成本預設是1.0，讀取以及檢測一條記錄是否符合搜尋條件的成本預設是0.2。

三、基於成本的優化步驟

在一條單表查詢語句真正執行之前，MySQL的查詢優化器會找出執行該語句所有可能使用的方案，對比之後找出成本最低的方案，這個成本最低的方案就是所謂的執行計劃，之後才會呼叫儲存引擎提供的介面真正的執行查詢。
下邊我們就以一個範例來分析一下這些步驟，單表查詢語句如下：

select * from employees.titles where emp_no > '10101' and emp_no < '20000' and to_date = '1991-10-10';

1、根據搜尋條件，找出所有可能使用的索引

• emp_no > ‘10101’，這個搜尋條件可以使用主鍵索引PRIMARY。
• to_date = ‘1991-10-10’，這個搜尋條件可以使用二級索引idx_titles_to_date。

綜上所述，上邊的查詢語句可能用到的索引，也就是possible keys只有PRIMARY和idx_titles_to_date。

2、計算全表掃描的代價

對於InnoDB儲存引擎來說，全表掃描的意思就是把聚簇索引中的記錄都依次和給定的搜尋條件做一下比較，把符合搜尋條件的記錄加入到結果集，所以需要將聚簇索引對應的頁面載入到記憶體中，然後再檢測記錄是否符合搜尋條件。由於查詢成本=I/O成本+CPU成本，所以計算全表掃描的代價需要兩個資訊：

1）聚簇索引佔用的頁面數

2）該表中的記錄數

MySQL為每個表維護了一系列的統計資訊，SHOW TABLE STATUS語句來檢視表的統計資訊。

SHOW TABLE STATUS LIKE 'titles';

Rows
表示表中的記錄條數。對於使用MyISAM儲存引擎的表來說，該值是準確的，對於使用InnoDB儲存引擎的表來說，該值是一個估計值。

Data_length
表示表佔用的儲存空間位元組數。使用MyISAM儲存引擎的表來說，該值就是資料檔案的大小，對於使用InnoDB儲存引擎的表來說，該值就相當於聚簇索引佔用的儲存空間大小，也就是說可以這樣計算該值的大小：

Data_length = 聚簇索引的頁面數量 x 每個頁面的大小

我們的titles使用預設16KB的頁面大小，而上邊查詢結果顯示Data_length的值是20512768，所以我們可以反向來推匯出聚簇索引的頁面數量：

聚簇索引的頁面數量 = Data_length ÷ 16 ÷ 1024 = 20512768 ÷ 16 ÷ 1024 = 1252

我們現在已經得到了聚簇索引佔用的頁面數量以及該表記錄數的估計值，所以就可以計算全表掃描成本了。但是MySQL在真實計算成本時會進行一些微調。

I/O成本：12521 = 1252。1252指的是聚簇索引佔用的頁面數，1.0指的是載入一個頁面的成本常數。

CPU成本：4420700.2=88414。442070指的是統計資料中表的記錄數，對於InnoDB儲存引擎來說是一個估計值，0.2指的是存取一條記錄所需的成本常數

總成本：1252+88414 = 89666。

綜上所述，對於titles的全表掃描所需的總成本就是89666。

在這裡插入圖片描述
我們前邊說過表中的記錄其實都儲存在聚簇索引對應B+樹的葉子節點中，所以只要我們通過根節點獲得了最左邊的葉子節點，就可以沿著葉子節點組成的雙向連結串列把所有記錄都檢視一遍。也就是說全表掃描這個過程其實有的B+樹內節點是不需要存取的，但是MySQL在計算全表掃描成本時直接使用聚簇索引佔用的頁面數作為計算I/O成本的依據，是不區分內節點和葉子節點的。

3、計算PRIMARY需要成本

計算PRIMARY需要多少成本的關鍵問題是：需要預估出根據對應的where條件在主鍵索引B+樹中存在多少條符合條件的記錄。

範圍區間數
當我們從索引中查詢記錄時，不管是=、in、>、<這些操作都需要從索引中確定一個範圍，不論這個範圍區間的索引到底佔用了多少頁面，查詢優化器粗暴的認為讀取索引的一個範圍區間的I/O成本和讀取一個頁面是相同的。
本例中使用PRIMARY的範圍區間只有一個：(10101, 20000)，所以相當於存取這個範圍區間的索引付出的I/O成本就是：1 x 1.0 = 1.0

預估範圍內的記錄數
優化器需要計算索引的某個範圍區間到底包含多少條記錄，對於本例來說就是要計算PRIMARY在(10101, 20000)這個範圍區間中包含多少條資料記錄，計算過程是這樣的：

步驟1：先根據emp_no > 10101這個條件存取一下PRIMARY對應的B+樹索引，找到滿足emp_no > 10101這個條件的第一條記錄，我們把這條記錄稱之為區間最左記錄。

步驟2：然後再根據emp_no < 20000這個條件繼續從PRIMARY對應的B+樹索引中找出第一條滿足這個條件的記錄，我們把這條記錄稱之為區間最右記錄。

步驟3：如果區間最左記錄和區間最右記錄相隔不太遠（只要相隔不大於10個頁面即可），那就可以精確統計出滿足emp_no > '10101' and emp_no < '20000'條件的記錄條數。否則只沿著區間最左記錄向右讀10個頁面，計算平均每個頁面中包含多少記錄，然後用這個平均值乘以區間最左記錄和區間最右記錄之間的頁面數量就可以了。那麼問題又來了，怎麼估計區間最左記錄和區間最右記錄之間有多少個頁面呢？計算它們父節點中對應的目錄項記錄之間隔著幾條記錄就可以了。

根據上面的步驟可以算出來PRIMARY索引的記錄條數，所以讀取記錄的CPU成本為：26808*0.2=5361.6，其中26808是預估的需要讀取的資料記錄條數，0.2是讀取一條記錄成本常數。

PRIMARY的總成本
確定存取的IO成本+過濾資料的CPU成本=1+5361.6=5362.6

在這裡插入圖片描述

4、計算idx_titles_to_date需要成本

在這裡插入圖片描述
因為通過二級索引查詢需要回表，所以在計算二級索引需要成本時還要加上回表的成本，而回表的成本就相當於下面這個SQL執行：

select * from employees.titles where 主鍵欄位 in （主鍵值1，主鍵值2，。。。，主鍵值3）;

所以idx_titles_to_date的成本 = 輔助索引的查詢成本 + 回表查詢的成本

5、比較各成本選出最優者

選擇成本最小的索引

四、基於索引統計資料的成本計算

有時候使用索引執行查詢時會有許多單點區間，比如使用IN語句就很容易產生非常多的單點區間，比如下邊這個查詢：

select * from employees.titles where to_date in ('a','b','c','d', ..., 'e');

很顯然，這個查詢可能使用到的索引就是idx_titles_to_date，由於這個索引並不是唯一二級索引，所以並不能確定一個單點區間對應的二級索引記錄的條數有多少，需要我們去計算。計算方式我們上邊已經介紹過了，就是先獲取索引對應的B+樹的區間最左記錄和區間最右記錄，然後再計算這兩條記錄之間有多少記錄（記錄條數少的時候可以做到精確計算，多的時候只能估算）。這種通過直接存取索引對應的B+樹來計算某個範圍區間對應的索引記錄條數的方式稱之為index pe。

如果只有幾個單點區間的話，使用index pe的方式去計算這些單點區間對應的記錄數也不是什麼問題，可是如果很多呢，比如有20000次，MySQL的查詢優化器為了計算這些單點區間對應的索引記錄條數，要進行20000次index pe操作，那麼這種情況下是很耗效能的，所以MySQL提供了一個系統變數eq_range_index_pe_limit，我們看一下這個系統變數的預設值：SHOW VARIABLES LIKE ‘%pe%’;為200。

也就是說如果我們的IN語句中的引數個數小於200個的話，將使用index pe的方式計算各個單點區間對應的記錄條數，如果大於或等於200個的話，可就不能使用index pe了，要使用所謂的索引統計資料來進行估算。像會為每個表維護一份統計資料一樣，MySQL也會為表中的每一個索引維護一份統計資料，檢視某個表中索引的統計資料可以使用SHOW INDEX FROM 表名的語法。

Cardinality屬性表示索引列中不重複值的個數。比如對於一個一萬行記錄的表來說，某個索引列的Cardinality屬性是10000，那意味著該列中沒有重複的值，如果Cardinality屬性是1的話，就意味著該列的值全部是重複的。不過需要注意的是，對於InnoDB儲存引擎來說，使用SHOW INDEX語句展示出來的某個索引列的Cardinality屬性是一個估計值，並不是精確的。可以根據這個屬性來估算IN語句中的引數所對應的記錄數：

1）使用SHOW TABLE STATUS展示出的Rows值，也就是一個表中有多少條記錄。

2）使用SHOW INDEX語句展示出的Cardinality屬性。

3）根據上面兩個值可以算出idx_key1索引對於的key1列平均單個值的重複次數：Rows/Cardinality

4）所以總共需要回表的記錄數就是：IN語句中的引數個數*Rows/Cardinality。

NULL值處理
上面知道在統計列不重複值的時候，會影響到查詢優化器。
對於NULL，有三種理解方式：

  • NULL值代表一個未確定的值，每一個NULL值都是獨一無二的，在統計列不重複值的時候應該都當作獨立的。

  • NULL值在業務上就是代表沒有，所有的NULL值代表的意義是一樣的，所以所有的NULL值都一樣，在統計列不重複值的時候應該只算一個。

  • NULL完全沒有意義，在統計列不重複值的時候應該忽略NULL。
    innodb提供了一個系統變數：

show global variables like '%innodb_stats_method%';

這個變數有三個值：

  • nulls_equal：認為所有NULL值都是相等的。這個值也是innodb_stats_method的預設值。如果某個索引列中NULL值特別多的話，這種統計方式會讓優化器認為某個列中平均一個值重複次數特別多，所以傾向於不使用索引進行存取。

  • nulls_unequal：認為所有NULL值都是不相等的。如果某個索引列中NULL值特別多的話，這種統計方式會讓優化器認為某個列中平均一個值重複次數特別少，所以傾向於使用索引進行存取。

  • nulls_ignored：直接把NULL值忽略掉。

最好不在索引列中存放NULL值才是正解

五、統計資料

InnoDB提供了兩種儲存統計資料的方式：

• 統計資料儲存在磁碟上。

• 統計資料儲存在記憶體中，當伺服器關閉時這些這些統計資料就都被清除掉了。

MySQL給我們提供了系統變數innodb_stats_persistent來控制到底採用哪種方式去儲存統計資料。在MySQL 5.6.6之前，innodb_stats_persistent的值預設是OFF，也就是說InnoDB的統計資料預設是儲存到記憶體的，之後的版本中innodb_stats_persistent的值預設是ON，也就是統計資料預設被儲存到磁碟中。

不過InnoDB預設是以表為單位來收集和儲存統計資料的，也就是說我們可以把某些表的統計資料（以及該表的索引統計資料）儲存在磁碟上，把另一些表的統計資料儲存在記憶體中。我們可以在建立和修改表的時候通過指定STATS_PERSISTENT屬性來指明該表的統計資料儲存方式。

  • 1、基於磁碟的永久性統計資料
    當我們選擇把某個表以及該表索引的統計資料存放到磁碟上時，實際上是把這些統計資料儲存到了兩個表裡：
    • innodb_table_stats儲存了關於表的統計資料，每一條記錄對應著一個表的統計資料
    • innodb_index_stats儲存了關於索引的統計資料，每一條記錄對應著一個索引的一個統計項的統計資料
  • 2、定期更新統計資料
    • 系統變數innodb_stats_auto_recalc決定著伺服器是否自動重新計算統計資料，它的預設值是ON，也就是該功能預設是開啟的。每個表都維護了一個變數，該變數記錄著對該表進行增刪改的記錄條數，如果發生變動的記錄數量超過了表大小的10%，並且自動重新計算統計資料的功能是開啟的，那麼伺服器會重新進行一次統計資料的計算，並且更新innodb_table_stats和innodb_index_stats表。不過自動重新計算統計資料的過程是非同步發生的，也就是即使表中變動的記錄數超過了10%，自動重新計算統計資料也不會立即發生，可能會延遲幾秒才會進行計算。
    •如果innodb_stats_auto_recalc系統變數的值為OFF的話，我們也可以手動呼叫ANALYZE TABLE語句來重新計算統計資料。ANALYZE TABLE single_table;
  • 3、控制執行計劃
    Index Hints
    •USE INDEX：限制索引的使用範圍，在資料表裡建立了很多索引，當MySQL對索引進行選擇時，這些索引都在考慮的範圍內。但有時我們希望MySQL只考慮幾個索引，而不是全部的索引，這就需要用到USE INDEX對查詢語句進行設定。
    •IGNORE INDEX ：限制不使用索引的範圍
    •FORCE INDEX：我們希望MySQL必須要使用某一個索引(由於 MySQL在查詢時只能使用一個索引，因此只能強迫MySQL使用一個索引)。這就需要使用FORCE INDEX來完成這個功能。
    基本語法格式：
SELECT * FROM table1 USE|IGNORE|FORCE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3

【相關推薦：】

以上就是深入解析MySQL中的查詢優化器（工作原理詳解）的詳細內容，更多請關注TW511.COM其它相關文章！