MySQL8 中文参考（七十一）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

原文：dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html

17.8.3.2 配置多个缓冲池实例

对于具有多吉字节范围缓冲池的系统，将缓冲池划分为单独的实例可以提高并发性，通过减少不同线程读取和写入缓存页面时的争用。此功能通常适用于具有多吉字节范围缓冲池大小的系统。可以使用innodb_buffer_pool_instances配置选项配置多个缓冲池实例，并且您可能还需要调整innodb_buffer_pool_size的值。

当InnoDB缓冲池很大时，许多数据请求可以通过从内存中检索来满足。您可能会遇到多个线程同时尝试访问缓冲池而导致瓶颈。您可以启用多个缓冲池以最小化此争用。存储在缓冲池中或从缓冲池中读取的每个页面都随机分配给其中一个缓冲池，使用哈希函数。每个缓冲池管理其自己的空闲列表、刷新列表、LRU 列表和所有与缓冲池相关的其他数据结构。在 MySQL 8.0 之前，每个缓冲池都由其自己的缓冲池互斥锁保护。在 MySQL 8.0 及更高版本中，缓冲池互斥锁被几个列表和哈希保护互斥锁所取代，以减少争用。

要启用多个缓冲池实例，请将innodb_buffer_pool_instances配置选项设置为大于 1（默认值）至 64（最大值）。仅当您将innodb_buffer_pool_size设置为 1GB 或更大时，此选项才会生效。您指定的总大小将分配给所有缓冲池。为了获得最佳效率，请指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以便每个缓冲池实例至少为 1GB。

要了解如何修改InnoDB缓冲池大小的信息，请参阅第 17.8.3.1 节，“配置 InnoDB 缓冲池大小”。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-performance-midpoint_insertion.html

17.8.3.3 使缓冲池具有扫描抵抗力

InnoDB不使用严格的 LRU 算法，而是使用一种技术来最小化带入缓冲池但从未再次访问的数据量。目标是确保频繁访问的（“热”）页面保留在缓冲池中，即使预读取和全表扫描带入可能或可能不会在之后访问的新块。

新读取的块被插入 LRU 列表的中间。所有新读取的页面默认情况下插入到 LRU 列表尾部的3/8位置。当它们在缓冲池中第一次被访问时，页面被移动到列表的前端（最近使用的端）。因此，从未被访问的页面永远不会进入 LRU 列表的前部，比严格的 LRU 方法更快地“老化”。这种安排将 LRU 列表分为两个部分，插入点后面的页面被视为“旧”，是 LRU 驱逐的理想受害者。

有关InnoDB缓冲池的内部工作原理和 LRU 算法的具体信息，请参见第 17.5.1 节，“缓冲池”。

您可以控制 LRU 列表中的插入点，并选择InnoDB是否将相同的优化应用于通过表或索引扫描带入缓冲池的块。配置参数innodb_old_blocks_pct控制 LRU 列表中“旧”块的百分比。innodb_old_blocks_pct的默认值为37，对应于原始的固定比率 3/8。值范围为5（缓冲池中的新页面很快就会老化）到95（只有 5%的缓冲池用于热页面，使算法接近熟悉的 LRU 策略）。

通过优化，可以避免缓冲池由于预读取而产生类似的问题，这可以避免由于表格或索引扫描而导致的问题。在这些扫描中，数据页通常会被快速连续访问几次，然后再也不会被访问。配置参数innodb_old_blocks_time指定了在第一次访问页面后的时间窗口（以毫秒为单位），在此期间可以访问该页面而无需将其移动到 LRU 列表的前端（最近使用的端）。innodb_old_blocks_time的默认值为1000。增加此值会使更多的块更有可能从缓冲池中更快地过期。

innodb_old_blocks_pct和innodb_old_blocks_time都可以在 MySQL 选项文件（my.cnf或my.ini）中指定，或者使用SET GLOBAL语句在运行时更改。在运行时更改值需要具有足够权限设置全局系统变量。请参见 Section 7.1.9.1，“系统变量权限”。

为了帮助您评估设置这些参数的效果，SHOW ENGINE INNODB STATUS命令会报告缓冲池的统计信息。详情请参见使用 InnoDB 标准监视器监视缓冲池。

由于这些参数的效果可能根据硬件配置、数据和工作负载的细节而有很大差异，所以在更改这些设置之前，始终要进行基准测试以验证效果，尤其是在任何性能关键或生产环境中。

在混合工作负载中，大部分活动是 OLTP 类型，定期批量报告查询导致大规模扫描时，设置innodb_old_blocks_time的值可以帮助保持正常工作负载的工作集在缓冲池中。

当扫描无法完全适应缓冲池的大型表格时，将innodb_old_blocks_pct设置为一个较小的值，可以避免只读取一次的数据占用缓冲池的大部分空间。例如，设置innodb_old_blocks_pct=5将限制只读取一次的数据占缓冲池的 5%。

当扫描适应内存的小型表格时，在缓冲池内移动页面的开销较小，因此可以将innodb_old_blocks_pct保持在默认值，甚至更高，例如innodb_old_blocks_pct=50。

innodb_old_blocks_time参数的影响比innodb_old_blocks_pct参数更难预测，影响相对较小，并且随着工作负载的变化更大。如果调整innodb_old_blocks_pct未能带来性能改善，建议进行自己的基准测试以确定最佳值。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-performance-read_ahead.html

17.8.3.4 配置 InnoDB 缓冲池预取（预读取）

一个预读取请求是一个异步的 I/O 请求，用于预取缓冲池中多个页面，以期望未来需要这些页面。这些请求一次带入一个 extent 中的所有页面。InnoDB使用两种预读取算法来提高 I/O 性能：

线性预读取是一种技术，根据缓冲池中按顺序访问的页面来预测哪些页面可能很快会被需要。您可以通过调整配置参数innodb_read_ahead_threshold来控制InnoDB何时执行预读取操作，该参数表示触发异步读取请求所需的连续页面访问次数。在添加此参数之前，InnoDB只会在读取当前 extent 的最后一页时计算是否发出整个下一个 extent 的异步预取请求。

配置参数innodb_read_ahead_threshold控制InnoDB在检测连续页面访问模式时的敏感度。如果从一个 extent 中连续读取的页面数量大于或等于innodb_read_ahead_threshold，InnoDB会启动整个后续 extent 的异步预读取操作。innodb_read_ahead_threshold的值可以设置为 0-64 之间的任何值。默认值为 56。值越高，访问模式检查越严格。例如，如果将值设置为 48，InnoDB仅在当前 extent 中连续访问了 48 页时才触发线性预读取请求。如果值为 8，即使在 extent 中连续访问了仅有 8 页，InnoDB也会触发异步预读取。您可以在 MySQL 的配置文件中设置此参数的值，或使用SET GLOBAL语句动态更改该值，这需要足够的权限来设置全局系统变量。参见 Section 7.1.9.1, “System Variable Privileges”。

随机预读是一种技术，根据缓冲池中已经存在的页面，预测哪些页面可能很快就会被需要，而不考虑这些页面的读取顺序。如果在缓冲池中找到来自同一范围的连续 13 个页面，InnoDB会异步发出请求，预取该范围的其余页面。要启用此功能，请将配置变量innodb_random_read_ahead设置为ON。

SHOW ENGINE INNODB STATUS 命令显示统计信息，帮助您评估预读算法的有效性。统计信息包括以下全局状态变量的计数器信息：

Innodb_buffer_pool_read_ahead
Innodb_buffer_pool_read_ahead_evicted
Innodb_buffer_pool_read_ahead_rnd

当微调innodb_random_read_ahead设置时，这些信息可能会有用。

有关 I/O 性能的更多信息，请参阅第 10.5.8 节，“优化 InnoDB 磁盘 I/O” 和第 10.12.1 节，“优化磁盘 I/O”。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-flushing.html

17.8.3.5 配置缓冲池刷新

InnoDB在后台执行某些任务，包括从缓冲池中刷新脏页。脏页是已经被修改但尚未写入磁盘上的数据文件的页面。

在 MySQL 8.0 中，缓冲池刷新由页面清理线程执行。页面清理线程的数量由innodb_page_cleaners变量控制，默认值为 4。但是，如果页面清理线程的数量超过缓冲池实例的数量，innodb_page_cleaners会自动设置为与innodb_buffer_pool_instances相同的值。

当脏页的百分比达到由innodb_max_dirty_pages_pct_lwm变量定义的低水位值时，会启动缓冲池刷新。默认的低水位标记是缓冲池页面的 10%。innodb_max_dirty_pages_pct_lwm值为 0 会禁用这种早期刷新行为。

innodb_max_dirty_pages_pct_lwm阈值的目的是控制缓冲池中的脏页百分比，并防止脏页数量达到由innodb_max_dirty_pages_pct变量定义的阈值，其默认值为 90。如果缓冲池中脏页的百分比达到innodb_max_dirty_pages_pct阈值，InnoDB会积极刷新缓冲池页面。

在配置innodb_max_dirty_pages_pct_lwm时，该值应始终低于innodb_max_dirty_pages_pct值。

其他变量允许对缓冲池刷新行为进行微调：

innodb_flush_neighbors变量定义了是否刷新缓冲池中的页面也会刷新同一范围内的其他脏页。
- 默认设置为 0 会禁用innodb_flush_neighbors。同一范围内的脏页不会被刷新。这个设置适用于非旋转存储（SSD）设备，其中寻道时间不是一个重要因素。
- 设置为 1 会刷新同一范围内的连续脏页。
- 设置为 2 会刷新同一范围内的脏页。
当表数据存储在传统的 HDD 存储设备上时，一次刷新相邻页面可以减少 I/O 开销（主要是磁盘寻道操作）相对于在不同时间刷新单个页面。对于存储在 SSD 上的表数据，寻道时间不是一个重要因素，您可以禁用此设置以分散写操作。
innodb_lru_scan_depth变量指定了每个缓冲池实例中，页面清理线程扫描缓冲池 LRU 列表以查找要刷新的脏页的深度。这是一个后台操作，由页面清理线程每秒执行一次。

一般来说，比默认值小的设置对大多数工作负载都是合适的。如果值显著高于必要值，可能会影响性能。只有在典型工作负载下有多余的 I/O 容量时才考虑增加该值。相反，如果写入密集型工作负载使您的 I/O 容量饱和，减少该值，特别是在大缓冲池的情况下。

在调整innodb_lru_scan_depth时，从一个较低值开始，并将设置向上配置，目的是很少看到零空闲页面。此外，在更改缓冲池实例数量时，考虑调整innodb_lru_scan_depth，因为innodb_lru_scan_depth * innodb_buffer_pool_instances定义了每秒页清理线程执行的工作量。

innodb_flush_neighbors和innodb_lru_scan_depth变量主要用于写入密集型工作负载。在大量 DML 活动中，如果刷新不够积极，刷新可能会滞后，或者如果刷新过于积极，磁盘写入可能会饱和 I/O 容量。理想的设置取决于您的工作负载、数据访问模式和存储配置（例如，数据存储在 HDD 还是 SSD 设备上）。

自适应刷新

InnoDB使用自适应刷新算法动态调整刷新速率，根据重做日志生成的速度和当前刷新速率。其目的是通过确保刷新活动跟上当前工作负载的步伐来平滑整体性能。自动调整刷新速率有助于避免由于缓冲池刷新导致的 I/O 活动突然下降，从而影响了用于普通读写活动的 I/O 容量。

尖锐的检查点通常与产生大量重做条目的写入密集型工作负载相关联，例如可能导致吞吐量突然变化。尖锐的检查点发生在InnoDB想要重用日志文件的一部分时。在这样做之前，必须刷新该日志文件部分中具有重做条目的所有脏页。如果日志文件变满，将发生尖锐的检查点，导致暂时的吞吐量降低。即使未达到innodb_max_dirty_pages_pct阈值，也可能发生这种情况。

自适应刷新算法通过跟踪缓冲池中脏页的数量以及重做日志记录生成的速率来避免这种情况。根据这些信息，它决定每秒从缓冲池中刷新多少脏页，从而使其能够管理工作负载的突然变化。

innodb_adaptive_flushing_lwm变量定义了重做日志容量的低水位标记。当超过该阈值时，即使innodb_adaptive_flushing变量被禁用，自适应刷新也会被启用。

内部基准测试显示，该算法不仅可以随时间保持吞吐量，还可以显著提高总体吞吐量。然而，自适应刷新可能会显著影响工作负载的 I/O 模式，并且在所有情况下可能并不适用。当重做日志面临填满的危险时，它会带来最大的好处。如果自适应刷新不适合您的工作负载特征，您可以禁用它。自适应刷新由innodb_adaptive_flushing变量控制，默认情况下启用。

innodb_flushing_avg_loops定义了InnoDB保持先前计算的刷新状态快照的迭代次数，控制自适应刷新对前台工作负载变化的快速响应。较高的innodb_flushing_avg_loops值意味着InnoDB会保持先前计算的快照时间更长，因此自适应刷新的响应速度更慢。设置较高的值时，重要的是确保重做日志利用率不会达到 75%（异步刷新开始的硬编码限制），并且innodb_max_dirty_pages_pct阈值保持脏页数量适合工作负载的水平。

对于工作负载一致、日志文件大小较大（innodb_log_file_size）且不会达到 75% 日志空间利用率的系统，应该使用较高的 innodb_flushing_avg_loops 值，以尽可能平滑地进行刷新。对于负载波动极大或日志文件提供的空间不多的系统，较小的值可以使刷新紧密跟踪工作负载变化，并有助于避免达到 75% 日志空间利用率。

请注意，如果刷新落后，缓冲池刷新速率可能超过 InnoDB 可用的 I/O 容量，即由 innodb_io_capacity 设置定义。innodb_io_capacity_max 值在这种情况下定义了 I/O 容量的上限，以防止 I/O 活动的激增消耗服务器的整个 I/O 容量。

innodb_io_capacity 设置适用于所有缓冲池实例。当脏页被刷新时，I/O 容量在缓冲池实例之间均等分配。

限制空闲期间的缓冲区刷新

截至 MySQL 8.0.18 版本，您可以使用 innodb_idle_flush_pct 变量来限制在空闲期间缓冲池刷新的速率，空闲期间是指数据库页面未被修改的时间段。innodb_idle_flush_pct 值是 innodb_io_capacity 设置的百分比，该设置定义了每秒可用于 InnoDB 的 I/O 操作数。默认的 innodb_idle_flush_pct 值为 100，即 innodb_io_capacity 设置的 100%。为了在空闲期间限制刷新，请定义一个小于 100 的 innodb_idle_flush_pct 值。

在空闲期间限制页面刷新可以延长固态存储设备的寿命。限制空闲期间页面刷新的副作用可能包括在长时间空闲期之后更长的关闭时间，以及在发生服务器故障时更长的恢复时间。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-preload-buffer-pool.html

17.8.3.6 保存和恢复缓冲池状态

为了在服务器重新启动后减少热身期，InnoDB 在服务器关闭时保存每个缓冲池中最近使用的页面的一定百分比，并在服务器启动时恢复这些页面。存储的最近使用页面的百分比由 innodb_buffer_pool_dump_pct 配置选项定义。

重启繁忙的服务器后，通常会有一个热身期，吞吐量稳步增加，因为缓冲池中的磁盘页被重新加载到内存中（当相同数据被查询、更新等时）。在启动时恢复缓冲池的能力通过重新加载重启前在缓冲池中的磁盘页，缩短了热身期，而不是等待 DML 操作访问相应的行。此外，I/O 请求可以批量执行，使整体 I/O 更快。页面加载在后台进行，不会延迟数据库启动。

除了在关闭时保存缓冲池状态并在启动时恢复它之外，您还可以在服务器运行时的任何时候保存和恢复缓冲池状态。例如，在稳定的工作负载下达到稳定吞吐量后，您可以保存缓冲池的状态。您还可以在运行报告或将数据页带入缓冲池的维护作业后，或在运行其他非典型工作负载后，恢复先前的缓冲池状态。

即使缓冲池的大小可能达到几个千兆字节，但相比之下，InnoDB 保存到磁盘的缓冲池数据非常小。只保存用于定位适当页面的表空间 ID 和页面 ID。此信息来自 INNODB_BUFFER_PAGE_LRU INFORMATION_SCHEMA 表。默认情况下，表空间 ID 和页面 ID 数据保存在名为 ib_buffer_pool 的文件中，该文件保存在 InnoDB 数据目录中。文件名和位置可以使用 innodb_buffer_pool_filename 配置参数进行修改。

因为数据像常规数据库操作一样在缓冲池中缓存并被淘汰，所以如果磁盘页最近被更新，或者 DML 操作涉及尚未加载的数据，都不会有问题。加载机制会跳过不再存在的请求页面。

底层机制涉及一个后台线程，用于执行转储和加载操作。

来自压缩表的磁盘页面以压缩形式加载到缓冲池中。在进行 DML 操作期间访问页面内容时，页面将像往常一样解压缩。因为解压缩页面是一个消耗 CPU 的过程，所以为了并发性能更高，最好在连接线程中执行操作，而不是在执行缓冲池恢复操作的单个线程中执行操作。

有关保存和恢复缓冲池状态的操作描述在以下主题中：

配置缓冲池页面转储百分比
在关闭时保存缓冲池状态并在启动时恢复
在线保存和恢复缓冲池状态
显示缓冲池转储进度
显示缓冲池加载进度
中止缓冲池加载操作
使用性能模式监视缓冲池加载进度

