MySQL-日志+事务

原创于 2025-06-23 15:10:54 发布 · 867 阅读

28 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

技术元宇宙

MySQL-日志+事务

4.1从一条sql的流程来讲起Mysql日志

执行一条 update 语句，期间发生了什么？对于三大日志的概览：
在这里插入图片描述

如果Buffer Pool没有数据，加载整页数据
更新前会将老数据记录到undo log
更新脏页 name=‘新小明’
写入Redo Log Buffer（记录物理修改操作如page_no=5, offset=200, value=‘新小明’）
顺序写入磁盘 prepare阶段
BingLog日志写入磁盘
Redo Log中标记为commit

4.2深入了解MySQL日志类别及作用

4.2.1 redo log（重做日志）

redo log（重做日志）是 InnoDB 存储引擎独有的，它让 MySQL 拥有了崩溃恢复能力。比如 MySQL 实例挂了或宕机了，重启时，InnoDB 存储引擎会使用 redo log 恢复数据，保证数据的持久性与完整性。
[图片]

那磁盘内的redo log是怎样生成的呢？

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。
更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。
然后会把“在某个数据页上做了什么修改”记录到 redo log 文件里。

在这里插入图片描述

4.2.1.1 redo log刷盘时机

设置正确的刷盘策略innodb_flush_log_at_trx_commit 。根据 MySQL 配置的刷盘策略的不同，MySQL 宕机之后可能会存在轻微的数据丢失问题。

查看默认刷盘策略：

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

[图片]

innodb_flush_log_at_trx_commit 的值有 3 种，也就是共有 3 种刷盘策略：
在这里插入图片描述

4.2.1.2 日志文件组

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示：
在这里插入图片描述

在这个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

write pos 是当前记录的位置，一边写一边后移
checkpoint 是当前要擦除的位置，也是往后推移，检查点的推进依赖于脏页被成功刷新到磁盘的数据页上

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。
在这里插入图片描述

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，紧急刷脏页，把 checkpoint 推进一下。
在这里插入图片描述

4.2.1.3 两阶段提交

MySQL 为了避免出现redo log和binglog之间的逻辑不一致的问题，使用了「两阶段提交」来解决
两阶段提交把单个事务的提交拆分成了 2 个阶段
分别是「准备阶段」和「提交阶段」，其实也就是把redo log 的写入拆成两步，然后中间穿插写入binlog。
在这里插入图片描述

两阶段提交具体过程

当客户端执行 commit 语句或者在自动提交的情况下，
MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交
在这个内部XA事务中，将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog

prepare 阶段：
- 将 XID（内部 XA 事务的 ID）写入到 redo log，
- 同时将 redo log 对应的事务状态设置为 prepare，
- 然后将 redo log 持久化到磁盘
commit 阶段：
- 把 XID 写入到 binlog，然后将 binlog 持久化到磁盘
- 接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，
- 此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，「pagecache页缓存」
- 因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

我们来看看在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象？下图中有时刻 A 和时刻 B 都有可能发生崩溃：
[图片]

不管是时刻 A（redo log 已经写入磁盘， binlog 还没写入磁盘），还是时刻 B （redo log 和 binlog 都已经写入磁盘，还没写入 commit 标识）崩溃，此时的 redo log 都处于 prepare 状态。

在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：

如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况。
如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况。

可以看到，对于处于 prepare 阶段的 redo log，即可以提交事务，也可以回滚事务，这取决于是否能在 binlog 中查找到与 redo log 相同的 XID，如果有就提交事务，如果没有就回滚事务。这样就可以保证 redo log 和 binlog 这两份日志的一致性了。

所以说，两阶段提交是以 binlog 写成功为事务提交成功的标识，因为 binlog 写成功了，就意味着能在 binlog 中查找到与 redo log 相同的 XID。

4.2.1.4 redo log 小结及思考

相信大家都知道 redo log 的作用和它的刷盘时机、存储形式。

现在我们来思考一个问题：只要每次把修改后的数据页直接刷盘不就好了，还有 redo log 什么事？
它们不都是刷盘么？差别在哪里？

实际上，数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，有必要把完整的数据页刷盘吗？

而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。

如果是写 redo log，一行记录可能就占几十
Byte，只包含表空间号、数据页号、磁盘文件偏移量、更新值，再加上是顺序写，所以刷盘速度很快。

4.2.2 undo log（回滚日志）

undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。
[图片]

4.2.2.1 undo log版本链

我们每条数据其实都有两个隐藏字段，一个是trx_id，一个是roll_pointer:

trx_id当前更新这条数据的事务ID
roll_pointer指向上一个版本的指针

举个例子：

现在假设有一个事务A（id = 50），插入了一条数据，那么此时这条数据的隐藏字段以及指向的undo log如下图所示：
接着假设有一个事务B跑来修改了一下这条数据，把值改成了值B，事务B的id是58，那么此时更新之前会生成一个undo log记录之前的值，然后会让roll_pointer指向这个实际的undo log回滚日志。如下图：
接着假设事务C又来修改了一下这个值为值C，他的事务id是69，此时会把数据行里的txr_id改成69，然后生成一条undo log，记录之前事务B修改的那个值

