分库分表 21 招

标签：分库 21 分片 id SQL 分表 数据 order 路由

（一）好好的系统，为什么要分库分表？

咱们先介绍下在分库分表架构实施过程中，会接触到的一些通用概念，了解这些概念能够帮助理解市面上其他的分库分表工具，尽管它们的实现方法可能存在差异，但整体思路基本一致。因此，在开始实际操作之前，我们有必要先掌握这些通用概念，以便更好地理解和应用分库分表技术。

我们结合具体业务场景，以t_order表为例进行架构优化。由于数据量已经达到亿级别，查询性能严重下降，因此我们采用了分库分表技术来处理这个问题。具体而言，我们将原本的单库分成了两个库，分别为DB_1和DB_2，并在每个库中再次进行分表处理，生成t_order_1和t_order_2两张表，实现对订单表的分库分表处理。

数据分片

通常我们在提到分库分表的时候，大多是以水平切分模式（水平分库、分表）为基础来说的，数据分片它将原本一张数据量较大的表 t_order 拆分生成数个表结构完全一致的小数据量表（拆分表） t_order_0、t_order_1、···、t_order_n，每张表只存储原大表中的一部分数据。

数据节点

数据节点是数据分片中一个不可再分的最小单元（表），它由数据源名称和数据表组成，例如上图中 DB_1.t_order_1、DB_2.t_order_2 就表示一个数据节点。

逻辑表

逻辑表是指具有相同结构的水平拆分表的逻辑名称。

比如我们将订单表t_order 分表拆分成 t_order_0 ··· t_order_9等10张表，这时我们的数据库中已经不存在 t_order这张表，取而代之的是若干的t_order_n表。

分库分表通常对业务代码都是无侵入式的，开发者只专注于业务逻辑SQL编码，我们在代码中SQL依然按 t_order来写，而在执行逻辑SQL前将其解析成对应的数据库真实执行的SQL。此时 t_order 就是这些拆分表的逻辑表。

业务逻辑SQL

select * from t_order where order_no='A11111'

真实执行SQL

select * from DB_1.t_order_n where order_no='A11111'

真实表

真实表就是在数据库中真实存在的物理表DB_1.t_order_n。

广播表

广播表是一类特殊的表，其表结构和数据在所有分片数据源中均完全一致。与拆分表相比，广播表的数据量较小、更新频率较低，通常用于字典表或配置表等场景。由于其在所有节点上都有副本，因此可以大大降低JOIN关联查询的网络开销，提高查询效率。

需要注意的是，对于广播表的修改操作需要保证同步性，以确保所有节点上的数据保持一致。

广播表的特点：

• 在所有分片数据源中，广播表的数据完全一致。因此，对广播表的操作（如插入、更新和删除）会实时在每个分片数据源中执行一遍，以保证数据的一致性。

• 对于广播表的查询操作，仅需要在任意一个分片数据源中执行一次即可。

• 与任何其他表进行JOIN操作都是可行的，因为由于广播表的数据在所有节点上均一致，所以可以访问到任何一个节点上的相同数据。

什么样的表可以作为广播表呢？

订单管理系统中，往往需要查询统计某个城市地区的订单数据，这就会涉及到省份地区表t_city与订单流水表DB_n.t_order_n进行JOIN查询，因此可以考虑将省份地区表设计为广播表，核心理念就是避免跨库JOIN操作。

注意：上边我们提到广播表在数据插入、更新与删除会实时在每个分片数据源均执行，也就是说如果你有1000个分片数据源，那么修改一次广播表就要执行1000次SQL，所以尽量不在并发环境下和业务高峰时进行，以免影响系统的性能。

单表

单表指所有的分片数据源中仅唯一存在的表（没有分片的表），适用于数据量不大且无需分片的表。

如果一张表的数据量预估在千万级别，且没有与其他拆分表进行关联查询的需求，建议将其设置为单表类型，存储在默认分片数据源中。

分片键

分片键决定了数据落地的位置，也就是数据将会被分配到哪个数据节点上存储。因此，分片键的选择非常重要。

比如我们将 t_order 表进行分片后，当插入一条订单数据执行SQL时，需要通过解析SQL语句中指定的分片键来计算数据应该落在哪个分片中。以表中order_no字段为例，我们可以通过对其取模运算（比如 order_no % 2）来得到分片编号，然后根据分片编号分配数据到对应的数据库实例（比如 DB_1 和 DB_2）。拆分表也是同理计算。

