Mysql多版本并发控制MVCC和日志系统

三流程序员03
79 阅读7分钟

介绍

RR级别下，同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。

Mysql在RC和RR隔离级别下都实现了MVCC机制。读的时候快照读，写的时候是当前读

MVCC

undo日志版本链

undo日志版本链是指一行数据被多个事务依次修改过，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

事务里面，每修改一行数据都会生成一个日志版本链，事务结束后，版本链销毁

trx_id:事务id是在事务里面执行第一个修改操作或排它锁操作时，事务才真正启动；此时，mysql内部按照事务的启动顺序分配事务id

image.png

image.png

read view机制

在RR隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前永远都不会变化

在RC隔离级别下，在每次执行查询sql时都会重新生成read-view

read-view视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

image.png

版本链比对规则：

  1. 如果 row 的 trx_id 落在绿色部分trx_id可见的；

  2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；

  3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况

    a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；

    b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息record header里的deleted_flag标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

readview原理解释

readview记录了sql查询那个时刻数据库里提交和未提交所有事务的状态。

RR隔离级别，事务里每次执行查询操作readview都是使用第一次查询时生成的readview，也就是都是以第一次查询时当时数据库里所有事务提交状态来比对数据是否可见，当然可以实现每次查询的可重复读的效果了。

RC隔离级别，事务里每次执行查询操作readview都会按照数据库当前状态重新生成readview， 也就是每次查询都是跟数据库里当前所有事务提交状态来比对数据是否可见，当然实现的就是每次都能查到已提交的最新数据效果了。

日志系统

redo log

关键参数 image.png

  • innodb_log_buffer_size: 设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。
  • innodb_log_group_home_dir：设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。
  • innodb_log_files_in_group: 设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2个，最大100个。
  • innodb_log_file_size：设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和

redo log写入磁盘分析

redo log写入磁盘时为顺序写，redolog可以理解为一个固定容量的环，写完一个文件后继续写另一个文件，两个文件写满后，覆盖第一个文件，循环写入，如下图所示

image.png

write pos:当前写入数据的位置， checkpoint:是当前要擦除的位置，擦除记录前要把记录更新到数据文件里。 write pos 和 checkpoint 之间的部分就是空着的可写部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示redo log写满了，此时会刷数据到ibd文件，checkpoint向后移，保证能继续写入。

innodb_flush_log_at_trx_commit 控制redo log buffer到redo log的写入策略

  • 0，每次事务提交时写数据到redo log buffer，数据库宕机后根据redolog恢复时可能会丢失数据
  • 1, 表示每次事务提交时都刷盘到redo log，不会丢失数据，相应效率降低，默认配置，适合线上系统
  • 2，每次事务提交时把数据写到操作系统的缓存page cache，数据库宕机不会丢失数据，操作系统宕机可能会丢失数据

image.png

InnoDB 有一个后台线程，每隔 1 秒，就会把redo log buffer中的日志，调用 操作系统函数 write 写到文件系统的page cache，然后调用操作系统函数fsync 持久化到磁盘文件。

如下图所示

image.png

bin log

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。binlog也可以用来实现主从复制功能，具体配置可以参考我的另一篇文章。

启动binlog记录功能，会影响服务器性能，但好处大于坏处，一般都会开启。MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的。

参数配置

[mysqld]
# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径(在data目录下)
log-bin=mysql-binlog

# 日志文件格式
binlog_format = row 

# 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
expire_logs_days = 15 

# 单个binlog日志文件的大小限制，默认为 1GB
max_binlog_size = 200M

image.png

参数解释

log_bin：binlog日志是否打开状态
log_bin_basename：binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。可以用来模拟主从同步复制异常。

日志格式

用参数binlog_format可以设置binlog日志的记录格式，mysql支持三种格式类型：

STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是如果SQL中使用一些系统函数，比如UUID()、SYSDATE()等，如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。

ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。

MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

binlog写入磁盘机制

binlog写入磁盘机制主要通过sync_binlog 参数控制，默认值是0。

  • 0，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行fsync写入磁盘。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
  • 1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
  • N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

发生以下任何事件时, binlog日志文件会重新生成：

  • 服务器启动或重新启动
  • 服务器刷新日志，执行命令flush logs
  • 日志文件大小达到 max_binlog_size 值，默认值为 1GB

删除binlog日志文件

#删除当前的binlog文件
reset master;

# 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql-binlog.000006';

# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2023-01-21 14:00:00';

查看 binlog 日志文件

# 查看bin-log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 

# 查看bin-log二进制文件（带查询条件）
mysqlbinlog --no-defaults -v --base64-output=decode-rows --start-datetime="2023-02-26 00:00:00" --stop-datetime="2023-02-27 00:00:00"  --start-position="15446" --stop-position="16583" ../data/master-bin.000004

image.png

image.png

binlog恢复数据

mysqlbinlog  --no-defaults --start-position=219 --stop-position=701 --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test

# 补充一个根据时间来恢复数据的命令，我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式
mysqlbinlog  --no-defaults --start-datetime="2023-1-27 23:32:24" --stop-datetime="2023-1-27 23:34:23" --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test

undo log

InnoDB对undo log文件的管理采用段的方式，也就是回滚段（rollback segment） 。每个回滚段记录了 1024 个 undo log segment ，每个事务只会使用一个undo log segment。

在MySQL5.5的时候，只有一个回滚段，那么最大同时支持的事务数量为1024个。在MySQL 5.6开始，InnoDB支持最大128个回滚段，故其支持同时在线的事务限制提高到了 128*1024 。

innodb_undo_directory：设置undo log文件所在的路径。该参数的默认值为"./"，即innodb数据文件存储位置，目录下ibdata1文件就是undo log存储的位置。
innodb_undo_logs: 设置undo log文件内部回滚段的个数，默认值为128。
innodb_undo_tablespaces: 设置undo log文件的数量，这样回滚段可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件。

undo log日志什么时候删除?

新增类型的，在事务提交之后就可以清除掉了。

修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。

为什么Mysql不能直接更新磁盘上的数据而设置这么一套复杂的机制来执行SQL？

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。而且磁盘随机读写的性能是非常差的，直接更新磁盘文件是不能让数据库抗住很高并发的。

Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

错误日志

Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

# 查看错误日志存放位置 
show variables like '%log_error%';

通用查询日志

通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。

通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。

general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。

general_log_file：通用查询日志记录的位置参数。

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第6天，点击查看活动详情

avatar
文章
阅读
粉丝