MySQL8.0 最佳化器介紹（一）

前言

線上，遇到一些sql效能問題，需要手術刀級別的調優。optimizer_trace是一個極好的工具，已經有很多資料介紹optimizer_trace怎麼使用與閱讀。有必要再介紹一下我們平時不太能注意到，但是又對sql效能起著絕對作用的最佳化器。

最佳化器是啥？在sql整個生命週期裡處於什麼樣的位置，起到什麼樣的作用，cmu15445 課程()中對此有一些直觀的描述。

以上圖片有6大模組，每一個模組都是一個單獨的領域。以最佳化器為例，從1979年到現在，已經發展出來9個細分的研究領域：

1. Planner framework
2. Transformation
3. Join Order Optimization
4. Functional Dependency and Physical Properties
5. Cost Model
6. Statistics
7. Query feedback loop
8. MPP optimization
9. BENCHMARK

接下來會選幾個領域做一些更底層的介紹，基於篇幅的限制，某些知識點，點到為止，可以作為以後工作再深入的一個入口。

要讓最佳化器能夠得到足夠好的plan，有幾個必要條件：

1. 資料庫中的表設定了合適的資料型別。
2. 資料庫中設定了合適的索引。並且索引上有正確的統計資訊。
3. 合理的資料分佈。

查詢最佳化器的作用：

當我們將查詢提交給MySQL執行時，大多數的查詢都不像 select  *  from  single_table;那樣簡單，從單個表讀取所有資料就行了，不需要用到高階的檢索方式來返回資料。大多數查詢都比較複雜，有些更復雜並且完全按照編寫的方式執行查詢絕不是獲得結果的最有效方式。我們可以有很多的可能性來最佳化查詢：新增索引、聯接順序、用於執行聯接的演算法、各種聯接最佳化以及更多。這就是最佳化器發揮作用的地方。

最佳化器的主要工作是準備查詢以執行和確定最佳查詢計劃。第一階段涉及對查詢進行轉換，目的是重寫的查詢可以以比原始查詢更低的成本執行查詢。第二階段包括計算查詢可以執行的各種方式的成本，確定並執行成本最低的計劃。

這裡有一個注意的點：最佳化器所做的工作並不精確科學，因為資料及其分佈的變化，最佳化器所做的工作並不精確。轉換最佳化器的選擇和計算的成本都是基於某種程度的估計。通常這些估計值足以得到一個好的查詢計劃，但偶爾你需要提供提示（hint）。如何配置最佳化器是另外一個話題。

查詢改寫（Transformations）

最佳化器有幾種更改查詢的改寫，在仍然返回相同結果的同時，讓查詢變為更適合MySQL。

當然，最佳化的前提是返回的結果符合期望，同時響應時間變短：減少了IO或者cpu時間。改寫的前提是原始查詢與重寫查詢邏輯一致，返回相同的查詢結果是至關重要的。為什麼不同的寫法，可以返回相同的結果，又是一門學問：關聯式資料庫基於數學集理論的研究。

舉個查詢改寫簡單的例子：

SELECT *
 FROM world.country
 INNER JOIN world.city
 ON city.CountryCode = country.Code
 WHERE city.CountryCode = 'AUS'

這個查詢有兩個條件：city.CountryCode = 'AUS'，city.CountryCode=country.Code。從這兩個條件可以得出country.Code='AUS'。最佳化器使用這些知識來直接過濾country。由於code列是country表的主鍵，這意味著最佳化器知道最多隻有一行符合條件，並且最佳化器可以將country表視為常數( constant)。實際上，查詢最終是使用country表中的列值作為選擇列表中的常量（constant)執行掃描CountryCode='AUS'的city表中的行。

改寫如下：

