22xCode
22xCode 博客

MySQL Binlog/Redolog和CrashSafe机制

2022-12-12 23:00:18 460

redo log

redo log是MySQL InnoDB的日志, 是物理日志, 记录的是"在某个数据页上做了什么修改"

提一下MySQL里经常说到的WAL技术, WAL的全称是Write Ahead Logging, 它的关键点就是先写日志, 再写磁盘. 日志是顺序写的, 磁盘是随机写. 顺序写速度是比随机写快的.

当有一条记录需要更新的时候, InnoDB引擎就会先把记录写到redo log 里面, 并更新内存, 这个时候更新就算完成了. 同时, InnoDB引擎会在适当的时候, 将这个操作记录更新到磁盘里面, 而这个更新往往是在系统比较空闲的时候做

redo log的大小

InnoDB的redo log是固定大小的, 比如可以配置为一组4个文件, 每个文件的大小是1GB, 那么总共就可以记录4GB的操作. 从头开始写, 写到末尾就又回到开头循环写

redo log的两个指针

write pos是当前记录的位置, 一边写一边后移, 写到第3号文件末尾后就回到0号文件开头.checkpoint是当前要擦除的位置, 也是往后推移并且循环的, 擦除记录前要把记录更新到数据文件.write pos和checkpoint之间的是文件上还空着的部分, 可以用来记录新的操作. 如果write pos追上checkpoint, 表示文件满了, 这时候不能再执行新的更新, 需要把redo log对应的所有脏页都flush到磁盘上, 把checkpoint推进一下, 让redo log留出空间继续写

binlog

binlog是MySQL Server的日志, binlog是逻辑日志, 记录的是这个语句的原始逻辑, 比如"给 ID = 2这一行的 c 字段加 1 "

binlog的大小

binlog是追加写入的. “追加写”是指binlog文件写到一定大小后会切换到下一个, 并不会覆盖以前的日志


一个更新操作的流程

  1. 执行器(Server)先找引擎取ID=2这一行. ID是主键, 引擎直接用树搜索找到这一行. 如果ID=2这一行所在的数据页本来就在内存中, 就直接返回给执行器； 否则, 需要先从磁盘读入内存, 然后再返回.
  2. 执行器拿到引擎给的行数据, 把这个值加上1, 比如原来是N, 现在就是N+1, 得到新的一行数据, 再调用引擎接口写入这行新数据
  3. 如果数据页在内存中就直接更新, 而如果这个数据页还没有在内存中的话, 在不影响数据一致性的前提下, InooDB会将这些更新操作缓存在change buffer(用的是buffer pool里的内存)中
  4. 引擎将这个更新操作记录到redo log里面 (如果把innodb_flush_log_at_trx_commit设置成1, 那么redo log在prepare阶段就要刷盘一次), 此时redo log处于prepare状态. 然后告知执行器执行完成了, 随时可以提交事务.
  5. 执行器生成这个操作的binlog, 并把binlog写入磁盘 (sync_binlog设置为1).
  6. 执行器调用引擎的提交事务接口, 引擎把刚刚写入的redo log改成提交（commit）状态(如果innodb_flush_log_at_trx_commit设置成1, 则redo log更改成commit状态时, 无需再次刷盘, 只写到文件系统中, redo log的prepare阶段就已经刷盘了), 更新完成

crash后mysql如何使用redo log和binlog进行校验

如果binlog写入完成, redo log未改成提交状态时, mysql crash了, 则mysql重启后, 会检查redo log为prepare的记录, 通过xid这个共同的字段与binlog的记录进行对比.

  1. 若redo log中存在的记录, 但binlog中没有, 则mysql认为此事务需要回滚
  2. 若redo log中存在的记录, binlog中也存在且是完整的, 则认为此次事务已完成, 将redo log改成提交状态

为什么日志要用二阶段提交

假设当前ID=2的行, 字段c的值是0, 再假设执行update语句 + 1 过程中在写完第一个日志后, 第二个日志还没有写完期间发生了crash

  1. 如果先写redo log再写binlog: 假如redo log写完后mysql崩溃, 由于redo log写完后, mysql即使崩溃, 仍然可以把数据恢复回来. 通过redo log恢复后, 数据库值为1, 如果后续通过binlog恢复备库数据时, 就会少了这一次更新, 备库数据为0, 数据不一致
  2. 如果先写binlog再写redo log: 假如binlog写完后mysql崩溃, 数据库就无法把数据恢复回来, 数据仍然为0, 但使用binlog恢复备库数据时会多了这一次更新, 备库数据为1, 数据不一致

