MySQL核心月報 2014.10-MySQL· 5.7重構·Optimizer Cost Model

為了生成執行計劃，優化器會使用一個基于估算查詢執行過程中各種操作開銷的成本模型。mysql優化器有一組在編譯時預設指定的“成本常量”（例如cpu算幾個因子、io算幾個因子），用于決策執行計劃的生成。

從5.7.5開始，優化器在執行計劃的生成過程中有了額外的成本估算項可用。這些估算項存在在mysql系統庫的server_cost和engine_cost表中，并且任何時候都可以通過修改表中的值來配置這些估算項。這些表存在的目的是，可以通過簡單的調整這些表中的成本估算項來影響執行計劃的生成，來達到調整執行計劃的目的。

兩張表的結構和内容如下：

<b>成本模型的工作方式</b>

可配置的優化器成本模型按如下方式工作：

mysql server在啟動時讀取成本模型表，并且在運作時使用記憶體中存貯的值。表中任何非null的成本估算項的值都會覆寫在代碼中寫死的預設成本常數，優先參與優化器成本計算。任何null值的成本估算項優化器都會認為使用者沒有指定特定的值，而使用代碼中預設的成本常數。

在mysql運作時，server可能會重新讀取成本表，可以通過動态載入存儲引擎或者執行flush optimizer_costs語句來觸發。

成本估算表可以讓管理者通過簡單的方式去調整成本估算項，也可以通過把估算項設定為null來恢複原來的内置預設值。優化器使用的是記憶體中緩存的開銷值，是以修改了表中的值後記得用flush optimizer_costs指令讓修改生效。

記憶體中緩存的成本項對目前正在執行的session是不起效果的，一個session内執行的query其成本項的值是不會變動的。即使server觸發了重新讀取成本表，任何估算項的變更也隻影響後來連結上來的session。

成本開銷表是不參與複制的，隻影響修改的本地執行個體，不會通過複制把開銷表的變更複制到備庫。

<b>成本模型資料庫</b>

優化器成本模型庫由mysql系統庫下的兩張表組成，包含了query執行過程中一些操作項的成本估算值：

server_cost: server層一些操作的成本估算項的值

engine_cost: 特定引擎的一些操作的成本估算項的值

server_cost表包含這些字段:

cost_name

成本模型中的成本估算項的名稱（不區分大小寫）。如果server無法識别名稱，在讀取的時候會打一個報錯在error log中。

cost_value

成本估算項的值。如果值是非null的，那麼server就使用這個值作為成本，否則就用編譯時内置的值，dba可以通過update這個列來修改響應的成本項。如果server讀到無效的值（例如負數），會在errorlog中打一條warning。

要覆寫内置的預設值就需要設定一個非null值，如果要恢複預設值，就把值重新改為null，然後執行flush optimizer_costs 告訴server重新讀取成本表。

last_update

這一行的最後修改時間。

comment

成本項的描述注釋。dba可以利用這個這個列來記錄為什麼修改了這個成本項的值，用于備查。

server_cost表的主鍵是server_cost，是以不能建立名稱相同的成本項。

server可以識别server_cost表中如下的cost_name:

disk_temptable_create_cost, disk_temptable_row_cost

内部建立磁盤臨時表的成本開銷。增加這些成本項的值可以讓優化器更偏向于生成不使用磁盤臨時表的執行計劃。

key_compare_cost

比較記錄鍵值的成本開銷。增加這個值可以讓執行計劃中比較鍵值的操作成本變的更加昂貴。例如，一個執行計劃執行了filesort，那麼它的代價會比利用索引避免排序的代價要大得多。

memory_temptable_create_cost, memory_temptable_row_cost

内部建立記憶體臨時表的成本開銷。增加這些值可以使得建立内部臨時表成本增加，因而優化器會偏向于不使用臨時表。

row_evaluate_cost

掃描記錄行的成本開銷。增加這個會導緻執行計劃中掃描很多行資料的操作變得更加昂貴，因而執行計劃會偏向掃描更少的函數。例如，一個全表掃描會比範圍掃描要昂貴的多。

engine_cost 表包含這些列:

engine_name

要應用這個成本項的存儲引擎的名稱（不區分大小寫）。如果這些值是default，那麼對所有沒在表裡指定的存儲引擎都會生效。如果server無法認出引擎名稱，會在errorlog輸出一條warning。

device_type

這個成本項适用的裝置類型。這個列可以為不同的儲存設備指定不同的成本開銷，例如sas盤和ssd盤是不一樣的。不過目前，這個資訊還沒啟用，隻有0可以設定。

cost_value，last_update，comment

這三列的含義跟server_cost表中的字段含義一樣.

engine_cost表的主鍵是 (cost_name, engine_name, device_type)，是以不允許為一個引擎的同一類儲存設備建立相同的成本項。

目前server隻識别engine_cost表中的一個cost_name：

io_block_read_cost

這個成本項表示從磁盤讀取一個資料的成本。增加這個值會導緻執行計劃中讀取磁盤塊會有更高的成本，是以優化器會偏向于讀取更少的磁盤塊。例如，一個全表掃描會比一個範圍掃描讀取更少的磁盤塊，是以優化器會偏向範圍掃描。