【MySQL-InnoDB底层原理与MySQL日志机制详解】

一、MySQL的内部组件结构

1.1、内部组件结构图示

如上图，MySQL的内部结构主要是分为三层，分别为：客户端、service层和引擎层，各自组件的职责描述如上图所述

1.2、MySQL的内部组件结构—InnoDB引擎底层原理与执行过程图解

对于上图中，在执行一条修改数据的操作时，在InnoDB存储引擎下，有以下步骤：

1. 首先，先结合更新数据的条件，到磁盘中读取满足条件的页数据，并且加入到buffer pool缓存区域中；
2. 再将要修改的数据备份到undo log日志中去，目的是为了当前事务在执行的时，如果出现异常，方便更新前数据的复原；
3. 备份完毕之后，就将buffer pool中的数据进行更新；
4. 更新缓存数据完毕后，将更新的操作记录到redo log日志中，先写入此文件，是因为这个些数据时是磁盘的顺序写，效率高，redo log日志保存的是更新数据的物理逻辑，例如：在哪一页做了什么操作，这个日志是：如果在缓存中的数据没有及时写入到磁盘中，突然断电了，那缓存中的数据就会被清空，这时，就需要利用redo log中保存的数据进行复原数据
5. 与此同时，也需要将数据的操作记录到binlog中，这个日志其实主要是为了误删数据库中的数据，如果数据需要复原，需要用到此文件
6. binlog日志记录完毕之前，会提交一个commit标识到redo log中，这时候就完成事务的提交，要等redo log 和binlog日志写完之后才提交事务是因为要确保redo log和binlog的数据一致性；
7. 最后才会在系统空闲之余，将buffer pool缓存修改的数据写入磁盘中；

二、Mysql日志机制

2.1、MySQL的日志文件分类

2.1.1 undo log回滚日志文件

1、文件的管理是以分段的方式进行管理的，也就是回滚段（rollback segment），每一个回滚段记录了1024个undo log segment，每一个事务只会使用一个undo log segment；
2、在MySQL5.5之前，只有一个回滚段，最大同时支持的事务数量为1024个，在MySQL5.6开始，可以支持128个回滚段，所以在线的事务可以提高到128*1024个事务；
3、undo log 日志如果是新增类型，在事务提交之后清除，如果是修改类型的，事务提交之后不会立马清除，因为这些修改的产生的日志版本链会用于MVCC，只有当没有事务用到该版本信息才会清除掉undo log 日志

2.1.1 redo log日志文件

1、redo log重做日志的关键参数：

• InnoDB_log_buffer_size：设置redo log buffer 大小参数，默认为16M，最大值为2G（4096M），最小为1M，可通过以下SQL查看其默认值大小，例：

show variables like '%innodb_log_buffer_size%';

• InnoDB _log_group_home_dir：设置redo log 文件存储的位置参数，默认是在MySQL安装路径下的data目录下，其中的ib_logfile0和ib_logfile1即为redo_log文件，如下：

show variables like '%innodb_log_group_home_dir%';

• innodb_log_file_size：设置单个redo log文件大小，默认值为48M，最大值为512G，着512G指的是所有redo log 文件的总和，而不是指单个文件，例：

show variables like '%innodb_log_file_size%';

2、redo log写入磁盘的过程：
redo log日志在写入数据到磁盘时，有两个指针同时协作写入的，分别是写入指针和擦除指针
写入指针：是记录当前的位置，一边写一边往后移，写到最后一个文件末尾后，就会回到开头文件开始；
擦除指针：是当前要擦除的位置，也是会逐步往后推移且循环的，可以用来记录新的操作，如果写指针追上和要擦除指针，表示redo log写满了，这时候不能再执行新的更新了，需要停下来先擦除一些记录，把擦除指针往后推进下

3、redo log写入磁盘的方式：其写入方式主要有三种，分别是InnoDB_flush_log_at_trx_commit的参数来控制写入策略，其参数取值有三个，分别为0、1、2，各自取值的含义如下：

 # 查看innodb_flush_log_at_trx_commit参数值：
 show variables like 'innodb_flush_log_at_trx_commit';
# 设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)：
set global innodb_flush_log_at_trx_commit=1;

若设置为0：表示当前的事务提交事务时，只是把需要写入redo log的数据写入到redo log buffer 中，由后台程序后续写入redo log文件，但是这种方式，如果数据库宕机了，缓存的数据就不复存在了，无法恢复数据；
若设置为1（默认值）：表示当前的事务提交事务时，都需要将数据先写入redo log文件中，数据比较安全，不存在数据丢失问题，但是效率稍微低些；
若设置为2：表示当前的事务提交事务时，会将需要写入redo log的数据写入到操作系统的page cahe中，后续操作系统在空闲期就会将缓存区中的数据写入磁redo log文件中，这种情况，MySQL宕机了不会丢失数据，但是如果是操作系统宕机了，缓存数据没来得及持久化的话，也会丢失缓存中的数据；

具体写入方式的图示如下：

2.1.1 binlog日志文件

