MySQL执行更新SQL流程

1 redo log

• InnoDB引擎特有日志
• MySQL的WAL（Writing Ahead logging）技术，预写式日志，先写日志再写磁盘
• 当有一条记录需要更新时，InnoDB引擎就会先把记录写在redo log日志中，然后在后面系统比较空闲的时候，将这个操作记录更新到磁盘里面
• 记录的是物理日志，在某个数据页上做了什么修改
• 空间大小固定，循环写入

redo日志相关配置

• innodb_log_file_size：配置单个redo日志文件大小
• innodb_log_files_in_group：配置redo日志文件个数
• innodb_log_buffer_size：redo日志内存缓冲区大小
• innodb_flush_log_at_trx_commit：redo日志缓冲区数据落盘时机
  • 0：每秒将redo日志缓冲区内容写入到文件系统缓存，再同步到磁盘
  • 1：每次提交时立即将redo日志缓冲区内容写入到文件系统缓存，再同步到磁盘
  • 2：每次提交立即将redo日志缓冲区内容写入到文件系统缓存，不立即同步到磁盘，由操作系统决定何时同步到磁盘

• write pos是当前记录的位置，一边写一边后移，写到3号文件结尾时，就回到0号文开头
• checkpoint是当前要同步磁盘的开始位置，边将数据同步到磁盘一边向后移动
• wirte position和checkpoint之间是空闲位置，可以用来记录更新操作
• 当wirte position追上checkpoint时，得先推进下checkpoint位置，才能继续写更新记录
• redo log可以保证在数据库发生异常的情况下重启，之前提交的记录都不会丢失，这个能力称之为crash-safe

2 binlog

• MySQL Server层的日志，也称为归档日志
• 所有的存储引擎都可以使用
• 主要用于归档，是逻辑日志，记录的是SQL语句
• 追加写入，达到设置阈值时，切换新的binlog文件

binlog日志相关配置

• server-id：开启binlog时，必须设置的唯一id
• log-bin：配置binlog文件路径和前缀
• binlog-format：指定binlog日志的格式
  • ROW：记录每一行的变化，占用空间大，但是记录的数据完整准确
  • STATEMENT：记录执行的SQL语句，sql中包含函数执行时会导致主从数据不一致问题
  • MIXED：根据情况自动选择ROW或者STATEMENT

expire_logs_day：设置binlog日志文件的保留天数，默认0，表示不删除旧的日志文件

max_binlog_size：设置单个binlog日志文件的最大大小，当达到该值时，会生成一个新的日志文件

binlog日志的操作

• show master status：查看当前的日志文件及位置
• purge binary logs：手动清理日志文件
  • purge binary logs to 'mysql-bin.0000010'：删除到指定文件之前的日志
  • purge binary logs before '2025-03-01 00:00:00'：删除到指定日期之前的日志
• show binary logs：查看binlog日志文件列表
• show binlog events：查看第一个binlog日志内的事件
• show binlog events in 'mysql-bin.0000010'：查看指定binlog日志文件中的事件

binlog解析的工具

• mysqlbinlog
• binlog2sql

3 Update执行逻辑

执行update语句内部流程

• 执行器向存储引擎获取id=1的这一行，
• 存储引擎使用索引树进行查找，如果id=1这一行所在的数据页本来就在内存中，就直接返回给执行器，否则先从磁盘读入内存，然后在返回
• 执行器拿到存储引擎给的数据，将name设置为lose，得到新的一行数据，再调用存储引擎写入这行数据
• 存储引擎将更新后的数据更新到内存中，同时将更新操作记录到redo log中，此时redo log处于prepare状态，告知执行器执行完了，随时可以提交事务
• 执行器生成这个操作的binlog，并把binlog写入磁盘
• 执行器调用存储引擎提交事务的接口，将刚刚写入的redo log改成提交（commit）状态，此时更新完成

执行更新流程图

MySQL两阶段提交

• prepare阶段
  • 将数据写入redo log
  • 将数据写入binlog
• commit阶段
  • binlog中提交commit请求
  • 调用innodb引擎发送commit请求

那么MySQL如何识别哪些提交属于一个事务呢？

• 在redo log和binlog中，都包含xid信息，xid是事务id，在MySQL实例中是全局唯一的。

崩溃恢复如何做呢？

• redo log中事务是完成的，即已经有了commit标识，则直接提交事务
• redo log中只有完整的prepare，没有commit标识，此时需要在binlog中查找
  • 如果binlog中已经commit，则提交事务
  • 如果binlog没有commit，则回滚事务