Mysql缓存

BufferPool

应用层
   │
SQL 解析层 / 优化器
   │
存储引擎层（InnoDB）
   ├─ Buffer Pool（缓存页）
   ├─ Redo Log / Undo Log
   ├─ B+Tree 索引结构
   └─ 数据页管理
   │
磁盘存储层

1. Buffer Pool 的作用

Buffer pool 是 **InnoDB 内存中缓存数据页（data page）和索引页（index page）**的区域，。核心作用：

• 减少磁盘 I/O，提高查询性能。
• 将频繁访问的数据保存在内存中，避免每次都读磁盘。
• Buffer Pool 管理的就是 一页一页的内存块，每个页通常是 16KB（InnoDB 默认页大小）。

Buffer Pool是在 MySQL 启动的时候，向操作系统申请的一片连续的内存空间，默认配置下 Buffer Pool只有 128MB。可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小，一般建议设置成可用物理内存的60%~80%。

MySQL 的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，然后在内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？

当然是缓存起来好，这样下次有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据了。

为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

Query Cache因MySql的存储引擎不同而实现略有差异，比如

• MyISAM，缓存的结果集存储在OS
• InnoDB则放在Buffer Pool中。

2. SQL 查询缓存如何存到BufferPool中

SQL和查询结果以键值对方式进行缓存，修改操作会以表单位导致缓存失效

MySQL使用一种哈希机制，将每个要存储或读取的页面分配到其中一个Buffer Pool实例中。这意味着不同的线程可以访问不同的Buffer Pool实例，而不必担心数据页被加载到错误的实例中。此外，MySQL通过数据页缓存哈希表来确保数据页, 不会在不同的Buffer Pool实例中重复加载,

3. MySQL 5.5支持 innodb_buffer_pool_instances多实例, 5.7.5支持chunk

MySQL 5.5版本，支持了innodb_buffer_pool_instances多实例 降低buffer pool的mutex争抢过热情况

MySQL 5.5 及更高版本支持动态调整 innodb_buffer_pool_size 的功能，这意味着可以在不重启数据库服务的情况下调整缓冲池的大小

5.7.5以后 BufferPool的真实结构

现实中Buffer Pool动辄就占用好几G的内存，相对于直接申请几G的内存完成扩容，MySQL有更优雅的实现方式。

为了实现动态调整Buffer Pool的大小。MySQL（5.7.5版本）设计了chunk 机制。

就是将每一个 Buffer Pool Instance 更加细力度化。将Buffer Pool拆分成更小的独立单元。

每个Buffer Pool划分成多个chunk，每个chunk中维护一部分缓存页、缓存页的描述信息。同属于一个Buffer Pool的chunk共享该Buffer Pool的lru、free、flush链表。

块大小由参数innodb_buffer_pool_chunk_size控制，默认值为 128M

4. Buffer Pool 中存储的数据类型

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

1. 数据页（Data Pages）

   • 表中的实际行数据。
   • 例如 user 表的一行数据，会被存储在某个页中，页被加载到 buffer pool 后，就能在内存中快速访问。

2. 索引页（Index Pages）

   • B+ 树索引的节点，包括叶子节点和内部节点。
   • 叶子节点存储行数据或行指针，非叶子节点存储键值和指向子节点的指针。

3. Undo Log Pages（撤销日志页）

   • InnoDB 的事务回滚信息。
   • 用于支持事务的 回滚 和 MVCC（多版本并发控制）。

4. Insert Buffer / Change Buffer

   • 针对非聚簇索引的插入/修改操作做延迟合并优化。
   • 会把变更先缓存在 buffer pool，再异步写入磁盘索引页。

5. 其他元信息

   • 如锁信息、页的状态、LRU 链表等管理结构。
   • 每个页在 buffer pool 中还有一小部分内存用于管理，比如：
     • 页的 状态标记（脏页 dirty / 干净页 clean）
     • 页在 LRU 链表的位置（用于替换算法）
     • 引用计数（当前有多少线程在使用该页）
     • 锁信息（页锁、记录锁等）

Buffer Pool中Undo 页是记录什么？

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

总结

MySQL InnoDB 的 buffer pool 本质上就是 缓存了表的数据页、索引页、Undo log 页等在内存中的副本，以减少磁盘访问，提高性能。

Buffer Pool 内部分区

InnoDB 的 Buffer Pool 维护一个 LRU 链表，用于页替换。LRU 链表分为两个部分

1. 新鲜区（new / hot）

   • 最近访问或修改的页
   • 占 1/8 左右（新加载或刚修改）

2. 老旧区（old / cold）

   • 其他页
   • 占 7/8 左右
   • 其中约 5/8 是频繁访问的热页，2/8 是真正的冷页

所以，“5/8 热数据、3/8 冷数据”是 经验比例，用于描述 Buffer Pool 中哪部分页是访问频繁的。

---

BufferPool 三个双向链表 维护缓存页的描述信息

MySQL除了缓存页之外，Buffer Pool中存在三个双向链表，这三个双向链表中维护着缓存页的描述信息。分别是

FreeList（空闲链表）

1.FreeList 通常用于描述磁盘页的管理, 为了加载时明确知道Buffer Pool中哪些缓存页是空闲的, InnoDB需要Page时从Free List中获取，如果Free List为空，即没有任何空闲Page，则会从LRUList淘汰旧的Page、Flush脏Page 来回收Page。

