MySQL对于千万级的大表要怎么优化？

原文地址：码农在新加坡的个人博客

目前做电商后台开发，经历了数据从零到百万到百亿级别的增长。在这个增长的过程中，我们做了很多的优化。
这里分享一下我们的优化经验，仅供参考。　

优化策略：
不要过度优化，优化的过程一定是跟随着业务量的增长来慢慢调整优化策略的，比如说，没人就在一开始数据为0的时候就开始畅想10亿的数据的优化方案。
所以一般是业务量的增长在某个方向达到了性能瓶颈或者将要达到性能瓶颈，我们针对特殊的点做优化。
同时，不同的业务场景，优化的方案也不尽相同，比如读频繁的场景、写频繁的场景、分页查询场景、热点数据场景。

最开始我们针对一个单一业务只有一张表，比如用户表，存了用户的所有信息。一开始数据量少，并没有什么性能压力。

一、优化SQL语句和索引

比如是否有回表，索引是否可以优化来减少回表，查询条件是否可以修改来命中索引。

二、主从复制/读写分离

其实很多时候主从复制在一开始设计的时候就应该支持了。毕竟很多的ORM库本身也是支持的，并没有很多难度。

当访问量（QPS）增大，MySQL已经有了性能的压力，就该考虑加缓存了。现行的比较常见的缓存策略就是Cache Aside Pattern，也就是如果有更新，让缓存失效（删除）。
为何不更新缓存而是删除缓存呢，主要是为了防止并发的情况下可能缓存不一致的问题。所以现在大多数缓存方案都是删除的方案。它能保证数据的最终一致性。
我们的做法是监听MySQL的binlog，某一行数据有修改，删除对应的缓存，比较常用的就是Memcached，我们也有地方会用到Redis，比如需要分页查询的时候，使用Redis的ZSET方便支持。

四、垂直切分

加了缓存之后发现缓存命中率低，为啥呢，因为我们的表里面有几列数据是常更新的数据。比如：登入时间，登出时间。当他们变化的时候会删除缓存，导致重新读DB。
这个时候需要一定的策略来增大缓存的命中率，我们使用垂直切分，把易变的数据和不易变的数据且分开来。这样大多数数据不易变的，就能充分利用缓冲的优势，易变的数据可以单独优化。

五、水平切分，分库分表

关键业务就水平切分，做sharding，分库。
非关键业务直接上TiDB吧。数据量增加到亿级别之后，我们很多业务直接切换到TiDB了。TiDB 是一个兼容MySQL 协议和语法的分布式数据库，支撑百亿数据没有问题，只是在生产环境还不太稳定，有时候会出现无法命中索引需要加force index，还有由于它的二段提交机制，经常会有一些write conflict之类的。
而对于一些关键业务，我们不放心使用TiDB，开始对MySQL进行水平切分。

六、热点数据本地缓存

取决于你的数据是否有热点数据，比如秒杀活动，双十一大促的活动，很多热点店铺，热点商品等等。这种热点数据，即时使用了Memcached/Redis缓存，还是可能导致缓存的单节点性能瓶颈，这个时候就需要使用内存本地缓存，我们使用的LRU缓存。

七、水平切分（写）

对于易变的数据，QPS增大很容易触发性能瓶颈，这个时候需要水平切分来降低单个DB/表的写入QPS，来降低MySQL的资源占用。

八、消息队列（写）

异步写入，进行流量削峰，降低峰值的压力。同时降低线上服务的压力。

九、批量更新（写）

我们的更新QPS一直在增涨，即使分库分表之后，每个库的update QPS也非常高，最后我们使用批量更新的方式，把数据先更新到缓存，然后批量取固定量的数据一起更新DB。

关于批量更新：
我们由于更新的QPS过大，导致了MySQL的CPU/IO/Network都非常大。我们就是用了一种批量更新的方式，也就是MySQL的UPDATE SET CASE WHEN语法。

1. MQ或者API写入缓存，并把主键写入到Redis ZSET，主要是为了批量的取数据。我们之前已经水平切分到了1000个table，所以这里有1000个Redis ZSET。
2. 设计一个定时器的服务，定时从ZSET里面POP 20条数据，根据主键从实际的缓存里面拿具体的value数据。
3. 组装MySQL批量更新语法一次性更新。

Note：我们定时器的服务可以支持分布式任务调度，我们利用了Kafka的partition机制，每个服务都消费同一个kafka的同一个group，这样kafka会给每个服务分配互斥的partition列表。我们通过实现partition->任务分片的映射算法，最终增加或者减少实例数量的时候，通过监听的kafka的partition的变化，来决定处理那些任务分片，从而实现了无中心服务器的任务调度。

我们这种方式，使MySQL的IO/Network/CPU都降低了80%以上。这也是我们更新的终极方案了。

上文只是我们优化经历的一些阶段，不同数据不同分析，比如你的查询主要是多行查询而非单行查询，那优化方式就不一样了。
像我们有服务是多行数据查询的，我们就使用Redis的List来实现了缓存，由于是更新缓存，担心不一致，我们同时监听了MySQL的binlog来校验缓存和数据库的一致性。

同时读频繁场景和写频繁场景，优化方案也不一样。

所以说，MySQL的优化，不是一个通用的方案，只能具体业务具体分析。但是大概方案就是我上面列出来的那些。希望对你的优化有启发。

<全文完>

欢迎关注我的微信公众号：码农在新加坡，有更多好的技术分享。