InnoDB 启动选项InnoDB Startup Options

InnoDB 系统变量InnoDB System Variables

可以在服务器启动时启用或禁用布尔类型的系统变量，启用时直接命名变量，禁用时使用 --skip- 前缀。例如，要启用或禁用 InnoDB 自适应哈希索引，可以在命令行中使用 --innodb-adaptive-hash-index 或 --skip-innodb-adaptive-hash-index，或者在选项文件中使用 innodb_adaptive_hash_index 或 skip_innodb_adaptive_hash_index。System variables that are true or false can be enabled at server startup by naming them, or disabled by using a --skip- prefix. For example, to enable or disable the InnoDB adaptive hash index, you can use --innodb-adaptive-hash-index or --skip-innodb-adaptive-hash-index on the command line, or innodb_adaptive_hash_index or skip_innodb_adaptive_hash_index in an option file.

一些变量描述提到“启用”或“禁用”某个变量。这些变量可以通过SET语句设置为ON或1来启用，或设置为OFF或0来禁用。布尔变量可以在启动时设置为ON、TRUE、OFF和FALSE（不区分大小写），也可以设置为1和0。参见第 6.2.2.4 节，“程序选项修改符”。Some variable descriptions refer to “enabling” or “disabling” a variable. These variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. Boolean variables can be set at startup to the values ON, TRUE, OFF, and FALSE (not case-sensitive), as well as 1 and 0. See Section 6.2.2.4, “Program Option Modifiers”.

取数值的系统变量可以在命令行上指定为--var_name=value，或在选项文件中指定为var_name=value。System variables that take a numeric value can be specified as --var_name=value on the command line or as var_name=value in option files.

许多系统变量可以在运行时更改（参见第 7.1.9.2 节，“动态系统变量”）。Many system variables can be changed at runtime (see Section 7.1.9.2, “Dynamic System Variables”).

有关GLOBAL和SESSION变量作用域修饰符的信息，请参阅SET语句文档。For information about GLOBAL and SESSION variable scope modifiers, refer to the SET statement documentation.

某些选项控制 InnoDB 数据文件的位置和布局。第 17.8.1 节，“InnoDB 启动配置” 说明了如何使用这些选项。Certain options control the locations and layout of the InnoDB data files. Section 17.8.1, “InnoDB Startup Configuration” explains how to use these options.

一些选项可能初期不会使用，它们可以根据机器容量和数据库 工作负载 调整 InnoDB 的性能特性。Some options, which you might not use initially, help tune InnoDB performance characteristics based on machine capacity and database workload.

有关指定选项和系统变量的更多信息，请参见 第 6.2.2 节，“指定程序选项”。For more information on specifying options and system variables, see Section 6.2.2, “Specifying Program Options”.

--innodb-dedicated-server

当通过命令行或 my.cnf 文件使用 --innodb-dedicated-server 或 --innodb-dedicated-server=ON 启动服务器时，InnoDB 会自动计算并设置以下变量的值：When this option is set by starting the server with --innodb-dedicated-server or --innodb-dedicated-server=ON, either on the command line or in a my.cnf file, InnoDB automatically calculates and sets the values of the following variables:

您应当仅在 MySQL 实例运行在可以使用所有可用系统资源的专用服务器上时考虑使用 --innodb-dedicated-server。如果 MySQL 实例与其他应用程序共享系统资源，则不建议使用此选项。You should consider using --innodb-dedicated-server only if the MySQL instance resides on a dedicated server where it can use all available system resources. Using this option is not recommended if the MySQL instance shares system resources with other applications.

强烈建议您在生产环境中使用此选项之前，先阅读 第 17.8.12 节，“为专用 MySQL 服务器启用自动 InnoDB 配置”。It is strongly recommended that you read Section 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”, before using this option in production.

--innodb-status-file

--innodb-status-file 启动选项控制 InnoDB 是否在数据目录中创建一个名为 innodb_status.pid 的文件，并大约每 15 秒将 SHOW ENGINE INNODB STATUS 输出写入该文件。The --innodb-status-file startup option controls whether InnoDB creates a file named innodb_status.pid in the data directory and writes SHOW ENGINE INNODB STATUS output to it every 15 seconds, approximately.