在这个过程中，order_no 就是 t_order 表的分片键。也就是说，每一条订单数据的 order_no 值决定了它应该存放的数据库实例和表。选择一个适合作为分片键的字段可以更好地利用水平分片带来的性能提升。

这样同一个订单的相关数据就会落在同一个数据库、表中，查询订单时同理计算，就可直接定位数据位置，大幅提升数据检索的性能，避免了全库表扫描。

不仅如此 ShardingSphere 还支持根据多个字段作为分片健进行分片，这个在后续对应章节中会详细讲。

分片策略

分片策略来指定使用哪种分片算法、选择哪个字段作为分片键以及如何将数据分配到不同的节点上。

分片策略是由分片算法和分片健组合而成，分片策略中可以使用多种分片算法和对多个分片键进行运算。

分库、分表的分片策略配置是相对独立的，可以各自使用不同的策略与算法，每种策略中可以是多个分片算法的组合，每个分片算法可以对多个分片健做逻辑判断。

分片算法

分片算法则是用于对分片键进行运算，将数据划分到具体的数据节点中。

常用的分片算法有很多：

• 哈希分片：根据分片键的哈希值来决定数据应该落到哪个节点上。例如，根据用户 ID 进行哈希分片，将属于同一个用户的数据分配到同一个节点上，便于后续的查询操作。
• 范围分片：分片键值按区间范围分配到不同的节点上。例如，根据订单创建时间或者地理位置来进行分片。
• 取模分片：将分片键值对分片数取模，将结果作为数据应该分配到的节点编号。例如， order_no % 2 将订单数据分到两个节点之一。
• .....

实际业务开发中分片的逻辑要复杂的多，不同的算法适用于不同的场景和需求，需要根据实际情况进行选择和调整。

绑定表

绑定表是那些具有相同分片规则的一组分片表，由于分片规则一致所产生的的数据落地位置相同，在JOIN联合查询时能有效避免跨库操作。

比如：t_order 订单表和 t_order_item 订单项目表，都以 order_no 字段作为分片键，并且使用 order_no 进行关联，因此两张表互为绑定表关系。

使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率。

当使用 t_order 和 t_order_item 表进行多表联合查询，执行如下联合查询的逻辑SQL。

SELECT * FROM t_order o JOIN t_order_item i ON o.order_no=i.order_no

如果不配置绑定表关系，两个表的数据位置不确定就会全库表查询，出现笛卡尔积关联查询，将产生如下四条SQL。

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_no=i.order_no 
SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_no=i.order_no 
SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_no=i.order_no 
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_no=i.order_no

而配置绑定表关系后再进行关联查询时，分片规则一致产生的数据就会落到同一个库表中，那么只需在当前库中 t_order_n 和 t_order_item_n 表关联即可。

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id 
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id 

注意：在关联查询时 t_order 它作为整个联合查询的主表。所有相关的路由计算都只使用主表的策略，t_order_item 表的分片相关的计算也会使用 t_order 的条件，所以要保证绑定表之间的分片键要完全相同。

SQL 解析

分库分表后在应用层面执行一条 SQL 语句时，通常需要经过以下六个步骤：SQL 解析 -> 执⾏器优化 -> SQL 路由 -> SQL 改写 -> SQL 执⾏ -> 结果归并 。

SQL解析过程分为词法解析和语法解析两步，比如下边查询用户订单的SQL，先用词法解析将这条SQL拆解成不可再分的原子单元。在根据不同数据库方言所提供的字典，将这些单元归类为关键字，表达式，变量或者操作符等类型。

SELECT order_no FROM t_order where  order_status > 0  and user_id = 10086 

接着语法解析会将拆分后的SQL关键字转换为抽象语法树，通过对抽象语法树遍历，提炼出分片所需的上下文，上下文包含查询字段信息（Field）、表信息（Table）、查询条件（Condition）、排序信息（Order By）、分组信息（Group By）以及分页信息（Limit）等，并标记出 SQL中有可能需要改写的位置。

执⾏器优化

执⾏器优化是根据SQL查询特点和执行统计信息，选择最优的查询计划并执行，比如user_id字段有索引，那么会调整两个查询条件的位置，主要是提高SQL的执行效率。

SELECT order_no FROM t_order where user_id = 10086 and order_status > 0

SQL 路由

通过上边的SQL解析得到了分片上下文数据，在匹配用户配置的分片策略和算法，就可以运算生成路由路径，将 SQL 语句路由到相应的数据节点上。

