web 前端培训 MySQL 面试题 binlog 日志的作用

​

关于 MySQL 的学习，市场上经常有什么千万级优化，亿级别的优化什么的，我暂且不评论好坏如何，我想要说的，千里之行，始于脚下，打好基础是学习任何知识的前提，MySQL 的内容不止是有增删改查，还有很多其他的增加的时候，触发什么锁操作，查的时候，如何优化，集群如何实现，如何实现高并发等，所有的一切都是需要把基础原理给搞清楚的，搞不清楚原理，你对知识的理解只是停留在表面，没有深入研究就容易忘记掉，死记硬背是不行的，在实际的项目开发中，你会遇到很多关于 MySQL 的瓶颈，今天，我们聊一个知识点，那就是 MySQL 的 binlog 日志。

1：什么是 binlog 日志

MySQL 中有好几种日志，binlog 属于最重要的日志了，主要用来记录 DDL(数据库定义语言)和 DML(数据库操纵)语句，以事件形式记录，包含了语句所执行的时间，并且是事务安全型的。

简单思考一下，MySQL 主要用来存储数据，表数据存储在磁盘上，并且高可用架构中，经常使用主从模式，任何一个系统，都需要进行稳定性保证和数据安全保证，那么 MySQL 系统中使用什么样的方式来保证呢，很简单的， 主要是使用日志来保存的。前端培训任何对数据表的操作包括 create，alter，drop，update，insert，delete 这些语句都会以日志的形式保存下来。

既然我们知道了 binlog 日志，是用来保存数据库操作语句的，那么我们就可以引申出 binlog 日志的两个主要作用 主从复制 和 数据恢复，主从复制是比较常见的高可用架构模型，master 把它的二进制日志传递给 slaves 数据库，来达到数据一致的目的，关于 MySQL 的主从复制我们后面还会比较详细的讲解，数据恢复主要通过 mysqlbinlog 日志工具来恢复数据。

2：如何操作 binlog 日志

// 查看日志开启状态

show variables like 'log_%';

// 只查看第一个 binlog 文件的内容

show binlog events;

// 查看所有的 binlog

show master logs;

// 查看当前正在写入的 binlog

show master status;

// 查看指定 binlog 文件内容

show binlog events in 'mysql_bin_000025';

3：binlog 的写入机制

binlog 写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中，一个事务的 binlog 是不能被拆开的，因此无论这个事务多大，都要确保一次性写入。因此这里就涉及到一个优化的问题，在实际的项目开发中，要减少大事务的出现，因为实际项目中，都是主从复制，大事务频繁出现，容易导致主从延迟，并且需要一次性写入大量的 binlog 日志，导致性能下降。

系统给 binlog cache 分配了一片内存，每个线程都有一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小，如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事物写到 binlog 中，并且清空 binlog cache。每个线程都有自己的 binlog cache，但是他们共用同一份 binlog 文件_web前端培训。

// 查看 binlog 日志相关的配置

show global variables like '%binlog%';

这里重点说一下 sync_binlog 配置项，这个配置项取值可以是 0，1，N

值为 0：表示每次提交事务都只 write，不 fsync 同步到磁盘

值为 1：表示每次提交事物都会执行 fsync

值为 N：表示每次提交事物都 write，但累积 N 个事务后才 fsync

在实际的项目开发中，一般设置 sync_binlog=1，每次提交事物都将数据持久化到磁盘，fsync 的过程是一个消耗性能的过程，因此这里有一个优化点：出现 IO 瓶颈的场景里，可以将 fsync_binlog 设置成一个比较大的值，可以提高性能。

4：binlog 日志主从复制

MySQL 的主从复制，恐怕是面试中经常会遇到的问题了，是出题率比较高的一项考察点，在实际的项目开发中，很多公司也都是采用主从复制架构来实现的。

面试题：1：主从复制是如何实现的？2：有没有遇到主从延迟的情况，如何解决的？

主从复制如何实现？

主从延迟是通过 slave 数据复制 master 数据库的 binlog 日志来实现的，这里面涉及到三个 MySQL 线程

首先，master 数据库将数据的改变记录到二进制 binlog 日志中

slave 数据库会监控探测 master 二进制日志是否发生改变，如果改变，则开始一个 I/O 线程请求 master 二进制事件

同时 master 节点为每个 I/O 线程启动一个 dump 线程，用于向其发送二进制事件，并且将 binlog 日志保存到中继日志中，从节点会启动 SQL 线程读取二进制日志，在本地重新执行，保持和主节点的数据一致，最后 I/O 和 SQL 线程进入到睡眠状态，等待下一次被唤醒。

主从延迟如何避免？

主从延迟的出现是不可避免的，当主数据库数据量太大的时候，会导致线程阻塞，出现延迟，业务上就会出现较大的影响，根据业务场景，实时性要求高的和实时性要求不高的场景。

解决方案如下：

1：业务上对数据的写入，批量写入增加时间间隔，例如写入 500 条数据后，sleep(1)时间

2：业务上出现短暂的延迟，可以通过前端增加倒计时功能来视觉上避免

3：业务上，如果请求量不是很大的情况，可以在短时间内请求主数据库

4：MySQL 采用分库架构，服务平行扩展，分散压力

5：MySQL 提高数据库版本，使用多线程复制 binlog 日志

6：提高服务器硬件设备，选中更多核更多内存的服务器

7：增加缓存层，在 MySQL 主数据更新的时候，将更新写入到 cache 中，读数据的时候先读区缓存层数据，过期之后再读取从数据库数据。

文章来源于 mamba 架构算法

​