[翻譯]——How the MySQL Optimizer Calculates the Cost of a Query (Doc ID 1327497.1)

来源:https://www.cnblogs.com/kerrycode/archive/2023/11/06/17813862.html
-Advertisement-
Play Games

本文是對這篇文章How the MySQL Optimizer Calculates the Cost of a Query (Doc ID 1327497.1)[1]的翻譯,翻譯如有不當的地方,敬請諒解,請尊重原創和翻譯勞動成果,轉載的時候請註明出處。謝謝! 適用於: MySQL 4.0 及後續更 ...


本文是對這篇文章How the MySQL Optimizer Calculates the Cost of a Query (Doc ID 1327497.1)[1]的翻譯,翻譯如有不當的地方,敬請諒解,請尊重原創和翻譯勞動成果,轉載的時候請註明出處。謝謝!

適用於:

MySQL 4.0 及後續更高的版本

本文檔中的內容適用於任何平臺。

目標

瞭解 MySQL 優化器如何計算SQL語句的查詢成本/代價以及如何分析EXPLAIN 語句的輸出。

解決方案

MySQL優化器使用成本模型(cost model),其中查詢計劃的總體成本由各種操作(operation)的成本總體決定。確定成本的主要方法是使用存儲提供的統計數據並使用所謂的成本常量(另請參閱下麵的“對資料庫成本模型進行更改”部分)。例如,這些統計數據是索引基數值(即索引中值的唯一性的度量)和每個表中的總行數。由於統計數據的不精確性,因為統計數據可能已經過時,或者它是使用近似方法來計算獲取的統計數據(對於InnoDB資料庫尤其如此,見下文),並且值的分佈未知,那麼優化器只能執行查詢時提供查詢結果集的行數的估計值。該預估值在某些情況下非常準確,但在某些情況下則不太準確。 實際的實現方式比上面的描述更複雜,確切的細節也可能取決於查詢的類型。另請參閱參考手冊中的優化器成本模型,瞭解 MySQL 5.7 及更高版本中優化器成本模型的討論。 在 MySQL 5.6 及後續更高版本中,當我們在評估優化器如何執行查詢語句時,優化器跟蹤功能(optimizer trace feature)可用於深入瞭解優化器的決策過程。更多詳細信息,請參閱註釋 2241524.1[2]

更改資料庫的成本模型

在 MySQL 5.7 以及後續版本中,優化器模型中使用的成本常量(cost constants)由資料庫用來進行成本估算。如果需要的話,可以更改此資料庫的配置。

警告:更改資料庫的成本模型被認為是高級資料庫管理。如果您進行更改以驗證其行為是否符合預期時,請務必小心,併在部署到生產環境之前進行完全徹底充分的測試。

從 MySQL 5.7.17 開始,資料庫成本模型中有兩個可以調整的變數。數據存儲在mysql.engine_cost表中,預設值為:

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment |
+-------------+-------------+------------------------+------------+---------------------+---------+
default     |           0 | io_block_read_cost     |       NULL | 2017-01-13 15:21:46 | NULL    |
default     |           0 | memory_block_read_cost |       NULL | 2017-01-13 15:21:46 | NULL    |
+-------------+-------------+------------------------+------------+---------------------+---------+
2 rows in set (0.00 sec)

io_block_read_cost和memory_block_read_cost參數分別指定從磁碟和記憶體讀取數據的相對成本。將 io_block_read_cost 參數的cost_value值設置為高於 memory_block_read_cost 的值,這會使優化器生成的查詢計劃更喜歡讀取記憶體中數據,而不是從磁碟讀取數據。 還可以通過將engine_name設置為要生效的存儲引擎的名稱來指定每個存儲引擎的成本值。 當所有變更生效後,使用 FLUSH OPTIMIZER_COSTS 命令觸發優化器重新讀取成本模型數據。 每個會話的優化器成本都會被緩存。只有在 FLUSH OPTIMIZER_COSTS 語句執行之後啟動的會話才會受到更改的影響。   例如,通過使用以下步驟將io_block_read_cost預設值設置為 2.0,將 InnoDB 存儲引擎的預設值設置為 3.0:

