最佳化器成本記錄表|全方位認識 mysql 系統庫

上一期《統計資訊記錄表|全方位認識 mysql 系統庫》中，我們詳細介紹了mysql系統庫中的統計資訊記錄表，本期我們將為大家帶來系列第五篇《最佳化器成本記錄表|全方位認識 mysql 系統庫》，下面請跟隨我們一起開始 mysql 系統庫的系統學習之旅吧！

01 最佳化器成本模型概述

為了生成執行計劃，最佳化器使用了基於成本的模型來對各種操作成本進行估算。 最佳化器具有一組可編輯的預設“成本常量”（這些值儲存在mysql系統資料庫下的server_cost和engine_cost表中），可用於調節執行計劃的決策。

* server_cost： server常規操作需要使用到的最佳化器成本估算常量值。

* engine_cost： 針對特定儲存引擎的操作需要使用到的的最佳化器成本估算常量值。

當伺服器啟動時會將成本模型表讀入記憶體中，在生成執行計劃時使用記憶體中的值。 表中指定的任何非NULL成本估算常量值優先使用。剩餘其他任何NULL常量值在使用時會轉換為內建的預設常量值。

成本常量值在伺服器執行過程中允許動態修改(透過修改server_cost和engine_cost表實現，修改完成後需要執行FLUSH OPTIMIZER_COSTS語句重新載入)，如果發現修改不對或者需要重置，可以直接把響應的成本常量值設定為NULL即可。

對成本常量值的修改的影響類似於全域性變數的修改，只對修改之後新的連線生效，對修改之前已經建立的連線不生效（無論是否執行過FLUSH OPTIMIZER_COSTS語句）。

server_cost和engine_cost表中的成本常量資料僅適用於當前例項，對其修改不會進行復制同步。

下面分別對這兩張表進行詳細說明。

02  最佳化器成本記錄表詳解

      2.1. engine_cost

該表提供查詢針對特定儲存引擎的操作需要使用到的的最佳化器成本估算常量值。

下面是該表中儲存的資訊內容。

root@localhost : mysql 01:01:47> select * from engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment |
+-------------+-------------+------------------------+------------+---------------------+---------+
| default     |           0 | io_block_read_cost     |       NULL | 2017-07-01 14:31:32 | NULL    |
| default     |           0 | memory_block_read_cost |       NULL | 2017-07-01 14:31:32 | NULL    |
+-------------+-------------+------------------------+------------+---------------------+---------+
2 rows in set (0.00 sec)

表欄位含義。

• ENGINE_NAME： 此成本估算常量適用的儲存引擎的名稱。名稱不區分大小寫。如果該值是預設值，則表示適用於所有儲存引擎。如果Server在讀取此表時未識別引擎名稱，則會向錯誤日誌寫入警告(預設值default除外，這裡指的是非法值)。

• device_type： 此成本估算常量適用的裝置型別。該列旨在為不同的儲存裝置型別指定不同的成本估算常量，例如：為機械硬碟與固態硬碟指定不同的估算常量值。目前該欄位未使用，目前的唯一有效值為0。

• cost_name： 與server_cost表中的相同欄位含義相同。

• cost_value： 與server_cost表中的相同欄位含義相同。

• last_update： 與server_cost表中的相同欄位含義相同。

• comment： 與server_cost表中的相同欄位含義相同。

engine_cost表的主鍵包含三列（cost_name，engine_name，device_type），所以這三列組合值必須唯一，不可建多個條目。

該表中記錄的有效成本常量值如下：

• io_block_read_cost（預設1.0）： 從磁碟讀取索引或資料塊的成本。與增加此值時的查詢計劃相比，讀取更多磁碟塊的查詢計劃與讀取更少磁碟塊的查詢計劃相比會被查詢計劃認為更加昂貴。例如：與讀取較少塊的範圍掃描相比，表掃描被認為是昂貴的。

• memory_block_read_cost（預設1.0）： 與io_block_read_cost類似，表示從記憶體緩衝區中讀取索引或資料塊的估算常量。

