关于分库分表:分库分表在sharding中的实现

为什么要分库分表

随着公司业务疾速倒退，数据库中数据量猛增，拜访性能变慢。关系型数据库自身比拟容易成为零碎瓶颈、单机存储容量、连接数、解决能力无限。当单表的数据量达到 1000W 或 100G 当前，因为查问纬度较多，即便增加从库、优化索引，做很多操作时性能仍降落重大。

计划一：通过晋升硬件来进步数据处理能力，比方减少存储容量、CPU 等，这种计划老本较高，并且瓶颈在数据库服务自身，通过进步硬件失去的晋升无限;

计划二：分库分表，使得繁多数据库、繁多数据表的数据质变小，从而达到晋升数据库性能的目标。

分库分表的形式

垂直拆分

通过将一个表按字段拆分成多张表，每张表只存储其中的局部字段。

带来的晋升：

• 缩小了 IO 争抢，并缩小了锁表的概率，浏览商品的用户，跟经营配置商品应用规定不会互相冲突
• 晋升数据库 IO 效率，避免跨页，进步索引效率，充分发挥热门数据的操作效率，商品信息的操作效率不会被连累
• 垂直拆分后，能够对商品信息表再做程度拆分

程度拆分

依照业务将一张大表里的数据依据规定拆分到多张表中（对数据的拆分，不影响表构造）

一致性 hash 算法

传统 hash 取模形式的局限性

1. 缩小节点时;
2. 减少节点时;

一致性哈希算法通过一个叫作一致性哈希环的数据结构实现。这个环的终点是 0，起点是 2^32 – 1，并且终点与起点连贯，故这个环的整数散布范畴是 [0, 2^32-1]，如下图所示：

假如，咱们当初对认领记录 t，按 opportunity_id 维度分表，分成了 t_0,t_1,t_2,t_3 四张表，而后咱们将这 4 张表别离进行 hash 运算，取值范畴是 0 -2^32-1，这样，咱们能够失去 hash 环上的 4 个点，别离标记为 4 张表的地位;

而后咱们用同样的 hash 函数对接下来收到的每一条认领记录的 opportunity_id 进行运算，这样咱们也失去了每一条认领记录在 hash 环上的地位。

而后接下来，在 hash 环上按顺时针方向找到离认领记录最近的一张表，将认领记录保留到这张表中。

新增表节点

假如在 t1/t2 表节点两头新增了一张表 t5，那么须要挪动的数据只有 t1-t5 之间的数据，相比于一般的 hash 算法，须要转移的数据大大减少。

数据迁徙

1. 停机迁徙
2. 双写
   

shardingJDBC 中的路由引擎

改写引擎

正确性改写

在蕴含分表的场景中，须要将分表配置中的逻辑表名称改写为路由之后所获取的实在表名称。仅分库则不须要表名称的改写。除此之外，还包含补列和分页信息修改等内容。

标志符改写

须要改写的标识符包含表名称、索引名称以及 Schema 名称。

表名称改写是指将找到逻辑表在原始 SQL 中的地位，并将其改写为实在表的过程。表名称改写是一个典型的须要对 SQL 进行解析的场景。从一个最简略的例子开始，若逻辑 SQL 为：

SELECT order_id FROM t_order WHERE order_id=1;

假如该 SQL 配置分片键 order_id，并且 order_id= 1 的状况，将路由至分片表 1。那么改写之后的 SQL 应该为：

SELECT order_id FROM t_order_1 WHERE order_id=1;

补列

须要在查问语句中补列通常由两种状况导致。第一种状况是 ShardingSphere 须要在后果归并时获取相应数据，但该数据并未能通过查问的 SQL 返回。这种状况次要是针对 GROUP BY 和 ORDER BY。后果归并时，须要依据 GROUP BY 和 ORDER BY 的字段项进行分组和排序，但如果原始 SQL 的选择项中若并未蕴含分组项或排序项，则须要对原始 SQL 进行改写。先看一下原始 SQL 中带有后果归并所需信息的场景：

SELECT order_id, user_id FROM t_order ORDER BY user_id;