配置缓冲池页面转储百分比

在从缓冲池转储页面之前，您可以通过设置innodb_buffer_pool_dump_pct选项来配置要转储的最近使用的缓冲池页面的百分比。如果您计划在服务器运行时转储缓冲池页面，可以动态配置该选项：

SET GLOBAL innodb_buffer_pool_dump_pct=40;

如果您计划在服务器关闭时转储缓冲池页面，请在配置文件中设置innodb_buffer_pool_dump_pct。

[mysqld]
innodb_buffer_pool_dump_pct=40

innodb_buffer_pool_dump_pct的默认值为 25（转储最近使用的页面的 25%）。

在关闭时保存缓冲池状态并在启动时恢复

在关闭服务器之前，请在关闭服务器之前发出以下语句以保存缓冲池状态：

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

innodb_buffer_pool_dump_at_shutdown默认启用。

在服务器启动时恢复缓冲池状态，请在启动服务器时指定--innodb-buffer-pool-load-at-startup选项：

mysqld --innodb-buffer-pool-load-at-startup=ON;

innodb_buffer_pool_load_at_startup默认启用。

在线保存和恢复缓冲池状态

要在 MySQL 服务器运行时保存缓冲池状态，请执行以下语句：

SET GLOBAL innodb_buffer_pool_dump_now=ON;

在 MySQL 运行时恢复缓冲池状态，请执行以下语句：

SET GLOBAL innodb_buffer_pool_load_now=ON;

显示缓冲池转储进度

要在将缓冲池状态保存到磁盘时显示进度，请执行以下语句：

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

如果操作尚未开始，则返回“未开始”。如果操作已完成，则打印完成时间（例如完成于 110505 12:18:02）。如果操作正在进行中，则提供状态信息（例如正在转储缓冲池 5/7，页码 237/2873）。

显示缓冲池加载进度

要在加载缓冲池时显示进度，请执行以下语句：

SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

如果操作尚未开始，则返回“未开始”。如果操作已完成，则打印完成时间（例如完成于 110505 12:23:24）。如果操作正在进行中，则提供状态信息（例如已加载 123/22301 页）。

中止缓冲池加载操作

要中止缓冲池加载操作，请执行以下语句：

SET GLOBAL innodb_buffer_pool_load_abort=ON;

使用性能模式监视缓冲池加载进度

您可以使用性能模式监视缓冲池加载进度。

以下示例演示了如何启用stage/innodb/buffer pool load阶段事件工具和相关的消费者表以监视缓冲池加载进度。

有关本示例中使用的缓冲池转储和加载过程的信息，请参阅第 17.8.3.6 节，“保存和恢复缓冲池状态”。有关性能模式阶段事件工具和相关消费者的信息，请参阅第 29.12.5 节，“性能模式阶段事件表”。

启用stage/innodb/buffer pool load工具：

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' 
       WHERE NAME LIKE 'stage/innodb/buffer%';

启用阶段事件消费者表，包括events_stages_current、events_stages_history和events_stages_history_long。

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
       WHERE NAME LIKE '%stages%';

通过启用innodb_buffer_pool_dump_now来转储当前缓冲池状态。
```
mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
```

检查缓冲池转储状态以确保操作已完成。

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G
*************************** 1\. row ***************************
Variable_name: Innodb_buffer_pool_dump_status
        Value: Buffer pool(s) dump completed at 150202 16:38:58

通过启用innodb_buffer_pool_load_now来加载缓冲池：
```
mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;
```

通过查询性能模式events_stages_current表，检查缓冲池加载操作的当前状态。WORK_COMPLETED列显示加载的缓冲池页面数。WORK_ESTIMATED列提供剩余工作的估计，以页面为单位。

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
       FROM performance_schema.events_stages_current;
+-------------------------------+----------------+----------------+
| EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
+-------------------------------+----------------+----------------+
| stage/innodb/buffer pool load |           5353 |           7167 |
+-------------------------------+----------------+----------------+

如果缓冲池加载操作已完成，则events_stages_current表将返回一个空集。在这种情况下，您可以查询events_stages_history表查看已完成事件的数据。例如：

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED 
       FROM performance_schema.events_stages_history;
+-------------------------------+----------------+----------------+
| EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
+-------------------------------+----------------+----------------+
| stage/innodb/buffer pool load |           7167 |           7167 |
+-------------------------------+----------------+----------------+

注意

在启动时使用innodb_buffer_pool_load_at_startup加载缓冲池时，您还可以使用性能模式监视缓冲池加载进度。在这种情况下，必须在启动时启用stage/innodb/buffer pool load工具和相关消费者。有关更多信息，请参见第 29.3 节，“性能模式启动配置”。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-in-core-file.html

17.8.3.7 排除核心文件中的缓冲池页面

核心文件记录了运行进程的状态和内存映像。由于缓冲池位于主内存中，并且运行进程的内存映像被转储到核心文件中，当mysqld进程死机时，具有大缓冲池的系统可能会产生大型核心文件。

大型核心文件可能会带来许多问题，包括编写它们所需的时间、它们占用的磁盘空间以及传输大文件所面临的挑战。

为了减小核心文件大小，您可以禁用innodb_buffer_pool_in_core_file变量，以在核心转储中省略缓冲池页面。innodb_buffer_pool_in_core_file变量在 MySQL 8.0.14 中引入，并默认启用。

如果您担心将数据库页面转储到可能在组织内外共享用于调试目的的核心文件中，从安全角度考虑��除缓冲池页面可能也是值得的。

注意

当mysqld进程死机时，访问缓冲池页面中的数据可能在某些调试场景中很有益。如果不确定是否包含或排除缓冲池页面，请咨询 MySQL 支持。

禁用innodb_buffer_pool_in_core_file仅在启用core_file变量且操作系统支持MADV_DONTDUMP非 POSIX 扩展到madvise()系统调用时生效，该扩展在 Linux 3.4 及更高版本中受支持。MADV_DONTDUMP扩展导致指定范围内的页面被排除在核心转储之外。

假设操作系统支持MADV_DONTDUMP扩展，请使用--core-file和--innodb-buffer-pool-in-core-file=OFF选项启动服务器，以生成不包含缓冲池页面的核心文件。

$> mysqld --core-file --innodb-buffer-pool-in-core-file=OFF

core_file变量是只读的，默认情况下禁用。通过在启动时指定--core-file选项来启用它。innodb_buffer_pool_in_core_file变量是动态的。它可以在启动时指定，也可以使用SET语句在运行时配置。

mysql> SET GLOBAL innodb_buffer_pool_in_core_file=OFF;

如果禁用了innodb_buffer_pool_in_core_file变量，但操作系统不支持MADV_DONTDUMP，或者madvise()失败，则会向 MySQL 服务器错误日志写入警告，并禁用core_file变量以防止意外包含缓冲池页的核心文件写入。如果只读的core_file变量被禁用，则必须重新启动服务器才能再次启用它。

以下表格显示了确定是否生成核心文件以及是否包含缓冲池页的配置和MADV_DONTDUMP支持方案。

表 17.4 核心文件配置方案

`core_file`变量	`innodb_buffer_pool_in_core_file`变量	madvise() MADV_DONTDUMP 支持	结果
关（默认）	与结果无关	与结果无关	不生成核心文件
开	开（默认）	与结果无关	核心文件生成时包含缓冲池页
开	关	是	生成的核心文件不包含缓冲池页
开	关	否	不生成核心文件，`core_file`被禁用，并且向服务器错误日志写入警告

通过禁用innodb_buffer_pool_in_core_file变量来减小核心文件大小取决于缓冲池的大小，但也受到InnoDB页面大小的影响。较小的页面大小意味着相同数据量需要更多的页面，而更多的页面意味着更多的页面元数据。以下表格提供了对于具有不同页面大小的 1GB 缓冲池可能看到的大小减小示例。

表 17.5 包含和不包含缓冲池页的核心文件大小

`innodb_page_size`设置	包含缓冲池页（`innodb_buffer_pool_in_core_file=ON`）	不包含缓冲池页（`innodb_buffer_pool_in_core_file=OFF`）
4KB	2.1GB	0.9GB
64KB	1.7GB	0.7GB

17.8.4 为 InnoDB 配置线程并发性

原文：dev.mysql.com/doc/refman/8.0/en/innodb-performance-thread_concurrency.html

InnoDB使用操作系统线程来处理来自用户事务的请求。（事务在提交或回滚之前可能向InnoDB发出许多请求。）在具有多核处理器的现代操作系统和服务器上，其中上下文切换效率高，大多数工作负载在没有对并发线程数量设置任何限制的情况下运行良好。

在需要最小化线程之间上下文切换的情况下，InnoDB可以使用多种技术来限制同时执行的操作系统线程数量（因此同时处理的请求数量）。当InnoDB从用户会话接收到一个新请求时，如果同时执行的线程数量达到预定义的限制，新请求会在再次尝试之前短暂休眠一段时间。等待锁的线程不计入同时执行的线程数量。

您可以通过设置配置参数innodb_thread_concurrency来限制并发线程的数量。一旦执行线程数量达到此限制，额外的线程会在被放入队列之前休眠一段由配置参数innodb_thread_sleep_delay设置的微秒数。

您可以将配置选项innodb_adaptive_max_sleep_delay设置为您允许的最高值，以及InnoDB会根据当前线程调度活动自动调整innodb_thread_sleep_delay的值。这种动态调整有助于在线程调度机制在系统轻载时和在接近满负荷运行时平稳工作。

在各个 MySQL 和InnoDB的发布版本中，innodb_thread_concurrency的默认值和隐含的并发线程数量限制已经发生了变化。innodb_thread_concurrency的默认值为0，因此默认情况下没有对同时执行的线程数量设置限制。

InnoDB只有在并发线程数量有限时才会使线程进入睡眠状态。当线程数量没有限制时，所有线程都会平等竞争调度。也就是说，如果innodb_thread_concurrency为0，则innodb_thread_sleep_delay的值会被忽略。

当线程数量有限时（当innodb_thread_concurrency > 0 时），InnoDB通过允许在执行单个 SQL 语句期间发出的多个请求进入InnoDB来减少上下文切换开销，而无需遵守innodb_thread_concurrency设置的限制。由于一个 SQL 语句（如连接）可能包含InnoDB内的多个行操作，InnoDB会分配一定数量的“票据”，允许线程以最小的开销重复调度。

当一个新的 SQL 语句开始执行时，线程没有任何票据，必须遵守innodb_thread_concurrency。一旦线程有资格进入InnoDB，它将被分配一定数量的票据，用于随后进入InnoDB执行行操作。如果票据用完，线程将被驱逐，并且会再次观察innodb_thread_concurrency，这可能会将线程重新放入等待线程的先进先出队列中。当线程再次有资格进入InnoDB时，将再次分配票据。分配的票据数量由全局选项innodb_concurrency_tickets指定，默认值为 5000。等待锁的线程在锁可用时会获得一个票据。

这些变量的正确值取决于您的环境和工作负载。尝试一系列不同的值，以确定哪个值适用于您的应用程序。在限制并发执行线程数量之前，请查看可能改善多核和多处理器计算机上InnoDB性能的配置选项，例如innodb_adaptive_hash_index。

关于 MySQL 线程处理的一般性性能信息，请参见第 7.1.12.1 节，“连接接口”。

17.8.5 配置后台 InnoDB I/O 线程的数量

原文：dev.mysql.com/doc/refman/8.0/en/innodb-performance-multiple_io_threads.html

InnoDB使用后台线程来处理各种类型的 I/O 请求。您可以使用innodb_read_io_threads和innodb_write_io_threads配置参数来配置服务数据页读取和写入 I/O 的后台线程数量。这些参数分别表示用于读取和写入请求的后台线程数量。它们在所有支持的平台上都有效。您可以在 MySQL 选项文件（my.cnf或my.ini）中为这些参数设置值；您不能动态更改值。这些参数的默认值为4，允许的值范围为1-64。

这些配置选项的目的是使InnoDB在高端系统上更具可扩展性。每个后台线程可以处理多达 256 个待处理的 I/O 请求。后台 I/O 的一个主要来源是预读取请求。InnoDB试图平衡传入请求的负载，使大多数后台线程平均分担工作。InnoDB还尝试将来自同一范围的读取请求分配给同一线程，以增加合并请求的机会。如果您拥有高端 I/O 子系统，并且在SHOW ENGINE INNODB STATUS输出中看到超过 64 × innodb_read_io_threads个待处理读取请求，您可以通过增加innodb_read_io_threads的值来提高性能。

在 Linux 系统上，默认情况下，InnoDB使用异步 I/O 子系统执行数据文件页的预读取和写入请求，这改变了InnoDB后台线程处理这些类型 I/O 请求的方式。有关更多信息，请参阅第 17.8.6 节，“在 Linux 上使用异步 I/O”。

有关InnoDB I/O 性能的更多信息，请参阅第 10.5.8 节，“优化 InnoDB 磁盘 I/O”。

17.8.6 在 Linux 上使用异步 I/O

原文：dev.mysql.com/doc/refman/8.0/en/innodb-linux-native-aio.html

InnoDB在 Linux 上使用异步 I/O 子系统（本机 AIO）执行数据文件页的预读和写请求。此行为由innodb_use_native_aio配置选项控制，仅适用于 Linux 系统，并且默认启用。在其他类 Unix 系统上，InnoDB仅使用同步 I/O。从历史上看，InnoDB仅在 Windows 系统上使用异步 I/O。在 Linux 上使用异步 I/O 子系统需要libaio库。

使用同步 I/O 时，查询线程排队 I/O 请求，InnoDB后台线程逐个检索排队的请求，为每个请求发出同步 I/O 调用。当 I/O 请求完成并且 I/O 调用返回时，处理请求的InnoDB后台线程调用 I/O 完成例程并返回以处理下一个请求。可以并行处理的请求数量为*n，其中n*是InnoDB后台线程的数量。InnoDB后台线程的数量由innodb_read_io_threads和innodb_write_io_threads控制。参见第 17.8.5 节，“配置后台 InnoDB I/O 线程数量”。

使用本机 AIO，查询线程直接将 I/O 请求分派给操作系统，从而消除了后台线程数量的限制。InnoDB后台线程等待 I/O 事件来标志完成的请求。当请求完成时，后台线程调用 I/O 完成例程并恢复等待 I/O 事件。

本机 AIO 的优势在于对于通常在SHOW ENGINE INNODB STATUS\G输出中显示许多待处理读/写操作的重度 I/O 绑定系统的可伸缩性。使用本机 AIO 时的并行处理增加意味着 I/O 性能受到 I/O 调度程序类型或磁盘阵列控制器属性的更大影响。

对于重度 I/O 绑定系统，本机 AIO 的一个潜在缺点是无法控制一次性发送到操作系统的 I/O 写请求数量。在某些情况下，一次性发送太多 I/O 写请求到操作系统进行并行处理可能导致 I/O 读取饥饿，这取决于 I/O 活动量和系统能力。

如果操作系统中异步 I/O 子系统出现问题导致InnoDB无法启动，您可以使用innodb_use_native_aio=0选项启动服务器。在启动过程中，如果InnoDB检测到潜在问题，比如tmpdir位置、tmpfs文件系统和 Linux 内核的组合不支持在tmpfs上进行异步 I/O，该选项也可能会被自动禁用。

17.8.7 配置 InnoDB I/O 容量

原文：dev.mysql.com/doc/refman/8.0/en/innodb-configuring-io-capacity.html

InnoDB主线程和其他线程在后台执行各种任务，其中大部分与 I/O 相关，例如从缓冲池中刷新脏页并将更改从更改缓冲区写入适当的辅助索引。InnoDB试图以不会对服务器正常工作产生不利影响的方式执行这些任务。它试图估计可用的 I/O 带宽，并调整其活动以利用可用容量。

innodb_io_capacity变量定义了InnoDB可用的整体 I/O 容量。它应该设置为系统每秒可以执行的 I/O 操作数（IOPS）的大致数量。当设置了innodb_io_capacity时，InnoDB根据设置的值估计可用于后台任务的 I/O 带宽。

你可以将innodb_io_capacity设置为 100 或更高的值。默认值为200。通常，约 100 左右的值适用于消费级存储设备，例如转速为 7200 转/分的硬盘。速度更快的硬盘、RAID 配置和固态硬盘（SSD）受益于更高的值。

理想情况下，保持设置尽可能低，但不要太低以至于后台活动落后。如果值设置过高，数据会从缓冲池和更改缓冲区中被过快地移除，使得缓存无法提供显著的好处。对于能够处理更高 I/O 速率的繁忙系统，你可以设置更高的值来帮助服务器处理与高频率行更改相关的后台维护工作。通常，你可以根据用于InnoDB I/O 的驱动器数量的函数增加该值。例如，你可以在使用多个磁盘或 SSD 的系统上增加该值。

默认设置的 200 通常对于低端 SSD 足够。对于高端总线连接的 SSD，考虑更高的设置，例如 1000。对于使用单独的 5400 转/分或 7200 转/分驱动器的系统，你可能会将值降低到 100，这代表了每秒 I/O 操作（IOPS）的估计比例，适用于可以执行约 100 IOPS 的旧一代磁盘驱动器。

虽然你可以指定一个很高的值，比如一百万，但实际上这样大的值很少有好处。通常，建议不要将值设定为 20000 以上，除非你确定较低的值对你的工作负载不足。

