上一期《統(tǒng)計(jì)信息記錄表|全方位認(rèn)識 mysql 系統(tǒng)庫》中，我們詳細(xì)介紹了mysql系統(tǒng)庫中的統(tǒng)計(jì)信息記錄表，本期我們將為大家?guī)硐盗械谖迤秲?yōu)化器成本記錄表|全方位認(rèn)識 mysql 系統(tǒng)庫》，下面請跟隨我們一起開始 mysql 系統(tǒng)庫的系統(tǒng)學(xué)習(xí)之旅吧！
01 優(yōu)化器成本模型概述
為了生成執(zhí)行計(jì)劃，優(yōu)化器使用了基于成本的模型來對各種操作成本進(jìn)行估算。
優(yōu)化器具有一組可編輯的默認(rèn)“成本常量”（這些值存儲在mysql系統(tǒng)數(shù)據(jù)庫下的server_cost和engine_cost表中），可用于調(diào)節(jié)執(zhí)行計(jì)劃的決策。
* server_cost：
server常規(guī)操作需要使用到的優(yōu)化器成本估算常量值。
* engine_cost：
針對特定存儲引擎的操作需要使用到的的優(yōu)化器成本估算常量值。
當(dāng)服務(wù)器啟動時(shí)會將成本模型表讀入內(nèi)存中，在生成執(zhí)行計(jì)劃時(shí)使用內(nèi)存中的值。
表中指定的任何非NULL成本估算常量值優(yōu)先使用。剩余其他任何NULL常量值在使用時(shí)會轉(zhuǎn)換為內(nèi)置的默認(rèn)常量值。
成本常量值在服務(wù)器運(yùn)行過程中允許動態(tài)修改(通過修改server_cost和engine_cost表實(shí)現(xiàn)，修改完成后需要執(zhí)行FLUSH OPTIMIZER_COSTS語句重新加載)，如果發(fā)現(xiàn)修改不對或者需要重置，可以直接把響應(yīng)的成本常量值設(shè)置為NULL即可。
對成本常量值的修改的影響類似于全局變量的修改，只對修改之后新的連接生效，對修改之前已經(jīng)建立的連接不生效（無論是否執(zhí)行過FLUSH OPTIMIZER_COSTS語句）。
server_cost和engine_cost表中的成本常量數(shù)據(jù)僅適用于當(dāng)前實(shí)例，對其修改不會進(jìn)行復(fù)制同步。
下面分別對這兩張表進(jìn)行詳細(xì)說明。
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在讀取此表時(shí)未識別引擎名稱，則會向錯(cuò)誤日志寫入警告(默認(rèn)值default除外，這里指的是非法值)。
device_type：
此成本估算常量適用的設(shè)備類型。該列旨在為不同的存儲設(shè)備類型指定不同的成本估算常量，例如：為機(jī)械硬盤與固態(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），所以這三列組合值必須唯一，不可建多個(gè)條目。
該表中記錄的有效成本常量值如下：
io_block_read_cost（默認(rèn)1.0）：
從磁盤讀取索引或數(shù)據(jù)塊的成本。與增加此值時(shí)的查詢計(jì)劃相比，讀取更多磁盤塊的查詢計(jì)劃與讀取更少磁盤塊的查詢計(jì)劃相比會被查詢計(jì)劃認(rèn)為更加昂貴。例如：與讀取較少塊的范圍掃描相比，表掃描被認(rèn)為是昂貴的。
memory_block_read_cost（默認(rèn)1.0）：
與io_block_read_cost類似，表示從內(nèi)存緩沖區(qū)中讀取索引或數(shù)據(jù)塊的估算常量。
如果io_block_read_cost和memory_block_read_cost值不同，則執(zhí)行計(jì)劃可能會在相同查詢的兩次運(yùn)行時(shí)發(fā)現(xiàn)執(zhí)行發(fā)生了變化（例如：
執(zhí)行計(jì)劃不同或者執(zhí)行時(shí)間不同）。例如：假設(shè)內(nèi)存訪問的成本低于磁盤訪問的成本。在這種情況下，在服務(wù)器啟動時(shí)還未完成將數(shù)據(jù)讀入緩沖池之前與之后，兩次執(zhí)行相同的查詢您可能會得到不同的計(jì)劃。
對io_block_read_cost和memory_block_read_cost參數(shù)的更改可能會為查詢計(jì)劃帶來收益，例如：
在所有其他條件都相同的情況下，將io_block_read_cost值設(shè)置為大于memory_block_read_cost的值會使優(yōu)化程序更喜歡走通過在內(nèi)存中查詢數(shù)據(jù)的查詢計(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引擎單獨(dú)插入一行常量值
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在讀取此表時(shí)未識別成本名稱，則會向錯(cuò)誤日志寫入警告。
cost_value：
成本估算變量值。如果該值不為NULL，則Server將直接使用其用作成本計(jì)算。否則，它使用默認(rèn)估計(jì)值（代碼內(nèi)的編譯默認(rèn)值）。DBA可以通過更新此列值以影響成本估算。但需要確保指定的是有效值（留意表結(jié)構(gòu)中的字段數(shù)據(jù)類型），如果Server在讀取此表時(shí)發(fā)現(xiàn)成本值無效（不正確），則會向錯(cuò)誤日志寫入警告。如果需要恢復(fù)默認(rèn)值，只需要將此字段設(shè)置為NULL值即可，然后執(zhí)行FLUSH OPTIMIZER_COSTS語句來通知Server重新讀取表中的數(shù)據(jù)。
last_update：
最后一次更新該行記錄的時(shí)間。
comment：
與成本估算變量相關(guān)的描述性信息。
PS：
server_cost表擁有主鍵列cost_name，因此不可能出現(xiàn)為某個(gè)成本估算變量設(shè)置多個(gè)值的情況。
表中記錄的內(nèi)容即為Server識別的成本估算常量，如下：
disk_temptable_create_cost（默認(rèn)為40.0），disk_temptable_row_cost（默認(rèn)為1.0）：
基于磁盤的內(nèi)部臨時(shí)表（InnoDB或MyISAM）的成本估算常量。增加這些值會增加使用基于磁盤的內(nèi)部臨時(shí)表的成本估計(jì)值，查詢優(yōu)化器在進(jìn)行成本估算時(shí)會偏向于更少使用它，與相應(yīng)的基于內(nèi)存的內(nèi)部臨時(shí)表的參數(shù)（memory_temptable_create_cost，memory_temptable_row_cost）的缺省值相比，默認(rèn)值較大。
key_compare_cost（默認(rèn)0.1）：
比較索引鍵值記錄的成本常量。增加此值會讓查詢優(yōu)化器認(rèn)為查詢較多索引鍵值是昂貴的。因?yàn)?，查詢?jì)劃會盡量避免文件排序(基于索引的排序)。
memory_temptable_create_cost（默認(rèn)2.0），memory_temptable_row_cost（默認(rèn)0.2）：
基于MEMORY存儲引擎的內(nèi)部臨時(shí)表的成本估算常量。增加這些值會增加使用內(nèi)部內(nèi)存臨時(shí)表的成本估計(jì)值，即會使得優(yōu)化器偏向于更少使用它。
row_evaluate_cost（默認(rèn)值為0.2）：
評估記錄行的成本常量。與讀取較少行的范圍掃描相比，表掃描變得相對昂貴，查詢計(jì)劃會偏向于更少使用表掃描
本期內(nèi)容就介紹到這里，本期內(nèi)容參考鏈接如下：
https://dev.mysql.com/doc/refman/5.7/en/cost-model.html
"翻過這座山，你就可以看到一片海！
"。
堅(jiān)持閱讀我們的"全方位認(rèn)識 mysql 系統(tǒng)庫"系列文章分享，你就可以系統(tǒng)地學(xué)完它。
謝謝你的閱讀，我們下期不見不散！
成都創(chuàng)新互聯(lián)憑借專業(yè)的設(shè)計(jì)團(tuán)隊(duì)扎實(shí)的技術(shù)支持、優(yōu)質(zhì)高效的服務(wù)意識和豐厚的資源優(yōu)勢，提供專業(yè)的網(wǎng)站策劃、
網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、網(wǎng)站優(yōu)化、軟件開發(fā)、網(wǎng)站改版等服務(wù)，在成都十年的網(wǎng)站建設(shè)設(shè)計(jì)經(jīng)驗(yàn)，為成都1000+中小型企業(yè)策劃設(shè)計(jì)了網(wǎng)站。
| 作者簡介
羅小波·沃趣科技高級數(shù)據(jù)庫技術(shù)專家
IT從業(yè)多年，主要負(fù)責(zé)MySQL 產(chǎn)品的數(shù)據(jù)庫支撐與售后二線支撐。曾參與版本發(fā)布系統(tǒng)、輕量級監(jiān)控系統(tǒng)、運(yùn)維管理平臺、數(shù)據(jù)庫管理平臺的設(shè)計(jì)與編寫，熟悉MySQL體系結(jié)構(gòu)，Innodb存儲引擎，喜好專研開源技術(shù)，多次在公開場合做過線下線上數(shù)據(jù)庫專題分享，發(fā)表過多篇數(shù)據(jù)庫相關(guān)的研究文章。
網(wǎng)頁標(biāo)題：優(yōu)化器成本記錄表|全方位認(rèn)識mysql系統(tǒng)庫-創(chuàng)新互聯(lián)
文章網(wǎng)址：
http://weahome.cn/article/dpcghe.html
重慶分公司
重慶分公司
