MySQL数据库学习- 1 | 日志系统:一条SQL更新语句是如何执行的?
更新示例
环境: MySQL 5.7.24, for linux-glibc2.12 (x86_64)
示例: 一条简单的 update 更新语句在 MySQL的各个功能模块中的执行过程。
-- CREATE
CREATE TABLE `T` (
`ID` tinyint NOT NULL primary key,
`c` tinyint
);
-- UPDATE
update T set c=c+1 where ID=2;
执行流程
基础架构
第一篇1 关于查询语句执行流程,介绍了MySQL的逻辑架构大体可分为:Server 层 和 存储引擎层 两部分。
更新流程
select 查询语句的执行流程, update 更新语句也是同样要走一遍的。
- 先是连接数据库,连接器开始工作。
- 表上有更新操作,与这个表有关的查询缓存会失效。所以,示例更新语句会把表T上所有缓存结果都清空。这也是不建议使用查询缓存的原因。
- 分析器开始词法分析、语法分析,MySQL获知这是一条更新语句。
- 优化器决定使用 ID这个索引。
- 执行器负责执行,找到ID=2这一行,然后更新。
与查询流程不同之处,更新流程流程会涉及两个重要的日志模块:redo log(重做日志)和binlog(归档日志)
日志模块
重做日志 redo log
- 如果将每一次的更新操作都写进磁盘,磁盘也要找到对应的记录再做更新,这样整个过程的IO成本、查找成本都很高。MySQL利用WAL技术(Write-Ahead Logging),先写日志,再写磁盘。
- redo log是InnoDB存储引擎特有的日志,属于存储引擎层 。
- 具体来说,更新一条记录时,InnoDB引擎会先将记录写到redo log里并更新内存,此时,更新记录就算完成了。同时,InnoDB引擎会在适当的时候(系统比较空闲时),将这个操作记录更新到磁盘里面。
- InnoDB的redo log是固定大小的,比如可配置为一组4个文件,每个文件大小1GB。可以循环写。
write pos 是当前记录的位置,一边写一边往后推移并且循环。
check point 是当前要擦除的位置,往后推移并且循环。擦除记录前需要把记录更新到数据文件。
write pos 与 check point 之间是空闲部分,可以用来记录新的操作。
如果write pos 追上 check point 表示空间满了,不能再执行新的更新操作,需要停下来先擦掉一些记录(将记录更新到数据文件)将 check point 推进一下。
有了redo log,InnoDB可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称之为 crash-safe。
归档日志 binlog
binlog与redo log的区别
区别-1 | 所属功能模块 |
---|---|
binlog | Server层 日志,所有引擎都可以使用 |
redo log | 存储引擎层 日志,InnoDB引擎特有 |
区别-2 | 日志类型 |
---|---|
binlog | 逻辑 日志,记录的是SQL语句的原始逻辑 |
redo log | 物理 日志,记录的是"在某个数据页上做了什么修改" |
区别-3 | 处理方式 |
---|---|
binlog | 追加写,binlog文件写到一定大小会切换到下一个日志文件 |
redo log | 循环写,文件大小固定 空间会用完 |
innodb_flush_log_at_trx_commit
这个参数建议设置为1,表示每次事务的redo log都直接持久化到磁盘,保证MySQL异常重启之后数据不丢失。
sync_binlog
这个参数建议设置为1,表示每次事务的binlog都直接持久化到磁盘,保证MySQL异常重启之后binlog不丢失。
两阶段提交
回到上面的更新语句执行流程图,最后3步将redo log的写入操作拆分成了2个步骤:prepare和commit,这就是两阶段提交(是跨系统维持数据逻辑一致性时常用的一个方案)。
redo log和binlog都可以用于表示事务的提交状态,而两阶段提交是为了让两份日志(redo log和binlog)之间的逻辑保持一致。
- 如果不使用两阶段提交,那么数据库的状态与使用日志恢复出来的库中的状态,有可能会不一致。出现场景:误操作后的数据恢复、扩容搭建备库(全量备份+应用binlog)。
参考资料
《高性能MySQL》
《MySQL实战45讲》 作者:丁奇
写在后面
之前学习了大神丁奇的《MySQL实战45讲》,目前在看《高性能高MySQL》,也想自己整理一下MySQL知识点,发现力不从心,也发现大神之所以是大神,那是因为真的牛。
推荐大家还是去学习丁奇的《MySQL实战45讲》,条理清晰,循序渐进,深入浅出,通俗易懂。而且每一讲后面都有高质量的留言评论, 从中能获益良多。感谢!
- 如有 错误之处 还请多多指正。希望能给您带来帮助。