InnoDB内存架构

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

内存架构

主要分为这么四大块： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。

Buffer Pool

InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。

Buffer Pool对应的一片连续的内存被划分为若干个页面，页面大小与innodb表空间使用的页面大小一致，默认都是16KB。为了与磁盘中的页面区分开来，我们这里把这些Buffer Pool的页面叫做缓冲页，为了更好管理这些缓冲页，设计mysql的大叔为每一个缓存页都创建了一些控制信息。这些控制信息包括该页所属的表空间编号、页号、缓冲页在buffer pool中的地址、链表节点信息等。默认情况下Buffer Pool只有128M大小。

为什么需要Buffer Pool

减少磁盘I/O操作：直接从磁盘读取或写入数据是相对缓慢的操作。Buffer Pool通过将频繁访问的数据页缓存到内存中，减少了对磁盘的直接访问次数，从而加快了数据的读取速度。

提高读写性能：当数据被修改时，首先在Buffer Pool中的页进行修改，然后将这些修改标记为“脏页”。之后，后台线程会定期将这些脏页刷新回磁盘，而不是每次修改都直接写入磁盘，这样可以减少磁盘写操作的频率，提高性能。

数据局部性原理：数据库操作往往遵循数据局部性原理，即最近访问过的数据很可能在不久的将来再次被访问。Buffer Pool利用这一原理，通过缓存最近访问的数据，提高了数据访问的速度。

预读和延迟写入：InnoDB可以预读认为将来可能会用到的数据页到Buffer Pool中，同时，数据的修改（脏页）会延迟写回磁盘，这样可以优化整体的I/O性能。

内部组成

• 数据页（Data Pages）：存储了实际的数据库表数据。通常大小为16KB。
• 索引页（Index Pages）：存储了B+树索引结构，用于快速定位数据页。索引页允许快速访问和检索数据。
• 插入缓冲页（Insert Buffer Pages）：用于优化插入操作，减少对索引页的直接写操作。插入缓冲可以收集多个插入操作，然后批量写入索引页。
• undo页（Undo Pages）：存储事务的逆操作信息，用于事务的回滚操作。保证事务的原子性和一致性。
• 自适应哈希索引（Adaptive Hash Index）：提供对B+树索引的快速访问。自适应哈希索引可以根据使用情况动态调整大小。
• 锁信息（Lock Information）：存储与数据页相关的锁信息，用于处理并发访问和事务的隔离。
  

内存管理

控制块的作用与信息

控制块（Control Block）是Buffer Pool中每个页的元数据部分，控制块的存在使得Buffer Pool能够有效地跟踪每个页的状态，并进行管理。它包含了管理页所需的信息：

• 页号：标识页在磁盘上的位置。
• 表空间号：标识页所属的表空间。
• 页类型：如数据页、索引页等。
• 链表节点：用于将控制块链接到其他管理结构，如LRU链表或Flush链表。
• 状态信息：如是否是脏页、是否被锁定等。

每个缓冲页对应的控制信息占用的内存大小是相同的，我们把每个页对应的控制信息占用的一块内存称为一个控制块。控制块与缓存页是一一对应的，他们都存放在Buffer Pool中，其中控制块存放在Buffer Pool的前面，缓冲页放在Buffer Pool的后面，整个Buffer Pool对应的内存空间如下图：

这里大家可能会疑问，控制块和缓冲页之间的那个碎片是什么呢？每一个控制块都对应一个缓冲页，那么在分配足够多的控制块和缓冲页后，剩下的那点空间可能不够一对控制块和缓冲页，自然也就用不到了。这个用不到的内存空间就称为碎片。当然，如果把buffer pool的大小设置的刚刚好，也可能不会产生碎片。

碎片空间的产生与影响

碎片空间（Fragmentation）是在Buffer Pool中由于页的分配和释放不连续导致的小块未使用的内存空间。产生原因和影响包括：

• 非连续分配：随着页的不断加载和替换，可能会在Buffer Pool中留下小块的未使用空间。
• 内存利用率下降：碎片空间不能被有效利用，导致内存利用率降低。
• 管理开销增加：碎片空间需要被跟踪和管理，增加了系统的复杂性和开销。

为了减少碎片空间，可以通过合理配置Buffer Pool的大小，避免频繁的页替换，或者使用内存分配策略来优化空间的使用。

页管理

• 如何管理空闲页（Free List）：空闲页链表（Free List）是Buffer Pool中的一个链表结构，用于管理当前未被使用的空闲页。
• • 快速分配：当需要从磁盘加载新页到Buffer Pool时，系统会从Free List中取出一个空闲页进行使用。
  • 维护：系统必须维护这个链表，确保可以快速访问和释放空闲页。

注意：一个控制块和缓冲页不可能同时在Free链表和flush链表。

free链表其实就是用来存储空闲的控制块的。每次启动Mysql服务器的时候，都会进行Buffer Pool的初始化（向操作系统申请Buffer Pool的内存空间，然后划分成若干对控制块和缓冲页。此时并没有真实的磁盘页缓存到Buffer Pool中。程序运行时会不断从磁盘上加载缓存到Buffer Pool中）。

🤔 这里请大家思考一个问题，当从磁盘上加载一个数据页到缓存中时，应该放在哪个位置呢？或者说我们如何知道Buffer Pool中哪些缓冲页是空闲的，哪些是已经被使用的？我们最好在某个地方标记Buffer Pool中哪些缓冲页是可用的。这个时候缓冲页对应的控制块就派上用场了，我们可以把所有空闲的缓冲页对应的控制块作为一个节点放到一个链表中，这个链表就是空闲（Free）链表。

刚初始化完的Buffer Pool中，所有的缓冲页都是空闲的，所以每一个缓冲页对应的控制块都会加入到Free链表中。如下图：