默认情况下不创建 innodb_status.pid 文件。要创建它，请使用 --innodb-status-file 选项启动 mysqld。当服务器正常关闭时，InnoDB 会删除该文件。如果发生异常关闭，则可能需要手动删除状态文件。The innodb_status.pid file is not created by default. To create it, start mysqld with the --innodb-status-file option. InnoDB removes the file when the server is shut down normally. If an abnormal shutdown occurs, the status file may have to be removed manually.

--innodb-status-file 选项用于临时用途，因为生成 SHOW ENGINE INNODB STATUS 输出可能会影响性能，并且 innodb_status.pid 文件随时间可能会变得非常大。The --innodb-status-file option is intended for temporary use, as SHOW ENGINE INNODB STATUS output generation can affect performance, and the innodb_status.pid file can become quite large over time.

有关相关信息，请参阅 第 17.17.2 节，“启用 InnoDB 监视器”。For related information, see Section 17.17.2, “Enabling InnoDB Monitors”.

innodb_adaptive_flushing

Specifies whether to dynamically adjust the rate of flushing dirty pages in the InnoDB 缓冲池 based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity. This setting is enabled by default. See Section 17.8.3.5, “Configuring Buffer Pool Flushing” for more information. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.Specifies whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer pool based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity. This setting is enabled by default. See Section 17.8.3.5, “Configuring Buffer Pool Flushing” for more information. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.

innodb_adaptive_flushing_lwm

定义低水位标志，表示启用自适应刷新时重做日志容量的百分比。有关更多信息，请参见第 17.8.3.5 节，“配置缓冲池刷新”。Defines the low water mark representing percentage of redo log capacity at which adaptive flushing is enabled. For more information, see Section 17.8.3.5, “Configuring Buffer Pool Flushing”.

innodb_adaptive_hash_index

是否启用InnoDB 自适应哈希索引。根据您的工作负载，动态启用或禁用自适应哈希索引可能有助于提高查询性能。由于自适应哈希索引并非对所有工作负载都有效，应在启用和禁用的情况下使用真实工作负载进行基准测试。有关详细信息，请参见第 17.5.3 节，“自适应哈希索引”。Whether the InnoDB adaptive hash index is enabled or disabled. It may be desirable, depending on your workload, to dynamically enable or disable adaptive hash indexing to improve query performance. Because the adaptive hash index may not be useful for all workloads, conduct benchmarks with it both enabled and disabled, using realistic workloads. See Section 17.5.3, “Adaptive Hash Index” for details.

此变量默认情况下是禁用的。您可以使用SET GLOBAL语句修改此参数，而无需重启服务器。在运行时更改此设置需要具有足够权限来设置全局系统变量。请参见第 7.1.9.1 节，“系统变量权限”。您还可以在服务器启动时使用--innodb-adaptive-hash-index来启用它。This variable is disabled by default. You can modify this parameter using the SET GLOBAL statement, without restarting the server. Changing the setting at runtime requires privileges sufficient to set global system variables. See Section 7.1.9.1, “System Variable Privileges”. You can also use --innodb-adaptive-hash-index at server startup to enable it.

禁用自适应哈希索引会立即清空哈希表。哈希表清空的同时，正常操作可以继续进行，并且执行使用哈希表的查询将直接访问索引 B 树。当重新启用自适应哈希索引时，哈希表会在正常操作中重新填充。Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.

在 MySQL 8.4 之前，此选项默认启用。Before MySQL 8.4, this option was enabled by default.

innodb_adaptive_hash_index_parts

对自适应哈希索引搜索系统进行分区。每个索引绑定到特定分区，每个分区由单独的闩保护。Partitions the adaptive hash index search system. Each index is bound to a specific partition, with each partition protected by a separate latch.

自适应哈希索引搜索系统默认分为 8 个部分。最大设置为 512。The adaptive hash index search system is partitioned into 8 parts by default. The maximum setting is 512.