SELECT 'AUS' AS `Code`,
 'Australia' AS `Name`,
 'Oceania' AS `Continent`,
 'Australia and New Zealand' AS `Region`,
 7741220.00 AS `SurfaceArea`,
 1901 AS `IndepYear`,
 18886000 AS `Population`,
 79.8 AS `LifeExpectancy`,
 351182.00 AS `GNP`,
 392911.00 AS `GNPOld`,
 'Australia' AS `LocalName`,
 'Constitutional Monarchy, Federation' AS `GovernmentForm`,
 'Elisabeth II' AS `HeadOfState`,
 135 AS `Capital`,
 'AU' AS `Code2`,
 city.*
 FROM world.city
 WHERE CountryCode = 'AUS';

從效能的角度來看，這是一個安全的轉變，且是最佳化器可以自動實現的，並且對外提供了一個開關。

某些轉換會更加複雜，且並不總是提高效能。因此set optimizer_switch =on or off 是可選的，

optimizer_switch 的內容 以及 何時怎麼使用 optimizer hints 會在下一篇文章中討論。

有對查詢改寫怎麼實現感興趣的朋友，可以在GreatSQL社群留言，為大家準備了大概9篇論文。

基於成本最佳化（Cost-Based Optimization）

一旦最佳化器決定要進行哪些轉換，就需要確定如何執行重寫查詢。業內目前有兩條路徑來解決，rule model 和 cost model。如果您已經熟悉對optimizer_trace輸出的解讀，作為dba已經對cost model 瞭解的足夠多了。

我再試著從最佳化器的角度來解讀一下成本最佳化。

單表查詢

無論查詢如何，計算成本的原則都是相同的，但是，查詢越複雜，成本估算就越複雜。

舉一個簡單的例子，一個查詢單個表的sql，where條件使用二級索引列。

mysql> SHOW CREATE TABLE world.city\G
**************************** 1. row ****************************
 Table: city
Create Table: CREATE TABLE `city` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Name` char(35) NOT NULL DEFAULT ",
 `CountryCode` char(3) NOT NULL DEFAULT ",
 `District` char(20) NOT NULL DEFAULT ",
 `Population` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`),
 KEY `CountryCode` (`CountryCode`),
 CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` 
(`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0008 sec)

SELECT *  FROM world.city  WHERE CountryCode = 'IND'

最佳化器可以選擇兩種方式來獲取匹配的行。一種方法是使用CountryCode上的索引查詢索引中的匹配行，然後查詢請求的行值。另一種方法是進行全表掃描並檢查每一行確定它是否符合where條件。

這些訪問方法中哪一種成本最低（最快）不是可以直接確定。這取決於幾個因素：

• 索引的選擇性：cost_單行直接獲取<cost_二級索引查詢逐漸後獲取<cost_全表掃描
• 索引必須顯著減少要檢查的行數。越多選擇指數，使用它相對便宜。（這裡行數不太準確，應該是IO次數，以及IO的方式，順序IO 還是隨機IO)  《MySQL是怎樣執行的》有介紹一行資料是怎麼讀取到的。
• 索引覆蓋度：如果索引包含所有列查詢需要，可以跳過對實際行的讀取。
• 讀取記錄的代價：取決於幾個因素，索引和行記錄是否都在innodb_buffer_pool中，如果不在，從磁碟讀取的代價和速度是多少。使用二級索引時，在切換讀取索引和讀取主鍵索引之間，將需要更多的隨機I/O，查詢記錄需要耗費的索引尋找次數（一般索引高度來決定）變得非常重要。

MySQL8.0 的最佳化器可以訊問InnoDB是否查詢所需的記錄可以在緩衝池中找到，或者是否

必須從從磁碟上讀取記錄。這對執行計劃的改進，有巨大的幫助。

讀取記錄的所需cost是很複雜的問題，MySQL不知道硬體的效能，MySQL8.0 預設磁碟讀取的成本是4倍記憶體讀取。

mysql>   select  cost_name, default_value from  mysql.server_cost;
+------------------------------+---------------+
| cost_name                    | default_value |
+------------------------------+---------------+
| disk_temptable_create_cost   |            20 |
| disk_temptable_row_cost      |           0.5 |
| key_compare_cost             |          0.05 |
| memory_temptable_create_cost |             1 |
| memory_temptable_row_cost    |           0.1 |
| row_evaluate_cost            |           0.1 |
+------------------------------+---------------+
6 rows in set (0.00 sec)

