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
  • 前言 插件化的需求主要源於對軟體架構靈活性的追求,特別是在開發大型、複雜或需要不斷更新的軟體系統時,插件化可以提高軟體系統的可擴展性、可定製性、隔離性、安全性、可維護性、模塊化、易於升級和更新以及支持第三方開發等方面的能力,從而滿足不斷變化的業務需求和技術挑戰。 一、插件化探索 在WPF中我們想要開 ...
  • 歡迎ReaLTaiizor是一個用戶友好的、以設計為中心的.NET WinForms項目控制項庫,包含廣泛的組件。您可以使用不同的主題選項對項目進行個性化設置,並自定義用戶控制項,以使您的應用程式更加專業。 項目地址:https://github.com/Taiizor/ReaLTaiizor 步驟1: ...
  • EDP是一套集組織架構,許可權框架【功能許可權,操作許可權,數據訪問許可權,WebApi許可權】,自動化日誌,動態Interface,WebApi管理等基礎功能於一體的,基於.net的企業應用開發框架。通過友好的編碼方式實現數據行、列許可權的管控。 ...
  • Channel 是乾什麼的 The System.Threading.Channels namespace provides a set of synchronization data structures for passing data between producers and consume ...
  • efcore如何優雅的實現按年分庫按月分表 介紹 本文ShardinfCore版本 本期主角: ShardingCore 一款ef-core下高性能、輕量級針對分表分庫讀寫分離的解決方案,具有零依賴、零學習成本、零業務代碼入侵適配 距離上次發文.net相關的已經有很久了,期間一直在從事java相關的 ...
  • 前言 Spacesniffer 是一個免費的文件掃描工具,通過使用樹狀圖可視化佈局,可以立即瞭解大文件夾的位置,幫助用戶處理找到這些文件夾 當前系統C盤空間 清理後系統C盤空間 下載 Spacesniffer 下載地址:https://spacesniffer.en.softonic.com/dow ...
  • EDP是一套集組織架構,許可權框架【功能許可權,操作許可權,數據訪問許可權,WebApi許可權】,自動化日誌,動態Interface,WebApi管理等基礎功能於一體的,基於.net的企業應用開發框架。通過友好的編碼方式實現數據行、列許可權的管控。 ...
  • 一、ReZero簡介 ReZero是一款.NET中間件 : 全網唯一開源界面操作就能生成API , 可以集成到任何.NET6+ API項目,無破壞性,也可讓非.NET用戶使用exe文件 免費開源:MIT最寬鬆協議 , 一直從事開源事業十年,一直堅持開源 1.1 純ReZero開發 適合.Net Co ...
  • 一:背景 1. 講故事 停了一個月沒有更新文章了,主要是忙於寫 C#內功修煉系列的PPT,現在基本上接近尾聲,可以回頭繼續更新這段時間分析dump的一些事故報告,有朋友微信上找到我,說他們的系統出現了大量的http超時,程式不響應處理了,讓我幫忙看下怎麼回事,dump也抓到了。 二:WinDbg分析 ...
  • 開始做項目管理了(本人3年java,來到這邊之後真沒想到...),天天開會溝通整理需求,他們講話的時候忙裡偷閑整理一下常用的方法,其實語言還是有共通性的,基本上看到方法名就大概能猜出來用法。出去打水的時候看到外面太陽好好,真想在外面坐著曬太陽,回來的時候好兄弟三年前送給我的鍵盤D鍵不靈了,在打"等待 ...