【MySQL精通之路】InnoDB(5)-内存结构

总目录：

【MySQL精通之路】InnoDB存储引擎-CSDN博客

【MySQL精通之路】InnoDB(4)-架构图-CSDN博客

编辑

1 缓存池（Buffer Pool）

1.1 缓存池LRU算法

1.2 缓存区配置

1.3 使用InnoDB标准监视器监视缓存池

2 更改缓存（Change Buffer）

2.1 配置更改缓存

2.2 配置更改缓冲区百分比

2.3 监控更改缓冲区

3 自适应哈希索引（Adaptive Hash Index）

4 日志缓冲区（Log Buffer）

图片来自MySQL官方文档

1 缓存池（Buffer Pool）

缓存池是主内存中的一个区域，InnoDB在访问表和索引数据时会在该区域进行缓存。

缓存池允许直接从内存访问频繁使用的数据，这加快了处理速度。在专用服务器上，通常会将高达80%的物理内存分配给缓冲池。

为了提高高容量读取操作的效率，缓存池被划分为可能容纳多行的页面。为了提高缓存管理的效率，缓存池被实现为页面的链表；很少使用的数据使用最近最少使用（LRU）算法的变体从高速缓存中淘汰。

了解如何利用缓存池将频繁访问的数据保存在内存中是MySQL调优的一个重要方面。

1.1 缓存池LRU算法

Buffer Pool LRU Algorithm

使用LRU算法的变体将缓冲池作为列表进行管理。

当需要空间将新页面添加到缓存池时，会收回最近最少使用的页面，并将新页面增加到列表的中间。

此中点插入策略将列表视为两个子列表：

头部为最近访问的新（“年轻”）页面的子列表

尾部为最近访问次数较少的旧页面的子列表

图17.2 缓存池列表

该算法将频繁使用的页面保留在新的子列表中。旧的子列表包含不太频繁使用的页面；这些页面可能会被清除。

默认情况下，算法操作如下：

缓冲池的3/8用于旧的子列表。

列表的中点是新子列表的尾部与旧子列表的头部相交的边界。

当InnoDB将一个页面读取到缓冲池中时，它最初会将其插入中点（旧子列表的头）。可以读取页面，因为它是用户启动的操作（如SQL查询）所必需的，或者是InnoDB自动执行的预读操作的一部分。

访问旧子列表中的页面会使其“年轻”，并将其移动到新子列表的头部。如果由于用户启动的操作需要读取页面，则会立即进行第一次访问，并使页面年轻。如果页面是由于预读操作而读取的，则第一次访问不会立即发生，而且可能在页面被收回之前根本不会发生。

当数据库运行时，缓冲池中未被访问的页面会向列表的尾部移动，从而“老化”。新旧子列表中的页面都会随着其他页面的更新而老化。旧子列表中的页面也会随着页面插入中点而老化。最终，一个未使用的页面到达旧子列表的尾部并被逐出。

默认情况下，查询读取的页面会立即移动到新的子列表中，这意味着它们在缓存池中停留的时间更长。例如，为mysqldump操作或不带WHERE子句的SELECT语句执行的表扫描可以将大量数据带入缓存池，并收回等量的旧数据，即使新数据再也不用了。类似地，由预读后台线程加载并只访问一次的页面会移动到新列表的开头。这些情况可能会将经常使用的页面推送到旧的子列表中，在那里它们会被驱逐。有关优化此行为的信息，

请参阅“使缓存池抗扫描”和“配置InnoDB缓存池预取（预读）”。
InnoDB标准监视器输出包含缓存池和内存部分中关于缓存池LRU算法操作的几个字段。有关详细信息，请参阅使用InnoDB标准监视器监视缓存池。

1.2 缓存区配置

您可以配置缓存池的各个方面以提高性能

理想情况下，您可以将缓存池的大小设置为尽可能大的值，从而为服务器上的其他进程留下足够的内存来运行，而不需要过多的分页。

缓存池越大，InnoDB就越像内存中的数据库，从磁盘读取数据一次，然后在随后的读取过程中访问内存中的数据。

参见第17.8.3.1节“配置InnoDB缓存池大小”。

在具有足够内存的64位系统上，可以将缓存池拆分为多个部分，以最大限度地减少并发操作之间对内存结构的争用。

有关详细信息，请参阅第17.8.3.2节“配置多个缓存池实例”。

您可以将频繁访问的数据保留在内存中，而不管操作会将大量不常访问的数据带入缓存池的活动突然激增。

有关详细信息，请参阅第17.8.3.3节“使缓存池具有抗扫描性”。

您可以控制如何以及何时执行预读请求，以异步方式将页面预取到缓存池中，以应对即将到来的需求。

