InnoDB：InnoDB 内存结构
跳到导航
跳到搜索
Buffer Pool（缓冲池）
缓冲池是主存储器中的一个区域，InnoDB 在访问表和索引数据时会对其进行缓存。缓冲池允许直接从内存中处理经常使用的数据，从而加快了处理速度。在专用服务器上，通常将多达 80％的物理内存分配给缓冲池。
为了提高大容量读取操作的效率，缓冲池被分为pages，它们可以潜在地容纳多行。为了提高缓存管理的效率，缓冲池被实现为页面的链接列表。使用LRU算法的变体将很少使用的数据从缓存中老化掉。
【知道如何利用缓冲池将经常访问的数据保留在内存中是 MySQL 优化的重要方面。】
缓冲池 LRU 算法
使用最近最少使用（LRU）算法的变体将缓冲池作为列表进行管理：当需要空间以将新页面添加到缓冲池时，将驱逐最近使用最少的页面，并将新页面添加到列表的中间。
此“中点插入”策略将列表视为两个子列表：
- 最前面是最近访问过的新(“young”)页面的子列表。
- 在末尾，是最近访问过的旧页面的子列表。
如上，该算法将大量页面保留在新的子列表中。旧的子列表包含较少使用的页面。这些页面是eviction的候选页面。
默认情况下，该算法的操作如下：
- 缓冲池的 3/8 专用于旧的子列表。
- 列表的中点是新子列表的尾部与旧子列表的头相交的边界。
- 当 InnoDB 将页面读入缓冲池时，它最初将其插入中点（旧子列表的头部）。
- 页面可以被读取，因为它是用户启动的操作（例如 SQL 查询）所必需的，或者是 InnoDB 自动执行的 read-ahead 操作的一部分。
- 访问旧子列表中的页面会使其“young”，并将其移至新子列表的开头。
- 如果由于用户启动的操作而需要读取页面，则将立即进行首次访问，并使页面年轻。如果由于预读操作而读取了该页面，则第一次访问不会立即发生，并且在退出该页面之前可能根本不会发生。
- 随着数据库的运行，通过移至列表的末尾，未访问缓冲池中的页面“变旧”。新的和旧的子列表中的页面都会随着其他页面的更新而老化。随着将页面插入中点，旧子列表中的页面也会老化。最终，未使用的页面到达旧子列表的尾部并被逐出。
默认情况下，查询读取的页面会立即移入新的子列表，这意味着它们在缓冲池中停留的时间更长。
- 例如，针对 mysqldump 操作或不具有 WHERE 子句的 SELECT 语句执行的表扫描可以将大量数据带入缓冲池，并逐出相同数量的旧数据，即使新数据不再使用也是如此。
同样，由预读后台线程加载且仅访问一次的页面将移至新列表的开头。这些情况可能会将常用页面推送到旧的子列表，在此它们将被逐出。
缓冲池配置【？】
- 理想情况下，您可以将缓冲池的大小设置为与实际一样大的值，从而为服务器上的其他进程留出足够的内存以运行而不会进行过多的分页。缓冲池越大，则 InnoDB 就像内存数据库一样，从磁盘读取一次数据，然后在后续读取过程中从内存访问数据。
- 在具有足够内存的 64 位系统上，可以将缓冲池分成多个部分，以最大程度地减少并发操作之间的内存结构争用。
- 您可以将频繁访问的数据保留在内存中，而不管操作中突然出现的活动高峰如何将大量不经常访问的数据带入缓冲池中。
- 您可以控制何时以及如何执行预读请求，以异步方式将页面预取到缓冲池中，从而预期很快将需要这些页面。
- 您可以控制何时进行后台冲洗，以及是否根据工作负荷动态调整冲洗速率。
- 您可以配置 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 标准监视器输出以来经过的时间。
| 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（更改缓冲区）
更改缓冲区是一种特殊的数据结构：
- 当辅助索引页不在缓冲池中时，它会缓存这些页的更改。【缓存二级索引的更改】
- 稍后当这些页面被其他读取操作加载到缓冲池时，这些更改将被合并。【合并二级索引缓存】
- 缓冲的更改可能是由“INSERT”，“UPDATE”或“DELETE”操作（DML）导致。
与聚集索引不同，二级索引通常是非唯一的，二级索引中的插入是以相对随机的顺序进行的。类似地，删除和更新可能会影响索引树中不相邻的辅助索引页。稍后，当受影响的页被其他操作读入缓冲池时，合并缓存的更改可避免从磁盘将辅助索引页读入缓冲池所需的大量随机访问I/O。
- 即：【“缓存二级索引的更改，合并二级索引缓存”将避免二级索引（由于非唯一性、随机顺序更改，所造成的大量的）随机I/O】
NOTE：
- 在系统大部分处于空闲状态或缓慢关闭期间运行的清除操作会定期将更新的索引页写入磁盘。
- 与将每个值立即写入磁盘相比，清除操作可以更有效地为一系列索引值写入磁盘块。
- 当有许多受影响的行和许多辅助索引要更新时，“Change Buffer”合并可能需要几个小时。在此期间，磁盘 I/O 会增加，这可能会导致磁盘绑定查询的速度大大降低。
- 提交事务之后，甚至在服务器关闭并重新启动之后，“Change Buffer”合并也可能继续发生。
- “Change Buffer”的结构：
- 在内存中，更改缓冲区占用了缓冲池的一部分。
- 在磁盘上，更改缓冲区是系统表空间的一部分，当数据库服务器关闭时，索引更改将存储在其中。
- 如：“InnoDB：InnoDB 体系结构”中所示。
- “Change Buffer”中缓存的数据类型由“innodb_change_buffering”变量控制。
- “Change Buffer”不支持“索引中包含降序索引列”和“主键中包含降序索引列”的辅助索引。
配置“Change Buffer”
当对表执行“INSERT”，“UPDATE”或“DELETE”操作时，索引列的值（尤其是辅助键的值）通常处于未排序的顺序，需要大量的 I/O 才能使辅助索引保持最新状态。
- 当相关页不在缓冲池中时，change buffer 缓存对辅助索引项的更改，从而避免了昂贵的I/O操作，因为它不会立即从磁盘读取页。
- 当页面加载到缓冲池中时，缓冲的更改将被合并，更新的页面随后将刷新到磁盘。
- InnoDB 主线程在服务器接近空闲时和 slow shutdown 期间合并缓冲的更改。
- 由于更改缓冲区功能可以减少磁盘读写操作，因此它对于受 I/O 约束的工作负载（例如批量插入之类，具有大量 DML 操作的应用程序）最有价值。
- 然而更改缓冲区占用了缓冲池的一部分，从而减少了可用于缓存数据页的内存。
- 如果工作集几乎都位于缓冲池，或者表的辅助索引相对较少，那么禁用更改缓冲可能很有用。
- 如果工作数据集完全位于缓冲池，则更改缓冲不会带来额外的开销，因为它仅适用于不在缓冲池中的页面。
“innodb_change_buffering”：使用该配置参数来控制 InnoDB 执行更改缓冲的程度。
- 您可以为“插入”，“删除”操作（索引记录最初标记为删除时）和“清除”操作（当索引记录被物理删除时）启用或禁用缓冲。“更新”操作是“插入”和“删除”的组合。【？？？？？？？】
- 允许的“innodb_change_buffering”值包括：
| Value | Description |
|---|---|
| all | 默认值。缓冲“插入”，“删除标记”和“清除”操作。 |
| none | 不要缓冲任何操作。 |
| inserts | 缓冲“插入”操作。 |
| deletes | 缓冲“删除标记”操作。 |
| changes | 缓冲“插入”和“删除标记”操作。 |
| purges | 缓冲在后台发生的物理删除操作。 |
- 可以在 MySQL 配置文件（“my.cnf”或“my.ini”）中设置“innodb_change_buffering”参数，或使用“SET GLOBAL”语句动态更改它（该语句需要足够的权限来设置全局系统变量）。
- 更改设置会影响新操作的缓冲，现有缓冲项的合并不受影响。
配置“Change Buffer”最大大小
“innodb_change_buffer_max_size”变量允许将更改缓冲区的最大大小配置为缓冲池（Buffer Pool）总大小的百分比。
- 默认情况下，“innodb_change_buffer_max_size”设置为 25，最大设置为 50。
- 考虑在具有大量插入，更新和删除活动的 MySQL 服务器上增加“innodb_change_buffer_max_size”值，因为 Change Buffer 合并不能随新的 Change Buffer 项增加的话，将导致更改缓冲区达到其最大大小限制。
- 在“使用的静态数据用于报告的”或者“Change Buffer 占用了太多与缓冲池共享的内存空间，从而导致页面比期望的更快地从缓冲池中老化的” MySQL 服务器上，考虑减少“innodb_change_buffer_max_size”。
- 使用代表性的工作负载测试不同的设置，以确定最佳配置。“innodb_change_buffer_max_size”设置是动态的，它允许修改设置而无需重新启动服务器。
监视更改缓冲区
以下选项可用于更改缓冲区监视：
- 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
- “INFORMATION_SCHEMA.INNODB_METRICS”表提供了在 InnoDB 标准监视器输出中找到的大多数数据点以及其他数据点。要查看更改缓冲区度量标准以及每个度量标准的描述，请发出以下查询：
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
- “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 提供更改缓冲区互斥锁 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 | +-------------------------------------------------------+---------+-------+
Adaptive Hash Index（自适应哈希索引）
自适应哈希索引功能使 InnoDB 能够在具有适当的工作负载和足够的缓冲池内存的系统上，像内存数据库一样执行操作，而不会牺牲事务功能或可靠性。
- 自适应哈希索引功能由“innodb_adaptive_hash_index”变量启用，或在服务器启动时由“--skip-innodb-adaptive-hash-index”禁用。
根据观察到的搜索模式，使用索引关键字的前缀构建哈希索引。该前缀可以是任何长度，并且可能只有 B 树中的一些值出现在哈希索引中。哈希索引是根据对经常访问的索引页面的需求而构建的。【？】
如果一个表几乎完全适合主内存，则哈希索引可以通过启用对任何元素的直接查找、并将索引值变成某种指针的方式来加快查询速度。 InnoDB 具有监视索引搜索的机制。如果 InnoDB 注意到查询可以从构建哈希索引中受益，则它会【自动】这样做。
在某些工作负载下，哈希索引查找的速度大大超过了监视索引查找和维护哈希索引结构的额外工作。在繁重的工作负载（例如多个并发连接）下，对自适应哈希索引的访问有时会成为争用的来源。使用“LIKE”运算符和“%”通配符的查询也往往没有好处。
对于无法从自适应哈希索引功能中受益的工作负载，将其关闭可减少不必要的性能开销。由于很难预先预测自适应哈希索引功能是否适合特定的系统和工作负载，因此请考虑启用和禁用该功能时运行基准测试。
- 与早期版本相比，MySQL 5.6 中的体系结构更改使它更适合禁用自适应哈希索引功能。
在 MySQL 5.7 中，自适应哈希索引功能已分区。每个索引都绑定到一个特定的分区，并且每个分区都受到单独的锁存器的保护。分区由“innodb_adaptive_hash_index_parts”变量控制。在较早的版本中，自适应哈希索引功能受单个闩锁的保护，这可能成为繁重工作负载下争用的来源。
- 默认情况下，“innodb_adaptive_hash_index_parts”变量设置为 8，最大设置为 512。
您可以在“SHOW ENGINE INNODB STATUS”输出的“SEMAPHORES”部分监视自适应哈希索引的使用和争用。
- 如果有许多线程在等待“btr0sea.c”中创建的 RW锁存，请考虑增加自适应哈希索引分区的数量或禁用自适应哈希索引功能。【？？？】
Log Buffer（日志缓冲区）
日志缓冲区是存储区域，用于保存要写入磁盘上的日志文件的数据。日志缓冲区的内容会定期刷新到磁盘。
- 日志缓冲区的大小由“innodb_log_buffer_size”变量定义。默认大小为 16MB。
- “innodb_flush_log_at_trx_commit”变量控制如何将日志缓冲区的内容写入磁盘并刷新到磁盘。
- “innodb_flush_log_at_timeout”变量控制日志刷新频率。
较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果您有更新，插入或删除许多行的事务，则增加日志缓冲区的大小可以节省磁盘 I/O。