如果io_block_read_cost和memory_block_read_cost值不同，則執行計劃可能會在相同查詢的兩次執行時發現執行發生了變化（例如： 執行計劃不同或者執行時間不同）。例如：假設記憶體訪問的成本低於磁碟訪問的成本。在這種情況下，在伺服器啟動時還未完成將資料讀入緩衝池之前與之後，兩次執行相同的查詢您可能會得到不同的計劃。

對io_block_read_cost和memory_block_read_cost引數的更改可能會為查詢計劃帶來收益，例如： 在所有其他條件都相同的情況下，將io_block_read_cost值設定為大於memory_block_read_cost的值會使最佳化程式更喜歡走透過在記憶體中查詢資料的查詢計劃。

修改io_block_read_cost的示例資訊如下：

# update已有的常量值
UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;
# 為innodb引擎單獨插入一行常量值
INSERT INTO mysql.engine_cost
  VALUES ('InnoDB', 0, 'io_block_read_cost', 3.0,
  CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');
FLUSH OPTIMIZER_COSTS;

      2.2 . serve r_cost

該表提供查詢server常規操作需要使用到的最佳化器成本估算常量值。

下 面是該表中儲存的資訊內容。

root@localhost : mysql 01:07:25> select * from server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2017-07-01 14:31:32 | NULL    |
| disk_temptable_row_cost      |       NULL | 2017-07-01 14:31:32 | NULL    |
| key_compare_cost             |       NULL | 2017-07-01 14:31:32 | NULL    |
| memory_temptable_create_cost |       NULL | 2017-07-01 14:31:32 | NULL    |
| memory_temptable_row_cost    |       NULL | 2017-07-01 14:31:32 | NULL    |
| row_evaluate_cost            |       NULL | 2017-07-01 14:31:32 | NULL    |
+------------------------------+------------+---------------------+---------+
6 rows in set (0.00 sec)

表欄位含義。

• cost_name： 成本模型中使用的成本估算變數名稱。名稱不區分大小寫。如果Server在讀取此表時未識別成本名稱，則會向錯誤日誌寫入警告。

• cost_value： 成本估算變數值。如果該值不為NULL，則Server將直接使用其用作成本計算。否則，它使用預設估計值（程式碼內的編譯預設值）。DBA可以透過更新此列值以影響成本估算。但需要確保指定的是有效值（留意表結構中的欄位資料型別），如果Server在讀取此表時發現成本值無效（不正確），則會向錯誤日誌寫入警告。如果需要恢復預設值，只需要將此欄位設定為NULL值即可，然後執行FLUSH OPTIMIZER_COSTS語句來通知Server重新讀取表中的資料。

• last_update： 最後一次更新該行記錄的時間。

• comment： 與成本估算變數相關的描述性資訊。

• PS： server_cost表擁有主鍵列cost_name，因此不可能出現為某個成本估算變數設定多個值的情況。

表中記錄的內容即為Server識別的成本估算常量，如下：

• disk_temptable_create_cost（預設為40.0），disk_temptable_row_cost（預設為1.0）： 基於磁碟的內部臨時表（InnoDB或MyISAM）的成本估算常量。增加這些值會增加使用基於磁碟的內部臨時表的成本估計值，查詢最佳化器在進行成本估算時會偏向於更少使用它，與相應的基於記憶體的內部臨時表的引數（memory_temptable_create_cost，memory_temptable_row_cost）的預設值相比，預設值較大。

• key_compare_cost（預設0.1）： 比較索引鍵值記錄的成本常量。增加此值會讓查詢最佳化器認為查詢較多索引鍵值是昂貴的。因為，查詢計劃會盡量避免檔案排序(基於索引的排序)。

• memory_temptable_create_cost（預設2.0），memory_temptable_row_cost（預設0.2）： 基於MEMORY儲存引擎的內部臨時表的成本估算常量。增加這些值會增加使用內部記憶體臨時表的成本估計值，即會使得最佳化器偏向於更少使用它。

• row_evaluate_cost（預設值為0.2）： 評估記錄行的成本常量。與讀取較少行的範圍掃描相比，表掃描變得相對昂貴，查詢計劃會偏向於更少使用表掃描

本期內容就介紹到這裡，本期內容參考連結如下：

https://dev.mysql.com/doc/refman/5.7/en/cost-model.html

| 作者簡介

羅小波·沃趣科技高階資料庫技術專家