有关相关信息，请参见 第 17.5.3 节，“自适应哈希索引”。For related information, see Section 17.5.3, “Adaptive Hash Index”.

innodb_adaptive_max_sleep_delay

允许InnoDB根据当前工作负载自动调整innodb_thread_sleep_delay的值向上或向下。任何非零值都启用自动、动态调整innodb_thread_sleep_delay的功能，最大可达innodb_adaptive_max_sleep_delay参数中指定的值。该值表示微秒数。此选项在繁忙系统中非常有用，尤其是InnoDB线程超过 16 个的情况下。（实际上，它对拥有数百或数千个同时连接的 MySQL 系统最有价值。）Permits InnoDB to automatically adjust the value of innodb_thread_sleep_delay up or down according to the current workload. Any nonzero value enables automated, dynamic adjustment of the innodb_thread_sleep_delay value, up to the maximum value specified in the innodb_adaptive_max_sleep_delay option. The value represents the number of microseconds. This option can be useful in busy systems, with greater than 16 InnoDB threads. (In practice, it is most valuable for MySQL systems with hundreds or thousands of simultaneous connections.)

欲了解更多信息，请参见第 17.8.4 节，“配置 InnoDB 的线程并发”。For more information, see Section 17.8.4, “Configuring Thread Concurrency for InnoDB”.

innodb_autoextend_increment

当自动扩展的 InnoDB 系统表空间 文件满时，用于扩展大小的增量（以兆字节为单位）。默认值为 64。有关相关信息，请参见 系统表空间数据文件配置 和 调整系统表空间大小。The increment size (in megabytes) for extending the size of an auto-extending InnoDB system tablespace file when it becomes full. The default value is 64. For related information, see System Tablespace Data File Configuration, and Resizing the System Tablespace.

innodb_autoextend_increment 设置不影响 每表文件表空间 或 通用表空间 文件。这些文件无论 innodb_autoextend_increment 设置如何，都会自动扩展。初始扩展为小幅度，之后的扩展以 4MB 增量进行。The innodb_autoextend_increment setting does not affect file-per-table tablespace files or general tablespace files. These files are auto-extending regardless of the innodb_autoextend_increment setting. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.

innodb_autoinc_lock_mode

用于生成自增值的锁模式。允许的值为 0、1 或 2，分别对应传统、连续或交错模式。The lock mode to use for generating auto-increment values. Permissible values are 0, 1, or 2, for traditional, consecutive, or interleaved, respectively.

默认设置为 2（交错），以兼容基于行的复制。The default setting is 2 (interleaved), for compatibility with row-based replication.

有关每种锁模式的特性，请参见InnoDB AUTO_INCREMENT 锁模式。For the characteristics of each lock mode, see InnoDB AUTO_INCREMENT Lock Modes.

innodb_background_drop_list_empty

启用innodb_background_drop_list_empty调试选项可以通过延迟表创建直到后台删除列表为空来避免测试用例失败。例如，如果测试用例 A 将表t1放入后台删除列表中，则测试用例 B 将在后台删除列表为空之前等待，然后再创建表t1。Enabling the innodb_background_drop_list_empty debug option helps avoid test case failures by delaying table creation until the background drop list is empty. For example, if test case A places table t1 on the background drop list, test case B waits until the background drop list is empty before creating table t1.

innodb_buffer_pool_chunk_size

innodb_buffer_pool_chunk_size 定义了 InnoDB 缓冲池调整大小操作的块大小。innodb_buffer_pool_chunk_size defines the chunk size for InnoDB buffer pool resizing operations.

为了在调整大小操作期间避免复制所有缓冲池页面，操作是以 “块” 的方式执行的。默认情况下，innodb_buffer_pool_chunk_size 为 128MB（134217728 字节）。块中包含的页面数量取决于 innodb_page_size 的值。innodb_buffer_pool_chunk_size 可以以 1MB（1048576 字节）为单位增加或减少。To avoid copying all buffer pool pages during resizing operations, the operation is performed in “chunks”. By default, innodb_buffer_pool_chunk_size is 128MB (134217728 bytes). The number of pages contained in a chunk depends on the value of innodb_page_size. innodb_buffer_pool_chunk_size can be increased or decreased in units of 1MB (1048576 bytes).

