在读多写少的情况下, 如何优化 MySQL 的数据查询方案！

面试官问：假设你负责的某业务在双十一期间要搞运营活动，公司投入了大量的营销费用进行推广，此举会带来巨大的流量，如果你作为架构师或者技术负责人，要如何设计方案来优化 MySQL，从而支持这种突发流量的读多写少的场景？

​

1、旁白：首先，我们要知道，对于大部分 OLTP 系统来说，读流量都是远远高于写流量的，可能达到几个数量级的差距，例如微信朋友圈，大部分人都是看的多，但可能一个月都发不了一条消息，同理，你上京东，更多的情况下是搜索并浏览商品，真正下单的比例会低很多。

其次，使用 Redis 作为 MySQL 的前置缓存，是可以帮助 MySQL 拦截大部分查询请求的，但这种方案适用于与用户账号关联不大的系统，例如搜索系统、商品系统等，这类系统中，每个用户看到的内容是一样的，Redis 缓存命中率非常高。因此实际到达 MySQL 的流量就很低了。但对于像订单系统、账户系统等跟用户账号相关的系统，缓存的命中率就没那么高了，毕竟我们不可能把所有用户的这些信息都缓存到 Redis 中。

因此，这种场景下，到达 MySQL 流量就很高了。

候选人：在正常的流量情况下，我们 MySQL 的架构是主从部署模式，读写都是在主库，从库只是作为数据备份。为了应对读多写少的场景，读写分离是首选的方案。对于写操作，数据还是写到主库中，对于读操作，就不再直接从主库中读取数据了，改成读从库，而且，为了支持大流量的读操作，需要部署多个从节点，组成一个集群（从节点之间的数据是一样的，是数据复制不是分片），实现一主多从的部署模式。如下图所示：

面试官：好的，那在这种模式下，当你的系统往这个 MySQL 集群提交了一个事务，MySQL 会执行哪些操作呢？

​

2、旁白：这个问题的本质是面试官想了解你对 MySQL 集群主从复制原理的掌握情况。MySQL 的主从复制的核心是 binlog，MySQL 的数据变化会以二进制的形式保存到磁盘文件 binlog 中，主从复制的过程就是将 binlog 从主库传输到从库的过程。通常情况下是异步的，但也支持同步方式和半同步方式。

候选人：这个过程可以分成三个阶段：

binlog 的写入：MySQL 主库在收到客户端提交事务的请求后，会先把相关操作写入 binlog，然后再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端操作成功的响应；

binlog 的同步：MySQL 从库会创建一个专门的 I/O 线程，它会连接主库的 log dump 线程，从主库接收 binlog 日志文件，然后把 binlog 信息写入本地的 relay log 中继日志文件中，最后返回复制成功的响应给主库；

binlog 的回放：同时，从库会创建一个专门回放 binlog 的线程，它会读取 relay log 中继日志，然后回放 binlog 从而更新存储引擎中的数据，最终实现主从数据的复制和一致性。

面试官：好的，前面你提到 MySQL 是一主多从的部署模式，那当活动期间系统流量很大时，是不是可以通过多增加几台从库的方式就能扛住这种并发的读请求呢？

候选人：不是这样的，从前面我们聊到的主从复制原理可以知道，随着从库数量的增加，连接到主库的从库 I/O 线程也会随之增多，这意味着主库也要创建同样数量的 log dump 线程来处理复制请求，这会对主库造成比较高的资源消耗，同时也会受制于主库的网络带宽。因此，在线上场景中，一个主库通常跟 2～3 个从库。

​

3、面试官：好的，那 binlog 从主库复制到从库的过程，是同步的还是异步的？会不会造成数据的丢失？

候选人：默认是异步方式，但也支持同步方式和半同步方式：

•异步方式：MySQL 提交事务的线程完全不关心 binlog 是否已经同步到从库，事务执行完成就会返回客户端响应结果，这种模式如果主库宕机，数据存在丢失的风险；•同步方式：MySQL 提交事务的线程会等待所有从库 binlog 同步成功的响应，这种情况不存在丢数据的情况；•半同步方式：MySQL 5.7 版本之后增加的功能，MySQL 提交事务的线程不会等待所有从库 binlog 同步成功的响应，只要有一部分从库同步成功，它就会返回客户端响应结果，同样的，这种方式也不存在丢数据的风险，但性能又比同步方式高。

面试官：好的，由于主从复制存在时间差，而读写分离的方案只会写主库，读从库，那就存在客户端已经将数据写到主库了，但由于主从复制存在延迟的问题，客户端读取不到这条数据，那这种情况有什么解决方案吗？

候选人：这个需要结合具体的业务场景进行分析，主要从架构设计上去做权衡，目前我想到的比较通用的有两种方案：

数据放到缓存中：在数据写入主库的同时，也写到缓存（例如 Redis）中，而读取数据时，优先从缓存中读取。不过这种方案存在缓存和数据库一致性的问题，不过这个属于另外一个范畴了，也存在解决方案，这里就不细说了。

读主库：通常情况下，我们是禁止读主库的，因为主库存在写请求，会锁行，如果查询的数据量很大且频繁时，会影响读请求的执行，最终对主库造成较大的压力。所以这个方案的执行要谨慎考虑。

面试官：好的，那从代码实现层面看，如果实现读写分离？

候选人：主要有三种方式：

自己改造代码，判断 SQL 语句是读还是写，从而将这条语句发送到对应的数据源（主库或者从库）中进行处理

部署代理中间件，例如 MyCat，由中间件来处理读写分离，此种方式对对开发者透明，缺点是会增加 SQL 请求的链路长度，增加网络调用的开销

使用内嵌到服务中的分库分表组件，例如 Sharding JDBC，该组件会代理所有的数据库请求，自动将请求路由到对应数据库实例上。

以上转自面试官。

如果大家有其他的方法，或者想要知道哪方面的办公技巧，下方评论哦~

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；