MySQL 数据更新过程

Linux点滴运维实践 2022-01-12

894

总结 MySQL 日志篇，但为了更好的理解，先铺垫一下 redo log、undo log 与 binlog 日志是在什么时候写入的，作用是什么。

MySQL 逻辑架构

MySQL 逻辑架构图

MySQL的逻辑架构大致分为三层

第一层：大多数是指的网络中， MySQL 客户端进行连接，授权认证，安全校验等；

第二层：MySQL Server 层，大多数 MySQL 的核心服务功能都在这一层，包括 SQL 解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数等），并且所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等；MySQL 服务器层是管理事务的，事务是由存储引擎实现的；

第三层：存储引擎层，负责 MySQL 中数据的存储和提取，MySQL Server 层也称为服务器层是通过 API 与存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

MySQL 数据更新过程

（数据更新过程图）

update test4 set name=”k8svip“ where id=1;

大体过程：我们通过客户端发送到MySQL Server 上面，然后从 MySQL Server连接池中分配一个线程给客户端使用，经过 SQL解析、优化、执行器几个环节，解析器主要对SQL语句生成解析树，优化器选择MySQL 认为最优的执行计划，执行器负责这个计划任务的执行，去调用存储引擎的接口去执行；

1. InnoDB 存储引擎会把经常使用和查询的数据放在缓冲池中，便于快速查询，如果不是内存缓冲池中的数据，就会触发上图中的1过程，加载数据到缓冲池中，然后进行更新，并且需要对记录加行锁；

2. 假设原来的name="abc"，现在我们更新为name="k8svip"，那么此时我们得先把更新前的name="abc" 和id为1的记录信息保存下来，如果出错了，或者不想更新了，方便回滚，此时就写到undo log 日志中，方便事务 rollback（如上图中2的过程）；

3. 我们把记录行信息从磁盘加载到buffer pool，并且加了行锁，也写了方便回滚使用的undo日志，此时就正式更新这行记录了，更新的时候，会先更新buffer pool缓冲池中的记录，此时缓冲池中的数据称为脏数据，即id=1，name="k8svip"，为什么称它为脏数据，是因为buffer cache pool缓冲池中的数据还在内存中，并没有刷新到磁盘，磁盘中的数据依然是旧值，如果此时宕机，数据会不会有丢失的风险呢，MySQL 又是如何避免的。

4. 更新完buffer cache后宕机，缓存中数据是否会丢失？MySQL内部还使用了redo log buffer机制，这也是内存缓冲区，是用来存放redo log的，就是记录你对数据做了什么修改，就像上面更新缓冲后，他会形成一条 redo log日志，从上图中4过程，日志仍然在缓存中，此时宕机buffer pool和redo log buffer有可能一起丢失。由于在InnoDB 数据库中，执行一条 SQL 语句，就是一个独立的事务，只有当你提交事务后，SQL 语句才算执行结束。此时宕机，我们还没有提交事务，内存中buffer pool 和redo log pool也会丢失，其实影响不大，磁盘中仍然是原先的值，重启后，照常使用；

5. 提交事务的时候，redo log 日志是否写入磁盘，它根据一定的策略把redo log日志从redo log buffer里刷新到磁盘文件中去，这里的策略是这个参数 innodb_flush_log_at_trx_commit，建议使用1。

innodb_flush_log_at_trx_commit 参数
0：提交事务时，不会把redo log buffer里的数据刷新到磁盘文件中，有可能出现，你提交事务了，mysql 宕机了，此时内存里的数据全部丢失；
1：提交事务时，必须把redo log buffer刷入磁盘文件中，只要事务提交成功，redo log就必然存在磁盘里了；
2：提交事务时，把redo log日志缓存写入到磁盘文件系统对应的os cache中去，而不是直接进行磁盘文件，可能1秒后会把os cache里的数据写入到磁盘文件中去，此时mysql服务挂了，数据不会丢失，但是如果服务器挂掉，数据丢失。

使用1的话，事务已经提交了，redo log进行了磁盘，但数据仍然在 buffer pool 中，还未刷入磁盘，此时机器宕机，数据会丢失吗？