在调整innodb_io_capacity时考虑写入工作负载。具有大量写入工作负载的系统可能会受益于更高的设置。对于写入工作负载较小的系统，较低的设置可能已经足够。

innodb_io_capacity设置不是每个缓冲池实例的设置。可用的 I/O 容量在刷新活动中均匀分配给缓冲池实例。

您可以在 MySQL 选项文件（my.cnf或my.ini）中设置innodb_io_capacity的值，或者使用SET GLOBAL语句在运行时修改它，这需要足够的权限来设置全局系统变量。请参阅第 7.1.9.1 节，“系统变量权限”。

在检查点忽略 I/O 容量

innodb_flush_sync变量默认启用，导致在发生 I/O 活动突发时，会忽略innodb_io_capacity设置，这种活动发生在检查点。要遵守innodb_io_capacity设置定义的 I/O 速率，请禁用innodb_flush_sync。

您可以在 MySQL 选项文件（my.cnf或my.ini）中设置innodb_flush_sync的值，或者使用SET GLOBAL语句在运行时修改它，这需要足够的权限来设置全局系统变量。请参阅第 7.1.9.1 节，“系统变量权限”。

配置 I/O 容量最大值

如果刷新活动落后，InnoDB可以以比innodb_io_capacity变量定义的更高的 IOPS（每秒 I/O 操作数）更积极地刷新。innodb_io_capacity_max变量定义了InnoDB在这种情况下执行的后台任务的最大 IOPS 数量。

如果在启动时指定了innodb_io_capacity设置，但未为innodb_io_capacity_max指定值，则innodb_io_capacity_max默认为innodb_io_capacity值的两倍或 2000，以较大值为准。

在配置innodb_io_capacity_max时，通常将innodb_io_capacity的两倍作为起点是一个不错的选择。默认值为 2000 适用于使用 SSD 或多个常规磁盘驱动器的工作负载。对于不使用 SSD 或多个磁盘驱动器的工作负载，设置为 2000 可能过高，可能会导致过多的刷新。对于单个常规磁盘驱动器，建议设置在 200 到 400 之间。对于高端、总线连接的 SSD，考虑设置更高的值，如 2500。与innodb_io_capacity设置一样，保持设置尽可能低，但不要太低以至于InnoDB无法足够扩展 IOPS 的速率超过innodb_io_capacity设置。

在调整innodb_io_capacity_max时，请考虑写入工作负载。具有大量写入工作负载的系统可能会受益于更高的设置。对于具有较小写入工作负载的系统，较低的设置可能足够。

innodb_io_capacity_max不能设置为低于innodb_io_capacity值。

使用SET语句将innodb_io_capacity_max设置为DEFAULT（SET GLOBAL innodb_io_capacity_max=DEFAULT）将innodb_io_capacity_max设置为最大值。

innodb_io_capacity_max 限制适用于所有缓冲池实例。这不是每个缓冲池实例的设置。

17.8.8 配置自旋锁轮询

原文：dev.mysql.com/doc/refman/8.0/en/innodb-performance-spin_lock_polling.html

InnoDB 互斥锁和读写锁通常保留在短时间间隔内。在多核系统上，一个线程在睡眠之前连续检查是否可以在一段时间内获取互斥锁或读写锁可能更有效。如果在此期间互斥锁或读写锁变为可用，线程可以立即继续，在同一时间片内。然而，多个线程频繁轮询共享对象（如互斥锁或读写锁）可能导致“缓存乒乓”，这会导致处理器使彼此的缓存部分失效。InnoDB通过强制在轮询活动之间引入随机延迟来最小化此问题。随机延迟实现为自旋等待循环。

自旋等待循环的持续时间取决于循环中发生的 PAUSE 指令数量。该数字是通过随机选择一个整数，范围从 0 到innodb_spin_wait_delay值，然后将该值乘以 50 来生成的。（在 MySQL 8.0.16 之前，乘数值 50 是硬编码的，在此之后可配置。）例如，对于innodb_spin_wait_delay设置为 6，会随机选择以下范围内的一个整数：

{0,1,2,3,4,5}

所选整数乘以 50，得到六个可能的 PAUSE 指令值之一：

{0,50,100,150,200,250}

对于这组值，250 是自旋等待循环中可能发生的 PAUSE 指令的最大数量。当innodb_spin_wait_delay设置为 5 时，会得到一组五个可能的值{0,50,100,150,200}，其中 200 是 PAUSE 指令的最大数量，依此类推。这样，innodb_spin_wait_delay设置控制着自旋锁轮询之间的最大延迟。

在所有处理器核心共享快速缓存内存的系统上，您可以通过设置innodb_spin_wait_delay=0来减少最大延迟或完全禁用忙等待循环。在具有多个处理器芯片的系统上，缓存失效的影响可能更显著，您可能需要增加最大延迟。

在 100MHz 奔腾时代，一个innodb_spin_wait_delay单位被校准为相当于一微秒。那个时间等价性并不成立，但PAUSE指令持续时间相对于其他 CPU 指令的处理器周期保持相对稳定，直到 Skylake 处理器的引入，这些处理器具有相对较长的PAUSE指令。innodb_spin_wait_pause_multiplier变量在 MySQL 8.0.16 中引入，以提供一种解决PAUSE指令持续时间差异的方法。

innodb_spin_wait_pause_multiplier变量控制PAUSE指令值的大小。例如，假设innodb_spin_wait_delay设置为 6，将innodb_spin_wait_pause_multiplier值从 50（默认值和以前硬编码值）减少到 5 会生成一组较小的PAUSE指令值：

{0,5,10,15,20,25}

增加或减少PAUSE指令值的能力允许对不同处理器架构进行微调InnoDB。较小的PAUSE指令值适用于具有相对较长PAUSE指令的处理器架构，例如。

innodb_spin_wait_delay和innodb_spin_wait_pause_multiplier变量是动态的。它们可以在 MySQL 选项文件中指定，或者使用SET GLOBAL语句在运行时进行修改。在运行时修改变量需要足够设置全局系统变量的权限。参见第 7.1.9.1 节，“系统变量权限”。

17.8.9 清理配置

原文：dev.mysql.com/doc/refman/8.0/en/innodb-purge-configuration.html

当您使用 SQL 语句删除行时，InnoDB 不会立即从数据库中物理删除行。只有在 InnoDB 丢弃为删除编写的撤销日志记录时，行及其索引记录才会被物理删除。此删除操作仅在行不再需要用于多版本并发控制（MVCC）或回滚后发生，称为清理。

清理定期运行。它解析并处理来自历史列表的撤销日志页，历史列表是由 InnoDB 事务系统维护的已提交事务的撤销日志页列表。清理在处理完撤销日志页后会释放它们。

配置清理线程

清理操作由一个或多个清理线程在后台执行。清理线程的数量由innodb_purge_threads 变量控制。默认值为 4。

如果 DML 操作集中在单个表上，则该表的清理操作由单个清理线程执行，这可能导致清理操作减慢、清理延迟增加，并且如果 DML 操作涉及大对象值，则表空间文件大小会增加。从 MySQL 8.0.26 开始，如果超过了innodb_max_purge_lag 设置，清理工作会自动在可用的清理线程之间重新分配。在这种情况下，过多的活动清理线程可能会与用户线程发生争用，因此请相应地管理innodb_purge_threads 设置。innodb_max_purge_lag 变量默认设置为 0，这意味着默认情况下没有最大清理延迟。

如果 DML 操作集中在少数表上，请将innodb_purge_threads 设置较低，以便线程不会因争夺对繁忙表的访问而相互竞争。如果 DML 操作分布在许多表上，请考虑设置更高的innodb_purge_threads。清理线程的最大数量为 32。

innodb_purge_threads 设置是允许的最大清理线程数。清理系统会自动调整使用的清理线程数。

配置清理批量大小

innodb_purge_batch_size变量定义了清除器在一个批处理中解析和处理的撤销日志页数。默认值为 300。在多线程清除配置中，协调员清除线程将innodb_purge_batch_size除以innodb_purge_threads，并将该数量的页分配给每个清除线程。

清除系统还释放不再需要的撤销日志页。它每 128 次迭代通过撤销日志这样做。除了定义在批处理中解析和处理的撤销日志页数外，innodb_purge_batch_size变量还定义了在每 128 次迭代中清除系统释放的撤销日志页数。

innodb_purge_batch_size变量用于高级性能调优和实验。大多数用户不需要更改innodb_purge_batch_size的默认值。

配置最大清除延迟

innodb_max_purge_lag变量定义了期望的最大清除延迟。当清除延迟超过innodb_max_purge_lag阈值时，将对INSERT、UPDATE和DELETE操作施加延迟，以便清除操作赶上。默认值为 0，表示没有最大清除延迟和延迟。

InnoDB事务系统维护一个由UPDATE或DELETE操作删除标记的索引记录的事务列表。列表的长度即为清除延迟。在 MySQL 8.0.14 之前，清除延迟延迟是通过以下公式计算的，结果是最小延迟为 5000 微秒：

(purge lag/innodb_max_purge_lag - 0.5) * 10000

从 MySQL 8.0.14 开始，清除延迟延迟是通过以下修订后的公式计算的，将最小延迟减少到 5 微秒。5 微秒的延迟对于现代系统更为合适。

(purge_lag/innodb_max_purge_lag - 0.9995) * 10000

延迟是在清除批处理的开始时计算的。

对于有问题的工作负载，典型的innodb_max_purge_lag设置可能是 1000000（100 万），假设事务很小，只有 100 字节大小，并且可以有 100MB 的未清除表行。

清除延迟显示为TRANSACTIONS部分中的History list length值，在SHOW ENGINE INNODB STATUS输出中。

mysql> SHOW ENGINE INNODB STATUS;
...
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
History list length 20

History list length 通常是一个较低的值，通常少于几千，但是写入密集的工作负载或长时间运行的事务可能会导致其增加，即使是只读事务也可能会增加。长时间运行的事务可能会导致 History list length 增加的原因是，在诸如 REPEATABLE READ 这样的一致性读事务隔离级别下，事务必须返回与创建该事务的读视图时相同的结果。因此，InnoDB 多版本并发控制（MVCC）系统必须保留数据的副本在撤销日志中，直到所有依赖于该数据的事务完成。以下是可能导致 History list length 增加的长时间运行事务的示例：

当存在大量并发的 DML 时，执行一个使用 --single-transaction 选项的 mysqldump 操作。
在禁用 autocommit 后运行 SELECT 查询，并忘记发出显式的 COMMIT 或 ROLLBACK。

为了防止在极端情况下出现过大的清理延迟，您可以通过设置 innodb_max_purge_lag_delay 变量来限制延迟。innodb_max_purge_lag_delay 变量指定了当超过 innodb_max_purge_lag 阈值时施加的延迟的最大延迟时间（以微秒为单位）。指定的 innodb_max_purge_lag_delay 值是由 innodb_max_purge_lag 公式计算的延迟时间的上限。

清理和撤销表空间截断

清理系统还负责截断撤销表空间。您可以配置 innodb_purge_rseg_truncate_frequency 变量来控制清理系统查找要截断的撤销表空间的频率。有关更多信息，请参见截断撤销表空间。

17.8.10 为 InnoDB 配置优化器统计信息

原文：dev.mysql.com/doc/refman/8.0/en/innodb-performance-optimizer-statistics.html

17.8.10.1 配置持久性优化器统计参数

17.8.10.2 配置非持久性优化器统计参数

17.8.10.3 为 InnoDB 表估算 ANALYZE TABLE 复杂度

本节描述了如何为InnoDB表配置持久性和非持久性优化器统计信息。

持久性优化器统计信息在服务器重启后保留，可以实现更高的计划稳定性和更一致的查询性能。持久性优化器统计信息还提供了以下额外的控制和灵活性：

你可以使用innodb_stats_auto_recalc配置选项来控制在表发生重大更改后是否自动更新统计信息。
你可以在CREATE TABLE和ALTER TABLE语句中使用STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句来为单个表配置优化器统计信息。
你可以在mysql.innodb_table_stats和mysql.innodb_index_stats表中查询优化器统计数据。
你可以查看mysql.innodb_table_stats和mysql.innodb_index_stats表的last_update列，以查看统计信息上次更新的时间。
你可以手动修改mysql.innodb_table_stats和mysql.innodb_index_stats表，以强制执行特定的查询优化计划或测试替代计划，而不修改数据库。

持久性优化器统计功能默认启用（innodb_stats_persistent=ON）。

非持久性优化器统计信息在每次服务器重启和其他一些操作后被清除，并在下一次访问表时重新计算。因此，在重新计算统计信息时可能会产生不同的估计值，导致执行计划的不同选择和查询性能的变化。

本节还提供了关于估算ANALYZE TABLE复杂度的信息，这在尝试在准确统计信息和ANALYZE TABLE执行时间之间取得平衡时可能会有用。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html

17.8.10.1 配置持久性优化器统计参数

持久性优化器统计功能通过将统计数据存储到磁盘并使其在服务器重新启动时持久化，从而改善了计划稳定性，使得优化器更有可能为给定查询每次做出一致的选择。

当innodb_stats_persistent=ON或当单独的表被定义为STATS_PERSISTENT=1时，优化器统计数据会持久化到磁盘。innodb_stats_persistent默认启用。

以前，当重新启动服务器和执行其他某些类型的操作时，优化器统计数据会被清除，并在下一次访问表时重新计算。因此，在重新计算统计数据时可能会产生不同的估计值，导致查询执行计划中的不同选择和查询性能的变化。

持久性统计数据存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中。请参阅第 17.8.10.1.5 节，“InnoDB 持久性统计表”。

如果您不希望将优化器统计数据持久化到磁盘，请参阅第 17.8.10.2 节，“配置非持久性优化器统计参数”

17.8.10.1.1 配置持久性优化器统计的自动计算

默认启用的innodb_stats_auto_recalc变量控制当表的行数变化超过 10%时是否自动计算统计数据。您还可以通过在创建或更改表时指定STATS_AUTO_RECALC子句来为单独的表配置自动统计数据重新计算。

由于自动统计重新计算的异步性质，即使启用了innodb_stats_auto_recalc，在运行影响表超过 10%的 DML 操作后，统计数据可能不会立即重新计算。在某些情况下，统计数据重新计算可能会延迟几秒钟。如果需要立即更新的统计数据，请运行ANALYZE TABLE来启动同步（前台）重新计算统计数据。

如果禁用了innodb_stats_auto_recalc，您可以通过在对索引列进行重大更改后执行ANALYZE TABLE语句来确保优化器统计的准确性。您还可以考虑将ANALYZE TABLE添加到在加载数据后运行的设置脚本中，并在低活动时间定期运行ANALYZE TABLE。

当向现有表添加索引或添加或删除列时，无论innodb_stats_auto_recalc的值如何，都会计算索引统计信息并将其添加到innodb_index_stats表中。

17.8.10.1.2 为单个表配置优化器统计参数

innodb_stats_persistent，innodb_stats_auto_recalc，和innodb_stats_persistent_sample_pages是全局变量。要覆盖这些系统范围的设置，并为单个表配置优化器统计参数，您可以在CREATE TABLE或ALTER TABLE语句中定义STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句。

STATS_PERSISTENT指定是否为InnoDB表启用持久统计信息。值DEFAULT导致表的持久统计设置由innodb_stats_persistent设置确定。值为1时，为表启用持久统计信息，值为0时禁用该功能。在为单个表启用持久统计信息后，使用ANALYZE TABLE在加载表数据后计算统计信息。
STATS_AUTO_RECALC指定是否自动重新计算持久统计信息。值DEFAULT导致表的持久统计设置由innodb_stats_auto_recalc设置确定。值为1时，当表数据变化了 10%时重新计算统计信息。值为0时，阻止对表进行自动重新计算。当使用值为 0 时，使用ANALYZE TABLE在对表进行重大更改后重新计算统计信息。
STATS_SAMPLE_PAGES 指定了在对索引列进行统计计算时，通过 ANALYZE TABLE 操作采样的索引页面数量，例如。

所有三个子句在以下 CREATE TABLE 示例中指定：