4.2.2.2 undo log 示例

事务回滚：

START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1; -- 生成 Undo Log
ROLLBACK; -- 利用 Undo Log 恢复 balance 的原始值

MVCC：

-- 事务 A
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1; -- 生成 Undo Log

-- 事务 B
START TRANSACTION;
SELECT balance FROM users WHERE id = 1; -- 读取事务 A 修改前的快照（通过 Undo Log 实现）
COMMIT;

4.2.3 bing log

4.2.3.1 什么是 Binlog 日志？

Binlog（Binary Log）是 MySQL 的二进制日志，用于记录所有修改数据的操作（如 INSERT、UPDATE、DELETE）和数据定义语句（如 CREATE TABLE 等），不记录只读操作（如 SELECT）。
作用：
1. 主从复制：将 Binlog 传递给从节点，用于主从数据同步。
2. 数据恢复：通过重放 Binlog 恢复数据到某个时间点或事务状态。

4.2.3.2 Binlog 的格式

Binlog 有三种格式：

STATEMENT：

记录每一条 SQL 语句。
优点：日志量小。
缺点：对于一些非幂等操作（如 NOW()、UUID()）可能导致数据不一致。

ROW：

记录每一行数据的变化。
优点：更加精确，可避免语句级问题。
缺点：日志量大。

MIXED：

混合模式，MySQL 根据实际操作选择使用 STATEMENT 或 ROW。

4.2.3.3 使用 Binlog 恢复数据的步骤

确认 Binlog 已启用
确保 MySQL 配置中启用了 Binlog 日志：

[mysqld]
log-bin=/var/lib/mysql/mysql-bin
binlog-format=ROW  # 推荐使用 ROW 格式

查看 Binlog 文件
运行以下命令查看所有可用的 Binlog 文件：

SHOW BINARY LOGS;

示例输出：

Log_Name            File_size      Encrypted
DLH-bin.000001        180             No
DLH-bin.000002        136579005       No
DLH-bin.000003        180             No
DLH-bin.000004        6930            No
DLH-bin.000005        95827240        No

查看 Binlog 日志内容
通过 mysqlbinlog 命令可以解析 Binlog 文件：

mysqlbinlog DLH-bin.000002

常用参数：

按时间范围：

mysqlbinlog --start-datetime="2024-11-20 10:00:00" --stop-datetime="2024-11-20 12:00:00" mysql-bin.000001

按位置范围：

mysqlbinlog --start-position=120 --stop-position=456 mysql-bin.000001

4.执行 Binlog 以恢复数据
将 Binlog 文件中的操作重放到数据库中：

mysqlbinlog DLH-bin.000002 | mysql -u root -p

如果需要恢复到特定时间点：

mysqlbinlog --start-datetime="2024-11-20 10:00:00" --stop-datetime="2024-11-20 11:00:00" DLH-bin.000002 | mysql -u root -p

4.2.3.4 binlog 刷盘时机

对于 InnoDB 存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？
mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

0：不去强制要求，由系统自行判断何时写入磁盘；
1（默认）：每次 commit 的时候都要将 binlog 写入磁盘；
N：每N个事务，才会将 binlog 写入磁盘。

4.2.4 中继日志

中继日志是 MySQL 主从复制架构中，从库（Slave）的核心组件，用于暂存从主库（Master）接收到的二进制日志事件（binlog events），然后在本地执行的中间存储。它在主从复制中扮演了关键角色，确保数据同步的可靠性和持久性。
在这里插入图片描述

4.2.5 慢查询日志

MySQL的慢查询日志用于记录响应时间超过阈值的SQL语句，帮助我们识别和优化性能较差的查询。
默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置：
开启慢查询日志：
首先，可以通过以下命令查看慢查询日志是否开启：

SHOW VARIABLES LIKE '%slow_query_log%';

如果显示slow_query_log为OFF，则表示未开启。可以使用以下命令开启慢查询日志：

SET GLOBAL slow_query_log = 1;

此设置仅对当前数据库实例有效，重启后会失效。若需永久生效，需要修改my.cnf配置文件，在[mysqld]下添加：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log    #慢查询日志文件的存放路径
long_query_time = 3    #long_query_time参数设置慢查询的时间阈值，单位为秒
log_output = FILE

4.3事务四大特性及如何保证

4.3.1 什么是事务

我们设想一个场景，这个场景中我们需要插入多条相关联的数据到数据库，不幸的是，这个过程可能会遇到下面这些问题：

数据库中途突然因为某些原因挂掉了。
多个并发访问数据库时，多个线程同时写入数据库，覆盖了彼此的更改。
。。。。。。
何为事务？总而言之，为了解决多事务并发问题，事务是逻辑上的一组操作，要么都执行，要么都不执行。

4.3.2 事务开启方式

隐式事务
事务自动开启、提交或回滚，比如insert、update、delete语句，事务的开启、提交或回滚由mysql内部自动控制的。
查看变量autocommit是否开启了自动提交：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit   | ON   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

