MySQL :: MySQL 8.4 參考手冊 :: 10.9.5 優化器成本模型

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / 優化器成本模型

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;