从上图可以看出，为了管理好Free链表，我们特意定义了一个基节点，里面包含了链表的头节点和尾节点指针，以及当前链表中节点的数量等信息。这里需要注意的是，链表的基节点占用的内存空间并不包含在为Buffer Pool申请的连续内存空间内，而是一块单独申请的内存空间。

有了Free链表之后，每当需要从磁盘中加载一个数据页到Buffer Pool中时，就从Free链表中取一个空闲的缓冲页，并且把该缓冲页对应的控制信息填上，然后把该缓存页对应的Free链表节点（也就是对应的控制块）从链表中移除，表示该缓冲页已经被使用了。

缓冲页的hash处理

当我们需要访问某个页中的数据时，就会把该页从磁盘加载到Buffer Pool中。如果该页已经在Buffer Pool中的话，可以直接使用。

🤔 前面我们一起思考了一个问题，当磁盘加载一个数据页放到缓冲页中时，应该把该数据页放在哪个位置？（也就是放到哪的问题）到这里我们继续再思考一个问题，就是加载数据页到缓冲页中时我们怎么知道该页在不在Buffer Pool中？（也就是要不要放的问题）首先如果我们依次去遍历Buffer Pool中的缓冲页是不现实的，因为Buffer Pool中的缓冲页很多，如果都遍历非常的耗时耗性能。我们回顾一下前面讲的知识点，控制块和缓冲页是一一对应的，链表中链接的其实是控制块信息，那么我们是不是可以根据控制块中的表空间号+页号来直接定位一个页呢。也就是说把表空间号+页号作为一个key，缓冲页对应的控制块作为value，然后通过哈希表来实现key-value的查找。

通过上述思考，我们确定可以用表空间号+页号作为key，用缓冲控制块的地址作为value来创建一个哈希表。在需要访问某个页的数据时，先从哈希表中根据表空间号+页号看看是否有对应的缓冲页。如果有，直接使用缓冲页就好，如果没有，就从Free链表中选一个空闲的缓冲页，然后把磁盘中对应的页记在到缓存页的位置。

• 如何管理脏页（Flush List）：脏页链表（Flush List）记录了所有已经被修改但还没有刷新回磁盘的页。
  • 刷新机制：后台线程会定期检查Flush List，并将脏页刷新回磁盘，以保证数据的持久性。
  • 优先级：在某些情况下，如系统重启或页需要被替换出Buffer Pool时，脏页需要被优先刷新。
• Flush链表其实就是用来存储已经被修改过的页，并且未来得及写入磁盘的控制块（缓冲页）。如果我们修改了Buffer Pool中的某个缓冲页的数据，它就与磁盘上的页不一致了，这样的缓冲页页称为脏页。当然，我们也可以再每次修改完某个缓冲页时就立即同步将其刷到磁盘中对应的页上。但是频繁地往磁盘中写数据会影响程序的性能。所以每次修改完缓冲页后，我们并不着急立即把修改刷到磁盘上，而是在未来的某个时间点进行异步刷盘。

🤔 这里我们提到了同步刷盘和异步刷盘。同步刷盘很好理解，就是在缓冲页中数据被修改后立即将对应修改的数据刷新到磁盘中。那么异步刷盘就需要思考几个问题：1. 异步刷盘的时机是什么；2. 异步刷盘的时候怎么知道哪些页是被修改过的。这里我们先讨论第二点我们如何知道哪些页被修改过。这是我们就需要创建一个Flush List来存储被修改过的数据页（脏页）。凡是被修改过的缓冲页对应的控制块都会被放到脏页链表中，Flush 链表和Free链表的构造差不多。

• 如何提高缓存命中率
  • LRU算法（Least Recently Used）：最近最少使用算法，用于替换最长时间未被访问的页。
  • LRU优化：InnoDB对LRU算法进行了优化，通过划分young和old区域，优先保留频繁访问的数据。
  • 预读和批量操作：合理使用预读机制和批量操作可以减少磁盘I/O，提高缓存的效率。
    

LRU算法及Mysql的优化

为什么需要使用LRU算法

Buffer Pool对应的内存大小毕竟是有限的。如果需要缓存的页占用的内存大小超过了Buffer Pool的大小，也就是Free链表中已经没有多余的空闲缓冲区了，这时候怎么办呢？这里我们是不是可以参考redis的缓存淘汰策略，把不经常使用的旧的缓冲页从Buffer Pool中移除，释放出空间然后把新的页放进去呢。

标准LRU算法

• 最近最少使用：LRU算法的核心思想是将最近最少使用的页置换出去，以腾出空间给新的页。
• 数据结构：通常使用一个双向链表来实现，最近访问的页在链表头部，最旧的页在尾部。
• 置换操作：当Buffer Pool满了，新页需要加载时，就从链表尾部移除页，并加载新页到头部。

当Buffer Pool中不再有空闲的缓冲页时，就需要淘汰最近很少使用的部分缓冲页。

🤔 这里我们说到了LRU算法，在Buffer Pool空间满时淘汰旧缓冲页，那么我们怎么知道哪些缓冲页需要被淘汰呢？我们是不是可以再创建一个LRU链表去淘汰缓冲页。

当需要访问某个页时，可以按照下面的方式处理LRU链表：

• 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓冲页时，就把该缓冲页对应的控制块作为节点塞到LRU链表的头部。
• 如果该页已经被加载到Buffer Pool中，则直接把该页对应的控制块移到LRU链表的头部。

也就是说，只要我们使用到某个缓冲页，就把该缓冲页调整到LRU链表的头部，这样LRU链表尾部就是最近很少使用的缓冲页。所以，在Buffer Pool中空闲缓冲页使用完时，就到LRU链表的尾部找到缓冲页淘汰就行了。