在Innodb初始化时， 会有Butter chunks中的所有Page都加入到FreeList, ,所有初始化好的空闲缓存页对)应的描述数据都会放入free链表中,每个节点都会双向链接自己前后节点,组成一个双向链表。当数据从表中删除时，或者表中的数据页被移出缓存池时，相应的数据页就会变为空闲状态，而 FreeList 负责追踪这些可用的空闲页，以便在需要时重用。

Buffer Pool加载磁盘上数据页的流程是什么？

• 第一步,从free链表中获取空闲缓存页
• 第二步,加载数据到buffer pool的空闲缓存页中
• 第三步,从free链表里移除该节点

LRUList（最近最少使用列表）

所有从数据文件中新读取进来的Page都会級存在URU List，井通过LRU策路对这些Page进行管理。LRU List实际划分为Young（5/8热数据）（和Old（3/8冷数据）两个部分，当InnoDB读取Page时，会先从当前Buller Poo Instance的page_hash查找，分为三种情况：

• a.如果在page_hash找到，即Page在LRU List中，則会判断Page是在Old区还是Young区，如果是在Old区，在读取完Page后会把它添加到Young区的链表头部

• b.如果在page_hash找到，并且Page在Young区，需要判断Page所在Young区的位置，只有Page处于Young区总长度大约1/4的位置之后，才会将其添加到Young区的链表头部。

• c.如果未能在page_hash找到，则去磁盘读取page，将其添加到old头部（3/8的位置）。

LRU List采用非常精细的LRU淘汰策略来管理Page，并且用以上机制避免了频繁对LRU 链表的调整。

FlushList（刷新列表）：

在 MySQL 中，FlushList 通常用于描述 InnoDB 写入后台线程（InnoDB Flush Background Thread）的工作。当有数据被修改，并且需要将这些修改持久化到磁盘上时，InnoDB 会将这些修改写入到一个称为 redo log 的日志文件中，并将相应的数据页标记为“脏页”。然后，后台线程会定期地将这些脏页从内存中刷新到磁盘上，以确保数据持久化。FlushList 用于跟踪哪些数据页是需要被刷新到磁盘上的，以及它们的刷新顺序。FlushList中的节点存放就是被修改了脏数据页的描述信息块。

所有保存在Fush List上的数据都会在LRU LIist中，但在LRU List中的致据不一定都在Fush List中。

如何一个页被修改多次，怎么处理落盘？

在Flush List上的每个Page都会保存其最早修改的Isn，即oldest_modification，虽然一个Page可能披修改多次，但只记录最早的修改。
Flush List上的Page会按照其各自的oldest_medfification进行降序排序，链表尾部保存oldest_modification最小的Page，在需要从Flush List中回收Page时，从尾部开始回收。

实际上默认情况下，磁盘中存放的数据页的大小是16KB，也就是说，一页数据包含了16KB的内容。 而Buffer Pool中存放的一个一个的数据页，我们通常叫做缓存页，因为毕竟Buffer Pool是一个缓冲池，里面的数据都是从磁盘缓存到内存去的。 而Buffer Pool中默认情况下，一个缓存页的大小和磁盘上的一个数据页的大小是一一对应起来的，都是16KB。 我们看下图，我给图中的Buffer Pool标注出来了他的内存大小，假设他是128MB吧，然后数据页的大小是16KB。

缓存页对应的描述信息是什么？

Buffer Pool中维护的数据结构是缓存页，而且每个缓存页都有它对应的描述信息。

缓存页对应的描述信息是MySQL Buffer Pool中用于管理缓存页的重要数据结构。每个缓存页都会有一个对应的描述信息块，其中包含了关于缓存页的元数据信息。这些信息主要包括：

1. 数据页所属的表空间编号：标识缓存页属于数据库中的哪个表空间。
2. 数据页的编号：缓存页在表空间中的唯一编号。
3. 缓存页在Buffer Pool中的地址：记录了缓存页在Buffer Pool中的存储位置。
4. 链表节点信息：由于Buffer Pool中使用多个链表（如LRU链表、free链表、flush链表）来管理缓存页，描述信息中包含了这些链表操作所需的节点信息。
5. 锁信息：如果缓存页被锁定，描述信息中会记录相关的锁信息，以确保数据的一致性。
6. LSN（Log Sequence Number）信息：用于故障恢复时能够定位到正确的日志位置。

MySQL刚启动，还没有从磁盘中读取任何数据页到内存（Buffer Pool）中，那此时Buffer Pool中所有的缓存页其实都是空的。

除了缓存页之外，Buffer Pool中存在三个双向链表。分别是FreeList、LRUList以及FlushList。这三个双向链表中维护着缓存页的描述信息。

加载数据流程

假设读取出来了1个数据页

当你通过select读取出一个数据页之后，是需要将这个数据页加载进Buffer Pool中的缓存页中的。

那问题来了，MySQL怎么知道该将你读取出来的数据页存放在那个缓存页中呢？相信你看了上图应该也能想到答案了。FreeList这个双向链表不是存放了空闲的缓存页的描述信息吗？那从FreeList中取出一个空间缓存页的描述信息不就好了？于是得到了下面这张图：

nnoDB会将你读取出来的数据页加载进Buffer Pool中的缓存页中，然后缓存页的描述信息也会被维护进LRU链表中。链表做了冷热数据分离优化，5/8的区域是热数据区域，3/8的区域算是冷数据区域。（本质上它们都是双向链表），而你新读取的数据页会被放在冷数据区的靠前的位置上。

