优化器成本记录表|全方位认识mysql系统库

上一期《统计信息记录表|全方位认识 MySQL 系统库》中，我们详细介绍了mysql系统库中的统计信息记录表，本期我们将为大家带来系列第五篇《优化器成本记录表|全方位认识 mysql 系统库》，下面请跟随我们一起开始 mysql 系统库的系统学习之旅吧！

01 优化器成本模型概述

为了生成执行计划，优化器使用了基于成本的模型来对各种操作成本进行估算。 优化器具有一组可编辑的默认“成本常量”（这些值存储在mysql系统数据库下的server_cost和engine_cost表中），可用于调节执行计划的决策。

* server_cost： server常规操作需要使用到的优化器成本估算常量值。

* engine_cost： 针对特定存储引擎的操作需要使用到的的优化器成本估算常量值。

当服务器启动时会将成本模型表读入内存中，在生成执行计划时使用内存中的值。 表中指定的任何非NULL成本估算常量值优先使用。剩余其他任何NULL常量值在使用时会转换为内置的默认常量值。

成本常量值在服务器运行过程中允许动态修改(通过修改server_cost和engine_cost表实现，修改完成后需要执行FLUSH OPTIMIZER_COSTS语句重新加载)，如果发现修改不对或者需要重置，可以直接把响应的成本常量值设置为NULL即可。

对成本常量值的修改的影响类似于全局变量的修改，只对修改之后新的连接生效，对修改之前已经建立的连接不生效（无论是否执行过FLUSH OPTIMIZER_COSTS语句）。

server_cost和engine_cost表中的成本常量数据仅适用于当前实例，对其修改不会进行复制同步。

下面分别对这两张表进行详细说明。

02  优化器成本记录表详解

      2.1. engine_cost

该表提供查询针对特定存储引擎的操作需要使用到的的优化器成本估算常量值。

下面是该表中存储的信息内容。

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： 此成本估算常量适用的存储引擎的名称。名称不区分大小写。如果该值是缺省值，则表示适用于所有存储引擎。如果Server在读取此表时未识别引擎名称，则会向错误日志写入警告(默认值default除外，这里指的是非法值)。

• device_type： 此成本估算常量适用的设备类型。该列旨在为不同的存储设备类型指定不同的成本估算常量，例如：为机械硬盘与固态硬盘指定不同的估算常量值。目前该字段未使用，目前的唯一有效值为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）： 从磁盘读取索引或数据块的成本。与增加此值时的查询计划相比，读取更多磁盘块的查询计划与读取更少磁盘块的查询计划相比会被查询计划认为更加昂贵。例如：与读取较少块的范围扫描相比，表扫描被认为是昂贵的。

• memory_block_read_cost（默认1.0）： 与io_block_read_cost类似，表示从内存缓冲区中读取索引或数据块的估算常量。

如果io_block_read_cost和memory_block_read_cost值不同，则执行计划可能会在相同查询的两次运行时发现执行发生了变化（例如： 执行计划不同或者执行时间不同）。例如：假设内存访问的成本低于磁盘访问的成本。在这种情况下，在服务器启动时还未完成将数据读入缓冲池之前与之后，两次执行相同的查询您可能会得到不同的计划。

对io_block_read_cost和memory_block_read_cost参数的更改可能会为查询计划带来收益，例如： 在所有其他条件都相同的情况下，将io_block_read_cost值设置为大于memory_block_read_cost的值会使优化程序更喜欢走通过在内存中查询数据的查询计划。

修改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常规操作需要使用到的优化器成本估算常量值。

下 面是该表中存储的信息内容。

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： 成本模型中使用的成本估算变量名称。名称不区分大小写。如果Server在读取此表时未识别成本名称，则会向错误日志写入警告。

• cost_value： 成本估算变量值。如果该值不为NULL，则Server将直接使用其用作成本计算。否则，它使用默认估计值（代码内的编译默认值）。DBA可以通过更新此列值以影响成本估算。但需要确保指定的是有效值（留意表结构中的字段数据类型），如果Server在读取此表时发现成本值无效（不正确），则会向错误日志写入警告。如果需要恢复默认值，只需要将此字段设置为NULL值即可，然后执行FLUSH OPTIMIZER_COSTS语句来通知Server重新读取表中的数据。

• last_update： 最后一次更新该行记录的时间。

• comment： 与成本估算变量相关的描述性信息。

• PS： server_cost表拥有主键列cost_name，因此不可能出现为某个成本估算变量设置多个值的情况。

表中记录的内容即为Server识别的成本估算常量，如下：

• disk_temptable_create_cost（默认为40.0），disk_temptable_row_cost（默认为1.0）： 基于磁盘的内部临时表（InnoDB或MyISAM）的成本估算常量。增加这些值会增加使用基于磁盘的内部临时表的成本估计值，查询优化器在进行成本估算时会偏向于更少使用它，与相应的基于内存的内部临时表的参数（memory_temptable_create_cost，memory_temptable_row_cost）的缺省值相比，默认值较大。

• key_compare_cost（默认0.1）： 比较索引键值记录的成本常量。增加此值会让查询优化器认为查询较多索引键值是昂贵的。因为，查询计划会尽量避免文件排序(基于索引的排序)。

• memory_temptable_create_cost（默认2.0），memory_temptable_row_cost（默认0.2）： 基于MEMORY存储引擎的内部临时表的成本估算常量。增加这些值会增加使用内部内存临时表的成本估计值，即会使得优化器偏向于更少使用它。

• row_evaluate_cost（默认值为0.2）： 评估记录行的成本常量。与读取较少行的范围扫描相比，表扫描变得相对昂贵，查询计划会偏向于更少使用表扫描

本期内容就介绍到这里，本期内容参考链接如下：

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

| 作者简介

罗小波·沃趣科技高级数据库技术专家