MySQL :: MySQL 9.0 參考手冊 :: 10.9.5 最佳化工具成本模型

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 最佳化工具成本模型

10.9.5 最佳化工具成本模型

為了產生執行計畫，最佳化工具會使用成本模型，該模型基於查詢執行期間發生的各種操作成本的估計值。最佳化工具有一組編譯到內部的預設「成本常數」，可用於做出有關執行計畫的決策。

最佳化工具還有一個成本估計資料庫，可在執行計畫建構期間使用。這些估計值儲存在 mysql 系統資料庫的 server_cost 和 engine_cost 資料表中，並且可隨時設定。這些資料表的目的是讓您可以輕鬆調整最佳化工具在嘗試得出查詢執行計畫時使用的成本估計值。

成本模型一般操作

可設定的最佳化工具成本模型運作方式如下

伺服器會在啟動時將成本模型資料表讀入記憶體，並在執行階段使用記憶體中的值。資料表中指定的任何非 NULL 成本估計值都優先於對應的編譯到內部的預設成本常數。任何 NULL 估計值都會指示最佳化工具使用編譯到內部的預設值。
在執行階段，伺服器可能會重新讀取成本資料表。當動態載入儲存引擎或執行 FLUSH OPTIMIZER_COSTS 陳述式時，就會發生這種情況。
成本資料表可讓伺服器管理員透過變更資料表中的條目來輕鬆調整成本估計值。也可以透過將條目的成本設定為 NULL 來輕鬆還原為預設值。最佳化工具會使用記憶體中的成本值，因此變更資料表後，應接著執行 FLUSH OPTIMIZER_COSTS 才能生效。
用戶端工作階段開始時的目前記憶體成本估計值，會套用到整個工作階段直到結束。特別是，如果伺服器重新讀取成本資料表，任何變更的估計值只會套用到後續啟動的工作階段。現有的工作階段不會受到影響。
成本資料表特定於給定的伺服器執行個體。伺服器不會將成本資料表變更複製到複本。

成本模型資料庫

最佳化工具成本模型資料庫由 mysql 系統資料庫中的兩個資料表組成，其中包含查詢執行期間發生的操作的成本估計資訊

server_cost：一般伺服器操作的優化器成本估算
engine_cost：特定儲存引擎操作的優化器成本估算

server_cost 表格包含以下欄位

cost_name
成本模型中使用的成本估算名稱。名稱不區分大小寫。如果伺服器讀取此表格時無法識別成本名稱，則會將警告寫入錯誤日誌。
cost_value
成本估算值。如果該值不為 NULL，則伺服器會將其用作成本。否則，它會使用預設估算值（編譯時的值）。DBA 可以透過更新此欄位來變更成本估算。如果伺服器發現讀取此表格時，成本值無效（非正數），則會將警告寫入錯誤日誌。
若要覆寫預設成本估算值（針對指定 NULL 的條目），請將成本設定為非 NULL 值。若要還原為預設值，請將該值設定為 NULL。然後執行 FLUSH OPTIMIZER_COSTS，通知伺服器重新讀取成本表格。
last_update
上次資料列更新的時間。
comment
與成本估算相關的描述性註解。DBA 可以使用此欄位來提供有關成本估算資料列為何儲存特定值的相關資訊。
default_value
成本估算的預設（編譯時）值。此欄位是唯讀產生欄位，即使相關的成本估算已變更，也會保留其值。對於在執行階段新增至表格的資料列，此欄位的值為 NULL。

server_cost 表格的主索引鍵是 cost_name 欄位，因此無法為任何成本估算建立多個條目。

伺服器會針對 server_cost 表格識別這些 cost_name 值

