InnoDB：InnoDB 内存结构

Buffer Pool（缓冲池）【！！！】

缓冲池是主存储器中的一个区域，InnoDB 在访问表和索引数据时会对其进行缓存。缓冲池允许直接从内存中处理经常使用的数据，从而加快了处理速度。在专用服务器上，通常将多达 80％的物理内存分配给缓冲池。

为了提高大容量读取操作的效率，缓冲池被分为pages，它们可以潜在地容纳多行。为了提高缓存管理的效率，缓冲池被实现为页面的链接列表。使用LRU算法的变体将很少使用的数据从缓存中老化掉。

【知道如何利用缓冲池将经常访问的数据保留在内存中是 MySQL 优化的重要方面。】

缓冲池 LRU 算法

使用最近最少使用（LRU）算法的变体将缓冲池作为列表进行管理：当需要空间以将新页面添加到缓冲池时，将驱逐最近使用最少的页面，并将新页面添加到列表的中间。

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

1. 最前面是最近访问过的新(“young”)页面的子列表。
2. 在末尾，是最近访问过的旧页面的子列表。

如上，该算法将大量页面保留在新的子列表中。旧的子列表包含较少使用的页面。这些页面是eviction的候选页面。

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

• 缓冲池的 3/8 专用于旧的子列表。
• 列表的中点是新子列表的尾部与旧子列表的头相交的边界。
• 当 InnoDB 将页面读入缓冲池时，它最初将其插入中点（旧子列表的头部）。

  页面可以被读取，因为它是用户启动的操作（例如 SQL 查询）所必需的，或者是 InnoDB 自动执行的 read-ahead 操作的一部分。

• 访问旧子列表中的页面会使其“young”，并将其移至新子列表的开头。

  如果由于用户启动的操作而需要读取页面，则将立即进行首次访问，并使页面年轻。如果由于预读操作而读取了该页面，则第一次访问不会立即发生，并且在退出该页面之前可能根本不会发生。

• 随着数据库的运行，通过移至列表的末尾，未访问缓冲池中的页面“变旧”。新的和旧的子列表中的页面都会随着其他页面的更新而老化。随着将页面插入中点，旧子列表中的页面也会老化。最终，未使用的页面到达旧子列表的尾部并被逐出。

默认情况下，查询读取的页面会立即移入新的子列表，这意味着它们在缓冲池中停留的时间更长。

例如，针对 mysqldump 操作或不具有 WHERE 子句的 SELECT 语句执行的表扫描可以将大量数据带入缓冲池，并逐出相同数量的旧数据，即使新数据不再使用也是如此。

同样，由预读后台线程加载且仅访问一次的页面将移至新列表的开头。这些情况可能会将常用页面推送到旧的子列表，在此它们将被逐出。

缓冲池配置【？】

1. 理想情况下，您可以将缓冲池的大小设置为与实际一样大的值，从而为服务器上的其他进程留出足够的内存以运行而不会进行过多的分页。缓冲池越大，则 InnoDB 就像内存数据库一样，从磁盘读取一次数据，然后在后续读取过程中从内存访问数据。
2. 在具有足够内存的 64 位系统上，可以将缓冲池分成多个部分，以最大程度地减少并发操作之间的内存结构争用。
3. 您可以将频繁访问的数据保留在内存中，而不管操作中突然出现的活动高峰如何将大量不经常访问的数据带入缓冲池中。
4. 您可以控制何时以及如何执行预读请求，以异步方式将页面预取到缓冲池中，从而预期很快将需要这些页面。
5. 您可以控制何时进行后台冲洗，以及是否根据工作负荷动态调整冲洗速率。
6. 您可以配置 InnoDB 保留当前缓冲池状态的方式，以避免服务器重启后的漫长预热时间。

使用 InnoDB 标准监视器监视缓冲池【？？？】

可以使用“SHOW ENGINE INNODB STATUS”访问 InnoDB 标准监视器输出，它提供有关缓冲池操作的度量。

缓冲池度量标准位于 InnoDB Standard Monitor（InnoDB 标准监视器）输出的 BUFFER POOL AND MEMORY（缓冲池和内存）部分中，如下所示：

----------------------
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 标准监视器报告的缓冲池度量：

• InnoDB 标准监视器输出中提供的每秒平均值基于自上次打印 InnoDB 标准监视器输出以来经过的时间。

