上一期《統計信息記錄表|全方位認識 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

| 作者簡介

羅小波·沃趣科技高級數據庫技術專家