更改 innodb_buffer_pool_chunk_size 值时，适用以下条件：The following conditions apply when altering the innodb_buffer_pool_chunk_size value:

为了避免潜在的性能问题，chunk 的数量（innodb_buffer_pool_size / innodb_buffer_pool_chunk_size）不应超过 1000。To avoid potential performance issues, the number of chunks (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) should not exceed 1000.

innodb_buffer_pool_size 变量是动态的，可以在服务器在线时调整缓冲池大小。然而，缓冲池大小必须等于或是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数，更改任意一个变量设置都需要重启服务器。The innodb_buffer_pool_size variable is dynamic, which permits resizing the buffer pool while the server is online. However, the buffer pool size must be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, and changing either of those variable settings requires restarting the server.

有关更多信息，请参阅 第 17.8.3.1 节，“配置 InnoDB 缓冲池大小”。See Section 17.8.3.1, “Configuring InnoDB Buffer Pool Size” for more information.

innodb_buffer_pool_debug

启用此选项允许在缓冲池小于 1GB 时使用多个缓冲池实例，忽略在 innodb_buffer_pool_instances 上施加的 1GB 最小缓冲池大小限制。只有在使用 WITH_DEBUG CMake 选项编译了调试支持时，才提供 innodb_buffer_pool_debug 选项。Enabling this option permits multiple buffer pool instances when the buffer pool is less than 1GB in size, ignoring the 1GB minimum buffer pool size constraint imposed on innodb_buffer_pool_instances. The innodb_buffer_pool_debug option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

innodb_buffer_pool_dump_at_shutdown

指定在 MySQL 服务器关闭时是否记录缓存于 InnoDB 缓冲池 的页面，以缩短下一次重启时的 预热 过程。通常与 innodb_buffer_pool_load_at_startup 配合使用。innodb_buffer_pool_dump_pct 选项定义要转储的最近最常使用缓冲池页面的百分比。Specifies whether to record the pages cached in the InnoDB buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart. Typically used in combination with innodb_buffer_pool_load_at_startup. The innodb_buffer_pool_dump_pct option defines the percentage of most recently used buffer pool pages to dump.

innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup 默认均为启用。Both innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup are enabled by default.

更多信息，请参阅 章节 17.8.3.6，“保存和恢复缓冲池状态”。For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_dump_now

立即记录缓存于 InnoDB 缓冲池 中的页。通常与 innodb_buffer_pool_load_now 结合使用。Immediately makes a record of pages cached in the InnoDB buffer pool. Typically used in combination with innodb_buffer_pool_load_now.

启用 innodb_buffer_pool_dump_now 会触发记录操作，但不会更改变量设置，该变量始终保持 OFF 或 0。触发转储后，要查看缓冲池转储状态，请查询 Innodb_buffer_pool_dump_status 变量。Enabling innodb_buffer_pool_dump_now triggers the recording action but does not alter the variable setting, which always remains OFF or 0. To view buffer pool dump status after triggering a dump, query the Innodb_buffer_pool_dump_status variable.

启用 innodb_buffer_pool_dump_now 会触发转储操作，但不会更改变量设置，该变量始终保持 OFF 或 0。触发转储后，要查看缓冲池转储状态，请查询 Innodb_buffer_pool_dump_status 变量。Enabling innodb_buffer_pool_dump_now triggers the dump action but does not alter the variable setting, which always remains OFF or 0. To view buffer pool dump status after triggering a dump, query the Innodb_buffer_pool_dump_status variable.

更多信息，请参阅 章节 17.8.3.6，“保存和恢复缓冲池状态”。For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_dump_pct

指定每个缓冲池中最近使用的页面所占的百分比，以便读取和转储。范围是 1 到 100。默认值为 25。例如，如果有 4 个缓冲池，每个缓冲池有 100 个页面，并且 innodb_buffer_pool_dump_pct 设置为 25，则每个缓冲池最近使用的 25 个页面将被转储。Specifies the percentage of the most recently used pages for each buffer pool to read out and dump. The range is 1 to 100. The default value is 25. For example, if there are 4 buffer pools with 100 pages each, and innodb_buffer_pool_dump_pct is set to 25, the 25 most recently used pages from each buffer pool are dumped.