只使用redolog或binlog中的其一能否实现crash-safe

  1. 只用redolog: 仍然可以实现crash-safe, 但是binlog在mysql中可以用来归档和主从复制, 有非常多基于binlog的中间件
  2. 只用binlog: 不能实现crash-safe, 因为binlog是逻辑日志, 不能实现数据页级别的恢复, 数据落盘是以页为单位，而一个sql可能涉及多个页（如abc三页），一旦crash的时候，只有a没有落盘，bc落盘了，那么根据sql进行重放就会出错了

相关参数的设置

  1. redo log用于保证crash-safe能力. innodb_flush_log_at_trx_commit这个参数设置成1的时候,
    表示每次事务的redo log都直接持久化(fsync)到磁盘. 设置成1, 可以保证MySQL异常重启之后数据不丢失. 如果设置0, 每隔1s将数据持久化到磁盘. 设置为2, 事务提交后, 将数据提交到文件系统缓存内, 由文件系统控制何时持久化到磁盘
  2. sync_binlog这个参数设置成1的时候, 表示每提交1次事务, binlog都持久化到磁盘. 这个参数建议设置成1, 这样可以保证MySQL异常重启之后binlog不丢失. 若设置成0, 表示事务提交之后, 将数据提交到文件系统, 不立马持久化到磁盘, 而让文件系统自行决定什么时候来做同步, 或者cache满了之后才同步到磁盘

change buffer

当需要更新一个数据页时, 如果数据页在内存中就直接更新, 而如果这个数据页还没有在内存中的话, 在不影响数据一致性的前提下, InooDB会将这些更新操作缓存在change buffer中, 这样就不需要从磁盘中读入这个数据页了. 在下次查询需要访问这个数据页的时候, 将数据页读入内存, 然后执行change buffer中与这个页有关的操作. 通过这种方式就能保证这个数据逻辑的正确性.

需要说明的是, 虽然名字叫作change buffer, 实际上它是可以持久化的数据. 也就是说, change buffer在内存中有拷贝, 也会被写入到磁盘上. 将change buffer中的操作应用到原数据页, 得到最新结果的过程称为merge. 除了访问这个数据页会触发merge外, 系统有后台线程会定期merge. 在数据库正常关闭（shutdown） 的过程中, 也会执行merge操作.

显然, 如果能够将更新操作先记录在change buffer, 减少读磁盘, 语句的执行速度会得到明显的提升. 而且, 数据读入内存是需要占用buffer pool的, 所以这种方式还能够避免占用内存, 提高内存利用率

但是假设一个业务的更新模式是写入之后马上会做查询, mysql将更新先记录在change buffer, 但之后由于马上要访问这个数据页, 会立即触发merge过程. 这样随机访问IO的次数不会减少, 反而增加了change buffer的维护代价. 所以, 对于这种业务模式来说, change buffer反而起到了副作用

大表的另一种优化思路

最近在跟进服务迁移到华为云的工作, 发现mysql性能下降非常明显例:某大表SELECT COUNT(1)原先只要60s, 而华为云需要112s之多, 从纸面配置看, 两者没有什么明显区别.但后续在跟华为云技术多次沟通, 多次调整后, 确定了性能下降的原因有三个方面物理配置参数设置mysql版本相关
2024-01-02

OLAP / OLTP

数据库系统可以在广义上分为联机事务处理（Online Transaction Process，OLTP）联机分析处理（Online Analyze Process，OLAP）两种面向不同领域的数据库，OLAP数据库也被称为数据仓库。从产品上看，有专门面向OLTP的数据库，例如MySQL、Postgr
2023-10-15

MySQL Binlog/Redolog和CrashSafe机制

redo logredo log是MySQL InnoDB的日志, 是物理日志, 记录的是"在某个数据页上做了什么修改"提一下MySQL里经常说到的WAL技术, WAL的全称是Write Ahead Logging, 它的关键点就是先写日志, 再写磁盘. 日志是顺序写的, 磁盘是随机写. 顺序写速度
2022-12-12

使用 SOAR 优化 SQL

介绍soar是由小米开源的SQL优化器和重写器项目地址 https://github.com/XiaoMi/soar使用基于 ubuntu20.04 lts按文档安装后https://github.com/XiaoMi/soar/blob/master/doc/install.md现有 catego
2022-08-12

MySQL主从复制搭建

masterdocker run -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --network rootnet --ip 172.18.0.10 -v /usr/soft/mysql-master/data:/var/lib/mysql:rw -v /u
2022-05-19

MySQL InnoDB 加锁机制