如果你将该数据页读取出来加载进缓存页中后，间隔没到1s，就使用该缓存页。那么InnoDB是不会将这个描述信息移动到5/8的热数据区域的。

但是当超过1s后，你又去读这个数据页。那这个数据页的描述信息就会被放到热数据区域。如下图：

假设一次性读取出来了好多数据页

MySQL是存在预读机制的，假设触发了MySQL的预读机制。一次性从磁盘中读取来N多个缓存页。会得到下面这张图：

因为发生了预读，所以你的一次磁盘IO读出了大量的数据页，但是这些数据页中很可能是有一些是你根本不需要的，仅仅是预读把它们级联查出来了。这时按老规矩，从FreeList中找到空闲的缓存页信息，然后将其从FreeList中移除。根据找到的空闲缓存页的描述信息，将从磁盘中读取出来的数据页加载进去。相应的该缓存页的描述信息也会被维护进LRU链表的冷数据区域。

这时你就会发现这种冷热数据分离的机制多么妙！即使发生了预读又怎么样？根本没有机会将热数据区的描述信息1挤下去。当内存不够用了需要将部分缓存页刷新到磁盘中时，那就从冷数据区域开始刷新好了，反正他们本来就不经常被使用。

同样的，当你超过1s后又访问了冷数据区的缓存页，比如访问了缓存页66和数据页67，该缓存页对应的描述信息是会被提升到热数据区，于是有了下面这张图：

更新数据流程

假设修改了某数据页

假设你执行了update xxx set xxx where id in (xxx,xxx,xxx,xxx)；

而符合条件的数据行恰巧就在描述信息1、描述信息66、描述信息67所指向的缓存页中，那BufferPool中会发生什么呢？

如下图：

你会看到，被你修改了的缓存页的描述信息，被添加到了FlushList这个双向链表中。

想必看到这里你已经知道了，原来FlushList中的节点存放就是被修改了脏数据页的描述信息块。

随着MySQL被使用的时间越来越长，BufferPool的大小就越来越小。等它不够用的时候，就会将部分LRU中的数据页描述信息移除出去，这时如果发现被移除出来的数据页在FLushList中，就会触发fsync的操作，触发随机写磁盘。如果该数据页是干净的，那移除出去就好了。其他也不用干啥。

举个例子：假设需要将描述信息66、描述信息67指向的缓存页落盘。会得到下面这张脑图：

Buffer Pool中的描述数据大概相当于缓存页大小的5%左右，也就是每个描述数据大概是800个字节左右的大小，然后假设你设置的buffer pool大小是128MB，实际上Buffer Pool真正的最终大小会超出一些，可能有个130多MB的样子，因为他里面还要存放每个缓存页的描述数据。

https://zhuanlan.zhihu.com/p/399592958

MySQL通过配置多个Buffer Pool来优化数据库的并发性能？

1、多线程在访问Buffer Pool的时候需要加锁吗？

那么大家思考一下，现在多个线程来并发的访问这个Buffer Pool了，此时他们都是在访问内存里的一些共享的数据结构，比如说缓存页、各种链表之类的，那么此时是不是必然要进行加锁？

对，多线程并发访问一个Buffer Pool，必然是要加锁的，让一个线程先完成一系列的操作，比如说加载数据页到缓存页，更新free链表，更新lru链表，然后释放锁，接着下一个线程再执行一系列的操作。

2、多线程并发访问会加锁，数据库的性能还能好吗？
既然我们已经解决了第一个问题，就是多线程并发访问一个Buffer Pool的时候必然会加锁，然后很多线程可能要串行着排队，一个一个的依次执行自己要执行的操作，那么此时我问大家第二个问题，此时数据库的性能还能好吗？

应该这么说，即使就一个Buffer Pool，多个线程会加锁串行着排队执行，其实性能也差不到哪儿去。

因为大部分情况下，每个线程都是查询或者更新缓存页里的数据，这个操作是发生在内存里的，基本都是微秒级的，很快很快，包括更新free、flush、lru这些链表，因为都是基于链表进行一些指针操作，性能也是极高的。

所以即使每个线程排队加锁，然后执行一系列操作，数据库的性能倒也是还可以的。但是再怎么可以，也毕竟也是每个线程加锁然后排队一个一个操作，这也不是特别的好，特别是有的时候你的线程拿到锁之后，他可能要从磁盘里读取数据页加载到缓存页里去，这还发生了一次磁盘IO呢！所以他要是进行磁盘IO的话，也许耗时就会多一些，那么后面排队等他的线程自然就多等一会儿了！

因此这里我们给大家介绍一个MySQL的生产环境优化经验，就是可以给MySQL设置多个Buffer Pool来优化他的并发能力。

一般来说，MySQL默认的规则是，如果你给Buffer Pool分配的内存小于1GB，那么最多就只会给你一个Buffer Pool。

但是如果你的机器内存很大，那么你必然会给Buffer Pool分配较大的内存，比如给他个8G内存，那么此时你是同时可以设置多个Buffer Pool的，比如说下面的MySQL服务器端的配置。

[server]
innodb_buffer_pool_size = 8589934592
innodb_buffer_pool_instances = 4

