MySQL：更新过程（buffer pool与redo、bin、undo log）

参考资料：

《Buffer Pool》

《MySQL更新过程》

《Buffer Pool如何淘汰缓存》

《Buffer Pool中的free链表》

《mysql底层解析》

相关文章：

《mysql中的Innodb_buffer_pool》

《mysql内存相关知识介绍》

        写在开头：本文为学习后的总结，可能有不到位的地方，错误的地方，欢迎各位指正。

目录

一、更新过程

        1、Buffer Pool

        2、undo log

        3、redo log

        4、bin log

        5、两阶段提交

        6、数据写回

二、Buffer Pool内数据存储

         1、Buffer Pool初始化

        2、缓存页管理

        3、数据加载

         4、缓存数据查询

三、Buffer Pool缓存淘汰

        1、缓存淘汰策略

        2、使用LRU会遇到的问题

        2.1、预读

        2.2、大量数据涌入

        3、冷热分离LRU

        4、补充

---

一、更新过程

        1、Buffer Pool

        MySQL 的是存储在磁盘中的，每次查询数据都要进行磁盘的随机读取肯定是无法接受的，于是MySQL想了一些办法进行优化，包括索引、预读以及本文介绍的buffer pool，都是重要的优化手段。

        buffer pool简单来说就是InnoDB 存储引擎索提供的缓存，我们第一次在查询的时候会将查询的结果存到 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中，这样就避免了我们每次查询都要操作磁盘的消耗。

                        

        注意：内存中的数据交换都是以页为单位的，一个数据页面默认大小16K，存储多条记录。因此即使我们只需要查询例如id=1的一条记录，也会将整个数据页的记录全部加载如内存。

        2、undo log

        undo log是基于InnoDB 所提供的MVVC机制（《mysql之事务、锁、隔离级别与MVCC》）而实现的，用来记录数据被修改前的样子。在准备更新一条记录的时候，该记录已经被加载到 Buffer pool 中了，实际上这里还会往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来。

        这样做的原因在于，Innodb 存储引擎支持事务，如果本次更新失败，也就是事务提交失败，那么该事务中的所有的操作都必须回滚到执行前的样子，也就是说当事务失败的时候，也不会对原始数据有影响，而undo log就是为了回滚时能恢复成最初的样子而设计的。

        

        到这一步，这条记录的更新前操作已经全部完成，然后开始更新这条语句，接着buffer pool中的数据就会被修改。此时，内存中的数据与磁盘中的数据便不一致了，接下来要讨论的就是如何将数据写回磁盘。

        可能有的朋友会想，既然内存中的数据已经修改，立即写回磁盘不就行了。这里就涉及到了MySQL对于磁盘操作的进一步优化思路，由于每次修改的数据都是随机的，在索引的介绍文章（《MySQL：索引（1）原理与底层结构》）中，我们解释了由于随机IO会导致磁盘寻道，这就必然导致性能的下降，因此MySQL并不会立即将数据写回，而是等待一个特定的时机，将buffer pool中整个数据页的数据一起写回。

        那么新的问题又产生了，怎么保证内存中的数据不丢失呢？这就要涉及redo log与bin log了。

        3、redo log

        除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中，其他的操作是在内存中完成的，内存中的数据的特点就是：断电丢失。如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的，于是便有了redo log来记录数据被修改后的状态。（redo 日志文件同样是 InnoDB 特有的）

        redo 记录的是数据修改之后的值，不管事务是否提交都会记录下来，例如，此时将要做的是update students set stuName='小强' where id=1; 那么这条操作就会被记录到内存中的 redo log buffer （注意，redo log buffer是一块独立的内存，不在buffer pool中）中，然后会在事务提交时将其持久化到磁盘中。

         在提交事务前会将  redo log buffer中的数据持久化到磁盘中，就是将 redo log buffer 中的数据写入到 redo log 磁盘文件中，一般情况下，redo log Buffer 数据写入磁盘的策略是立即刷入磁盘，刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置。

        innodb_flush_log_at_trx_commit 可选值0、1、2。

        0：表示先刷入os cache，间隔1秒后调用“flush”操作将缓存刷新到磁盘。

        1：表示在每次事务提交的时候，都把log buffer刷到os cache中去。

        2：表示在每次事务提交的时候会把log buffer刷到os cache中去，但并不会立即刷写到磁盘，具体落地时间由os cache决定。

        如果 redo log Buffer 刷入磁盘后，即使数据库服务器宕机了，数据也不会丢失了，因为 redo log buffer 中的数据已经被写入到磁盘了，已经被持久化了，就算数据库宕机了，在下次重启的时候 MySQL 也会将 redo 日志文件内容恢复到 Buffer Pool 中（这边我的理解是和  Redis  的持久化机制是差不多的，在  Redis  启动的时候会检查 rdb 或者是 aof 或者是两者都检查，根据持久化的文件来将数据恢复到内存中）。

        注意：redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。我们知道一个文件内循环写必然造成旧的内容被覆盖，至于如何判断是否可以覆盖旧的内容，下文将会介绍。

        4、bin log

        上面介绍到的redo log是  InnoDB  存储引擎特有的日志文件，而bin log属于是  MySQL  级别的日志。redo log记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。bin log是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录做了更新操作” 两者的主要特点总结如下:

        bin log 有以下几种模式：