CREATE TABLE `t1` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY  (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,
  STATS_PERSISTENT=1,
  STATS_AUTO_RECALC=1,
  STATS_SAMPLE_PAGES=25;

17.8.10.1.3 配置 InnoDB 优化器统计信息的采样页面数

优化器使用关于键分布的估计统计信息来选择执行计划的索引，基于索引的相对选择性。诸如 ANALYZE TABLE 等操作会导致 InnoDB 从表中的每个索引中随机采样页面，以估算索引的基数。这种采样技术被称为随机潜水。

innodb_stats_persistent_sample_pages 控制了采样页面的数量。您可以在运行时调整此设置以管理优化器使用的统计估算的质量。默认值为 20。在遇到以下问题时考虑修改此设置：

统计数据不够准确且优化器选择了次优执行计划，如 EXPLAIN 输出所示。您可以通过比较索引的实际基数（通过在索引列上运行 SELECT DISTINCT 确定）与 mysql.innodb_index_stats 表中的估算值来检查统计数据的准确性。

如果确定统计数据不够准确，则应增加 innodb_stats_persistent_sample_pages 的值，直到统计估算足够准确。然而，增加 innodb_stats_persistent_sample_pages 太多可能会导致 ANALYZE TABLE 运行缓慢。
ANALYZE TABLE 运行太慢。在这种情况下，应将 innodb_stats_persistent_sample_pages 减少，直到 ANALYZE TABLE 的执行时间可接受。然而，将值减少太多可能会导致统计不准确和查询执行计划不佳的第一个问题。

如果无法在准确统计和ANALYZE TABLE执行时间之间取得平衡，请考虑减少表中索引列的数量或限制分区数量以减少ANALYZE TABLE的复杂性。还需要考虑表主键中的列数，因为主键列会附加到每个非唯一索引上。

有关相关信息，请参阅第 17.8.10.3 节，“InnoDB 表的 ANALYZE TABLE 复杂性估算”。

17.8.10.1.4 在持久统计计算中包含已标记删除的记录

默认情况下，InnoDB在计算统计信息时会读取未提交的数据。在未提交事务删除表中的行的情况下，计算行估计和索引统计时会排除已标记删除的记录，这可能导致使用除READ UNCOMMITTED之外的事务隔离级别并行操作表的其他事务出现非最佳执行计划。为避免这种情况，可以启用innodb_stats_include_delete_marked以确保在计算持久性优化器统计信息时包含已标记删除的记录。

当启用innodb_stats_include_delete_marked时，ANALYZE TABLE在重新计算统计信息时会考虑已标记删除的记录。

innodb_stats_include_delete_marked是一个影响所有InnoDB表的全局设置，仅适用于持久性优化器统计信息。

17.8.10.1.5 InnoDB 持久性统计表

持久性统计功能依赖于mysql数据库中的内部管理表，名称为innodb_table_stats和innodb_index_stats。这些表会在所有安装、升级和源代码构建过程中自动设置。

表 17.6 innodb_table_stats 的列

列名	描述
`database_name`	数据库名称
`table_name`	表名、分区名或子分区名
`last_update`	指示`InnoDB`上次更新此行的时间戳
`n_rows`	表中的行数
`clustered_index_size`	主索引的大小，以页为单位
`sum_of_other_index_sizes`	其他（非主键）索引的总大小，以页为单位

表 17.7 innodb_index_stats 的列

列名	描述
`database_name`	数据库名称
`table_name`	表名、分区名或子分区名
`index_name`	索引名称
`last_update`	指示上次更新行的时间戳
`stat_name`	统计信息的名称，其值在`stat_value`列中报告
`stat_value`	在`stat_name`列中命名的统计信息的值
`sample_size`	用于`stat_value`列中提供的估计值的页面样本数
`stat_description`	在`stat_name`列中命名的统计信息的描述

innodb_table_stats和innodb_index_stats表包括一个last_update列，显示索引统计信息上次更新的时间：

mysql> SELECT * FROM innodb_table_stats \G
*************************** 1\. row ***************************
           database_name: sakila
              table_name: actor
             last_update: 2014-05-28 16:16:44
                  n_rows: 200
    clustered_index_size: 1
sum_of_other_index_sizes: 1
...

mysql> SELECT * FROM innodb_index_stats \G
*************************** 1\. row ***************************
   database_name: sakila
      table_name: actor
      index_name: PRIMARY
     last_update: 2014-05-28 16:16:44
       stat_name: n_diff_pfx01
      stat_value: 200
     sample_size: 1
     ...

innodb_table_stats和innodb_index_stats表可以手动更新，这样可以强制执行特定的查询优化计划或测试替代计划而不修改数据库。如果手动更新统计信息，请使用FLUSH TABLE *tbl_name*语句加载更新后的统计信息。

持久统计被视为本地信息，因为它们与服务器实例相关。因此，在自动统计信息重新计算时，innodb_table_stats和innodb_index_stats表不会被复制。如果运行ANALYZE TABLE来启动统计信息的同步重新计算，该语句会被复制（除非你禁止了它的日志记录），并且重新计算会在副本上进行。

17.8.10.1.6 InnoDB 持久统计表示例

innodb_table_stats表中包含每个表的一行。以下示例演示了收集的数据类型。

表t1包含一个主索引（列a，b），一个次要索引（列c，d），和一个唯一索引（列e，f）：

CREATE TABLE t1 (
a INT, b INT, c INT, d INT, e INT, f INT,
PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
) ENGINE=INNODB;

插入五行样本数据后，表t1如下所示：

mysql> SELECT * FROM t1;
+---+---+------+------+------+------+
| a | b | c    | d    | e    | f    |
+---+---+------+------+------+------+
| 1 | 1 |   10 |   11 |  100 |  101 |
| 1 | 2 |   10 |   11 |  200 |  102 |
| 1 | 3 |   10 |   11 |  100 |  103 |
| 1 | 4 |   10 |   12 |  200 |  104 |
| 1 | 5 |   10 |   12 |  100 |  105 |
+---+---+------+------+------+------+

要立即更新统计信息，运行ANALYZE TABLE（如果innodb_stats_auto_recalc已启用，则假定在几秒钟内自动更新统计信息，假设已达到更改表行的 10%阈值）：

mysql> ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+

表t1的表统计信息显示了InnoDB上次更新表统计信息的时间（2014-03-14 14:36:34），表中的行数（5），聚集索引大小（1页），以及其他索引的组合大小（2页）。

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 't1'\G
*************************** 1\. row ***************************
           database_name: test
              table_name: t1
             last_update: 2014-03-14 14:36:34
                  n_rows: 5
    clustered_index_size: 1
sum_of_other_index_sizes: 2

innodb_index_stats表对每个索引包含多行。innodb_index_stats表中的每一行提供与stat_name列中命名的特定索引统计信息相关的数据，并在stat_description列中描述。例如：

mysql> SELECT index_name, stat_name, stat_value, stat_description
       FROM mysql.innodb_index_stats WHERE table_name like 't1';
+------------+--------------+------------+-----------------------------------+
| index_name | stat_name    | stat_value | stat_description                  |
+------------+--------------+------------+-----------------------------------+
| PRIMARY    | n_diff_pfx01 |          1 | a                                 |
| PRIMARY    | n_diff_pfx02 |          5 | a,b                               |
| PRIMARY    | n_leaf_pages |          1 | Number of leaf pages in the index |
| PRIMARY    | size         |          1 | Number of pages in the index      |
| i1         | n_diff_pfx01 |          1 | c                                 |
| i1         | n_diff_pfx02 |          2 | c,d                               |
| i1         | n_diff_pfx03 |          2 | c,d,a                             |
| i1         | n_diff_pfx04 |          5 | c,d,a,b                           |
| i1         | n_leaf_pages |          1 | Number of leaf pages in the index |
| i1         | size         |          1 | Number of pages in the index      |
| i2uniq     | n_diff_pfx01 |          2 | e                                 |
| i2uniq     | n_diff_pfx02 |          5 | e,f                               |
| i2uniq     | n_leaf_pages |          1 | Number of leaf pages in the index |
| i2uniq     | size         |          1 | Number of pages in the index      |
+------------+--------------+------------+-----------------------------------+

stat_name列显示以下类型的统计信息：

size：当stat_name=size时，stat_value列显示索引中的总页数。
n_leaf_pages：当stat_name=n_leaf_pages时，stat_value列显示索引中叶子页的数量。
n_diff_pfx*NN*：当stat_name=n_diff_pfx01时，stat_value列显示索引的第一列中不同值的数量。当stat_name=n_diff_pfx02时，stat_value列显示索引的前两列中不同值的数量，依此类推。当stat_name=n_diff_pfx*NN*时，stat_description列显示一个逗号分隔的计数索引列的列表。

为了进一步说明提供基数数据的n_diff_pfx*NN*统计信息，再次考虑之前介绍的t1表示例。如下所示，t1表创建了一个主索引（列a、b）、一个次要索引（列c、d）和一个唯一索引（列e、f）：

CREATE TABLE t1 (
  a INT, b INT, c INT, d INT, e INT, f INT,
  PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
) ENGINE=INNODB;

插入五行样本数据后，表t1如下所示：

mysql> SELECT * FROM t1;
+---+---+------+------+------+------+
| a | b | c    | d    | e    | f    |
+---+---+------+------+------+------+
| 1 | 1 |   10 |   11 |  100 |  101 |
| 1 | 2 |   10 |   11 |  200 |  102 |
| 1 | 3 |   10 |   11 |  100 |  103 |
| 1 | 4 |   10 |   12 |  200 |  104 |
| 1 | 5 |   10 |   12 |  100 |  105 |
+---+---+------+------+------+------+

当查询index_name、stat_name、stat_value和stat_description，其中stat_name LIKE 'n_diff%'时，将返回以下结果集：

mysql> SELECT index_name, stat_name, stat_value, stat_description
       FROM mysql.innodb_index_stats
       WHERE table_name like 't1' AND stat_name LIKE 'n_diff%';
+------------+--------------+------------+------------------+
| index_name | stat_name    | stat_value | stat_description |
+------------+--------------+------------+------------------+
| PRIMARY    | n_diff_pfx01 |          1 | a                |
| PRIMARY    | n_diff_pfx02 |          5 | a,b              |
| i1         | n_diff_pfx01 |          1 | c                |
| i1         | n_diff_pfx02 |          2 | c,d              |
| i1         | n_diff_pfx03 |          2 | c,d,a            |
| i1         | n_diff_pfx04 |          5 | c,d,a,b          |
| i2uniq     | n_diff_pfx01 |          2 | e                |
| i2uniq     | n_diff_pfx02 |          5 | e,f              |
+------------+--------------+------------+------------------+

对于PRIMARY索引，有两个n_diff%行。行数等于索引中的列数。

注意

对于非唯一索引，InnoDB会附加主键的列。

当index_name=PRIMARY且stat_name=n_diff_pfx01时，stat_value为1，表示索引的第一列（列a）中有一个单独的值。通过查看表t1中列a中的数据，确认列a中的不同值的数量，其中有一个单独的值（1）。计数的列（a）显示在结果集的stat_description列中。
当index_name=PRIMARY且stat_name=n_diff_pfx02时，stat_value为5，表示索引的两列（a,b）中有五个不同的值。通过查看表t1中列a和b中的数据，确认列a和b中的不同值的数量，其中有五个不同的值：(1,1)、(1,2)、(1,3)、(1,4)和(1,5)。计数的列（a,b）显示在结果集的stat_description列中。

对于次要索引（i1），有四个n_diff%行。次要索引（c,d）仅定义了两列，但有四个n_diff%行，因为InnoDB会在所有非唯一索引后缀主键。因此，有四个n_diff%行，而不是两个，以考虑次要索引列（c,d）和主键列（a,b）。

当index_name=i1且stat_name=n_diff_pfx01时，stat_value为1，表示索引的第一列（列c）中有一个单独的值。通过查看表t1中列c中的数据，确认列c中的不同值的数量，其中有一个单独的值：(10)。计数的列（c）显示在结果集的stat_description列中。
当 index_name=i1 且 stat_name=n_diff_pfx02 时，stat_value 为 2，表示索引（c,d）的前两列中有两个不同的值。在表 t1 中查看列 c 和 d 的数据，可以确认有两个不同的值：(10,11) 和 (10,12)。计数的列（c,d）显示在结果集的 stat_description 列中。
当 index_name=i1 且 stat_name=n_diff_pfx03 时，stat_value 为 2，表示索引（c,d,a）的前三列中有两个不同的值。在表 t1 中查看列 c、d 和 a 的数据，可以确认有两个不同的值：(10,11,1) 和 (10,12,1)。计数的列（c,d,a）显示在结果集的 stat_description 列中。
当 index_name=i1 且 stat_name=n_diff_pfx04 时，stat_value 为 5，表示索引（c,d,a,b）的四列中有五个不同的值。在表 t1 中查看列 c、d、a 和 b 的数据，可以确认有五个不同的值：(10,11,1,1)、(10,11,1,2)、(10,11,1,3)、(10,12,1,4) 和 (10,12,1,5)。计数的列（c,d,a,b）显示在结果集的 stat_description 列中。

对于唯一索引（i2uniq），有两行 n_diff%。

当 index_name=i2uniq 且 stat_name=n_diff_pfx01 时，stat_value 为 2，表示索引（列 e）的第一列中有两个不同的值。在表 t1 中查看列 e 的数据，可以确认有两个不同的值：(100) 和 (200)。计数的列（e）显示在结果集的 stat_description 列中。
当 index_name=i2uniq 且 stat_name=n_diff_pfx02 时，stat_value 为 5，表示索引（e,f）的两列中有五个不同的值。在表 t1 中查看列 e 和 f 的数据，可以确认有五个不同的值：(100,101)、(200,102)、(100,103)、(200,104) 和 (100,105)。计数的列（e,f）显示在结果集的 stat_description 列中。

17.8.10.1.7 使用 `innodb_index_stats` 表检索索引大小

您可以使用 innodb_index_stats 表检索表、分区或子分区的索引大小。在下面的示例中，检索了表 t1 的索引大小。有关表 t1 和相应索引统计信息的定义，请参见第 17.8.10.1.6 节，“InnoDB 持久性统计表示例”。

mysql> SELECT SUM(stat_value) pages, index_name,
       SUM(stat_value)*@@innodb_page_size size
       FROM mysql.innodb_index_stats WHERE table_name='t1'
       AND stat_name = 'size' GROUP BY index_name;
+-------+------------+-------+
| pages | index_name | size  |
+-------+------------+-------+
|     1 | PRIMARY    | 16384 |
|     1 | i1         | 16384 |
|     1 | i2uniq     | 16384 |
+-------+------------+-------+

对于分区或子分区，您可以使用相同的查询，只需修改WHERE子句以检索索引大小。例如，以下查询检索表t1的分区索引大小：

mysql> SELECT SUM(stat_value) pages, index_name,
       SUM(stat_value)*@@innodb_page_size size
       FROM mysql.innodb_index_stats WHERE table_name like 't1#P%'
       AND stat_name = 'size' GROUP BY index_name;

原文：dev.mysql.com/doc/refman/8.0/en/innodb-statistics-estimation.html

17.8.10.2 配置非持久性优化器统计参数

本节描述了如何配置非持久性优化器统计。当innodb_stats_persistent=OFF或当使用STATS_PERSISTENT=0创建或更改单个表时，优化器统计不会持久保存到磁盘。相反，统计信息存储在内存中，在服务器关闭时丢失。统计信息也会定期由某些操作和在某些条件下更新。

优化器统计默认持久保存到磁盘，由innodb_stats_persistent配置选项启用。有关持久性优化器统计的信息，请参见第 17.8.10.1 节，“配置持久性优化器统计参数”。

优化器统计更新

当非持久性优化器统计更新时：

运行ANALYZE TABLE。
运行SHOW TABLE STATUS，SHOW INDEX，或使用启用innodb_stats_on_metadata选项查询信息模式TABLES或STATISTICS表。

innodb_stats_on_metadata的默认设置为OFF。启用innodb_stats_on_metadata可能会降低具有大量表或索引的模式的访问速度，并降低涉及InnoDB表的查询的执行计划的稳定性。通过使用SET语句在全局配置innodb_stats_on_metadata。
```
SET GLOBAL innodb_stats_on_metadata=ON
```
注意

当优化器统计配置为非持久性时，innodb_stats_on_metadata才适用（当禁用innodb_stats_persistent时）。
使用启用了--auto-rehash选项的mysql客户端，这是默认设置。auto-rehash选项会导致所有InnoDB表被打开，并且打开表操作会导致统计信息被重新计算。

为了提高mysql客户端的启动时间和更新统计信息，您可以使用--disable-auto-rehash选项关闭auto-rehash。auto-rehash功能使交互用户可以自动完成数据库、表和列名的名称补全。
首先打开一个表。
InnoDB检测到自上次更新统计信息以来表的 1/16 已被修改。

配置采样页面数

MySQL 查询优化器使用关于键分布的估计统计信息来选择执行计划的索引，基于索引的相对选择性。当InnoDB更新优化器统计信息时，它会从表的每个索引中随机采样页面，以估算索引的基数。（这种技术被称为随机潜水。）

为了让您控制统计信息估计的质量（从而为查询优化器提供更好的信息），您可以使用参数innodb_stats_transient_sample_pages更改采样页面数。默认的采样页面数为 8，这可能不足以产生准确的估算，导致查询优化器做出错误的索引选择。这种技术对于大表和用于连接的表尤为重要。对于这些表进行不必要的全表扫描可能会成为一个重大的性能问题。请参阅 Section 10.2.1.23, “避免全表扫描”以获取调整此类查询的提示。innodb_stats_transient_sample_pages是一个可以在运行时设置的全局参数。

当innodb_stats_persistent=0时，innodb_stats_transient_sample_pages的值会影响所有InnoDB表和索引的索引采样。在更改索引采样大小时，请注意以下可能产生重大影响的情况：

像 1 或 2 这样的小值可能导致基数的估计不准确。
增加innodb_stats_transient_sample_pages的值可能需要更多的磁盘读取。比 8 大得多的值（比如 100），可能会导致打开表或执行SHOW TABLE STATUS的时间显著减慢。
优化器可能会根据不同的索引选择性估计选择非常不同的查询计划。

无论对于系统来说，innodb_stats_transient_sample_pages的哪个值效果最好，都要设置该选项并将其保持在该值。选择一个能够为数据库中所有表提供合理准确估计而又不需要过多 I/O 的值。因为统计数据会在执行ANALYZE TABLE之外的各种时间自动重新计算，所以增加索引样本大小，运行ANALYZE TABLE，然后再减小样本大小是没有意义的。

较小的表通常需要比较少的索引样本。如果您的数据库有许多大表，考虑使用比大多数较小表更高的值innodb_stats_transient_sample_pages。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-analyze-table-complexity.html

17.8.10.3 估计 InnoDB 表的 ANALYZE TABLE 复杂性

ANALYZE TABLE复杂性对InnoDB表取决于：

采样的页面数，由innodb_stats_persistent_sample_pages定义。
表中索引列的数量
分区的数量。如果表没有分区，则分区的数量被视为 1。

使用这些参数，估计ANALYZE TABLE复杂性的近似公式将是：

innodb_stats_persistent_sample_pages的值 * 表中索引列的数量 * 表中分区的数量

通常，结果值越大，执行ANALYZE TABLE所需的时间越长。

注意

innodb_stats_persistent_sample_pages定义了在全局级别采样的页面数。要为单个表设置采样页面数，请在CREATE TABLE或ALTER TABLE中使用STATS_SAMPLE_PAGES选项。更多信息，请参见第 17.8.10.1 节，“配置持久性优化器统计参数”。

如果innodb_stats_persistent=OFF，则采样的页面数由innodb_stats_transient_sample_pages定义。有关更多信息，请参见第 17.8.10.2 节，“配置非持久性优化器统计参数”。

要更深入地估计ANALYZE TABLE复杂性，请考虑以下示例。

在大 O 符号中，ANALYZE TABLE的复杂性描述为：

 O(n_sample
  * (n_cols_in_uniq_i
     + n_cols_in_non_uniq_i
     + n_cols_in_pk * (1 + n_non_uniq_i))
  * n_part)

其中：

n_sample是采样的页面数（由innodb_stats_persistent_sample_pages定义）
n_cols_in_uniq_i是所有唯一索引中所有列的总数（不包括主键列）
n_cols_in_non_uniq_i是所有非唯一索引中所有列的总数
n_cols_in_pk是主键中的列数（如果未定义主键，则InnoDB在内部创建单列主键）
n_non_uniq_i是表中非唯一索引的数量
n_part是分区的数量。如果未定义分区，则认为表是单个分区。

现在，考虑以下表（表t），它具有主键（2 列），唯一索引（2 列）和两个非唯一索引（每个两列）：

CREATE TABLE t (
  a INT,
  b INT,
  c INT,
  d INT,
  e INT,
  f INT,
  g INT,
  h INT,
  PRIMARY KEY (a, b),
  UNIQUE KEY i1uniq (c, d),
  KEY i2nonuniq (e, f),
  KEY i3nonuniq (g, h)
);

对于上述算法所需的列和索引数据，请查询表t的mysql.innodb_index_stats持久性索引统计表。n_diff_pfx%统计数据显示了为每个索引计算的列。例如，主键索引计算了列a和b。对于非唯一索引，除了用户定义的列外，还计算了主键列（a，b）。

注意

有关InnoDB持久性统计表的其他信息，请参见第 17.8.10.1 节，“配置持久性优化器统计参数”

mysql> SELECT index_name, stat_name, stat_description
       FROM mysql.innodb_index_stats WHERE
       database_name='test' AND
       table_name='t' AND
       stat_name like 'n_diff_pfx%';
 +------------+--------------+------------------+
 | index_name | stat_name    | stat_description |
 +------------+--------------+------------------+
 | PRIMARY    | n_diff_pfx01 | a                |
 | PRIMARY    | n_diff_pfx02 | a,b              |
 | i1uniq     | n_diff_pfx01 | c                |
 | i1uniq     | n_diff_pfx02 | c,d              |
 | i2nonuniq  | n_diff_pfx01 | e                |
 | i2nonuniq  | n_diff_pfx02 | e,f              |
 | i2nonuniq  | n_diff_pfx03 | e,f,a            |
 | i2nonuniq  | n_diff_pfx04 | e,f,a,b          |
 | i3nonuniq  | n_diff_pfx01 | g                |
 | i3nonuniq  | n_diff_pfx02 | g,h              |
 | i3nonuniq  | n_diff_pfx03 | g,h,a            |
 | i3nonuniq  | n_diff_pfx04 | g,h,a,b          |
 +------------+--------------+------------------+

根据上述索引统计数据和表定义，可以确定以下值：

n_cols_in_uniq_i，所有唯一索引中所有列的总数，不包括主键列，为 2（c和d）
n_cols_in_non_uniq_i，所有非唯一索引中所有列的总数为 4（e，f，g和h）
n_cols_in_pk，主键中的列数为 2（a和b）
n_non_uniq_i，表中非唯一索引的数量为 2（i2nonuniq和i3nonuniq）
n_part，分区的数量为 1。

现在，您可以计算innodb_stats_persistent_sample_pages *（2 + 4 + 2 （1 + 2）） 1 来确定扫描的叶页数。假设innodb_stats_persistent_sample_pages设置为默认值20，默认页面大小为 16 KiB（innodb_page_size=16384），则可以估计对表t读取了 20 * 12 * 16384 bytes，约为 4 MiB。

注意

可能并非从磁盘读取全部 4 MiB，因为一些叶页可能已经缓存在缓冲池中。

17.8.11 配置索引页面的合并阈值

原文：dev.mysql.com/doc/refman/8.0/en/index-page-merge-threshold.html

您可以配置索引页面的MERGE_THRESHOLD值。如果索引页面的“page-full”百分比低于MERGE_THRESHOLD值，当删除行或通过UPDATE操作缩短行时，InnoDB会尝试将索引页面与相邻的索引页面合并。默认的MERGE_THRESHOLD值为 50，这是先前硬编码的值。最小的MERGE_THRESHOLD值为 1，最大值为 50。

当索引页面的“page-full”百分比低于默认的MERGE_THRESHOLD设置值 50%时，InnoDB会尝试将索引页面与相邻页面合并。如果两个页面都接近 50%满，页面合并后很快可能会发生页面分裂。如果此合并-分裂行为频繁发生，可能会对性能产生不利影响。为避免频繁的合并-分裂，您可以降低MERGE_THRESHOLD值，以便InnoDB在较低的“page-full”百分比下尝试页面合并。在较低的页面满百分比下合并页面会在索引页面中留下更多空间，并有助于减少合并-分裂行为。

可以为表或单个索引定义索引页面的MERGE_THRESHOLD。为单个索引定义的MERGE_THRESHOLD值优先于为表定义的MERGE_THRESHOLD值。如果未定义，MERGE_THRESHOLD值默认为 50。

为表设置`MERGE_THRESHOLD`

您可以使用CREATE TABLE语句的*table_option* COMMENT子句为表设置MERGE_THRESHOLD值。例如：

CREATE TABLE t1 (
   id INT,
  KEY id_index (id)
) COMMENT='MERGE_THRESHOLD=45';

您还可以使用ALTER TABLE的*table_option* COMMENT子句为现有表设置MERGE_THRESHOLD值：

CREATE TABLE t1 (
   id INT,
  KEY id_index (id)
);

ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';

为单个索引设置`MERGE_THRESHOLD`

要为单个索引设置MERGE_THRESHOLD值，可以使用CREATE TABLE、ALTER TABLE或CREATE INDEX中的*index_option* COMMENT子句，如下例所示：

使用CREATE TABLE为单个索引设置MERGE_THRESHOLD：

CREATE TABLE t1 (
   id INT,
  KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40'
);

使用ALTER TABLE为单个索引设置MERGE_THRESHOLD：

CREATE TABLE t1 (
   id INT,
  KEY id_index (id)
);

ALTER TABLE t1 DROP KEY id_index;
ALTER TABLE t1 ADD KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40';

使用CREATE INDEX为单个索引设置MERGE_THRESHOLD：

CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

注意

无法修改GEN_CLUST_INDEX的索引级别的MERGE_THRESHOLD值，GEN_CLUST_INDEX是InnoDB在创建没有主键或唯一键索引的InnoDB表时创建的聚簇索引。只能通过为表设置MERGE_THRESHOLD来修改GEN_CLUST_INDEX的MERGE_THRESHOLD值。

查询索引的`MERGE_THRESHOLD`值

可通过查询INNODB_INDEXES表来获取索引的当前MERGE_THRESHOLD值。例如：

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='id_index' \G
*************************** 1\. row ***************************
       INDEX_ID: 91
           NAME: id_index
       TABLE_ID: 68
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 57
MERGE_THRESHOLD: 40

您可以使用SHOW CREATE TABLE来查看表的MERGE_THRESHOLD值，如果使用*table_option* COMMENT子句明确定义：

mysql> SHOW CREATE TABLE t2 \G
*************************** 1\. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  KEY `id_index` (`id`) COMMENT 'MERGE_THRESHOLD=40'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

注意

在索引级别定义的MERGE_THRESHOLD值优先于为表定义的MERGE_THRESHOLD值。如果未定义，MERGE_THRESHOLD默认为 50%（MERGE_THRESHOLD=50，这是先前硬编码的值。

同样，您可以使用SHOW INDEX来查看索引的MERGE_THRESHOLD值，如果使用*index_option* COMMENT子句明确定义：

mysql> SHOW INDEX FROM t2 \G
*************************** 1\. row ***************************
        Table: t2
   Non_unique: 1
     Key_name: id_index
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment: MERGE_THRESHOLD=40

测量`MERGE_THRESHOLD`设置的效果

INNODB_METRICS表提供两个计数器，可用于衡量MERGE_THRESHOLD设置对索引页面合并的影响。

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS
       WHERE NAME like '%index_page_merge%';
+-----------------------------+----------------------------------------+
| NAME                        | COMMENT                                |
+-----------------------------+----------------------------------------+
| index_page_merge_attempts   | Number of index page merge attempts    |
| index_page_merge_successful | Number of successful index page merges |
+-----------------------------+----------------------------------------+

降低MERGE_THRESHOLD值时的目标是：

较少数量的页面合并尝试和成功的页面合并
一样数量的页面合并尝试和成功的页面合并

如果MERGE_THRESHOLD设置过小，可能会导致数据文件过大，因为空页面空间过多。

有关使用INNODB_METRICS计数器的信息，请参阅第 17.15.6 节，“InnoDB INFORMATION_SCHEMA Metrics Table”。

17.8.12 启用专用 MySQL 服务器的自动配置

原文：dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

当启用innodb_dedicated_server时，InnoDB会自动配置以下变量：

innodb_buffer_pool_size
innodb_redo_log_capacity或在 MySQL 8.0.30 之前，innodb_log_file_size和innodb_log_files_in_group。

注意

innodb_log_file_size和innodb_log_files_in_group在 MySQL 8.0.30 中已弃用。这些变量已被innodb_redo_log_capacity变量取代。
innodb_flush_method

只有在 MySQL 实例驻留在可以使用所有可用系统资源的专用服务器上时才考虑启用innodb_dedicated_server。例如，如果在仅运行 MySQL 的 Docker 容器或专用 VM 中运行 MySQL 服务器，则考虑启用innodb_dedicated_server。如果 MySQL 实例与其他应用程序共享系统资源，则不建议启用innodb_dedicated_server。

接下��的信息描述了每个变量如何自动配置。

innodb_buffer_pool_size

缓冲池大小根据服务器检测到的内存量进行配置。

表 17.8 自动配置的缓冲池大小

检测到的服务器内存缓冲池大小
小于 1GB 128MB（默认值）
1GB 至 4GB 检测到的服务器内存 * 0.5
大于 4GB 检测到的服务器内存 * 0.75

检测到的服务器内存	缓冲池大小
小于 1GB	128MB（默认值）
1GB 至 4GB	`检测到的服务器内存` * 0.5
大于 4GB	`检测到的服务器内存` * 0.75

innodb_redo_log_capacity

重做日志容量根据服务器检测到的内存量进行配置，并且在某些情况下，根据是否显式配置innodb_buffer_pool_size。如果没有显式配置innodb_buffer_pool_size，则假定使用默认值。

警告

如果innodb_buffer_pool_size设置为大于检测到的服务器内存量的值，则自动重做日志容量配置行为是未定义的。

表 17.9 自动配置的日志文件大小

检测到的服务器内存	缓冲池大小	重做日志容量
小于 1GB	未配置	100MB
小于 1GB	小于 1GB	100MB
1GB 至 2GB	不适用	100MB
2GB 至 4GB	未配置	1GB
2GB 到 4GB	任何配置的值	round(0.5 * `检测到的服务器内存` in GB) * 0.5 GB
4GB 到 10.66GB	不适用	round(0.75 * `检测到的服务器内存` in GB) * 0.5 GB
10.66GB 到 170.66GB	不适用	round(0.5625 * `检测到的服务器内存` in GB) * 1 GB
大于 170.66GB	不适用	128GB

innodb_log_file_size（在 MySQL 8.0.30 中已弃用）

日志文件大小根据自动配置的缓冲池大小进行配置。

表 17.10 自动配置的日志文件大小

缓冲池大小日志文件大小
小于 8GB 512MB
8GB 到 128GB 1024MB
大于 128GB 2048MB
innodb_log_files_in_group（在 MySQL 8.0.30 中已弃用）

日志文件数量根据自动配置的缓冲池大小进行配置。MySQL 8.0.14 中添加了自动配置innodb_log_files_in_group变量。

表 17.11 自动配置的日志文件数量

缓冲池大小 ��志文件数量
小于 8GB round(缓冲池大小)
8GB 到 128GB round(缓冲池大小 * 0.75)
大于 128GB 64

注意

如果四舍五入的缓冲池大小值小于 2GB，则强制执行最小的innodb_log_files_in_group值为 2。
innodb_flush_method

当启用innodb_dedicated_server时，刷新方法设置为O_DIRECT_NO_FSYNC。如果O_DIRECT_NO_FSYNC设置不可用，则使用默认的innodb_flush_method设置。

InnoDB在刷新 I/O 期间使用O_DIRECT，但在每次写入操作后跳过fsync()系统调用。

警告

在 MySQL 8.0.14 之前，此设置不适用于需要fsync()系统调用来同步文件系统元数据更改的文件系统，如 XFS 和 EXT4。

从 MySQL 8.0.14 开始，在创建新文件、增加文件大小和关闭文件后会调用fsync()，以确保文件系统元数据更改被同步。在每次写入操作后仍然跳过fsync()系统调用。

如果重做日志文件和数据文件位于不同存储设备上，并且在数据文件写入从未备份电池支持的设备缓存之前发生意外退出，则可能会发生数据丢失。如果您使用或打算使用不同的存储设备用于重做日志文件和数据文件，并且您的数据文件位于没有备电池支持的设备缓存上，请改用O_DIRECT。

缓冲池大小	日志文件大小
小于 8GB	512MB
8GB 到 128GB	1024MB
大于 128GB	2048MB

缓冲池大小	��志文件数量
小于 8GB	round(`缓冲池大小`)
8GB 到 128GB	round(`缓冲池大小` * 0.75)
大于 128GB	64

如果自动配置的选项在选项文件或其他地方明确配置，则使用明确指定的设置，并且类似于以下内容的启动警告将打印到stderr：

[警告] [000000] InnoDB: 由于明确指定了 innodb_buffer_pool_size=134217728，因此 innodb_dedicated_server 选项对 innodb_buffer_pool_size 无效。

对一个选项的显式配置不会阻止其他选项的自动配置。

如果启用了innodb_dedicated_server，并且明确配置了innodb_buffer_pool_size，则基于缓冲池大小配置的变量将使用根据服务器上检测到的内存量计算的缓冲池大小值，而不是明确定义的缓冲池大小值。

每次启动 MySQL 服务器时，自动配置的设置都会被评估和重新配置。

17.9 InnoDB 表和页面压缩

原文：dev.mysql.com/doc/refman/8.0/en/innodb-compression.html

17.9.1 InnoDB 表压缩

17.9.2 InnoDB 页面压缩

本节提供关于InnoDB表压缩和InnoDB页面压缩功能的信息。页面压缩功能也被称为透明页面压缩。

使用InnoDB的压缩功能，您可以创建数据以压缩形式存储的表。压缩有助于提高原始性能和可伸缩性。压缩意味着在磁盘和内存之间传输的数据量更少，并且在磁盘和内存中占用的空间更少。对于具有二级索引的表，好处尤为明显，因为索引数据也被压缩。压缩对于 SSD 存储设备尤为重要，因为它们的容量往往比 HDD 设备低。

17.9.1 InnoDB 表压缩

原文：dev.mysql.com/doc/refman/8.0/en/innodb-table-compression.html

17.9.1.1 表压缩概述

17.9.1.2 创建压缩表

17.9.1.3 调整 InnoDB 表的压缩

17.9.1.4 监控运行时的 InnoDB 表压缩

17.9.1.5 InnoDB 表压缩的工作原理

17.9.1.6 用于 OLTP 工作负载的压缩

17.9.1.7 SQL 压缩语法警告和错误

本节描述了InnoDB表压缩，支持InnoDB表位于 file_per_table 表空间或 general tablespaces 中的情况。表压缩是通过在CREATE TABLE或ALTER TABLE中使用ROW_FORMAT=COMPRESSED属性来启用的。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-compression-background.html

17.9.1.1 表压缩概述

因为处理器和缓存内存的速度增加比磁盘存储设备更快，许多工作负载是磁盘限制的。数据压缩可以使数据库大小更小，减少 I/O，并提高吞吐量，但会增加 CPU 利用率。压缩对于读密集型应用程序特别有价值，在具有足够 RAM 的系统上，可以将经常使用的数据保留在内存中。

使用ROW_FORMAT=COMPRESSED创建的InnoDB表可以在磁盘上使用比配置的innodb_page_size值更小的页面大小。较小的页面需要更少的 I/O 来从磁盘读取和写入，这对 SSD 设备特别有价值。

压缩页面大小是通过CREATE TABLE或ALTER TABLE的KEY_BLOCK_SIZE参数指定的。不同的页面大小要求表必须放置在 file-per-table 表空间或 general tablespace 中，而不是在 system tablespace 中，因为系统表空间无法存储压缩表。有关更多信息，请参见 Section 17.6.3.2, “File-Per-Table Tablespaces”和 Section 17.6.3.3, “General Tablespaces”。

无论KEY_BLOCK_SIZE值如何，压缩级别都是相同的。当您为KEY_BLOCK_SIZE指定较小的值时，您将获得越来越小页面的 I/O 优势。但是，如果指定的值太小，当数据值无法压缩到足以适应每个页面的多行时，重新组织页面会增加额外的开销。对于每个索引的关键列的长度，表的KEY_BLOCK_SIZE可以有一个硬限制。指定一个太小的值，CREATE TABLE或ALTER TABLE语句将失败。

在缓冲池中，压缩数据以小页面的形式保存，页面大小基于KEY_BLOCK_SIZE值。为了提取或更新列值，MySQL 还在缓冲池中创建一个包含未压缩数据的未压缩页面。在缓冲池中，对未压缩页面的任何更新也会重新写回等效的压缩页面。您可能需要调整缓冲池的大小以容纳压缩和未压缩页面的额外数据，尽管在需要空间时，未压缩页面会从缓冲池中被驱逐，然后在下一次访问时再次解压缩。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-compression-usage.html

17.9.1.2 创建压缩表

可以在 file-per-table 表空间或通用表空间中创建压缩表。表压缩不适用于 InnoDB 系统表空间。系统表空间（空间 0，.ibdata 文件）可以包含用户创建的表，但也包含内部系统数据，这些数据永远不会被压缩。因此，压缩仅适用于存储在文件表表空间或通用表空间中的表（和索引）。

在文件表表空间中创建压缩表

要在文件表表空间中创建压缩表，必须启用innodb_file_per_table（默认情况下）。您可以在 MySQL 配置文件（my.cnf或my.ini）中设置此参数，也可以使用SET语句动态设置。

配置innodb_file_per_table选项后，在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED子句或KEY_BLOCK_SIZE子句，或两者都指定，以在文件表表空间中创建一个压缩表。

例如，您可以使用以下语句：

SET GLOBAL innodb_file_per_table=1;
CREATE TABLE t1
 (c1 INT PRIMARY KEY)
 ROW_FORMAT=COMPRESSED
 KEY_BLOCK_SIZE=8;

在通用表空间中创建压缩表

要在通用表空间中创建压缩表，必须为通用表空间定义FILE_BLOCK_SIZE，这在创建表空间时指定。FILE_BLOCK_SIZE值必须是相对于innodb_page_size值的有效压缩页大小，并且由CREATE TABLE或ALTER TABLE的KEY_BLOCK_SIZE子句定义的压缩表的页大小必须等于FILE_BLOCK_SIZE/1024。例如，如果innodb_page_size=16384且FILE_BLOCK_SIZE=8192，则表的KEY_BLOCK_SIZE必须为 8。有关更多信息，请参见第 17.6.3.3 节，“通用表空间”。

以下示例演示了创建通用表空间并添加压缩表。该示例假定默认的innodb_page_size为 16K。FILE_BLOCK_SIZE为 8192 要求压缩表具有KEY_BLOCK_SIZE为 8。

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

注意

截至 MySQL 8.0，压缩表的表空间文件是使用物理页大小而不是InnoDB页大小创建的，这使得空压缩表的初始表空间文件大小比以前的 MySQL 版本更小。
如果指定ROW_FORMAT=COMPRESSED，可以省略KEY_BLOCK_SIZE；KEY_BLOCK_SIZE设置默认为innodb_page_size值的一半。
如果指定了有效的KEY_BLOCK_SIZE值，可以省略ROW_FORMAT=COMPRESSED；压缩会自动启用。
要确定KEY_BLOCK_SIZE的最佳值，通常需要创建几个具有不同此子句值的相同表的副本，然后测量生成的.ibd文件的大小，并查看每个在实际工作负载中的表现如何。对于一般表空间，请记住删除表不会减小一般表空间.ibd文件的大小，也不会将磁盘空间返回给操作系统。有关更多信息，请参见第 17.6.3.3 节，“一般表空间”。
KEY_BLOCK_SIZE值被视为提示；如果需要，InnoDB可以使用不同的大小。对于每个表的文件表空间，KEY_BLOCK_SIZE只能小于或等于innodb_page_size值。如果指定的值大于innodb_page_size值，则指定的值将被忽略，发出警告，并将KEY_BLOCK_SIZE设置为innodb_page_size值的一半。如果innodb_strict_mode=ON，指定无效的KEY_BLOCK_SIZE值会返回错误。对于一般表空间，有效的KEY_BLOCK_SIZE值取决于表空间的FILE_BLOCK_SIZE设置。有关更多信息，请参见第 17.6.3.3 节，“一般表空间”。
InnoDB支持 32KB 和 64KB 页大小，但这些页大小不支持压缩。有关更多信息，请参阅innodb_page_size文档。
InnoDB数据页的默认未压缩大小为 16KB。根据选项值的组合，MySQL 使用 1KB、2KB、4KB、8KB 或 16KB 的页大小用于表空间数据文件（.ibd文件）。实际的压缩算法不受KEY_BLOCK_SIZE值的影响；该值确定每个压缩块的大小，进而影响可以打包到每个压缩页中的行数。
在文件表表空间中创建压缩表时，将 KEY_BLOCK_SIZE 设置为 InnoDB 页大小通常不会产生太多压缩效果。例如，设置 KEY_BLOCK_SIZE=16 通常不会产生太多压缩效果，因为正常的 InnoDB 页大小为 16KB。对于具有许多长 BLOB、VARCHAR 或 TEXT 列的表，此设置仍然可能很有用，因为这些值通常可以很好地压缩，因此可能需要较少的溢出页，如 Section 17.9.1.5, “How Compression Works for InnoDB Tables” 中所述。对于通用表空间，不允许将 KEY_BLOCK_SIZE 值设置为 InnoDB 页大小。有关更多信息，请参见 Section 17.6.3.3, “General Tablespaces”。
表的所有索引（包括聚簇索引语句的输出中）。
有关与性能相关的配置选项，请参见 Section 17.9.1.3, “Tuning Compression for InnoDB Tables”。

压缩表的限制

压缩表不能存储在 InnoDB 系统表空间中。
通用表空间可以包含多个表，但压缩表和非压缩表不能共存于同一通用表空间中。
压缩适用于整个表及其所有关联的索引，而不是单独的行，尽管子句名称为 ROW_FORMAT。
InnoDB 不支持压缩临时表。当启用 innodb_strict_mode（默认情况下）时，如果指定了 ROW_FORMAT=COMPRESSED 或 KEY_BLOCK_SIZE，则 CREATE TEMPORARY TABLE 会返回错误。如果禁用了 innodb_strict_mode，则会发出警告，并且临时表将使用非压缩的行格式创建。对临时表的 ALTER TABLE 操作也适用相同的限制。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-compression-tuning.html

17.9.1.3 调整 InnoDB 表的压缩

大多数情况下，InnoDB 数据存储和压缩中描述的内部优化确保系统能够很好地运行压缩数据。然而，由于压缩的效率取决于您的数据性质，您可以做出影响压缩表性能的决策：

哪些表需要压缩。
使用何种压缩页大小。
根据运行时性能特征调整缓冲池大小的时机，例如系统花费在压缩和解压数据上的时间量。工作负载更像是数据仓库（主要是查询）还是 OLTP 系统（查询和 DML 混合）。
如果系统在压缩表上执行 DML 操作，并且数据分布方式导致运行时昂贵的压缩失败，则可能需要调整其他高级配置选项。

使用本节中的指南来帮助做出这些架构和配置选择。当您准备进行长期测试并将压缩表投入生产时，请参阅第 17.9.1.4 节，“监视 InnoDB 表在运行时的压缩”，以验证这些选择在实际条件下的有效性。

何时使用压缩

一般来说，压缩最适合包含合理数量的字符列并且数据读取频率远远高于写入频率的表。因为没有确切的方法来预测压缩是否对特定情况有益，所以始终要使用特定的工作负载和数据集在代表性配置上进行测试。在决定哪些表需要压缩时，请考虑以下因素。

数据特征和压缩

压缩在减小数据文件大小方面的效率的一个关键因素是数据本身的性质。请记住，压缩通过识别数据块中重复的字节字符串来工作。完全随机化的数据是最糟糕的情况。典型数据通常具有重复值，因此可以有效地压缩。字符字符串通常压缩得很好，无论是在CHAR、VARCHAR、TEXT还是BLOB列中定义。另一方面，包含大部分二进制数据（整数或浮点数）或先前压缩的数据（例如JPEG或PNG图像）的表通常可能不会有效地压缩，或者根本不会压缩。

你可以选择是否为每个 InnoDB 表启用压缩。一个表及其所有索引使用相同的（压缩的）页面大小。也许主键（聚簇）索引，其中包含表的所有列的数据，比次要索引更有效地压缩。对于那些存在长行的情况，使用压缩可能导致长列值被存储在“页外”，如 DYNAMIC 行格式中所讨论的那样。这些溢出页面可能会压缩得很好。考虑到这些因素，对于许多应用程序，一些表比其他表更有效地压缩，你可能会发现你的工作负载只有在一部分表被压缩时才能表现最佳。

要确定是否压缩特定表，进行实验。你可以通过使用实现 LZ77 压缩的实用程序（如gzip或 WinZip）对未压缩表的.ibd 文件进行压缩的粗略估计。你可以期望 MySQL 压缩表的压缩效率比基于文件的压缩工具要低，因为 MySQL 根据默认的页面大小（16KB）对数据进行分块压缩。除了用户数据，页面格式还包括一些未压缩的内部系统数据。基于文件的压缩工具可以检查更大的数据块，因此可能会在一个巨大的文件中找到比 MySQL 在一个单独页面中找到的更多重复字符串。

另一种测试特定表的压缩的方法是将一些数据从未压缩的表复制到一个类似的、压缩的表（具有完全相同的索引）中，该表位于 file-per-table 表空间中，并查看生成的.ibd文件的大小。例如：

USE test;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL autocommit=0;

-- Create an uncompressed table with a million or two rows.
CREATE TABLE big_table AS SELECT * FROM information_schema.columns;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
COMMIT;
ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment;

SHOW CREATE TABLE big_table\G

select count(id) from big_table;

-- Check how much space is needed for the uncompressed table.
\! ls -l data/test/big_table.ibd

CREATE TABLE key_block_size_4 LIKE big_table;
ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed;

INSERT INTO key_block_size_4 SELECT * FROM big_table;
commit;

-- Check how much space is needed for a compressed table
-- with particular compression settings.
\! ls -l data/test/key_block_size_4.ibd

这个实验产生了以下数字，当然这些数字可能会有很大的变化，取决于你的表结构和数据：

-rw-rw----  1 cirrus  staff  310378496 Jan  9 13:44 data/test/big_table.ibd
-rw-rw----  1 cirrus  staff  83886080 Jan  9 15:10 data/test/key_block_size_4.ibd

要查看压缩是否对你特定的工作负载有效：

对于简单的测试，使用一个没有其他压缩表的 MySQL 实例，并运行查询来访问信息模式INNODB_CMP表。
对涉及多个压缩表的更复杂测试，运行查询来访问信息模式INNODB_CMP_PER_INDEX表。因为INNODB_CMP_PER_INDEX表中的统计信息收集起来很昂贵，你必须在查询该表之前启用配置选项innodb_cmp_per_index_enabled，并且你可能会将这样的测试限制在开发服务器或非关键复制服务器上。
运行一些典型的 SQL 语句来测试你正在测试的压缩表。
通过查询INFORMATION_SCHEMA.INNODB_CMP或INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX来检查成功压缩操作与总体压缩操作的比率，并比较COMPRESS_OPS和COMPRESS_OPS_OK。
如果高比例的压缩操作成功完成，该表可能是压缩的一个良好候选。
如果你得到高比例的压缩失败，你可以根据 17.9.1.6 节，“OLTP 工作负载的压缩”中描述的方式调整innodb_compression_level、innodb_compression_failure_threshold_pct 和 innodb_compression_pad_pct_max 选项，并尝试进一步的测试。

数据库压缩与应用程序压缩

决定是在应用程序中压缩数据还是在表中压缩数据；不要同时对相同数据使用两种类型的压缩。当你在应用程序中压缩数据并将结果存储在压缩表中时，额外的空间节省是极不可能的，双重压缩只会浪费 CPU 周期。

在数据库中进行压缩

启用时，MySQL 表压缩是自动的，并适用于所有列和索引值。列仍然可以使用LIKE等运算符进行测试，排序操作仍然可以使用索引，即使索引值已经被压缩。由于索引通常占数据库总大小的相当大比例，压缩可能会在存储、I/O 或处理器时间上带来显著的节省。压缩和解压缩操作发生在数据库服务器上，该服务器可能是一个强大的系统，大小适合处理预期的负载。

在应用程序中进行压缩

如果在将数据插入数据库之前在应用程序中压缩数据，您可能会通过对某些列进行压缩而对其他列不进行压缩来节省不易压缩的数据的开销。这种方法在客户端机器上使用 CPU 周期进行压缩和解压缩，而不是在数据库服务器上进行，这可能适用于具有许多客户端的分布式应用程序，或者客户端机器有多余 CPU 周期的情况。

混合方法

当然，也可以结合这些方法。对于某些应用程序，可能适合使用一些压缩表和一些未压缩表。最好是对一些数据进行外部压缩（并将其存储在未压缩表中），并允许 MySQL 在应用程序中压缩（部分）其他表。一如既往，提前设计和实际测试对于做出正确决策是有价值的。

工作负载特征和压缩

除了选择哪些表进行压缩（以及页面大小）之外，工作负载是性能的另一个关键决定因素。如果应用程序主要由读操作组成，而不是更新操作，那么在索引页用完每页“修改日志”空间后，需要重新组织和重新压缩的页面就会减少。如果更新主要更改非索引列或包含BLOB或大字符串的列（恰好存储在“页外”），则压缩的开销可能是可以接受的。如果对表的唯一更改是使用单调递增主键进行的INSERT，并且几乎没有次要索引，那么几乎没有必要重新组织和重新压缩索引页。由于 MySQL 可以通过修改未压缩数据“原地”在压缩页面上“标记删除”和删除行，对表进行的DELETE操作相对高效。

对于某些环境来说，加载数据所需的时间与运行时检索一样重要。特别是在数据仓库环境中，许多表可能是只读或读取频繁的。在这种情况下，除非减少磁盘读取或存储成本的节省显著，否则在加载时间上付出压缩的代价可能是可以接受的，也可能不可接受。

从根本上讲，压缩在 CPU 时间可用于压缩和解压数据时效果最佳。因此，如果您的工作负载受限于 I/O，而不是 CPU，您可能会发现压缩可以提高整体性能。在测试不同压缩配置的应用程序性能时，请在与生产系统计划配置相似的平台上进行测试。

配置特性和压缩

从磁盘读取和写入数据库页是系统性能中最慢的部分。压缩试图通过使用 CPU 时间来压缩和解压数据来减少 I/O，并且在 I/O 相对稀缺的情况下，与处理器周期相比，效果最佳。

当在具有快速、多核 CPU 的多用户环境中运行时，通常尤其如此。当压缩表的一页在内存中时，MySQL 通常会使用额外的内存，通常为 16KB，在缓冲池中存储一页的未压缩副本。自适应 LRU 算法试图在压缩和未压缩页之间平衡内存使用，考虑到工作负载是以 I/O 为限还是以 CPU 为限。然而，与内存高度受限的配置相比，将更多内存专用于缓冲池的配置在使用压缩表时往往运行得更好。

选择压缩页大小

压缩页大小的最佳设置取决于表及其索引包含的数据类型和分布。压缩页大小应始终大于最大记录大小，否则操作可能会失败，如压缩 B-Tree 页中所述。

设置压缩页大小过大会浪费一些空间，但页面不必经常压缩。如果压缩页大小设置过小，则插入或更新可能需要耗时的重新压缩，并且 B 树节点可能需要更频繁地拆分，导致更大的数据文件和不太有效的索引。

通常，您将压缩页大小设置为 8K 或 4K 字节。鉴于 InnoDB 表的最大行大小约为 8K，KEY_BLOCK_SIZE=8通常是一个安全选择。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-compression-tuning-monitoring.html

17.9.1.4 在运行时监控 InnoDB 表压缩

整体应用性能、CPU 和 I/O 利用率以及磁盘文件大小是衡量压缩对应用效果的良好指标。本节基于第 17.9.1.3 节“调整 InnoDB 表压缩”中的性能调优建议，展示如何发现在初始测试中可能不会出现的问题。

要深入了解压缩表的性能考虑，您可以使用信息模式表在运行时监控压缩性能，详情请参阅示例 17.1“使用压缩信息模式表”。这些表反映了内存的内部使用和整体使用的压缩率。

INNODB_CMP表报告每个压缩页面大小（KEY_BLOCK_SIZE）的压缩活动信息。这些表中的信息是系统范围的：它总结了数据库中所有压缩表的压缩统计信息。您可以使用这些数据来帮助决定是否压缩表，通过在没有访问其他压缩表时检查这些表来检查这些表。它对服务器的开销相对较低，因此您可以定期在生产服务器上查询它，以检查压缩功能的整体效率。

INNODB_CMP_PER_INDEX 表报告了单个表和索引的压缩活动信息。这些信息更具针对性，更有助于逐个评估压缩效率和诊断性能问题的表或索引。（因为每个 InnoDB 表都表示为一个聚簇索引，MySQL 在这种情况下并不会对表和索引进行明显区分。）INNODB_CMP_PER_INDEX 表确实涉及相当大的开销，因此更适用于开发服务器，在那里你可以独立比较不同工作负载、数据和压缩设置的影响。为了防止意外增加此监控开销，你必须在查询 INNODB_CMP_PER_INDEX 表之前启用 innodb_cmp_per_index_enabled 配置选项。

要考虑的关键统计数据是压缩和解压操作的数量和所花费的时间。由于 MySQL 在修改后无法容纳压缩数据时会拆分 B 树节点，因此比较“成功”压缩操作的数量与总体操作数量。根据 INNODB_CMP 和 INNODB_CMP_PER_INDEX 表中的信息以及整体应用程序性能和硬件资源利用情况，你可能需要调整硬件配置、调整缓冲池大小、选择不同的页面大小或选择不同的要压缩的表。

如果压缩和解压所需的 CPU 时间较长，更换更快或多核 CPU 可以帮助提高性能，同时保持相同的数据、应用工作负载和一组压缩表。增加缓冲池的大小也可能有助于性能，这样更多未压缩的页面可以保留在内存中，减少只存在于内存中的压缩页面的解压需求。

如果整体压缩操作的数量（与应用程序中的INSERT、UPDATE和DELETE操作以及数据库大小相比）较多，可能表明一些被压缩的表正在过度更新，以至于无法有效压缩。如果是这样，选择更大的页面大小，或者更加谨慎地选择要压缩的表。

如果“成功”压缩操作（COMPRESS_OPS_OK）的数量占总压缩操作（COMPRESS_OPS）的比例很高，那么系统可能表现良好。如果比例较低，那么 MySQL 可能比理想情况下更频繁地重新组织、重新压缩和拆分 B 树节点。在这种情况下，避免压缩一些表，或者增加一些被压缩表的KEY_BLOCK_SIZE。您可能会关闭一些表的压缩，如果这些表导致应用程序中“压缩失败”的数量超过总数的 1%或 2%。（在诸如数据加载之类的临时操作期间，这种失败比率可能是可以接受的）。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-compression-internals.html

17.9.1.5 InnoDB 表的压缩工作原理

本节描述了 InnoDB 表的压缩的一些内部实现细节。这里提供的信息可能有助于性能调优，但对于基本的压缩使用并非必须了解。

压缩算法

一些操作系统在文件系统级别实现了压缩。文件通常被分成固定大小的块，这些块被压缩成可变大小的块，这很容易导致碎片化。每次修改块内的内容时，整个块都会在写入磁盘之前重新压缩。这些特性使得这种压缩技术不适合在更新密集型数据库系统中使用。

MySQL 借助著名的zlib 库实现了压缩，该库实现了 LZ77 压缩算法。这种压缩算法成熟、稳健，并且在 CPU 利用率和数据大小减少方面非常高效。该算法是“无损”的，因此始终可以从压缩形式重构原始未压缩数据。LZ77 压缩通过查找在待压缩数据中重复的数据序列来工作。数据中的值模式决定了它的压缩效果，但典型的用户数据通常可以压缩 50%或更多。

与应用程序执行的压缩或其他一些数据库管理系统的压缩功能不同，InnoDB 压缩同时适用于用户数据和索引。在许多情况下，索引可以占据总数据库大小的 40-50%甚至更多，因此这种差异是显著的。当压缩对数据集有效时，InnoDB 数据文件的大小（每表一个文件表空间或通用表空间 .ibd文件）为未压缩大小的 25%至 50%甚至更小。根据工作负载的不同，这种较小的数据库可以进一步减少 I/O，提高吞吐量，以较小的 CPU 利用率成本。您可以通过修改innodb_compression_level配置选项来调整压缩级别和 CPU 开销之间的平衡。

InnoDB 数据存储和压缩

InnoDB 表中的所有用户数据都存储在包含 B 树索引（聚簇索引）的页面中。在其他一些数据库系统中，这种类型的索引被称为“索引组织表”。索引节点中的每一行包含（用户指定或系统生成的）主键的值和表的所有其他列的值。

InnoDB 表中的二级索引也是 B 树，包含值对：索引键和指向聚簇索引中行的指针。该指针实际上是表的主键值，用于访问聚簇索引，如果需要除索引键和主键之外的列。二级索引记录必须始终适合单个 B 树页面。

B 树节点（包括聚簇索引和二级索引）的压缩处理与用于存储长 VARCHAR、BLOB 或 TEXT 列的溢出页的压缩处理不同，如下节所述。

B 树页面的压缩

由于 B 树页面经常更新，因此需要特殊处理。重要的是要尽量减少 B 树节点分裂的次数，以及尽量减少解压缩和重新压缩它们的内容的需求。

MySQL 使用的一种技术是在未压缩形式的 B 树节点中维护一些系统信息，从而方便某些原地更新。例如，这允许行被标记为删除并删除，而无需进行任何压缩操作。

另外，MySQL 尝试在更改索引页面时避免不必要的解压缩和重新压缩。在每个 B 树页面中，系统保留一个未压缩的“修改日志”来记录对页面所做的更改。小记录的更新和插入可以写入此修改日志，而无需完全重建整个页面。

当修改日志的空间用尽时，InnoDB 解压缩页面，应用更改并重新压缩页面。如果重新压缩失败（称为压缩失败的情况），则 B 树节点将被分裂，并重复该过程，直到更新或插入成功。

为了避免在写密集型工作负载中频繁出现压缩失败，例如 OLTP 应用程序，MySQL 有时会在页面中保留一些空间（填充），以便修改日志更快地填满，并在仍有足够空间避免分割页面时重新压缩页面。每个页面中留下的填充空间量因系统跟踪页面分割频率而异。在频繁向压缩表写入的繁忙服务器上，您可以调整innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max配置选项来微调此机制。

通常，MySQL 要求 InnoDB 表中的每个 B 树页面至少能容纳两条记录。对于压缩表，此要求已经放宽。B 树节点的叶子页面（无论是主键还是辅助索引）只需要容纳一条记录，但该记录必须以未压缩形式适合于每页修改日志。如果innodb_strict_mode为ON，MySQL 在CREATE TABLE或CREATE INDEX期间检查最大行大小。如果行不适合，将发出以下错误消息：ERROR HY000: Too big row。

如果在innodb_strict_mode为 OFF 时创建表，并且随后的INSERT或UPDATE语句尝试创建一个在压缩页面大小内不适合的索引条目，则操作将失败，并显示ERROR 42000: Row size too large。（此错误消息不会指定记录过大的索引，也不会提及索引记录的长度或特定索引页面上的最大记录大小。）要解决此问题，请使用ALTER TABLE重建表，并选择更大的压缩页面大小（KEY_BLOCK_SIZE），缩短任何列前缀索引，或完全禁用压缩，使用ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPACT。

innodb_strict_mode不适用于通用表空间，通用表空间也支持压缩表。通用表空间的表空间管理规则严格执行，与innodb_strict_mode独立执行。有关更多信息，请参见第 15.1.21 节，“CREATE TABLESPACE Statement”。

压缩 BLOB、VARCHAR 和 TEXT 列

在 InnoDB 表中，不是主键的BLOB、VARCHAR和TEXT列可能存储在单独分配的溢出页上。我们将这些列称为页外列。它们的值存储在溢出页的单链表上。

对于使用ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED创建的表，根据它们的长度和整行的长度，BLOB、TEXT或VARCHAR列的值可能完全存储在页外。对于存储在页外的列，聚集索引记录仅包含指向溢出页的 20 字节指针，每列一个。是否存储任何列在页外取决于页大小和整行的总大小。当整行长度过长无法完全适应聚集索引页时，MySQL 会选择最长的列进行页外存储，直到整行适应聚集索引页。如上所述，如果一行无法适应压缩页，将会出现错误。

注意

对于使用ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED创建的表，长度小于或等于 40 字节的TEXT和BLOB列始终存储在内部。

使用ROW_FORMAT=REDUNDANT和ROW_FORMAT=COMPACT的表将BLOB、VARCHAR和TEXT列的前 768 字节存储在聚集索引记录中，以及主键。768 字节前缀后跟一个指向包含列其余部分值的溢出页的 20 字节指针。

当表处于COMPRESSED格式时，写入溢出页的所有数据都会被压缩“原样”；也就是说，MySQL 会对整个数据项应用 zlib 压缩算法。除数据外，压缩的溢出页包含一个未压缩的页头和页尾，其中包括页校验和指向下一个溢出页的链接，等等。因此，如果数据高度可压缩，通常情况下文本数据会获得非常显著的存储节省，对于较长的BLOB、TEXT或VARCHAR列。图像数据，如JPEG，通常已经被压缩，因此将其存储在压缩表中并不会带来太大好处；双重压缩可能会浪费 CPU 周期而几乎不节省空间。

溢出页与其他页的大小相同。存储在页外的包含十列的行占用十个溢出页，即使列的总长度只有 8K 字节。在未压缩的表中，十个未压缩的溢出页占用 160K 字节。在具有 8K 页大小的压缩表中，它们只占用 80K 字节。因此，对于具有长列值的表，通常更有效使用压缩表格式。

对于 file-per-table 表空间，使用 16K 压缩页大小可以减少BLOB、VARCHAR或TEXT列的存储和 I/O 成本，因为这些数据通常压缩效果良好，可能需要较少的溢出页，即使 B 树节点本身占用的页数与未压缩形式相同。通用表空间不支持 16K 压缩页大小（KEY_BLOCK_SIZE）。有关更多信息，请参见 Section 17.6.3.3，“通用表空间”。

压缩和 InnoDB 缓冲池

在压缩的InnoDB表中，每个压缩页（无论是 1K、2K、4K 还是 8K）对应于一个 16K 字节的未压缩页（如果设置了innodb_page_size，则大小可能更小）。要访问页中的数据，MySQL 从磁盘读取压缩页（如果尚未在缓冲池中），然后将页解压缩为其原始形式。本节描述了InnoDB在处理压缩表的页时如何管理缓冲池。

为了最小化 I/O 并减少解压缩页的需求，有时缓冲池同时包含数据库页的压缩和未压缩形式。为了为其他所需的数据库页腾出空间，MySQL 可以从缓冲池中驱逐一个未压缩页，同时保留压缩页在内存中。或者，如果某个页已经有一段时间没有被访问，该页的压缩形式可能会被写入磁盘，以释放其他数据的空间。因此，在任何给定时间，缓冲池可能同时包含页的压缩和未压缩形式，或仅包含页的压缩形式，或两者都不包含。

MySQL 使用最近最少使用（LRU）列表跟踪要保留在内存中的页面和要驱逐的页面，以便热（频繁访问）数据倾向于保留在内存中。当访问压缩表时，MySQL 使用自适应 LRU 算法在内存中实现压缩和未压缩页面的适当平衡。这种自适应算法对系统是否以 I/O 绑定或 CPU 绑定方式运行敏感。目标是在 CPU 繁忙时避免花费过多的处理时间解压页面，并在 CPU 有空闲周期可以用于解压已经在内存中的压缩页面时避免做过多的 I/O。当系统处于 I/O 绑定状态时，该算法更倾向于驱逐页面的未压缩副本而不是两者，以便为其他磁盘页面腾出更多的内存空间。当系统处于 CPU 绑定状态时，MySQL 更倾向于同时驱逐压缩和未压缩页面，以便更多的内存用于“热”页面，并减少仅以压缩形式在内存中解压数据的需求。

压缩和 InnoDB 重做日志文件

在将压缩页写入数据文件之前，MySQL 会将页面的副本写入重做日志（如果自上次写入数据库以来已重新压缩）。这样做是为了确保即使在zlib库升级并且该更改引入与压缩数据不兼容的问题的极少数情况下，重做日志也可以用于崩溃恢复。因此，在使用压缩时，可以预期日志文件的大小会增加，或者需要更频繁的检查点。日志文件大小或检查点频率的增加量取决于压缩页被修改的次数，这些修改需要重新组织和重新压缩。

要在每个表的表空间中创建一个压缩表，必须启用innodb_file_per_table。在创建一个压缩表时，不依赖于innodb_file_per_table设置在一般表空间中。有关更多信息，请参见第 17.6.3.3 节，“一般表空间”。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-performance-compression-oltp.html

17.9.1.6 OLTP 工作负载的压缩

传统上，InnoDB的压缩功能主要推荐用于只读或读最多的工作负载，例如在数据仓库配置中。随着 SSD 存储设备的兴起，这些设备速度快但相对较小且昂贵，使压缩对于OLTP工作负载也变得有吸引力：高流量、互动式网站可以通过使用频繁进行INSERT、UPDATE和DELETE操作的应用程序与压缩表来减少其存储需求和每秒的 I/O 操作（IOPS）。

这些配置选项允许您调整压缩方式，以适应特定的 MySQL 实例，重点放在写入密集型操作的性能和可伸缩性上：

innodb_compression_level 允许您调整压缩程度。较高的值可以让您在存储设备上容纳更多数据，但会增加压缩时的 CPU 开销。较低的值可以减少 CPU 开销，当存储空间不是关键问题，或者您预计数据不容易压缩时。
innodb_compression_failure_threshold_pct 指定在更新压缩表时的压缩失败的截止点。当超过此阈值时，MySQL 开始在每个新的压缩页面内留下额外的空闲空间，动态调整空闲空间的量，直到达到innodb_compression_pad_pct_max指定的页面大小百分比。
innodb_compression_pad_pct_max 允许您调整每个页内保留的空间量，用于记录压缩行的更改，而无需重新压缩整个页面。值越高，可以记录更多更改而无需重新压缩页面。当运行时指定的压缩操作“失败”达到指定百分比时，MySQL 为每个压缩表内的页面使用可变数量的空闲空间，需要昂贵的操作来拆分压缩页面。
innodb_log_compressed_pages 允许您禁用将重新压缩的页面的图像写入重做日志。当对压缩数据进行更改时，可能会发生重新压缩。默认情况下启用此选项，以防止在恢复过程中使用不同版本的zlib压缩算法时可能发生的损坏。如果您确定zlib版本不会更改，请禁用innodb_log_compressed_pages以减少修改压缩数据的工作负载生成的重做日志。

因为使用压缩数据有时需要同时在内存中保留页面的压缩和未压缩版本，所以在使用压缩与 OLTP 风格的工作负载时，准备增加innodb_buffer_pool_size配置选项的值。

译文：dev.mysql.com/doc/refman/8.0/en/innodb-compression-syntax-warnings.html

17.9.1.7 SQL 压缩语法警告和错误

本节描述了在使用表格压缩功能时可能遇到的语法警告和错误，其中包括 file-per-table 表空间和 general tablespaces。

文件-每表表空间的 SQL 压缩语法警告和错误

当innodb_strict_mode被启用（默认情况下），在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED或KEY_BLOCK_SIZE会产生以下错误，如果innodb_file_per_table被禁用。

ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

注意

如果当前配置不允许使用压缩表，表格将不会被创建。

当innodb_strict_mode被禁用时，在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED或KEY_BLOCK_SIZE会产生以下警告，如果innodb_file_per_table被禁用。

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.        |
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4\.                            |
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=DYNAMIC.                          |
+---------+------+---------------------------------------------------------------+

注意

这些消息只是警告，而不是错误，表格是在没有压缩的情况下创建的，就好像没有指定选项一样。

“非严格”行为允许您将mysqldump文件导入不支持压缩表的数据库，即使源数据库包含压缩表。在这种情况下，MySQL 会创建表格为ROW_FORMAT=DYNAMIC，而不是阻止操作。

要将转储文件导入新数据库，并使表格按照原始数据库中的存在方式重新创建，请确保服务器具有innodb_file_per_table配置参数的正确设置。

属性KEY_BLOCK_SIZE仅在指定为COMPRESSED或省略时才被允许。在任何其他ROW_FORMAT中指定KEY_BLOCK_SIZE会生成一个警告，您可以通过SHOW WARNINGS查看。但是，表格是非压缩的；指定的KEY_BLOCK_SIZE会被忽略。

等级	代码	消息
警告	1478	`InnoDB: ignoring KEY_BLOCK_SIZE=`n` unless ROW_FORMAT=COMPRESSED.`

如果您启用了innodb_strict_mode，将KEY_BLOCK_SIZE与除COMPRESSED之外的任何ROW_FORMAT组合会产生错误，而不是警告，并且表格不会被创建。

表 17.12，“ROW_FORMAT 和 KEY_BLOCK_SIZE 选项”概述了与CREATE TABLE或ALTER TABLE一起使用的ROW_FORMAT和KEY_BLOCK_SIZE选项。

表 17.12 ROW_FORMAT 和 KEY_BLOCK_SIZE 选项

选项	使用说明	描述
`ROW_FORMAT=REDUNDANT`	MySQL 5.0.3 之前使用的存储格式	比`ROW_FORMAT=COMPACT`效率低；用于向后兼容性
`ROW_FORMAT=COMPACT`	MySQL 5.0.3 以来的默认存储格式	在聚簇索引页中存储长列值的前 768 字节，其余字节存储在溢出页中
`ROW_FORMAT=DYNAMIC`		如果适合，则在聚簇索引页内存储值；如果不适合，则仅存储一个 20 字节指向溢出页的指针（无前缀）
`ROW_FORMAT=COMPRESSED`		使用 zlib 对表和索引进行压缩
`KEY_BLOCK_SIZE=`n``		指定压缩页面大小为 1、2、4、8 或 16 千字节；意味着`ROW_FORMAT=COMPRESSED`。对于一般表空间，不允许`KEY_BLOCK_SIZE`值等于`InnoDB`页面大小。

表 17.13，“在关闭 InnoDB 严格模式时 CREATE/ALTER TABLE 的警告和错误”总结了在CREATE TABLE或ALTER TABLE语句中某些配置参数和选项的特定组合导致的错误条件，以及这些选项在SHOW TABLE STATUS输出中的显示方式。

当innodb_strict_mode为OFF时，MySQL 创建或修改表，但会忽略如下设置。您可以在 MySQL 错误日志中看到警告消息。当innodb_strict_mode为ON时，这些指定的选项组合会生成错误，并且表不会被创建或修改。要查看错误条件的完整描述，请发出SHOW ERRORS语句：例如：

mysql> `CREATE TABLE x (id INT PRIMARY KEY, c INT)` 
-> `ENGINE=INNODB KEY_BLOCK_SIZE=33333;` 
ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478)

