MySQL 5.7 optimizer cost model

ゝ一纸荒年。 2023-07-17 11:21 41阅读 0赞

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

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

### engine\_cost ###

每列含义：

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

--------------------

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

--------------------

### server\_cost ###

每列含义：

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

--------------------

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

## 工作方式 ##

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

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

## 示例 ##

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

--------------------

由叶老师主讲的知数堂「MySQL优化课」第17期已发车，我们的课程从第15期就升级成MySQL 8.0版本了，现在上车刚刚好，一起开启MySQL 8.0的修行之旅吧。

扫码入群了解「MySQL优化」课吧，有助教妹子在等着哟

![format_png][]

--------------------

另外，叶老师在腾讯课堂的短课程《**MySQL性能优化**》已开课，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。

下面是报名小程序码

![format_png 1][]

点“在看”给我一朵小黄花![format_png 2][]

![format_png 3][]

[format_png]: /images/20230528/4d70a9d112b542c780c77028241f635d.png
[format_png 1]: /images/20230528/ca7eab89034942e3bc95f50e2f23b772.png
[format_png 2]: /images/20230528/b54789df41ed47509acb1b06952752a8.png
[format_png 3]: /images/20230528/5198b0714e1142fb9ab818d7544bbed9.png