上一期《統(tǒng)計信息記錄表|全方位認識 mysql 系統(tǒng)庫》中，我們詳細介紹了mysql系統(tǒng)庫中的統(tǒng)計信息記錄表，本期我們將為大家?guī)硐盗械谖迤秲?yōu)化器成本記錄表|全方位認識 mysql 系統(tǒng)庫》，下面請跟隨我們一起開始 mysql 系統(tǒng)庫的系統(tǒng)學習之旅吧！

01 優(yōu)化器成本模型概述

為了生成執(zhí)行計劃，優(yōu)化器使用了基于成本的模型來對各種操作成本進行估算。 優(yōu)化器具有一組可編輯的默認“成本常量”（這些值存儲在mysql系統(tǒng)數(shù)據(jù)庫下的server_cost和engine_cost表中），可用于調(diào)節(jié)執(zhí)行計劃的決策。

* server_cost： server常規(guī)操作需要使用到的優(yōu)化器成本估算常量值。

* engine_cost： 針對特定存儲引擎的操作需要使用到的的優(yōu)化器成本估算常量值。

當服務器啟動時會將成本模型表讀入內(nèi)存中，在生成執(zhí)行計劃時使用內(nèi)存中的值。 表中指定的任何非NULL成本估算常量值優(yōu)先使用。剩余其他任何NULL常量值在使用時會轉(zhuǎn)換為內(nèi)置的默認常量值。

成本常量值在服務器運行過程中允許動態(tài)修改(通過修改server_cost和engine_cost表實現(xiàn)，修改完成后需要執(zhí)行FLUSH OPTIMIZER_COSTS語句重新加載)，如果發(fā)現(xiàn)修改不對或者需要重置，可以直接把響應的成本常量值設置為NULL即可。

對成本常量值的修改的影響類似于全局變量的修改，只對修改之后新的連接生效，對修改之前已經(jīng)建立的連接不生效（無論是否執(zhí)行過FLUSH OPTIMIZER_COSTS語句）。

server_cost和engine_cost表中的成本常量數(shù)據(jù)僅適用于當前實例，對其修改不會進行復制同步。

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

02 優(yōu)化器成本記錄表詳解

  2.1. engine_cost

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

下面是該表中存儲的信息內(nèi)容。

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： 此成本估算常量適用的存儲引擎的名稱。名稱不區(qū)分大小寫。如果該值是缺省值，則表示適用于所有存儲引擎。如果Server在讀取此表時未識別引擎名稱，則會向錯誤日志寫入警告(默認值default除外，這里指的是非法值)。

• device_type： 此成本估算常量適用的設備類型。該列旨在為不同的存儲設備類型指定不同的成本估算常量，例如：為機械硬盤與固態(tài)硬盤指定不同的估算常量值。目前該字段未使用，目前的唯一有效值為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）： 從磁盤讀取索引或數(shù)據(jù)塊的成本。與增加此值時的查詢計劃相比，讀取更多磁盤塊的查詢計劃與讀取更少磁盤塊的查詢計劃相比會被查詢計劃認為更加昂貴。例如：與讀取較少塊的范圍掃描相比，表掃描被認為是昂貴的。

• memory_block_read_cost（默認1.0）： 與io_block_read_cost類似，表示從內(nèi)存緩沖區(qū)中讀取索引或數(shù)據(jù)塊的估算常量。

如果io_block_read_cost和memory_block_read_cost值不同，則執(zhí)行計劃可能會在相同查詢的兩次運行時發(fā)現(xiàn)執(zhí)行發(fā)生了變化（例如： 執(zhí)行計劃不同或者執(zhí)行時間不同）。例如：假設內(nèi)存訪問的成本低于磁盤訪問的成本。在這種情況下，在服務器啟動時還未完成將數(shù)據(jù)讀入緩沖池之前與之后，兩次執(zhí)行相同的查詢您可能會得到不同的計劃。

對io_block_read_cost和memory_block_read_cost參數(shù)的更改可能會為查詢計劃帶來收益，例如： 在所有其他條件都相同的情況下，將io_block_read_cost值設置為大于memory_block_read_cost的值會使優(yōu)化程序更喜歡走通過在內(nèi)存中查詢數(shù)據(jù)的查詢計劃。