上述方案看似很完美，但是会遇到两个问题：

• InnoDB提供了预读机制，就是InnoDB认为执行当前的请求时，会认为读取到的数据页周边的数据页再未来一段时间内可能也会被访问，于是就预先把这些页面加载到Buffer Pool中。根据触发方式的不同，预读又分为以下两种：
  1. 线性预读：如果顺序访问的某个区的页面超过系统变量innodb_read_ahead_threshold的值，就会触发一次异步读取下一个区中全部页面到Buffer Pool中的请求。注意异步读取从磁盘中加载这些被预读的页面时并不会影响到当前工作线程的正常执行。innodb_read_abead_threshold系统变量的值默认是56。
  2. 随机预读：如果某个区的13个连续的页面都被加载到了Buffer Pool中，不管页面是不是顺序读取的，都会触发一次异步读取本区中所有其他页面到Buffer Pool中的请求。InnoDB默认是关闭随机预读的功能（innodb_random_read_ahead=OFF）。

预读机制本身是为了提高系统性能。如果预读到Buffer Pool中的页被成功地使用到，那就可提高语句执行的效率。可是如果用不到呢？这些预读的页都会放到LRU链表的头。如果此时Buffer Pool的容量不太大，而且很多预读的页面都没有用到的话，LRU链表尾部的一些缓冲页会很快被淘汰掉，从而大大降低Buffer Pool命中率。

• 再我们真实的项目中可能会需要写一些进行全表扫描的语句（比如在没有建索引或者压根儿没有WHERE子句的查询时)。
  全表扫描意味着将访问该表的聚簇索引的所有叶子节点。如果需要访问的页面特别多，而Buffer Pool的空间又不够这就需要将其他语句在执行过程中用到的页面踢出Buffer Pool，等其他语句重新执行时，又需要重新将用到的页从磁盘加载到Buffer Pool中。我们在业务中一般尽量避免对很大的表执行全表扫描操作，这是一个很耗时的操作，只会在特定场景下偶尔对很大的表执行全表扫描操作。由于对大表执行全表扫描操作可能要将Buffer Pool中的缓冲页替换一次，这会严重影响到其他查询对Buffer Pool的使用，从而降Buffer Pool命中率。

可能降低Buffer Pool命中率的两种情况：

• 加载到Buffer Pool中的页不一定被用到（预读失效）
• 如果有非常多的使用频率偏低的页被同时加载到Buffer Pool中，则可能会把频率非常高的页从Buffer Pool中淘汰掉（大表扫描，也即Buffer Pool污染问题）

因为这两种情况的存在，InnoDB把LRU链表按照一定的比例分为两截：

• 一部分存储使用频率非常高的缓冲页，也称为热数据，或者young区域。
• 另一部分存储使用不是很高的缓冲页，也称为冷数据，或者old区域。

需要注意的是，我们并不是按照固定的比例将LRU链表分为两半。随着程序的运行，某个节点所属的页可能发生变化。我们可以通过系统变量innodb_old_blocks_pck的值来确定old区域在LRU链表中所占的比例。

有了这个被划分为young和old区域的LRU链表之后，就可以针对前面提到的两种可能降低Buffer Pool命中率的情况进行优化了：

1. 针对预读的页面可能不进行后续的访问优化。当磁盘上的某个页面被初次加载到Buffer Pool中的某个缓冲页时，该缓冲页对应的控制块会被放到old区域。这样一来，预读到Buffer Pool却不进行后续的访问的页面就会被逐渐从old区域移出，而不影响young区域中使用比较频繁的缓冲页。
2. 针对全表扫描时，短时间内访问大量使用频率非低的页面的优化。在进行全表扫描时，虽然首次加载到Buffer Pool中的页放到old区域的头部，但是后续会被马上访问，每次进行访问时又会把该页放到young的头部，这样仍然会把那些使用比较频繁的页面挤出去。

那是不是可以在第一次访问该页面时不将其从old区域移到young区域的头部，而是在后续访问时再将其移动到young区域的头部呢？回答是行不通！因为InnoDB规定，每次去页面中读取一条记录时，都算是访问一次页，而一个页中可能存在多条记录，也就是说读取完某个页的记录相当于访问了这个页很多次。

全表扫描有一个特点，那就是它的执行频率比较低，谁也不会没事写全表扫描语句玩。而且在执行全表扫描的过程中，即使某个页中有很多条记录，尽管每读取一条记录都算是一次访问，但是这个过程所花费的时间也是非常少的。所以我们只需要规定，在某个处于old区域的缓冲页进行第一次访问时，就在它对应的控制块中记录下这个访问时间，如果后续的访问时间与第一次访问时间在某个间隔内，那么该页面就不会从old区域移到young区域的头部，否则将他移到young区域的头部，这个时间间隔是有系统变量innodb_old_blocks_time（默认1000ms）控制的。也意味着对于从磁盘加载到LRU链表中old区域的某个页来说，如果第一次和最后一次访问该页的时间间隔小于1000ms，那么该页不会加入到young区域中。很明显，在一次全表扫描的过程中，多次访问一个页的时间不会超过1000ms。当然，与innodb_old_blocks_pct一样，我们也可以在服务器启动时设置。

对于young区域的缓冲页来说，我们每次访问一个缓冲页就要把它移到LRU链表的头部，这样开销是不是太大了。毕竟在young区域的缓冲页都是热数据，也就是可以经常被访问。这样频繁的对LRU链表执行节点的移动操作是不是不太好呢？我们是不是可以通过一些优化策略比如被访问到的缓冲页位于young区域的1/4的后面时，才会被移动到LRU链表头部。这样就可以减低调整LRU链表的频率。从而提升性能。

MySQL对LRU算法进行了优化，以适应数据库操作的特点：