• STATMENT

        基于 SQL 语句的复制(statement-based replication, SBR)，每一条会修改数据的 SQL 语句会记录到 bin log 中。

        【优点】：不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能

        【缺点】：在某些情况下会导致主从数据不一致，比如执行sysdate()、sleep()等

• ROW

        基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了

        【优点】：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题

        【缺点】：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨

• MIXED

        基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log

        和redo log一样，bin log刷入磁盘的方式也有三种，通过sync_bin log不同配置来实现，0、1分别表示先写入os cache与立即写入磁盘，默认为0，其中先写入os cache可能存在服务器宕机而导致内存中数据丢失的风险，因此建议改为1。

        到这里为止，redo log与bin log分别进行了介绍，下面我们再介绍下这两个日志文件写入的关联，即两阶段提交。（ 更多bin log的相关内容可以查看这篇文章《这老哥的删库水平，和我有的一拼》）

        5、两阶段提交

        执行commit操作后（mysql默认开启自动提交，如果手动开始事务begin，则需要显示提交commit），由于要保证redolog与binlog的一致性，redolog采用2阶段提交方式。

• redo log buffer刷盘（innodb_flush_log_at_trx_commit=1），并将该事务的redolog标记为prepare状态。将binlog_cache数据刷盘（sync_binlog=1）。
• 待bin log落盘完成，再将redo log中该事务信息标记为commit，释放相关锁资源，此时一个更新事务的操作全部完成。

        至此，redo log才算彻底完成（当然还有后续，标记undol og中该事务修改页的原始快照信息为delete，当无其他事务引用该原始数据时(MVCC)，再将其删除，以及内存中数据刷回磁盘）。

        redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。为什么日志需要“两阶段提交”？

        由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

• 先写 redo log 后写 bin log。假设在 redo log 写完，bin log 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。 但是由于 binlog 没写完就 crash 了，这时候 bin log 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。 然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
• 先写 bin log 后写 redo log。如果在 bin log 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 bin log 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 bin log 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

        可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

        6、数据写回

        经过上文中的过程，整个事务的操作已经完成，数据也已修改，但是修改的只是内存中的数据，并未真的落地回磁盘。那么buffer pool中的数据何时写回呢？主要有以下几种情况：

• 数据库关闭时，将所有脏页刷新到磁盘，这是默认的方式。
• Master Thread操作，这个主线程会每秒、每10秒从脏页列表刷新一定比例的页到磁盘，这是个异步的操作，不会阻塞查询。
• buffer pool中的free列表空闲页不足时，需要刷新一部分来自LRU列表的脏页（下文介绍）。
• redo log文件不可用时，需要强制刷新一部分，为了保证redo log的循环利用。

二、Buffer Pool内数据存储

        关于Buffer Pool中的内容结构我在之前的文章（《mysql中的Innodb_buffer_pool》）中已经介绍过了，这里不做赘述，下文主要针对数据页（data page）进行介绍。

        下文参考自《Buffer Pool中的free链表》 

         1、Buffer Pool初始化

        Buffer Pool中有N多缓存页，每个缓存页都有一个描述信息。数据库启动后，按BP大小向os申请一块内存区域，作为BP的内存区域。当内存区域申请完后，DB按默认缓存页及对应描述信息快，在BP中划出一块块内存，当DB把BP划分完后：

         这时，BP中的一个个缓存页还都是空的，要等数据库运行起来后，当我们要对数据执行CRUD操作时，才会把数据对应的页从磁盘文件里读取出来，放入BP中的缓存页。

        注意：buffer pool并非只有一个，通过Innodb_buffer_pool_instances参事，可以将pool分成N个实例。如果设置的pool的size超过了1G的话，建议使用多个pool实例，来优化多线程情况下，并发读取同一个pool造成的锁的竞争。

        2、缓存页管理

        数据库启动后进行CRUD操作，会不停的从磁盘上读取一个个数据页放入BP中的对应的缓存页里去，把数据缓存起来，后续就能对该数据在内存里执行CRUD。但是此时在从磁盘上读取数据页放入Buffer Pool中的缓存页的时候，必然涉及到一个问题，那就是哪些缓存页是空闲的？

        因为默认情况下磁盘上的数据页和缓存页一一对应，都是16K，一个数据页对应一个缓存页。所以必须要知道Buffer Pool中哪些缓存页是空闲状态。

        所以数据库会为BP设计个free链表，双向链表，每个节点就是个空闲缓存页的描述数据块的地址，即只要一个缓存页空闲，那他的描述数据块就会被放入free链表。刚开始DB启动时，可能所有缓存页都空闲，因为此时可能是个空DB，所以此时所有缓存页的描述数据块，都放入free链表。

         上图中，这free链表里就是各个缓存页的描述信息块，只要缓存页空闲，对应的描述信息块就会加入free链表，每个节点都会双向链接自己的前后节点，组成一个双向链表。free链表有个基础节点引用链表的头节点和尾节点，存储了链表中有多少个描述数据块的节点，即有多少个空闲缓存页。

        3、数据加载

        先从free链表获取一个描述信息块，就能获取到对应空闲缓存页。

         就能将磁盘上的数据页读到对应缓存页，同时将相关的描述信息写入缓存页的描述信息块，比如该数据页所属的表空间之类的信息，最后把那描述信息块从free链表中移除：

         4、缓存数据查询

        我们知道，Buffer Pool的作用就是缓存数据，因此当产生CURD操作时会首先在buffer pool中查找数据是否存在。buffer pool保存一个数据页缓存哈希表：