InnoDB 缓冲池度量

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	await 读入缓冲池的缓冲池页面数。
Pending writes LRU	从 LRU 列表的底部开始写入的缓冲池中的旧脏页数。
Pending writes flush list	检查点期间要刷新的缓冲池页面数。
Pending writes single page	缓冲池中暂挂的独立页面写入数。
Pages made young	在缓冲池 LRU 列表中变年轻的页面总数(移至“新”页面的子列表的开头)。
Pages made not young	缓冲池 LRU 列表中没有年轻的页面总数(保留在“旧”子列表中但没有年轻的页面)。
youngs/s	每秒平均访问缓冲池 LRU 列表中的旧页面所导致的页面年轻。有关更多信息，请参见此表后面的 Comments。
non-youngs/s	每秒平均访问缓冲池 LRU 列表中的旧页面导致的页面不年轻。有关更多信息，请参见此表后面的 Comments。
Pages read	从缓冲池读取的页面总数。
Pages created	在缓冲池中创建的页面总数。
Pages written	从缓冲池写入的页面总数。
reads/s	每秒平均每秒读取的缓冲池页面数。
creates/s	每秒平均创建的缓冲池页面的每秒数量。
writes/s	每秒平均缓冲池页面写入数。
Buffer pool hit rate	从缓冲池内存与磁盘存储读取的页面的缓冲池页面命中率。
young-making rate	页面访问的平均命中率使页面更年轻。有关更多信息，请参见此表后面的 Comments。
not (young-making rate)	页面访问未使页面变年轻的平均命中率。有关更多信息，请参见此表后面的 Comments。
Pages read ahead	预读操作的每秒平均数。
Pages evicted without access	每秒从缓冲池访问而未访问的页面的平均值。
Random read ahead	随机预读操作的每秒平均数。
LRU len	缓冲池 LRU 列表的页面总大小。
unzip_LRU len	缓冲池 unzip_LRU 列表的页面总大小。
I/O sum	最近 50 秒内访问的缓冲池 LRU 列表页面的总数。
I/O cur	已访问的缓冲池 LRU 列表页面的总数。
I/O unzip sum	已访问的缓冲池 unzip_LRU 列表页面的总数。
I/O unzip cur	已访问的缓冲池 unzip_LRU 列表页面的总数。

Notes :

• “youngs/s”度量仅适用于旧页面。它基于对页面的访问次数而不是页面数。可以对给定页面进行多次访问，所有访问都计入在内。如果没有大扫描时看到的“youngs/s”值很低，则可能需要减少延迟时间或增加用于旧子列表的缓冲池百分比。增加百分比会使旧的子列表变大，因此该子列表中的页面需要更长的时间才能移到尾部，这增加了再次访问这些页面并使它们变年轻的可能性。
• “non-youngs/s”度量仅适用于旧页面。它基于对页面的访问次数而不是页面数。可以对给定页面进行多次访问，所有访问都计入在内。如果执行大型表扫描时看不到较高的“non-youngs/s”值(和较高的“youngs/s”值)，请增加延迟值。
• “young-making”比率用于访问所有缓冲池页面，而不仅仅是访问旧子列表中的页面。 “young-making”比率和“not”比率通常不会加总到整个缓冲池命中率。旧子列表中的页面命中会导致页面移动到新的子列表，但是新子列表中的页面命中只会导致页面与列表的头部保持一定距离时才移动到列表的头部。
• “not (young-making rate)”是由于未满足“innodb_old_blocks_time”所定义的延迟，或者由于新子列表中的页面点击未导致页面移动到头部而导致页面访问未使页面变年轻的平均点击率。此速率说明了对所有缓冲池页面的访问，而不仅仅是访问旧子列表中的页面。

缓冲池服务器状态变量和“INNODB_BUFFER_POOL_STATS”表提供了与 InnoDB 标准监视器输出中许多相同的缓冲池度量标准。

Change Buffer（更改缓冲区）

更改缓冲区是一种特殊的数据结构，当这些页面不在 Buffer Pool（缓冲池）中时，该缓存可缓存对 secondary index页的更改（二级索引，区别于聚集索引）。

• 可能由“INSERT”，“UPDATE”或“DELETE”操作（DML）导致的缓冲更改将在以后通过其他读取操作将页面加载到缓冲池中时合并。

与 clustered indexes（聚集索引）不同，二级索引通常是不唯一的，并且二级索引中的插入以相对随机的 Sequences 发生。同样，删除和更新可能会影响索引树中不相邻的二级索引页。当稍后通过其他操作将受影响的页读入缓冲池时，合并缓存的更改将避免从磁盘将辅助索引页读入缓冲池所需的大量随机访问 I/O。