• young区域和old区域的划分

目的：区分频繁访问的“热”数据和较少访问的“冷”数据。

young区域：新访问或频繁访问的页被放在这个区域，减少置换的可能性。

old区域：较少访问的页在这个区域，当需要置换时，首先考虑这个区域的页。

• 预读失效问题

问题：预读机制可能会加载不常访问的页，导致实际需要的页被置换出去。

解决：通过限制预读页在LRU链表中的移动，减少其对热数据的影响。

• Buffer Pool污染问题

问题：大量不常访问的数据占据了Buffer Pool，导致热数据被置换。

解决：通过调整young和old区域的比例，以及控制页在old区域的停留时间，减少污染。

脏页的刷新机制

脏页是指在Buffer Pool中被修改过但还未写回磁盘的页。为了保证数据的一致性和持久性，脏页需要在适当的时机刷新到磁盘。刷新脏页是InnoDB存储引擎中重要的后台操作。

后台有专门的线程负责每隔一段时间就把脏页刷新到磁盘。这样可以不影响用户现场处理正常的请求。刷新方式有以下两种：

• 从LRU链表的冷数据中刷新一部分页到磁盘。后台线程会定时从LRU链表尾部开始扫描一些页，扫描页数量可以通过系统变量innodb_lru_scan_depth来指定。如果在LRU链表中发现脏页，则把他们刷新到磁盘，这种刷新页面的方式成BUF_FLUSH_LRU。
• 从FLUSH链表中刷新一部分页到磁盘。后台线程也会定时从FLUSH链表中刷新一部分页到磁盘，刷新的数量取决于当时系统是否繁忙。这种刷页方式称为BUF_FLUSH_LIST。

有时后台线程刷新脏页的进度比较慢，导致用户线程在准备加载一个磁盘页到Buffer Pool中时没有可用的缓冲页。这时就会尝试查看LRU链表尾部，看是否存在可以直接释放掉的未修改缓冲页。如果没有，则不得不将LRU链表尾部的一个脏页同步刷新到磁盘（这样会降低用户请求的速度）。这种将单个页刷新到磁盘中的刷新方式称为BUF_PLUSH_SINGLE_PAGE。当然，在系统特别繁忙时，也可能出现用户线程从FLUSH链表中刷新脏页的情况，很显然，处理用户请求的过程中去刷新脏页是一种严重降低处理速度的行为（毕竟磁盘的速度太慢了）。

刷新机制

1. Redo Log日志满了：为了保证事务的持久性，每当事务提交时，InnoDB会先将修改记录到Redo Log中。当Redo Log达到一定大小后，系统会触发刷新操作，将脏页写回磁盘。
2. Buffer Pool空间不足：当Buffer Pool需要为新的数据页腾出空间时，可能会选择将一些脏页刷新到磁盘，尤其是那些长时间未被访问的脏页。
3. 后台线程刷新：InnoDB有专门的后台线程，定期检查Flush List，将脏页刷新到磁盘。这是为了保证数据的及时持久化。
4. MySQL正常关闭：在数据库关闭过程中，为了保证数据不丢失，InnoDB会将所有的脏页刷新到磁盘。
5. Checkpoint操作：InnoDB会周期性地执行Checkpoint操作，这是一种将脏页刷新到磁盘的过程，以减少系统重启后的恢复时间。

Buffer Pool大小的调整

调整参数innodb_buffer_pool_size

作用：innodb_buffer_pool_size参数用于设置InnoDB Buffer Pool的大小。这是InnoDB用来缓存数据和索引的内存区域。

配置：该参数可以根据服务器的物理内存大小和数据库的需要进行调整。较大的Buffer Pool可以提高缓存命中率，但会占用更多的内存资源。官方建议pool的空间设置为物理内存的50%-75%。

动态调整：在MySQL 5.7及以后的版本中，innodb_buffer_pool_size支持在线动态调整，无需重启数据库服务。

如果你设置的innodb_buffer_pool_size超过了1G的话，应该再修改一下Innodb_buffer_pool_instances=N，将pool分成N个pool实例，将来操作的数据会按照page的hash来映射到不同的pool实例。这样可以大幅优化多线程情况下，并发读取同一个pool造成的锁的竞争。

调整参数innodb_old_blocks_pct

作用：innodb_old_blocks_pct参数用于设置LRU链表中old区域所占的比例。

配置：这个比例影响着young区域和old区域的划分，进而影响数据页在Buffer Pool中的生命周期。较高的比例意味着old区域更大，可能会减少对young区域热数据的影响。

优化：根据工作负载的特点调整此参数，可以帮助减少Buffer Pool污染，提高缓存效率。

InnoDB的LRU和普通的不太一样，Innodb的加入了midpoint位置的概念。最新读取到的页，并不是直接放到LRU列表的头部的，而是放到midpoint位置。这个位置大概是LRU列表的5/8处，该参数由innodb_old_blocks_pct控制。默认值37，表示新读取的页插入到LRU尾端37%的位置。在midpoint之后的列表都是old列表，之前的是young列表，可以简单理解为young列表的页都是最活跃的数据。

为什么不直接把数据页放到LRU链表头部呢？因为某些数据扫描操作需要访问的页很多，有时候这些页仅仅在本次查询有效，以后就不用了，并不算是活跃的热点数据。那么真正活跃的还是希望放到头部去，这些新的暂不确定是否真正未来要活跃。所以，这可以理解为预热。

还引入了一个参数innodb_old_blocks_time用来表示页读取到mid位置后需要等待多久才会被加入到LRU链表的热端（young区域）。

还有一个重要的查询命令可以看到这些信息show engine innodb status;

性能监控与优化

慢SQL监控