mysql> `SHOW ERRORS;`
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Error | 1478 | InnoDB: invalid KEY_BLOCK_SIZE=33333\.     |
| Error | 1005 | Can't create table 'test.x' (errno: 1478) |
+-------+------+-------------------------------------------+

表 17.13 在关闭 InnoDB 严格模式时 CREATE/ALTER TABLE 的警告和错误

语法	警告或错误条件	在`SHOW TABLE STATUS`中显示的`ROW_FORMAT`结果
`ROW_FORMAT=REDUNDANT`	无	`REDUNDANT`
`ROW_FORMAT=COMPACT`	无	`COMPACT`
指定了`ROW_FORMAT=COMPRESSED`或`ROW_FORMAT=DYNAMIC`或`KEY_BLOCK_SIZE`	除非启用了`innodb_file_per_table`，否则对于每个表的文件表空间被忽略。通用表空间支持所有行格式。请参见第 17.6.3.3 节，“General Tablespaces”。	`文件表空间的默认行格式；通用表空间的指定行格式`
指定了无效的`KEY_BLOCK_SIZE`（不是 1、2、4、8 或 16）	`KEY_BLOCK_SIZE`被忽略	指定的行格式，或默认行格式
指定了`ROW_FORMAT=COMPRESSED`和有效的`KEY_BLOCK_SIZE`	无；使用指定的`KEY_BLOCK_SIZE`	`COMPRESSED`
指定了`KEY_BLOCK_SIZE`与`REDUNDANT`、`COMPACT`或`DYNAMIC`行格式	`KEY_BLOCK_SIZE`被忽略	`REDUNDANT`、`COMPACT`或`DYNAMIC`
`ROW_FORMAT`不是`REDUNDANT`、`COMPACT`、`DYNAMIC`或`COMPRESSED`之一	如果被 MySQL 解析器识别则被忽略。否则，会发出错误。	默认行格式或 N/A