所以这样的话，一旦你有了多个buffer pool之后，你的多线程并发访问的性能就会得到成倍的提升，因为多个线程可以在不同的buffer pool中加锁和执行自己的操作，大家可以并发来执行了！

所以这个在实际生产环境中，设置多个buffer pool来优化高并发访问性能，是mysql一个很重要的优化技巧。

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

优点

• 将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

• 数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

无法使用 Change Buffer 的更新操作

1. 唯一索引的更新操作

• 唯一性检查：唯一索引必须保证实时唯一性，因此在执行插入或更新操作时，必须立即验证是否存在相同值的数据。如果使用 Change Buffer 缓存变更，无法实时校验唯一性，可能导致重复数据插入或更新，破坏数据的完整性。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

2. 主键索引的更新操作

• 数据一致性：主键索引页的更新操作通常涉及到行数据的完整更新，这些操作无法简单地合并到索引页上，因为这可能导致数据的不一致性。因此，对于主键索引的更新，InnoDB 通常会直接进行相应的 I/O 操作，将更改写回到磁盘上的相应索引页。

3. 写入后立即读取的场景

• 立即读取：如果业务模式是写入数据后立即读取，即使满足了条件，将更新先记录在 Change Buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 I/O 的次数不会减少，反而增加了 Change Buffer 的维护代价。因此，这种业务模式下，Change Buffer 反而起到了副作用。

适用 Change Buffer 的场景

• 非唯一二级索引的更新操作：
  • 插入操作：非唯一二级索引的插入操作可以使用 Change Buffer。如果数据页不在缓冲池中，InnoDB 会将插入操作记录在 Change Buffer 中，稍后在数据页加载到缓冲池时再合并。
  • 更新操作：非唯一二级索引的更新操作可以使用 Change Buffer。更新操作分为标记删除和实际删除两个步骤，标记删除操作可以记录在 Change Buffer 中。
  • 删除操作：非唯一二级索引的删除操作可以使用 Change Buffer。删除操作可以记录在 Change Buffer 中，稍后在数据页加载到缓冲池时再合并。

Change Buffer 的触发时机

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。

• 数据页加载到缓冲池时：当数据页从磁盘加载到缓冲池时，Change Buffer 中的相关更改会被合并到数据页中。
• 系统后台定时触发：系统后台定时触发 merge 操作，将 Change Buffer 中的更改合并到数据页中。
• 数据库正常关闭时：数据库正常关闭时，Change Buffer 中的更改会被合并到数据页中，并持久化到磁盘。
• redo log 写满时：当 redo log 写满时，Change Buffer 中的更改会被合并到数据页中，并持久化到磁盘。

Change Buffer 的相关参数

• innodb_change_buffer_max_size：配置 Change Buffer 的最大大小，占整个缓冲池的比例，默认值是 25%，最大值是 50%。
• innodb_change_buffering：配置哪些写操作启用 Change Buffer 功能，可选值为 all、none、inserts、deletes、changes、purges。默认值为 all。

希望这些信息对你有所帮助！

什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

了解完change buffer，结合我们的场景，假设现在正准备插入一条(4, ‘李四’),我们大致可以分为以下两种情况：

1.这个记录要更新的目标页在内存中：

对于唯一索引，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。
此时，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。可以忽略

2.这个记录要更新的目标页不在内存中

对于唯一索引，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引，则是将更新记录在 change buffer，语句执行就结束了。
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 优点

• 如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。

• 数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

MySQL查询缓存详解 cache

缓存条件，原理

MySQL Query Cache是用来缓存我们所执行的SELECT语句以及该语句的结果集，MySql在实现Query Cache的具体技术细节上类似典型的KV存储，就是将SELECT语句和该查询语句的结果集做了一个HASH映射并保存在一定的内存区域中。

查询缓存的工作流程如下:

1：命中条件

缓存存在一个hash表中,通过查询SQL,查询数据库,客户端协议等作为key.在判断是否命中前,MySQL不会解析SQL,而是直接使用SQL去查询缓存,SQL任何字符上的不同,如空格,注释,都会导致缓存不命中.

如果查询中有不确定数据,例如CURRENT_DATE()和NOW()函数,那么查询完毕后则不会被缓存.所以,包含不确定数据的查询是肯定不会找到可用缓存的

2：工作流程

1. 服务器接收SQL,以SQL和一些其他条件为key查找缓存表(额外性能消耗)

2. 如果找到了缓存,则直接返回缓存(性能提升)

3. 如果没有找到缓存,则执行SQL查询,包括原来的SQL解析,优化等.

4. 执行完SQL查询结果以后,将SQL查询结果存入缓存表(额外性能消耗)

缓存数据失效时机

1：在表的结构或数据发生改变时，查询缓存中的数据不再有效。有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。

当某个表正在写入数据,则这个表的缓存(命中检查,缓存写入等)将会处于失效状态.在Innodb中,如果某个事务修改了表,则这个表的缓存在事务提交前都会处于失效状态,在这个事务提交前,这个表的相关查询都无法被缓存.

2：清理查询缓存 & 减少碎片策略

FLUSH QUERY_CACHE; 清理查询缓存内存碎片

RESET QUERY_CACHE; 从查询缓存中移出所有查询

FLUSH TABLES; 关闭所有打开的表，同时该操作将会清空查询缓存中的内容

1. 选择合适的block大小

2. 使用 FLUSH QUERY CACHE 命令整理碎片.这个命令在整理缓存期间,会导致其他连接无法使用查询缓存

