大厂计划 | MySQL 高频面试题04：主从复制、读写分离与分库分表

最新推荐文章于 2024-09-19 15:59:27 发布

Dyski丶

最新推荐文章于 2024-09-19 15:59:27 发布

阅读量1.2k

点赞数 1

分类专栏： MySQL 文章标签： mysql mysql优化

本文链接：https://blog.csdn.net/Dyski/article/details/119148429

版权

MySQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

谈谈你对主从（从服务器=备库，二者概念一样）复制的了解？

主备延迟、主备同步延迟是什么？怎么产生的？如何解决？

谈谈你对读写分离的了解？

谈谈你对分库分表中的分表切分的了解？

分库分表后的 ID 是怎么生成的？

谈谈你对主从（从服务器=备库，二者概念一样）复制的了解？

MySQL 的主从复制，就是将 MySQL 主数据库上的数据复制到从数据库中去。

主从复制的目的是为了实现数据库的读写分离：写操作和实时性较强的读操作则访问主数据库；读操作则访问从数据库。从而使数据库具有更强大的访问负载能力，支撑更多的用户访问。

它的原理是：当应用程序客户端发送一条更新命令到数据库的时候，数据库会把这条更新命令通过 Binlog 线程同步记录到 Binlog 中，然后通过网络通信的方式将它发送到从服务器，从服务器将其写到自己的中转日志 Relay log 中，然后由从服务器的 SQL 执行线程从 Relay log 中读取这条新的日志，并把它重新执行一遍。

这样当客户端应用程序执行一个update命令的时候，这个命令会在主数据库和从数据库上同步执行，从而实现了主数据库向从数据库的复制，让从数据库和主数据库保持一样的数据。

主从复制主要涉及三个线程：

主服务器的 binlog 线程：在主服务器中，负责将主服务器上的数据更改写入到 binlog 日志文件中。

从服务器的 I/O 线程：在从服务器中，负责从主服务器上读取 binlog 日志，并写入从服务器的 Relay log 中转日志中。

从服务器的 SQL 线程：在从服务器中，负责读取中转日志，解析出主服务器已经执行的数据更改并在从服务器中重放执行。

主备延迟、主备同步延迟是什么？怎么产生的？如何解决？

是什么

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值。

怎么产生的

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。造成的原因可能有：

备库所在机器的性能要比主库所在的机器性能差。

备库的压力太大：由于主库直接影响业务，大家使用起来可能会比较克制，但却忽视了备库的压力控制。结果备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟。

主库上执行了大事务。如：一次性地用 delete 语句删除太多的数据，就会造成大事务，应该分批的删除，不能一次性删除；再者可能就是使用了在大表的 DDL（修改表结构）导致了大事务。

如何解决

实际上主备延迟、主备同步延迟根本没有什么一招制敌的办法，因为所有的 SQL 都必须要在备库里面再执行一遍，但是主库如果不断的有更新操作写入，那么一旦有延迟产生，那么延迟加重的可能性就会原来越大。当然，我们还是可以做一些缓解的措施。

因为主库主要负责更新操作，它对安全性的要求比备库高，所以应将 sync_binlog，innodb_flush_log_at_trx_commit 设为双1模式；但备库则不太需要这么高的数据安全，可以暂时地将备库的 sync_binlog 和 innodb_flush_log_at_trx_commit 设置为非双1模式来提高 SQL 的执行效率。当然，当备库赶上来后还是得将其设置回双1模式的，保证安全性。

另一个解决措施就是增加从服务器，这个目的还是分散读的压力，从而降低服务器负载。

sync_binlog

sync_binlog = 1；表示每次事务提交后写到 binlog 日志文件到文件系统后就立即持久化到磁盘。磁盘 I/O 次数多，效率不高，但是安全，异常重启后可保证最多只有1个事务的数据丢失。

sync_binlog = 0；表示直到 binlog 日志文件在文件系统中写满后才持久化到磁盘。磁盘 I/O 次数少，效率高，但是不安全，如果异常重启，将会丢失多个事务的数据。

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit = 1；表示每次 redo log 从 redo log buffer 写到文件系统中，然后处于 prepare 阶段后就立即持久化到磁盘，而不会等到 commit 阶段经由每秒轮询刷到磁盘。磁盘 I/O 次数多，效率不高，但是安全。

innodb_flush_log_at_trx_commit = 2；表示每次 redo log 从 redo log buffer 写到文件系统中，然后处于 prepare 阶段后不会触发持久化磁盘的操作，而是到 commit 阶段经由每秒轮询刷到磁盘。磁盘 I/O 次数少，效率高，但是不安全，由于 redo log prepare 阶段后少了一次刷盘，异常重启后就算 binlog 写到磁盘了，那也无法恢复 redo log prepare 阶段之前 redo log 的数据，进而无法崩溃恢复。