InnoDB 存储引擎架构

合集 - MySQL(18)

1.【汇总】 InnoDB 存储引擎2023-08-252.InnoDB 的介绍2023-08-143.Innodb 与 ACID 模型2023-03-224.MySQL事务执行过程2023-03-285.InnoDB 多版本控制2023-03-24

6.InnoDB 存储引擎架构2023-08-14

7.InnoDB 内存架构2023-03-278.InnoDB 磁盘架构2023-08-259.InnoDB 表空间2023-08-1710.InnoDB 双写缓冲区2023-08-1711.MySQL服务器日志2023-03-2812.InnoDB 的锁2023-08-1813.InnoDB 的事务模型2023-08-2314.InnoDB 中不同 SQL 语句设置的锁2023-08-2415.InnoDB 中的死锁2023-08-2416.InnoDB 磁盘 I/O 和 文件空间管理2023-08-2517.MySQL 查询执行计划2024-01-0518.【MySQL优化】索引失效的场景2023-09-14

收起

目录

• InnoDB 架构
  • 概念
    • page
    • page size
    • checkpoint
• InnoDB 内存架构
  • Buffer Pool
    • 缓冲池 LRU 算法
    • 相关配置
      • innodb_buffer_pool_size
      • innodb_buffer_pool_instances
    • 缓冲池优化
  • Change Buffer
    • 配置变更缓冲
      • innodb_change_buffering
      • innodb_change_buffer_max_size
    • Change Buffer 触发时机
    • change Buffer 的适用场景
  • 自适应哈希索引
    • 使用场景
    • 限制
    • 注意事项
  • Log Buffer

InnoDB 架构

下图显示了组成 InnoDB 存储引擎架构的内存和磁盘结构。有关每个结构的信息：

概念

page

页（Page）：InnoDB 在磁盘（data files）和内存（buffer pool）之间传输数据的基本单位。

一个页（Page）可以包含一个或多个行（row），这具体取决于每行中有多少数据，如果一行不能完全放入一页，InnoDB 会设置额外的指针式数据结构，以便有关该行的信息可以存储在一页中。

在每页中容纳更多数据的一种方法是使用压缩行格式（compressed row format），对于使用 BLOB 或大型文本字段的表，紧凑行格式允许将这些大型列与行的其余部分分开存储，从而减少不引用这些列的查询的 I/O 开销和内存使用量。

为了提升 I/O 吞吐量，InnoDB 的读取和写入操作，都是按照一个或多个页进行的，会一次性读取或写入一个范围。

page size

通过选项 innodb_page_size 控制可以修改 page size，其默认值为：16384，即 16KB。

默认的 16KB 页面大小或更大的空间，适用于各种工作负载，特别是涉及全表扫描的查询和涉及批量更新的 DML 操作。

对于涉及许多小型写入的 OLTP 工作负载，较小的页面大小可能更有效，当单个页面包含许多行时，可能会面临竞争等问题。

对于 SSD 存储设备来说，较小的页面也可能会更高效，因为，SSD 存储设备通常使用较小的块大小。

保持 InnoDB 的页面大小接近存储设备的块大小，可以最大限度地减少重写到磁盘的未更改数据量。

checkpoint

当对缓存在缓冲池（buffer pool）中的数据页进行修改时，这些修改会在稍后写入数据文件（data file），这个过程称为刷新（flushing）。

检查点（checkpoint）是已成功写入数据文件的最新更改的记录，用 LSN 的值来表示。

InnoDB 内存架构

Buffer Pool

缓冲池（Buffer Pool）是主内存中的一个区域，InnoDB 在被访问时，会缓存表和索引数据。缓冲池允许直接从内存访问经常使用的数据，从而加快处理速度，在专用服务器上，高达 80% 的物理内存通常分配给缓冲池。

为了提高大容量读取操作的效率，缓冲池被划分为可以容纳多个行（row）的页（page）。为了提高缓存管理的效率，缓冲池中的页（page）通过链表管理；使用最近最少使用 (LRU) 算法的变体，很少使用的数据会从缓存中老化。

缓冲池 LRU 算法

缓冲池使用 LRU 算法的变体作为链表进行管理。当需要空间向缓冲池添加新页面时，最近最少使用的页面将被逐出，并将新页面添加到链表的中间。

