前言

線上，遇到一些sql性能問題，需要手術(shù)刀級別的調(diào)優(yōu)。optimizer_trace是一個極好的工具，已經(jīng)有很多資料介紹optimizer_trace怎么使用與閱讀。有必要再介紹一下我們平時不太能注意到，但是又對sql性能起著絕對作用的優(yōu)化器。

優(yōu)化器是啥？在sql整個生命周期里處于什么樣的位置，起到什么樣的作用，cmu15445 課程(https://15445.courses.cs.cmu.edu/fall2022/notes/14-optimization.pdf)中對此有一些直觀的描述。

以上圖片有6大模塊，每一個模塊都是一個單獨的領域。以優(yōu)化器為例，從1979年到現(xiàn)在，已經(jīng)發(fā)展出來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

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

要讓優(yōu)化器能夠得到足夠好的plan，有幾個必要條件：

1. 數(shù)據(jù)庫中的表設置了合適的數(shù)據(jù)類型。
2. 數(shù)據(jù)庫中設置了合適的索引。并且索引上有正確的統(tǒng)計信息。
3. 合理的數(shù)據(jù)分布。

查詢優(yōu)化器的作用：

當我們將查詢提交給MySQL執(zhí)行時，大多數(shù)的查詢都不像 select  *  from  single_table;那樣簡單，從單個表讀取所有數(shù)據(jù)就行了，不需要用到高級的檢索方式來返回數(shù)據(jù)。大多數(shù)查詢都比較復雜，有些更復雜并且完全按照編寫的方式執(zhí)行查詢絕不是獲得結(jié)果的最有效方式。我們可以有很多的可能性來優(yōu)化查詢：添加索引、聯(lián)接順序、用于執(zhí)行聯(lián)接的算法、各種聯(lián)接優(yōu)化以及更多。這就是優(yōu)化器發(fā)揮作用的地方。

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

這里有一個注意的點：優(yōu)化器所做的工作并不精確科學，因為數(shù)據(jù)及其分布的變化，優(yōu)化器所做的工作并不精確。轉(zhuǎn)換優(yōu)化器的選擇和計算的成本都是基于某種程度的估計。通常這些估計值足以得到一個好的查詢計劃，但偶爾你需要提供提示（hint）。如何配置優(yōu)化器是另外一個話題。

查詢改寫（Transformations）

優(yōu)化器有幾種更改查詢的改寫，在仍然返回相同結(jié)果的同時，讓查詢變?yōu)楦m合MySQL。

當然，優(yōu)化的前提是返回的結(jié)果符合期望，同時響應時間變短：減少了IO或者cpu時間。改寫的前提是原始查詢與重寫查詢邏輯一致，返回相同的查詢結(jié)果是至關重要的。為什么不同的寫法，可以返回相同的結(jié)果，又是一門學問：關系數(shù)據(jù)庫基于數(shù)學集理論的研究。

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

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'。優(yōu)化器使用這些知識來直接過濾country。由于code列是country表的主鍵，這意味著優(yōu)化器知道最多只有一行符合條件，并且優(yōu)化器可以將country表視為常數(shù)( constant)。實際上，查詢最終是使用country表中的列值作為選擇列表中的常量（constant)執(zhí)行掃描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';

從性能的角度來看，這是一個安全的轉(zhuǎn)變，且是優(yōu)化器可以自動實現(xiàn)的，并且對外提供了一個開關。

某些轉(zhuǎn)換會更加復雜，且并不總是提高性能。因此set optimizer_switch =on or off 是可選的，

optimizer_switch 的內(nèi)容 以及 何時怎么使用 optimizer hints 會在下一篇文章中討論。

有對查詢改寫怎么實現(xiàn)感興趣的朋友，可以在GreatSQL社區(qū)留言，為大家準備了大概9篇論文。

基于成本優(yōu)化（Cost-Based Optimization）

一旦優(yōu)化器決定要進行哪些轉(zhuǎn)換，就需要確定如何執(zhí)行重寫查詢。業(yè)內(nèi)目前有兩條路徑來解決，rule model 和 cost model。如果您已經(jīng)熟悉對optimizer_trace輸出的解讀，作為dba已經(jīng)對cost model 了解的足夠多了。

我再試著從優(yōu)化器的角度來解讀一下成本優(yōu)化。

單表查詢

無論查詢?nèi)绾?，計算成本的原則都是相同的，但是，查詢越復雜，成本估算就越復雜。

舉一個簡單的例子，一個查詢單個表的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'

優(yōu)化器可以選擇兩種方式來獲取匹配的行。一種方法是使用CountryCode上的索引查找索引中的匹配行，然后查找請求的行值。另一種方法是進行全表掃描并檢查每一行確定它是否符合where條件。

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

• 索引的選擇性：cost_單行直接獲取<cost_二級索引查詢逐漸后獲取<cost_全表掃描

索引必須顯著減少要檢查的行數(shù)。越多選擇指數(shù)，使用它相對便宜。（這里行數(shù)不太準確，應該是IO次數(shù)，以及IO的方式，順序IO 還是隨機IO)  《MySQL是怎樣運行的》有介紹一行數(shù)據(jù)是怎么讀取到的。

