Innodb底层原理与Mysql日志机制深入剖析 知识体系总览一、MySQL内部组件结构✅1. Server层与存储引擎层架构✅2. 各组件详解连接器查询缓存分析器优化器执行器二、redo log重做日志✅1. 四大关键参数✅2. redo log 写入磁盘过程分析✅3. innodb_flush_log_at_trx_commit 写入策略三、binlog二进制归档日志✅1. binlog配置与启用✅2. 三种日志格式✅3. sync_binlog写入磁盘机制✅4. binlog文件管理✅5. binlog数据恢复实战✅6. 为什么需要两份日志 redo log vs binlog 全量对比四、undo log回滚日志✅1. undo log管理机制✅2. undo log删除时机✅3. 为什么不直接更新磁盘五、其他日志✅1. 错误日志✅2. 通用查询日志 全文总结✅1. Server层五大组件✅2. redo log三策略✅3. binlog三格式✅4. 两份日志的分工✅5. undo log双重作用✅6. binlog数据恢复✅7. MySQL日志体系速记 知识体系总览Innodb底层原理与Mysql日志机制 ├── 一、MySQL内部组件结构 │ ├── ✅1. Server层与存储引擎层架构 │ └── ✅2. 各组件详解连接器→查询缓存→分析器→优化器→执行器 ├── 二、redo log重做日志 │ ├── ✅1. 四大关键参数 │ ├── ✅2. 写入磁盘过程write pos / checkpoint │ ├── ✅3. innodb_flush_log_at_trx_commit写入策略 │ └── 三种策略对比表 ├── 三、binlog二进制归档日志 │ ├── ✅1. binlog配置与启用 │ ├── ✅2. 三种日志格式STATEMENT/ROW/MIXED │ ├── ✅3. sync_binlog写入磁盘机制 │ ├── ✅4. binlog文件管理生成/删除/查看 │ └── ✅5. binlog数据恢复实战 ├── 四、undo log回滚日志 │ ├── ✅1. undo log管理机制 │ └── ✅2. undo log删除时机 ├── 五、其他日志 │ ├── ✅1. 错误日志 │ └── ✅2. 通用查询日志 └── 全文总结一、MySQL内部组件结构✅1. Server层与存储引擎层架构大体来说MySQL 可以分为 Server 层和存储引擎层两部分。Server层主要包括连接器、查询缓存、分析器、优化器、执行器等涵盖 MySQL 的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等所有跨存储引擎的功能都在这一层实现比如存储过程、触发器、视图等。存储引擎层负责数据的存储和提取。其架构模式是插件式的支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB它从 MySQL 5.5.5 版本开始成为了默认存储引擎。 核心MySQL采用插件式存储引擎架构Server层负责SQL解析和执行计划存储引擎层负责实际数据存取。这也是为什么MySQL能同时支持多种存储引擎的根本原因。✅2. 各组件详解连接器负责跟客户端建立连接、获取权限、维持和管理连接[root192 ~]# mysql -h host[数据库地址] -u root[用户] -p root[密码] -P 3306 关键理解权限在连接时读取并缓存连接期间即使管理员修改权限也不影响已有连接只有新建连接才会使用新权限。查询缓存MySQL 拿到查询请求后先到查询缓存查 key-valuekey查询语句value查询结果。命中则直接返回。 重要查询缓存弊大于利——任何表更新都会清空该表所有缓存。对于更新压力大的数据库命中率极低。MySQL 8.0已移除查询缓存功能。因为查询缓存的失效非常频繁只要有对一个表的更新这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来还没使用呢就被一个更新全清空了。对于更新压力大的数据库来说查询缓存的命中率会非常低。-- 查看是否开启缓存showglobalvariableslike%query_cache_type%;-- 按需使用仅对特定SQL开启缓存selectSQL_CACHE*fromtestwhereID5;分析器先做词法分析识别SQL中的关键字、表名、列名再做语法分析判断SQL是否满足MySQL语法最终生成语法树。例如如果你的语句不对就会收到“You have an error in your SQL syntax”的错误提醒比如下面这个语句 from 写成了 “rom”。mysqlselect*fro testwhereid1;ERROR1064(42000): You have an errorinyourSQLsyntax;checkthe manual that correspondstoyour MySQL server versionfortherightsyntaxtousenearfro test where id1at line1优化器经过了分析器MySQL 就知道你要做什么了。在开始执行之前还要先经过优化器的处理。优化器是在表里面有多个索引的时候决定使用哪个索引或者在一个语句有多表关联join的时候决定各个表的连接顺序以及一些mysql自己内部的优化机制。执行器开始执行的时候要先判断一下你对这个表 T 有没有执行查询的权限如果没有就会返回没有权限的错误如下所示 (在工程实现上如果命中查询缓存会在查询缓存返回结果的时候做权限验证)。mysqlselect*fromtestwhereid10;如果有权限就打开表继续执行。打开表的时候执行器就会根据表的引擎定义去使用这个引擎提供的接口。二、redo log重做日志 核心redo log是InnoDB特有的日志用于实现crash-safe能力——即使数据库异常重启已提交的事务数据也不会丢失。✅1. 四大关键参数参数说明默认值innodb_log_buffer_sizeredo log buffer大小默认16M最大4096M最小1Minnodb_log_group_home_dirredo log文件存储位置./数据文件目录innodb_log_files_in_groupredo log文件个数2最大100innodb_log_file_size单个redo log文件大小48Mshowvariableslike%innodb_log_buffer_size%;-- redo log buffer大小参数showvariableslike%innodb_log_group_home_dir%;-- redo log文件存储位置参数showvariableslike%innodb_log_files_in_group%;-- redo log文件的个数showvariableslike%innodb_log_file_size%;-- 单个redo log文件大小如下时redo log文件存储形式✅2. redo log 写入磁盘过程分析redo log 从头开始写写完一个文件继续写另一个文件写到最后一个文件末尾就又回到第一个文件开头循环写。write pos当前记录位置一边写一边后移checkpoint当前要擦除的位置擦除前要先把记录更新到数据文件write pos 和 checkpoint 之间的部分就是空着的可写部分可以用来记录新的操作。如果 write pos 追上checkpoint表示redo log写满了这时候不能再执行新的更新得停下来先擦掉一些记录把 checkpoint 推进一下。✅3. innodb_flush_log_at_trx_commit 写入策略这个参数控制 redo log 的写入策略有三种可能取值设置为0每次事务提交时只把 redo log 留在 redo log buffer数据库宕机可能丢失数据设置为1(默认值)每次事务提交时将 redo log 直接持久化到磁盘数据最安全线上推荐设置为2每次事务提交时只把 redo log 写到 OS 的 page cache数据库宕机不丢失OS宕机丢失InnoDB 有一个后台线程每隔 1 秒把 redo log buffer 中的日志调用 write 写到 page cache然后调用 fsync 持久化到磁盘。showvariableslikeinnodb_flush_log_at_trx_commit;setglobalinnodb_flush_log_at_trx_commit1;三、binlog二进制归档日志 核心binlog是Server层的归档日志记录所有修改操作用于数据恢复和主从复制。MySQL 5.7默认关闭8.0默认打开。✅1. binlog配置与启用启动binlog记录功能会影响服务器性能但如果需要恢复数据或主从复制功能则好处则大于对服务器的影响。# 查看binlog相关参数showvariableslike%log_bin%;打开binlog功能需要修改配置文件my.ini(windows)或my.cnf(linux)然后重启数据库。在配置文件中的[mysqld]部分增加如下配置:# my.cnf 配置 log-binmysql-binlog # binlog存放位置 server-id1 # 服务器唯一ID,集群环境中每台mysql服务器的id不能一样不加启动会报错 binlog_format row # 日志格式 expire_logs_days 15 # 自动删除15天前的binlog max_binlog_size 200M # 单个文件大小限制默认为 1GB重启数据库后我们再去看data数据目录会多出两个文件第一个就是binlog日志文件第二个是binlog文件的索引文件这个文件管理了所有的binlog文件的目录。showbinarylogs;-- 查看binlog文件列表,看有多少binlog文件showvariableslike%log_bin%;关键字段说明log_binbinlog日志是否打开状态log_bin_basename是binlog日志的基本文件名后面会追加标识来表示每一个文件binlog日志文件会滚动增加log_bin_index指定的是binlog文件的索引文件这个文件管理了所有的binlog文件的目录。sql_log_bin控制当前session的SQL是否写入binlog文件如果想在主库上执行一些操作但不复制到slave库上可以通过修改参数sql_log_bin来实现。比如说模拟主从同步复制异常。✅2. 三种日志格式格式记录方式优点缺点STATEMENT记录SQL语句日志量小IO开销低函数如UUID()在slave执行结果不一致ROW记录每行修改的数据解决函数、存储过程复制问题日志量大性能较差MIXED两者结合自动选择平衡性能与一致性— 面试要点推荐使用MIXED格式MySQL会根据SQL是否包含不确定性函数自动在STATEMENT和ROW之间选择。✅3. sync_binlog写入磁盘机制binlog写入磁盘机制主要通过 sync_binlog 参数控制默认值是 0。为0的时候表示每次提交事务都只 write 到page cache由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升但是机器宕机page cache里面的 binlog 会丢失。也可以设置为1表示每次提交事务都会执行 fsync 写入磁盘这种方式最安全。还有一种折中方式可以设置为N(N1)表示每次提交事务都write 到page cache但累积N个事务后才 fsync 写入磁盘这种如果机器宕机会丢失N个事务的binlog。注意发生以下任何事件时, binlog日志文件会重新生成服务器启动或重新启动服务器刷新日志执行命令flush logs日志文件大小达到 max_binlog_size 值默认值为 1GB✅4. binlog文件管理重新生成条件服务器重启执行flush logs文件大小达到max_binlog_size删除binlog文件-- 删除binlog文件reset master;-- 删除所有purgemaster logstomysql-binlog.000006;-- 删除指定文件之前purgemaster logs before2023-01-21 14:00:00;-- 删除指定日期前查看binlog内容可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容# 查看bin-log二进制文件命令行方式不用登录mysqlmysqlbinlog --no-defaults-v--base64-outputdecode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007# 查看bin-log二进制文件带查询条件mysqlbinlog --no-defaults-v--base64-outputdecode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime2023-01-21 00:00:00stop-datetime2023-02-01 00:00:00start-position5000stop-position20000查出来的binlog日志文件内容如下/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;DELIMITER/*!*/;# at 4#230127 21:13:51 server id 1 end_log_pos 123 CRC32 0x084f390f Start: binlog v 4, server v 5.7.25-log created 230127 21:13:51 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;# at 123#230127 21:13:51 server id 1 end_log_pos 154 CRC32 0x672ba207 Previous-GTIDs# [empty]# at 154#230127 21:22:48 server id 1 end_log_pos 219 CRC32 0x8349d010 Anonymous_GTID last_committed0 sequence_number1 rbr_onlyyes/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;SETSESSION.GTID_NEXTANONYMOUS/*!*/;# at 219#230127 21:22:48 server id 1 end_log_pos 291 CRC32 0xbf49de02 Query thread_id3 exec_time0 error_code0SETTIMESTAMP1674825768/*!*/;SETsession.pseudo_thread_id3/*!*/;SETsession.foreign_key_checks1,session.sql_auto_is_null0,session.unique_checks1,session.autocommit1/*!*/;SETsession.sql_mode1342177280/*!*/;SETsession.auto_increment_increment1,session.auto_increment_offset1/*!*/;/*!\C utf8 *//*!*/;SETsession.character_set_client33,session.collation_connection33,session.collation_server33/*!*/;SETsession.lc_time_names0/*!*/;SETsession.collation_databaseDEFAULT/*!*/;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: STMT_END_F### UPDATE test.account### WHERE### 11### 2lilei### 31000### SET### 11### 2lilei### 32000# at 413#230127 21:22:48 server id 1 end_log_pos 444 CRC32 0x23355595 Xid 10COMMIT/*!*/;# at 444。。。能看到里面有具体执行的修改伪sql语句以及执行时的相关情况。✅5. binlog数据恢复实战核心思路回放执行binlog中记录的SQL。用binlog日志文件恢复数据其实就是回放执行之前记录在binlog文件里的sql举一个数据恢复的例子-- 1. 刷新日志生成新的binlog文件flush logs;-- 2. 执行操作插入数据INSERTINTOtest.account(id,name,balance)VALUES(4,zhuge,666);INSERTINTOtest.account(id,name,balance)VALUES(5,zhuge1,888);-- 3. 误操作删除deletefromaccountwhereid3;恢复数据——找到插入SQL的BEGIN前面的at位置和COMMIT后面的at位置# 按位置恢复mysqlbinlog --no-defaults --start-position219--stop-position701--databasetest\D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009|mysql-uroot-p123456-vtest# 按时间恢复mysqlbinlog --no-defaults --start-datetime2023-1-27 23:32:24--stop-datetime2023-1-27 23:34:23\--databasetest D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009|mysql-uroot-p123456-vtest 重要要恢复大量数据需全量备份 binlog增量恢复组合使用。仅靠binlog不现实因为早期binlog会被定期删除。比如程序员经常说的删库跑路的话题假设我们把数据库所有数据都删除了要怎么恢复了如果数据库之前没有备份所有的binlog日志都在的话就从binlog第一个文件开始逐个恢复每个binlog文件里的数据这种一般不太可能因为binlog日志比较大早期的binlog文件会定期删除的所以一般不可能用binlog文件恢复整个数据库的。一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。备份数据库一般可以用mysqldump 命令工具# 备份mysqldump-uroot-p数据库名备份文件名;# 备份整个数据库mysqldump-uroot-p数据库名 表名字备份文件名;# 备份单个表# 恢复mysql-uroot-ptest备份文件名✅6. 为什么需要两份日志最开始 MySQL 并没有 InnoDB 引擎MySQL 自带的 MyISAM 引擎没有 crash-safe 能力binlog 只能用于归档。而InnoDB 是另一个公司以插件形式引入Mysql后为了 crash-safe 能力设计了 redo log。保证即使数据库发生异常重启之前提交的记录都不会丢失这个能力称为crash-safe redo log vs binlog 全量对比特性redo logbinlog所属层InnoDB引擎层Server层记录内容物理日志数据页修改逻辑日志SQL原始逻辑写入方式循环写空间固定追加写空间无限增长主要作用crash-safe数据恢复、主从复制适用引擎仅InnoDB所有存储引擎文件管理固定大小循环覆盖可配置自动删除策略四、undo log回滚日志 核心undo log记录数据修改前的状态用于事务回滚和MVCC。它保证了事务的原子性和一致性读。✅1. undo log管理机制InnoDB 采用**回滚段rollback segment**方式管理 undo log每个回滚段记录了 1024 个 undo log segment 每个事务只会使用一个undo log segment。在MySQL5.5的时候只有一个回滚段那么最大同时支持的事务数量为1024个。在MySQL 5.6开始InnoDB支持最大128个回滚段故其支持同时在线的事务限制提高到了 128*1024 。版本回滚段数最大并发事务数MySQL 5.511024MySQL 5.6128128 × 1024关键参数innodb_undo_directory设置undo log文件路径默认./innodb_undo_logs设置undo log文件内部回滚段的个数默认值为128。innodb_undo_tablespaces设置undo log文件的数量这样回滚段可以较为平均地分布在多个文件中。设置该参数后会在路径innodb_undo_directory看到undo为前缀的文件。✅2. undo log删除时机新增类型的在事务提交之后就可以清除掉了。修改类型的事务提交之后不能立即清除掉这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。✅3. 为什么不直接更新磁盘为什么Mysql不能直接更新磁盘上的数据而设置这么一套复杂的机制来执行SQL了因为来一个请求就直接对磁盘文件进行随机读写然后更新磁盘文件里的数据性能可能相当差。因为磁盘随机读写的性能是非常差的所以直接更新磁盘文件是不能让数据库抗住很高并发的。Mysql这套机制看起来复杂但它可以保证每个更新请求都是更新内存BufferPool然后顺序写日志文件同时还能保证各种异常情况下的数据一致性。更新内存的性能是极高的然后顺序写磁盘上的日志文件的性能也是非常高的要远高于随机读写磁盘文件。正是通过这套机制才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。五、其他日志✅1. 错误日志记录数据库启动/停止及运行过程中的严重错误时的相关信息。默认开启无法关闭。当数据库出现任何故障导致无法正常使用时建议首先查看此日志。# 查看错误日志存放位置showvariableslike%log_error%;✅2. 通用查询日志通用查询日志记录用户的所有操作包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等如select、show等无论SQL的语法正确还是错误、也无论SQL执行成功还是失败MySQL都会将其记录下来。默认关闭仅在调试时开启。通用查询日志用来还原操作时的具体场景可以帮助我们准确定位一些疑难问题比如重复支付等问题。general_log是否开启日志参数默认为OFF处于关闭状态因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启只在需要调试查询问题时开启。general_log_file通用查询日志记录的位置参数。showvariableslike%general_log%;SETGLOBALgeneral_logon;-- 开启通用查询日志 全文总结✅1. Server层五大组件连接器(权限) → 查询缓存(8.0已移除) → 分析器(词法语法) → 优化器(选索引/定顺序) → 执行器(调引擎接口)✅2. redo log三策略innodb_flush_log_at_trx_commit0(最快但不安全) / 1(默认最安全) / 2(折中)✅3. binlog三格式STATEMENT(日志小但函数不一致) / ROW(安全但日志大) / MIXED(推荐自动选择)✅4. 两份日志的分工redo log crash-safe引擎层循环写binlog 数据恢复主从复制Server层追加写✅5. undo log双重作用事务回滚 MVCC多版本并发控制✅6. binlog数据恢复mysqlbinlog --start-position/--start-datetime回放指定范围日志配合全量备份使用✅7. MySQL日志体系速记日志作用开启redo logcrash-safe默认binlog恢复/复制8.0默认undo log回滚/MVCC默认错误日志诊断故障默认通用查询日志审计排查按需