innodb_buffer_pool_filename

指定保存由 innodb_buffer_pool_dump_at_shutdown 或 innodb_buffer_pool_dump_now 生成的表空间 ID 和页面 ID 列表的文件名称。表空间 ID 和页面 ID 的保存格式如下：space, page_id。默认情况下，该文件名为 ib_buffer_pool，位于 InnoDB 数据目录中。非默认位置必须相对于数据目录指定。Specifies the name of the file that holds the list of tablespace IDs and page IDs produced by innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now. Tablespace IDs and page IDs are saved in the following format: space, page_id. By default, the file is named ib_buffer_pool and is located in the InnoDB data directory. A non-default location must be specified relative to the data directory.

可以在运行时使用 SET 语句指定文件名：A file name can be specified at runtime, using a SET statement:

SET GLOBAL innodb_buffer_pool_filename='file_name';

您也可以在启动时、在启动字符串中或在 MySQL 配置文件中指定文件名。启动时指定文件名时，该文件必须存在，否则 InnoDB 会返回启动错误，指示没有此类文件或目录。You can also specify a file name at startup, in a startup string or MySQL configuration file. When specifying a file name at startup, the file must exist or InnoDB returns a startup error indicating that there is no such file or directory.

更多信息，请参阅 章节 17.8.3.6，“保存和恢复缓冲池状态”。For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_in_core_file

禁用（默认）innodb_buffer_pool_in_core_file 变量可以通过排除 InnoDB 缓冲池页来减小核心文件的大小。Disabling (default) the innodb_buffer_pool_in_core_file variable reduces the size of core files by excluding InnoDB buffer pool pages.

要使用此变量，必须启用 core_file 变量，要禁用此选项，操作系统必须支持 MADV_DONTDUMP 对 madvise() 的非 POSIX 扩展，该功能在 Linux 3.4 及更高版本中受支持。有关更多信息，请参阅 第 17.8.3.7 节，“从核心文件中排除或包含缓冲池页”。To use this variable, the core_file variable must be enabled, and to disable this option the operating system must support the MADV_DONTDUMP non-POSIX extension to madvise(), which is supported in Linux 3.4 and later. For more information, see Section 17.8.3.7, “Excluding or Including Buffer Pool Pages from Core Files”.

在支持 MADV_DONTDUMP 的系统上默认禁用，通常仅限于 Linux，而不包括 macOS 或 Windows。This is disabled by default on systems that support MADV_DONTDUMP, which is typically only Linux and not macOS or Windows.

在 MySQL 8.4 之前，此选项默认启用。Before MySQL 8.4, this option was enabled by default.

innodb_buffer_pool_instances

InnoDB 缓冲池被划分的区域数量。对于拥有多 GB 范围缓冲池的系统，将缓冲池划分为独立实例可以提高并发性，因为不同线程在读写缓存页面时的争用会减少。存储在缓冲池中或从缓冲池读取的每个页面会随机分配给其中一个缓冲池实例，使用哈希函数。每个缓冲池实例管理其自己的空闲列表、刷新列表、LRU以及与缓冲池相关的所有其他数据结构，并由其自己的缓冲池互斥锁保护。The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool instance manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

总缓冲池大小在所有缓冲池实例之间分配。为了获得最佳效率，请指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以使每个缓冲池实例至少为 1GB。The total buffer pool size is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.

如果innodb_buffer_pool_size <= 1 GiB，则默认的innodb_buffer_pool_instances值为 1。If innodb_buffer_pool_size <= 1 GiB, then the default innodb_buffer_pool_instances value is 1.

如果 innodb_buffer_pool_size > 1 GiB，则默认的 innodb_buffer_pool_instances 值是在以下两个计算提示中取最小值，范围为 1-64：If innodb_buffer_pool_size > 1 GiB, then the default innodb_buffer_pool_instances value is the minimum value from the following two calculated hints, within a range of 1-64:

