23 | MySQL性能提升（针对秒杀、大促时的优化）

一、前言

本篇文章主要讲解了三个参数来提升性能 [真实有效，童叟无欺] ，即：MySQL 写入 binlog 和 redo log 的流程。

 

二、正文开始

🏁：1.sync_binlog参数 [上来先展示参数镇镇场子]

此参数控制binlog的写入机制，binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache（1.1讲解了），事务提交的时候，再把 binlog cache 写到 binlog 文件中（1.2讲解了），最后fsync到磁盘中。

 

⚠️：1.1.一个事务的binlog不能被拆开 [重要事情讲一遍]，所以这里就引发了一个问题，binlog_cash_size就是系统控制给每个线程分配内存的，超过这个大小就要存到磁盘了。

 

⚠️：1.2.事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

图解：

write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。

fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

 

⭐️：1.3.大招

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；

sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

 

🏁：2.innodb_flush_log_at_trx_commit参数 [稳住场子]

此参数控制redo log的写入机制，事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的，然后再写到磁盘FS Page Cache，最后持久化到磁盘。

图解：

⚠️：2.1.buffer - page cache - disk 三者同步关系是什么

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ; 【如果事务执行期间 MySQL 发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失，但是风险太大，重启丢数据啊亲！！。】

设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；【redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的】

设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

 

❓：2.2.如果参数不是1的话，那MySQL有其他的手段从buffer中同步到page cache和磁盘？

⭐️：InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

❓：2.2.知道了同步原理，那么没有提交的事务是否有可能被持久化到磁盘中呢？

可能，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。

 

⭐️：2.3.什么情况下会让一个没有提交的事务的redo log写入到磁盘？

• 2.2中的后台线程。

• redo log buffer占用空间达到innodb_log_buffer的一半时，会write到cache中但是没有fsync。

• 当参数值为1时产生的连坐现象，即：原因为多个事务公用一个redo log。

 

⭐️：2.4.重大说明！！！！！！！装逼神器！！！！！！一泻千里！！！！（很重要的意思）

MySQL的redo log 再两阶段过程中，由于prepare阶段会持久化一次到磁盘加上每秒的后台轮训刷盘，Innodb再redo log的commit阶段就不需要fsync了，只用write到page cache就ok了。

这里说明了，redo log 状态改为commit的时候不会进行fsync，因为只要binlog 写磁盘成功，就算redo log 的状态还是prepare也没有关系 会被认为事务已经执行成功，所以只需要write 到page cache就ok了，没必要再浪费io主动去进行一次fsync。

 

⭐️：2.5.双1配置需要几次刷盘？

两次。redo log 的 prepare阶段一次；binlog刷盘一次。但是，答案真的是这样么？显然不是两次，要不也不会在笔记中加这一句话逼逼了。

 

🏁：3.组提交。 [坚持住，最后一个点]

这里需要说明得是什么是组提交，就是把多次redo log和binlog的fsync操作通过一次提交解决磁盘的IOPS性能问题

PS：这里的“多次”是redo log可能有多次写buffer操作，binlog可能有多次write操作。

 

⭐️：3.1.组提交内部是怎么把多次操作变成一次的？

个人总结版，多个并发事务在prepare阶段（比如：3个事务），都写完 redo log buffer，持久化到磁盘的过程都具有不同的LSN编号（比如：50，160，200），第一个被到达的事务会被标记为leader在可以写盘的时候 将 本组内最大的LSN编号写入到磁盘中（即：200），后面的事务再写盘时就可以直接返回了。（思考：第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。那么MySQL怎么让这个效果达到最好呢？）

日志逻辑序列号（log sequence number，LSN）的概念。LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。

 

⭐️：3.2.MySQL如何让组提交发挥最大优势的？

• 两阶段提交中把写binlog这个流程拆解为两步，交叉执行来拖时间。优点：binlog也可以使用使用组提交，但是由于步骤三执行的时间可能较快会导致binlog的组提交不明显。

  PS：这里可能会有为什么不说步骤二的执行时间也可能过快呢？我觉得也有可能，这里应该是个为了引出binlog组提交参数而说明的。

        

• 设置 binlog_group_commit_sync_delay，表示延迟多少微秒后才调用 fsync;

  binlog_group_commit_sync_no_delay_count，表示累积多少次以后才调用 fsync。 来实现提升 binlog 组提交的效果。

  ⚠️：这两个是或的关系，一个满足就会调用fsync

 

三、问答时间

🏁：4.为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

回答：MySQL 这么设计的主要原因是，binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中。redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

通俗解释就是redo log 只有在crash recovery用到

🏁：5.极端情况下，整个事务都提交成功了，redo log commit 完成了，备库也收到 binlog 并执行了。但是主库和客户端网络断开了，导致事务成功的包返回不回去，这时候客户端也会收到“网络断开”的异常。这种也只能算是事务成功的，不能认为是 bug。

🏁：6.当有多个单个索引时要考虑优化器选择索引合并算法的现象，所以要仔细分析是否可以变为联合索引。

🏁：7.可以问下公司是否在数据库连接层有专门管理请求和数据库服务资源的模块，不然直接面对客户端风险不可控。

四、课后评论

🏁：7.如果 sync_binlog = N ；binlog_group_commit_sync_no_delay_count = M；binlog_group_commit_sync_delay = 很大值
这种情况fsync什么时候发生呀，min(N,M)吗？

答：达到N次以后，可以刷盘了，然后再进入(sync_delay和no_delay_count)这个逻辑；Sync_delay如果很大，就达到no_delay_count才刷；

🏁：8.为什么binlog 是不能“被打断的”的呢？主要出于什么考虑？

一个线程只能同时有一个事务在执行。由于这个设定，所以每当执行一个begin/start transaction的时候，就会默认提交上一个事务；
这样如果一个事务的binlog被拆开的时候，在备库执行就会被当做多个事务分段自行，这样破坏了原子性，是有问题的。