MySQL分库分表知识总结

分表

为何要分表

分表的终极目的是提高查询效率。

如何拆分

垂直拆分

垂直拆分就是将一张大表拆分成若干张小表，每张表的结构不同。最常见的是将热点列放在一张表，不常用的列放在另外一张表。

举个例子，table_1有如下列：col_1、col_2、… 、col_9、col_10，其中col_1 - col_5是热点列，而col_6 - col_10则用的比较少，于是将table_1拆分成两张表table_1和table_2，table_1包含col_1 - col_5，table_2包含col_6 - col_10。

这样做的好处是：

1. 让数据库缓存更多的数据，提高查询效率。热点列因为经常访问，所以常驻内存。
2. 降低CPU占用率。MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，列太多，会导致CPU占用变高。

水平拆分

每张表的结构相同，表名不同（但类似）。可以对表中某个列的值求hash（一般使用一致性hash算法，方便扩容）来分表。如果是时序数据，可以按时间范围分表，比如按天分表或按月分表。查找数据时，需要使用相同的方法来定位目标数据位于哪一张表。

举个例子，将table拆分成table_1、table_2、… 、table_9、table_10，然后通过hash(id)来进行路由。对于时序数据，假如是按月拆分，可以拆分为table_2023_01、table_2023_02、… 、table_2023_12这种形式，通过时间列进行路由。

水平拆分之后，每次查询时先通过路由规则来定位表，然后再去对应的表查询，这样就可以减少查询需要匹配的数据量，从而提高查询效率。水平分表一般配合水平分库来使用，提升硬件规模，效果会更佳。

分库

为何要分库

有时候，纵使你把表拆分的再细，也没办法解决性能瓶颈的问题，因为此时的瓶颈在于硬件（CPU、I/O、内存），这时就需要分库了。分库的目的是让数据库分布在不同的MySQL进程上，并且这些MySQL进程在硬件上是隔离（或者互相独立）的，以此来提高数据库的并发和查询效率。虽然你可以在同一个MySQL进程里创建多个数据库，或者这些数据库在同一个硬件环境中（共享CPU、I/O、内存），但是这种做法对于解决性能问题没有任何帮助。

如何拆分

垂直拆分

垂直拆分是按照业务拆分，比如订单系统一个库，支付系统一个库，这在微服务场景中较为多见。垂直拆分有一个不好的点，那就是引入了跨库事务或者分布式事务，增加了系统的复杂度。

水平拆分

水平拆分需要配合水平分表来使用，例如db_1和db_2都有t_user表，通过路由规则来决定是访问db_1的t_user表还是访问db_2的t_user表。这种场景下一般需要一个数据库网关，我们的程序连接的是数据库网关，数据库网关连接不同的数据库实例然后根据规则去路由请求。

数据迁移

假设你一开始是单库单表的，现在用户规模上来了，需要分库分表，方案都设计好了，现在的问题是如何平滑的进行数据迁移，
这里有个即简单又能让停机时间更短的方案可供参考：

1. 搭建好新库；
2. 旧库开启binlog，旧库继续服役；
3. 从旧库导出全量数据；
4. 将全量数据按照路由规则写入新库；
5. 全量数据写入完毕，弃用旧库，此时系统不可用；
6. 将binlog中的增量数据按照路由规则写入新库；
7. 启用新库，此时系统恢复到可用状态。

上述操作选择在凌晨执行，增量数据是非常少的，因此系统停机时间也会比较短。

动态扩容的分库方案

系统刚起步时就可以按照分库的方案来设计系统，预留足够的逻辑库，一开始这些逻辑库位于同一台服务器的同一个MySQL实例上，等到规模起来了，可以增加服务器并部署多个MySQL实例，将这些逻辑库分别部署到不同的MySQL实例上。另外，使用一致性Hash算法来路由，在扩容或者缩容时，需要迁移的数据会更少。