有关信息，请参阅 第 17.8.3.1 节，“配置 InnoDB 缓冲池大小”。For related information, see Section 17.8.3.1, “Configuring InnoDB Buffer Pool Size”.

innodb_buffer_pool_load_abort

中断恢复 InnoDB buffer pool 内容的过程，该过程由 innodb_buffer_pool_load_at_startup 或 innodb_buffer_pool_load_now 触发。Interrupts the process of restoring InnoDB buffer pool contents triggered by innodb_buffer_pool_load_at_startup or innodb_buffer_pool_load_now.

启用 innodb_buffer_pool_load_abort 会触发中止操作，但不会更改变量设置，该变量始终保持 OFF 或 0。要在触发中止操作后查看缓冲池加载状态，请查询 Innodb_buffer_pool_load_status 变量。Enabling innodb_buffer_pool_load_abort triggers the abort action but does not alter the variable setting, which always remains OFF or 0. To view buffer pool load status after triggering an abort action, query the Innodb_buffer_pool_load_status variable.

更多信息，请参阅 章节 17.8.3.6，“保存和恢复缓冲池状态”。For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_load_at_startup

指定在 MySQL 服务器启动时，InnoDB 缓冲池 会自动通过加载之前持有的相同页面来完成 热身。通常与 innodb_buffer_pool_dump_at_shutdown 配合使用。Specifies that, on MySQL server startup, the InnoDB buffer pool is automatically warmed up by loading the same pages it held at an earlier time. Typically used in combination with innodb_buffer_pool_dump_at_shutdown.

innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup 默认均为启用。Both innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup are enabled by default.

更多信息，请参阅 章节 17.8.3.6，“保存和恢复缓冲池状态”。For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_load_now

立即通过加载数据页而无需等待服务器重启来预热 InnoDB 缓冲池。这在基准测试期间将缓存内存恢复到已知状态或在运行报表或维护查询后让 MySQL 服务器准备恢复其正常工作负载时可能非常有用。Immediately warms up the InnoDB buffer pool by loading data pages without waiting for a server restart. Can be useful to bring cache memory back to a known state during benchmarking or to ready the MySQL server to resume its normal workload after running queries for reports or maintenance.

启用 innodb_buffer_pool_load_now 会触发加载操作，但不会更改变量设置，该变量始终保持 OFF 或 0。要在触发加载后查看缓冲池加载进度，请查询 Innodb_buffer_pool_load_status 变量。Enabling innodb_buffer_pool_load_now triggers the load action but does not alter the variable setting, which always remains OFF or 0. To view buffer pool load progress after triggering a load, query the Innodb_buffer_pool_load_status variable.

更多信息，请参阅 章节 17.8.3.6，“保存和恢复缓冲池状态”。For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_size

