共計 6414 個字符，預計需要花費 17 分鐘才能閱讀完成。
本篇內容主要講解“MySQL 中的查詢優化器怎么用”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“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-101、根據搜索條件，找出所有可能使用的索引
? 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 titlesRows
表示表中的記錄條數。對于使用 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 中的查詢優化器怎么用”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