PS: 清空缓存的命令式 RESET QUERY CACHE

InnoDB与查询缓存

Innodb会对每个表设置一个事务计数器,里面存储当前最大的事务ID.当一个事务提交时,InnoDB会使用MVCC中系统事务ID最大的事务ID跟新当前表的计数器.

只有比这个最大ID大的事务能使用查询缓存,其他比这个ID小的事务则不能使用查询缓存.

另外,在InnoDB中,所有有加锁操作的事务都不使用任何查询缓存

什么是页分裂？

我们要知道，InnoDB 不是按行来操作数据的，它可操作的最小单位是页，页加载进内存后才会通过扫描页来获取行记录。

比如查询id=111，是获取111所在的数据页，加载进内存后取出111这一行。
页的默认大小为16KB，64个连续的数据页称为一个extent（区），64个页组成一个区，所以区的大小为1MB(16*64=1024)，连续的256个数据区称为一组数据区；

两个数据页之间会有指针指向上一个和下一个数据页，形成一个双向链表，数据页中的每个数据行之间会有单向指针连接，组成一个单向链表；

当一个数据页中的数据行太多放不下的下，就会生成一个新的数据页来存储，同时使用双向链表来相连；

使用索引时，一个最基础的条件是，后面数据页中的数据行的主键值要大于前一个数据页中数据行的主键值；

至于原因，其实索引简单来说，就是一遍一遍过筛子，通过二分法的逻辑不断减少要筛选的数据，而真实数据是按主键顺序存储的，所以主键值就是筛选标准，以便尽快定位我们需要的数据；

我们假设如下的前两行数据已满足凑成一页的条件：

• 如果我们设置了主键ID自定义，非自增，在已经插入了1、5、6、7(已分为两页)等ID的情况下，再插入ID2，就会触发页分裂；
• 因为我们需要保证，后一个数据页中的所有主键值要比前一个数据页中的主键值大；
• 这时我我们需要把2插入到1的后面，5、6、7等ID依次后移；
• 注意，这里不是单纯的移动ID，而是要带着数据一起搬家！另外，每一行数据所占用的空间是不固定的，有可能移动之后，一页空间存不下5、6、7三条数据，需要同时生成第三页存放7ID;
• 如果第三页已经存在了咋办，那就得生成第N页，同时修改第二、三页和第N页的指针，调整到符合要求；
  所以如果插入的主键是乱序，为满足索引条件，可能会产生频繁的页分裂，从而导致更新效率变低； 当然了，真正的页分裂要比上面所说的复杂很多，但本质是通过这种逻辑来完成页分裂的。

MySQL日志

MySQL日志主要包括
错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

其中比较重要的就是二进制日志binlog（归档日志）、事务日志redo log（重做日志）和undo log（回滚日志）。

日志log记录流程

binlog（mysql server层日志、二进制日志 ）

binlog 是 一种二进制日志，做数据库主从同步。其主要是用来记录对 MySQL 数据更新或潜在发生更新的 SQL 语句，并以 “事务”的形式保存在磁盘中。

MySQL 基础架构分为 Server 层和存储引擎层两部分。

• Server 层的日志是 binlog，记录 SQL 语句的原始逻辑，用来归档和复制。
• InnoDB 引擎的日志是 redo log，记录数据页的修改，用来实现 crash-safe 能力。

查看数据库binlog模式：SHOW VARIABLES LIKE 'binlog_format';

binlog 主要有以下作用：

• 复制：MySQL 主从复制在 Master 端开启 binlog，Master 把它的二进制日志传递给 slaves 并回放来达到 master-slave 数据一致的目的
• 数据恢复：用于数据库的基于时间点的还原（ mysqlbinlog 工具恢复数据）
• 增量备份

几个知识点：

• binlog 不会记录不修改数据的语句，比如Select或者Show
• binlog 会重写日志中的密码，保证不以纯文本的形式出现
• MySQL 8 之后的版本可以选择对 binlog 进行加密
• 具体的写入时间：在事务提交的时候，数据库会把 binlog cache 写入 binlog 文件中，但并没有执行fsync()操作，即只将文件内容写入到 OS 缓存中。随后根据配置判断是否执行 fsync。
• 删除时间：保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

binlog日志三种格式，Statement、MiXED、ROW

binlog 的不同模式有什么区别呢？

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

  但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。

  所以使用 Statement 格式会出现一些数据一致性问题。

• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

rr rc 的主从复制，binlog区别？

RC 隔离级别不支持 statement 格式的bin log，因为该格式的复制，会导致主从数据的不一致；只能使用 mixed 或者 row 格式的bin log; 这也是为什么MySQL默认使用RR隔离级别的原因。

复制时，我们最好使用：binlog_format=row**

redo log（重做日志，InnoDB独有）

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL有了崩溃恢复的能力。

redo log 的作用主要是实现 ACID 中的持久性，保证提交的数据不丢失，它记录了事务提交的变更操作，服务器意外宕机重启时，利用 redo log 进行回放，重新执行已提交的变更操作

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。循环覆盖较旧的日志

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

redo日志格式

type：该条redo日志的类型，redo日志设计大约有53种不同的类型日志。
space ID：表空间ID。
page number：页号。
data：该条redo日志的具体内容。（比如：某个事务将系统表空间中的第100号页面中偏移量为1000处的那个字节的值1改成2）

