为了生成执行计划,优化器使用一个成本模型,该模型基于查询执行期间发生的各种操作的成本估计。优化器有一组编译后的默认值”成本的常量”它可以做出有关执行计划的决定。
优化器还有一个成本估算数据库,在执行计划构建期间使用。这些估计值存储在server_cost
而且engine_cost
表mysql
系统数据库,可在任何时候进行配置。这些表的目的是方便地调整优化器在尝试获得查询执行计划时使用的成本估计。
可配置优化器成本模型是这样工作的:
服务器在启动时将成本模型表读入内存,并在运行时使用内存中的值。任何非
零
表中指定的成本估计优先于相应的编译后默认成本常数。任何零
Estimate指示优化器使用编译后的默认值。在运行时,服务器可能会重新读取成本表。这种情况发生在动态加载存储引擎或
冲洗OPTIMIZER_COSTS
执行语句。成本表使服务器管理员可以通过更改表中的条目轻松地调整成本估计。通过将条目的开销设置为,也很容易恢复为默认值
零
.优化器使用内存中的成本值,因此对表的更改应该遵循以下操作冲洗OPTIMIZER_COSTS
生效。客户端会话开始时的内存中成本估算在整个会话中都适用,直到会话结束。特别是,如果服务器重新读取成本表,任何更改的估计只适用于随后开始的会话。现有的会话不受影响。
成本表是特定于给定服务器实例的。服务器不会将成本表更改复制到副本。
中的优化器成本模型数据库由两个表组成mysql
包含查询执行期间发生的操作的成本估计信息的系统数据库:
的server_cost
表包含以下列:
cost_name
成本模型中使用的成本估算的名称。名称不区分大小写。如果服务器在读取这个表时没有识别出成本名,它就会在错误日志中写入一个警告。
cost_value
成本估算值。如果为non-
零
,服务器将其作为成本。否则,它将使用默认值(编译后的值)。dba可以通过更新这一列来更改成本估算。如果服务器在读取这个表时发现成本值是无效的(非正的),它就会在错误日志中写入一个警告。覆盖缺省成本估算(对于指定
零
),将代价设置为非零
价值。若要恢复默认值,请将该值设置为零
.然后执行冲洗OPTIMIZER_COSTS
告诉服务器重新读取成本表。last_update
上次更新行的时间。
评论
与成本估计相关的描述性评论。dba可以使用此列提供关于成本估算行为何存储特定值的信息。
default_value
成本估算的默认(编译后的)值。该列是只读生成的列,即使更改了相关的成本估计,也会保留其值。对于在运行时添加到表中的行,该列的值为
零
.
的主键server_cost
表是cost_name
列,因此不可能为任何成本估算创建多个条目。
服务器会识别这些cost_name
的值server_cost
表:
disk_temptable_create_cost
,disk_temptable_row_cost
存储在基于磁盘的存储引擎中内部创建的临时表的成本估计
InnoDB
或MyISAM
).增加这些值会增加使用内部临时表的成本估计,并使优化器更喜欢较少使用它们的查询计划。有关此类表的信息,请参见第8.4.4节,“MySQL内部临时表的使用”.与相应内存参数的默认值相比,这些磁盘参数的默认值更大(
memory_temptable_create_cost
,memory_temptable_row_cost
)反映了处理基于磁盘的表的更大成本。key_compare_cost
比较记录键的成本。增加该值会导致比较多个键的查询计划的开销增加。例如,执行
filesort
与使用索引避免排序的查询计划相比,会变得相对昂贵。memory_temptable_create_cost
,memory_temptable_row_cost
中存储的内部创建临时表的成本估计
内存
存储引擎。增加这些值会增加使用内部临时表的成本估计,并使优化器更喜欢较少使用它们的查询计划。有关此类表的信息,请参见第8.4.4节,“MySQL内部临时表的使用”.与相应磁盘参数的默认值相比,这些内存参数的默认值较小(
disk_temptable_create_cost
,disk_temptable_row_cost
)反映处理基于内存的表的成本更低。row_evaluate_cost
成本评估记录条件。与检查更少行的查询计划相比,增加这个值会导致检查多行的查询计划的开销更大。例如,与读取更少行的范围扫描相比,表扫描的开销相对更大。
的engine_cost
表包含以下列:
engine_name
用于此成本估算的存储引擎的名称。名称不区分大小写。如果值是
默认的
,它适用于所有自己没有命名条目的存储引擎。如果服务器在读取这个表时没有识别引擎名称,它就会向错误日志写入一个警告。device_type
此成本估计适用的设备类型。该列用于指定不同存储设备类型的不同成本估计,例如硬盘驱动器和固态驱动器。目前,这个信息还没有使用,0是唯一允许的值。
cost_name
和在
server_cost
表格cost_value
和在
server_cost
表格last_update
和在
server_cost
表格评论
和在
server_cost
表格default_value
成本估算的默认(编译后的)值。该列是只读生成的列,即使更改了相关的成本估计,也会保留其值。对于在运行时添加到表中的行,该列的值为
零
,例外情况是如果行具有相同的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
值不同,执行计划可能会在同一查询的两次运行之间改变。假设访问内存的成本小于访问磁盘的成本。在这种情况下,在服务器启动时,在数据被读入缓冲池之前,您可能会得到与运行查询之后不同的计划,因为那时数据在内存中。
对于希望更改默认成本模型参数的dba,可以尝试将值加倍或减半,并测量效果。
更改io_block_read_cost
而且memory_block_read_cost
参数最有可能产生有价值的结果。这些参数值使数据访问方法的成本模型能够考虑从不同来源读取信息的成本;也就是说,从磁盘读取信息的成本与读取已经存在于内存缓冲区中的信息的成本。例如,在其他条件相同的情况下io_block_read_cost
到大于的值memory_block_read_cost
导致优化器更喜欢读取内存中已经保存的信息的查询计划,而不是必须从磁盘读取的计划。
这个例子展示了如何更改的默认值io_block_read_cost
:
更新mysql。engine_costSET cost_value = 2.0 WHERE cost_name = 'io_block_read_cost'; FLUSH OPTIMIZER_COSTS;
的值的更改io_block_read_cost
只是为了InnoDB
存储引擎:
插入mysql。engine_costVALUES ('InnoDB', 0, 'io_block_read_cost', 3.0, CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB'); FLUSH OPTIMIZER_COSTS;