有关详细信息，请参阅第17.8.3.4节“配置InnoDB缓存池预取（预读）”。

您可以控制后台刷新发生的时间，以及是否根据工作负载动态调整刷新速率。

有关详细信息，请参阅第17.8.3.5节“配置缓存池刷新”。

您可以配置InnoDB如何保留当前缓存池状态，以避免服务器重新启动后的漫长预热期。

有关详细信息，请参阅第17.8.3.6节“保存和恢复缓冲池状态”。

1.3 使用InnoDB标准监视器监视缓存池

可以使用SHOW ENGINE INNODB STATUS访问的InnoDB Standard Monitor输出提供了有关缓存池操作的指标。缓存池指标位于InnoDB标准监视器输出的缓存池和内存部分：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

下表描述了InnoDB Standard Monitor报告的缓存池指标。

InnoDB Standard Monitor输出中提供的每秒平均值基于自上次打印InnoDB Standard监视器输出以来的运行时间。

Name	Description
Total memory allocated	以字节为单位分配给缓存池的总内存。
Dictionary memory allocated	为InnoDB数据字典分配的总内存（以字节为单位）。
Buffer pool size	分配给缓存池的总大小（以页为单位）。
Free buffers	缓存池可用列表的总大小（以页为单位）。
Database pages	缓存池LRU列表的总大小（以页面为单位）。
Old database pages	缓存池旧LRU子列表的总大小（以页面为单位）。
Modified db pages	缓存池中当前修改的页数。
Pending reads	等待读取到缓存池中的缓存池页面数。
Pending writes LRU	从LRU列表底部开始写入缓存池中的旧脏页数。
Pending writes flush list	列表在检查点期间要刷新的缓冲池页面数。
Pending writes single page	单页缓存池中挂起的独立页写入数。
Pages made young	缓存池LRU列表中变年轻的页面总数（移动到“新”页面的子列表的头）。
Pages made not young	缓存池LRU列表中未年轻的页面总数（保留在“旧”子列表中但未年轻的页）。
youngs/s	每秒平均访问缓存池LRU列表中导致页面变年轻的旧页面的次数。有关详细信息，请参阅此表后面的注释。
non-youngs/s	每秒平均访问缓存池LRU列表中的旧页面，导致页面不年轻。有关详细信息，请参阅此表后面的注释。
Pages read	从缓存池中读取的页面总数。
Pages created	缓存池中创建的页面总数。
Pages written	从缓存池中写入的页数。
reads/s	每秒平均读取缓存池页面的次数。
creates/s	每秒创建的缓存池页面的平均数。
writes/s	每秒缓存池页面写入的平均次数。
Buffer pool hit rate	命中率从缓存池读取的页面与从磁盘存储读取的页面的缓存池页面命中率。
young-making rate	页面访问导致页面年轻化的平均命中率。有关详细信息，请参阅此表后面的注释。
not (young-making rate)	页面访问未导致页面年轻化的平均命中率。有关详细信息，请参阅此表后面的注释。
Pages read ahead	每秒预读操作的平均值。
Pages evicted without access	每秒未经缓存池访问而被移出的页面的平均数。
Random read ahead	每秒随机预读操作的平均值。
LRU len	缓存池LRU列表的总大小（以页面为单位）。
unzip_LRU len	缓存池unzip _ RU列表的长度（以页为单位）。
I/O sum	访问的缓存池LRU列表页面的总数。
I/O cur	当前间隔内访问的缓存池LRU列表页的总数。
I/O unzip sum	解压缩的缓存池unzip _LRU列表页的总数。
I/O unzip cur	当前间隔内解压缩的缓冲池unzip _LRU列表页的总数。

注意：

youngs/s指标

仅适用于旧页面。它基于页面访问次数。一个给定的页面可以有多次访问，所有访问都会被计算在内。如果在没有进行大型扫描的情况下看到非常低的youngs/s值，请考虑减少延迟时间或增加用于旧子列表的缓存池的百分比。增加百分比会使旧的子列表更大，因此该子列表中的页面移动到尾部所需的时间更长，这会增加这些页面再次被访问并变得年轻的可能性。参见第17.8.3.3节“使缓冲池具有抗扫描性”。

non-youngs/s指标仅适用于旧页面。它基于页面访问次数。一个给定的页面可以有多次访问，所有访问都会被计算在内。如果在执行大型表扫描时没有看到更高的non-youngs/s值（以及更高的youngs/s值），请增加延迟值。参见第17.8.3.3节“使缓冲池具有抗扫描性”。