后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

redo log刷盘时机

重做日志用来实现事务的持久性，即D特性。它由两部分组成：

• ①内存中的重做日志缓冲

• ②重做日志文件

• 事务提交时，首先将变更写入 redo log，事务就视为成功。至于数据页（表、索引）上的变更，可以放在后面慢慢做

  • 数据页上的变更宕机丢失也没事，因为 redo log 里已经记录了
  • 数据页在磁盘上位置随机，写入速度慢，redo log 的写入是顺序的速度快
    它由两部分组成，内存中的 redo log buffer，磁盘上的 redo log file
  • redo log file 由一组文件组成，当写满了会循环覆盖较旧的日志，这意味着不能无限依赖 redo log，更早的数据恢复需要 binlog
  • buffer 和 file 两部分组成意味着，写入了文件才真正安全，同步策略由下面的参数控制
  • innodb_flush_log_at_trx_commit
    • 0 - 每隔 1s 将日志 write and flush 到磁盘
    • 1 - 每次事务提交将日志 write and flush（默认值）
    • 2 - 每次事务提交将日志 write，每隔 1s flush 到磁盘，意味着 write 意味着写入操作系统缓存，如果 MySQL 挂了，而操作系统没挂，那么数据不会丢失

redo log的两阶段提交

当有数据修改时，会先将修改redo log cache和binlog cache然后在刷入到磁盘形成redo log file，当redo log file全都刷入到磁盘时（prepare 状态）和提交成功后才能将binlog cache刷入磁盘，

当binlog全部刷新到磁盘后会记录一个xid，然后在relo log file上打上commit标志（commit阶段）

说明：

• prepare: redo log 写入 log buffer，并 fsync() 持久化到磁盘，在redo log事务记录 2PC 的 XID, 并在redo log事务上打上 prepare 标识；

• commit: binlog 写入 log buffer ，并 fsync() 持久化到磁盘，在 binlog 事务中记录 2PC 的 XID，同时在 redo log事务上打上 commit 标识。

注意：prepare 和 commit 阶段所提到的“事务”，都是指内部XA事务，即2PC

redo log 为什么要用 XA 事务号？

因为：

• redo log 是 InnoDB 的日志

• binlog 是 Server 层的日志

两者属于 不同模块，需要一个 共同的事务编号 XID 来关联，恢复时 MySQL 根据 XID 判断事务是否需要补充或回滚

redo log刷盘时机及配置 innodb_flush_log_at_trx_commit

为了保证事务的持久性，用户线程在事务提交时需要将该事务执行过程中产生的所有redo日志都刷新到磁盘上。会很明显的降低数据库性能。如果对事务的持久性要求不是那么强烈的话，可以选择修改一个称为innodb_flush_log_at_trx_commit的系统变量的值。
该变量有3个可选的值：

0：当该系统变量值为0时，表示在事务提交时不立即向磁盘中同步redo日志，这个任务是交给后台线程做的。 这样很明显会加快请求处理速度，但是如果事务提交后服务器挂了，后台线程没有及时将redo日志刷新到磁盘，那么该事务对页面的修改会丢失。

1：当该系统变量值为1时，表示在事务提交时需要将redo日志同步到磁盘，可以保证事务的持久性。1也是innodb_flush_log_at_trx_commit的默认值。

2：当该系统变量值为2时，表示在事务提交时需要将redo日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘。 这种情况下如果数据库挂了，操作系统没挂的话，事务的持久性还是可以保证的，但是操作系统也挂了的话，那就不能保证持久性了。

MySQL崩溃恢复时怎么办？

Crash Recovery 阶段逻辑：

遍历 redo log：，根据XID关联数据

• 情况 A：

  binlog 有，redo log prepare + commit→ 事务完整 → 恢复成功

• 情况 B：

  binlog 有，redo log 只有 prepare（还没 commit）→ binlog 已提交→ redo log 补写 commit → 事务提交

• 情况 C：

  binlog 没写成功，但 redo log 是 prepare→ 认为事务没成功 → 回滚事务

undo log（回滚日志）

undo log 和 redo log 其实都不是 MySQL 数据库层面的日志，而是 InnoDB 存储引擎的日志。二者的作用联系紧密，事务的隔离性由锁来实现，原子性、一致性、持久性通过数据库的 redo log 或 redo log 来完成。

• redo log 又称为重做日志，用来保证事务的持久性
• undo log:
  • 用来保证事务的原子性和 MVCC，old version 就存储在 undo log 里
  • 主要为事务的回滚, undo 恢复事务修改前的旧值

undo log是逻辑日志，可以理解为:

• 当delete一条记录时，undo log中会记录一条对应的insert记录
• 当insert一条记录时，undo log中会记录一条对应的delete记录
• 当update一条记录时，它记录一条对应相反的update记录

undo log刷盘时机

undo log，它的写入和刷盘是在事务执行期间进行的，每当事务执行一次更改操作时，对应的undo log就会被写入内存缓冲区，并在一段时间后被刷入磁盘，以保证事务的原子性和一致性。

关键点：Undo 的内容本身不用立即刷盘，因为 redo log 记录了它的生成。

也就是说：

• undo 是普通表空间中的数据页
• 真正保证 undo 持久化的是 redo log
• 只要 redo log 已经写入并持久化（prepare 阶段），undo log 即便还没落盘，也能从 redo 恢复