这种中点插入策略将链表视为两个子链表：

• 头部：是最近访问的新（“年轻”）页面的子链表

• 尾部：是最近访问较少的旧页面的子链表

缓冲池链表的结构，如下：

该算法将经常使用的页面保留在 new 子链表中。old 子链表包含不常用的页面，这些页面是被驱逐的候选页面。

默认情况下，该算法的运行策略如下：

• 缓冲池的 3/8 专用于 old 子链表；

• 链表的中点是 new 子链表的尾部与 old 子链表的头部相交的边界；

• 当 InnoDB 将一个数据页读入缓冲池时，它最初将其插入到链表的中点，即 old 子链表的头部；

  用户启动的操作（如，SQL查询）会触发数据页的读取，或者，数据页会作为 InnoDB 自动执行的预读操作的一部分被加载到缓冲池。

• 访问 old 子链表中的页，会使其“年轻化”，它将被移动到 new 子链表的头部；

  注意，这个数据页被访问，分两种情况：

  • 如果该页是因为用户启动的操作需要而被读取的，则页面中的数据会立即被访问，并且该页会变为年轻页。

  • 如果该页是由于预读操作而读取该页，则页面中的数据的不会被立即访问，并且，在该页被逐出之前可能根本不会访问。

• 随着数据库的运行，缓冲池中未访问的页会通过向链表尾部移动而“老化”。new 子链表 和 old 子链表中的页会随着其他的页更新而老化。当页面在中点插入时，old 子链表中的页也会老化。最终，未使用的页到达 old 子链表的尾部并被驱逐。

默认情况下，因查询而读取的页，会立即移动到 new 子链表中，这意味着它们在缓冲池中停留的时间更长。

执行全表扫描会将大量的数据放入缓冲池，并逐出等量的旧数据，即使新数据不再使用。如，执行 mysqldump 操作或不带 WHERE 子句的 SELECT 的语句。同样，由后台预读线程（read-ahead background thread）加载且仅访问一次的页，也会被移动到 new 子链表的头部。

这些情况，都可能会将经常使用的页推送到 old 子链表，并在 old 子链表中被驱逐。

相关配置

innodb_buffer_pool_size

System Variable	innodb_buffer_pool_size
Default Value (Windows, 32-bit platforms)	134217728（128MB）
Minimum Value	5242880（5MB）
Maximum Value (64-bit platforms)	18446744073709551615 ($2^{64}-1$)
Maximum Value (32-bit platforms)	4294967295（$2^{32}-1$）

innodb_buffer_pool_size 定义了缓冲池的大小，即 InnoDB 缓存表和索引数据的内存区域，单位：字节，默认值为： 134217728 字节 (128MB)。

当缓冲池的大小大于 1GB 时，将设置 innodb_buffer_pool_instances 为大于 1 的值，在高负载时，可以提高服务器的可伸缩性。

更大的缓冲池可以减少 InnoDB 多次访问相同表数据时的磁盘 I/O，因此，在专用数据库服务器上，我们可以将缓冲池大小设置为计算机物理内存大小的 80%。

配置缓冲池大小时请注意以下潜在问题，并准备在必要时缩小缓冲池的大小。

• 可能会导致操作系统分配物理内存时，产生分页竞争。

• InnoDB 为缓冲区和控制结构保留额外的内存，因此，分配的总空间会比指定的缓冲池大小大 10% 左右。

• 缓冲池的地址空间必须是连续的，在Windows 系统上，DLL 需要从特定地址加载，这可能是一个问题。

• 初始化缓冲池的时间大致与其大小成正比，因此，在具有大型缓冲池的实例上，初始化时间可能会很长。

  innodb_buffer_pool_size 以动态设置，因此，我们可以在不重新启动服务器的情况下，调整缓冲池的大小。

  为了减少初始化时间，我们可以在服务器关闭时保存缓冲池状态，并在服务器启动时再恢复它。

当您增加或减少缓冲池大小时，操作将以块（chunks）的形式执行，块大小由可以通过变量 innodb_buffer_pool_chunk_size 配置，默认值为：128 MB。

缓冲池大小必须等于如下乘积，或者是该值的倍数：

innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

如果将缓冲池大小不满足上述条件，缓冲池将自动调整其大小，使其满足上述规则。

