Sql执行流程与Redo log、 Undo log、 Bin log日志文件

Sql执行流程与日志文件

Sql的执行流程

mysql的内部组件结构如下图所示

• 连接器

  与客户端建立连接，检验登录密码，分配相应权限

• 查询缓存

  执行sql语句时会先从这里找一下，这里数据的存储格式是key-value，key是sql语句，value是查询结果。只有当sql语句和查询缓存中的sql语句完全一致才会匹配成功，并且只要对表进行了更新操作就会清除查询缓存中的内容。mysql8.0已经移除了查询缓存

• 词法分析器

  对sql语句进行解析，进行相应语法校验，一条select查询语句经过分析后的结果如下图所示

  

• 优化器

  Mysql自动对sql语句进行相应的优化，存在多个索引时决定使用哪一个，多个表连接查询时决定谁驱动谁

• 执行器

  开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

当使用InnoDB存储引擎执行一条update语句的大致流程如下：

经过server层后就到存储引擎层了，

• 首先去Buffer pool中查询当前数据所在的页是否在缓存池中，如果在就直接操作，如果不在就去磁盘中找到并把整页数据赋值到Buffer Pool中
• 在进行更新操作前先生成Undo log文件
• 更新内存中的数据
• 更新完成后会写Redo log到redo log buffer中
• redo log buffer就经过它相应的刷盘策略 持久化到磁盘的Redo log文件中
• 事务提交前，默认情况下会对Redo log进行持久化操作，还会生成bin log文件写入磁盘中
• redo log 与 bin log此时都已经持久化到磁盘中了，此时会写一个commit标记到redo log日志文件中

Redo Log

当进行更新操作时就会生成redo log文件，它是用来保证事务的持久性的，事务提交前就会把内存中的redo log写入到磁盘中

redo log刷盘策略

• mysql线程以秒为单位将内存中的日志刷新到磁盘

• Redo log Buffer缓存池使用了1/2，就触发刷盘机制

• 默认情况下事务提交前，由innodb_flush_log_at_trx_commit这个变量的值来决定提交时是否持久化redo log

• 当磁盘中Redo log文件不可重写部分占了75%容量时，就触发刷新Buffer pool中脏页的机制

关键参数

• innodb_log_buffer_size

  设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。

  show variables like '%innodb_log_buffer_size%';
  

• innodb_log_group_home_dir

  设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。

  show variables like '%innodb_log_group_home_dir%';
  

  
  
  

• innodb_log_files_in_group

  设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1… iblogfileN。默认2个，最大100个。

  show variables like '%innodb_log_files_in_group%';
  

  
  

• innodb_log_file_size

  设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。

  show variables like '%innodb_log_file_size%';
  

• innodb_flush_log_at_trx_commit

  这个参数控制 redo log 的写入策略，它有三种可能取值：

  • 设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，等Mysql线程去进行刷盘操作，数据库宕机可能会丢失数据。
  • 设置为1： 默认值，每次提交事务时都将redo log buffer中同步写到磁盘，当然这里是先写到操作系统的page cache中，在写到磁盘
  • 设置为2：每次提交事务时都将redo log buffer中异步写到磁盘，这里是写到操作系统的page cache中，如果数据的宕机但是操作系统没有挂数据还是存在的

Redo log 写入磁盘文件大致过程

是交替循环写多个文件，写满一个文件后写下一个文件，但最后一个文件写满后又写第一个文件，大致如下图所示：

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

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

write pos 和 checkpoint 之间的部分就是空着的可写部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示redo log写满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

Bin log

当数据库进行更新操作时，就会记录相应操作到Bin log文件中，它的作用是：数据恢复、主从复制

在mysql5.7中Binlog默认是关闭的，在Mysql8.0版本中默认是开启的，

# 查看binlog相关参数
show variables like '%log_bin%';

log_bin的值是OFF就代表binlog是关闭状态，打开binlog功能，需要修改配置文件my.cnf，然后重启数据库。

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 其他配置
binlog_format = row # 日志文件格式，下面会详细解释，推荐使用MIXED混合模式
expire_logs_days = 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

重启mysql服务后再通过上面的命令查看结果如下，此时就表示binlog已经开启了

Binlog的日志格式

mysql支持三种

• STATEMENT：直接报错执行的sql语句，日志量小，节约IO开销，提高性能，如果此时执行UUID()、SYSDATE()等函数就会造成主从数据不一致问题
• ROW：日志会记录每一行具体修改的值，但对于范围型的更新操作则会生成非常多的数据，比如更新id>10，这个时候就会为每一行数据都生成一条binlog
• 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;命令
• mysql服务重启
• 单个文件记满

删除binlog日志

删除当前的binlog文件
reset master;
# 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql-binlog.000006';
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2023-01-21 14:00:00';

查看 binlog 日志文件

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 D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 00:00:00" start-position="5000" stop-position="20000"

在binlog文件中每一个 BEGIN 或者是COMMIT 上下都有一个表示位置的数，比如 \# at 766，可以拿到这个偏移量去进行数据恢复

binlog日志文件恢复数据

思路是首先通过上面的命令查看到binlog文件的内容，找到要恢复的一个起始和结束位置，在执行数据恢复命令，或者是直接执行整个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 数据库名

Undo log

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

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

undo log日志什么时候删除

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

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