当innodb_strict_mode为ON时，MySQL 会拒绝无效的ROW_FORMAT或KEY_BLOCK_SIZE参数并发出错误。严格模式默认为ON。当innodb_strict_mode为OFF时，MySQL 对被忽略的无效参数发出警告而不是错误。

使用SHOW TABLE STATUS无法查看所选的KEY_BLOCK_SIZE。语句SHOW CREATE TABLE显示KEY_BLOCK_SIZE（即使在创建表时被忽略）。MySQL 无法显示表的真实压缩页大小。

通用表空间的 SQL 压缩语法警告和错误

如果在创建通用表空间时未定义FILE_BLOCK_SIZE，则该表空间不能包含压缩表。如果尝试添加压缩表，则会返回错误，如下例所示：

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPRESSED
       KEY_BLOCK_SIZE=8;
ERROR 1478 (HY000): InnoDB: Tablespace `ts1` cannot contain a COMPRESSED table

尝试向通用表空间添加具有无效KEY_BLOCK_SIZE的表会返回错误，如下例所示：

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED
       KEY_BLOCK_SIZE=4;
ERROR 1478 (HY000): InnoDB: Tablespace `ts2` uses block size 8192 and cannot
contain a table with physical page size 4096

对于通用表空间，表的KEY_BLOCK_SIZE必须等于表空间的FILE_BLOCK_SIZE除以 1024。例如，如果表空间的FILE_BLOCK_SIZE为 8192，则表的KEY_BLOCK_SIZE必须为 8。