缓冲池的字节大小，即 InnoDB 缓存表和索引数据的内存区域。默认值为 134217728 字节（128MB）。最大值取决于 CPU 架构；在 32 位系统上最大值为 4294967295（2³²-1），在 64 位系统上最大值为 18446744073709551615（2⁶⁴-1）。在 32 位系统上，CPU 架构和操作系统可能会限制比标称最大值更低的实际最大值。当缓冲池的大小大于 1GB 时，将 innodb_buffer_pool_instances 设置为大于 1 的值可以提高繁忙服务器的可扩展性。The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 134217728 bytes (128MB). The maximum value depends on the CPU architecture; the maximum is 4294967295 (2³²-1) on 32-bit systems and 18446744073709551615 (2⁶⁴-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.

更大的缓冲池可以减少访问同一表数据多次时的磁盘 I/O。在专用数据库服务器上，你可以将缓冲池大小设置为机器物理内存的 80%。在配置缓冲池大小时，请注意以下潜在问题，并准备在必要时缩减缓冲池的大小。A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary.

当你增加或减少缓冲池大小时，该操作会分块执行。块大小由 innodb_buffer_pool_chunk_size 变量定义，默认值为 128 MB。When you increase or decrease buffer pool size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size variable, which has a default of 128 MB.

缓冲池大小必须始终等于或是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍。如果你将缓冲池大小更改为不等于或不是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍的值，缓冲池大小会自动调整为等于或是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍的值。Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you alter the buffer pool size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

innodb_buffer_pool_size 可以动态设置，这允许您在不重启服务器的情况下调整缓冲池大小。Innodb_buffer_pool_resize_status 状态变量报告在线缓冲池调整操作的状态。更多信息请参见 第 17.8.3.1 节，“配置 InnoDB 缓冲池大小”。innodb_buffer_pool_size can be set dynamically, which allows you to resize the buffer pool without restarting the server. The Innodb_buffer_pool_resize_status status variable reports the status of online buffer pool resizing operations. See Section 17.8.3.1, “Configuring InnoDB Buffer Pool Size” for more information.

如果服务器使用 --innodb-dedicated-server 启动，那么 innodb_buffer_pool_size 的值会在未显式定义时自动设置。更多信息请参见 第 17.8.12 节，“为专用 MySQL 服务器启用自动 InnoDB 配置”。If the server is started with --innodb-dedicated-server, the value of innodb_buffer_pool_size is set automatically if it is not explicitly defined. For more information, see Section 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”.

innodb_change_buffer_max_size

InnoDB 变更缓冲区的最大大小，占 缓冲池 总大小的百分比。对于具有大量插入、更新和删除活动的 MySQL 服务器，你可能会增加该值；对于用于报告且数据不变的 MySQL 服务器，你可能会减少该值。更多信息请参见 第 17.5.2 节，“变更缓冲区”。有关一般 I/O 调优建议，请参见 第 10.5.8 节，“优化 InnoDB 磁盘 I/O”。Maximum size for the InnoDB change buffer, as a percentage of the total size of the buffer pool. You might increase this value for a MySQL server with heavy insert, update, and delete activity, or decrease it for a MySQL server with unchanging data used for reporting. For more information, see Section 17.5.2, “Change Buffer”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.

innodb_change_buffering

是否启用 InnoDB 的 变更缓冲，这是一种优化方法，将写入操作延迟到辅助索引，以便 I/O 操作可以顺序执行。允许的值在下表中描述，也可以用数字指定值。Whether InnoDB performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. Permitted values are described in the following table. Values may also be specified numerically.

在 MySQL 8.4 之前，默认值是 all。Before MySQL 8.4, the default value was all.

更多信息，请参见 第 17.5.2 节，“变更缓冲”。有关一般 I/O 调优建议，请参见 第 10.5.8 节，“优化 InnoDB 磁盘 I/O”。For more information, see Section 17.5.2, “Change Buffer”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.

innodb_change_buffering_debug

设置 InnoDB 变更缓冲的调试标志。值为 1 强制所有更改进入变更缓冲。值为 2 在合并时会导致意外退出。默认值 0 表示未设置变更缓冲调试标志。此选项仅在使用 WITH_DEBUG CMake 选项编译的调试支持中可用。Sets a debug flag for InnoDB change buffering. A value of 1 forces all changes to the change buffer. A value of 2 causes an unexpected exit at merge. A default value of 0 indicates that the change buffering debug flag is not set. This option is only available when debugging support is compiled in using the WITH_DEBUG CMake option.

innodb_checkpoint_disabled

这是一个调试选项，只用于专家调试。它会禁用检查点，以便每次有意的服务器退出都会触发 InnoDB 恢复。它只应在短时间内启用，通常是在执行写入 redo 日志条目的 DML 操作之前，以便在服务器退出后需要恢复。仅当使用 WITH_DEBUG CMake 选项编译了调试支持时，此选项才可用。This is a debug option that is only intended for expert debugging use. It disables checkpoints so that a deliberate server exit always initiates InnoDB recovery. It should only be enabled for a short interval, typically before running DML operations that write redo log entries that would require recovery following a server exit. This option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

innodb_checksum_algorithm

指定如何生成和验证存储在 InnoDB 表空间 磁盘块中的 校验和。innodb_checksum_algorithm 的默认值为 crc32。Specifies how to generate and verify the checksum stored in the disk blocks of InnoDB tablespaces. The default value for innodb_checksum_algorithm is crc32.

值 innodb 与早期版本的 MySQL 向后兼容。值 crc32 使用一种算法，可以更快地计算每个修改块的校验和，并检查每次磁盘读取的校验和。它一次扫描 64 位的块，比 innodb 校验和算法扫描 8 位块的速度更快。值 none 在校验和字段中写入一个常量值，而不是基于块数据计算值。表空间中的块可以使用旧的、新的和无校验和的混合值，并随着数据的修改逐步更新；一旦表空间中的块被修改为使用 crc32 算法，则关联的表将无法被早期版本的 MySQL 读取。The value innodb is backward-compatible with earlier versions of MySQL. The value crc32 uses an algorithm that is faster to compute the checksum for every modified block, and to check the checksums for each disk read. It scans blocks 64 bits at a time, which is faster than the innodb checksum algorithm, which scans blocks 8 bits at a time. The value none writes a constant value in the checksum field rather than computing a value based on the block data. The blocks in a tablespace can use a mix of old, new, and no checksum values, being updated gradually as the data is modified; once blocks in a tablespace are modified to use the crc32 algorithm, the associated tables cannot be read by earlier versions of MySQL.

校验和算法的严格形式会在表空间中遇到有效但不匹配的校验和值时报告错误。建议仅在新实例中使用严格设置，以首次设置表空间。严格设置速度稍快一些，因为它们在磁盘读取期间无需计算所有校验和值。The strict form of a checksum algorithm reports an error if it encounters a valid but non-matching checksum value in a tablespace. It is recommended that you only use strict settings in a new instance, to set up tablespaces for the first time. Strict settings are somewhat faster, because they do not need to compute all checksum values during disk reads.

下表显示了none、innodb和crc32选项值及其严格对应值之间的差异。none、innodb和crc32会将指定类型的校验和值写入每个数据块，但为了兼容性，在读取操作时验证数据块时也接受其他校验值。严格设置也接受有效的校验值，但在遇到有效但不匹配的校验值时会打印错误消息。如果实例中所有InnoDB数据文件都是在相同的innodb_checksum_algorithm值下创建的，使用严格形式可以加快验证速度。The following table shows the difference between the none, innodb, and crc32 option values, and their strict counterparts. none, innodb, and crc32 write the specified type of checksum value into each data block, but for compatibility accept other checksum values when verifying a block during a read operation. Strict settings also accept valid checksum values but print an error message when a valid non-matching checksum value is encountered. Using the strict form can make verification faster if all InnoDB data files in an instance are created under an identical innodb_checksum_algorithm value.

innodb_cmp_per_index_enabled

在信息模式的 INNODB_CMP_PER_INDEX 表中启用每个索引的压缩相关统计信息。由于收集这些统计信息可能开销较大，仅在开发、测试或复制实例上进行与 InnoDB 压缩 表相关的性能调优时启用此选项。Enables per-index compression-related statistics in the Information Schema INNODB_CMP_PER_INDEX table. Because these statistics can be expensive to gather, only enable this option on development, test, or replica instances during performance tuning related to InnoDB compressed tables.

更多信息，请参阅 第 28.4.8 节，“INFORMATION_SCHEMA INNODB_CMP_PER_INDEX 和 INNODB_CMP_PER_INDEX_RESET 表” 和 第 17.9.1.4 节，“运行时监控 InnoDB 表压缩”。For more information, see Section 28.4.8, “The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables”, and Section 17.9.1.4, “Monitoring InnoDB Table Compression at Runtime”.

innodb_commit_concurrency

可以同时 提交 的 线程数。值为 0（默认值）允许任意数量的 事务 同时提交。The number of threads that can commit at the same time. A value of 0 (the default) permits any number of transactions to commit simultaneously.

innodb_commit_concurrency 的值不能在运行时从零更改为非零，或从非零更改为零。值可以从一个非零值更改为另一个非零值。The value of innodb_commit_concurrency cannot be changed at runtime from zero to nonzero or vice versa. The value can be changed from one nonzero value to another.

innodb_compress_debug