十一，MySQL日志篇之undo-log、redo-log、bin-log

引言在我们开发任何的项目中都会存在日志而且MySQL更是其中的佼佼者日志种类繁多本篇文章的目的就是全解MySQL中的各类日志如撤销日志、错误日志、慢查询日志、中继日志、回滚日志......日志的作用不用所不管是线上排查、亦或是性能优化几乎都需要从日志中来获得信息作为依据而MySQL中很多功能也需要基于日志实现比如事务回滚、数据库持久化、数据恢复、数据迁移、MVCC机制....废话不多说直接开车undo-log撤销日志undo就是撤销的意思我们通常称他为回滚日志在日常开发过程中如果代码敲错了我们习惯按ctrlZ来撤销那么undo-log的作用也是如此但它是同来给MySQL撤销SQL操作的在SQL执行篇中聊过当一条写入类型的SQL执行时都会记录undo-log日志会生成相应的反SQL放入到undo-log日志中例如如果当前是insert插入操作则生成一个对应的delete操作如果当前是delete删除操作InnoDB中会修改隐藏字段deleted_bit1则生成改为0的语句如果当前是update修改操作比如将姓名从竹子改成了熊猫那就会生成一个从熊猫改回竹子的操作当事务中的某条SQL执行失败时MySQL就需要回滚事务中其他执行成功的SQL此时就会找到这个事务在undo-log中生成的反SQL然后将库中的数据改回事务发生前的样子前面说过一条写SQL在执行前回生成对应的反SQL记录在undo-log中但实际上并不会生成反SQL专门证明不会生成反SQL呢 如果大家有仔细研究过MySQL日志会发现undo-log并不存在单独的日志文件也就是磁盘中并不会存在xx-undo.log这类的文件哪undo-log存在哪里呢InnoDB存储引擎默认是将undo-log存储在xx.ibdata共享表数据文件中默认采用段的形式存储也就是当一个事务尝试写某行表数据时首先会将就数据拷贝到xx.ibdata文件中将表中行数据的隐藏字段roll_ptr回滚指针会指向xx.ibdata文件中的旧数据然后再写表上写表上的数据那undo-log究竟在xx.ibdata文件中这么存储呢在共享表数据文件中有一块区域名为Rollback Segment回滚段每个回滚段中有1024个undo-log Segment每个undo段可存储一条旧数据而执行写SQL时undo-log就是写入到这些段中的不过在MySQL5.5版本前默认只有一个Rollback Segment而在MySQL5.5版本后默认有128个回滚段即支持128*1024条undo记录同时存在对于事务回滚原理的纠正前面我们所讲述的事务回滚原理都是为了方便大家更好地理解并不是十分准确实际上当一个事务需要回滚时本质是不会以执行反SQL的模式还原数据而是直接将roll_ptr回滚指针指向的undo记录从xx.ibdata共享表数据文件中拷贝到xx.ibd表数据文件覆盖掉原本改动过的数据如下一条写SQL的执行流程如上图的序号表示当需要进行回滚事务时直接用undo旧记录覆盖表中修改过的新记录即可如果是insert操作因为要插入的这条数据是不存在的那么就不会产生undo记录此时回滚时如何删除这条记录呢因为插入操作不会产生undo旧记录因此隐藏字段中的roll_ptrnull因此直接用null覆盖插入的新记录即可这样就实现了删除数据的效果基于undo版本链实现的MVCC机制根据前面的学习我们知道undo-log中记录的旧数据不仅仅只有一条一条相同的行数据可能存在多条不同版本的undo记录内部会通过roll_ptr回滚指针组成一个单向链表而这个链表则被称之为undo版本链案例如下-- 事务T1trx_id1两次修改同一条数据 UPDATE zz_users SET user_name 竹子 ​ WHERE user_id 1; ​ UPDATE zz_users SET user_sex 男 WHERE user_id 1; ​undo-log日志中的旧数据版本链示意图大致如下对于如何利用版本链实现MVCC机制就不重复赘述了在MVCC机制的那篇文章中都讲过undo-log的内存缓冲区InnoDB存储引擎在MySQL启动时会在内存中构建一个BufferPool缓冲池这个缓冲池主要存放两类镀锡一类是数据相关的缓冲如索引、锁、表数据等另一类是各种日志的缓冲如undo、Bin、Redo....等日志而当一条写SQL执行时不会直接去往磁盘中的xx.ibdata文件写数据而是会写在undo_log_buffer缓冲区中因为工作线程直接去写磁盘太影响效率了写进缓冲区后会由后台线程去刷写磁盘如果当一个事务提交时undo的旧记录会不会立马被删除呢因为事务提交了不需要再回滚改动过的数据似乎用不上undo旧记录了确实是这样的但不会立马删除undo记录对于旧记录的删除工作InnoDB存储引擎中会有专门的purger线程负责purger线程内部会维护一个ReadView它会以此作为判断依据来决定何时移除undo记录那为什么不是事务提交之后立马删除undo记录呢因为可能会有其他事务再通过快照读undo版本链中的旧数据直接移除可能会导致其他事务读不到数据因此删除的工作就交给了purger线程undo-log相关的参数最后再来看看关于Undo-log的一些参数其实在MySQL5.5之前没有太多参数如下innodb_max_undo_log_size本地磁盘文件中Undo-log的最大值默认1GBinnodb_rollback_segments指定回滚段的数量默认为1个除开上述两个参数外其他参数基本上是在MySQL5.6才有的如下innodb_undo_directory指定Undo-log的存放目录默认放在.ibdata文件中innodb_undo_logs指定回滚段的数量默认为128个也就是之前的innodb_rollback_segmentsinnodb_undo_tablespaces指定Undo-log分成几个文件来存储必须开启innodb_undo_directory参数innodb_undo_log_truncate是否开启Undo-log的在线压缩功能即日志文件超过大小一半时自动压缩默认OFF关闭没错在MySQL5.5版本以后Undo-log日志支持单独存放并且多出了几个参数可以调整Undo-log的区域Redo-log重做日志详细聊完了undo-log日志之后我们再来看看它的好兄弟redo-log日志这两个日志都是InnoDB存储引擎独有的undo-log日志主要用于实现事务回滚和MVCC机制而redo-log则用来实现数据的恢复在MySQL事务篇中我们聊过数据恢复机制redo-log日志的作用就在于此为何需要Redo-log日志MySQL绝大部分存储引擎都是基于磁盘存储数据的但如果每次读写数据都走磁盘效率是十分低的因此InnoDB存储引擎在设计时当MySQL启动后就会在内存中创建一个BufferPool运行过程中会将大量操作汇集在内存中进行比如写入数据时先写到内存中然后由后台线程再刷写到磁盘上虽然使用了BufferPool提升了MySQL整体的读写性能但它是基于内存的也就是意味着随着机器的宕机、重启、其中保存的数据会消失那当一个事务向内存中写入数据后MySQL突然宕机了那么就会导致这条未刷盘的数据丢失所以为了解决这个问题Redo-log日志就出现了因为把数据写到内存有丢失风险明显违背了事务ACID原则中的持久性所以redo-log的出现就是为了解决该问题redo-log是一种预写式日志即在向内存写入数据前会先写日志当后续数据未被刷写到磁盘、MySQL崩溃时就可以通过日志来恢复数据确保所有提交的事务都会被持久化注意工作线程执行SQL前写的redo-log日志也是写在了内存中的redo_log_buffer缓冲区既然redo-log日志也是先写内存那redo-log有没有丢失的风险呢 这跟redo-log的刷盘策略有关Redo-log的刷盘策略对于内存中的redo_log_buffer缓冲区其中写入的数据会何时被刷写到磁盘对于这点在之前SQL执行篇-写SQL执行时的日志操作中简单的提到过简单来说就是刷盘的时机由innodb_flush_log_at_trx_commit参数来控制默认是处于第二个级别也就是每次提交事务时都会刷盘这也就意味着一个事务执行成功后相应的Redo-log日志绝对会被刷写到磁盘中因此无需担心会出现丢失风险。那如果事务还未提交时MySQL宕机怎么办对于这个问题在事务篇的那个截图中有不再反复赘述但再来思考一个问题既然Redo-log要写磁盘那为何不在写日志的时候直接把数据写到磁盘里面去呢Redo-log中为何“多此一举”先刷写一次Redo-log日志到磁盘后台线程再根据Redo-log日志把数据落盘这个动作似乎看起来有些多余对吧但实际上这样做好处很大①日志比数据先落入磁盘因此就算MySQL崩溃也可以通过日志恢复数据。②写日志时是以追加形式写到末尾而写数据时则是计算数据位置随机插入。对于第一点好处就不多说了重点来聊一聊第二点因为写日志的时候只需要将记录追加到日志文件的尾部即可这是按顺序写入但写入表数据时还需要先先计算数据的位置比如修改一条数据时需要先判断这条数据在磁盘文件中的那个位置找到了位置再写入这是随机写入顺序写入的速度会比随机写入快很多很多。因为写日志会比写数据落盘快因此日志落盘后返回比数据落盘后返回要快对于客户端而言响应时间会更短~Redo-log相关的参数这里也列举出几个Redo-log日志中较为重要的系统参数innodb_flush_log_at_trx_commit设置redo_log_buffer的刷盘策略默认每次提交事务都刷盘innodb_log_group_home_dir指定redo-log日志文件的保存路径默认为./innodb_log_buffer_size指定redo_log_buffer缓冲区的大小默认为16MBinnodb_log_files_in_group指定redo日志的磁盘文件个数默认为2innodb_log_file_size指定redo日志的每个磁盘文件的大小限制默认为48MB其中主要讲一下Redo-log的本地磁盘文件个数为啥默认是两个呢因为MySQL通过来回写这两个文件的形式记录Redo-log日志用两个日志文件组成一个“环形”如下先来简单解释一下图中存在的两根指针write pos这根指针用来表示当前Redo-log文件写到了哪个位置check point这根指针表示目前哪些Redo-log记录已经失效且可以被擦除覆盖两根指针中间区域也就是图中的红色区域代表是可以写入日志记录的可用空间而蓝色区域则表示日志落盘但数据还未落盘的记录这句话怎么理解呢当一个事务写了redo-log日志、并将数据写入缓冲区后但数据还未写到本地的表数据文件中此时这个事务对应的redo-log记录就为上图中的蓝色而当一个事务所写的数据也落盘后对应的redo-log记录就会变为红色。当write pos指针追上check point指针时红色区域就会消失也就代表Redo-log文件满了再当MySQL执行写操作时就会被阻塞因为无法再写入redo-log日志了所以会触发checkpoint刷盘机制将redo-log记录对应的事务数据全部刷写到磁盘中的表数据文件后阻塞的写事务才能继续执行触发checkpoint刷盘机制随着数据的落盘check point指针也会不断的向后移动红色区域也会不断增长因此阻塞的写事务才能继续执行。OK~再补齐一些关于checkpoint机制的系统参数innodb_log_write_ahead_size设置checkpoint刷盘机制每次落盘动作的大小默认为8K如果你要设置必须要为4k的整数倍这跟read-on-write问题有关innodb_log_compressed_pages是否对Redo日志开启页压缩机制默认ON这跟InnoDB的页压缩技术有关后续《特性篇》聊innodb_log_checksumsRedo日志完整性效验机制默认开启必须要开启否则有可能刷写数据时只刷一半出现类似于“网络粘包”的问题后续几个参数略微有些复杂因为主要跟MySQL5.6之后的优化有关后续在《MySQL特性篇》中会再次细聊\Bin-log变更日志Bin-log日志也被称之为二进制日志作用与Redo-log类似主要是记录所有对数据库表结构变更和表数据修改的操作对于select、show这类读操作并不会记录。bin-log是MySQL-Server级别的日志也就是所有引擎都能用的日志而redo-log、undo-log都是InnoDB引擎专享的无法跨引擎生效OK~再看到这张写SQL的执行流程图重点观察里面的第⑨步无论当前表使用的是什么引擎实际上都需要完成记录bin-log日志这步操作和之前分析的两种日志相同bin-log也由内存日志缓冲区本地磁盘文件两部分组成这也就意味着写bin-log日志时也会先写缓冲区然后由后台线程去刷盘bin-log的缓冲区为啥要单独把bin-log的缓冲区拎出来讲呢因为它跟redo-log、undo-log的缓冲区并不同前面分析的两种日志缓冲区都位于InnoDB创建的共享BufferPool中而bin_log_buffer是位于每条线程中的关系图如下也就是说MySQL-Server会给每一条工作线程都分配一个bin_log_buffer而并不是放在共享缓冲区中这是为啥呢因为MySQL设计时要兼容所有引擎直接将bin-log的缓冲区设计在线程的工作内存中这样就能够让所有引擎通用并且不同线程/事务之间由于写的都是自己工作内存中的bin-log缓冲因此并发执行时也不会冲突bin_log_buffer的设计就类似于咱们之前讲《并发编程》时讲过的《ThreadLocal线程变量副本》。OK~简单理解bin-log缓冲区的设计后对于bin-log的刷盘策略就不反复赘述了就是通过sync_binlog参数控制与之前redo-log类似上面有Bin-log本地日志文件的格式bin-log的本地日志文件采用的是追加写的模式也就是一直向文件末尾写入新的日志记录当一个日志文件写满后会创建一个新的bin-log日志文件每个日志文件的命名为mysql-bin.000001、mysql-bin.000002、mysql-bin.00000x....可以通过show binary logs;命令查看已有的bin-log日志文件接着再来聊聊bin-log文件的内部格式在bin-log的本地文件中其中存储的日志记录共有Statment、Row、Mixed三种格式分别是啥意思呢Statment每一条会对数据库产生变更的SQL语句都会记录到bin-log中咋理解这句话呢举个例子sql 体验AI代码助手 代码解读复制代码-- 查询一次用户表数据如下 SELECT * FROM zz_users; ------------------------------------------------------------- | user_id | user_name | user_sex | password | register_time | ------------------------------------------------------------- | 1 | 熊猫 | 女 | 6666 | 2022-08-14 15:22:01 | | 2 | 竹子 | 男 | 1234 | 2022-09-14 16:17:44 | | 3 | 子竹 | 男 | 4321 | 2022-09-16 07:42:21 | | 4 | 猫熊 | 女 | 8888 | 2022-09-27 17:22:59 | | 9 | 黑竹 | 男 | 9999 | 2022-09-28 22:31:44 | ------------------------------------------------------------- ​ -- 将用户表中所有 ID3的密码重置 update zz_users set password 1111 where user_id 3;比如上述这个事务执行时MySQL会将第二条update语句记录在bin-log日志中但对于select语句则不会记录在记录SQL时还会记录一下SQL的上下文信息如执行时间、事务ID、日志量......这种方式的优势很明显由于只记录对数据库产生变更操作的SQL所以不会产生太大的日志量节约空间恢复数据时因为数据量小所以磁盘IO次数少因此性能会比较不错。同时做主备等高可用架构时数据同步也会较小因此比较节省带宽但虽然优势不小但缺页很明显即恢复数据、主从同步数据时有时会出现数据不一致的情况如SQL中使用了sysdate()、now()这类函数比如举个简单的例子sql ​ 体验AI代码助手 代码解读 复制代码insert into zz_users values(11,棕熊,男,3333,sysdate());比如这条插入语句由于对用户表产生了变更操作所以会被记录到bin-log中但当主从架构之间做数据同步时假设将这条SQL同步到从机上执行此时问题就来了sysdate()函数会获取机器的当前时间但主机和从机执行这条SQL显然不是同一时间因此就会导致ID11的这条数据在主机和从机的用户表中注册时间会出现不一致Row这种模式就是为了解决Statment模式的缺陷Row模式中不再记录每条造成变更的SQL语句而是记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了这又怎么理解呢还是以前面的重置密码的例子来说sql 体验AI代码助手 代码解读复制代码-- 将用户表中所有 ID3的密码重置ID4、9的两条数据会被重置 update zz_users set password 1111 where user_id 3;在这种模式下就不会记录这条update语句而是记录发生改变的行数据即ID4、9的两条用户数据会将其更改后的值记录到bin-log日志中这种方式因为不记录SQL而是记录修改后的值因此有个很大的好处是当主从同步数据时复制的是主机上的数据因此不会出现主从数据不一致的情况。但缺陷同样很明显比如表中有800W数据现在我对ID600W的所有数据进行了修改操作哪也就意味着会有600W条记录写入bin-log日志这个数据量可想而知其磁盘IO、网络带宽开销会很高Mixed这种被称为混合模式即Statment、Row的结合版因为Statment模式会导致数据出现不一致而Row模式数据量又会很大因此Mixed模式结合了两者的优劣势对于可以复制的SQL采用Statment模式记录对于无法复制的SQL采用Row记录这样即保留了Statment模式的数据量小又具备Row模式的数据精准性鱼和熊掌兼得焉其实看到这里如果比较熟悉Redis4.x版本的小伙伴应该会有种熟悉感Redis的RDB、AOF持久化模式正好对应MySQL的Statment、Row模式而Redis4.0引入了混合持久化机制MySQL5.1版本也引入了混合日志模式为什么有了Bin-log还需要Redo-logRedo-log、Bin-log都是记录更新数据库的操作但为什么会同时设计两个呢这其实跟InnoDB有关如若对MySQL旧史较为熟悉的小伙伴应该知道MySQL自己的官方引擎实际上最初是MyISAMInnoDB是Innobase-Oy公司开发的一款可拔插式引擎由于InnoDB被MySQL支持后使用频率越来越高后面MySQL官方才用InnoDB替换了MyISAM作为默认引擎那这跟上面的问题有啥关系呢其实关系很大MySQL-Server、MyISAM是出自于官方的产品因此MyISAM中并未设计记录变更操作的日志记录变更操作由MySQL-Server来通过Bin-log完成但因为MyISAM不支持事务所以MySQL-Server设计的Bin-log无法用于灾难恢复因此InnoDB在设计时又重新设计出Redo-log日志可以利用该日志实现crash-safe灾难恢复能力确保任何事务提交后数据都不会丢失Redo-log、Bin-log两者的区别对于Redo-log、Bin-log两者的区别主要可以从四个维度上来说①生效范围不同Redo-log是InnoDB专享的Bin-log是所有引擎通用的②写入方式不同Redo-log是用两个文件循环写而Bin-log是不断创建新文件追加写③文件格式不同Redo-log中记录的都是变更后的数据而Bin-log会记录变更SQL语句④使用场景不同Redo-log主要实现故障情况的数据恢复Bin-log则用于数据灾备、同步不小心删库后应该跑路吗首先来说一下为啥要讨论这个问题呢这是由于之前MySQL架构篇的评论区的一位小伙伴提出的这里有两个问题①删库后跑路会不会被人发现②MySQL能不能和Oracle一样具备闪回功能先来简单聊聊第一个问题如果你在线上真的删库了哪就先别想着跑路你跑不掉因为bin-log日志中会记录执行SQL的连接会话信息同时一般规模较大的企业都会搭建完善的监控系统会监控服务的网络连接因此当你删库后可以顺着bin-log → session → network-connection这条线确定执行删库SQL的IP如果你还未断开连接直接通过MySQL的命令就能定位到删库的IP因此基本上删库了是可以定位到责任人的当然如果项目配备的监控系统不够完善同时你的连接已经断开并且电脑换了一个局域网同时时间来到了三天以后如果还没人发现你哪基本上跑路也不会有人发现但这样干会存在些许做贼心虚的嫌疑OK~不过多的讨论这个话题了总之你跑路肯定不能跑误删了数据就要想办法恢复咋恢复呢通过日志恢复但Redo-log、Bin-log都会记录数据库的变更操作因此用谁比较合适呢答案是Bin-log因为Redo-log采用循环写的方式一边写会一边擦里面无法得到完整的数据而Bin-log是追加写的模式你不去主动删除磁盘的日志文件并且磁盘的空间还足够一般Bin-log日志文件都会在本地因此当你删库后可以直接去本地找Bin-log的日志文件然后拷贝出来一份再打开最后一个文件把里面删库的记录手动移除再利用mysqlbinlog工具导出xx.SQL文件最后执行该SQL文件即可恢复删库前的数据。这里就叙说大体逻辑具体的数据恢复操作会在后续的《MySQL线上排查与数据恢复篇》细讲其实也可以通过Flashback工具提供的闪回功能恢复数据但以后再细聊bin-log相关的参数log_bin是否开启bin-log日志默认ON开启表示会记录变更DB的操作log_bin_basename设置bin-log日志的存储目录和文件名前缀默认为./bin.0000xlog_bin_index设置bin-log索引文件的存储位置因为本地有多个日志文件需要用索引来确定目前该操作的日志文件binlog_format指定bin-log日志记录的存储方式可选Statment、Row、Mixedmax_binlog_size设置bin-log本地单个文件的最大限制最多只能调整到1GBbinlog_cache_size设置为每条线程的工作内存分配多大的bin-log缓冲区sync_binlog控制bin-log日志的刷盘频率binlog_do_db设置后只会收集指定库的bin-log日志默认所有库都会记录......省略其他不常用参数Redo-log的两阶段交详细大家应该听说过MySQL事务两阶段提交方案啥叫做事务两阶段提交呢实则是指Redo-log分两次写入如下注意看之前给出的写SQL执行流程图其中第⑤、⑩步分别会写两次Redo-log日志这个日志的作用前面讲的很明白了主要用来做崩溃恢复但为什么要分两次写呢写一次不行嘛其实想要弄明白这个问题要结合bin-log日志一起来聊。如果只写一次的话那到底先写bin-log还是redo-log呢先写bin-log再写redo-log当事务提交后先写bin-log成功结果在写redo-log时断电宕机了再重启后由于redo-log中没有该事务的日志记录因此不会恢复该事务提交的数据。但要注意主从架构中同步数据是使用bin-log来实现的而宕机前bin-log写入成功了就代表这个事务提交的数据会被同步到从机也就意味着从机会比主机多出一条数据。先写redo-log再写bin-log当事务提交后先写redo-log成功但在写bin-log时宕机了主节点重启后会根据redo-log恢复数据但从机依旧是依赖bin-log来同步数据的因此从机无法将这个事务提交的数据同步过去毕竟bin-log中没有撒最终从机会比主机少一条数据。经过上述分析后可得知如果redo-log只写一次那不管谁先写都有可能造成主从同步数据时的不一致问题出现为了解决该问题redo-log就被设计成了两阶段提交模式设置成两阶段提交后整个执行过程有三处崩溃点redo-log(prepare)在写入准备状态的redo记录时宕机事务还未提交不会影响一致性。bin-log在写bin记录时崩溃重启后会根据redo记录中的事务ID回滚前面已写入的数据。redo-log(commit)在bin-log写入成功后写redo(commit)记录时崩溃因为bin-log中已经写入成功了所以从机也可以同步数据因此重启时直接再次提交事务写入一条redo(commit)记录即可。通过这种两阶段提交的方案就能够确保redo-log、bin-log两者的日志数据是相同的bin-log中有的主机再恢复如果bin-log没有则直接回滚主机上写入的数据确保整个数据库系统的数据一致性OK~,最后再简单补充一点为什么bin-log又被叫做二进制日志呢因为记录日志时MySQL写入的是二进制数据而并非字符数据也就意味着直接用cat/vim这类工具是无法打开的必须要通过MySQL提供的mysqlbinlog工具解析查看Error-log错误日志前面已经将最重要的undo-log、redo-log、bin-log三大日志讲明白了这三个日志都是用来辅助MySQL、InnoDB在线上正常运行的但凡其中一个出现问题都有可能导致MySQL无法正常工作。接下来再看几个辅助性的日志即error-log、slow-log、relay-log。error-logMySQL线上MySQL由于非外在因素断电、硬件损坏...导致崩溃时辅助线上排错的日志。slow-log系统响应缓慢时用于定位问题SQL的日志其中记录了查询时间较长的SQL。relay-log搭建MySQL高可用热备架构时用于同步数据的辅助日志。接下来先看error-log这个日志的作用很明显从名字都能得知它是用于记录MySQL报错信息的其中涵盖了MySQL-Server的启动、停止运行的时间以及报错的诊断信息也包括了错误、警告和提示等多个级别的日志详情。通过错误日志一方面可以用来监控MySQL的运行状态便于预防故障、发现故障同时也可以在出现问题时用来辅助排查问题、修复故障因为MySQL-Server的错误日志是默认开启的并且无法手动关闭一般来说error-log日志文件默认是在MySQL安装目录下的data文件夹中但如果你想要改变位置哪也可以通过log-error这个参数来手动指定保存的位置与文件名。如果你不清楚错误日志的位置也可以通过SHOW VARIABLES LIKE log_error;命令来查看。最后稍微提一嘴如何根据错误日志来排错问题呢实际上非常简单在MySQL故障的情况下打开error-log文件然后搜索Error、Waiting级别的日志记录然后参考诊断信息即可Slow-log慢查询日志对于线上响应缓慢的问题一步步的排查过程之后还未找到问题最终就会来到数据库尝试对SQL或索引调优但一个项目中存在成千上万条SQL到底是由于哪条SQL造成的响应缓慢如果一条条去分析其工作量定然非常吃力为了排查问题时足够轻松MySQL官方支持开启慢查询日志。慢查询日志是什么呢也就是当一条SQL执行的时间超过规定的阈值后那么这些耗时的SQL就会被记录在慢查询日志中当线下出现响应缓慢的问题时可以直接通过查看慢查询日志定位问题定位到产生问题的SQL后再用explain这类工具去生成SQL的执行计划然后根据生成的执行计划来判断为什么耗时长是由于没走索引还是索引失效等情况导致的。不过对于慢查询SQL的监控MySQL默认是关闭的也就是说MySQL默认不会记录慢查询日志因为为了后续线上问题好排查项目上线前一定要记得开启slow_query_log设置是否开启慢查询日志默认OFF关闭。slow_query_log_file指定慢查询日志的存储目录及文件名。可以通过这两个参数来开启慢查询日志如果不设置存储目录默认放在MySQL的具体库的目录下。当开启慢查询日志的监控后可以通过设置long_query_time参数来指定查询SQL的阈值sql ​ 体验AI代码助手 代码解读 复制代码set global long_query_time 1;其默认单位是秒因此如果要指定更细粒度的时间可以通过0.01这种形式设置0.01表示10ms。当然该参数也可不设置不指定阈值的情况下默认为10s即执行时间超过10s的查询SQL才会记录到慢查询日志中。对于阈值的设置并不是随咱们率性而为这个参数一定要设置合理因为该参数的大小会直接影响MySQL的性能比如设置一个0.2s但如果大量业务SQL执行时都会超出该时长那最终会导致MySQL十分频繁的往慢查询日志中写数据。要记住慢查询日志在内存中是没有缓冲区的也就意味着每次记录慢查询SQL都必须触发磁盘IO来完成因此阈值设的太小容易使得MySQL性能下降如果设的太大又会导致无法检测到问题SQL因此该值一定要设置一个合理值。问题来了这个值设成多大合理呢可以先开启general log观察后实际的业务情况后再决定General-log查询日志general log即查询日志MySQL会向其中写入所有收到的查询命令如select、show等同时要注意无论SQL的语法正确还是错误、也无论SQL执行成功还是失败MySQL都会将其记录下来。对于该日志可以通过下述参数开启general_log是否开启查询日志默认OFF关闭。general_log_file指定查询日志的存储路径和文件名默认在库的目录下主机名.log。项目测试阶段可以先开启查询日志然后压测所有业务紧接着再分析日志中SQL的平均耗时再根据正常的SQL执行时间设置一个偏大的慢查询阈值即可这是个笨办法如果项目规模较大直接设置一个大概值然后上灰度发布走正式的运营场景效果会更佳。当然压测阶段结束后项目正式上线前一定要记得关闭普通查询日志Relay-log中继日志relay log在单库中是见不到的该类型的日志仅存在主从架构中的从机上主从架构中的从机其数据基本上都是复制主机bin-log日志同步过来的而从主机复制过来的bin-log数据放在哪儿呢也就是放在relay-log日志中中继日志的作用就跟它的名字一样仅仅只是作为主从同步数据的“中转站”。当主机的增量数据被复制到中继日志后从机的线程会不断从relay-log日志中读取数据并更新自身的数据relay-log的结构和bin-log一模一样同样存在一个xx-relaybin.index索引文件以及多个xx-relaybin.00001、xx-relaybin.00002....数据文件。对于这个日志的具体参数、工作过程放在后续的《MySQL高可用-主从读写分离篇》阐述。日志篇总结叨叨絮絮下来就大致将MySQL中的一些常见、较为重要的日志讲明白啦其实重点搞清楚undo-log、redo-log、bin-log即可其他的会在后续篇章中再次提到最后稍微总结一下这三个比较核心的日志undo-log主要用于实现事务ACID原则中的原子性和MVCC机制。redo-log主要用于实现事务原则中的持久性确保事务提交后就不会丢失。bin-log主要结合redo-log实现事务原则中的一致性确保事务提交前后数据的一致。对于其他几类日志在本篇中也仅讲明了大概毕竟后面的章节中会再出现而对于上述三大日志也就基本上不会提到了因此剖析的较为全面那么咱们下篇见~下面准备讲《MySQL内存篇》其实也主要是讲InnoDB Buffer Pool缓冲区至于为什么半道出家的InnoDB能替换掉官方的MyIASM引擎最大原因也在于此。