尝试向配置为存储压缩表的通用表空间添加具有未压缩行格式的表会返回错误，如下例所示：

mysql> CREATE TABLESPACE `ts3` ADD DATAFILE 'ts3.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts3 ROW_FORMAT=COMPACT;
ERROR 1478 (HY000): InnoDB: Tablespace `ts3` uses block size 8192 and cannot
contain a table with physical page size 16384

innodb_strict_mode不适用于通用表空间。通用表空间的表空间管理规则严格执行，与innodb_strict_mode无关。有关更多信息，请参见第 15.1.21 节，“CREATE TABLESPACE Statement”。

有关在通用表空间中使用压缩表的更多信息，请参阅第 17.6.3.3 节，“通用表空间”。

17.9.2 InnoDB 页面压缩

原文：dev.mysql.com/doc/refman/8.0/en/innodb-page-compression.html

InnoDB支持位于 file-per-table 表空间中的表的页面级压缩。此功能称为透明页压缩。通过在CREATE TABLE或ALTER TABLE中指定COMPRESSION属性来启用页面压缩。支持的压缩算法包括Zlib和LZ4。

支持的平台

页面压缩需要稀疏文件和空洞打孔支持。在 Windows 上，页面压缩受 NTFS 支持，在以下 MySQL 支持的 Linux 平台子集上，内核级别提供空洞打孔支持：

