MySQL分库分表的实现方式

Thu, Sep 30, 2021 阅读时间 1 分钟

分库分表

数据库的数据量增大会直接影响读写的性能，比如一次查询操作，扫描 5 万条数据和 500 万条数据，查询速度肯定是不同的。分库分表就是把一个表拆分成多个库的多个表，这些表可以在同一个服务器上，也可以在不同的服务器上，总而言之，就是要通过拆分，减小单库单表的读写压力。

那么什么时候才需要分库分表呢？

参考阿里巴巴的《Java 开发手册》中数据库部分的建表规约： **单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。**由于不同的服务器配置、数据的存储结构都不同，所以这个数据并不确切，但可以作为一个参考。

另外单表的数据库的连接是有限制的，不能无限制创建，比如 MySQL 中可以使用 max_connections 查看默认的最大连接数，当访问连接数过多时，就会导致连接失败。当并发量过大时，可能导致数据库无法支持业务请求。使用数据库连接池，可以优化连接数问题，但是更好更根除的方式是通过分库分表，来避免数据库连接成为业务瓶颈。

分库分表的方式

垂直拆分

垂直拆分就是按照表的列进行拆分，对于一个字段数过多的表，可以用这种方式拆分。可以把一些频繁更新的字段划到一个表中，把另一些不常写入的数据放到另一张表。这也可以提升性能（因为MySQL的bufer pool从磁盘上加载页时，字段变少可以让一个页中存储的行数增加，减少加载的次数，具体可以查看InnoDB的特性这篇文章）。

垂直拆分也可以把总是要一起操作的字段放到一张表，来缩小事务锁定的粒度。

水平拆分

垂直拆分就是按照表的行进行拆分。根据某个规则，一部分行存在一张表上，另一部分行存在其他表上，减小单个表的行数，提高查询效率。

实际上垂直拆分和水平拆分可以结合使用，先垂直拆分，再水平拆分。可以发挥各自的优势。

分库分表带来的问题

分布式事务的问题

对业务进行分库之后，同一个操作会分散到多个数据库中，涉及跨库执行 SQL 语句，也就出现了分布式事务问题。分布式事务的解决方案比如TCC模型，异步补偿的事务模型等，具体可以查看分布式事务这篇文章。

跨表的关联查询

分库分表后，跨库和跨表的查询操作实现起来会比较复杂，性能也无法保证。所以最好通过一些手段避免跨表查询，比如可以适当的建立合理的数据库字段冗余，避免出现跨库查询。如果非要跨表查询，在实际开发中，一般会使用额外的存储，比如维护一份文件索引。

跨库跨表的数据合并和排序

分库分表以后，数据分散存储到不同的数据库和表中，如果查询指定数据列表，或者需要对数据列表进行排序时，就变得异常复杂，可能需要在应用程序的内存中进行处理。所以还是要尽量避免，或者使用分库分表的中间件来进行处理。

分库分表的中间件：

比如 Apache ShardingSphere，或者淘宝的TDDL，但是它们都是java的，我不是java技术栈，所以了解不多。

分库分表保证唯一主键的解决方案

在单库单表时，业务 ID 可以依赖数据库的自增主键实现，现在我们把存储拆分到了多处，各个数据表就需要生成一个全局唯一的主键。生成一个全局唯一的主键。下面介绍几种生成全局唯一主键的解决方式。

使用单个表的自增主键实现

就是创建一个表，只有一个 ID 字段作为主键。每个表要插入数据时，先向这个表里插入一行，得到的主键值作为业务ID。

这个方案实现起来简单，但问题也很明显。首先，性能无法保证，在并发比较高的情况下，如果通过这样的数据表来创建自增 ID，对这个单个表的写入就会成为性能瓶颈。第二，存在单点故障，如果生成自增 ID 的数据库挂掉，那么会直接影响创建功能。

使用单个表自增主键的区间分配

上面那个方案的优化版本。既然每一次插入时，都要去插入一个表会导致性能问题，那就每次从这个表中获取一个区间自己维护。减少访问这张表的次数不就好了，具体的方案是：

先建立一个sequence表，用于记录某个业务主键当前被占用的ID区间的最大值。sequence表主要有两个字段：name 和 value，name是业务序列的名字，value是这个分配出去的 ID 最大值。

接下来插入一条行记录，当需要获取主键时，每台服务器主机从数据表中取对应的 ID 区间缓存在本地，同时更新 sequence 表中的 value 最大值记录。

取到对应的 ID 区间后，在服务器内部进行分配，涉及的并发问题可以依赖乐观锁等机制解决。本地就可以使用 AtomicInteger 等方式进行 ID 分配。

比如，当一个服务器想要插入时，可以先去 sequence 表，发现此时value是1000，获取200的区间后，更新value值为1200，则其他服务要获取主键只能从1200往后获取。

同时，为了防止单点故障，sequence 表所在的数据库，通常会配置多个从库，实现高可用。

淘宝的 TDDL 中间件使用了这个策略。

使用UUID

UUID 虽然很好地满足了全局唯一这个要求，但是并不适合作为数据库存储的唯一主键。

首先 UUID 作为数据库主键太长了，会导致比较大的存储开销。

第二，UUID 是无序的，如果使用 UUID 作为主键，会降低数据库的写入性能，因为无序的主键在插入B+树时，会频繁的触发页分裂（具体可以查看MySQL的索引这篇文章）。

基于Snowflake算法生成全局唯一ID

雪花算法是twitter开源的ID生成算法，由64位二进制数字组成，分为四部分，如下图：

第一位默认不使用，总是0，保证数值是正数。

然后是41位的时间戳，表示毫秒数，41位可以表示69年多点，一般够用，用时间戳来保证递增。

然后是10位的机器ID，支持最多1024个节点。

最后是12位的序列号，作为当前时间戳和机器下的流水号，每个节点每毫秒内支持 212 的区间，也就是 4096 个 ID，换算成秒，相当于可以允许 409 万的 QPS（每秒钟409万次插入都不会冲突），如果在这个区间内超出了 4096，则等待至下一毫秒计算。

Twitter 给出了 Snowflake 算法的示例，可以点击代码库查看。

Snowflake算法几乎可以解决生成全局ID的一切问题，又唯一，又递增，也不长，还有意义。唯一的问题是，当出现服务器时钟回拨时，会导致混乱，可能出现重复ID，不过这种可能性较小吧，运维同学注意一下就好。

分库分表的拆分方案

从一个实际的业务场景出发进行讨论。

某个电商网站的订单数据库模块，经过对业务增长的估算，预估三年后，数据规模可能达到 6000 万，每日订单数会超过 10 万。

首先选择存储实现，订单作为电商业务的核心数据，应该尽量避免数据丢失，并且对数据一致性有强要求，还要支持事务，那么可以选择 MySQL 的 InnoDB 存储引擎。

然后是数据库的可用性设计，订单数据是典型读多写少的数据，不仅要面向消费者端的读请求，内部也有很多上下游关联的业务模块在调用，所以可以搭建MySQL的一主多从模式，读写分离，写操作走主库，读操作走从库。

最后是数据规模，6000 万的数据量，参考《阿里巴巴 Java 开发手册》中「单表行数超过 500 万行」进行分表的建议，此时需要考虑进行分库分表，那么如何进行拆分呢，6000 万的数据规模，我们按照单表承载百万数量级来拆分，拆分成 64 张表，进一步可以把 64 张表拆分到两个数据库中，每个库中配置 32 张表。

那么接下来要考虑的就是如何把数据分布到这 64 张表上呢。