9.Innodb底层原理与Mysql日志机制深入剖析

小虾米吃橘子
136 阅读8分钟

MySQL的内部组件结构

MySQL 由 Server层 和 连接器 组成

连接器

用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份。

一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。

Server层

分析器

MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

SQL语句经过分析器分析之后，会生成一个语法树。

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；

或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序；

以及一些mysql自己内部的优化机制。

执行器

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

存储引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB。

Innodb底层原理与Mysql日志机制

redo log

关键参数

################Configuring Redo Log Capacity (MySQL 8.0.30 or Higher)

SELECT FILE_NAME, START_LSN, END_LSN FROM performance_schema.innodb_redo_log_files;
#取代了 innodb_log_files_in_group and innodb_log_file_size
show variables like '%innodb_redo_log_capacity%';

SHOW STATUS LIKE 'Innodb_redo_log_resize_status';
SHOW STATUS LIKE 'Innodb_redo_log_capacity_resized';

SELECT FILE_ID, START_LSN, END_LSN, SIZE_IN_BYTES, IS_FULL, CONSUMER_LEVEL 
FROM performance_schema.innodb_redo_log_files;

################Configuring Redo Log Capacity (Before MySQL 8.0.30)
#设置redo log buffer大小参数，默认16M 
show variables like '%innodb_log_buffer_size%';
#设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。
show variables like '%innodb_log_group_home_dir%';
#设置redo log文件的个数
show variables like '%innodb_log_files_in_group%';
#设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和
show variables like '%innodb_log_file_size%';

#这个参数控制 redo log 的写入策略
show variables like '%innodb_flush_log_at_trx_commit%';

redo log 写入磁盘过程分析

redo log 从头开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写。

redo log 的写入策略

  • 设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数据。
  • 设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。
  • 设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 操作系统函数 write 写到文件系统的 page cache，然后调用操作系统函数 fsync 持久化到磁盘文件。

binlog

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。

# 查看binlog相关参数
show variables like '%log_bin%';

binlog 的日志格式（三种）

  • STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。
  • ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
  • MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

binlog写入磁盘机制

binlog写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0。

  • 为0的时候，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
  • 也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
  • 还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

发生以下任何事件时, binlog日志文件会重新生成：

  • 服务器启动或重新启动
  • 服务器刷新日志，执行命令flush logs
  • 日志文件大小达到 max_binlog_size 值，默认值为 1GB

删除 binlog 日志文件

删除当前的binlog文件
reset master;
# 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql-binlog.000006';
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2023-01-21 14:00:00';

查看 binlog 日志文件

# 查看bin-log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows /var/lib/mysql/binlog.000001
mysqlbinlog --no-defaults -v --base64-output=decode-rows /var/lib/mysql/binlog.000041 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 13:26:18" start-position="5000" stop-position="20000"

binlog日志文件恢复数据

INSERT INTO `account` (`id`, `name`, `balance`) VALUES (10, 'zhuge', '666');
INSERT INTO `account` (`id`, `name`, `balance`) VALUES (11, 'zhuge1', '888');

delete from account where id > 3;

找到两条插入数据的sql，每条sql的上下都有BEGIN和COMMIT，我们找到第一条sql BEGIN前面的文件位置标识 at 896(这是文件的位置标识)，再找到第二条sql COMMIT后面的文件位置标识 at 1426

我们可以根据文件位置标识来恢复数据


mysqlbinlog  --no-defaults --start-position=896 --stop-position=1426 --database=tmp_acid /var/lib/mysql/binlog.000001 | mysql -v tmp_acid

备份数据

一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

mysqldump -u root tmp_acid > tmp_bak #备份整个数据库
#mysql创建数据库
CREATE DATABASE tmp_bak DEFAULT CHARACTER SET 'utf8mb4';
mysql -u root tmp_bak < tmp_bak #恢复整个数据库

为什么会有redo log和binlog两份日志呢？

binlog 日志只能用于归档，属于存储引擎层外的功能。

InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

undo log

InnoDB对undo log文件的管理采用段的方式，也就是回滚段（rollback segment） 。每个回滚段记录了 1024 个 undo log segment ，每个事务只会使用一个undo log segment。

# 查看undo相关配置
show variables like '%undo%';

undo log日志什么时候删除

新增类型的，在事务提交之后就可以清除掉了。

修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。

为什么Mysql不能直接更新磁盘上的数据而设置这么一套复杂的机制来执行SQL了？

提升读写性能，在异常情况下保证数据一致。

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。

因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。

Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

错误日志

Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

# 查看错误日志存放位置
show variables like '%log_error%';

通用查询日志

通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题

通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。

show variables like '%general_log%';
# 打开通用查询日志
SET GLOBAL general_log=on;
avatar
文章
阅读
粉丝