目录
本章描述了 InnoDB 内存结构和相关话题。
15.5.1 缓冲池（Buffer Pool）
缓冲池 是主（内）存中 InnoDB 高速缓存（Cache） 其所访问的表和索引数据的一块区域。缓冲池允许直接从内存访问频繁使用的数据，这样可以加快处理速度。在专属服务器上，通常把高达 80% 的物理内存分配给缓冲池。
为了提高大量读操作的效率，将缓冲池划分为可潜在地容纳多行的页。为了提升高速缓存管理的效率，缓冲池以页的链表方式实现。使用 最近最少使用（LRU）算法的变体 ，将很少使用的数据从高速缓存中淘汰出去。
了解如何利用缓冲池来将频繁访问的数据保持在内存中是 MySQL 优化很重要的方面。
缓冲池LRU算法
缓冲池由一个使用 LRU 算法变体的列表管理。当需要空间来向缓冲池中添加新页时，逐出最近最少使用的页，添加新页到列表中间。这种中点插入策略将列表视作两个字列表：
- 位于头部，最近访问的新（“年轻”）的页的子列表
- 位于尾部，最近较少访问的旧页的子列表
图 15.2 缓冲池列表
该算法保存频繁使用的页到新子列表，将不常用的页保存到旧子列表，这些页面可能会被选举驱逐。
默认情况下，算法的操作如下：
- 缓冲池的 3/8 专用于旧子列表。
- 列表的中点是新列表尾部与旧列表头部相遇的边界。
- 当 InnoDB 读取一页到缓冲池时，它最初将其插入到中点（旧子列表的头部）。一个页面可由用户发起的需要该页数据的操作（如 SQL 查询），或作为 InnoDB 自动执行的预读操作所读取。
- 访问旧子列表的页将使它“年轻化”，将它移动到新子列表的头部。如果该页是由用户发起的操作读取的，立即产生第一次访问立即产生，且该页变得“年轻”。如果该页是由预读操作读取的，不立即产生第一次访问，而且可能在该页被逐出前从未产生。
- 随着数据库的运行，缓冲池中未访问的页逐渐“老化”，移向列表尾部。新、旧子列表中的页随着其他页的更新而老化。旧子列表还会随着新插入到中点的页而老化。最终，仍保持未使用的页因触及旧子列表的尾部而被逐出（缓冲池）。
默认情况下，查询访问的页立刻移动到新子列表，意味着它们可以在缓冲池中停留更长时间。例如，由 mysqldump 操作或 不含 WHERE 子句的SELECT 语句执行的表扫描，会将大量数据加载到缓冲池并逐出等量的旧数据，即使新数据从未再次使用。类似地，由预读后台线程加载并且只访问一次的页被移动到新子列表头。这些情况会将频繁使用的页推到会将它们逐出的旧子列表中。有关优化这种行为的信息，参阅 Section 15.8.3.3, “Making the Buffer Pool Scan Resistant”， 和 Section 15.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.
InnoDB 标准监控器输出 在 BUFFER POOL AND MEMORY 部分包含几个与缓冲池 LRU 算法相关的字段，详见 Monitoring the Buffer Pool Using the InnoDB Standard Monitor.
缓冲池配置
可以配置缓冲池的各个方面来提高性能：
- 理想情况下，在为其他进程留足内存来运行以避免过多分页的前提下，可以将缓冲池设置成尽可能大的值。缓冲池越大，InnoDB 表现得就越像内存数据库，从磁盘读取一次数据，后续读取全部从内存访问数据。查看 Section 15.8.3.1, “Configuring InnoDB Buffer Pool Size”.
- 在拥有足够内存的 64 位系统中，可将缓冲池分割为多个部分以最小化并发操作间的内存结构竞争。详见 Section 15.8.3.2, “Configuring Multiple Buffer Pool Instances”.
- 可以将频繁访问的数据保存在内存中，而不管会将大量不经常访问的数据带入缓冲池的操作的突然活动高峰。详见 Section 15.8.3.3, “Making the Buffer Pool Scan Resistant”.
- 可以控制如何以及何时执行预读请求，以便异步地将预期即将需要的页面预取到缓冲池中。详见 Section 15.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.
- 可以控制何时进行后台刷新，以及是否根据工作负载动态调整刷新速率。详见 Section 15.8.3.5, “Configuring Buffer Pool Flushing”.
- 可以配置 InnoDB 如何保持当前的缓冲池状态，以避免服务器重启后漫长的预热时间。详见 Section 15.8.3.6, “Saving and Restoring the Buffer Pool State”.
使用InnoDB标准监控器监控缓冲池
InnoDB 标准监视器输出 可使用SHOW ENGINE INNODB STATUS访问，提供有关缓冲池操作的指标。缓冲池指标位于 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 标准监视器输出以来所经过的时间。
表15.2 InnoDB 缓冲池指标
| Name | Description |
|---|---|
| Total memory allocated | The total memory allocated for the buffer pool in bytes. |
| Dictionary memory allocated | The total memory allocated for the InnoDB data dictionary in bytes. |
| Buffer pool size | The total size in pages allocated to the buffer pool. |
| Free buffers | The total size in pages of the buffer pool free list. |
| Database pages | The total size in pages of the buffer pool LRU list. |
| Old database pages | The total size in pages of the buffer pool old LRU sublist. |
| Modified db pages | The current number of pages modified in the buffer pool. |
| Pending reads | The number of buffer pool pages waiting to be read into the buffer pool. |
| Pending writes LRU | The number of old dirty pages within the buffer pool to be written from the bottom of the LRU list. |
| Pending writes flush list | The number of buffer pool pages to be flushed during checkpointing. |
| Pending writes single page | The number of pending independent page writes within the buffer pool. |
| Pages made young | The total number of pages made young in the buffer pool LRU list (moved to the head of sublist of “new” pages). |
| Pages made not young | The total number of pages not made young in the buffer pool LRU list (pages that have remained in the “old” sublist without being made young). |
| youngs/s | The per second average of accesses to old pages in the buffer pool LRU list that have resulted in making pages young. See the notes that follow this table for more information. |
| non-youngs/s | The per second average of accesses to old pages in the buffer pool LRU list that have resulted in not making pages young. See the notes that follow this table for more information. |
| Pages read | The total number of pages read from the buffer pool. |
| Pages created | The total number of pages created within the buffer pool. |
| Pages written | The total number of pages written from the buffer pool. |
| reads/s | The per second average number of buffer pool page reads per second. |
| creates/s | The average number of buffer pool pages created per second. |
| writes/s | The average number of buffer pool page writes per second. |
| Buffer pool hit rate | The buffer pool page hit rate for pages read from the buffer pool vs from disk storage. |
| young-making rate | The average hit rate at which page accesses have resulted in making pages young. See the notes that follow this table for more information. |
| not (young-making rate) | The average hit rate at which page accesses have not resulted in making pages young. See the notes that follow this table for more information. |
| Pages read ahead | The per second average of read ahead operations. |
| Pages evicted without access | The per second average of the pages evicted without being accessed from the buffer pool. |
| Random read ahead | The per second average of random read ahead operations. |
| LRU len | The total size in pages of the buffer pool LRU list. |
| unzip_LRU len | The length (in pages) of the buffer pool unzip_LRU list. |
| I/O sum | The total number of buffer pool LRU list pages accessed. |
| I/O cur | The total number of buffer pool LRU list pages accessed in the current interval. |
| I/O unzip sum | The total number of buffer pool unzip_LRU list pages decompressed. |
| I/O unzip cur | The total number of buffer pool unzip_LRU list pages decompressed in the current interval. |
注意事项:
- youngs/s 指标仅适用于旧页面。对于给定的页面，可以有多次访问，所有的访问都被计算在内。如果在没有大型扫描发生时看到非常低的 youngs/s 值，请考虑减少延迟时间或增加用于旧子列表的缓冲池的百分比。增加该百分比会使旧的子列表变得更大，因此该子列表中的页面移动到尾部需要更长的时间，这增加了这些页面被再次访问并变年轻的可能性。请参见 15.8.3.3 Making the Buffer Pool Scan Resistantl.
- non-youngs/s 指标仅适用于旧页面。它基于页面访问的数量。对于给定的页面，可以有多次访问，所有的访问都被计算在内。如果在执行大型表扫描时没有看到更高的 non-youngs/s 值(以及更高的 youngs/s 值），请增加延迟值。请参见 Section 15.8.3.3, “Making the Buffer Pool Scan Resistant”.
- young-making rate 指标表示所有缓冲池页面访问，而不仅仅是旧子列表中页面的访问。young-making rate 和 not rate 通常不计入总的缓冲池命中率。旧子列表中的页面命中导致页面移动到新子列表，但是新子列表中的页面命中，仅当它们离头部有一定的距离时，会导致页面移动到列表的头部。
- not (young-making rate) 是所有没有导致页面年轻的页面访问的平均命中率。它们可能由不满足
innodb_old_blocks_time定义的延迟导致，或者由新子列表中没有导致页面移动到其头部的页面命中导致。这个比率表示所有缓冲池页面访问，而不仅仅是旧子列表中页面的访问。
缓冲池 服务器状态变量 和 INNODB_BUFFER_POOL_STATS 提供了很多在 InnoDB 标准监视器输出 中相同的缓冲区指标。详见 Example 15.10, “Querying the INNODB_BUFFER_POOL_STATS Table” 。
15.5.2 更改缓冲区（Change Buffer）
更改缓冲区是当次级索引页不再缓冲池中时缓存对它们所做更改的一种数据结构。缓存的更改，可能由于INSERT、UPDATE或DELETE (DML)操作导致，稍后在这些页面被其他读操作加载到缓冲池时合并。
图 15.3 更改缓冲区
不像聚簇索引，次级索引通常是非唯一的，且对次级索引的插入以相对随机的顺序发生。同样，更新和删除可能影响在索引树中不相邻的次级索引页。在其他操作将受影响的页面读入缓冲池时，稍后合并缓存的更改，避免了需要从磁盘将次级索引页读入到缓冲池的大量随机 I/O 访问。
运行于系统大部分空闲或慢关机时的清除操作会周期性地将变更的索引页写入到磁盘中。相较于立即将每个索引值写入磁盘，清除操作可以更高效地将一系列索引值写入磁盘块。
当受影响的行很多，并且有大量的次级索引要更新时，对更改缓冲区的合并可能花费数小时。在此期间，磁盘 I/O 增加，导致以磁盘为瓶颈的查询性能显著下降。提交事务后，甚至服务器关机、重启后，更改缓冲区合并可能继续发生。详见 Section 15.21.3, “Forcing InnoDB Recovery”。
在内存中，更改缓冲区占用缓冲池的一部分。在磁盘上，更改缓冲区属于系统表空间的一部分。数据库服务器关闭时对索引的更改缓存于其中。
缓存于更改缓冲区中的数据类型，由 innodb_change_buffering
变量管理，详见 Configuring Change Buffering。你也可以配置更改缓冲区的最大大小，详见 Configuring the Change Buffer Maximum Size。
包含降序索引列的次级索引或主键索引，不支持更改缓冲（技术）。
关于更改缓冲区频繁问及的问题，请查看 Section A.16, “MySQL 8.0 FAQ: InnoDB Change Buffer”。
配置更改缓冲区
在一张表上执行 INSERT、UPDATE或DELETE 操作时，索引列的值（尤其是次级键的值）通常是无序的，需要大量 I/O 操作来保持次级索引的更新。更改缓存 缓存在相关页面 不在缓冲池 中时缓存对次级索引条目的更改，因此通过不立即从磁盘读入页面避免了昂贵的I/O操作。缓存的更改在页面被加载到缓冲池时合并，被更新的页面稍后刷新到磁盘。在服务器接近空闲或慢关机时 InnoDB 主线程合并缓存的更改。
由于更改缓冲（技术）产生更少的磁盘读写，它对 I/O 受限的工作负载最有价值；例如，有大量诸如批量插入（Bulk Inserts）操作的 DML 操作的应用从更改缓存（技术）中获益。
然而，更改缓存占用缓冲池的一部分，减少了可用于缓存数据页的内存。如果工作集总是填满缓冲池，或你的表有相对很少的次级索引，禁用更改缓存将变得有用。如果工作数据集完全适合缓冲区，更改缓存则不会带来额外开销，因为它只适用于不在缓冲池中的页面。
innodb_change_buffering 变量控制 InnoDB 执行更改缓冲的范围。你可以启用或禁用对 inserts 、delete（索引记录开始被标记为删除时）和 purges （索引记录物理删除时）操作的缓存。更新操作是 inserts 和 delete 操作的组合。innodb_change_buffering的默认值是 all 。
允许的innodb_change_buffering值包括：
- all
默认值：缓存插入、删除标记操作和清除。 - none
不缓存任何操作 - inserts
缓存插入操作 - deletes
缓存标记删除操作 - changes
缓存插入和标记删除操作 - purges
缓存发生于后台的物理删除操作
可以在 MySQL 选项文件中设置 innodb_change_buffering 。或使用 SET GLOBAL 语句动态修改它，这需要你具备足够的设置全局系统变量的权限，详见 Section 5.1.9.1, “System Variable Privileges”。修改该配置仅影响对新操作的缓存，而不影响对已存在的缓存条目的合并。
配置更改缓冲区最大大小
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 标准监控器输出 包含更改缓冲区状态信息。执行
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
更多信息参见 Section 15.17.3, “InnoDB Standard Monitor and Lock Monitor Output”。
- INFORMATION_SCHEMA.INNODB_METRICS 表提供了InnoDB标准监控器输出中的大多数数据点和其他数据点。要查看更改缓冲区指标及其各自描述，执行如下查询：
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
有关更多 INNODB_METRICS 表的用途信息，查看 Section 15.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”。
- 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 |
+---------------------+-------------+-------------------------------+
INNODB_BUFFER_PAGE表的相关用途信息查看 Section 15.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”。
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 互斥锁等待 的信息，查看 Section 15.16.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”。
15.5.3 自适应哈希索引（Adaptive Hash Index）
在适当结合了工作负载和充足的用于缓冲池的内存而不用牺牲事务特性和可靠性的系统上，自适应哈希索引允许 InnoDB 表现得更像是内存数据库。自适应哈希（散列）索引由 innodb_adaptive_hash_index 变量启用，或在服务器启动时使用--skip-innodb-adaptive-hash-index关闭。
基于观察到的搜索模式，哈希索引使用索引键的前缀构建。前缀可以是任何长度，也可能只有 B-tree 中的某些值在哈希索引中出现。哈希索引是为了经常访问的索引页的需求创建的。
如果一张表几乎可以全部放进主存，哈希索引通过允许直接查找任何元素、将索引值转变为一种指针来加速查询。InnoDB 有一种监控索引搜索的机制。如果 InnoDB 察觉查询能从创建一个哈希索引中获益，它会自动这么做。
对于某些工作负载，哈希索引查找所获得的加速远远超过了监视索引查找和维护哈希索引结构的额外工作。在繁重的工作负载下，对自适应散列索引的访问有时会成为资源争用的源头，例如多个并发连接。使用 LIKE 操作符和 % 通配符的查询也不会受益。对于不能从自适应哈希索引中获益的工作负载，关闭它以减少不必要的性能负载。由于很难提前预测自适应哈希索引是否适合一个特定的系统和工作负载，考虑在它开启和关闭时分别运行基准测试。
自适应哈希索引功能是分区的。每个索引绑定到一个特定的分区，每个分区由一个单独的闩锁保护。分区由 innodb_adaptive_hash_index_parts 变量控制。 innodb_adaptive_hash_index_parts 默认设置为 8 ，最大可设置为 512 。
可以在 SHOW ENGINE INNODB
STATUS 输出的 SEMAPHORES 部分监视自适应哈希索引的使用和争用情况。如果有大量线程在等待由 btr0sea.c 创建的 rw 闩锁，考虑增加自适应哈希索引分区或禁用自适应哈希索引。
有关哈希索引性能特性的信息，查看 Section 8.3.9, “Comparison of B-Tree and Hash Indexes” 。
15.5.4 日志缓冲区（Log Buffer）
日志缓冲区是保存将要写入位于磁盘上的日志文件的数据的内存区域。日志缓冲区大小由 innodb_log_buffer_size 定义，默认大小为 16M 。日志缓冲区的内容会定期刷新到磁盘。一个大的日志缓冲区允许大型事务得以运行，而不需要在事务提交前将 Redo Log 数据写到磁盘。因此，如果你有很多UPDATE、INSERT、DELETE很多行的事务，增大日志缓冲区的大小可以节省磁盘 I/O 。
innodb_flush_log_at_trx_commit 变量控制日志缓冲区的内容如何写入和刷新到磁盘。innodb_flush_log_at_timeout 变量控制日志刷新频率。
相关信息请查看 Memory Configuration 和 Section 8.5.4, “Optimizing InnoDB Redo Logging 。
820
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