innodb_buffer_pool_instances

innodb_buffer_pool_instances 可以配置 InnoDB 缓冲池划分的区域数。

System Variable	innodb_buffer_pool_instances
Default Value (Windows, 32-bit platforms)	（自动调整大小）
Default Value (其他操作系统)	8 (or 1 if innodb_buffer_pool_size < 1GB)
Minimum Value	1
Maximum Value	64

对于缓冲池大小在数 GB 范围内的系统，可以将缓冲池划分为单独的实例，每个缓冲池管理自己的空闲链表（free lists）、 flush 链表(flush lists)、 LRU 链表（LRU lists）以及连接到缓冲池的所有其他数据结构，并受到自己的缓冲池互斥锁（mutex）的保护。

这样就可以减少不同线程读取和写入缓存页面时的竞争，这样就可以提高并发性能，当页面写入缓冲池或者从缓冲池读取页面时，就可以通过哈希函数将页面随机分配给其中一个缓冲池实例。

在 32 位 Windows 系统上的默认值取决于 的值 innodb_buffer_pool_size，如下所述：

• 如果 innodb_buffer_pool_size $\ge 1.3GB$，则默认为：innodb_buffer_pool_instances = innodb_buffer_pool_size / 128 MB，每个内存块都有单独的内存分配请求。

  选择 1.3GB 作为边界的原因是，32 位 Windows 系统存在无法为单个缓冲池分配所需的连续地址空间的重大风险。

• 如果缓冲池大小小于 1.3GB，则，缓冲池实例数的默认值为 1。

在所有其他平台上，当缓冲池大小大于或等于 1GB 时，默认值为 8，否则，默认值为 1。

注意，这个选项只有在缓冲池大小大于 1GB 时，修改才会生效，否则，当缓冲池小于 1GB 时，它默认为 1。

因此，为了获得最佳效率，我们可以指定 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 的组合，这样，每个缓冲池实例就至少可以获得 1GB 的内存。

缓冲池优化

我们可以如下方式配置缓冲池，以提高性能：

• 理想情况下，我们可以将缓冲池的大小设置为尽可能大的值，为服务器上的其他进程留下足够的内存来运行，而不会出现过多的分页。缓冲池越大，InnoDB 就越像内存数据库，从磁盘读取数据一次，然后在后续读取时从内存访问数据；

• 在内存充足的 64 位系统上，我们可以通过配置 innodb_buffer_pool_instances，将缓冲池拆分为多个部分，以最大程度地减少并发操作之间对内存结构的竞争；

• 我们可以将频繁访问的数据保留在内存中，而不管操作活动是否突然出现峰值，这些操作会将大量不经常访问的数据带入缓冲池；

• 我们可以控制如何以及何时执行预读请求，以异步方式将页面预取到缓冲池中，以预测即将需要的页面；

• 我们可以控制何时发生后台刷新以及是否根据工作负载动态调整刷新速率；

• 我们可以配置如何 InnoDB 保留当前缓冲池状态，以避免服务器重新启动后出现过长的预热期。

Change Buffer

当二级索引页不在缓冲池中时，写缓冲区（change buffer）它会缓存二级索引页的更改。缓冲的更改可能是由 INSERT、UPDATE 或 DELETE 等 DML 操作产生的，稍后在其他读取操作将索引页加载到缓冲池中之后，这些更改将被合并。

change Buffer 是一种应用在非唯一普通索引页（non-unique secondary index page）不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更（Buffer changes），等未来数据被读取时，再将数据合并（Merge）恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘 IO，提升数据库性能。

change Buffer 的结构如下：

如上图所示，在内存中，change Buffer 占用缓冲池中的一部分；同时，在磁盘上，change Buffer 也是系统表空间的一部分，当数据库服务器关闭时，索引的变更将被刷盘到系统表空间中。

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

当 InnoDB 的索引列具有唯一约束时，对索引列的修改，就必须进行唯一性检查，就需要将磁盘上索引页读入 buffer pool，此时，就可以直接通过 buffer pool 修改，而不需要通过 change buffer 机制更改。

当系统运行缓慢或者在服务缓慢关闭期间，清除（purge）操作会定期将被更新的索引页写入磁盘。与将每个索引值立即写入磁盘相比，清除（purge）操作可以更高效地将一系列索引值写入磁盘块。

