mysql 优化器 cost评估_mysql8 参考手册--优化器成本模型

为了生成执行计划，优化器使用成本模型，该模型基于对查询执行期间发生的各种操作的成本进行估算。优化器具有一组内置的默认“ 成本常数 ”，可用于制定有关执行计划的决策。

优化器还有一个成本估算数据库，可在执行计划构建期间使用。这些估计值存储在系统数据库的server_cost和 engine_cost表中， mysql并且可以随时配置。这些表的目的是使得可以轻松调整优化器在尝试得出查询执行计划时使用的成本估算。

成本模型一般运作

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

服务器在启动时将成本模型表读取到内存中，并在运行时使用内存中的值。NULL表中指定的任何非成本估算值都优先于相应的已编译默认成本常量。任何NULL 估计值都指示优化器使用编译后的默认值。

在运行时，服务器可以重新读取成本表。当动态加载存储引擎或FLUSH OPTIMIZER_COSTS 执行语句时，会发生这种情况。

成本表使服务器管理员可以通过更改表中的条目轻松调整成本估算。通过将条目的费用设置为，也很容易恢复为默认值NULL。优化器使用内存中的成本值，因此对表的更改应随后 FLUSH OPTIMIZER_COSTS生效。

客户会话开始时当前的内存中成本估算将在整个会话中应用，直到结束。特别是，如果服务器重新读取成本表，则任何更改的估算值仅适用于随后启动的会话。现有会话不受影响。

成本表特定于给定的服务器实例。服务器不会将成本表更改复制到复制从属服务器。

成本模型数据库

优化器成本模型数据库由mysql系统数据库中的两个表组成，其中包含查询执行期间发生的操作的成本估算信息：

1、server_cost：针对一般服务器操作的优化器成本估算

2、engine_cost：针对特定存储引擎的特定操作的优化器成本估算

该server_cost表包含以下列：

cost_name

成本模型中使用的成本估算的名称。名称不区分大小写。如果服务器在读取该表时无法识别成本名称，则会向错误日志中写入警告。

cost_value

成本估算值。如果该值为non- NULL，则服务器将其用作成本。否则，它将使用默认估计值(编译值)。DBA可以通过更新此列来更改成本估算。如果服务器在读取此表时发现成本值无效(非正值)，则会向错误日志中写入警告。

要覆盖默认费用估算值(对于指定的条目NULL)，请将费用设置为非NULL值。要恢复为默认值，请将值设置为NULL。然后执行FLUSH OPTIMIZER_COSTS以告知服务器重新读取成本表。

last_update

最后一行的更新时间。

comment

与成本估算相关的描述性注释。DBA可以使用此列来提供有关成本估算行为何存储特定值的信息。

default_value

成本估算的默认(内置)值。此列是只读生成的列，即使更改了相关的成本估算，它也会保留其值。对于在运行时添加到表中的行，此列的值为NULL。

该server_cost表的主键是该cost_name列，因此无法为任何成本估算创建多个条目。

服务器识别表的这些cost_name 值server_cost：

1、disk_temptable_create_cost， disk_temptable_row_cost

内部存储的临时表的成本估算，这些临时表存储在基于磁盘的存储引擎中( InnoDB或MyISAM)。增加这些值会增加使用内部临时表的成本估算，并使优化器更喜欢较少使用它们的查询计划。有关此类表的信息，请参见 第8.4.4节“ MySQL中的内部临时表使用”。

对于相比于相应的存储器参数的缺省值，这些磁盘参数较大的默认值(memory_temptable_create_cost， memory_temptable_row_cost)反映处理的基于磁盘的表的更高的成本。

2、key_compare_cost

比较记录键的成本。增加该值将导致查询计划比较多个键变得更加昂贵。例如，filesort与避免使用索引进行排序的查询计划相比，执行的查询计划 变得相对昂贵。

3、memory_temptable_create_cost， memory_temptable_row_cost

MEMORY存储引擎中 内部创建的临时表的成本估算。增加这些值会增加使用内部临时表的成本估算，并使优化器更喜欢较少使用它们的查询计划。有关此类表的信息，请参见 第8.4.4节“ MySQL中的内部临时表使用”。

与相应磁盘参数的默认值()相比，这些内存参数的默认值较小disk_temptable_create_cost， disk_temptable_row_cost反映了处理基于内存的表的成本较低。

4、row_evaluate_cost

评估记录条件的成本。与检查较少行的查询计划相比，增加该值会导致检查许多行的查询计划变得更加昂贵。例如，与读取较少行的范围扫描相比，表扫描变得相对昂贵。

engine_cost表包含以下列：

engine_name

此成本估算适用的存储引擎的名称。名称不区分大小写。如果值为 default，则适用于所有没有自己命名条目的存储引擎。如果服务器在读取该表时无法识别引擎名称，则会向错误日志中写入警告。

device_type

此费用估算适用的设备类型。该列旨在为不同的存储设备类型(例如，硬盘驱动器与固态驱动器)指定不同的成本估算。当前，此信息未使用，并且0是唯一允许的值。

cost_name

与server_cost表相同。

cost_value

与server_cost表相同。

last_update

与server_cost表相同。

comment

与server_cost表相同。

default_value

成本估算的默认(内置)值。此列是只读生成的列，即使更改了相关的成本估算，它也会保留其值。对于在运行时添加到表中的行，此列的值为，不同的是NULL，如果该行cost_name与原始行之一具有相同的值，则该 default_value列将与该行具有相同的值。

对于主键engine_cost表是包含(一个元组cost_name， engine_name， device_type)个列，所以它不可能在这些列中的值的任意组合来创建多个条目。

服务器识别表的这些cost_name 值engine_cost：

io_block_read_cost

从磁盘读取索引或数据块的成本。与读取较少磁盘块的查询计划相比，增加该值会使读取许多磁盘块的查询计划变得更加昂贵。例如，与读取较少块的范围扫描相比，表扫描变得相对昂贵。

memory_block_read_cost

与相似io_block_read_cost，但是代表从内存数据库缓冲区读取索引或数据块的开销。

如果io_block_read_cost和 memory_block_read_cost值不同，则执行计划可能会在同一查询的两次运行之间改变。假设内存访问的成本小于磁盘访问的成本。在这种情况下，在服务器启动之前，将数据读入缓冲池之前，您可能会获得与运行查询之后不同的计划，因为这样数据就将存储在内存中。

*更改成本模型数据库8

对于希望从其默认值更改成本模型参数的DBA，请尝试将该值加倍或减半并测量效果。

io_block_read_cost和 memory_block_read_cost参数的 更改最有可能产生有价值的结果。这些参数值使数据访问方法的成本模型可以考虑从不同来源读取信息的成本。也就是说，从磁盘读取信息的成本与读取内存缓冲区中已经存在的信息的成本。例如，在所有其他条件相同的情况下，将其设置 io_block_read_cost为大于 memory_block_read_cost会导致优化器将查询计划(该计划读取的是已保存在内存中的信息)优先于必须从磁盘读取的计划。

本示例说明如何更改以下项的默认值 io_block_read_cost：

UPDATE mysql.engine_cost

SET cost_value = 2.0

WHERE cost_name = 'io_block_read_cost';

FLUSH OPTIMIZER_COSTS;

此示例显示如何更改io_block_read_cost仅 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;