• 目的：慢SQL监控是一种数据库性能监控手段，用于识别执行时间超过预定阈值的SQL语句。
• 工具：可以使用MySQL的慢查询日志（slow query log）来记录慢SQL，并通过各种工具（如pt-query-digest等）进行分析。
• 优化：通过分析慢SQL，可以对查询进行优化，比如添加索引、改写查询逻辑或调整数据库结构。

性能抖动问题

• 原因：性能抖动可能是由于多种因素引起的，如脏页刷新、大量短连接、锁争用、资源竞争等。
• 监控：通过监控系统负载、响应时间和事务吞吐量等指标，可以发现性能抖动的迹象。
• 优化：解决性能抖动的方法包括增加Buffer Pool大小、优化SQL语句、调整锁策略、增加硬件资源等。

Change Buffer

Change Buffer（针对于非唯一二级索引页），我们知道InnoDB推荐使用自增主键，插入时主键值时递增的，可以顺序访问。与聚簇索引不同，二级索引通常是不是唯一的，并且以相对随机的顺序插入。类似的，二级索引的更新和删除经常也会影响索引树中不相邻的二级索引数据页。对于二级索引数据变更引起的随机访问，如果每次都进行磁盘IO显然会影响数据库的性能。因此在执行DML语句时，如果这些数据页没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更暂存在Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。这样做的目的是为了减少对磁盘的I/O操作，从而提高数据库的整体性能。

下面我们看看Change Buffer做了哪些事情：

1. Change Buffer用于存储SQL变更操作，比如Insert/Update/Delete等SQL语句；
2. Change Buffer中的每个变更操作都有其对应的数据页，并且该数据页未加载到缓存中；
3. 当Change Buffer中变更操作对应的数据页加载到缓存中后，InnoDB会把变更操作Merge到数据页上；
4. InnoDB会定期加载Change Buffer中操作对应的数据页到缓存中，并Merge变更操作；

Change Buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示Change Buffer的大小最多只能占用Buffer Pool的50%。为了保证数据的一致性、正确性、持久性，会将Change Buffer中的操作应用到原数据，这个过程叫做merge。merge的时候是真正进行数据更新的时刻。

为什么必须是二级索引页，不能是主键索引页？很简单，因为主键索引要保证唯一性的约束，如果把 insert id=1 缓存起来，再次有要 insert id=1 时再缓存起来，则等批量的 apply 时就会出错。change buffer 缓存的操作有三种：

• BTR_INSERT_OP：普通的 insert
• BTR_DELMARK_OP：在用户线程执行 update 和 delete 中，如果发生数据删除行为，会将记录标记为 delete mark
• BTR_DELETE_OP：purge 线程删除二级索引中 delete mark 的数据行

数据结构

space	marker	offset	metadata	secondary index record
表空间id，占用4个字节	区分新老版本，占用1个字节	索引页偏移量，即page_no，占用4个字节	元数据，占用4个字节	辅助索引记录

metadata存储内容如下：

IBUF_REC_OFFECT_COUNT	IBUF_REC_OFFECT_TYPE	IBUF_REC_OFFECT_FLAGS
用来排序每个记录进入Change Buffer的顺序，占用2个字节	对用缓存的操作类型，占用1个字节	表示行格式，占用1个字节

为了保证每次都能merge成功，还需要有一个特殊的页来标记每个辅助索引页（space，page_no）的可用空间。这个就是Insert Buffer Bitmap，每个Insert Buffer Bitmap页用来追踪16384个辅助索引页。每个辅助索引页在Insert Buffer Bitmap存储结构如下：

IBUF_BITMAP_FREE	IBUF_BITMAP_BUFFERED	IBUF_BITMAP_IBUF
表示该辅助索引页的可用空间（占用2个bit）：
0 表示无可用空间; 1 表示剩余空间 > 1/32; 2 表示剩余空间 > 1/16; 3 表示剩余空间 > 1/8	1 表示该辅助索引页有记录被缓存到Change Buffer中（占用1个bit）	1 表示该页是Change Buffer B+ Tree的页

Change Buffer本质是一块写缓存，组织形式是B-tree，存在于系统表空间中。其根节点位于系统表空间的第四页面（FSP_IBUF_TREE_ROOT_PAGE_NO）

ibuf entry layout

其缓存的每一个操作叫做一个 entry，物理结构是：

ibuf entry counter

ibuf entry counter的存在是为了与 space_id 和 page_no 一起构成 entry 的主键，在Change Buffer里对于同一个二级索引页的entry，其entry counter 是递增的

在Change Buffer中插入entry时，先定位到待插入的位置（btr_pcur_open）：

• search tuple: （space_id, page_no, 0xFFFF）为主键
• mode PAGE_CUR_LE（<=）模式搜索 B-tree
• latch_mode是BTR_MODIFY_PREV或BTR_MODIFY_TREE

0xFFFF是最大的entry counter，所以cursor会定位到对应于同一个二级索引页的具有最大counter的entry，记为max_counter。max_counter+1 即为待插入entry的counter。但在每一次ibuf merge，清空了该二级索引页的所有entry后，再插入针对该索引页的新的ibuf entry，counter 又从 0 开始。

工作流程

变更操作什么时候放入Change Buffer

并不是数据库中的所有操作都会进入Change Buffer，满足以下条件的在执行阶段不会修改数据页，而是会进入Change Buffer：

• SQL会修改数据库中的数据；
• SQL语句不涉及唯一键的校验；
• SQL语句不需要返回变更后的数据；
• 涉及的数据页不在缓存中；

Change Buffer什么时候合并到原数据页

Change Buffer中缓存了变更操作，变更数据会先持久化到redo log，最终合并到数据库的原数据页，合并过程称为Merge。

• 访问变更操作对应的数据页；
• InnoDB后台线程定时Merge；
• Buffer Pool空间不足；
• 数据库正常关闭时；
• Redo Log写满时；