当有许多受影响的行和大量的二级索引需要更新时，change Buffer 合并可能需要几个小时。在此期间，磁盘 I/O 会增加，这可能会导致磁盘绑定的SQL查询显著减慢。当提交事务后，甚至在服务器关闭并重新启动后，更改缓冲区合并也可能继续发生。

change Buffer 中缓存的数据类型由 innodb_change_buffering 变量控制。

注意，如果二级索引包含降序索引列，或者，主键包含降序索引列，则二级索引不支持 change Buffer。

配置变更缓冲

当对表执行 INSERT、UPDATE 和 DELETE 操作时，索引列的值（特别是二级索引的值）通常处于未排序的状态，需要大量 I/O 操作才能使二级索引更新。

如果 DML 操作涉及的页不在缓冲池中时，change Buffer 会缓存对二级索引条目的更改，也就是说，它不会立即从磁盘读取二级索引页，这样就避免昂贵的 I/O 操作。

在相关的页被加载到缓冲池中后，缓冲的更改将会被合并，更新的页才会被刷新到磁盘中。当服务器空闲时，或者缓慢关闭期间，InnoDB 主线程会合并缓冲的更改。

因为 change Buffer 可以减少磁盘读取和写入，所以，change Buffer 对于 I/O 密集型工作负载最有价值。例如，具有大量 DML 操作（例如，批量插入）的应用程序可以从 change Buffer 中受益。

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

innodb_change_buffering

innodb_change_buffering 变量控制 InnoDB 执行更改缓冲的程度。

我们可以启用或禁用 INSERT、DELETE 操作（当索引的记录最初标记为删除时）和清除（purge）操作（当索引的记录被物理删除时）的缓冲。

一次更新操作是一个插入操作和一个删除操作的组合。

System Variable	innodb_change_buffering
Default Value	all	默认值，缓冲插入、删除标记操作和清除（purge）操作。
Valid Values	none	不缓冲任何操作。
	inserts	缓冲区插入操作。
	deletes	缓冲区删除标记操作。
	changes	缓冲插入和删除标记操作。
	purges	缓冲后台发生的物理删除操作。

innodb_change_buffer_max_size

innodb_change_buffer_max_size 变量控制 change buffer 占用整个 buffer pool 的百分比。

其默认值为：25，即默认占用 25% 的内存，可以配置的范围：$[0,50]$

Change Buffer 触发时机

触发数据写入 Change Buffer 中的场景：

• 数据页被访问

• 后台线程会认为数据库空闲时；

• 数据库缓冲池不够用时；

• 数据库正常关闭时；

• redo log写满时；

  通常几乎不会出现redo log写满的情况，此时整个数据库处于无法写入的不可用状态。

change Buffer 的适用场景

• 适合开启 change buffer 的场景：

  • 数据表大部分是非唯一索引；

  • 业务是写多读少，或者不是写后立刻读取。

  这两类场景，可以使用写缓冲，将原本每次写入都需要进行磁盘 I/O 的 SQL，优化定期批量写磁盘，例如，账单流水业务。

• 不适合开启 change buffer 的场景：

  • 数据表的列都是唯一索引；

  • 写入一个数据后，会立刻读取它。

  这两类场景，在写操作进行时（进行后），本来就要进行进行页读取，相应的页面原本就要入缓冲池，此时，写缓存反倒成了负担，增加了复杂度。

自适应哈希索引

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

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

InnoDB 会根据观察到的搜索模式，使用索引键的前缀构建哈希索引。前缀可以是任意长度，并且可能只有 B-tree 中的某些值出现在哈希索引中，哈希索引是针对经常访问的索引页按需构建的。

如果表几乎完全适合主内存，那么，哈希索引可以通过直接查找任意元素，它可以将索引值转换为某种指针来加速查询。 InnoDB 有一个监视索引搜索的机制，如果 InnoDB 注意到查询可以从构建哈希索引中受益，它会自动执行此操作。

自适应哈希索引特征是分区的，每个索引都绑定到一个特定的分区，并且每个分区都由单独的锁存器保护。

分区由变量控制 innodb_adaptive_hash_index_parts ， 其默认为：8，可选值为： $[1,512]$。

