MySQL 8.4 Release Notes
为了生成执行计划，优化器使用一个基于在查询执行过程中发生的各种操作成本估算的成本模型。优化器拥有一组编译好的默认 “成本常数”，可用于在执行计划决策时使用。To generate execution plans, the optimizer uses a cost model that is based on estimates of the cost of various operations that occur during query execution. The optimizer has a set of compiled-in default “cost constants” available to it to make decisions regarding execution plans.
优化器还拥有一个成本估算数据库，用于在执行计划构建期间使用。这些估算存储在 mysql 系统数据库中的 server_cost 和 engine_cost 表中，并且可以随时进行配置。这些表的目的是使调整优化器在尝试生成查询执行计划时使用的成本估算变得容易。The optimizer also has a database of cost estimates to use
during execution plan construction. These estimates are stored
in the server_cost and
engine_cost tables in the
mysql system database and are configurable at
any time. The intent of these tables is to make it possible to
easily adjust the cost estimates that the optimizer uses when it
attempts to arrive at query execution plans.
可配置的优化器成本模型的工作方式如下：The configurable optimizer cost model works like this:
服务器在启动时将成本模型表读取到内存中，并在运行时使用内存中的数值。表中指定的任何非
NULL成本估算都优先于相应的编译默认成本常量。任何NULL估算表示优化器将使用编译默认值。The server reads the cost model tables into memory at startup and uses the in-memory values at runtime. Any non-NULLcost estimate specified in the tables takes precedence over the corresponding compiled-in default cost constant. AnyNULLestimate indicates to the optimizer to use the compiled-in default.在运行时，服务器可能会重新读取成本表。这种情况发生在动态加载存储引擎或执行
FLUSH OPTIMIZER_COSTS语句时。At runtime, the server may re-read the cost tables. This occurs when a storage engine is dynamically loaded or when aFLUSH OPTIMIZER_COSTSstatement is executed.成本表使服务器管理员能够通过更改表中的条目轻松调整成本估算。通过将条目的成本设置为
NULL，也可以轻松恢复为默认值。优化器使用内存中的成本值，因此对表的更改应随后执行FLUSH OPTIMIZER_COSTS以生效。Cost tables enable server administrators to easily adjust cost estimates by changing entries in the tables. It is also easy to revert to a default by setting an entry's cost toNULL. The optimizer uses the in-memory cost values, so changes to the tables should be followed byFLUSH OPTIMIZER_COSTSto take effect.当客户端会话开始时，内存中的成本估算适用于整个会话，直到会话结束。特别是，如果服务器重新读取成本表，任何更改的估算仅适用于随后启动的会话。现有会话不受影响。The in-memory cost estimates that are current when a client session begins apply throughout that session until it ends. In particular, if the server re-reads the cost tables, any changed estimates apply only to subsequently started sessions. Existing sessions are unaffected.
成本表是特定于给定服务器实例的。服务器不会将成本表的更改复制到副本。Cost tables are specific to a given server instance. The server does not replicate cost table changes to replicas.
优化器成本模型数据库由 mysql 系统数据库中的两个表组成，这些表包含查询执行过程中操作的成本估算信息：The optimizer cost model database consists of two tables in
the mysql system database that contain cost
estimate information for operations that occur during query
execution:
server_cost 表包含以下列：The server_cost table contains these
columns:
cost_name成本模型中使用的成本估算名称。名称不区分大小写。如果服务器在读取此表时无法识别成本名称，它会在错误日志中写入警告。The name of a cost estimate used in the cost model. The name is not case-sensitive. If the server does not recognize the cost name when it reads this table, it writes a warning to the error log.
cost_value成本估算值。如果该值非
NULL，服务器将使用该值作为成本。否则，它将使用默认估算值（编译时内置的值）。DBA 可以通过更新此列来更改成本估算。如果服务器在读取此表时发现成本值无效（非正数），它会在错误日志中写入警告。The cost estimate value. If the value is non-NULL, the server uses it as the cost. Otherwise, it uses the default estimate (the compiled-in value). DBAs can change a cost estimate by updating this column. If the server finds that the cost value is invalid (nonpositive) when it reads this table, it writes a warning to the error log.要覆盖默认成本估算（针对指定为
NULL的条目），请将成本设置为非NULL的值。要恢复为默认值，请将值设置为NULL。然后执行FLUSH OPTIMIZER_COSTS告诉服务器重新读取成本表。To override a default cost estimate (for an entry that specifiesNULL), set the cost to a non-NULLvalue. To revert to the default, set the value toNULL. Then executeFLUSH OPTIMIZER_COSTSto tell the server to re-read the cost tables.last_update最后一行更新的时间。The time of the last row update.
commentcomment与成本估算相关的描述性评论。数据库管理员可以使用此列提供有关为什么成本估算行存储特定值的信息。A descriptive comment associated with the cost estimate. DBAs can use this column to provide information about why a cost estimate row stores a particular value.
default_value成本估算的默认（编译内置）值。该列是只读生成列，即使相关的成本估算发生变化，它的值仍然保持不变。对于在运行时添加到表中的行，该列的值为
NULL。The default (compiled-in) value for the cost estimate. This column is a read-only generated column that retains its value even if the associated cost estimate is changed. For rows added to the table at runtime, the value of this column isNULL.
server_cost 表的主键是 cost_name 列，因此无法为任何成本估算创建多个条目。The primary key for the server_cost table
is the cost_name column, so it is not
possible to create multiple entries for any cost estimate.
服务器识别 server_cost 表的以下 cost_name 值：The server recognizes these cost_name
values for the server_cost table:
disk_temptable_create_cost,disk_temptable_row_cost用于内部创建的临时表（存储在基于磁盘的存储引擎中，例如
InnoDB或MyISAM）的成本估算。增加这些值会增加使用内部临时表的成本估算，并使优化器偏向使用较少内部临时表的查询计划。有关此类表的信息，请参见 第 10.4.4 节，“MySQL 中的内部临时表使用”。The cost estimates for internally created temporary tables stored in a disk-based storage engine (eitherInnoDBorMyISAM). Increasing these values increases the cost estimate of using internal temporary tables and makes the optimizer prefer query plans with less use of them. For information about such tables, see Section 10.4.4, “Internal Temporary Table Use in MySQL”.这些磁盘参数的默认值较大，相比于对应的内存参数默认值（
memory_temptable_create_cost、memory_temptable_row_cost），反映了基于磁盘的表处理成本更高。The larger default values for these disk parameters compared to the default values for the corresponding memory parameters (memory_temptable_create_cost,memory_temptable_row_cost) reflects the greater cost of processing disk-based tables.key_compare_cost比较记录键的成本。增加此值会导致比较许多键的查询计划变得更昂贵。例如，执行
filesort的查询计划相比于使用索引避免排序的查询计划，其成本相对更高。The cost of comparing record keys. Increasing this value causes a query plan that compares many keys to become more expensive. For example, a query plan that performs afilesortbecomes relatively more expensive compared to a query plan that avoids sorting by using an index.memory_temptable_create_cost,memory_temptable_row_cost用于内部创建并存储在
MEMORY存储引擎中的临时表的成本估算。增加这些值会提高使用内部临时表的成本估算，并使优化器更倾向于选择使用它们较少的查询计划。有关此类表的信息，请参见第 10.4.4 节，“MySQL 中的内部临时表使用”。The cost estimates for internally created temporary tables stored in theMEMORYstorage engine. Increasing these values increases the cost estimate of using internal temporary tables and makes the optimizer prefer query plans with less use of them. For information about such tables, see Section 10.4.4, “Internal Temporary Table Use in MySQL”.这些内存参数的默认值小于对应磁盘参数的默认值（
disk_temptable_create_cost，disk_temptable_row_cost），反映了处理基于内存的表的成本较低。The smaller default values for these memory parameters compared to the default values for the corresponding disk parameters (disk_temptable_create_cost,disk_temptable_row_cost) reflects the lesser cost of processing memory-based tables.row_evaluate_cost评估记录条件的成本。增加此值会导致检查大量行的查询计划相对于检查较少行的查询计划变得更昂贵。例如，相对于读取较少行的范围扫描，表扫描变得相对更昂贵。The cost of evaluating record conditions. Increasing this value causes a query plan that examines many rows to become more expensive compared to a query plan that examines fewer rows. For example, a table scan becomes relatively more expensive compared to a range scan that reads fewer rows.
engine_cost 表包含以下列：The engine_cost table contains these
columns:
engine_name此成本估算适用的存储引擎的名称。名称不区分大小写。如果值为
default，则适用于所有没有自己命名条目的存储引擎。如果服务器在读取此表时无法识别引擎名称，会在错误日志中写入警告。The name of the storage engine to which this cost estimate applies. The name is not case-sensitive. If the value isdefault, it applies to all storage engines that have no named entry of their own. If the server does not recognize the engine name when it reads this table, it writes a warning to the error log.device_type此成本估算适用的设备类型。该列用于为不同的存储设备类型指定不同的成本估算，例如硬盘驱动器与固态驱动器。目前，该信息尚未使用，且唯一允许的值为 0。The device type to which this cost estimate applies. The column is intended for specifying different cost estimates for different storage device types, such as hard disk drives versus solid state drives. Currently, this information is not used and 0 is the only permitted value.
cost_name与
server_cost表相同。Same as in theserver_costtable.cost_value与
server_cost表相同。Same as in theserver_costtable.last_update与
server_cost表相同。Same as in theserver_costtable.commentcomment与
server_cost表相同。Same as in theserver_costtable.default_value成本估算的默认（编译时内置）值。此列是一个只读生成列，即使关联的成本估算发生更改，也会保留其值。对于在运行时添加到表中的行，此列的值为
NULL，但如果该行的cost_name值与原始行中的某个值相同，则default_value列与该行的值相同。The default (compiled-in) value for the cost estimate. This column is a read-only generated column that retains its value even if the associated cost estimate is changed. For rows added to the table at runtime, the value of this column isNULL, with the exception that if the row has the samecost_namevalue as one of the original rows, thedefault_valuecolumn has the same value as that row.
engine_cost 表的主键是由 (cost_name、engine_name、device_type) 列组成的元组，因此无法为这些列组合的值创建多个条目。The primary key for the engine_cost table
is a tuple comprising the (cost_name,
engine_name,
device_type) columns, so it is not possible
to create multiple entries for any combination of values in
those columns.
服务器识别 engine_cost 表的这些 cost_name 值：The server recognizes these cost_name
values for the engine_cost table:
io_block_read_cost从磁盘读取索引或数据块的成本。增加此值会导致读取大量磁盘块的查询计划相比于读取较少磁盘块的查询计划变得更昂贵。例如，与读取较少块的范围扫描相比，全表扫描相对更昂贵。The cost of reading an index or data block from disk. Increasing this value causes a query plan that reads many disk blocks to become more expensive compared to a query plan that reads fewer disk blocks. For example, a table scan becomes relatively more expensive compared to a range scan that reads fewer blocks.
memory_block_read_cost类似于
io_block_read_cost，但是表示从内存数据库缓冲区读取索引或数据块的成本。Similar toio_block_read_cost, but represents the cost of reading an index or data block from an in-memory database buffer.
如果 io_block_read_cost 和 memory_block_read_cost 的值不同，相同查询的两次执行计划可能会发生变化。假设内存访问的成本低于磁盘访问的成本。在服务器启动时，如果数据尚未被读入缓冲池，你可能会得到与查询运行后不同的执行计划，因为那时数据已经在内存中。If the io_block_read_cost and
memory_block_read_cost values differ, the
execution plan may change between two runs of the same query.
Suppose that the cost for memory access is less than the cost
for disk access. In that case, at server startup before data
has been read into the buffer pool, you may get a different
plan than after the query has been run because then the data
is in memory.
对于希望更改默认成本模型参数的数据库管理员，可以尝试将值加倍或减半，并测量其效果。For DBAs who wish to change the cost model parameters from their defaults, try doubling or halving the value and measuring the effect.
更改 io_block_read_cost 和 memory_block_read_cost 参数最有可能产生有价值的结果。这些参数值使数据访问方法的成本模型能够考虑从不同来源读取信息的成本；也就是说，从磁盘读取信息与从内存缓冲区读取信息的成本。例如，在其他条件相同的情况下，将 io_block_read_cost 设置为比 memory_block_read_cost 更大的值，会导致优化器更倾向于选择读取已存于内存中的信息的查询计划，而不是必须从磁盘读取的计划。Changes to the io_block_read_cost and
memory_block_read_cost parameters are most
likely to yield worthwhile results. These parameter values
enable cost models for data access methods to take into
account the costs of reading information from different
sources; that is, the cost of reading information from disk
versus reading information already in a memory buffer. For
example, all other things being equal, setting
io_block_read_cost to a value larger than
memory_block_read_cost causes the optimizer
to prefer query plans that read information already held in
memory to plans that must read from disk.
下面的示例演示了如何更改 io_block_read_cost 的默认值：This example shows how to change the default value for
io_block_read_cost:
UPDATE mysql.engine_cost SET cost_value = 2.0 WHERE cost_name = 'io_block_read_cost'; FLUSH OPTIMIZER_COSTS;
此示例展示了如何仅为 InnoDB 存储引擎更改 io_block_read_cost 的值：This example shows how to change the value of
io_block_read_cost only for the
InnoDB storage engine:
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;