年轻的生成率考虑了所有缓冲池页面访问，而不仅仅是旧子列表中页面的访问。年轻页的命中率和非命中率通常不会加起来成为整个缓冲池的命中率。旧的子列表中的页面命中会导致页面移动到新的子列表，但只有当页面与列表头相距一定距离时，新子列表中页面命中才会导致页面移动到头。

not（young makeing rate）是由于不满足innodb_old_block_time定义的延迟，或者由于新子列表中的页面命中没有导致页面移动到头部，因此页面访问没有导致页面年轻的平均命中率。此速率将考虑所有缓冲池页面访问，而不仅仅是旧子列表中页面的访问。

2 更改缓存（Change Buffer）

交换缓存区是一种特殊的数据结构，当辅助索引页不在缓存池中时，它会缓存对这些页的更改。缓存的更改可能由INSERT、UPDATE或DELETE操作（DML）引起，稍后当页面通过其他读取操作加载到缓存池中时，这些更改将被合并。

图17.3更改缓存区

与聚集索引不同，二级索引通常是非唯一的，并且插入二级索引的顺序相对随机。类似地，删除和更新可能会影响索引树中不相邻的辅助索引页。

然后，当其他操作将受影响的页读取到缓存池中时，合并缓存的更改可以避免从磁盘将辅助索引页读取到缓存池中所需的大量随机访问I/O。

在系统大部分空闲或缓慢关闭时运行的清洗操作会定期将更新的索引页写入磁盘。

与立即将每个值写入磁盘相比，清洗操作可以更有效地写入一系列索引值的磁盘块。
当有许多受影响的行和许多要更新的辅助索引时，交换缓冲区合并可能需要几个小时。在此期间，磁盘I/O会增加，这可能会导致磁盘绑定查询的速度显著减慢。在提交事务之后，甚至在服务器关闭和重新启动之后，更改缓存区合并也可能继续发生

（有关更多信息，请参阅第17.213.3节“强制InnoDB恢复”）。

在内存中，更改缓存区占据了缓存池的一部分。在磁盘上，更改缓存区是系统表空间的一部分，当数据库服务器关闭时，索引更改将在其中缓存。

缓存在更改缓存区中的数据类型由innodb_change_buffering变量控制。

如果辅助索引包含降序索引列或主键包含降序索引列则不支持交换缓存。

有关更改缓冲区的常见问题解答，

请参阅第A.16节“MySQL 8.0常见问题解答：InnoDB更改缓存区”。

2.1 配置更改缓存

在表上执行INSERT、UPDATE和DELETE操作时，索引列的值（尤其是辅助关键字的值）通常按未排序的顺序排列，需要大量I/O才能使辅助索引保持最新。

当相关页面不在缓冲池中时，更改缓存区缓存对辅助索引项的更改，从而避免了昂贵的I/O操作，因为不会立即从磁盘读取页面。当页面加载到缓冲池中时，缓存的更改会合并，更新后的页面稍后会刷新到磁盘。当服务器接近空闲时，以及在缓慢关闭期间，InnoDB主线程合并缓存的更改。

因为它可以减少磁盘读取和写入，所以更改缓存对于I/O绑定的工作负载来说是最有价值的；例如，具有大量DML操作（如大容量插入）的应用程序受益于更改缓存。

但是，更改缓冲区占用了缓冲池的一部分，从而减少了可用于缓存数据页的内存。如果工作集几乎适合缓存池，或者表的辅助索引相对较少，则禁用更改缓存可能会很有用。如果工作数据集完全适合缓存池中，则更改缓存不会带来额外的开销，因为它只适用于不在缓存池中的页。

innodb_change_buffering变量控制innodb执行更改缓存的程度。您可以启用或禁用插入、删除操作（当索引记录最初标记为删除时）和清除操作（当物理删除索引记录时）的缓存。更新操作是插入和删除的组合。innodb_change_buffering的默认值为all。

允许的innodb_change_buffering值包括：

all

默认值：缓存区插入、删除标记操作和清除。

none

不要缓存任何操作。

inserts

缓存区插入操作。

deletes

缓存区删除标记操作。

changes

缓存插入和删除标记操作。

purges

缓存后台发生的物理删除操作。

您可以在MySQL选项文件（my.cnf或my.ini）中设置innodb_change_buffering变量，也可以使用SET GLOBAL语句动态更改它，这需要足够的权限来设置全局系统变量。

参见第7.1.9.1节“系统变量权限”。更改设置会影响新操作的缓冲；

现有缓冲条目的合并不受影响。

2.2 配置更改缓冲区百分比

innodb_change_buffer_max_size：

将更改缓冲区的最大值配置为缓冲池总大小的百分比。

默认情况下：innodb_change_buffer_max_size设置为25。最大设置为50。

