mysql> create table T (id int primary key, c int);
mysql> update T set c=c+1 where id=2;
执行过程
通过连接器,客户端与 MySQL 建立连接
update语句会把 T表上的所有查询缓存清空
分析器会通过词法分析和语法分析识别这是一条更新语句
优化器会决定使用id这个索引(主键索引)
执行器负责具体执行,找到其中一行,然后更新
更新过程中还会涉及 redolog (重做日志)和 binlog (归档日志)
redolog - Store Engine
如果每次更新操作都需要写入磁盘,即在磁盘中 找到并更新 相关的记录,整个过程的 IO成本 和 查找成本 都很高
针对这种情况,MySQL采用的是 WAL 技术( Write-Ahead Logging )
先写日志,再写磁盘
当有一条记录需要更新的时候,InnoDB会先把记录写到 redolog ,并 更新内存 ,这时更新操作已经算完成
InnoDB会在适当(系统空闲)的时候,将这个操作记录到磁盘里面
如果redolog写满,需要先将部分数据写入到磁盘,从而腾出空间
InnoDB的redolog是 固定大小 的,如果每个日志文件大小为1GB,4个日志文件为一组
redolog的总大小为4GB, 循环写
write pos是 当前记录的位置 ,一边写一边后移,写到3号文件末尾后就回到0号文件开头
redolog是 顺序写 ,数据文件是 随机写
checkpoint是 当前要擦除的位置 , 擦除记录前需要先把对应的数据落盘
write pos到checkpoint之间的部分可以用来 记录新的操作
如果write pos赶上了checkpoint,说明redolog已 满 ,不能再执行新的更新操作,需要先推进checkpoint
只要write pos未赶上checkpoint,就可以执行新的更新操作
checkpoint到write pos之间的部分 等待落盘
如果checkpoint赶上了write pos,说明redolog已 空
有了redolog之后,InnoDB能保证 数据库 即使发生 异常重启 , 之前提交的记录都不会丢失 ,到达 crash-safe
# innodb_log_file_size -> 单个redolog文件的大小
# 268435456 Bytes = 256 MB
mysql> SHOW VARIABLES LIKE '%innodb_log_file%';
+---------------------------+-----------+
| Variable_name | Value |
+---------------------------+-----------+
| innodb_log_file_size | 268435456 |
| innodb_log_files_in_group | 3 |
+---------------------------+-----------+
# 这里的written是指写到磁盘缓存
# 0 -> Logs are written and flushed to disk once per second
# 1 -> Logs are written and flushed to disk at each transaction commit
# 2 -> Logs are written after each transaction commit and flushed to disk once per second
mysql> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2 |
+--------------------------------+-------+
binlog - Server
redolog是InnoDB特有的日志,binlog属于Server层日志
有两份日志的原因
一开始并没有InnoDB,采用MyISAM,但 MyISAM没有crash-safe的能力 , binlog日志只能用于归档
InnoDB是以插件的形式引入MySQL的, 为了实现crash-safe ,InnoDB采用了 redolog 的方案
binlog一开始的设计就是 不支持崩溃恢复 (原库)的,如果不考虑搭建从库等操作, binlog是可以关闭的 (sql_log_bin)
redolog vs binlog
redolog是InnoDB特有的,binlog是MySQL的Server层实现的,所有层都可以使用
redolog是 物理日志 ,记录某个 数据页 上做了什么 修改
binlog是 逻辑日志 ,记录某个 语句的原始逻辑
逻辑日志: 提供给别的引擎用 ,大家都能理解的逻辑,例如 搭建从库
物理日志: 只能内部使用 ,其他引擎无法共享内部的物理格式
redolog是 循环写 , 空间固定 , 不能持久保存
binlog是 追加写 , 空间不受限制 ,有 归档 功能
redolog主要用于 crash-safe ,原库恢复
binlog主要用于恢复成 临时库 (从库)
崩溃恢复的过程 不写binlog (需要 读binlog )
用binlog恢复实例(从库),需要 写redolog
redolog支持事务的持久性,undo log支持事务的隔离性
redo log对应用开发来说是 透明 的
binlog有两种模式
statement格式 :SQL语句
row格式 :行内容(记两条,更新前和更新后), 推荐
日志一样的可以用于 重放
mysql> SHOW VARIABLES LIKE '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
update 内部流程
浅色框在 InnoDB内部 执行,深色框在 执行器中 执行
执行器先通过InnoDB获取id=2这一行,id是主键,InnoDB可以通过 主键索引 找到这一行
如果 id=2这一行所在的数据页 本来就在内存( InnoDB管理的内存 )中,直接返回给执行器
否则先从磁盘读入内存,然后再返回
执行器拿到InnoDB返回的行数据,进行 +1 操作,得到新的一行数据,再调用InnoDB的引擎接口写入这行数据
InnoDB首先将这行新数据 更新到内存 中,同时将这个更新操作 记录到redolog (物理记录)
更新到内存中,在 事务提交后,后续的查询就可以直接在内存中读取该数据页 ,但此时的数据可能 还没有真正落盘
但在 事务提交前,其他事务是无法看到这个内存修改的
而在 事务提交后,说明已经成功写入了redolog,可崩溃恢复,不会丢数据,因此可以直接读内存的数据
刚更新的内存是不会删除的,除非内存不够用,在数据从内存删除之前,系统会保证它们已经落盘
此时redolog处于 prepare 状态( prepare标签 ),然后告诉执行器执行完成, 随时可以提交事务
对其他事务来说,刚刚修改的内存是 不可见 的
执行器生成这个操作的binlog(逻辑记录)并写入磁盘
binlog写成功事务就算成功 ,可以提交事务
哪怕崩溃恢复,也会恢复binlog写成功的事务
binlog如果没写成功就回滚 ,回滚会 写redolog ,打上 rollback标签 ,binlog则会直接丢弃
执行器调用InnoDB的 提交事务 接口,InnoDB把刚刚写入的redolog改成 commit 状态,更新完成
redolog打上了 commit标签
commit表示两个日志都生效了
commit完成后才会返回客户端
# 0 -> Disables synchronization of the binary log to disk by the MySQL server
# Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file
# This setting provides the best performance
# 1 -> Enables synchronization of the binary log to disk before transactions are committed
# This is the safest setting but can have a negative impact on performance due to the increased number of disk writes
# N -> The binary log is synchronized to disk after N binary log commit groups have been collected
# A higher value improves performance, but with an increased risk of data loss
mysql> SHOW VARIABLES LIKE '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 |
+---------------+-------+
# 如果系统IO能扛得住,采用双1
For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:
sync_binlog=1.
innodb_flush_log_at_trx_commit=1.
# 实际中的线上环境可能会做一定的权衡,innodb_flush_log_at_trx_commit=2,sync_binlog=0
# 最多丢失1s的redolog,丢失部分binlog
# 归纳
innodb_flush_log_at_trx_commit
0 -> write+flush by second
1 -> write+flush by commit
2 -> write by second, flush by second
sync_binlog
0 -> rely on os
1 -> 1 commit
N -> N commit
redolog的两阶段提交
目的:为了让redolog和binlog的 逻辑一致
脑洞:假设不采用两阶段提交
有两种顺序:redolog->binlog,或者binlog->redolog
此时可以认为redolog和binlog 完全独立
崩溃恢复完全依赖于redolog,恢复临时库完全依赖于binlog
按照上面的两种顺序,都会导致redolog和binlog逻辑上的不一致
假设原库crash后执行 原库恢复 +执行 临时库恢复 ,恢复出来的数据是不一致的
恢复清醒:两阶段提交(假设是 双1 的配置)
redolog prepare + binlog成功, 提交事务 ,崩溃恢复后也会继续提交事务(redolog commit),逻辑一致
redolog prepare + binlog失败, 回滚事务 ,崩溃恢复后也会继续回滚事务(redolog rollback),逻辑一致
一个事务的binlog是有 固定格式 的
redolog与binlog是通过 事务id 进行 关联 的
此时binglog中没有对应的记录,事务记录是不完整的
崩溃恢复后是会从 checkpoint 开始往后主动刷数据
采用非双1的配置,在极端环境下会出现redolog与binlog 不一致 的情况
优先保证redolog ,先原库崩溃恢复,再处理从库(原库可以在崩溃恢复后,重新做一次 全量备份 ,重建从库)