因为应用 user_id 进行排序，在后果归并中须要可能获取到 user_id 的数据，而下面的 SQL 是可能获取到 user_id 数据的，因而无需补列。

如果选择项中不蕴含后果归并时所需的列，则须要进行补列，如以下 SQL：

SELECT order_id FROM t_order ORDER BY user_id;

因为原始 SQL 中并不蕴含须要在后果归并中须要获取的 user_id，因而须要对 SQL 进行补列改写。补列之后的 SQL 是：

SELECT order_id, user_id AS ORDER_BY_DERIVED_0 FROM t_order ORDER BY user_id;

补列的另一种状况是应用 AVG 聚合函数。在分布式的场景中，应用 avg1 + avg2 + avg3 / 3 计算平均值并不正确，须要改写为 (sum1 + sum2 + sum3) / (count1 + count2 + count3)。这就须要将蕴含 AVG 的 SQL 改写为 SUM 和 COUNT，并在后果归并时从新计算平均值。例如以下 SQL：

SELECT AVG(price) FROM t_order WHERE user_id=1;

须要改写为：

SELECT COUNT(price) AS AVG_DERIVED_COUNT_0, SUM(price) AS AVG_DERIVED_SUM_0 FROM t_order WHERE user_id=1;

而后才可能通过后果归并正确的计算平均值。

分页修改

从多个数据库获取分页数据与单数据库的场景是不同的。假如每 10 条数据为一页，取第 2 页数据。在分片环境下获取 LIMIT 10, 10，归并之后再依据排序条件取出前 10 条数据是不正确的。举例说明，若 SQL 为：

SELECT score FROM t_score ORDER BY score DESC LIMIT 1, 2;

下图展现了不进行 SQL 的改写的分页执行后果。

通过图中所示，想要获得两个表中独特的依照分数排序的第 2 条和第 3 条数据，应该是 95 和 90。因为执行的 SQL 只能从每个表中获取第 2 条和第 3 条数据，即从 t_score_0 表中获取的是 90 和 80；从 t_score_0 表中获取的是 85 和 75。因而进行后果归并时，只能从获取的 90，80，85 和 75 之中进行归并，那么后果归并无论怎么实现，都不可能取得正确的后果。

正确的做法是将分页条件改写为 LIMIT 0, 3，取出所有前两页数据，再联合排序条件计算出正确的数据。下图展现了进行 SQL 改写之后的分页执行后果。

越获取偏移量地位靠后数据，应用 LIMIT 分页形式的效率就越低。有很多办法能够防止应用 LIMIT 进行分页。比方构建行记录数量与行偏移量的二级索引，或应用上次分页数据结尾 ID 作为下次查问条件的分页形式等。

分页信息修改时，如果应用占位符的形式书写 SQL，则只须要改写参数列表即可，无需改写 SQL 自身。

批量拆分

在应用批量插入的 SQL 时，如果插入的数据是跨分片的，那么须要对 SQL 进行改写来避免将多余的数据写入到数据库中。插入操作与查问操作的不同之处在于，查问语句中即应用了不存在于以后分片的分片键，也不会对数据产生影响；而插入操作则必须将多余的分片键删除。举例说明，如下 SQL：

INSERT INTO t_order (order_id, xxx) VALUES (1, ‘xxx’), (2, ‘xxx’), (3, ‘xxx’);

假如数据库依然是依照 order_id 的奇偶值分为两片的，仅将这条 SQL 中的表名进行批改，而后发送至数据库实现 SQL 的执行，则两个分片都会写入雷同的记录。尽管只有合乎分片查问条件的数据才可能被查问语句取出，但存在冗余数据的实现计划并不合理。因而须要将 SQL 改写为：

INSERT INTO t_order_0 (order_id, xxx) VALUES (2, ‘xxx’);

INSERT INTO t_order_1 (order_id, xxx) VALUES (1, ‘xxx’), (3, ‘xxx’);

应用 IN 的查问与批量插入的状况类似，不过 IN 操作并不会导致数据查问后果谬误。通过对 IN 查问的改写，能够进一步的晋升查问性能。如以下 SQL：

SELECT * FROM t_order WHERE order_id IN (1, 2, 3);

改写为：

