MySQL8.0 優化器介紹(一)

来源:https://www.cnblogs.com/greatsql/archive/2023/03/29/17267981.html
-Advertisement-
Play Games

GreatSQL社區原創內容未經授權不得隨意使用,轉載請聯繫小編並註明來源。 GreatSQL是MySQL的國產分支版本,使用上與MySQL一致。 作者: 奧特曼愛小怪獸 文章來源:GreatSQL社區原創 前言 線上,遇到一些sql性能問題,需要手術刀級別的調優。optimizer_trace是一 ...


  • GreatSQL社區原創內容未經授權不得隨意使用,轉載請聯繫小編並註明來源。
  • GreatSQL是MySQL的國產分支版本,使用上與MySQL一致。
  • 作者: 奧特曼愛小怪獸
  • 文章來源:GreatSQL社區原創

前言

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

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

圖片

以上圖片有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*43*21=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》(https://dl.acm.org/doi/pdf/10.1145/582095.582099)

需要中文版的朋友可以留言到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,待續


Enjoy GreatSQL

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 1. 三值邏輯 1.1. 真 1.1.1. true 1.2. 假 1.2.1. false 1.3. 不確定 1.3.1. unknown 2. 兩種NULL 2.1. 未知”(unknown) 2.1.1. 不知道戴墨鏡的人眼睛是什麼顏色 2.1.2. 雖然現在不知道,但加上某些條件後就可以知道 ...
  • 通過Hbase與Cassandra對比,層次展開瞭解Apache Cassandra特性和使用場景,通過部署但實力和多實例集群進一步理解其運作,最後通過CQL及其客戶端命令工具理解其數據模型和數據類型,通過對鍵空間、表、索引、數據操作熟悉常見CQL語法和使用。 ...
  • 所需準備 1.安裝資料庫實例,如SQLEXPRADV_x64_CHS 2.安裝資料庫管理軟體,如SQL Server Management Studio 操作步驟 1.打開SQL Server Management Studio,使用Windows身份驗證登入。 2.點擊伺服器實例,右鍵屬性->安全 ...
  • Redis資料庫 Redis(Remote Dictionary Server)是一個使用 C 語言編寫的,高性能非關係型的鍵值對資料庫。與傳統資料庫不同的是,Redis 的數據是存在記憶體中的,所以讀寫速度非常快,被廣泛應用於緩存方向。Redis可以將數據寫入磁碟中,保證了數據的安全不丟失,而且Re ...
  • RMAN備份時會記錄每一次備份的狀態信息,例如COMPLETED,FAILED等,但是使用下麵腳本查詢資料庫時,偶爾你會看到有些備份的狀態為COMPLETED WITH WARNINGS SET LINESIZE 1080;COL STATUS FORMAT A9;COL START_TIME FO ...
  • 摘要:智能把控大數據量查詢,防患系統奔潰於未然。 本文分享自華為雲社區《拒絕“爆雷”!GaussDB(for MySQL)新上線了這個功能》,作者:GaussDB 資料庫。 什麼是最大讀取行 一直以來,大數據量查詢是資料庫DBA們調優的重點,DBA們通常十八般武藝輪番上陣以期提升大數據查詢的性能:例 ...
  • 從MySQL 5.5版本開始預設 使用InnoDB作為引擎,它擅長處理事務,具有自動崩潰恢復的特性,在日常開發中使用非常廣泛 下麵是官方的InnoDB引擎架構圖,主要分為記憶體結構和磁碟結構兩大部分。 InnoDB 記憶體結構 1. Buffer Pool Buffer Pool:緩衝池,簡稱BP。其作 ...
  • 日常生產中 HDFS 上小文件產生是一個很正常的事情,同時小文件也是 Hadoop 集群運維中的常見挑戰,尤其對於大規模運行的集群來說可謂至關重要。 數據地圖是離線開發產品的基本使用單位,包含全部表和項目的相關信息,可以對錶做相關的許可權管理和脫敏管理操作,以及可以展示對應項目占用情況和其表的占用情況 ...
一周排行
    -Advertisement-
    Play Games
  • ## 引言 最近發現自己喜歡用的 Todo 軟體總是差點意思,畢竟每個人的習慣和工作流不太一樣,我就想著自己寫一個小的[Todo 項目]( https://github.com/circler3/TodoTrack ),核心的功能是自動記錄 Todo 執行過程中消耗的時間(尤其面向程式員),按照自己 ...
  • ### 前言 當我們編寫 C# 代碼時,經常需要處理大量的數據集合。在傳統的方式中,我們往往需要先將整個數據集合載入到記憶體中,然後再進行操作。但是如果數據集合非常大,這種方式就會導致記憶體占用過高,甚至可能導致程式崩潰。 C# 中的`yield return`機制可以幫助我們解決這個問題。通過使用`y ...
  • 1. ADO.NET的前世今生 ADO.NET的名稱起源於ADO(ActiveX Data Objects),是一個COM組件庫,用於在以往的Microsoft技術中訪問數據。之所以使用ADO.NET名稱,是因為Microsoft希望表明,這是在NET編程環境中優先使用的數據訪問介面。 ADO.NE ...
  • 1. 為什麼需要單元測試 在我們之前,測試某些功能是否能夠正常運行時,我們都將代碼寫到Main方法中,當我們測試第二個功能時,我們只能選擇將之前的代碼清掉,重新編寫。此時,如果你還想重新測試你之前的功能時,這時你就顯得有些難為情了,因為代碼都被你清掉了。當然你完全可以把代碼寫到一個記事本中進行記錄, ...
  • 1. 透過現象看本質 反射被譽為是 c#中的黑科技 ,在很多領域中都有反射的身影,例如,我們經常使用的ORM框架,ABP框架 等。 反射指程式可以訪問、檢測和修改它本身狀態或行為的一種能力。. 程式集包含模塊,而模塊包含類型,類型又包含成員。. 反射則提供了封裝程式集、模塊和類型的對象。. 您可以使 ...
  • # Rust Web 全棧開發之 Web Service 中的錯誤處理 ## Web Service 中的統一錯誤處理 ### Actix Web Service 自定義錯誤類型 -> 自定義錯誤轉為 HTTP Response - 資料庫 - 資料庫錯誤 - 串列化 - serde 錯誤 - I/ ...
  • 在前面的幾篇文章中,詳細地給大家介紹了Java里的集合。但在介紹集合時,我們涉及到了泛型的概念卻並沒有詳細學習,所以今天我們要花點時間給大家專門講解什麼是泛型、泛型的作用、用法、特點等內容 ...
  • ###BIO:同步阻塞 主線程發起io請求後,需要等待當前io操作完成,才能繼續執行。 ###NIO:同步非阻塞 引入selector、channel、等概念,當主線程發起io請求後,輪詢的查看系統是否準備好執行io操作,沒有準備好則主線程不會阻塞會繼續執行,準備好主線程會阻塞等待io操作完成。 # ...
  • 摘要:在讀多寫少的環境中,有沒有一種比ReadWriteLock更快的鎖呢?有,那就是JDK1.8中新增的StampedLock! 本文分享自華為雲社區《【高併發】高併發場景下一種比讀寫鎖更快的鎖》,作者: 冰 河。 什麼是StampedLock? ReadWriteLock鎖允許多個線程同時讀取共 ...
  • ## 併發與並行😣 ### 併發與並行的概念和區別 並行:同一個時間段內多個任務同時在不同的CPU核心上執行。強調同一時刻多個任務之間的”**同時執行**“。 併發:同一個時間段內多個任務都在進展。強調多個任務間的”**交替執行**“。 ![](https://img2023.cnblogs.co ...