Undo page 的落盘时机：

时机	原因
后台清理 checkpoint 落盘	buffer pool 需要换页
Undo 页变冷	LRU 冷页被刷出
活跃事务少时 purge 线程清理	MVCC 清理

Undo 不需要在提交时刷盘，因为 redo 已经保证它可恢复。

阶段	操作	是否立即刷盘
写 Undo	写入 Undo Page（Buffer Pool）	❌不会
写 redo（记录 undo 页变化）	顺序写 WAL	✔redo 会刷盘
Undo page 落盘	被 checkpoint/淘汰	⏳延后刷盘

Undo Log 如何实现 MVCC

InnoDB 的 MVCC 基于 版本链 (Version Chain)：

每一行都有：

• 隐藏列：DB_TRX_ID（最后修改该行的事务 ID）
• 隐藏列：DB_ROLL_PTR（指向 undo log 的指针）

当一行被更新：

原记录A(最新)   ←指向—  Undo Log1 (旧版本)
                           ↑
                           Undo Log2 (更旧版本)

MVCC 三大核心数据结构

一个行记录包含：

1. DB_TRX_ID
   最后写该行的事务 ID

2. DB_ROLL_PTR
   指向该行更新前的 undo log 记录

3. 版本链（undo 多条记录）
   每次 UPDATE/DELETE 都会创建一条 undo 记录：

MVCC 怎么判断“某行是否对当前事务可见”？

InnoDB 会根据：

• 当前读视图（Read View）
• 行的 trx_id（DB_TRX_ID）
• 版本链上的旧版本

决定哪个版本对当前事务可见。

Read View 规则（核心）：

若行版本的 trx_id：

1. 小于 Read View 的 min_trx_id → 可见
2. 大于 Read View 的 max_trx_id → 不可见
3. 介于两者之间 → 看是否在活跃事务列表中

如果不可见 → 顺着 undo log 向后找旧版本。

---

举例说明 MVCC

假设 3 个事务：

事务	开始时间	行修改
T1	最早	更新行
T2	稍后	开始读取
T3	修改行	更新又产生版本 2

当 T2 读取时，发现行的 trx_id 是 T3：

• T3 事务 ID > T2 可见范围
  → 不可见
  → 跟随 row 的 roll_ptr 找 undo
  → 得到 T1 的版本
  → 这是 T2 能看到的版本

---

Canal订阅日志格式

Canal为变更日志提供了一种统一的格式，以下是一个从MySQL products 表捕获的变更操作的简单示例

{
  "data": [
    {
      "id": "111",
      "name": "scooter",
      "description": "Big 2-wheel scooter",
      "weight": "5.18"
    }
  ],
  "database": "inventory",
  "es": 1589373560000,
  "id": 9,
  "isDdl": false,
  "mysqlType": {
    "id": "INTEGER",
    "name": "VARCHAR(255)",
    "description": "VARCHAR(512)",
    "weight": "FLOAT"
  },
  "old": [
    {
      "weight": "5.15"
    }
  ],
  "pkNames": [
    "id"
  ],
  "sql": "",
  "sqlType": {
    "id": 4,
    "name": 12,
    "description": 12,
    "weight": 7
  },
  "table": "products",
  "ts": 1589373560798,
  "type": "UPDATE"
}

注：请参考 Canal 文档 以了解每个字段的含义

Canal是一款CDC（变更数据捕获）工具，能够实时捕获MySQL的数据变化并将其流式传输到其他系统中。Canal为变更日志提供了一种统一的格式，并支持使用 JSON 和 protobuf（Canal默认使用protobuf）进行消息的序列化

https://github.com/alibaba/canal/blob/5cc85ff2ded1a88fbb13c80da2f72c0aa3306e3d/connector/core/src/main/java/com/alibaba/otter/canal/connector/core/consumer/CommonMessage.java

package com.alibaba.otter.canal.connector.core.consumer;

import java.io.Serializable;
import java.util.List;
import java.util.Map;

public class CommonMessage implements Serializable {

    private static final long         serialVersionUID = 2611556444074013268L;

    private String                    database;                               // 数据库或schema
    private String                    table;                                  // 表名
    private List<String>              pkNames;
    private Boolean                   isDdl;
    // 类型:INSERT/UPDATE/DELETE
    private String                    type;
    // binlog executeTime, 执行耗时
    private Long                      es;
    // dml build timeStamp, 同步时间
    private Long                      ts;
    // 执行的sql,dml sql为空
    private String                    sql;
    // 数据列表
    private List<Map<String, Object>> data;
    // 旧数据列表,用于update,size和data的size一一对应
    private List<Map<String, Object>> old;

    public String getDatabase() {
        return database;
    }

    public void setDatabase(String database) {
        this.database = database;
    }

    public String getTable() {
        return table;
    }

    public void setTable(String table) {
        this.table = table;
    }

    public List<String> getPkNames() {
        return pkNames;
    }

    public void setPkNames(List<String> pkNames) {
        this.pkNames = pkNames;
    }

    public Boolean getIsDdl() {
        return isDdl;
    }

