Innodb底层原理与Mysq旧志机制深入剖析

Innodb底层原理与Mysq旧志机制深入剖析

回顾mysql底层结构

mysql的组成结构，包括查询数据经过的流程，我们之前已经有过了解了，现在就更深入地了解各个组成结构的作用。
执行sql语句时，哪些操作在存储引擎、哪些操作在server端，这个是需要好好理解一下的！

Server层

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

• 连接器
  
  
  • 尝试建立连接：mysql ‐h host[数据库地址] ‐u root[用户] ‐p root[密码] ‐P 3306
    
  • 通过权限表确认权限，是否可以建立连接，用户名密码不对则提示"Access denied for user"
    
  • 已经建立的连接，管理员修改了权限也不会受到影响
    
  
  
• 分析器
  
  
  • 词法分析、语法分析
    
  • 判断对表的执行权限
    
  
  
• 优化器：mysql内部的一些优化机制
  
  
  • mysql表中有多个索引时，决定走哪一个索引，以及是否走索引
    
  • 多表关联时，决定表的连接顺序
    
  
  
• 执行器
  
  
  • 验证是否有执行权限，有权限则执行语句
    
  • 查询缓存（8.0废除此功能）、或直接查询表
    
  
  

存储引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。Mysql默认的存储引擎是Innodb
分析Innodb与mysql日志的原理

• 执行器调用Innodb提供的接口更新id=1的数据
  
• 将id=1所在记录整页数据加载到Buffer Pool中来
  
• 将数据的旧值写入undolog（用于回滚数据）
  
• 更新缓存池中记录的值
  
• 将此次更新写入redolog的缓存池中
  
• 再由缓存池写入磁盘日志文件（磁盘顺序写），redolog标记为prepare阶段
  
• 将此次数据更新写入到binlog中
  
• 将redolog标记为commit（保证两个日志数据一致）
  

一些需要注意的地方

1.为什么要加载整页的数据到缓存池中
磁盘最小读写单位是块一般为4k, Innodb将数据在组织为页（16kb，大小是块的整数倍），加载一条数据或者整页数据同样都会触发一次IO，而加载整页数据进内存,并缓存起来, 下次如果再查到这一页的数据可以直接返回数据不需要再做一次磁盘IO。
2.为什么要用磁盘顺序写
对于机械盘来说，顺序写效率更高。对现在的固态盘来说区别不大
3.为什么mysql要使用两种日志呢
历史原因：binlog是mysql自带的，属于server层；redolog是innodb存储引擎特有的
其次是分工不同，同时协同工作更能保证数据不会丢失
4.redolog与binlog在恢复数据的用途中有什么区别，为什么这样分工呢？
redolog用来恢复还没写到磁盘的数据（突然宕机了）
binlog用来恢复已经写入磁盘，但被误删除的数据。
分工不同：redolog追踪数据页状态，知道磁盘上哪些数据做了修改，而binlog只是单纯地记录了操作，比如修改了哪一条数据。

redolog是循环写的，无法长期保存历史，所以工作中我们还需要定期备份数据；
binlog日志不是流程里必须的，在配置文件可以选择是否开启binlog（binlog日志5.7默认关闭，8.0默认开启的）

Mysql日志相关的一些配置

redolog配置项

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_files_in_group ：设置redo log文件个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2个，最大100个。
show variables like '%innodb_log_files_in_group%';
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%';
redolog的磁盘写入策略

通过调整innodb_flush_log_at_trx_commit可以控制写入策略

• 值为0，仅写入redo log buffer ，不会立刻刷到磁盘。由后台进程每秒-->写到page cache -->fsync到磁盘
  
• 值为1（默认值），将redo log buffer中的事务写入page cache，立即调用fsync将日志刷新到磁盘中（简言之，直接写入磁盘）
  
• 值为2，将redo log buffer中的事务写入page cache，但是写磁盘交由系统调度
  

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

复制代码

binlog相关配置项

通过show variables like '%log_bin%';查看binlog相关参数，MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的。

图片中相关配置，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

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

复制代码

• log_bin：binlog日志是否打开
  
• log_bin_basename：是binlog日志的基本文件名，后面会自动追加编号来表示每一个文件，binlog日志文件会滚动增加
  