答案是不会，因为虽然buffer cache未进行刷盘，但redo log日志已经落盘，此时buffer cache pool丢失后，MySQL 服务重启时，会进行检查恢复，会根据redo log日志去恢复之前做过的修改。

6. 如果MySQL开启了binlog，提交事务的时候，同时会写binlog，对于binlog日志，其实也有一个参数控制刷盘策略；

sync_binlog参数
0：不是直接把binlog写入磁盘文件中，而是先进入os cache内存缓存，此时MySQL服务挂掉不会影响日志刷盘，机器宕机，此日志会丢失；
1：会强制在提交事务的时候，把binlog直接写到磁盘文件中，这样提交事务之后，哪怕宕机，磁盘上的binlog也不丢失。

7. 当我们把binlog写入磁盘文件之后，接着就会完成最终事务的提交，此时会把本次更新对应的binlog文件名称和这次更新的binlog日志在文件里位置信息，都写入到redo log日志里去，同时在redo log日志文件里写入一个commit标记，在完成这个事情后，才算完成事务提交，上图中1-7步骤。

8. 上图中7向redo log日志中写入commit做什么？目的是保持redo log和binlog日志一致。如果在第6步向binlog写日志时，mysql宕机了，因为redo log没有commit标记，此时认为事务提交失败，只要redo log中写入commit标记，事务才算最终成功，redo log 里有本次更新对应的日志，binlog 里也有本次更新对应的日志，redo log和binlog最终完全是一致的。

9. 此时buffer pool中的数据，还没有刷新到磁盘中，还处于脏数据状态，那么数据什么时候，如何写到磁盘文件中呢？

MySQL 有一个后台的IO线程，会在之后的某个时间，随机把内存buffer pool中的修改后的脏数据写到磁盘上的数据文件里去。

在你 IO 线程把脏数据刷回磁盘之前的瞬间，mysql 宕机崩溃了，也没关系，因为重启之后，会根据 redo 日志恢复之前提交事务做过的修改到内存里去，就是id=1的数据的name修改为了k8svip，然后等适当时机，IO线程自然还是会把这个修改后的数据刷到磁盘上的数据文件中。

redo log日志

redo log 日志文件及参数

innodb_log_buffer_size：redo log 缓存大小，不同版本默认值不同，一般需要设置成8到16M足够了；
innodb_log_file_size：redo log文件大小，它是一个固定值；
innodb_log_files_in_group：为提高性能 MySQL使用的WAL技术会以循环方式将redo日志文件写到多个文件，这里的值指的是redo log日志文件的数量，默认为2；
innodb_log_group_home_dir：redo log 日志文件目录；

redo log 原理

MySQL Innodb 存储引擎的数据库的最小执行单元是事务，它为了保证数据的持久性，在每提交一个事务时就将日志刷新到磁盘上，这样效率就太低了，严重影响性能，因此 MySQL 中 redo log采用了WAL技术（Write-Ahead Logging），预写式日志，是一种数据安全写入机制，它就是先写日志，然后再写入磁盘，这样就保证了数据的安全性。

先在内存中提交事务，然后写 Redo log，最后有后台线程再把buferr pool中的脏数据写到磁盘中，redo log 日志是为了防止宕机导致内存数据丢失，最后才是后台任务线程把内存中的数据异步刷新到磁盘，事务才算提交完成。

redo log采用固定大小，循环写入的格式，当redo log写满之后，重新从头开始如何循环写，形成一个环状，为什么这样设计呢？

redo log记录的是数据页的修改，如果buffer pool中的数据页已经刷盘，那这些记录就失效了，新日志会将这些失效的记录进行覆盖擦除；

（图来源于网络）

上图中的write pos表示redo log当前记录的日志序列号LSN(log sequence number)，写入还未刷盘，循环往后递增；check point表示redo log中的修改记录已刷入磁盘后的LSN，循环往后递增，这个LSN之前的数据已经全落盘。