简单点理解就是拿到分片策略中配置的分片键等信息，在从SQL解析结果中找到对应分片键字段的值，计算出 SQL该在哪个库的哪个表中执行，SQL路由又根据有无分片健分为 分片路由 和 广播路由。

有分⽚键的路由叫分片路由，细分为直接路由、标准路由和笛卡尔积路由这3种类型。

标准路由

标准路由是最推荐也是最为常⽤的分⽚⽅式，它的适⽤范围是不包含关联查询或仅包含绑定表之间关联查询的SQL。

当 SQL分片健的运算符为 = 时，路由结果将落⼊单库（表），当分⽚运算符是 BETWEEN 或 IN 等范围时，路由结果则不⼀定落⼊唯⼀的库（表），因此⼀条逻辑SQL最终可能被拆分为多条⽤于执⾏的真实SQL。

SELECT * FROM t_order  where t_order_id in (1,2)

SQL路由处理后

SELECT * FROM t_order_0  where t_order_id in (1,2)
SELECT * FROM t_order_1  where t_order_id in (1,2)

直接路由

直接路由是直接将SQL路由到指定⾄库、表的一种分⽚方式，而且直接路由可以⽤于分⽚键不在SQL中的场景，还可以执⾏包括⼦查询、⾃定义函数等复杂情况的任意SQL。

笛卡尔积路由

笛卡尔路由是由⾮绑定表之间的关联查询产生的，比如订单表t_order 分片键是t_order_id 和用户表t_user分片键是t_order_id ，两个表的分片键不同，要做联表查询，会执行笛卡尔积路由，查询性能较低尽量避免走此路由模式。

SELECT * FROM t_order_0 t LEFT JOIN t_user_0 u ON u.user_id = t.user_id WHERE t.user_id = 1
SELECT * FROM t_order_0 t LEFT JOIN t_user_1 u ON u.user_id = t.user_id WHERE t.user_id = 1
SELECT * FROM t_order_1 t LEFT JOIN t_user_0 u ON u.user_id = t.user_id WHERE t.user_id = 1
SELECT * FROM t_order_1 t LEFT JOIN t_user_1 u ON u.user_id = t.user_id WHERE t.user_id = 1

无分⽚键的路由又叫做广播路由，可以划分为全库表路由、全库路由、 全实例路由、单播路由和阻断路由这 5种类型。

全库表路由

全库表路由针对的是数据库 DQL 和 DML，以及 DDL等操作，当我们执行一条逻辑表 t_order SQL时，在所有分片库中对应的真实表 t_order_0 ···  t_order_n 内逐一执行。

全库路由

全库路由主要是对数据库层面的操作，比如数据库 SET 类型的数据库管理命令，以及 TCL 这样的事务控制语句。

对逻辑库设置 autocommit 属性后，所有对应的真实库中都执行该命令。

SET autocommit=0;

全实例路由

全实例路由是针对数据库实例的 DCL 操作（设置或更改数据库用户或角色权限），比如：创建一个用户 order ，这个命令将在所有的真实库实例中执行，以此确保 order 用户可以正常访问每一个数据库实例。

CREATE USER [email protected] identified BY '程序员小富';

单播路由

单播路由用来获取某一真实表信息，比如获得表的描述信息：

DESCRIBE t_order; 

t_order 的真实表是 t_order_0 ···· t_order_n，他们的描述结构相完全同，我们只需在任意的真实表执行一次就可以。

阻断路由

⽤来屏蔽SQL对数据库的操作，例如：

USE order_db;

这个命令不会在真实数据库中执⾏，因为 ShardingSphere 采⽤的是逻辑 Schema（数据库的组织和结构） ⽅式，所以无需将切换数据库的命令发送⾄真实数据库中。

SQL 改写

SQL经过解析、优化、路由后已经明确分片具体的落地执行的位置，接着就要将基于逻辑表开发的SQL改写成可以在真实数据库中可以正确执行的语句。比如查询 t_order 订单表，我们实际开发中 SQL是按逻辑表 t_order 写的。

SELECT * FROM t_order

这时需要将分表配置中的逻辑表名称改写为路由之后所获取的真实表名称。

SELECT * FROM t_order_n

SQL执⾏

将路由和改写后的真实 SQL 安全且高效发送到底层数据源执行。但这个过程并不能将 SQL 一股脑的通过 JDBC 直接发送至数据源执行，需平衡数据源连接创建以及内存占用所产生的消耗，它会自动化的平衡资源控制与执行效率。

结果归并

将从各个数据节点获取的多数据结果集，合并成一个大的结果集并正确的返回至请求客户端，称为结果归并。而我们SQL中的排序、分组、分页和聚合等语法，均是在归并后的结果集上进行操作的。