MySQL 版本: 8.0.25隔离级别: 可重复读InnoDB有两种不同的SELECT，即普通SELECT 和 锁定读SELECT. 锁定读SELECT 又有两种，即SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE; 锁定读SELECT 之外的则是 普通SE
2022-03-26

MySQL InnoDB MVCC机制

对于普通select来说, InnoDB使用MVCC保证了事务隔离. 同一事务的两次相同查询语句都是同样结果, 其他事务修改记录不影响当前事务, 特殊情况是会看到同一事务中先前语句所做的更新, 所以对于普通select(快照读)来说, MVCC是解决了脏读/不可重复读/幻行的; 而对于当前读(锁定读
2022-02-23

MySQL WITH AS 语法

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用with as，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用特别对于UNION ALL比较有用. 因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，
2022-02-20

MySQL优化-表结构设计

首先明确一个, 减少占用的存储空间, 可以减少操作时占用的内存, 可以提高CPU处理效率字符串的ip地址可以转换为整数类型存储, mysql提供INET_ATON()和INET_NTOA()进行转换尽量避免字段允许为NULL, 字段为NULL会占用额外空间整数类型可以选择置为无符号, 同样的存储空间
2021-11-14
大字段如何对查询产生影响

大字段如何对查询产生影响

一些应用, 在表结构的设计上使用了text或者blob的字段；其中一个应用，对blob/text字段的依赖非常的严重，查询和更新的频率也是非常的高，单表的存储空间已经达到了近100G，这个时候，应用其实已经被数据库绑死了，任何应用或者查询逻辑的变更几乎成为不可能；为了清楚大字段对性能的影响，我们必须
2021-10-10

常见的大表查询优化

测试表user, user_detail各100w数据下面是一个常见的连表查询分页sqlSELECT * FROM user u LEFT JOIN user_detail ud ON u.id = ud.user_id LIMIT 800000, 10 执行时间3.323s优化下可以写成这样SEL
2021-05-10

各平台时间格式

javayyyy-MM-dd HH:mm:ss2021-01-18 13:05:25mysqlDATE_FORMAT(time,'%Y-%m-%d %H:%i:%s')2021-01-18 13:05:25sql serverSELECT CONVERT(varchar(100), GETDATE(
2021-01-18

Mysql 通过binlog日志恢复数据

https://www.cnblogs.com/YCcc/p/10825870.html
2021-01-13

freemarker 时间显示不正常 设置时区

项目在本地开发的时候显示正常，部署上服务器就一直差8个小时，最后发现freemarker官方文档有这样的说明time_zone：时区的名称来显示并格式化时间。 默认情况下，使用JVM的时区。 也可以是 Java 时区 API 接受的值，或者 "JVM default" (从 FreeMarker 2
2020-03-28
IDEA 2019.1 xml 不高亮

IDEA 2019.1 xml 不高亮

前几天更新了idea后，发现xml里的代码都没有了高亮，变得跟记事本一个德性了打开setting ，搜索 File Types，找到xml项， 查看下方的匹配格式，果然没有xml，(idea真是厉害)点击右方的+，输入*.xml，点击ok，解决问题
2020-03-28

npm install 淘宝镜像

npm install --registry=https://registry.npm.taobao.org
2020-03-28
Java中方法的参数传递机制

Java中方法的参数传递机制

来看一段代码 public class Man { private String name; private Integer age; public String getName() { return name; } publi
2020-03-28
基于自定义注解手写权限控制

基于自定义注解手写权限控制

方法一: AOP 方法二: 拦截器项目结构项目依赖<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-w
2020-03-28

Docker 部署 详细全过程 附代码

Docker 部署本站 全过程环境：CentOS7.61. 安装Docker其他版本CentOS可以参考这个https://help.aliyun.com/document_detail/187598.html查看本机内核版本，内核版本需高于 3.10uname -r 确保 yum 包最新yum u
2020-03-28

SpringBoot 启动普通java工程

引入依赖<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> <version>2.0.9</version> </dependency>
2020-03-28

Vue.js DOM操作

<template> <input type="button" @click="reply($event)" value="回复"> </template> export default { methods: { replyFun(e) {
2020-03-29
CentOS7编译调试OpenJDK12

CentOS7编译调试OpenJDK12

1. 下载源码https://hg.openjdk.java.net/jdk/jdk12点击左侧的browse，再点击zip，就可以下载zip格式的源码压缩包。unzip xxx.zip 解压文件2. 安装jdkyum install java-11-openjdk-devel -y3. 运行con
2020-04-23
编写自己的Spring Boot Starter

编写自己的Spring Boot Starter

1.新建一个maven项目命名规则统一是xxx-spring-boot-starter完整pom.xml<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0"
2020-06-29