autocommit为ON表示开启了自动提交。

显式事务
事务需要手动开启、提交或回滚，由开发者自己控制。
BEGIN：开启事务
COMMIT：提交事务
ROLLBACK：回滚事务

4.3.3 事务ACID属性

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完不成；【原子性由 undo log日志来实现。】
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；【由另外三个特性维持】
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；【锁和MVCC机制来实现】
持久性（Durabilily）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。【redo log日志来实现】

总结：
只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

4.4隔离级别和并发事务问题

4.4.1并发事务处理带来的问题

脏读（Dirty Reads）
- 事务A读取到了事务B已经修改但尚未提交的数据
不可重读（Non-Repeatable Reads）
- 事务B内部的相同查询语句在不同时刻读出的结果不一致
幻读（Phantom Reads）
- 事务A读取到了事务B提交的新增数据

4.4.2事务的隔离级别

暂时无法在飞书文档外展示此内容

查看隔离级别：

SELECT @@global.transaction_isolation;   #查看全局的事务隔离级别设置
SELECT @@session.transaction_isolation;  #查看当前会话的事务隔离级别设置

4.4.3并发事务问题演示

一、读未提交

事务A
-- 演示脏读
SET transaction_isolation='read-uncommitted';

BEGIN;
update t_user set name ="aa" where id =1;
ROLLBACK;
COMMIT;


事务B
-- 演示脏读
SET transaction_isolation='read-uncommitted';
BEGIN;
SELECT *FROM t_user  where id =1;
ROLLBACK;

二、读已提交

事务A
-- 演示不可重复度
SET transaction_isolation='read-committed';
BEGIN;
SELECT * FROM t_user;
SELECT * FROM t_user;     -- 两次读取到的数据不一致
COMMIT;


事务B
-- 演示不可重复度
SET transaction_isolation='read-committed';
BEGIN;
update t_user set name ="aa" where id =1;
COMMIT;

三、可重复读

-- 事务A
-- 演示可重复度
SET transaction_isolation='repeatable-read';
BEGIN;
SELECT * FROM t_user;
SELECT * FROM t_user;     -- 两次读取到的数据一致
COMMIT;

-- 事务B
-- 演示可重复度
SET transaction_isolation='repeatable-read';
BEGIN;
update t_user set name ="aa" where id =1;
COMMIT;



-- 演示一下幻读
BEGIN;
SELECT * from t_user;  -- 第一次查询
SELECT * from t_user;  -- 编辑数据库表 新增一条数据在查询；发现和第一次查询一致
update t_user set name ="D" where id = 4;   --  编辑id=41的数据
SELECT * from t_user;  --  再查询出现幻读
ROLLBACK;

– 在一个事务里面查询完后，编辑一下又写入到数据库 –

四、串行化

-- 演示一下串行化
SET transaction_isolation='serializable';
BEGIN;
SELECT * from t_user;  -- 第一次查询
-- 编辑数据库表 新增一条id=4的数据时被阻塞
SELECT * from t_user;  --  再查询和第一次查询
update t_user set name ="D" where id = 4;   --  编辑id=41的数据无影响
SELECT * from t_user;  --  再查询和第一次查询一致
ROLLBACK;

4.5MVCC机制

4.5.1 快照读和当前读区别

快照读：读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读。

select * from  user where id > 2;

当前读：读取的是记录数据的最新版本，显式加锁的都是当前读。

select * from user where id > 2 for update;
insert  delete  update

4.5.1 MVCC实现原理

MVCC(Multi-Version Concurrency Control)多版本并发控制，就可以做到读写不阻塞，且避免了类似脏读这样的问题，主要通过undo Log版本链和redeview读视图来实现。

4.5.1.1undo Log版本链回顾:

我们每条数据其实都有两个隐藏字段，一个是trx_id，一个是roll_pointer:

trx_id当前更新这条数据的事务ID
roll_pointer指向上一个版本的指针

4.5.1.2Read view读视图：

暂时无法在飞书文档外展示此内容

Read view 匹配条件规则如下：
在可重复读隔离级别，当事务开启，第一次查询时会生成当前事务的视图read-view，该视图在事务结束之前永远都不会变化，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

版本链比对规则：

如果 row 的 trx_id <最小活跃事务id，表示这个版本是已提交的事务生成的，这个数据是可见的；
如果 row 的 trx_id >=最大活跃事务id+1，表示这个版本是由将来启动的事务生成的，是不可见的；
如果 row 的 trx_id (最小活跃事务id <trx_id<最大活跃事务id+1)，那就包括两种情况

a. 若 row 的 trx_id 在活跃事务id数组中，表示这个版本是由还没提交的事务生成的，不可见.
b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见.

当前事务修改的数据对当前事务可见

在可重复读（RR）隔离级别下，一个事务里只会获取一次read view，都是副本共用的，从而保证每次查询的数据都是一样的。
在读已提交（RC）隔离级别下，同一个事务里面，每一次查询都会产生一个新的Read View副本，这样就可能造成同一个事务里前后读取数据可能不一致的问题（不可重复读并发问题）。

标签

#mysql #数据库