1、binlog二进制日志记录了所有执行过的修改操作语句，不会保存查询的操作数据，在MySQL出现意外停止时，可以通过二进制日志文件进行排查，用户操作数据或者表结构都有相关的操作日志记录，但是启动binlog日志会影响性能，如果需要回复数据或者是主从复制功能，可以开启，在MySQL5.7版本中默认是关闭的，8.0默认才开启，我们可以通过以下语句查看其状态值：

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

上图中的log_bin的value为off就表示当前的binlog是关闭的，没有可以通过**修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库，具体修改的参数如下：

# 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 # 执行自动删除binlog日志文件的天数， 默认为0， 表示不自动删除
 max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

2、binlog日志储存数据的方式：其是通过设置参数binlog_format的值来控制的，主要分为三种类型，分别是：
STATEMENT：这是基于SQL进行复制的，每一条修改的SQL就都会记录到master的binlog中去，这种方式日志量小，节省了IO的开销，性能较好，但是如果是SQL中存在一些函数，例如SYSDATE()等，那如果涉及到主从问题，就会产生数据不一致问题；
ROW：这是一种基于行的复制，日志中会记录成每一行数据修改的形式，若涉及主从的，就会在从节点上对相同的数据进行修改，可以解决第一种方式产生的数据不一致问题，但是，性能肯定没有第一种statement方式的效率高；
MIXED：这种方式是将上联的两种方式进行组合使用，在这种模式下，MySQL会根据执行的每一条具体的SQL来区分对待记录的日志形式，就是会在StateMent和Row中选择一种，推荐使用的存储方式；

3、binlog日志写入磁盘的方式：其和redo log方式很相似，主要通过 sync_binlog 参数控制，默认值是0，不同于redo log的点在于，redo log有redo log buffer，而binlog没有，其sync_binlog 参数取值主要有以下几种：

• sync_binlog为0：表示每次提交事务都只写到page cahe中，有系统自行判断什么时候执行fsync写入磁盘，会有丢失数据的风险
• sync_binlog为1：表示每次提交事务都会执行fsync写入磁盘，数据不会丢失，数据安全
• sync_binlog为大于1的N：这种方式表示每次提交事务都只写到page cahe中，一直到page cahe中积累了指定数量的事务，才会执行fsync写入磁盘

4、binlog日志文件数量新增场景列举：

1. 服务器重启或启动；
2. 服务器刷新日志，执行新的flush logs；
3. 日志文件大小达到设置的文件容量值，默认为1个G

5、删除binlog日志文件

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

6、查看binlog日志文件

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

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

SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 # at 291
 #230127 21:22:48 server id 1 end_log_pos 345 CRC32 0xc4ab653e Table_map: `test`.`account` mapped
to number 99
 # at 345
 #230127 21:22:48 server id 1 end_log_pos 413 CRC32 0x54a124bd Update_rows: table id 99 flags: ST
MT_END_F
 ### UPDATE `test`.`account`
 ### WHERE
 ### @1=1
 ### @2='lilei'
 ### @3=1000
 ### SET
 ### @1=1
 ### @2='lilei'
 ### @3=2000
 # at 413
 #230127 21:22:48 server id 1 end_log_pos 444 CRC32 0x23355595 Xid = 10
 COMMIT/*!*/;
 # at 444

7、binlog日志文件恢复数据
通过binlog文件恢复数据主要有两种方式，第一种是通过查看binlog文件，确认要恢复的数据，通过偏移量来恢复：
例如上一步查询出的，在事务的BEGIN之后或者之前会有一个at XX，这里就是BEGIN后，为# at 291，然后在COMMIT之后也有一个，就找着两个区间，秩序以下SQL进行数据恢复数据：

mysqlbinlog ‐‐no‐defaults ‐‐start‐position=291 ‐‐stop‐position=444 ‐‐database=test D:/dev/mysql‐
5.7.25‐winx64/data/mysql‐binlog.000009 | mysql ‐uroot ‐proot ‐v test

除此外，还可以通过另外一种方式进行数据的恢复，那就是找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544， 再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式，执行以下SQL进行数据恢复：

mysqlbinlog ‐‐no‐defaults ‐‐start‐datetime="2023‐1‐27 23:32:24" ‐‐stop‐datetime="2023‐1‐27 23:34:
23" ‐‐database=test D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000009 | mysql ‐uroot ‐proot ‐v
test

**但是，如果是要恢复删库跑路的这种数据，如果数据库之前没有备份，所有的binlog日志都在的话，就从binlog第一个文件开始逐个恢复每个binlog文件里的数据，这种一般不太可能，因为binlog日志比较大，早期的binlog文件会定期删除的，所以一般不可能用binlog文件恢复整个数据库的。
一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备
份时间点之后的binlog来恢复数据。
备份数据库一般可以用mysqldump 命令工具

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

 mysql ‐u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test

三、MySQL优化总结

一、数据库优化方式

3.1.1 计算机硬件层面

3.1.2 计算机系统配置层面

3.1.3 数据表结构层面

3.1.4 SQL以及索引优化层面

从1.1~1.4的优化方案中，逐个往下，优化的成本是不断在降低，而且优化的效果是不断在增加的