1.將io_block_read_cost的預設值更新為 2.0:

mysql> UPDATE mysql.engine_cost
          SET cost_value = 2.0
        WHERE cost_name = 'io_block_read_cost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

2.為InnoDB存儲引擎添加新的成本規則,將io_block_read_cost設置為3.0:

mysql> INSERT INTO mysql.engine_cost (engine_name, device_type, cost_name, cost_value, comment)
       VALUES ('InnoDB'0'io_block_read_cost'3.0'Using a slower disk for InnoDB');
Query OK, 1 row affected (0.01 sec)

3.刷新新的成本值:

mysql> FLUSH OPTIMIZER_COSTS;
Query OK, 0 rows affected (0.00 sec)

4.驗證新的成本值是否在查詢計劃中給出了預期結果。

5.根據需要部署到生產。

InnoDB

如果您的表使用InnoDB 存儲引擎,您還應該註意索引統計信息是基於有限數量記錄的隨機樣本/採樣的估計。如果您運行ANALYZE TABLE tablename(將tablename替換為實際的表名),然後運行SHOW INDEXES FROM tablename,那麼您將看到基數將在後續一系列操作之間波動。表中的總行數也是如此。如果多次執行SHOW TABLE STATUS LIKE 'tablename',即使表上沒有發生任何更新,那麼您將看到每次執行時行總數的估計值也會有所不同(另一方面,對於MyISAM存儲引擎表,表中的行數是精確的,因為其缺乏多版本控制和使用表鎖,使得維護精確的統計數據變得更容易)。然而,您也可能不走運,最終對要檢查的行數的估計相對較差。這也意味著,如果您運行ANALYZE TABLE tablename,那麼行列中的值可能會發生變化,在某些情況下甚至查詢計劃本身也會發生變化。另請參閱MySQL 參考手冊中的InnoDB 表限制。

註意:如果innodb_stats_on_metadata設置為ON(MySQL 5.5 及更早版本中的預設值),InnoDB會在元數據語句(例如SHOW TABLE STATUS或SHOW INDEX )執行期間訪問INFORMATION_SCHEMA表TABLES或STATISTICS時更新統計信息。

為了更好地估計 InnoDB 表的索引基數,可以將參數innodb_stats_sample_pages設置為更大的值。但需要註意,較大的值將導致索引更新時間更長,並且每次打開表時都會重新計算統計信息,因此可能會對性能產生影響。另請參閱MySQL 優化器團隊的Oystein Grovlen 的博客[3],文中討論了更改innodb_stats_sample_pages的值如何影響索引基數計算。 innodb_stats_sample_pages選項是隨 MySQL 5.1.38 中的 InnoDB 插件一起引入的。在MySQL 5.1和MySQL 5.0及更早版本的內置InnoDB中,該值無法更改,並且預設值為8。在 MySQL 5.6.3 中,innodb_stats_sample_pages已替換為innodb_stats_transient_sample_pages選項。對於 MySQL 5.6.2 及更高版本,另請參閱innodb_stats_persistent_sample_pages。   實際檢查行數示例 要更好地估計實際檢查的行數,請在查詢之前和查詢完成之後執行SHOW SESSION STATUS LIKE 'handler%'命令。另一種可能性是確保查詢被慢查詢日誌記錄,慢查詢日誌還提供檢查的總行數。

使用會話狀態變數查找檢查的行數的示例是:

mysql> SHOW SESSION STATUS LIKE 'handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
16 rows in set (0.00 sec)

