MySQL 5.7 optimizer cost model

转载

叶金荣 2021-07-13 11:46:18

文章标签 MySQL 5.7 文章分类 MySQL 数据库

在MySQL 5.7版本以后，mysql库下面新增了两张cost表，主要用途是方便用于来调整MySQL优化器对于不同对象的cost计算方式，例如临时表、扫描行、排序等等。

Server层一些操作的成本估算项的值特定引擎的一些操作的成本估算项的值

root@mysqldb 21:54: [mysql]> select * from engine_cost
-> ;
+-------------+-------------+------------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name | cost_value | last_update | comment |
+-------------+-------------+------------------------+------------+---------------------+---------+
| default | 0 | io_block_read_cost | NULL | 2018-12-13 15:07:13 | NULL |
| default | 0 | memory_block_read_cost | NULL | 2018-12-13 15:07:13 | NULL |
+-------------+-------------+------------------------+------------+---------------------+---------+
2 rows in set (0.00 sec)

每列含义：

列名	含义
cost_name	成本模型中的成本估算项的名称（不区分大小写）。如果Server无法识别名称，在读取的时候会打一个报错在error log中
cost_value	成本估算项的值。如果值是非NULL的，那么Server就使用这个值作为成本，否则就用编译时内置的值，DBA可以通过UPDATE这个列来修改响应的成本项。如果Server读到无效的值（例如负数），会在errorlog中打一条Warning
last_update	这一行的最后修改时间
comment	成本项的描述注释

ioblockread_cost ：这个成本项表示从磁盘读取一个数据的成本。增加这个值会导致执行计划中读取磁盘块会有更高的成本，因此优化器会偏向于读取更少的磁盘块。例如，一个全表扫描会比一个范围扫描读取更少的磁盘块，因此优化器会偏向范围扫描，默认值1.0
memoryblockreadcost：基本含义同ioblockreadcost，但表示从内存buffer中读取一个数据的成本，默认值1.0

每列含义：

列名	含义
engine_name	要应用这个成本项的存储引擎的名称（不区分大小写）。如果这些值是default，那么对所有没在表里指定的存储引擎都会生效。如果Server无法认出引擎名称，会在errorlog输出一条Warning
device_type	这个成本项适用的设备类型。这个列可以为不同的存储设备指定不同的成本开销，例如SAS盘和SSD盘是不一样的。不过目前，这个信息还没启用，只有0可以设置
cost_value	成本估算项的值。如果值是非NULL的，那么Server就使用这个值作为成本，否则就用编译时内置的值，DBA可以通过UPDATE这个列来修改响应的成本项。如果Server读到无效的值（例如负数），会在errorlog中打一条Warning
last_update	这一行的最后修改时间
comment	成本项的描述注释

disktemptablecreatecost, disktemptablerowcost：内部创建磁盘临时表的成本开销。增加这些成本项的值可以让优化器更偏向于生成不使用磁盘临时表的执行计划，disktemptablecreatecost默认值40，disktemptablerowcost默认值0.1
keycomparecost：比较记录键值的成本开销。增加这个值可以让执行计划中比较键值的操作成本变的更加昂贵。例如，一个执行计划执行了filesort，那么它的代价会比利用索引避免排序的代价要大得多，默认值0.1
memorytemptablecreatecost, memorytemptablerowcost：内部创建内存临时表的成本开销。增加这些值可以使得建立内部临时表成本增加，因而优化器会偏向于不使用临时表，memorytemptablecreatecost默认值2，memorytemptablerowcost默认值0.2
rowevaluatecost：扫描记录行的成本开销。增加这个会导致执行计划中扫描很多行数据的操作变得更加昂贵，因而执行计划会偏向扫描更少的函数，默认值0.2。例如，一个全表扫描会比范围扫描要昂贵的多

工作方式

可配置的优化器成本模型按如下方式工作：

MySQL Server在启动时读取成本模型表，并且在运行时使用内存中存贮的值。表中任何非NULL的成本估算项的值都会覆盖在代码中写死的默认成本常数，优先参与优化器成本计算。任何NULL值的成本估算项优化器都会认为用户没有指定特定的值，而使用代码中默认的成本常数。
在MySQL运行时，Server可能会重新读取成本表，可以通过动态载入存储引擎或者执行FLUSH OPTIMIZER_COSTS语句来触发。
成本估算表可以让管理员通过简单的方式去调整成本估算项，也可以通过把估算项设置为NULL来恢复原来的内置默认值。优化器使用的是内存中缓存的开销值，所以修改了表中的值后记得用FLUSH OPTIMIZER_COSTS命令让修改生效。
内存中缓存的成本项对当前正在执行的Session是不起效果的，一个Session内执行的Query其成本项的值是不会变动的。即使Server触发了重新读取成本表，任何估算项的变更也只影响后来链接上来的Session。
成本开销表是不参与复制的，只影响修改的本地实例，不会通过复制把开销表的变更复制到备库。

1.修改ioblockread_cost的cost值

UPDATE mysql.engine_cost SET cost_value = 2.0WHERE cost_name = 'io_block_read_cost';FLUSH OPTIMIZER_COSTS;

2.只修改innodb引擎对应的ioblockread_cost的cost值

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;

参考文章：

1.淘宝内核月报：http://mysql.taobao.org/monthly/2014/10/01/2.MySQL官方文档：https://dev.mysql.com/doc/refman/5.7/en/cost-model.html