write pos到check point之间的部分是redo log空余的部分（绿色），用来记录新的日志；check point到write pos之间是redo log已经记录的数据页修改数据，此时数据页还未刷回磁盘的部分。当write pos追上check point时，会先推动check point向前移动，空出位置（刷盘）再记录新的日志。

redo log日志满了，在擦除之前，需要确保这些要被擦除记录对应在内存中的数据页都已经刷到磁盘中了。擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求的，此刻MySQL的性能会下降。所以在并发量大的情况下，合理调整redo log的文件大小非常重要。

MySQL Innodb 存储引擎记录了redo log，即使 MySQL 宕机重启，系统会检查redo log。MySQL 启动时，不管上次是正常关闭还是异常关闭，总是会进行恢复操作，会先检查数据页中的LSN，如果这个 LSN 小于 redo log 中的LSN，即write pos位置，说明在redo log上记录着数据页上尚未完成的操作，接着就会从最近的一个check point 出发，开始同步数据。

简单理解，比如：redo log的LSN是500，数据页的LSN是300，表明重启前有部分数据未完全刷入到磁盘中，那么系统则将redo log中LSN序号300到500的记录进行重放刷盘。

undo log 日志

undo log 日志文件及参数

Undo logs that reside in the global temporary tablespace are used for transactions that modify data in user-defined temporary tables. These undo logs are not redo-logged, as they are not required for crash recovery. They are used only for rollback while the server is running. This type of undo log benefits performance by avoiding redo logging I/O.

Undo log 记录默认被记录到系统表空间(ibdata)中，如果开启了innodb_file_per_table，将放在每个表的.idb文件中。

innodb_undo_directory：undo log 日志存放目录，默认是存放在表的共享表空间中；
innodb_undo_logs：定义回滚段rollback segment的数量；

undo log 原理

innodb 存储引擎对 undo log 管理采用段的方式，rollback segment 称为回滚段，每个回滚段又有1024个undo log segement，undo log 是用于回滚事务时使用，这里涉及一个版本链的概念，在一个事务中，我们可以对一行记录进行多次修改操作，这样就会形成一个版本链，并且在 InnoDB 中，如果在一个事务中时，每行记录中实际上都包含了两个隐藏字段事务id和回滚指针，如下图：

这里面有涉及一个快照的概念，这个是由什么来实现的呢，是通过 ReadView来实现的，但不同的隔离级别生成ReadView又不一样。

如果数据库隔离级别是读未提交，那么读取版本链中的最新版本记录即可，如果是串行化，事务之间是加锁执行的，不存在读不一致的问题，如果是读提交或可重复读，就需要遍历版本链中的每一条记录，判断该条记录是否对当前事务可见，直到找到为止，遍历完还没有找到，就说明记录不存在，InnoDB 通过 ReadView 实现了这个功能，ReadView 中主要包含以下4个内容。

m_ids：表示在生成 ReadView时当前系统中活跃的读写事务的事务 ID 列表；

min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中，最小的事务id，即mix(m_ids)；

max_trx_id：表示生成 ReadView时，系统中应该分配给下一个事务的ID，即max(m_ids)+1；

creator_trx_id：表示生成该ReadView事务的事务id；

如何判断某个快照版本对当前事务的可见性？

1. 当前事务去访问的事务ID trx_id属性值与当前 ReadView中的creator_trx_id 值相同，表示当前事务访问自己修改过的记录，可以被访问；

2. 当前事务去访问的ReadView版本中的trx_id属性值小于 ReadView 中的 min_trx_id，说明你在访问生成该ReadView 前已经提交过的事务，所以该版本可以被访问；

3. 当前事务去访问的trx_id属性值大于或等于max_trx_id，表明在生成该版本的ReadView后才开启的新事务，所以该版本不可以被当前事务访问；

4. 当前事务去访问 min_trx_id<=trx_id<=max_trx_id分两种情况，需要判断被访问的Row的trx_id，是否在m_ids数组列表中，如果在就表示这个版本是还没有提交的事务生成的，不可见，只有被访问的事务trx_id可以；如果被访问的trx_id不在m_ids数组中，就表示这个版本是已经提交了的事务，可见。