mysql> SELECT * FROM (SELECT id FROM t1 WHERE id < 100) t1 INNER JOIN t2 USING (id);
+----+-----+
| id | val |
+----+-----+
|  1 | a   |
|  2 | b   |
|  3 | c   |
...
| 98 | c   |
| 99 | a   |
+----+-----+
76 rows in set (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 100   |
| Handler_read_last          | 0     |
| Handler_read_next          | 99    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 100   |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 99    |
+----------------------------+-------+
16 rows in set (0.00 sec)

這表明SQL查詢總共讀取了 300 行,分佈如下:

  • 1 行讀取索引中的第一個條目 (Handler_read_first)
  • 99 行按鍵順序讀取下一行(Handler_read_next)
  • 100 行從索引查找行(Handler_read_key)
  • 100 行進行表掃描 (Handler_read_rnd_next) 此外,由於派生表 (Handler_write),有 99 行被寫入內部臨時表。

參考資料

[1]

原文: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1327497.1

[2]

1: https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1327497.1&id=2241524.1

[3]

Oystein Grovlen: http://oysteing.blogspot.com/2011/04/more-stable-query-execution-time-by.html

掃描上面二維碼關註我 如果你真心覺得文章寫得不錯,而且對你有所幫助,那就不妨幫忙“推薦"一下,您的“推薦”和”打賞“將是我最大的寫作動力! 本文版權歸作者所有,歡迎轉載,但未經作者同意必須保留此段聲明,且在文章頁面明顯位置給出原文連接.
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 線程池(重點) 線程池:三大方法、七大參數、四種拒絕策略 池化技術 程式的運行,本質:占用系統的資源!優化資源的使用!-> 池化技術(線程池、連接池、對象池......);創建和銷毀十分消耗資源 池化技術:事先準備好一些資源,有人要用就拿,拿完用完還給我。 線程池的好處: 1、降低資源消耗 2、提高 ...
  • 我們在類中通常會有一個屬性為 IsDel來表示軟刪除或也稱邏輯刪除,這個屬性會導致我們在進行查詢操作時,每一次都要 .where(s=>s.IsDel==false) 非常的麻煩。在使用efCore時可以通過配置查詢篩選器來很好的解決這個問題。 public class SysUser { publ ...
  • 搜索查找指令 find 指令 find指令將從指定目錄向下遞歸的遍歷其各個子目錄,將滿足條件的文件或者目錄顯示在終端。 基本語法 find [搜索範圍(指定目錄)] [選項] 選項說明 選項 功能 -name<查詢方式> 按照指定的文件名查找模式查找文件 -user<用戶名> 查找屬於指定用戶名所有 ...
  • rsyslog 介紹 日誌(Log)是記錄和存儲電腦、軟體、應用或其他系統的操作和事件的文件或數據流。它們可以為系統管理員、開發人員和最終用戶提供詳細的背景信息,以幫助他們瞭解和診斷系統的行為。 rsyslog 是一個開源的日誌處理工具,一般用在類Unix系統上,是syslogd 的擴展。它提供了 ...
  • 包括線程概念簡介;線程創建函數pthread_create以及退出、回收等;線程同步互斥鎖pthread_mutex_t,讀寫鎖pthread_rwlock_t,條件變數pthread_cond_t以及信號量semaphore ...
  • 本文分享自華為雲社區《GaussDB資料庫SQL系列-數據去重》,作者: Gauss松鼠會小助手2 。 一、前言 數據去重在資料庫中是比較常見的操作。複雜的業務場景、多業務線的數據來源等等,都會帶來重覆數據的存儲。本文以GaussDB資料庫為實驗平臺,將為大家詳細講解如何去重。 二、數據去重應用場景 ...
  • 本篇文章記錄最近ES做節點替換,從shard遷移過程中被鎖定導致無法分配,主shard正常,希望可以幫助其它人 failed to create shard,failed to obtain in-memory shard lock,ShardLockObtainFailedException 一、 ...
  • 設置進程記憶體(Process Memory) Apache Flink通過嚴格控制其各種組件的記憶體使用,在JVM之上提供高效的工作負載。 配置總記憶體(Total Memory) Flink JVM進程的總進程記憶體(total process memory)由Flink應用程式消耗的記憶體(總Flink ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...