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

飞越沧海的蝴蝶

已于 2023-09-20 12:06:53 修改

阅读量164

点赞数 2

分类专栏： Java架构学习之旅文章标签： mysql 数据库

于 2023-09-18 11:18:54 首次发布

本文链接：https://blog.csdn.net/weixin_42287045/article/details/132969572

版权

Java架构学习之旅专栏收录该内容

24 篇文章 0 订阅

订阅专栏

学习内容重点：

1、MySQL的内部组件结构详解

2、Innodb存储引擎底层原理剖析

3、Redolog重做日志原理详解

4、binlog二进制归档日志原理详解

5、删库跑路之数据恢复实战

6、undo log回滚日志原理详解

7、错误日志与通用查询日志详解

MySQL的内部组件结构

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server层

主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说如果我们在create table时不指定表的存储引擎类型,默认会给你设置存储引擎为InnoDB。

下面我们来看下Server层的连接器、查询缓存、分析器、优化器、执行器分别主要干了哪些事情。

连接器

简单释义：连接器负责处理客户端的连接请求，验证用户的身份，以及向客户端发送响应。连接器还负责处理事务和锁定，并确保数据完整性。

详细释义：

我们知道由于MySQL是开源的，他有非常多种类的客户端：navicat,mysql front,jdbc,SQLyog等非常丰富的客户端，包括各种编程语言实现的客户端连接程序，这些客户端要向mysql发起通信都必须先跟Server端建立通信连接，而建立连接的工作就是有连接器完成的。

第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

1、如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。

2、如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

查询缓存

简单释义：查询缓存可以缓存已经执行过的查询结果。如果同样的查询再次执行，MySQL可以直接从缓存中读取结果，而不用再次执行查询，从而提高查询性能。

详细释义：

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

为什么大多数情况查询缓存就是个鸡肋？

因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

一般建议大家在静态表里使用查询缓存，什么叫静态表呢？就是一般我们极少更新的表。比如，一个系统配置表、字典表，那这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种“按需使用”的方式。你可以将my.cnf参数 query_cache_type 设置成 DEMAND。

#query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存

这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

select SQL_CACHE * from account where id = 2；

查看当前mysql实例是否开启缓存机制

show global variables like "%query_cache_type%";

mysql 8.0已经移除了查询缓存功能

分析器

简单释义：分析器负责分析SQL查询语句，检查语法错误，并生成执行计划。执行计划是MySQL用于执行查询的一组指令。

详细释义：

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

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

做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒。

优化器

简单释义：优化器根据执行计划和表统计信息，确定最优的查询执行方式。它会尝试使用索引、排序和连接等技术，以最小的代价获取结果。

执行器

简单释义：执行器根据执行计划和优化器生成的指令，执行SQL查询，并将结果返回给客户端。

详细释义：开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证)。

select * from account where id=10;

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

Innodb底层原理与Mysql日志机制

redo log重做日志关键参数

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

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

innodb_log_files_in_group：设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2个，最大100个。

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

redo log 写入磁盘过程分析：

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

innodb_flush_log_at_trx_commit：这个参数控制 redo log 的写入策略，它有三种可能取值：

binlog二进制归档日志

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

启动binlog记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处则大于对服务器的影响。

MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的。上图中log_bin的值是OFF就代表binlog是关闭状态，打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

在配置文件中的[mysqld]部分增加如下配置:

#2023-09-18 start add
# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# 其他配置
# 日志文件格式
binlog_format=row
# 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
expire_logs_days=15
# 单个binlog日志文件的大小限制，默认为 1GB
max_binlog_size=200M
# binlog 刷写的策略
sync_binlog=1
#2023-09-18 start end

重启数据库后我们再去看data数据目录会多出两个文件，第一个就是binlog日志文件，第二个是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。

可以执行命令查看有多少binlog文件

属性释义：

binlog 的日志格式

用参数 binlog_format 可以设置binlog日志的记录格式，mysql支持三种格式类型：

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写入磁盘机制(与上面的redo日志很像)

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

删除或查看日志可以自行百度搜索，这里不多做赘述

执行mysqlbinlog命令

注意，这里的可以直接执行的话必须配置环境变量，要不然得到相应的目录下执行

.\mysqlbinlog --no-defaults -v --base64-output=decode-rows "D:\ProgramData\MySQL\MySQL Server 5.7\Data\mysql-binlog.000001"

能看到里面有具体执行的修改伪sql语句以及执行时的相关情况。

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230918 13:08:51 server id 1  end_log_pos 123 CRC32 0xe3641764  Start: binlog v 4, server v 5.7.17-log created 230918 13:08:51 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#230918 13:08:51 server id 1  end_log_pos 154 CRC32 0x0a548c78  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

binlog日志文件恢复数据

用binlog日志文件恢复数据其实就是回放执行之前记录在binlog文件里的sql，举一个数据恢复的例子

# 先执行刷新日志的命令生成一个新的binlog文件mysql-binlog.000002，后面我们的修改操作日志都会记录在最新的这个文件里
flush logs;
# 执行两条插入语句
INSERT INTO `test1`.`account` (`id`, `name`, `balance`) VALUES ('8', 'zhaowu', '6660');
INSERT INTO `test1`.`account` (`id`, `name`, `balance`) VALUES ('9', 'zhaoqi', '8880');
# 假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了

# 现在需要恢复被删除的两条数据，我们先查看binlog日志文件

