InnoDB 底层原理

InnoDB更新语句及日志底层原理

原创已于 2025-05-30 14:24:53 修改 · 690 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #InnoDB #mysql

于 2025-05-27 22:59:39 首次发布

MySQL 专栏收录该内容

11 篇文章

订阅专栏

更新语句整体执行流程

undo log

undo log 作用
如果事务提交失败，数据要回滚，利用 undo log 恢复 Buffer Poll 缓存中的数据。

redo log

redo log 和 idb 文件写入方式对比
redo log 写入方式是磁盘顺序写，即首先为日志文件分配好磁盘空间，每次的更新语句在文件末尾追加。

idb 文件的写入方式是随机写，因为每张表对应一个 idb 文件，当有多条更新 sql 语句，要修改不同的表时，由于 idb 文件存放顺序不确定，也就不能顺序写。

redo log 作用
当 Buffer Pool 缓存池中的文件还没来得及更新到 ibd 磁盘文件中，系统宕机了，这时可以利用 redo log 文件恢复 ibd 文件内容。

redo log 写入磁盘过程分析
redo log 文件可能不止一个，这些文件是首尾相连的，写入时从第一个文件开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写，如下面这个图所示。

在这里插入图片描述
如果 write pos 追上了 check point，这时要把文件中的一部分内容持久化道磁盘中，为后续的写入清出一定的空间。

redo log 日志关键参数
innodb_log_buffer_size： 设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。

show variables like '%innodb_log_buffer_size%';

innodb_log_group_home_dir： 设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。

show variables like '%innodb_log_group_home_dir%';

innodb_log_file_size： 设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。

show variables like '%innodb_log_file_size%';

innodb_flush_log_at_trx_commit： 控制 redo log 的写入策略，有三种值。

0：每次事务提交时，只把日志信息保存到 redo log buffer 中，如果数据库宕机可能会丢数据。
1：每次提交事务时，直接把日志信息持久化到磁盘，性能稍微低点，但是数据一定不会丢失，线上环境推荐用这个。
2：每次事务提交把日志信息写到操作系统的内存 page cache 中。如果数据库宕机，数据不会丢失，但是如果操作系统宕机了，page cache 中的数据还没有保存到磁盘中，数据就会丢失。

show variables like 'innodb_flush_log_at_trx_commit';

InnoDB 有一个后台线程，每隔一秒会利用 write 函数把 redo log buffer 中的数据保存到 page cache 中，再由操作系统使用 fsync 函数把数据持久化到磁盘中。所以当 innodb_flush_log_at_trx_commit 的值为 2 时，操作系统会把 page cache 中的数据持久化到磁盘中。

bin log

binlog 使用
MySQL5.7版本默认关闭，可以通过以下配置来开启

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 其他配置
binlog_format = row # 日志文件格式，下面会详细解释
expire_logs_days = 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

查看 binlog 相关信息

show variables like '%log_bin%';

查看有多少个文件

show binary logs;

binlog 作用
1、保存所有执行过的修改数据的语句，当不小心删除了表中的数据，可以通过 binlog 来恢复。
2、主从复制功能也可以通过 binlog 来实现。

binlog 日志关键参数

binlog_format: 设置日志记录格式，有三个值。

STATEMENT：记录 sql 语句，缺点是例如对于 UUID()、SYSDATE()等函数的结果和之前执行的不一样。
ROW：记录更新后的那条数据信息，例如一个 sql 语句更新了十条数据，那么这种记录方式就会记录这十条数据。缺点是记录内容多。
MIXED：混合模式。如果 sql 语句中包含只有在执行时才知道结果的情况就用 ROW，否则用 STATEMENT。

sync_binlog: 写入磁盘机制，也有三个值可以选择。

0：每次提交事务后把数据保存到 page cache 中，由系统决定什么时候写到磁盘中。
1：每次提交数据都会把数据写入磁盘，这种最安全。
N(>1)：每次提交事务后把数据保存到 page cache 中，保存到 N 个事务之后写入磁盘。

查看 binlog 日志文件

# 查看bin-log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 

# 查看bin-log二进制文件（带查询条件）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 00:00:00" start-position="5000" stop-position="20000"