前言

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

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

以上圖片有6大模塊，每一個(gè)模塊都是一個(gè)單獨(dú)的領(lǐng)域。以優(yōu)化器為例，從1979年到現(xiàn)在，已經(jīng)發(fā)展出來9個(gè)細(xì)分的研究領(lǐng)域：

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

接下來會(huì)選幾個(gè)領(lǐng)域做一些更底層的介紹，基于篇幅的限制，某些知識(shí)點(diǎn)，點(diǎn)到為止，可以作為以后工作再深入的一個(gè)入口。

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

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

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

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

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

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

查詢改寫（Transformations）

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

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

舉個(gè)查詢改寫簡(jiǎn)單的例子：

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

這個(gè)查詢有兩個(gè)條件：city.CountryCode = 'AUS'，city.CountryCode=country.Code。從這兩個(gè)條件可以得出country.Code='AUS'。優(yōu)化器使用這些知識(shí)來直接過濾country。由于code列是country表的主鍵，這意味著優(yōu)化器知道最多只有一行符合條件，并且優(yōu)化器可以將country表視為常數(shù)( constant)。實(shí)際上，查詢最終是使用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';

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

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

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

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

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

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

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

單表查詢

無論查詢?nèi)绾?，?jì)算成本的原則都是相同的，但是，查詢?cè)綇?fù)雜，成本估算就越復(fù)雜。

舉一個(gè)簡(jiǎn)單的例子，一個(gè)查詢單個(gè)表的sql，where條件使用二級(jí)索引列。

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上的索引查找索引中的匹配行，然后查找請(qǐng)求的行值。另一種方法是進(jìn)行全表掃描并檢查每一行確定它是否符合where條件。

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

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

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

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

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

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

讀取記錄的所需cost是很復(fù)雜的問題，MySQL不知道硬件的性能，MySQL8.0 默認(rèn)磁盤讀取的成本是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)

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

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

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

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

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

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

默認(rèn)過濾效果（Default Filtering Effects）

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

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

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

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

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

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

論文太深?yuàn)W了 ，來點(diǎn)大家看得懂的

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

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

查詢成本（The Query Cost）

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

1、explain（explain 后面的sql，真的不會(huì)執(zhí)行 or 產(chǎn)生cost嗎？如果會(huì)，什么場(chǎng)景會(huì)觸發(fā)cost）

2、explain format= tree (8.0.16)  or  explain format= json

3、explain analyze(8.0.18) 在format= tree的基礎(chǔ)上，增加了多種信息（ actual cost  怎么定義 的？actual cost又是一個(gè)量化分析的話題，它是一個(gè)絕對(duì)的概念還是一個(gè)相對(duì) explain的概念），執(zhí)行成本、返回行數(shù)、執(zhí)行時(shí)間、循環(huán)次數(shù)等，本質(zhì)上，EXPLAIN ANALYZE只適用于顯式查詢，因?yàn)樗枰獜念^到尾監(jiān)視查詢。另一方面，簡(jiǎn)單的EXPLAIN語(yǔ)句也可以用于正在進(jìn)行的查詢。詳見語(yǔ)法：（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í)行計(jì)劃功能。

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í)行計(jì)劃展示：

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

作色與表訪問方式成本大小的關(guān)系。

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.

以上都只是一個(gè)平均值or 經(jīng)驗(yàn)值，可視化執(zhí)行計(jì)劃的顏色展示不是絕對(duì)的真理。

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

5、終極武器 optimizer trace

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

1、sql_mode

2、optimizer switch

3、index statistics

4、mysql.engine_ cost and mysql.server_cost tables

done，待續(xù)