.\mysqlbinlog --no-defaults -v --base64-output=decode-rows "D:\ProgramData\MySQL\MySQL Server 5.7\Data\mysql-binlog.000002"

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230918 13:20:35 server id 1  end_log_pos 123 CRC32 0x3d788405  Start: binlog v 4, server v 5.7.17-log created 230918 13:20:35 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#230918 13:20:35 server id 1  end_log_pos 154 CRC32 0x5aa0ca95  Previous-GTIDs
# [empty]
# at 154
#230918 13:22:23 server id 1  end_log_pos 219 CRC32 0x9a6709c2  Anonymous_GTID  last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230918 13:22:23 server id 1  end_log_pos 292 CRC32 0x05d85363  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1695100943/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 292
#230918 13:22:23 server id 1  end_log_pos 347 CRC32 0x7d65b31d  Table_map: `test1`.`account` mapped to number 4707
# at 347
#230918 13:22:23 server id 1  end_log_pos 399 CRC32 0x6a410fa6  Write_rows: table id 4707 flags: STMT_END_F
### INSERT INTO `test1`.`account`
### SET
###   @1=8
###   @2='zhaowu'
###   @3=6660
# at 399
#230918 13:22:23 server id 1  end_log_pos 430 CRC32 0xba4bec17  Xid = 22
COMMIT/*!*/;
# at 430
#230918 13:22:51 server id 1  end_log_pos 495 CRC32 0x062fc577  Anonymous_GTID  last_committed=1        sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 495
#230918 13:22:51 server id 1  end_log_pos 568 CRC32 0x638c6360  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1695100971/*!*/;
BEGIN
/*!*/;
# at 568
#230918 13:22:51 server id 1  end_log_pos 623 CRC32 0xcce3a3f8  Table_map: `test1`.`account` mapped to number 4707
# at 623
#230918 13:22:51 server id 1  end_log_pos 675 CRC32 0x929bd2d3  Write_rows: table id 4707 flags: STMT_END_F
### INSERT INTO `test1`.`account`
### SET
###   @1=9
###   @2='zhaoqi'
###   @3=8880
# at 675
#230918 13:22:51 server id 1  end_log_pos 706 CRC32 0xa521b19e  Xid = 25
COMMIT/*!*/;
# at 706
#230918 13:24:27 server id 1  end_log_pos 771 CRC32 0xae78e491  Anonymous_GTID  last_committed=2        sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 771
#230918 13:24:27 server id 1  end_log_pos 844 CRC32 0x71de003e  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1695101067/*!*/;
BEGIN
/*!*/;
# at 844
#230918 13:24:27 server id 1  end_log_pos 899 CRC32 0x82061057  Table_map: `test1`.`account` mapped to number 4707
# at 899
#230918 13:24:27 server id 1  end_log_pos 951 CRC32 0xea4db289  Delete_rows: table id 4707 flags: STMT_END_F
### DELETE FROM `test1`.`account`
### WHERE
###   @1=8
###   @2='zhaowu'
###   @3=6660
# at 951
#230918 13:24:27 server id 1  end_log_pos 982 CRC32 0x6d96d3c9  Xid = 28
COMMIT/*!*/;
# at 982
#230918 13:24:27 server id 1  end_log_pos 1047 CRC32 0x54f03851         Anonymous_GTID  last_committed=3        sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1047
#230918 13:24:27 server id 1  end_log_pos 1120 CRC32 0x0168bec8         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1695101067/*!*/;
BEGIN
/*!*/;
# at 1120
#230918 13:24:27 server id 1  end_log_pos 1175 CRC32 0x583677ae         Table_map: `test1`.`account` mapped to number 4707
# at 1175
#230918 13:24:27 server id 1  end_log_pos 1227 CRC32 0x06c249f6         Delete_rows: table id 4707 flags: STMT_END_F
### DELETE FROM `test1`.`account`
### WHERE
###   @1=9
###   @2='zhaoqi'
###   @3=8880
# at 1227
#230918 13:24:27 server id 1  end_log_pos 1258 CRC32 0x5d6c0096         Xid = 29
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

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

我们可以根据文件位置标识来恢复数据，执行如下sql：

./mysqlbinlog  --no-defaults --start-position=292 --stop-position=706 --database=test1 "D:\ProgramData\MySQL\MySQL Server 5.7\Data\mysql-binlog.000002" | ./mysql -uroot -proot -v test1

被删除数据被恢复。

注意：假设我们把数据库所有数据都删除了要怎么恢复了，如果数据库之前没有备份，所有的binlog日志都在的话，就从binlog第一个文件开始逐个恢复每个binlog文件里的数据，这种一般不太可能，因为binlog日志比较大，早期的binlog文件会定期删除的，所以一般不可能用binlog文件恢复整个数据库的。

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

undo log回滚日志

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

在MySQL5.5的时候，只有一个回滚段，那么最大同时支持的事务数量为1024个。在MySQL 5.6开始，InnoDB支持最大128个回滚段，故其支持同时在线的事务限制提高到了 128*1024 。

undo log日志什么时候删除

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

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

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

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差，所以直接更新磁盘文件是不能让数据库抗住很高并发的。

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

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。这样MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

错误日志

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

在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

通用查询日志

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

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

general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。

general_log_file：通用查询日志记录的位置参数。

# 打开通用查询日志 
SET GLOBAL general_log=on;

飞越沧海的蝴蝶

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql-7.Innodb底层原理与Mysql日志机制深入剖析

1、MySQL的内部组件结构详解2、Innodb存储引擎底层原理剖析3、Redolog重做日志原理详解4、binlog二进制归档日志原理详解5、删库跑路之数据恢复实战6、undo log回滚日志原理详解7、错误日志与通用查询日志详解
复制链接

扫一扫