分布式主键

数据分⽚后，一个逻辑表（t_order）对应诸多的真实表（t_order_n），它们之间由于⽆法互相感知，主键ID都从初始值累加，所以必然会产⽣重复主键ID，此时主键不再唯一那么对于业务来说也就没意义了。

尽管可通过设置表⾃增主键 初始值 和 步⻓ 的⽅式避免ID碰撞，但这样会使维护成本加大，可扩展性差。

这个时候就需要我们手动为一条数据记录，分配一个全局唯一的ID，这个ID被叫做分布式ID，而生产这个ID的系统通常被叫做发号器。

大家可以参考我之前发布的这篇文章 9种分布式ID生成方案

数据脱敏

分库分表数据脱敏是一种有效的数据保护措施，可以确保敏感数据的机密性和安全性，减少数据泄露的风险。

比如，我们在分库分表时可以指定表的哪些字段为脱敏列，并设置对应的脱敏算法，在数据分片时解析到执行SQL中有待脱敏字段，会直接将字段值脱敏后的写入库表内。

对于用户的个人信息，如姓名、地址和电话号码等，可以通过加密、随机化或替换成伪随机数据的方式进行脱敏，以确保用户的隐私得到保护。

大家可以参考我之前发布的这篇文章 大厂也在用的 6种 数据脱敏方案

分布式事务

分布式事务的核心问题是如何实现跨多个数据源的原子性操作。

由于不同的服务通常会使用不同的数据源来存储和管理数据，因此，跨数据源的操作可能会导致数据不一致性或丢失的风险。因此，保证分布式事务的一致性是非常重要的。

以订单系统为例，它需要调用支付系统、库存系统、积分系统等多个系统，而每个系统都维护自己的数据库实例，系统间通过API接口交换数据。

为了保证下单后多个系统同时调用成功，可以使用强一致性事务的XA协议，或者柔性事务的代表工具Seata，来实现分布式事务的一致性。这些工具可以帮助开发人员简化分布式事务的实现，减少错误和漏洞的出现，提高系统的稳定性和可靠性。

经过分库分表之后，问题的难度进一步提升。自身订单服务，也需要处理跨数据源的操作。这样一来，系统的复杂度显著增加。因此，不到万不得已的情况下，最好避免采用分库分表的解决方案。

关于分布式事务详细的介绍，大家可以参考我之前发布的这篇文章 对比 5 种分布式事务方案，还是宠幸了阿里的 Seata（原理 + 实战）

数据迁移

分库分表后还有个让人头疼的问题，那就是数据迁移，为了不影响现有的业务系统，通常会新建数据库集群迁移数据。将数据从旧集群的数据库、表迁移到新集群的分库、分表中。这是一个比较复杂的过程，在迁移过程中需要考虑数据量、数据一致性、迁移速度等诸多因素。

迁移主要针对 存量数据 和 增量数据 的处理，存量数据指旧数据源中已经存在且有价值的历史数据，增量数据指当下持续增长以及未来产生的业务数据。

存量数据可以采用定时、分批次的迁移，迁移过程可能会持续几天。

增量数据可以采用新、旧数据库集群双写模式。待数据迁移完毕，业务验证了数据一致性，应用直接切换数据源即可。

后续我们会结合三方工具，来演示迁移的过程。

影子库

什么是影子库（Shadow Table）？

影子库是一个与生产环境数据库结构完全相同的实例，它存在的意义是为了在不影响线上系统的情况下，验证数据库迁移或者其他数据库变更操作的正确性，以及全链路压测。影子库中存储的数据是从生产环境中定期复制过来的，但是它不对线上业务产生任何影响，仅用于测试，验证和调试。

在进行数据库升级、版本变更、参数调优等操作前，通过在影子库上模拟这些操作，可以发现潜在的问题，因为测试环境的数据是不可靠的。

在使用影子库时，需要遵循以下几个原则：

• 与生产环境数据库的结构应该完全一致，包括表结构、索引、约束等；

• 数据要与生产环境保持一致，可以通过定期同步方式实现；

• 读写操作不会影响生产环境，一般情况下应该禁止在影子库上执行更新、删除等操作；

• 由于影子库的数据特点，访问权限应该严格控制，只允许授权人员进行访问和操作；

   

   

  作者|程序员内点事

标签：分库,21,分片,id,SQL,分表,数据,order,路由
From： https://www.cnblogs.com/88223100/p/21-bidding-for-database-and-table-division.html