考虑在具有大量插入、更新和删除操作的MySQL服务器上增加innodb_change_buffer_max_size，其中更改缓冲区合并与新的更改缓冲区条目不同步，导致更改缓冲区达到其最大大小限制。

考虑在具有用于报告的静态数据的MySQL服务器上减少innodb_change_buffer_max_size，或者如果更改缓冲区占用了与缓冲池共享的太多内存空间，导致页面提前从缓冲池中移出。

使用具有代表性的工作负载测试不同的设置，以确定最佳配置。

innodb_change_buffer_max_size变量是动态的，允许在不重启服务器的情况下修改设置。

2.3 监控更改缓冲区

以下选项可用于更改缓冲区监控：

InnoDB标准监视器输出包括更改缓冲区状态信息。

要查看监视器数据，请发出SHOW ENGINE INNODB STATUS语句。

mysql> SHOW ENGINE INNODB STATUS\G

更改缓冲区状态信息位于INSERT BUFFER AND ADAPTIVE HASH INDEX（插入缓冲区和自适应哈希索引）标题下，显示方式如下：

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s

有关更多信息：

【MySQL精通之路】SHOW ENGINE INNODB STATUS指标解释-CSDN博客

Information Schema库中的 INNODB_METRICS表：

提供了INNODB Standard Monitor输出中的大部分数据点以及其他数据点。要查看更改缓冲区指标及其描述，请发出以下查询：

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G

关于INNODB_METRICS表的内容参考：

【MySQL精通之路】InnoDB-INFORMATION_SCHEMA库Metrics表-CSDN博客

Information Schema库INNODB_BUFFER_PAGE表提供有关缓冲池中每个页面的元数据，包括更改缓冲区索引和更改缓冲区位图页面。

更改缓冲区页面由PAGE_TYPE标识。

IBUF_INDEX是更改缓冲区索引页的页类型

IBUF_BITMAP是更改缓冲位图页的页类别。

警告:
查询INNODB_BUFFER_PAGE表可能会带来显著的性能开销。为了避免影响性能，请在测试实例上重现要调查的问题，并在该测试实例上运行查询。

例如，可以查询INNODB_BUFFER_PAGE表，以确定IBUF_INDEX和IBUF_BITMAP页面的大致数量（占缓冲池页面总数的百分比）。

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100))
       AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                  25 |        8192 |                        0.3052 |
+---------------------+-------------+-------------------------------+

Performance Schema表为高级性能监视提供了更改缓冲区互斥等待检查。若要查看更改缓冲区检测，请发出以下查询：

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
+-------------------------------------------------------+---------+-------+

有关监视InnoDB互斥等待的信息，请参阅第17.16.2节“使用性能模式监视InnoDB mutex等待”。

3 自适应哈希索引（Adaptive Hash Index）

自适应哈希索引使InnoDB能够在具有适当的工作负载组合和足够的缓冲池内存的系统上执行更像内存中的数据库，而不会牺牲事务特性或可靠性。

自适应哈希索引由innodb_adaptive_hash_index变量启用

或在服务器启动时由--skip-innodb-adaptive-hash-index关闭。

根据观察到的搜索模式，使用索引键的前缀构建哈希索引。

前缀可以是任何长度，并且可能只有B树中的一些值出现在哈希索引中。

哈希索引是根据经常访问的索引页的需要构建的。

如果一个表几乎完全适合主内存，那么哈希索引可以通过直接查找任何元素来加快查询速度，将索引值变成某种指针。

InnoDB有一个监视索引搜索的机制。如果InnoDB注意到查询可以从构建哈希索引中受益，它会自动这样做。

博主PS：

我们知道表的数据是通过页组成为一颗B+树存放的，自适应哈希索引的意义就是监控哪些页经常被访问到，那么就把这些经常访问到的页使用hash索引，键值对的方式存放下来，下一次要找某页数据时，就不从B+树寻找了，直接hash判断页的位置。我们知道B+树本质是二分查找法。二分查找是永远没有hash算法直接计算数据位置来得快的。

对于某些工作负载，哈希索引查找的速度大大超过了监视索引查找和维护哈希索引结构的额外工作。在繁重的工作负载（例如多个并发联接）下，对自适应哈希索引的访问有时会成为争用的来源。使用LIKE运算符和%通配符的查询也往往没有好处。

对于没有从自适应哈希索引中获益的工作负载，关闭它可以减少不必要的性能开销。

由于很难提前预测自适应哈希索引是否适用于特定的系统和工作负载，请考虑在启用和禁用它的情况下运行基准测试。

对自适应哈希索引功能进行了分区。每个索引都绑定到一个特定的分区，每个分区都由一个单独的锁存器保护。分区由innodb_adaptive_hash_index_parts变量控制。