mysql>  select engine_name,cost_name,default_value  from  mysql.engine_cost;
+-------------+------------------------+---------------+
| engine_name | cost_name              | default_value |
+-------------+------------------------+---------------+
| default     | io_block_read_cost     |             1 |
| default     | memory_block_read_cost |          0.25 |
+-------------+------------------------+---------------+
2 rows in set (0.00 sec)

表關聯順序（Table Join Order）

多表關聯時，outer and straight joins,join 順序是固定的。inner join時，最佳化器會自由選擇join順序，為每一種組合計算代價。計算複雜度和表數量的關係：

N張表，需要做N! 的計算。5張表，組合度為5!=5*4*3*2*1=120

MySQL支援連線多達61個表，在這種情況下可能有61!計算成本的組合。計算組合的成本過高且可能需要更長時間而不是執行查詢本身。因此，最佳化器預設情況下會刪除基於成本的部分評估查詢計劃，因此只有最有希望的計劃會被完全評估。

在給定的表之後，還可以透過引數optimizer_prune_level和optimizer_search_depth 配置搜尋裁剪、搜尋深度，來停止評估。比如10張表關聯，理論上需要評估10！=3628800次,預設最多62次。

最佳聯接順序 有兩個個因素影響，表自身的大小，經過過濾器後每個表減少的行數。

預設過濾效果（Default Filtering Effects）

多表關聯時，知道每張表有多少行資料參與join，很有意義。

當使用索引時，當過濾器與其他表不相關時，最佳化器可以非常準確地估計與索引匹配的行數。如果沒有索引，直方圖統計可用於獲得良好的濾波估計。當沒有過濾列的統計資訊時，就會出現困難。在這種情況下，最佳化器會後退基於內建預設估計。

那到底是怎麼估算的呢？詳見以下這篇大名鼎鼎的論文：

《Access Path Selection in a Relational Database Management System》()

需要中文版的朋友可以留言到GreatSQL社群。