执行过程

1. 更改暂存：当一个非主键索引的记录被更新或删除时，这些更改操作首先被暂存到Change Buffer中。Change Buffer使用特定的数据结构来记录更改操作的相关信息，如更改类型（插入、更新或删除）、更改的数据页地址以及更改的内容。
2. 合并更改：当数据页从磁盘上读取到内存中时，Change Buffer中的相关信息会被用来合并这些更改。这意味着，当从非主键索引页读取数据时，如果有相关的更改操作被暂存在Change Buffer中，这些更改会立即被应用到该页上。这样，读取的数据就包含了最新的更改，确保了数据的一致性。
3. 刷新到磁盘：虽然Change Buffer中的更改操作是暂存的，但它们最终还是需要被刷新到磁盘上以保持数据的一致性。在合适的时机，InnoDB会将Change Buffer中的更改操作写入到磁盘上的重做日志中。这一步是必要的，因为如果突然的系统故障或崩溃发生，这些未写回磁盘的更改可能会丢失。
4. 清理过程：随着时间的推移，Change Buffer中的数据可能会老化或不再需要。为了保持Change Buffer的使用效率，InnoDB会定期执行清理过程，移除那些已经过时或不再需要的更改操作信息。

当我们要更新一条普通索引记录的时候：如果这条记录在内存中，那么直接更新内存；如果该记录没有在内存中，会将该数据页从磁盘加载到内存，然后更新Change Buffer（即merge），更新完Change Buffer之后，MySQL会在redo log中记录下Change Buffer的修改。事务就算完成了，后续binlog落盘，redo log commit。

触发时机

Change Buffer的触发时机主要是在非主键索引的更新或删除操作时。当对一个非主键索引的记录进行更改时，这些更改操作首先会被暂存到Change Buffer中：

1. 非主键索引的更新操作： 当一个非主键索引的记录被更新时，Change Buffer会触发并将更改操作暂存到内存中。
2. 非主键索引的删除操作： 当一个非主键索引的记录被删除时，Change Buffer同样会触发并将该删除操作暂存到内存中。
3. 数据页读取操作： 当从非主键索引页读取数据时，Change Buffer会检查该页在Change Buffer中是否有相关的更改。如果有，它会将这些更改应用到该页上，确保读取的数据是最新的。

需要注意的是，Change Buffer触发的时机并不是在每次数据更改时都立即触发。而是将这些更改暂存到内存中的Change Buffer，并在合适的时机（如数据页读取操作时）再将这些更改应用到相应的数据页上。这样可以减少频繁的磁盘I/O操作，提高数据库的性能。此外，Change Buffer的触发时机也受到一些参数和配置的影响。例如，可以通过调整InnoDB存储引擎的相关参数来控制Change Buffer的行为和触发条件。在实际应用中，需要根据具体的业务场景和性能需求进行合理的配置和优化。

优势与限制

优势

• 减少I/O操作：通过暂存非主键索引的更改操作，Change Buffer可以减少频繁的磁盘I/O操作，从而提高数据库的性能。
• 提高数据一致性：确保读取的数据是最新的，减少了数据不一致的风险。
• 优化非主键索引操作：对于大量的非主键索引操作，合理利用Change Buffer可以显著提升数据库的性能和响应速度。

限制

• 内存使用：Change Buffer的使用需要消耗一定的内存资源。需要合理配置以避免过多的内存占用。
• 数据持久性：由于更改是暂存到内存中的Change Buffer中，如果发生突然的系统故障或崩溃，可能会丢失一些未写回磁盘的更改。

为什么只缓存非唯一索引数据

我们通过以下sql案例进行说明：

create table user_info (
	id   int primary key,
	age  int not null,
	name varchar(16),
	sex  bool,
	key(age)
) engine=InnoDB;

非唯一索引更新

假设我们使用SQL语句update user_info set age=6 where id=1修改ID=1的用户的年龄为6，该操作会同时修改年龄索引以及行数据中的年龄，更新步骤如下：

• 如果需要更改的年龄索引页和行数据页在缓存中，直接更新缓存中的数据，并把数据页标记为脏页；
• 如果需要更改的年龄索引页和行数据页不在缓存中，直接把SQL语句update user_info set age=6 where id=1暂存到Change Buffer；

唯一索引更新

假设我们使用SQL语句update user_info set id=2 where id=1修改ID=1的用户的ID为2，该操作会同时修改聚簇索引和行数据，更新步骤如下：

• 如果需要更改的聚簇索引和行数据页在缓存中，直接更新缓存中的数据，并把数据页标记为脏页；
• 如果需要更改的聚簇索引页和行数据页不在缓存中，需要把对应的数据页加载到缓存中，判断修改之后ID是不是符合唯一键约束，然后修改缓存中的数据；

可以看到，由于唯一索引需要进行唯一性校验，所以对唯一索引进行更新时必须将对应的数据页加载到缓存中进行校验，从而导致Change Buffer失效。

通过上述分析，我们知道唯一索引无法使用Change Buffer，那么我们实际使用过程中应该使用普通索引还是唯一索引呢？

对于唯一索引，所有更新操作都需要做唯一性约束的判断，必须将数据页读入内存，直接在内存中更新，不使用Change Buffer 。

对于普通索引，当数据页在内存中时，直接进行更新操作即可；当数据页不在内存中时，直接将更新操作写入Change Buffer。

从等值查询性能角度来看：

• 普通索引在查找到第一个满足条件的数据之后，需要继续向后查找满足条件的数据；
• 唯一索引在查找到第一个满足条件的数据之后，不需要再次向后查找，因为索引具有唯一性；

