面试官系列，深入数据库分区分库分表

作者: 邴越 | 来源:发表于2020-02-15 17:16 被阅读0次

一、为什么要分库分表

软件时代，传统应用都有这样一个特点：访问量、数据量都比较小，单库单表都完全可以支撑整个业务。随着互联网的发展和用户规模的迅速扩大，对系统的要求也越来越高。因此传统的MySQL单库单表架构的性能问题就暴露出来了。而有下面几个因素会影响数据库性能:

数据量

MySQL单库数据量在5000万以内性能比较好，超过阈值后性能会随着数据量的增大而变弱。MySQL单表的数据量是500w-1000w之间性能比较好，超过1000w性能也会下降。

磁盘

因为单个服务的磁盘空间是有限制的，如果并发压力下，所有的请求都访问同一个节点，肯定会对磁盘IO造成非常大的影响。

数据库连接

数据库连接是非常稀少的资源，如果一个库里既有用户、商品、订单相关的数据，当海量用户同时操作时，数据库连接就很可能成为瓶颈。

为了提升性能，所以我们必须要解决上述几个问题，那就有必要引进分库分表，当然除了分库分表，还有别的解决方案，就是NoSQL和NewSQL，NoSQL主要是MongoDB等，NewSQL则以TiDB为代表。

二、分区分库分表的原理

1、什么是分区、分表、分库

（1）分区

就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的，分区实现比较简单，数据库mysql、oracle等很容易就可支持。

（2）分表

就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。

（3）分库

一旦分表，一个库中的表会越来越多，将整个数据库比作图书馆，一张表就是一本书。当要在一本书中查找某项内容时，如果不分章节，查找的效率将会下降。而同理，在数据库中就是分区。

2、什么时候使用分区？

一张表的查询速度已经慢到影响使用的时候。

sql经过优化
数据量大
表中的数据是分段的
对数据的操作往往只涉及一部分数据，而不是所有的数据

最常见的分区方法就是按照时间进行分区，分区一个最大的优点就是可以非常高效的进行历史数据的清理。

（1）分区的实现方式

mysql5自5.1开始对分区(Partition)有支持。

（2）分区类型

目前MySQL支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及KEY分区四种。

（3）RANGE分区实例

基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。本例中使用to_days函数。

CREATE TABLE my_range_datetime(
    id INT,
    hiredate DATETIME
) 
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20171202') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20171203') ),
    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20171204') ),
    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20171205') ),
    PARTITION p5 VALUES LESS THAN ( TO_DAYS('20171206') ),
    PARTITION p6 VALUES LESS THAN ( TO_DAYS('20171207') ),
    PARTITION p7 VALUES LESS THAN ( TO_DAYS('20171208') ),
    PARTITION p8 VALUES LESS THAN ( TO_DAYS('20171209') ),
    PARTITION p9 VALUES LESS THAN ( TO_DAYS('20171210') ),
    PARTITION p10 VALUES LESS THAN ( TO_DAYS('20171211') )，
    PARTITION p11 VALUES LESS THAN (MAXVALUE) 
);

3、什么时候分表？

一张表的查询速度已经慢到影响使用的时候。

sql经过优化
数据量大
当频繁插入或者联合查询时，速度变慢

分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了

（1）分表的实现方式

需要结合相关中间件，需要业务系统配合迁移升级，工作量较大。

三、分库分表后引入的问题

1、分布式事务问题

如果我们做了垂直分库或者水平分库以后，就必然会涉及到跨库执行SQL的问题，这样就引发了互联网界的老大难问题-"分布式事务"。那要如何解决这个问题呢？
1.使用分布式事务中间件 2.使用MySQL自带的针对跨库的事务一致性方案(XA)，不过性能要比单库的慢10倍左右。3.能否避免掉跨库操作(比如将用户和商品放在同一个库中)

2、跨库join的问题

分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。粗略的解决方法：全局表：基础数据，所有库都拷贝一份。字段冗余：这样有些字段就不用join去查询了。系统层组装：分别查询出所有，然后组装起来，较复杂。

3、横向扩容的问题

当我们使用HASH取模做分表的时候，针对数据量的递增，可能需要动态的增加表，此时就需要考虑因为reHash导致数据迁移的问题。

4、结果集合并、排序的问题

因为我们是将数据分散存储到不同的库、表里的，当我们查询指定数据列表时，数据来源于不同的子库或者子表，就必然会引发结果集合并、排序的问题。如果每次查询都需要排序、合并等操作，性能肯定会受非常大的影响。走缓存可能一条路!

四、分库分表中间件设计

分表又分为单库分表（表名不同）和多库分表（表名相同），不管使用哪种策略都还需要自己去实现路由，制定路由规则等，可以考虑使用开源的分库分表中间件，无侵入应用设计，例如淘宝的tddl等。

分库分表中间件全部可以归结为两大类型：

CLIENT模式；
PROXY模式；

CLIENT模式代表有阿里的TDDL，开源社区的sharding-jdbc（sharding-jdbc的3.x版本即sharding-sphere已经支持了proxy模式）。

