優(yōu)化器成本記錄表|全方位認(rèn)識(shí) mysql 系統(tǒng)庫(kù)

上一期《統(tǒng)計(jì)信息記錄表|全方位認(rèn)識(shí) mysql 系統(tǒng)庫(kù)》中，我們?cè)敿?xì)介紹了mysql系統(tǒng)庫(kù)中的統(tǒng)計(jì)信息記錄表，本期我們將為大家?guī)?lái)系列第五篇《優(yōu)化器成本記錄表|全方位認(rèn)識(shí) mysql 系統(tǒng)庫(kù)》，下面請(qǐng)跟隨我們一起開始 mysql 系統(tǒng)庫(kù)的系統(tǒng)學(xué)習(xí)之旅吧！

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

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

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

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

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

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

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

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

下面分別對(duì)這兩張表進(jìn)行詳細(xì)說(shuō)明。

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

      2.1. engine_cost

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

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

• device_type： 此成本估算常量適用的設(shè)備類型。該列旨在為不同的存儲(chǔ)設(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ì)劃相比會(huì)被查詢計(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ì)劃可能會(huì)在相同查詢的兩次運(yùn)行時(shí)發(fā)現(xiàn)執(zhí)行發(fā)生了變化（例如： 執(zhí)行計(jì)劃不同或者執(zhí)行時(shí)間不同）。例如：假設(shè)內(nèi)存訪問(wèn)的成本低于磁盤訪問(wèn)的成本。在這種情況下，在服務(wù)器啟動(dòng)時(shí)還未完成將數(shù)據(jù)讀入緩沖池之前與之后，兩次執(zhí)行相同的查詢您可能會(huì)得到不同的計(jì)劃。

對(duì)io_block_read_cost和memory_block_read_cost參數(shù)的更改可能會(huì)為查詢計(jì)劃帶來(lái)收益，例如： 在所有其他條件都相同的情況下，將io_block_read_cost值設(shè)置為大于memory_block_read_cost的值會(huì)使優(yōu)化程序更喜歡走通過(guò)在內(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)化器成本估算常量值。

下 面是該表中存儲(chǔ)的信息內(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í)未識(shí)別成本名稱，則會(huì)向錯(cuò)誤日志寫入警告。

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

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

• comment： 與成本估算變量相關(guān)的描述性信息。

• PS： server_cost表?yè)碛兄麈I列cost_name，因此不可能出現(xiàn)為某個(gè)成本估算變量設(shè)置多個(gè)值的情況。

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

• disk_temptable_create_cost（默認(rèn)為40.0），disk_temptable_row_cost（默認(rèn)為1.0）： 基于磁盤的內(nèi)部臨時(shí)表（InnoDB或MyISAM）的成本估算常量。增加這些值會(huì)增加使用基于磁盤的內(nèi)部臨時(shí)表的成本估計(jì)值，查詢優(yōu)化器在進(jìn)行成本估算時(shí)會(huì)偏向于更少使用它，與相應(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）： 比較索引鍵值記錄的成本常量。增加此值會(huì)讓查詢優(yōu)化器認(rèn)為查詢較多索引鍵值是昂貴的。因?yàn)?，查詢?jì)劃會(huì)盡量避免文件排序(基于索引的排序)。

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

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

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

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

| 作者簡(jiǎn)介

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