因为InnoDB引擎是以页为单位读取数据的，读取一条数据时，往往会将临近的数据也读到内存，所以多向后查询几条数据带来的性能差别微乎其微。

从索引修改角度来看：

由于唯一索引无法使用Change Buffer，对索引的修改会引起大量的磁盘I/O，影响数据库性能。

综上可知，如果不是业务中要求数据库对某个字段做唯一性检查，我们最好使用普通索引而不是唯一索引。总结原因主要有以下几点：

• 数据一致性：对于主键或唯一索引页，由于其索引键的唯一性，更改操作通常涉及到行数据的完整更新。这意味着更改操作无法简单地合并到索引页上，因为这可能导致数据的不一致性。因此，对于主键或唯一索引的更改，InnoDB通常会直接进行相应的I/O操作，将更改写回到磁盘上的相应索引页。
• 数据结构差异：主键索引和唯一索引通常采用B+树结构，而普通索引则可能采用其他数据结构。由于Change Buffer针对的是普通索引页的更改，因此其设计更适用于普通索引的数据结构。
• 使用场景：Change Buffer的设计初衷是为了优化非主键索引的更新和删除操作。对于主键或唯一索引，由于其唯一性，通常不需要通过Change Buffer来合并更改。
• 内存使用考虑：将Change Buffer限制于非主键索引页可以更有效地利用内存资源。主键索引通常更频繁地被访问和查询，因此直接进行I/O操作可以确保其数据的最新性。将Change Buffer用于非主键索引页可以减少对内存的竞争，从而更高效地利用内存资源。

适用场景

什么情况下Change Buffer会有较大的性能提升

• 数据库大部分索引是非唯一索引；
• 业务是写多读少，或者不是写后立刻读取；

不适合使用Change Buffer的场景：

• Change Buffer可以优化update、insert、delete。但是对于聚簇索引是无法起到优化作用
• 某些场景对唯一索引也无法启动优化作用。
• 如果会造成数据页分裂、y页合并应该也不会写入Change Buffer。
• 遇到了锁。

相关参数

• innodb_change_buffer_max_size：配置Change Buffer的大小，占整个Buffer Pool的比例，默认值是25%，最大值是50%。写多读少的业务，才需要调大这个值。
• innodb_change_buffering： 这是一个控制Change Buffer行为的参数。通过它可以启用或禁用某些特定的功能。
• • all : 默认值，包含insert，delete_marking和purges.
  • none: 禁用Change Buffer
  • inserts : 缓存插入操作
  • deletes : 缓存删除操作
  • changes: 包含 insert delete-marking
  • purge: 后台的物理删除操作
• innodb_change_buffer_free_percent： 这个参数定义了Change Buffer中保留给将来更改的空间百分比。当Change Buffer的使用率达到这个百分比时，InnoDB会开始将一些更改写入到磁盘上的重做日志中。

如何优化使用

• 调整缓冲池大小

根据数据库的工作负载和可用内存资源，合理配置InnoDB缓冲池的大小。增加缓冲池的大小可以增加Change Buffer的使用空间，但需要注意不要过度消耗内存资源。

大量插入、更新和删除操作：增大innodb_change_buffer_max_size可以帮助提高写入性能，因为它允许更多的更改暂存到内存中，减少了对磁盘的直接写入。如果增大innodb_change_buffer_max_size，确保有足够的内存来容纳更大的缓冲池，以避免其他性能问题。

大量查询操作：减小innodb_change_buffer_max_size可以减少数据页从缓冲池中被淘汰的概率，从而提高查询性能。因为如果缓冲池中的数据经常被替换，那么查询可能经常需要从磁盘读取数据，这会降低性能。

动态设置：innodb_change_buffer_max_size设置是动态的，它允许修改设置而无需重新启动服务器。许多参数都可以在运行时动态地修改，而不需要重启服务器。但是，这种动态调整可能不会立即生效，因为InnoDB有一个内部队列来处理和管理缓冲池中的数据。尽管可以动态地调整这个参数，但在生产环境中更改前应进行充分的测试，以确保它不会对现有的工作负载产生负面影响。

在调整这个参数之前，考虑其他与InnoDB性能相关的参数，如innodb_buffer_pool_size，以确保整体配置的合理性。

• 监控Change Buffer使用情况

通过监控数据库的性能指标和日志文件，可以了解Change Buffer的使用情况。例如，可以监控Change Buffer的命中率、缓冲区使用情况等指标，以便进行适当的调整和优化。

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

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

• 合理设计索引

索引设计是影响Change Buffer性能的关键因素之一。合理设计索引可以减少非主键索引的更新和删除操作的数量，从而减少Change Buffer的使用压力。

• 定期维护和优化数据库

定期进行数据库的维护和优化工作，如重建索引、优化表等操作，可以帮助保持数据库的性能并减少不必要的I/O操作。

• 考虑使用持久化存储引擎

如果数据库需要更高的数据持久性和可靠性要求，可以考虑使用其他持久化存储引擎（如MyISAM）代替InnoDB。但需要注意的是，MyISAM存储引擎在并发写入和高负载场景下可能存在性能瓶颈和限制。

Adaptive Hash Index

hash index

哈希（hash）是一种非常快的查找方法，一般情况下查找的时间复杂度为O(1)，常用于连接（join）操作，如SQL Server和Oracle中的哈希连接（hash join）。但是SQL Server和Oracle等常见的数据库并不支持哈希索引（hash index）。MySQL的Heap存储引擎默认的索引类型为哈希，而InnoDB存储引擎提出了另一种实现方法，自适应哈希索引（adaptive hash index）。

自适应哈希

InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应的。自适应哈希索引通过Buffer Pool的B+树构造而来，因此建立的速度很快，不需要对整个表都建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