System R針對join ordering問題，開創性的使用基於動態規劃的方法，結合Interesting Order形成等價類的方式，來對search space進行高效搜尋。不僅如此，其對於selectivity的計算，cost的計算方式，影響非常深遠，相信早期的商業資料庫大多采用類似的代價估算方式(MySQL直至今日仍然如此）。

論文太深奧了 ，來點大家看得懂的

這個列表並不詳盡，但它應該能讓您很好地瞭解MySQL是如何實現過濾估計的。預設過濾效果顯然不是非常準確，特別是對於大表，因為資料不遵循這樣的嚴格規則。這就是為什麼索引和直方圖對於獲得良好的查詢計劃非常重要。在確定查詢計劃的最後，會對單個部分和整個查詢進行成本估算。這些資訊有助於瞭解最佳化器到達查詢執行計劃。

（這裡也可以看出MySQL的最佳化器的參考值相對Oracle是比較簡單的，導致的結果就是MySQL解析sql很快，快到幾乎不用快取執行計劃，Oracle為了解決生成計劃慢的問題， 引入了軟簡析，軟軟簡析，繫結執行計劃等方案，當然MySQL的最佳化器短板也很明顯，為DBA們製造了大量sql最佳化的需求）

查詢成本（The Query Cost）

有5種方式檢視optimizer 估算出來的成本。每一種都值得獨立開篇來討論，每一種都有它使用的場景,（生產上做操作有絕對的安全保障嗎？）。

• 1、explain（explain 後面的sql，真的不會執行 or 產生cost嗎？如果會，什麼場景會觸發cost）
• 2、explain format= tree (8.0.16)  or  explain format= json
• 3、explain analyze(8.0.18) 在format= tree的基礎上，增加了多種資訊（ actual cost  怎麼定義 的？actual cost又是一個量化分析的話題，它是一個絕對的概念還是一個相對 explain的概念），執行成本、返回行數、執行時間、迴圈次數等，本質上，EXPLAIN ANALYZE只適用於顯式查詢，因為它需要從頭到尾監視查詢。另一方面，簡單的EXPLAIN語句也可以用於正在進行的查詢。詳見語法：（https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze）

mysql> explain format=tree SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
*************************** 1. row ***************************
  -> Nested loop inner join  (cost=4.95 rows=9)
    -> Filter: (`<subquery2>`.b is not null)  (cost=2.83..1.80 rows=9)
        -> Table scan on <subquery2>  (cost=0.29..2.61 rows=9)
            -> Materialize with deduplication  (cost=3.25..5.58 rows=9)
                -> Filter: (t2.b is not null)  (cost=2.06 rows=9)
                    -> Filter: (t2.id < 10)  (cost=2.06 rows=9)
                        -> Index range scan on t2 using PRIMARY  (cost=2.06 rows=9)
    -> Index lookup on t1 using a (a=`<subquery2>`.b)  (cost=2.35 rows=1)
1 row in set (0.01 sec)

mysql> explain analyze SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10)\G
*************************** 1. row ***************************
  -> Nested loop inner join  (cost=4.95 rows=9) (actual time=0.153..0.200 rows=9 loops=1)
    -> Filter: (`<subquery2>`.b is not null)  (cost=2.83..1.80 rows=9) (actual time=0.097..0.100 rows=9 loops=1)
        -> Table scan on <subquery2>  (cost=0.29..2.61 rows=9) (actual time=0.001..0.002 rows=9 loops=1)
            -> Materialize with deduplication  (cost=3.25..5.58 rows=9) (actual time=0.090..0.092 rows=9 loops=1)
                -> Filter: (t2.b is not null)  (cost=2.06 rows=9) (actual time=0.037..0.042 rows=9 loops=1)
                    -> Filter: (t2.id < 10)  (cost=2.06 rows=9) (actual time=0.036..0.040 rows=9 loops=1)
                        -> Index range scan on t2 using PRIMARY  (cost=2.06 rows=9) (actual time=0.035..0.038 rows=9 loops=1)
    -> Index lookup on t1 using a (a=`<subquery2>`.b)  (cost=2.35 rows=1) (actual time=0.010..0.010 rows=1 loops=9)
1 row in set (0.01 sec)

explain format= json  怎麼算 參考 format= json 怎麼算

explain analyze 怎麼讀？參考 

(https://www.mmzsblog.cn/articles/2022/05/07/1651914715938.html)

4、MySQL Workbench  Visual Explain diagram  大部分的mysql客戶端都提供視覺化的執行計劃功能。

SELECT ci.ID, 
       ci.Name, 
       ci.District,  
       co.Name AS Country, 
       ci.Population  
  FROM world.city ci  
  INNER JOIN  (SELECT Code, 
                      Name  
                 FROM world.country  
                WHERE Continent = 'Europe'  
             ORDER BY SurfaceArea  LIMIT 10  ) co 
          ON co.Code = ci.CountryCode  
  ORDER BY ci.Population DESC  
  LIMIT 5；

視覺化執行計劃展示：

12種資料表訪問方式作色

作色與表訪問方式成本大小的關係

Blue (1) is the cheapest; green (2), yellow (3), and orange (4) represent low to  medium costs; and the most expensive access types and operations are red symbolizing  a high (5) to very high (6) cost.

以上都只是一個平均值or 經驗值，視覺化執行計劃的顏色展示不是絕對的真理。

可以思考一下：索引look up 一定比全表掃描好嗎？索引只會帶來查詢上的正向最佳化嗎？

• 5、終極武器 optimizer trace

影響以上輸出的因素有：（不好意思，以下每種，又是一個開篇話題 ：） 我真是太討厭了。。。）

1、sql_mode

2、optimizer switch

3、index statistics

4、mysql.engine_ cost and mysql.server_cost tables

done，待續