• 在系统大部分处于空闲状态或缓慢关闭期间运行的清除操作会定期将更新的索引页写入磁盘。与将每个值立即写入磁盘相比，清除操作可以更有效地为一系列索引值写入磁盘块。
• 当有许多受影响的行和许多辅助索引要更新时，更改缓冲区合并可能需要几个小时。在此期间，磁盘 I/O 会增加，这可能会导致磁盘绑定查询的速度大大降低。提交事务之后，甚至在服务器关闭并重新启动之后，更改缓冲区合并也可能继续发生。
• 在内存中，更改缓冲区占用了缓冲池的一部分。在磁盘上，更改缓冲区是系统表空间的一部分，当数据库服务器关闭时，索引更改将存储在其中。
• 更改缓冲区中缓存的数据类型由“innodb_change_buffering”变量控制。
• 如果索引包含降序索引列或主键包含降序索引列，则辅助索引不支持更改缓冲。【？】

配置更改缓冲

当对 table 执行“INSERT”，“UPDATE”或“DELETE”操作时，索引列的值（尤其是辅助键的值）通常处于未排序的 Sequences，需要大量的 I/O 才能使辅助索引保持最新状态。

1. 当相关的页面不在缓冲池中时，change buffer 缓存对辅助索引条目的更改，从而通过不立即从磁盘读取页面来避免昂贵的 I/O 操作。
2. 当页面加载到缓冲池中时，缓冲的更改将合并，更新的页面随后将刷新到磁盘。

InnoDB 主线程在服务器接近空闲时和 slow shutdown 期间合并缓冲的更改。

• 由于更改缓冲区功能可以减少磁盘读写操作，因此它对于受 I/O 约束的工作负载（例如，具有大量 DML 操作的应用程序，例如批量插入）最有价值。
• 但是，更改缓冲区占用了缓冲池的一部分，从而减少了可用于缓存数据页的内存。如果工作集几乎适合缓冲池，或者您的表具有相对较少的二级索引，则禁用更改缓冲可能很有用。如果工作数据集完全适合缓冲池，则更改缓冲不会带来额外的开销，因为它仅适用于不在缓冲池中的页面。

您可以使用“innodb_change_buffering”配置参数来控制 InnoDB 执行更改缓冲的程度。您可以为插入，删除操作（最初将索引记录标记为删除）和清除操作（物理删除索引记录）启用或禁用缓冲。更新操作是插入和删除的组合。

• “innodb_change_buffering”的默认值为“all”。

允许的“innodb_change_buffering”值包括：

Value	Description
all	默认值：缓冲区插入，删除标记操作和清除。
none	不要缓冲任何操作。
inserts	缓冲插入操作。
deletes	缓冲删除标记操作。
changes	缓冲插入和删除标记操作。
purges	缓冲在后台发生的物理删除操作。

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

• 更改设置会影响新操作的缓冲，现有缓冲条目的合并不受影响。

配置更改缓冲区最大大小

“innodb_change_buffer_max_size”变量允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比。

• 默认情况下，“innodb_change_buffer_max_size”设置为 25，最大设置为 50。

1. 考虑在具有大量插入，更新和删除活动的 MySQL 服务器上增加“innodb_change_buffer_max_size”，其中更改缓冲区合并不能与新的更改缓冲区条目保持同步，从而导致更改缓冲区达到其最大大小限制。
2. 考虑在 MySQL 服务器上使用用于报告的静态数据减少“innodb_change_buffer_max_size”，或者如果更改缓冲区占用了与缓冲池共享的太多内存空间，从而导致页面比期望的更快地超出缓冲池。
3. 使用代表性的工作负载测试不同的设置，以确定最佳配置。“innodb_change_buffer_max_size”设置是动态的，它允许修改设置而无需重新启动服务器。

监视更改缓冲区

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

1. InnoDB 标准监视器（Standard Monitor）输出包括更改缓冲区状态信息。要查看监视器数据，请发出“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
   

2. “INFORMATION_SCHEMA.INNODB_METRICS”表提供了在 InnoDB 标准监视器输出中找到的大多数数据点以及其他数据点。要查看更改缓冲区度量标准以及每个度量标准的描述，请发出以下查询：

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

3. “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 |
   +---------------------+-------------+-------------------------------+
   

4. Performance Schema 提供更改缓冲区互斥锁 await 检测，以进行高级性能监视。要查看更改缓冲区检测，请发出以下查询：

   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   |
   +-------------------------------------------------------+---------+-------+