什麼是成本

MySQL中一條SQL語句的執行成本包含兩個部分：

• I/O成本：從磁碟中載入數據（頁）到記憶體的的過程中消耗的時間稱為I/O成本。

• CPU成本：讀取記錄以及檢測記錄是否滿足搜索條件、對結果集進行排序等操作，消耗的時間稱為CPU成本。

MySQL預設規定讀取一個頁面的I/O成本是1.0，讀取及檢測一條記錄的CPU成本是0.2。

單表查詢的成本

MySQL Server接收到客戶端發過來的SQL語句後，經歷過查找緩存（如有）和語法解析後，MySQL的優化器會找出所有可能用來執行這條語句的方案，並對比這些方案後選擇一條成本最低的方案，這個成本最低的方案就是執行計劃，在此期間會訪問少量的數據。然後才會跳用存儲引擎的結構真正地執行查詢。這個過程總結一下：

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

2. 計算全表掃描的代價。

3. 計算使用不同索引執行查詢的代價。

4. 對比各種執行方案的代價，找出成本最低的方案。

SELECT
  *
FROM
  single_table
WHERE
  key1 IN ('a', 'b', 'c')
  AND key2 > 10
  AND key2 < 1000
  AND key3 > key2
  AND key_part1 LIKE '%a%'
  AND common_field LIKE 'a%';

找出所有可能使用的索引possible keys

根據搜索條件，只要索引列和常數使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=、<>或者LIKE，就會形成掃描區間，這些搜索條件都可能使用到索引，一個查詢中可能使用到的索引統稱為possible keys。

• key1 IN ('a', 'b', 'c')使用了二級索引idx_key1。

• key2 > 10 AND key2 < 1000使用了唯一二級索引uk_key2。

• key3 > key2未與常數進行比較，無法形成合適的掃描區間。

• key_part1 LIKE '%a%'使用LIKE時無法匹配開頭，無法形成合適的掃描區間。

• common_field列未建立索引。

這條語句的possible keys為idx_key1、uk_key2。

計算全表掃描的代價

全表掃描的成本需要兩個參數：

• 聚簇索引占用的頁面數

• 該表中的記錄數

通過SHOW TABLE STATUS語句查看表的統計信息

SHOW TABLE STATUS LIKE 'single_table';

• Rows：對於InnoDB存儲引擎，這個值表示表中記錄條數的估值。

• Data_length：表示表占用的存儲空間位元組數，對於InnoDB來說，相當於聚簇索引占用的存儲空間大小。

表的預設大小為16KB，可以推導出聚簇索引的頁面數量：

I/O成本：頁面數量為97，沒載入一個頁的成本為1.0，總共97 ✖️ 1.0 + 1.1 = 98.1，其中1.1為內部微調值。

CPU成本：讀取一條記錄的成本為0.2，總共9895 ✖️ 0.2 + 1.0 = 1939.6，其中1.0為內部微調值。

聚簇索引包含記錄和目錄項，只有葉子節點才是記錄，但是計算成本時，直接將所有頁面都計算進入。

計算使用不同索引的執行查詢的代價

MySQL優化器優先分析使用唯一二級索引的成本，再分析使用普通二級索引的成本。

分析步驟：

1. 掃描區間數量

2. 需要回表的記錄數

無論掃描區間的二級索引占用了多少頁面，優化器都認為讀取索引的一個掃描區間的I/O成本與讀取一個頁面的I/O成本是相同的，也就是幾個掃描區間，就有幾個1.0。

<a name="index fanout"></a>

優化器需要計算二級索引的某個掃描區間包含多少條記錄，才能知道有多少條記錄需要執行回表。1️⃣根據其中一個訪問條件找到滿足條件的第一條記錄，也稱為區間最左記錄；2️⃣根據條件從索引中找到最後一條記錄，也稱為區間最右記錄；3️⃣如果去見最左記錄和區間最右記錄相隔不是很遠（不大於10個頁），就可以精確統計出滿足條件的二級索引記錄的條數。

如果這些頁面相隔不太遠，可以通過INDEX頁中Page Header部分的PAGE_N_RECS屬性知道該頁有多少記錄，遍歷這些頁，將PAGE_N_RECS相加即可。如果頁面很多，就從區間最左記錄向右讀10個頁面，計算每個頁平均記錄數量，然後乘以區間頁數得出估算數值。父節點中每一個目錄項記錄對應的是一頁，只需要找出多少個目錄項，就知道區間有多少的頁。此時可以知道區間有多少條記錄，讀取這些記錄所需CPU成本為記錄數✖️0.2。

根據這些記錄的主鍵到聚簇索引進行回表，每次回表操作相當於訪問一個頁，也就是說二級索引掃描區間中有多少條記錄，就需要進行多少次回表，也就是需要進行多少次頁面I/O。I/O成本為二級索引得到的記錄數✖️1.0。

回表後，再讀取並判斷其他條件，需要消耗CPU成本記錄數量✖️0.2。

註意，實際運行的時候，第一次計算成本時，沒有加上回表後聚簇索引記錄的CPU成本，如果這個方案的執行成本較低，會重新計算一遍這個方案的成本，並把回表後聚簇索引的CPU成本加上。

當多個索引列使用AND連接，如果為單點區間可以使用索引Intersection合併；當使用OR連接，單點區間可以使用Union合併，若不是，可以使用Sort-Unino合併。