架构如下：

PROXY模式代表有阿里的cobar，民间组织的MyCAT。架构如下：

无论是CLIENT模式，还是PROXY模式。几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。

五、分库分表常用中间件

目前应用比较多的基本有以下几种，

TDDL
Sharding-jdbc
Mycat
Cobar

1、TDDL

淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法。

2、Sharding-jdbc

当当开源的，属于 client 层方案，目前已经更名为 ShardingSphere。SQL 语法支持也比较多，没有太多限制，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。

3、Cobar

阿里 b2b 团队开发和开源的，属于 proxy 层方案，就是介于应用服务器和数据库服务器之间。应用程序通过 JDBC 驱动访问 Cobar 集群，Cobar 根据 SQL 和分库规则对 SQL 做分解，然后分发到 MySQL 集群不同的数据库实例上执行。

4、Mycat

基于 Cobar 改造的，属于 proxy 层方案，支持的功能完善，社区活跃。

六、常见分表、分库常用策略

平均进行分配hash(object)%N(适用于简单架构)。
按照权重进行分配且均匀轮询。
按照业务进行分配。
按照一致性hash算法进行分配(适用于集群架构，在集群中节点的添加和删除不会造成数据丢失，方便数据迁移)。

七、全局ID生成策略

1、自动增长列

优点：数据库自带功能，有序，性能佳。
缺点：单库单表无妨，分库分表时如果没有规划，ID可能重复。

解决方案，一个是设置自增偏移和步长。

假设总共有 10 个分表
级别可选: SESSION(会话级), GLOBAL(全局)
SET @@SESSION.auto_increment_offset = 1; ## 起始值, 分别取值为 1~10
SET @@SESSION.auto_increment_increment = 10; ## 步长增量

如果采用该方案，在扩容时需要迁移已有数据至新的所属分片。

另一个是全局ID映射表。

在全局 Redis 中为每张数据表创建一个 ID 的键，记录该表当前最大 ID；
每次申请 ID 时，都自增 1 并返回给应用；
Redis 要定期持久至全局数据库。

2、UUID(128位)

在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。通常平台会提供生成UUID的API。

UUID 由4个连字号(-)将32个字节长的字符串分隔后生成的字符串，总共36个字节长。形如：550e8400-e29b-41d4-a716-446655440000。

UUID 的计算因子包括：以太网卡地址、纳秒级时间、芯片ID码和许多可能的数字。
UUID 是个标准，其实现有几种，最常用的是微软的 GUID(Globals Unique Identifiers)。

优点：简单，全球唯一；
缺点：存储和传输空间大，无序，性能欠佳。

3、COMB(组合)

组合 GUID(10字节) 和时间(6字节)，达到有序的效果，提高索引性能。

4、Snowflake(雪花) 算法

Snowflake 是 Twitter 开源的分布式 ID 生成算法，其结果为 long(64bit) 的数值。
其特性是各节点无需协调、按时间大致有序、且整个集群各节点单不重复。
该数值的默认组成如下(符号位之外的三部分允许个性化调整)：

1bit: 符号位，总是 0(为了保证数值是正数)。
41bit: 毫秒数(可用 69 年)；
10bit: 节点ID(5bit数据中心 + 5bit节点ID，支持 32 * 32 = 1024 个节点)
12bit: 流水号(每个节点每毫秒内支持 4096 个 ID，相当于 409万的 QPS，相同时间内如 ID 遇翻转，则等待至下一毫秒)

八、优雅实现分库分表的动态扩容

优雅的设计扩容缩容的意思就是进行扩容缩容的代价要小，迁移数据要快。

可以采用逻辑分库分表的方式来代替物理分库分表的方式，要扩容缩容时，只需要将逻辑上的数据库、表改为物理上的数据库、表。

第一次进行分库分表时就多分几个库，一个实践是利用32 * 32来分库分表，即分为32个库，每个库32张表，一共就是1024张表，根据某个id先根据先根据数据库数量32取模路由到库，再根据一个库的表数量32取模路由到表里面。

刚开始的时候，这个库可能就是逻辑库，建在一个mysql服务上面，比如一个mysql服务器建了16个数据库。

如果后面要进行拆分，就是不断的在库和mysql实例之间迁移就行了。将mysql服务器的库搬到另外的一个服务器上面去，比如每个服务器创建8个库，这样就由两台mysql服务器变成了4台mysql服务器。我们系统只需要配置一下新增的两台服务器即可。

比如说最多可以扩展到32个数据库服务器，每个数据库服务器是一个库。如果还是不够？最多可以扩展到1024个数据库服务器，每个数据库服务器上面一个库一个表。因为最多是1024个表么。

这么搞，是不用自己写代码做数据迁移的，都交给dba来搞好了，但是dba确实是需要做一些库表迁移的工作，但是总比你自己写代码，抽数据导数据来的效率高得多了。

哪怕是要减少库的数量，也很简单，其实说白了就是按倍数缩容就可以了，然后修改一下路由规则。

参考文档

shardingsphere.apache.org
深度认识 Sharding-JDBC