SELECT * FROM t_order_0 WHERE order_id IN (2);

SELECT * FROM t_order_1 WHERE order_id IN (1, 3);

能够进一步的晋升查问性能。

归并引擎

排序归并

因为在 SQL 中存在 ORDER BY 语句，因而每个数据后果集本身是有序的，因而只须要将数据后果集以后游标指向的数据值进行排序即可。这相当于对多个有序的数组进行排序，归并排序是最适宜此场景的排序算法。

ShardingSphere 在对排序的查问进行归并时，将每个后果集的以后数据值进行比拟（通过实现 Java 的 Comparable 接口实现），并将其放入优先级队列。每次获取下一条数据时，只需将队列顶端后果集的游标下移，并依据新游标从新进入优先级排序队列找到本人的地位即可。

通过一个例子来阐明 ShardingSphere 的排序归并，下图是一个通过分数进行排序的示例图。图中展现了 3 张表返回的数据后果集，每个数据后果集曾经依据分数排序结束，然而 3 个数据后果集之间是无序的。将 3 个数据后果集的以后游标指向的数据值进行排序，并放入优先级队列，t_score_0 的第一个数据值最大，t_score_2 的第一个数据值次之，t_score_1 的第一个数据值最小，因而优先级队列依据 t_score_0，t_score_2 和 t_score_1 的形式排序队列。

分组归并

分组归并的状况最为简单，它分为流式分组归并和内存分组归并。流式分组归并要求 SQL 的排序项与分组项的字段以及排序类型（ASC 或 DESC）必须保持一致，否则只能通过内存归并能力保障其数据的正确性。

举例说明，假如依据科目分片，表构造中蕴含考生的姓名（为了简略起见，不思考重名的状况）和分数。通过 SQL 获取每位考生的总分，可通过如下 SQL：

SELECT name, SUM(score) FROM t_score GROUP BY name ORDER BY name;

在分组项与排序项完全一致的状况下，获得的数据是间断的，分组所需的数据全数存在于各个数据后果集的以后游标所指向的数据值，因而能够采纳流式归并。如下图所示。

聚合归并

比拟类型的聚合函数是指 MAX 和 MIN。它们须要对每一个同组的后果集数据进行比拟，并且间接返回其最大或最小值即可。

累加类型的聚合函数是指 SUM 和 COUNT。它们须要将每一个同组的后果集数据进行累加。

求平均值的聚合函数只有 AVG。它必须通过 SQL 改写的 SUM 和 COUNT 进行计算

分页归并

上文所述的所有归并类型都可能进行分页。分页也是追加在其余归并类型之上的装璜器，ShardingSphere 通过装璜者模式来减少对数据后果集进行分页的能力。分页归并负责将无需获取的数据过滤掉。

ShardingSphere 的分页性能比拟容易让使用者误会，用户通常认为分页归并会占用大量内存。在分布式的场景中，将 LIMIT 10000000, 10 改写为 LIMIT 0, 10000010，能力保障其数据的正确性。用户非常容易产生 ShardingSphere 会将大量无意义的数据加载至内存中，造成内存溢出危险的错觉。其实，通过流式归并的原理可知，会将数据全副加载到内存中的只有内存分组归并这一种状况。除了内存分组归并这种状况之外，其余状况都通过流式归并获取数据后果集，因而 ShardingSphere 会通过后果集的 next 办法将无需取出的数据全副跳过，并不会将其存入内存。

但同时须要留神的是，因为排序的须要，大量的数据依然须要传输到 ShardingSphere 的内存空间。因而，采纳 LIMIT 这种形式分页，并非最佳实际。因为 LIMIT 并不能通过索引查问数据，因而如果能够保障 ID 的连续性，通过 ID 进行分页是比拟好的解决方案，例如：

SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id;

或通过记录上次查问后果的最初一条记录的 ID 进行下一页的查问，例如：

SELECT * FROM t_order WHERE id > 10000000 LIMIT 10;

（本文作者：翁迪全）

本文系哈啰技术团队出品，未经许可，不得进行商业性转载或者应用。非商业目标转载或应用本文内容，敬请注明“内容转载自哈啰技术团队”。