修改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常規(guī)操作需要使用到的優(yōu)化器成本估算常量值。

下 面是該表中存儲的信息內(nèi)容。

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： 成本模型中使用的成本估算變量名稱。名稱不區(qū)分大小寫。如果Server在讀取此表時未識別成本名稱，則會向錯誤日志寫入警告。

• cost_value： 成本估算變量值。如果該值不為NULL，則Server將直接使用其用作成本計算。否則，它使用默認估計值（代碼內(nèi)的編譯默認值）。DBA可以通過更新此列值以影響成本估算。但需要確保指定的是有效值（留意表結(jié)構(gòu)中的字段數(shù)據(jù)類型），如果Server在讀取此表時發(fā)現(xiàn)成本值無效（不正確），則會向錯誤日志寫入警告。如果需要恢復默認值，只需要將此字段設置為NULL值即可，然后執(zhí)行FLUSH OPTIMIZER_COSTS語句來通知Server重新讀取表中的數(shù)據(jù)。

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

• comment： 與成本估算變量相關的描述性信息。

• PS： server_cost表擁有主鍵列cost_name，因此不可能出現(xiàn)為某個成本估算變量設置多個值的情況。

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

• disk_temptable_create_cost（默認為40.0），disk_temptable_row_cost（默認為1.0）： 基于磁盤的內(nèi)部臨時表（InnoDB或MyISAM）的成本估算常量。增加這些值會增加使用基于磁盤的內(nèi)部臨時表的成本估計值，查詢優(yōu)化器在進行成本估算時會偏向于更少使用它，與相應的基于內(nèi)存的內(nèi)部臨時表的參數(shù)（memory_temptable_create_cost，memory_temptable_row_cost）的缺省值相比，默認值較大。

• key_compare_cost（默認0.1）： 比較索引鍵值記錄的成本常量。增加此值會讓查詢優(yōu)化器認為查詢較多索引鍵值是昂貴的。因為，查詢計劃會盡量避免文件排序(基于索引的排序)。

• memory_temptable_create_cost（默認2.0），memory_temptable_row_cost（默認0.2）： 基于MEMORY存儲引擎的內(nèi)部臨時表的成本估算常量。增加這些值會增加使用內(nèi)部內(nèi)存臨時表的成本估計值，即會使得優(yōu)化器偏向于更少使用它。

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

本期內(nèi)容就介紹到這里，本期內(nèi)容參考鏈接如下：

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

"翻過這座山，你就可以看到一片海！ "。 堅持閱讀我們的"全方位認識 mysql 系統(tǒng)庫"系列文章分享，你就可以系統(tǒng)地學完它。 謝謝你的閱讀，我們下期不見不散！

成都創(chuàng)新互聯(lián)憑借專業(yè)的設計團隊扎實的技術支持、優(yōu)質(zhì)高效的服務意識和豐厚的資源優(yōu)勢，提供專業(yè)的網(wǎng)站策劃、網(wǎng)站設計、成都網(wǎng)站建設、網(wǎng)站優(yōu)化、軟件開發(fā)、網(wǎng)站改版等服務，在成都十年的網(wǎng)站建設設計經(jīng)驗，為成都1000+中小型企業(yè)策劃設計了網(wǎng)站。

| 作者簡介

羅小波·沃趣科技高級數(shù)據(jù)庫技術專家

IT從業(yè)多年，主要負責MySQL 產(chǎn)品的數(shù)據(jù)庫支撐與售后二線支撐。曾參與版本發(fā)布系統(tǒng)、輕量級監(jiān)控系統(tǒng)、運維管理平臺、數(shù)據(jù)庫管理平臺的設計與編寫，熟悉MySQL體系結(jié)構(gòu)，Innodb存儲引擎，喜好專研開源技術，多次在公開場合做過線下線上數(shù)據(jù)庫專題分享，發(fā)表過多篇數(shù)據(jù)庫相關的研究文章。

本文題目：優(yōu)化器成本記錄表|全方位認識mysql系統(tǒng)庫-創(chuàng)新互聯(lián)
網(wǎng)站URL：http://jinyejixie.com/article44/dpcghe.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供自適應網(wǎng)站、網(wǎng)站策劃、品牌網(wǎng)站設計、網(wǎng)站設計公司、全網(wǎng)營銷推廣、定制網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)