• log_bin_index：指定的是binlog文件的索引文件，记录当前所有有效的 binlog 文件列表。
  
• sql_log_bin：sql语句是否写入binlog文件，ON/OFF。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。
  

binlog 的日志格式

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

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

简言之，STATEMENT记录sql语句，ROW记录具体数据；MIXED按需来

binlog写入磁盘机制

通过参数调整sync_binlog值来控制：

• 值为0，每次提交事务写到page cache，让系统自行决定什么时候写入磁盘，系统宕机的话直接丢失binlog
  
• 值为1，每次提交事务直接写到磁盘，效率低下
  
• 值为N，每次提交事务写到page cache，累计N次之后写入磁盘，系统宕机只丢失N个事务的binlog
  

如何重新生成日志？

• 服务器重启
  
• 日志刷新flush logs
  
• 日志大小达到max_binlog_size
  

删除 binlog 日志文件

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

复制代码

查看 binlog 日志文件

可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容:

1. # 查看bin‐log二进制文件（命令行方式，不用登录mysql）
2. mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000007
4. # 查看bin‐log二进制文件（带查询条件）
5. 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"

复制代码

执行mysqlbinlog命令

1. mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000007

复制代码

binlog日志文件恢复数据

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

1. # 先执行刷新日志的命令生成一个新的binlog文件mysql‐binlog.000008，后面我们的修改操作日志都会记录在最新的这个文件里
2. flush logs;
3. # 执行两条插入语句
4. INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('4','zhuge','666');
5. INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('5','zhuge1','888');
6. # 假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了
7. delete from account where id > 3;

复制代码

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

1. mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000008

复制代码

找到两条插入数据的sql，每条sql的上下都有BEGIN和COMMIT，我们找到第一条sql BEGIN前面的文件位置标识 at 219(这是文件的位置标识)，再找到第二条sql COMMIT后面的文件位置标识 at 701我们可以根据文件位置标识来恢复数据，执行如下sql：

1. mysqlbinlog ‐‐no‐defaults ‐‐start‐position=219 ‐‐stop‐position=701 ‐‐database=test D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000009 | mysql ‐uroot ‐p123456 ‐v test
2. # 补充一个根据时间来恢复数据的命令，我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式
3. mysqlbinlog ‐‐no‐defaults ‐‐start‐datetime="2023‐1‐27 23:32:24"
4. ‐‐stop‐datetime="2023‐1‐27 23:34:23" ‐‐database=test D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000009 | mysql ‐uroot ‐p123456 ‐v test

复制代码

被删除数据被恢复！
undo log回滚日志

InnoDB对undo log文件的管理采用段的方式，也就是回滚段（rollback segment） 。每个回滚段记录了 1024 个undo log segment ，每个事务只会使用一个undo log segment。
在MySQL5.5的时候，只有一个回滚段，那么最大同时支持的事务数量为1024个。在MySQL 5.6开始，InnoDB支持最大 128个回滚段，故其支持同时在线的事务限制提高到了 128*1024

• innodb_undo_directory：设置undo log文件所在的路径。该参数的默认值为"./"，即innodb数据文件存储位置，目录下ibdata1文件就是undo log存储的位置。
  
• innodb_undo_logs: 设置undo log文件内部回滚段的个数，默认值为128。
  
• innodb_undo_tablespaces: 设置undo log文件的数量，这样回滚段可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件。
  

undo log日志什么时候删除
新增类型的，在事务提交之后就可以清除掉了。
修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。
为什么Mysql不能直接更新磁盘上的数据而要设置这么一套复杂的机制来执行SQL了？
因为请求直接对磁盘随机读写的话性能很差，无法让数据库抗住很高的并发。
Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。
更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。
log_error错误日志

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

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

复制代码

类似数据库启动失败之类的，都可以在这里找到一些详细的原因说明

general_log通用查询日志

通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL 数据库服务器的所有 SQL 指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。
通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。

• general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。
  
• general_log_file：通用查询日志记录的位置参数。
  

1. show variables like '%general_log%';
2. # 打开通用查询日志
3. SET GLOBAL general_log=on;

复制代码

一般不建议开启，只在需要调试查询问题时开启。

来源：豆瓜网用户自行投稿发布，如果侵权，请联系站长删除