disk_temptable_create_cost、disk_temptable_row_cost
儲存在磁碟儲存引擎（InnoDB 或 MyISAM）中的內部建立臨時表格的成本估算。增加這些值會增加使用內部臨時表格的成本估算，並使優化器偏好較少使用它們的查詢計畫。有關此類表格的資訊，請參閱第 10.4.4 節，「MySQL 中內部臨時表格的使用」。
與對應的記憶體參數（memory_temptable_create_cost、memory_temptable_row_cost）的預設值相比，這些磁碟參數的預設值較大，反映了處理磁碟表格的更高成本。
key_compare_cost
比較記錄鍵的成本。增加此值會導致比較許多索引鍵的查詢計畫變得更加昂貴。例如，執行 filesort 的查詢計畫相對於透過使用索引來避免排序的查詢計畫，會變得相對更加昂貴。
memory_temptable_create_cost、memory_temptable_row_cost
儲存在 MEMORY 儲存引擎中的內部建立臨時表格的成本估算。增加這些值會增加使用內部臨時表格的成本估算，並使優化器偏好較少使用它們的查詢計畫。有關此類表格的資訊，請參閱第 10.4.4 節，「MySQL 中內部臨時表格的使用」。
與對應的磁碟參數（disk_temptable_create_cost、disk_temptable_row_cost）的預設值相比，這些記憶體參數的預設值較小，反映了處理記憶體表格的較低成本。
row_evaluate_cost
評估記錄條件的成本。增加此值會導致檢查許多資料列的查詢計畫相對於檢查較少資料列的查詢計畫，變得更加昂貴。例如，資料表掃描相對於讀取較少資料列的範圍掃描，會變得相對更加昂貴。

engine_cost 表格包含以下欄位

engine_name
此成本估算套用的儲存引擎名稱。名稱不區分大小寫。如果值為 default，則會套用至所有沒有自己的命名條目的儲存引擎。如果伺服器讀取此表格時無法識別引擎名稱，則會將警告寫入錯誤日誌。
device_type
此成本估算套用的裝置類型。此欄位旨在為不同的儲存裝置類型指定不同的成本估算，例如硬碟機與固態硬碟。目前，此資訊未使用，且唯一允許的值為 0。
cost_name
與 server_cost 表格中的相同。
cost_value
與 server_cost 表格中的相同。
last_update
與 server_cost 表格中的相同。
comment
與 server_cost 表格中的相同。
default_value
成本估算的預設（編譯時）值。此欄位是唯讀產生欄位，即使相關的成本估算已變更，也會保留其值。對於在執行階段新增至表格的資料列，此欄位的值為 NULL，但如果資料列的 cost_name 值與其中一個原始資料列相同，則 default_value 欄位的值與該資料列相同。

engine_cost 表格的主索引鍵是由 (cost_name、engine_name、device_type) 欄位組成的元組，因此無法為這些欄位中值的任何組合建立多個條目。

伺服器會針對 engine_cost 表格識別這些 cost_name 值

io_block_read_cost
從磁碟讀取索引或資料區塊的成本。增加此值會導致讀取許多磁碟區塊的查詢計畫相對於讀取較少磁碟區塊的查詢計畫，變得更加昂貴。例如，資料表掃描相對於讀取較少區塊的範圍掃描，會變得相對更加昂貴。
memory_block_read_cost
與 io_block_read_cost 類似，但表示從記憶體資料庫緩衝區讀取索引或資料區塊的成本。

如果 io_block_read_cost 和 memory_block_read_cost 值不同，則相同查詢的兩次執行之間，執行計畫可能會變更。假設記憶體存取的成本低於磁碟存取的成本。在這種情況下，在資料讀入緩衝集區之前的伺服器啟動時，您可能會獲得與查詢執行後不同的計畫，因為那時資料已在記憶體中。

變更成本模型資料庫

對於想要將成本模型參數從預設值變更的 DBA，請嘗試將值加倍或減半並測量其影響。

變更 io_block_read_cost 和 memory_block_read_cost 參數最有可能產生有價值的結果。這些參數值使資料存取方法的成本模型能夠考慮從不同來源讀取資訊的成本；也就是說，從磁碟讀取資訊與讀取已在記憶體緩衝區中的資訊的成本。例如，在所有其他條件相同的情況下，將 io_block_read_cost 設定為大於 memory_block_read_cost 的值，會使優化器偏好讀取已在記憶體中保存的資訊的查詢計畫，而不是必須從磁碟讀取的計畫。

以下範例示範如何變更 io_block_read_cost 的預設值

UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

以下範例示範如何僅針對 InnoDB 儲存引擎變更 io_block_read_cost 的值

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;