    public void setIsDdl(Boolean isDdl) {
        this.isDdl = isDdl;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public Long getTs() {
        return ts;
    }

    public void setTs(Long ts) {
        this.ts = ts;
    }

    public String getSql() {
        return sql;
    }

    public void setSql(String sql) {
        this.sql = sql;
    }

    public List<Map<String, Object>> getData() {
        return data;
    }

    public void setData(List<Map<String, Object>> data) {
        this.data = data;
    }

    public List<Map<String, Object>> getOld() {
        return old;
    }

    public void setOld(List<Map<String, Object>> old) {
        this.old = old;
    }

    public Long getEs() {
        return es;
    }

    public void setEs(Long es) {
        this.es = es;
    }

    public void clear() {
        database = null;
        table = null;
        type = null;
        ts = null;
        es = null;
        data = null;
        old = null;
        sql = null;
    }

    @Override
    public String toString() {
        return "CommonMessage{" + "database='" + database + '\'' + ", table='" + table + '\'' + ", pkNames=" + pkNames
               + ", isDdl=" + isDdl + ", type='" + type + '\'' + ", es=" + es + ", ts=" + ts + ", sql='" + sql + '\''
               + ", data=" + data + ", old=" + old + '}';
    }
}

canal 投递的binlog

{
    "data": [
        {
            "id": "12965868",
            "name": "",
            "editer_name": "",
            "user_id": "1569055836",
            "customer_type": "2",
            "brand_id": "3",
            "series_id": "0",
            "spu_id": "0",
            "guide_product_id": "0",
            "provider": "1",
            "cooperate_type": "2",
            "dispatch_type": "1",
            "category_id": "0",
            "photo_url": "",
            "photo_urls": "",
            "product_update_time": "2024-02-20 11:18:53",
            "serial_no": "",
            "components": "防尘袋;包装盒",
        }
    ],
    "database": "aplum",
    "es": 1708399133000,
    "id": 47258465,
    "isDdl": false,
    "mysqlType": {
        "id": "int unsigned",
        "name": "varchar(128)",
        "editer_name": "varchar(256)",
        "user_id": "int unsigned",
        "customer_type": "enum('man','woman','adult','child')",
        "brand_id": "int unsigned",
        "series_id": "int unsigned",
        "spu_id": "int",
        "guide_product_id": "int",
        "provider": "enum('secondhand','designer','resell','plum','reclo','big_customer','vip','brandnew','zhongxin')",
        "cooperate_type": "enum('deposit','negotiate','seller_define_price','has_floor_price','commission')",
        "dispatch_type": "enum('plum','seller','oversea')",
        "category_id": "int unsigned",
        "photo_url": "varchar(1024)",
        "photo_urls": "varchar(2048)",
    },
    "old": [
        {
            "product_update_time": "2024-02-18 17:28:29",
            "components": ""
        }
    ],
    "pkNames": [
        "id"
    ],
    "sql": "",
    "sqlType": {
        "id": 4,
        "name": 12,
        "editer_name": 12,
        "user_id": 4,
        "customer_type": 4,
        "brand_id": 4,
        "series_id": 4,
        "spu_id": 4,
        "guide_product_id": 4,
        "provider": 4,
        "cooperate_type": 4,
        "dispatch_type": 4,
        "category_id": 4,
        "photo_url": 12,
        "photo_urls": 12,
    },
    "table": "t_product",
    "ts": 1708399134233,
    "type": "UPDATE"
}

MySQL 存储

MySQL主从复制

一、半同步复制介绍

MySQL基础复制有三种模式：异步复制/同步复制/半同步复制，3种模式各有利弊，下面对各种复制模式的优缺点做个简要的介绍：

异步复制（Asynchronous replicaton）

这是MySQL默认的复制模式

• 异步复制指主库写binlog
• 从库I/O线程读binlog并写入relaylog
• 从库SQL线程重放事务

这三步之间是异步的。

异步复制的优点是主库不需要关心备库的状态，主库不保证事务被传输到从库，如果主库崩溃，某些事务可能还未发送到从库，切换后可能导致事务的丢失。其优点是可以有更高的吞吐量，缺点是不能保持数据实时一致，不适合要求主从数据一致性要求较高的应用场景。

同步复制（Synchronous replicaton）

同步复制的模式下，主库在提交事务前，必须确认事务在所有的备库上都已经完成提交。即主库是最后一个提交的，在提交前需要将事务传递给从库并完成重放、提交等一系列动作。

其优点是任何时候主备库都是一致的，主库的崩溃不会丢失事务，缺点是由于主库需要等待备库先提交事务，吞吐量很低。

半同步复制（Semisynchronous replicaiton）

SHOW GLOBAL VARIABLES LIKE 'rpl_semi_sync_master_enabled';
SHOW GLOBAL VARIABLES LIKE 'rpl_semi_sync_slave_enabled';

MySQL从5.5开始引入了半同步复制，半同步复制介于异步复制和同步复制之间。主库在提交事务时先等待，必须确认至少一个从库收到了事件（从库将事件写入relaylog，不需要重放和提交，并向主库发送一个确认信息ACK），主库收到确认信息后才会正式commit。

与同步复制相比，半同步复制速度快很多，因为他只需要至少1个从库确认写入relaylog，并不需要完成在从库上的事务提交，同时又比异步复制更安全，因为主库在提交时，事务至少已经存在2个地方（主库的binlog和从库的relaylog）。由于半同步复制在提交事务前，需要从库返还确认信息，所以这里涉及到网络的往返通信开销，因此半同步复制只适合在网络条件较好的且地理上距离不远的环境部署，否则可能会因为网络延迟大幅降低主库性能。