根据InnoDB的官方文档显示，启用自适应哈希索引后，读取和写入速度可以提高2倍；对于辅助索引的连接操作，性能可以提高5倍。在我看来，自适应哈希索引是非常好的优化模式，其设计思想是数据库自优化，即无需DBA对数据库进行调整。

Adaptive Hash Index是针对B+树Search Path的优化，因此所有会涉及到Search Path的操作，均可使用此Hash索引进行优化，这些可优化的操作包括：Unique Scan/Range Scan(Locate First Key Page)/Insert/Delete/Purge等等，几乎涵盖InnoDB所有的操作类型。

Adaptive意味着不是所有的页都会以Hash索引维护，页进入Hash 索引的条件是：同种类型的操作(Scan/Insert等)，命中同一页的次数超过此页面记录数量的1/16，则可将当前页加入Hash索引， 用以优化后续可能的相同Search Path。

Adaptive Hash Index用于优化对Buffer Pool数据的查询。InnoDB虽然没有直接支持hash索引，但是给我们提供了一个功能就是这个自适应哈希索引。hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。

InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，所以称之为自适应hash索引。无需人工干预，是系统根据情况自动完成。

使用场景

• 很多单行记录查询，比如用户登录系统时密码的校验。
• 索引范围查询，此时AHI可以快速定位首行记录。
• 所有记录内存能放得下，这时AHI往往是有效的。
• 当业务有大量LIKE或者JOIN，AHI的维护反而可能成为负担，降低系统效率，此时可以手动关闭AHI功能。

注意事项

• AHI目的：缓存索引中的热点数据，提高检索效率，时间复杂度O(1) VS O(N)的差异
• 基于主键的搜索，几乎都是hash searches
• 基于普通索引的搜索，大部分是non-hash searches，小部分是hash searches
• 无序，没有树高，对热点Buffer Pool建立AHI，非持久化
• 初始化为innodb_buffer_pool_size的1/64，会随着Buffer Pool动态调整
• 只支持等值查询（基于主键的等值查询AHI效果更好）
• AHI很可能是部分长度索引，并非所有的查询都能有效果

Adaptive Hash Index 限制

• 只能用于等值比较，例如=、<=>、IN、AND等
• 无法用于排序
• 有冲突可能
• MySQL自动管理，人为无法干预

涉及的参数

show variables like '%adaptive%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| innodb_adaptive_flushing         | ON     |
| innodb_adaptive_flushing_lwm     | 10     |
| innodb_adaptive_hash_index       | ON     |
| innodb_adaptive_hash_index_parts | 8      |
| innodb_adaptive_max_sleep_delay  | 150000 |
+----------------------------------+--------+

• innodb_adaptive_hash_index：该参数影响自适应哈希索引是否启用。默认情况下启用此变量。当我们禁用自适应哈希索引会立即清空哈希表。当哈希表被清空时，正常操作可以继续，并且执行使用哈希表的查询直接访问索引 B 树。当重新启用自适应散列索引时，在正常操作期间会再次填充散列表。
• innodb_adaptive_flushing：该参数影响每秒刷新脏页的操作，默认情况下是启用此变量，刷新脏页会通过判断产生重做日志的速度来判断最合适的刷新脏页的数量，如果关闭该参数会导致你的MySQL的服务器的tps有明显的波动。每当重做日志写满了，MySQL就会停下手头的任务，先把脏页刷到磁盘里，才能继续干活。
• innodb_adaptive_flushing_lwm：该参数可以设置redo log flush低水位线，当需要flush的redo log超过这个低水位时，innodb会立即启用adaptive flushing，默认值10，最小值0，最大值70。
• innodb_adaptive_hash_index_parts：该参数是5.7后InnoDB将自适应哈希索引进行了分区处理，每个区对应一个锁，如果大量地访问，那么可能会对性能产生影响(抢锁)，InnoDB将这个值默认设为8，最小值1，最大值512。
• innodb_adaptive_max_sleep_delay：允许根据当前工作量InnoDB自动调整innodb_thread_sleep_delayup或down的值。任何非零值都可以自动、动态地调整innodb_thread_sleep_delay值，直至达到innodb_adaptive_max_sleep_delay选项中指定的最大值。该值表示微秒数。此选项在具有16个以上InnoDB线程的繁忙系统中很有用。实际上，它对于具有数百或数千个同时连接的MySQL系统最有价值。

虽然在MySQL官方版本中每个版本都是把AHI默认打开，但根据实际生产中的表现来看却差强人意，InnoDB AHI只能在特定的、几乎是只读的场景中才能提高性能；而对于诸如：drop table、truncate table、alter I关掉，执行完后再打开。但这一系列操作比较麻烦，稍有疏忽可能会影响业务，考虑打开AHI弊大于利，最好禁用AHI。

Log Buffer

日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。

Log Buffer如果较大，就可以存储更多的Redo Log，这样一来在事务提交之前我们就不需要将Redo Log刷入磁盘，只需要丢到Log Buffer中去即可。因此较大的Log Buffer就可以更好的支持较大的事务运行；同理，如果有事务会大量的更新、插入或者删除行，那么适当的增大Log Buffer的大小，也可以有效的减少部分磁盘I/O操作。

Log Buffer空间满了会自动写入磁盘，可以通过将将innodb_log_buffer_size参数调大，减少磁盘IO频率。至于Log Buffer中的数据刷入到磁盘的频率，则可以通过参数innodb_flush_log_at_trx_commit来决定。

参数：

innodb_log_buffer_size：日志缓冲区大小

innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个：

• 0：每秒将日志写入并刷新到磁盘一次。
• 1：日志在每次事务提交时写入并刷新到磁盘，默认值，数据不丢失，但是会频繁 IO 操作。
• 2：日志在每次事务提交后写入，并每秒刷新到磁盘一次。