RHEL 7 及使用内核版本 3.10.0-123 或更高的衍生发行版
OEL 5.10（UEK2）内核版本 2.6.39 或更高
OEL 6.5（UEK3）内核版本 3.8.13 或更高
OEL 7.0 内核版本 3.8.13 或更高
SLE11 内核版本 3.0-x
SLE12 内核版本 3.12-x
OES11 内核版本 3.0-x
Ubuntu 14.0.4 LTS 内核版本 3.13 或更高
Ubuntu 12.0.4 LTS 内核版本 3.2 或更高
Debian 7 内核版本 3.2 或更高

注意

给定 Linux 发行版的所有可用文件系统可能不支持空洞打孔。

页面压缩的工作原理

当写入页面时，使用指定的压缩算法对其进行压缩。压缩后的数据写入磁盘，其中空洞打孔机制释放页面末尾的空块。如果压缩失败，则按原样写出数据。

Linux 系统上的空洞打孔大小

在 Linux 系统上，文件系统块大小是用于空洞打孔的单位大小。因此，只有当页面数据可以压缩到小于或等于InnoDB页面大小减去文件系统块大小时，页面压缩才有效。例如，如果innodb_page_size=16K且文件系统块大小为 4K，则页面数据必须压缩到小于或等于 12K 才能进行空洞打孔。

Windows 系统上的空洞打孔大小

在 Windows 系统上，稀疏文件的基础架构基于 NTFS 压缩。空洞打孔大小是 NTFS 压缩单元，它是 NTFS 群集大小的 16 倍。群集大小及其压缩单元如下表所示：

表 17.14 Windows NTFS 群集大小和压缩单元

群集大小	压缩单元
512 字节	8 KB
1 KB	16 KB
2 KB	32 KB
4 KB	64 KB

Windows 系统上的页面压缩仅在页面数据可以压缩到小于或等于InnoDB页面大小减去压缩单元大小时才有效。

默认的 NTFS 簇大小为 4KB，压缩单元大小为 64KB。这意味着对于开箱即用的 Windows NTFS 配置，页面压缩没有任何好处，因为最大的innodb_page_size也是 64KB。

要使页面压缩在 Windows 上工作，文件系统必须使用小于 4K 的簇大小，并且innodb_page_size必须至少是压缩单元大小的两倍。例如，要使页面压缩在 Windows 上工作，可以使用 512 字节的簇大小（具有 8KB 的压缩单元）构建文件系统，并使用大于 16K 的innodb_page_size值初始化InnoDB。

启用页面压缩

要启用页面压缩，请在CREATE TABLE语句中指定COMPRESSION属性。例如：

CREATE TABLE t1 (c1 INT) COMPRESSION="zlib";

您还可以在ALTER TABLE语句中启用页面压缩。但是，ALTER TABLE ... COMPRESSION仅更新表空间压缩属性。设置新的压缩算法后对表空间的写入使用新设置，但要将新的压缩算法应用于现有页面，必须使用OPTIMIZE TABLE重建表。

ALTER TABLE t1 COMPRESSION="zlib";
OPTIMIZE TABLE t1;

禁用页面压缩

要禁用页面压缩，请使用ALTER TABLE设置COMPRESSION=None。设置COMPRESSION=None后对表空间的写入不再使用页面压缩。要取消现有页面的压缩，必须在设置COMPRESSION=None后使用OPTIMIZE TABLE重建表。

ALTER TABLE t1 COMPRESSION="None";
OPTIMIZE TABLE t1;

页面压缩元数据

页面压缩元数据位于信息模式INNODB_TABLESPACES表中，以下列中：

FS_BLOCK_SIZE：文件系统块大小，用于空洞打孔的单位大小。
FILE_SIZE：文件的表面大小，表示未压缩的文件的最大大小。
ALLOCATED_SIZE：文件的实际大小，即磁盘上分配的空间量。

注意

在类 Unix 系统上，ls -l *tablespace_name*.ibd显示文件的表面文件大小（等同于FILE_SIZE）以字节为单位。要查看磁盘上分配的实际空间量（等同于ALLOCATED_SIZE），请使用du --block-size=1 *tablespace_name*.ibd。--block-size=1选项以字节而不是块打印分配的空间，以便与ls -l输出进行比较。

使用 SHOW CREATE TABLE 查看当前页面压缩设置 (Zlib, Lz4, 或 None)。一个表可能包含具有不同压缩设置的页面。

在以下示例中，从信息模式 INNODB_TABLESPACES 表中检索了员工表的页面压缩元数据。

# Create the employees table with Zlib page compression

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
) COMPRESSION="zlib";

# Insert data (not shown)

# Query page compression metadata in INFORMATION_SCHEMA.INNODB_TABLESPACES

mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM
       INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='employees/employees'\G
*************************** 1\. row ***************************
SPACE: 45
NAME: employees/employees
FS_BLOCK_SIZE: 4096
FILE_SIZE: 23068672
ALLOCATED_SIZE: 19415040

员工表的页面压缩元数据显示，表面文件大小为 23068672 字节，而实��文件大小（带有页面压缩）为 19415040 字节。文件系统块大小为 4096 字节，这是用于打孔的块大小。

识别使用页面压缩的表

要识别启用页面压缩的表，可以检查信息模式 TABLES 表的 CREATE_OPTIONS 列，查看定义了 COMPRESSION 属性的表：

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES 
       WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
+------------+--------------+--------------------+
| TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS     |
+------------+--------------+--------------------+
| employees  | test         | COMPRESSION="zlib" |
+------------+--------------+--------------------+

SHOW CREATE TABLE 还显示了如果使用的话的 COMPRESSION 属性。

页面压缩的限制和使用注意事项

如果文件系统块大小（或 Windows 上的压缩单元大小）* 2 > innodb_page_size，则禁用页面压缩。
不支持位于共享表空间中的表进行页面压缩，这包括系统表空间、临时表空间和一般表空间。
不支持撤销日志表空间进行页面压缩。
不支持重做日志页面进行页面压缩。
用于空间索引的 R 树页面不进行压缩。
属于压缩表 (ROW_FORMAT=COMPRESSED) 的页面保持不变。
在恢复期间，更新的页面以未压缩形式写出。
在不支持使用的压缩算法的服务器上加载页面压缩的表空间会导致 I/O 错误。
在降级到不支持页面压缩的早期版本的 MySQL 之前，需要取消对使用页面压缩功能的表进行解压缩。要对表进行解压缩，运行 ALTER TABLE ... COMPRESSION=None 和 OPTIMIZE TABLE。
如果在 Linux 和 Windows 服务器上都可用使用的压缩算法，则可以在页面压缩的表空间之间进行复制。
将一个页面压缩的表空间文件从一个主机移动到另一个主机时，需要使用一个能保留稀疏文件的实用程序来保留页面压缩。
在 Fusion-io 硬件上使用 NVMFS 可以实现更好的页面压缩效果，因为 NVMFS 设计用于利用打孔功能。
使用具有较大InnoDB页面大小和相对较小文件系统块大小的页面压缩功能可能导致写放大。例如，具有 64KB 的最大InnoDB页面大小和 4KB 文件系统块大小可能会提高压缩率，但也可能增加对缓冲池的需求，导致增加的 I/O 和潜在的写放大。

MySQL8-中文参考-七十一-

MySQL8 中文参考（七十一）

17.8.3.2 配置多个缓冲池实例

17.8.3.3 使缓冲池具有扫描抵抗力

17.8.3.4 配置 InnoDB 缓冲池预取（预读取）

17.8.3.5 配置缓冲池刷新

自适应刷新

限制空闲期间的缓冲区刷新

17.8.3.6 保存和恢复缓冲池状态

配置缓冲池页面转储百分比

在关闭时保存缓冲池状态并在启动时恢复

在线保存和恢复缓冲池状态

显示缓冲池转储进度

显示缓冲池加载进度

中止缓冲池加载操作

使用性能模式监视缓冲池加载进度

17.8.3.7 排除核心文件中的缓冲池页面

17.8.4 为 InnoDB 配置线程并发性

17.8.5 配置后台 InnoDB I/O 线程的数量

17.8.6 在 Linux 上使用异步 I/O

17.8.7 配置 InnoDB I/O 容量

在检查点忽略 I/O 容量

配置 I/O 容量最大值

17.8.8 配置自旋锁轮询

17.8.9 清理配置

配置清理线程

配置清理批量大小

配置最大清除延迟

清理和撤销表空间截断

17.8.10 为 InnoDB 配置优化器统计信息

17.8.10.1 配置持久性优化器统计参数

17.8.10.1.1 配置持久性优化器统计的自动计算

17.8.10.1.2 为单个表配置优化器统计参数

17.8.10.1.3 配置 InnoDB 优化器统计信息的采样页面数

17.8.10.1.4 在持久统计计算中包含已标记删除的记录

17.8.10.1.5 InnoDB 持久性统计表

17.8.10.1.6 InnoDB 持久统计表示例

17.8.10.1.7 使用 innodb_index_stats 表检索索引大小

17.8.10.2 配置非持久性优化器统计参数

优化器统计更新

配置采样页面数

17.8.10.3 估计 InnoDB 表的 ANALYZE TABLE 复杂性

17.8.11 配置索引页面的合并阈值

为表设置MERGE_THRESHOLD

为单个索引设置MERGE_THRESHOLD

查询索引的MERGE_THRESHOLD值

测量MERGE_THRESHOLD设置的效果

17.8.12 启用专用 MySQL 服务器的自动配置

17.9 InnoDB 表和页面压缩

17.9.1 InnoDB 表压缩

17.9.1.1 表压缩概述

17.9.1.2 创建压缩表

在文件表表空间中创建压缩表

在通用表空间中创建压缩表

注意

压缩表的限制

17.9.1.3 调整 InnoDB 表的压缩

何时使用压缩

数据特征和压缩

数据库压缩与应用程序压缩

在数据库中进行压缩

在应用程序中进行压缩

混合方法

工作负载特征和压缩

配置特性和压缩

选择压缩页大小

17.9.1.4 在运行时监控 InnoDB 表压缩

17.9.1.5 InnoDB 表的压缩工作原理

压缩算法

InnoDB 数据存储和压缩

B 树页面的压缩

压缩 BLOB、VARCHAR 和 TEXT 列

压缩和 InnoDB 缓冲池

压缩和 InnoDB 重做日志文件

17.9.1.6 OLTP 工作负载的压缩

17.9.1.7 SQL 压缩语法警告和错误

文件-每表表空间的 SQL 压缩语法警告和错误

通用表空间的 SQL 压缩语法警告和错误

17.9.2 InnoDB 页面压缩

支持的平台

17.8.10.1.7 使用 `innodb_index_stats` 表检索索引大小

为表设置`MERGE_THRESHOLD`

为单个索引设置`MERGE_THRESHOLD`

查询索引的`MERGE_THRESHOLD`值

测量`MERGE_THRESHOLD`设置的效果