根據統計數據計算成本

當查詢語句使用IN形成很多很多單點掃描區間

SELECT * FROM single_table WHERE key1 IN ('aaa', 'aab', 'aac', 'aad'...'zzz');

由於搜索條件涉及key1列，可以使用idx_key1索引，但是idx_key1不是唯一二級索引，沒法確定一個單點區域有多少條索引記錄。只能通過區間最左記錄和區間最右記錄之間的頁面估算記錄數量，這種通過直接訪問索引對應的B+樹來計算某個掃描區間內對應的索引記錄條數的方式稱為index dive。這也是為什麼說在查詢真正執行前，可能少量訪問B+樹中的數據。

但是如果IN語句中的參數非常多，這種index dive帶來的性能消耗非常大。這時候可以使用索引統計數據（idnex statistics）來進行估算。MySQL提供系統變數eq_range_index_dive_limit，如果IN語句中的參數數量小於eq_range_index_dive_limit值，則使用index dive，若不小於eq_range_index_dive_limit，則使用索引統計數據。

SHOW VARIABLES LIKE 'eq_range_index_dive_limit'

MySQL除了為每個表維護一份統計數據，還會為 表中的每一個索引維護一份統計數據。

SHOW INDEX FROM single_table;

在InnoDB存儲引擎中，索引統計數據中的Cardinality是一個估值，並不精確。表統計數據的Rows也是。

根據索引統計數據中的Cardinality和表統計數據的Rows，可以計算出列中一個值平均重覆多少次：

就可以估算出所有的單點掃描區間有多少記錄了：

這種方法相對來說比index dive簡單多了，但是不精確，適用於單點區間非常多的情況。

連接查詢的成本

什麼是條件過濾

查詢驅動表後得到的記錄條數稱為驅動表的扇出，連接查詢的成本分為兩個部分：

• 單詞查詢驅動表的成本

• 多次查詢被驅動表的成本（次數與驅動表的扇出決定）

顯然， 驅動表的扇出越少，連查查詢的成本越低。驅動表的扇出越多，成本成倍增加。

• 當驅動表使用全表查詢時，扇出值就是驅動表中的記錄數量，查詢優化器根據表統計數據得到表的記錄數量，並作為扇出值。

• 當驅動表使用二級索引查詢的計算方法，可以得出掃描區間內的記錄數量，這個就是扇出值。

• 當驅動表無法使用索引時，只能姑且認為扇出值是全表記錄數量。

• 當驅動表可以使用二級索引生成合適的掃描區間，但是搜索條件中包含不可以使用索引的列，那就需要在索引列形成的掃描區間的記錄數量中，猜測有多少記錄符合條件。

這個猜測過程稱為條件過濾（Condition Filtering）。猜測過程可能會使用到索引，也可能會使用到統計數據。

兩表連接成本分析

外連接的驅動表是固定的，只需要分別為驅動表和被驅動表選擇成本最低的訪問方法，就可以得到最好的方案。

內連接驅動表和被驅動可以互換，優化器需要分別考慮兩種連接順序的成本，然後選取成本更低的連接順序，以及各個表最優訪問方法作為最終的執行計劃。

在連接查詢中，最影響成本的是驅動表扇出值✖️單詞訪問被驅動表的成本，如果這兩個值較高，成本則是成倍增長。優化重點就是：

• 儘量減少驅動表的扇出；

• 訪問被驅動表的成本儘量低，儘量在被驅動表的連接列上建立索引，這樣可以使用ref訪問方法。如果是主鍵或唯一二級索引那就最好了。

多表連接成本分析

分析多表連接的成本，首先要考慮多表不同的連接順序。MySQL在計算各種連接順序的成本之前，會維護一個全局變數，就是當前最小的連接查詢成本。在分析不同連接順序的成本時，將保存當前已經分析連接順序的成本最小值，如果在分析某個連接順序的成本時，該成本已經超過當前最小的連接查詢成本，那就停止分析該連接順序了，如果分析得出該連接順序的成本小於當前最小的連接查詢成本，則將覆蓋新的值。

MySQL還提供optimizer_search_depth系統變數，表示依次分析不同連接順序成本的最大表的數量。如果連接表的個數小於該值，就會分析每一種連接順序的成本；若大於，也只分析該值數量的表。該值越大，成本分析越精確，越容易生成好的執行計劃，但是消耗的時間越長；否則得到的就不是很好的執行計劃，但是節省了分析時間。

SHOW VARIABLES LIKE 'optimizer_search_depth';

啟髮式規則簡介

根據以往經驗指定的一些規則，凡事不滿足這些規則的連接順序壓根就不分析。好處是減少分析不同連接順序的數量，從而減少時間；不好是不能得到最好的執行計劃。系統變數optimizer_prune_level控制是否使用啟髮式規則。

SHOW VARIABLES LIKE 'optimizer_prune_level';

成本常數

MySQL中一條語句在server層進行操作的成本對應的成本常數存儲在mysql.server_cost表中，存儲引起的操作對應的成本常數存儲在mysql.engine_cost表中。

SELECT * FROM mysql.server_cost;

SELECT * FROM mysql.engine_cost;

閱讀自《MySQL是怎樣運行的》小孩子4919.