MySQL架构优化及SQL优化

变更项目的整体架构是性能收益最大的方式。主要涉及两方面，一方面是从整个项目角度，引入一些中间件优化整体性能，另一方面是调整MySQL的部署架构，确保能承载更大的流量访问，提高数据层的整体吞吐。

1. 引入缓存中间件解决读压力

正常的项目业务中，读请求的数量远超写请求，所有的读请求都落入数据库处理，对MySQL会造成巨大的访问压力，甚至会由于流量过大，直接将数据库打到宕机，通常都会在应用程序和数据库之间架设一个缓存来解决这个问题，例如最常用的Redis。

在缓存Key设计合理的情况下，至少能够为MySQL分担70%以上的读压力，查询MySQL之前先查询一次Redis，Redis中有缓存数据则直接返回，没有数据时再将请求交给MySQL处理，从MySQL查询到数据后，再次将数据写入Redis，后续有相同请求再来读取数据时，直接从Redis返回数据即可。

2. 引入消息中间件解决写压力

项目中存在写操作比较频繁的时候，通过引入MQ消息中间件做削峰填谷。项目中核心的业务直接到MySQL执行落库操作，一些不重要的操作先发往MQ，MQ写入成功后直接将结果返回，后续在由消费线程去执行。

3. MySQL主从读写分离

当经过Redis、MQ后，必须要走MySQL执行的请求还是超出单机MySQL的承载范围，并且MySQL就是以单机的形式在线上运行，会出现频繁宕机的情况。

有三种MySQL架构的优化方案：主从架构，双主架构、分库分表架构。

主从复制，大多数中间件都会存在的一种高可用机制，MySQL中也存在这种架构，使用两台服务器来部署两个MySQL节点，一台为主机，另一台为从机，从节点会一直不断的从主节点上同步增量数据，当主节点发生故障时，从节点可以替换原本的主节点，以此来为客户端提供正常服务，

从节点仅作为一个备胎，难免有些浪费资源，可以在主从架构的模式下，略微做些调整，即实现读写分离，由于读操作并不会变更数据，对于读请求可以分发到从节点上处理，会引发数据变更的写请求，则分发到主节点处理，这样从而能够进一步提升MySQL的整体性能。

主节点的数据变更后，从节点也会基于bin-log日志去同步数据，但这种模式下会存在些许的数据不一致性，同步是需要时间的，向主节点修改一条数据后，立马去从节点中查询，这时不一定能够看到最新的数据，因为这时数据也许还未被同步过来。并没有太好的办法解决，项目对数据的实时性特别高就不要考虑主从架构。

4. MySQL双主双写热备

读写分离更适用于读大于写的业务。写大于读的业务从机分担的仅是系统的10~20%流量，所以双主双写（双柱热备）是最佳的选择。

两个MySQL节点都是主，同时都为从，两者之间相互同步数据，同时具备处理读/写请求的能力，出现读/写操作时，可由任意一个节点处理。

对于每张表的主键要处理好，如果表的主键是int自增类型的，要手动设置一下自增步长和起始值，比如这里有两个MySQL节点，将步长设置为2，起始值分别为1、2，能够确保主键的唯一性，设置后两个节点自增ID的序列如下：

• • 节点1：[1、3、5、7、9、11、13、15、17、19.....]
  • 节点2：[2、4、6、8、10、12、14、16、18、20.....]

当插入数据的SQL语句发往节点1时，会按照奇数序列自增ID，发往节点2时会以偶数序列自增ID，双方相互同步数据，最终两个MySQL节点都会具备完整的数据，因此后续的读请求，无论发往哪个节点都可以读到数据。

多主模式中的每个节点都会存储完整的数据，当数据增长达到硬件的最大容量时，就无法继续写入数据了，只能通过加大磁盘的形式进一步提高存储容量，但硬件也不可能无限制的加下去，而且由于多主是基于主从架构实现的，因为具备木桶效应，要加得所有节点一起加，否则另一个节点无法同步写入数据时，就会造成所有节点无法写入数据。

5. MySQL分库分表思想

根据业务属性的不同创建不同的数据库，不同的业务连接不同的数据库，各自之间数据分开存储，节点之间数据不会同步，以这种方式来部署MySQL，即提高了数据库的整体吞吐量和并发能力，同时也不存在之前的存储容量的木桶问题。实际上对项目做了分库分表之后，带来的问题、要解决的问题只会更多，只不过相较于分库分表带来的收益而言，解决问题的成本是值得的，所以才会使用分库分表技术。

 参考文档：MySQL调优篇：单机数据库如何在高并发场景下健步如飞？