使用场景

• 自适应哈希索引的适用场景：

  • 很多单行记录查询；

    比如，用户登录系统时密码的校验。

  • 索引范围查询，此时，自适应哈希索引可以快速定位首行记录；

  • 所有记录内存能放得下，这时，自适应哈希索引往往是有效的；

• 自适应哈希索引的不适用场景：

  • 使用 LIKE 运算符和 % 通配符的查询；

  • 多个并发关联查询；

    自适应哈希索引的维护反而可能成为负担，降低系统效率，此时，可以手动关闭自适应哈希索引功能。

  • 基准测试。

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

  对于某些工作负载，自适应哈希索引查找所带来的加速，远远超过了监视索引查找和维护哈希索引结构的额外工作。对于无法从自适应哈希索引中受益的工作负载，将其关闭可以减少不必要的性能开销。

限制

自适应哈希索引的限制：

• 只能使用完整的 key 来进行等值查询；

  如，=、<=、>=、IN、AND 等；

  对于 B+ 树索引，键的任何最左边的前缀都可以用于查找行。

• 优化器无法使用哈希索引来加速 ORDER BY 操作；

  哈希索引不能用于按顺序搜索下一个条目。

• MySQL 无法确定两个值之间大约有多少行；

  范围优化器使用它来决定使用哪个索引。

• 有冲突可能；

• MySQL自动管理，人为无法干预。

注意事项

一些资料统计，启用AHI后，读取和写入速度可以提高 2 倍，二级索引的连接操作性能可以提高 5 倍。

自适应哈希索引还有一些要求：

• 对这个页的连续访问模式必须是一样的；

  例如，对于 (a, b) 这样的联合索引页，其访问模式可能是下面的情况之一：

  where a=xxx
  where a =xxx and b=xxx

  访问模式一样是指查询的条件是一样的，若交替进行上述两种查询，那么，InnoDB 存储引擎不会对该页构造自适应哈希索引。

• 对这个页的连续访问达到一定的数量。

  比如，页通过该模式访问了 N 次，其中，N = 页中记录 * 1/16。

基于主键的搜索，几乎都是 hash searches；基于普通索引的搜索，大部分是non-hash searches，小部分是hash searches。

Log Buffer

日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域，日志缓冲区的内容会定期刷新到磁盘。

大型日志缓冲区允许大型事务运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果有更新、插入或删除许多行的事务，可以通过增加日志缓冲区的大小，以节省磁盘 I/O。

日志缓冲区相关的配置如下：

• innodb_log_buffer_size ：定义了日志缓冲区大小。

  默认大小为：16MB。

• innodb_flush_log_at_trx_commit ：定义了日志的刷盘策略。

  控制如何将日志缓冲区的内容写入并刷新到磁盘，默认值为：1，表示每个事务提交时，都会将日志写入并刷新到磁盘，完全符合 ACID 模型。

  可选值：

  • 0：每秒将日志写入并刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。
  • 1：每次事务提交时都会将日志写入并刷新到磁盘。
  • 2：日志会在每次事务提交后写入，并每秒刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。

  注意，执行 DDL 语句和其他 InnoDB 内部活动刷新日志的频率，不会受 innodb_flush_log_at_trx_commit 变量的控制。

  因此，对于设置为 0 和 2，不能 100% 保证每秒刷新一次。由于 DDL 更改和其他 InnoDB 的内部活动，可能导致日志不会按照 innodb_flush_log_at_trx_commit 配置的频率进行刷新，刷新可能会更频繁地发生，并且，有时由于调度问题，刷新可能会不那么频繁。

  如果日志每秒刷新一次，则在崩溃中最多可能会丢失一秒的事务；如果日志刷新频率高于或低于每秒一次，则可能丢失的事务量会相应变化。

• innodb_flush_log_at_timeout ：控制日志刷新频率。

  每 N 秒写入并刷新日志。innodb_flush_log_at_timeout 允许增加刷新之间的超时时间，以减少刷新并避免影响二进制日志组提交的性能。 innodb_flush_log_at_timeout 的默认设置是每秒一次。

---

参考：

• MySQL各种“Buffer”之Change Buffer

• 自适应哈希索引(Adaptive Hash Index, AHI)

• 一文带你了解MySQL之Adaptive Hash Index