• 索引覆蓋度：如果索引包含所有列查詢需要，可以跳過對實際行的讀取。
• 讀取記錄的代價：取決于幾個因素，索引和行記錄是否都在innodb_buffer_pool中，如果不在，從磁盤讀取的代價和速度是多少。使用二級索引時，在切換讀取索引和讀取主鍵索引之間，將需要更多的隨機I/O，查找記錄需要耗費的索引尋找次數(shù)（一般索引高度來決定）變得非常重要。

MySQL8.0 的優(yōu)化器可以訊問InnoDB是否查詢所需的記錄可以在緩沖池中找到，或者是否

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

讀取記錄的所需cost是很復雜的問題，MySQL不知道硬件的性能，MySQL8.0 默認磁盤讀取的成本是4倍內(nèi)存讀取。

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)

表關聯(lián)順序（Table Join Order）

多表關聯(lián)時，outer and straight joins,join 順序是固定的。inner join時，優(yōu)化器會自由選擇join順序，為每一種組合計算代價。計算復雜度和表數(shù)量的關系：

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

MySQL支持連接多達61個表，在這種情況下可能有61!計算成本的組合。計算組合的成本過高且可能需要更長時間而不是執(zhí)行查詢本身。因此，優(yōu)化器默認情況下會刪除基于成本的部分評估查詢計劃，因此只有最有希望的計劃會被完全評估。

在給定的表之后，還可以通過參數(shù)optimizer_prune_level和optimizer_search_depth 配置搜索裁剪、搜索深度，來停止評估。比如10張表關聯(lián)，理論上需要評估10！=3628800次,默認最多62次。

最佳聯(lián)接順序 有兩個個因素影響，表自身的大小，經(jīng)過過濾器后每個表減少的行數(shù)。

默認過濾效果（Default Filtering Effects）

多表關聯(lián)時，知道每張表有多少行數(shù)據(jù)參與join，很有意義。

當使用索引時，當過濾器與其他表不相關時，優(yōu)化器可以非常準確地估計與索引匹配的行數(shù)。如果沒有索引，直方圖統(tǒng)計可用于獲得良好的濾波估計。當沒有過濾列的統(tǒng)計信息時，就會出現(xiàn)困難。在這種情況下，優(yōu)化器會后退基于內(nèi)置默認估計。

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

《Access Path Selection in a Relational Database Management System》(https://dl.acm.org/doi/pdf/10.1145/582095.582099)

需要中文版的朋友可以留言到GreatSQL社區(qū)。

System R針對join ordering問題，開創(chuàng)性的使用基于動態(tài)規(guī)劃的方法，結(jié)合Interesting Order形成等價類的方式，來對search space進行高效搜索。不僅如此，其對于selectivity的計算，cost的計算方式，影響非常深遠，相信早期的商業(yè)數(shù)據(jù)庫大多采用類似的代價估算方式(MySQL直至今日仍然如此）。

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

這個列表并不詳盡，但它應該能讓您很好地了解MySQL是如何實現(xiàn)過濾估計的。默認過濾效果顯然不是非常準確，特別是對于大表，因為數(shù)據(jù)不遵循這樣的嚴格規(guī)則。這就是為什么索引和直方圖對于獲得良好的查詢計劃非常重要。在確定查詢計劃的最后，會對單個部分和整個查詢進行成本估算。這些信息有助于了解優(yōu)化器到達查詢執(zhí)行計劃。

（這里也可以看出MySQL的優(yōu)化器的參考值相對Oracle是比較簡單的，導致的結(jié)果就是MySQL解析sql很快，快到幾乎不用緩存執(zhí)行計劃，Oracle為了解決生成計劃慢的問題， 引入了軟簡析，軟軟簡析，綁定執(zhí)行計劃等方案，當然MySQL的優(yōu)化器短板也很明顯，為DBA們制造了大量sql優(yōu)化的需求）

查詢成本（The Query Cost）

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

1、explain（explain 后面的sql，真的不會執(zhí)行 or 產(chǎn)生cost嗎？如果會，什么場景會觸發(fā)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的概念），執(zhí)行成本、返回行數(shù)、執(zhí)行時間、循環(huán)次數(shù)等，本質(zhì)上，EXPLAIN ANALYZE只適用于顯式查詢，因為它需要從頭到尾監(jiān)視查詢。另一方面，簡單的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客戶端都提供可視化的執(zhí)行計劃功能。

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；

可視化執(zhí)行計劃展示：

12種數(shù)據(jù)表訪問方式作色

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

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 經(jīng)驗值，可視化執(zhí)行計劃的顏色展示不是絕對的真理。

可以思考一下：索引look up 一定比全表掃描好嗎？索引只會帶來查詢上的正向優(yōu)化嗎？

5、終極武器 optimizer trace

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

1、sql_mode

2、optimizer switch

3、index statistics

4、mysql.engine_ cost and mysql.server_cost tables

done，待續(xù)