• key：表空间号+数据页号
• value：缓存页的地址

        当要使用一个数据页时，通过“表空间号+数据页号”作为K查这个哈希表，若有，则直接读取缓存中的数据也，若无，则读取磁盘。

        每次读取一个数据页到缓存后，都会在这哈希表写入一个数据，下次若再使用这数据页，就能从哈希表直接读出来，毕竟他经被放入一个缓存页了：

三、Buffer Pool缓存淘汰

        1、缓存淘汰策略

        随着不断将磁盘数据页加载到空闲缓存页，free中的空闲缓存页会越来越少。最终耗尽free中的空闲缓存页。这时，还要加载数据页到一个空闲缓存页时，MySQL 该何去何从？

        若所有缓存页都有数据了，那就无法再从磁盘加载新数据页到缓存页了，则只能淘汰一些缓存页：把一个缓存页里被修改过的数据，刷到磁盘的数据页，然后该缓存页就能被清空， 变回空闲页。

        经验丰富的朋友肯定想到了LRU算法，不熟悉的朋友可以看我之前在介绍Redis缓存淘汰时的介绍（《Redis：内存淘汰机制》），简单来说就是将不常用的缓存页淘汰出去。

        不过，和Redis中一样，标准的LRU并不完全使用与Buffe Pool中，下面我们来详细解释下。

        2、使用LRU会遇到的问题

        2.1、预读

        MySQL为了减少随机读取，使用了预读的机制，即读取一个数据页时，其后续的数据页也有可能被一起读入。预读的触发条件如下：

• 参数innodb_read_ahead_threshold默认56，即若顺序访问了一个区里的多个数据页，访问的数据页数量超过阈值，就会触发预读，将下个相邻区中的所有数据页都加载到缓 存
• 若BP里缓存了一个区里（数据块）的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时直接触发预读，把这个区里的其他的数据页都加载到缓存里去。该机制通过参数innodb_random_read_ahead控制，默认OFF关闭。

        预读机制从一定程度上将随机读取优化为了顺序读取，但是也为缓存淘汰带来了问题，因为我们其实并不确定被预读进来的后续页面是否会被用到。

        假设现有两个空闲缓存页，加载一个数据页时，连带着把他的一个相邻数据页也加载到缓存，正好每个数据页放入一个空闲缓存页！但实际上只有一个缓存页被访问，另外一个通过预读机制加载的缓存页，其实无人问津，此时这俩缓存页可都在LRU链表前边：

         这时，若无空闲页了，要加载新数据页，就得从LRU链表的尾部将“最近最少使用的缓存页”取出，刷入磁盘。我们会发现这其实并不合理，因为被预读进来的缓存页并不能保证被访问，把这个页给剔除才是合理的。

        2.2、大量数据涌入

        例如全表扫描，短时间内大量的数据涌入buffer pool中，此时可能LRU链表中排在前面的一大串缓存页，都是全表扫描加载进来的。若此次全表扫描后，后续几乎没用到这个表里的数据呢？此时LRU链尾可能全都是之前一直被频繁访问的那些缓存页。

        然后当需要淘汰缓存页时，就会将LRU链表尾部一直被频繁访问的缓存页给淘汰掉了，而留下之前全表扫描加载进来的大量的不经常访问的缓存页。

        为了解决以上2个问题，MySQL对LRU进行了一定调整，即冷热分离的LRU。

        3、冷热分离LRU

        之前问题都是因为所有缓存页都混在一个LRU链表才导致的，改良版LRU链表拆为热数据、冷数据两部分，冷热数据比例由innodb_old_blocks_pct参数控制，默认37，即冷数据占37%。这时的LRU链表：

         数据页第一次被加载到缓存时，缓存页会被放在冷区的链表头部，如果在一定时间内（innodb_old_blocks_time参数控制，默认1000，即1000ms），又访问了该缓存页，他才会被移到热区的链表头部。

         通过冷热分离的方式，我们避免了热数据由于各种原因被错误淘汰的情况

        4、补充

        之前我们介绍了死锁分析语句show engine innodb status，除了死锁，这个分析语句还会告诉我们LRU的相关信息

        database pages表示LRU列表中页的数量。

        pages made young显示了LRU列表中页移动到前端的次数。

        buffer pool hit rate表示缓冲池的命中率，100%表示良好，该值小于95%时，需要考虑是否因为全表扫描引起了LRU列表被污染。