MySQL优化器成本记录表

﹏ヽ暗。殇╰゛Y 2022-09-05 15:50 175阅读 0赞

SQL优化器会分析所有可能的执行计划，选择成本最低的执行，这种优化器称之为：CBO（Cost-based Optimizer，基于成本的优化器）。

# 什么是成本 #

MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者  
说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模  
糊的，其实在MySQL中一条查询语句的执行成本是由下边这两个方面组成的：

## IO成本 ##

我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到  
磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然  
后再操作。将索引或者数据以页为基本单位，从磁盘加载到内存的过程称为IO成本。

## CPU成本 ##

在页中定位记录，并且判断记录是否满足搜索条件，对结果集排序等操作称为CPU成本。

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL  
规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索  
条件的成本默认是 0.2。1.0、0.2 这些数字称之为成本常数，这两个成本常数我  
们最常用到，当然还有其他的成本常数。

注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

那么一页的数据是多大呢，可以通过下面的命令查看：

# 成本常数 #

参考官网：[**https://dev.mysql.com/doc/refman/5.7/en/cost-model.html**][https_dev.mysql.com_doc_refman_5.7_en_cost-model.html]

## server\_cost（server层） ##

该表提供查询server常规操作需要使用到的优化器成本估算常量值。

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

mysql> show create table mysql.server_cost;
    +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table       | Create Table                                                                                                                                                                                                                                                                                                              |
    +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | server_cost | CREATE TABLE `server_cost` (
      `cost_name` varchar(64) NOT NULL,
      `cost_value` float DEFAULT NULL,
      `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `comment` varchar(1024) DEFAULT NULL,
      PRIMARY KEY (`cost_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 |
    +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from mysql.server_cost;
    +------------------------------+------------+---------------------+---------+
    | cost_name                    | cost_value | last_update         | comment |
    +------------------------------+------------+---------------------+---------+
    | disk_temptable_create_cost   |       NULL | 2021-08-11 08:50:05 | NULL    |
    | disk_temptable_row_cost      |       NULL | 2021-08-11 08:50:05 | NULL    |
    | key_compare_cost             |       NULL | 2021-08-11 08:50:05 | NULL    |
    | memory_temptable_create_cost |       NULL | 2021-08-11 08:50:05 | NULL    |
    | memory_temptable_row_cost    |       NULL | 2021-08-11 08:50:05 | NULL    |
    | row_evaluate_cost            |       NULL | 2021-08-11 08:50:05 | 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： 与成本估算变量相关的描述性信息。

server\_cost表拥有主键列cost\_name，因此不可能出现为某个成本估算变量设置多个值的情况。

### 常量值含义 ###

*  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）： 评估记录行的成本常量。与读取较少行的范围扫描相比，表扫描变得相对昂贵，查询计划会偏向于更少使用表扫描。

## **engine\_cost（存储引擎层）** ##

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

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

mysql> show create table mysql.engine_cost;
    +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                  |
    +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | engine_cost | CREATE TABLE `engine_cost` (
      `engine_name` varchar(64) NOT NULL,
      `device_type` int(11) NOT NULL,
      `cost_name` varchar(64) NOT NULL,
      `cost_value` float DEFAULT NULL,
      `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `comment` varchar(1024) DEFAULT NULL,
      PRIMARY KEY (`cost_name`,`engine_name`,`device_type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 |
    +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from mysql.engine_cost;
    +-------------+-------------+------------------------+------------+---------------------+---------+
    | engine_name | device_type | cost_name              | cost_value | last_update         | comment |
    +-------------+-------------+------------------------+------------+---------------------+---------+
    | default     |           0 | io_block_read_cost     |       NULL | 2021-08-11 08:50:05 | NULL    |
    | default     |           0 | memory_block_read_cost |       NULL | 2021-08-11 08:50:05 | 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的示例信息如下：

为innodb引擎单独插入一行常量值：

mysql> insert into mysql.engine_cost values('InnoDB',0,'io_block_read_cost',2, null, null);
    
    Query OK, 1 row affected (0.04 sec)
    
    mysql> flush optimizer_costs;
    Query OK, 0 rows affected (0.00 sec)

# 版本之间的差异 #

上述展示的成本常量都是基于MySQL5.7.35。

MySQL8中各个成本常量都发生了很大的变化。

[https_dev.mysql.com_doc_refman_5.7_en_cost-model.html]: https://dev.mysql.com/doc/refman/5.7/en/cost-model.html