ShardingSphere Proxy 是 Apache ShardingSphere 的一个子项目，是一个基于 MySQL 协议的数据库中间件，用于实现分库分表、读写分离等功能。在使用过程中，遇到了一些问题，记录如下。
这里主要针对的是 分库分表 的使用场景。
问题概述 #
数据库往往是一个系统最容易出现瓶颈的点，当遇到数据库瓶颈时，我们可以通过数据拆分来缓解问题。数据拆分的方式通常分为横向拆分和纵向拆分，横向拆分即分库分表；纵向拆分即把一个库表中的字段拆分到不同的库表中去。这两种手段并不互斥，而是在实际情况中相辅相成。本文即是横向拆分相关内容。
- 常见的部署方式有哪些？
- 数据分片规则怎么配置？
- 数据分片数应该怎么确定？
- 数据分片后唯一索引还有用吗？
- 数据分片后数据迁移？
- 数据分片后如何确定实际执行 SQL 语句？
- 数据分片后后的查询优化？
0. 常见的部署方式 #
官方提供了两种部署方式：
- 单机部署：将 ShardingSphere Proxy 部署在单台服务器上，用于测试和开发环境。
- 集群部署：将 ShardingSphere Proxy 部署在多台服务器上，用于生产环境。集群模式下使用 zookeeper 来存储元数据。
关于元数据，元数据是 ShardingSphere Proxy 的核心，用于存储分库分表规则、读写分离规则等信息。 官方建议使用集群模式部署 生产环境的 ShardingSphere Proxy
如果不按照官方的指引，选择部署了多个 Standalone 模式的 ShardingSphere Proxy，那么需要注意“每个这样的 proxy 节点会有自己的元信息，他们之间并不互通”。这样一些情况下会出现节点之间元数据不一致的问题，参看如下测试：
# 启动 3 个 standalone 模式的 ShardingSphere Proxy
+-------+
| LB |
+-------+
|
|-------------|--------------|
| | |
+-------+ +-------+ +-------+
| Node1 | | Node2 | | Node3 |
+-------+ +-------+ +-------+
初始表结构如下：
CREATE TABLE t_user (
id BIGINT NOT NULL comment '主键',
/* 主键 */
user_id BIGINT NOT NULL unique comment '用户ID',
/* 用户ID, 分表 key */
mch_id BIGINT NOT NULL comment '商户ID',
/* 商户ID, 分库 key */
PRIMARY KEY (user_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
然后在任意一个 standalone 模式的 ShardingSphere Proxy 执行更新表结构的 SQL 语句：
ALTER TABLE t_user ADD COLUMN name VARCHAR(255) NOT NULL DEFAULT '' COMMENT '姓名';
这时候在另外一个 standalone 模式的 ShardingSphere Proxy 查询表结构 和 查询数据的语句：
desc t_user;
select * from t_user;
会发现表结构描述已经是最新的，但是实际查询数据时，没有新增的字段，如下图：
这就是元数据不一致的问题，所以在生产环境中，建议使用集群模式部署 ShardingSphere Proxy。当然我们可以通过手动同步元数据的方式来解决这个问题，参考如下：
REFRESH TABLE METADATA
这样操作后再查询数据，就能拿到新增的字段了, 如下图：
1. 如何配置分库分表规则？算法有哪些？ #
规则配置参考：https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/rules/sharding/
这个问题很好理解，分库分表的规则配置是整个数据分片的核心，也是最重要的一环。在配置文件中，可以通过 shardingAlgorithms 配置项指定分库分表算法，如下：
- !SHARDING
tables: # 分片表配置
t_user: # 逻辑表名
actualDataNodes: ds_${0..1}.t_user_${0..1} # 实际数据节点
databaseStrategy: # 分库策略
standard: # 用于单分片键的分片策略
shardingColumn: mch_id # 分片键
shardingAlgorithmName: database_inline # 分库算法
tableStrategy: # 分表策略
standard: # 用于单分片键的分片策略
shardingColumn: user_id # 分片键
shardingAlgorithmName: t_user_inline # 分表算法
shardingAlgorithms: # 分片算法配置
database_inline: # 算法1 - 分库算法
props:
algorithm-expression: ds_${mch_id % 2} # 分库算法表达式
t_user_inline: # 算法2 - 分表算法
props:
algorithm-expression: t_user_${user_id % 2} # 分表算法表达式
在这个例子中，我们配置了一个 t_user 表，使用 standard 算法，分库分表的规则如下：
- 分库规则：根据
mch_id字段进行分库，分库算法为database_inline，分库算法表达式为ds_${mch_id % 2}。 - 分表规则：根据
user_id字段进行分表，分表算法为t_user_inline，分表算法表达式为t_user_${user_id % 2}。
这里的分库分表算法都是基于 “取模” 的算法，这是一种简单的分片算法，适用于大部分场景。ShardingSphere Proxy 支持的分片算法如下：
-
自动分片算法
需要注意的是，自动分片算法的分片逻辑由 ShardingSphere 自动管理，需要通过配置 autoTables 分片规则进行使用。
自动分片算法又分为：
-
取模分片算法（MOD）：根据分片键（int）对分片数取模，然后路由到对应的分片上执行。
-
Hash取模分片算法（HASH_MOD）：根据分片键（int）进行 Hash 计算，然后对分片数取模，然后路由到对应的分片上执行。
-
基于分片容量的范围分片算法（VOLUME_RANGE）：配置分片键的范围上限（range-upper）和下限（range-lower），同时还需要配置分片容量（sharding-volume）。适用于数据增长趋势相对均匀，按分片容量将数据均匀地分布到不同的分片表中，可以有效避免数据倾斜问题；由于数据已经被按照范围进行分片，支持频繁进行范围查询场景。
-
基于分片边界的范围分片算法（BOUNDARY_RANGE）：根据数据的取值范围进行分片，特别适合按数值范围频繁查询的场景，比如数据中如果包含
date: 202408这种字段，可以按照date字段进行分片，在查询时可以直接根据date字段的范围进行查询。 -
自动时间段分片算法（AUTO_INTERVAL）：配置分片的起始时间范围（datetime-lower）和结束时间范围（datetime-upper），同时还需要配置单一分片能承载的最大时间（sharding-seconds）。如下配置：
shardingAlgorithms: t_order_auto_interval: props: datetime-lower: 2024-08-01 00:00:00 datetime-upper: 2029-08-31 23:59:59 sharding-seconds: 31536000 # 1年该配置表示，将数据按照时间范围进行分片，存储从 2024-08-01 到 2029-08-31 的数据，每个分片最多存储 1 年的数据。
-
-
标准分片算法
ShardingSphere Proxy 实现的标准分片算法有：
-
行表达式分片算法（INLINE）：提供对 SQL 中的
=和IN运算符的分片操作支持，只支持单分片键。 -
时间范围分片算法（INTERVAL）：针对于时间字段（字符串类型）作为分片健的范围分片算法，适用于按照天、月、年这种固定区间的数据分片。举例如下：
shardingAlgorithms: t_order_interval: props: datetime-pattern: "yyyy-MM-dd HH:mm:ss" # 分片字段格式 datetime-lower: "2024-01-01 00:00:00" # 范围下限 datetime-upper: "2024-06-30 23:59:59" # 范围上限 sharding-suffix-pattern: "yyyyMM" # 分片名后缀，可以是MM，yyyyMMdd等。 datetime-interval-amount: 1 # 分片间隔，这里指一个月 datetime-interval-unit: "MONTHS" # 分片间隔单位这里的配置表示，将数据按照时间范围进行分片，存储从 2024-01-01 到 2024-06-30 的数据，每个分片最多存储 1 个月的数据。数据分片后的表名后缀为
yyyyMM，即每个月一个分片。
-
-
复合分片算法
-
复合行表达式分片算法（COMPLEX_INLINE）：这个也是类似于 INLINE 算法，但是支持多分片键的场景。比如在单分片键的情况下，我们使用
t_user.user_id来进行分片，现在我们需要使用t_user.mch_id和t_user.user_id来进行分片，这时候就可以使用 COMPLEX_INLINE 算法。如下：- !SHARDING tables: t_user: actualDataNodes: ds_${0..1}.t_user_${0..1} databaseStrategy: complex: shardingColumns: mch_id, user_id shardingAlgorithmName: complex_inline shardingAlgorithms: complex_inline: props: algorithm-expression: ds_${mch_id + user_id % 2}
-
-
Hint 分片算法
-
Hint 行表达式分片算法（HINT_INLINE）：类似于 INLINE 算法，但是支持不依赖于 SQL 的分片键场景。java 可以直接使用 hint API 进行使用，其他语言可以通过 SQL 注释进行使用。如下：
HintManager hintManager = HintManager.getInstance(); hintManager.addDatabaseShardingValue("t_order", 1); hintManager.addTableShardingValue("t_order", 1);/* SHARDINGSPHERE_HINT: {key} = {value}, {key} = {value} */ SELECT * FROM t_user WHERE id = 1;可以使用 keys 参见 https://shardingsphere.apache.org/document/5.5.0/cn/user-manual/common-config/sql-hint/
注意如果使用的是 shardingsphere-proxy 代理，需要在配置文件中开启 sqlCommentParseEnabled 开关。
props: sql-show: true sqlParser: sqlCommentParseEnabled: true
-
-
自定义分片算法
- 自定义分片算法（CLASS_BASED）：用户自定义分片算法。
一般情况下，我们可以根据业务场景选择合适的分片算法，如果没有特殊需求，可以使用自动分片算法，这样可以减少配置的复杂度。
2. 分片数如何确定？ #
说到数据分片数，那么必须要问一下为什么要进行数据分片，不分片不行吗？大部分业务可能都用不上分库分表，考虑分库分表时我们面临的场景大部分都是：“单表数据超过一个界限导致性能收到严重影响”，这个界限现在说法比较多的是 500w - 2000w，但这并不是铁律还是要以实际情况为准。
在决定分片数时候，有几个考虑因素：
- 数据量：根据数据量的大小来决定分片数。确保分片数能够降低单表的性能压力，同时为未来预留一定的增长空间。
- 分片键的选择：选择合适的分片键非常重要，分片键的选择会直接影响到分片数。譬如说一个分片键的取值只有 A 和 B，那我们设置超过2个分片数也没有太大的意义。
- 分片算法：选择 mod 和 range 两种分片算法时，分片数也会不一样。譬如，业务中根据时间分片，那么需要考虑是什么时间范围，按月还是年？而使用 mod 分片则需要考虑数据量和分片键。
- 维护成本：分片数越多，需要的资源和维护成本就越高。
这个当然需要结合具体情况具体分析，上述只是一个简单的分析办法，实际情况中我们还要考虑数据倾斜、数据增长和部署方式等因素。
除掉上述的因素考虑外，选择的数值尽量是 2 的幂次。免责条款：纯个人经验，不保真
3. 分库分表后唯一索引的生效范围如何？ #
分库分表后，唯一索引的生效范围是一个比较棘手的问题。在分库分表的场景下，唯一索引的生效范围是在单个分片内，而不是整个分片集群内。这就意味着，如果要保证唯一索引的唯一性，需要在应用层进行处理。
举个例子：
```sql
CREATE TABLE `t_order` (
`order_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`user_id` INT(11) NOT NULL,
`status` VARCHAR(45) NOT NULL,
PRIMARY KEY (`order_id`),
UNIQUE KEY `uk_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```
在这个例子中，order_id 字段是唯一索引，但是在分库分表的场景下，唯一索引的生效范围是在单个分片内，而不是整个分片集群内。这就意味着，如果要保证唯一索引的唯一性，需要在应用层进行处理。
如下伪代码：
```java
// 生成订单号
String orderId = generateOrderId();
// 根据订单号查询订单
Order order = orderService.findByOrderId(orderId);
if (order != null) {
// 订单号已存在
throw new OrderIdExistException();
}
// 保存订单
orderService.save(order);
```
在这个例子中，通过在应用层进行处理，可以保证唯一索引的唯一性。当然这里会存在并发问题，同样需要在应用层进行处理。可以考虑其他的解决方案，如分布式锁等，这里不再赘述。
这里还引申出来另一个问题：分库分表后怎么生成全局唯一ID？
这个问题在 ss 中可以通过设置 snowflake 算法来生成主键ID (具体参考官方文档说明配置)，如下：
# 设置表的主键生成策略
your_table:
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
# 设置主键生成器
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 1 # 注意，如果有多台机器，这里需要设置不同的值。如果不理解，请熟读雪花算法
4. 分库分表后如何迁移数据？ #
虽然在数据分片时已经提前预估了数据容量，但是难会有数据增长超出预期，或者数据意外倾斜导致单个/某个数据分片数据量超出承载能力，这时候往往通过数据迁移来解决这个问题。
有些情况可以通过删除无效数据来解决。譬如一些行为数据，历史数据 等等。
ShardingSphere-Scaling 提供了数据迁移的功能，目前处于实验室阶段，可以用于数据迁移。参考：https://shardingsphere.apache.org/document/5.1.0/cn/user-manual/shardingsphere-scaling/
除此之外，就是互联网中常用的迁移数据的方式，如：
- 停机迁移：停止服务，将老数据依次应用新的分片规则写入到新的数据源中；执行切换；启动服务。
- 不停机迁移：
- 编写迁移程序，将历史数据迁移到新的分片上；
- 更新业务应用，添加双写/读写切换 功能。打开双写，
数据分片后迁移会非常麻烦，建议在设计分片规则时，尽量考虑到未来的数据增长情况，避免数据迁移（适当的增加分片数,分片键避免产生数据倾斜）。
5. 如何确定实际执行的SQL？ #
SharidngSphere Proxy 提供了 DistSQL 操作语言，分为：RDL、RQL、RAL 和 RUL，描述如下：
| 操作语言 | 描述 |
|---|---|
| RDL | Resource & Rule Definition Language，负责资源和规则的创建、修改和删除。 |
| RQL | Resource & Rule Query Language，负责资源和规则的查询和展现。 |
| RAL | Resource & Rule Administration Language，负责强制路由、熔断、配置导入导出、数据迁移控制等管理功能。 |
| RUL | Resource & Rule Utility Language，负责 SQL 解析、SQL 格式化、执行计划预览等功能。 |
如果我们要确定实际执行的 SQL 语句，可以通过 RUL 操作语言来实现，如下：
-- 预览执行计划
PREVIEW SELECT * FROM t_user WHERE user_id = 1;
这里我们分别试验下，不同的查询条件的实际执行有什么异同。
CREATE TABLE t_user (
id BIGINT NOT NULL comment '主键', /* 主键 */
user_id BIGINT NOT NULL unique comment '用户ID', /* 用户ID, 分表 key */
mch_id BIGINT NOT NULL comment '商户ID', /* 商户ID, 分库 key */
PRIMARY KEY (user_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
5.1. 查询条件不是分片键 #
可以发现当查询条件中没有包含任何的分片键时，ShardingSphere Proxy 会将 SQL 语句发送到所有的分片节点（分库 & 分表）上执行，然后将结果合并返回。
5.2. 查询条件是分片键（分库键） #
当查询条件中包含分片键（分库键）时，ShardingSphere Proxy 会根据分片键的值，将 SQL 语句直接路由到对应的分库上执行，但是仍然要聚合分表的结果。
5.3. 查询条件是分片键（分表键） #
当查询条件中包含分片键（分表键）时，ShardingSphere Proxy 会根据分片键的值，还需要将 SQL 语句路由到所有的分库上执行，但是分表已经确定所以不需要聚合分表的结果。
5.4. 查询条件是分片键（分库键和分表键） #
当查询条件中包含分片键（分库键和分表键）时，ShardingSphere Proxy 会根据分片键的值，已经能准确的确定到分库和分表，所以直接将 SQL 语句路由特定的数据分片上执行。
从上述测试结果，测试了 RUL 中的 PREVIEW 语句，可以看到 ShardingSphere Proxy 在执行 SQL 时，会根据 SQL 中的查询条件，将 SQL 语句路由到特定的数据分片上执行，这样可以减少不必要的数据传输和计算，提高查询效率。同时也提醒我们在使用分库分表时，需要选择合理的分片键，以提高查询效率。在编写 SQL 时，也要尽量包含分片键，以减少不必要的数据传输和计算。
6. 分库分表后怎么优化查询？ #
想要优化 Shardingsphere Proxy 的查询，首先要了解 Shardingsphere Proxy 的查询过程。Shardingsphere Proxy 的查询过程如下：
- 客户端发送 SQL 语句到 Shardingsphere Proxy。
- Shardingsphere Proxy 解析 SQL 语句，根据 SQL 语句中的分片键，将 SQL 语句路由到特定的数据分片上执行。
- 数据分片执行 SQL 语句，返回结果。
- Shardingsphere Proxy 将结果聚合返回给客户端。
其中第二步是最重要的一步，Shardingsphere Proxy 会根据 SQL 语句中的分片键，将 SQL 语句路由到特定的数据分片上执行。通过前面第五个问题，我们也已经知道了使用 RUL 去分析 SQL 的实际执行计划，这样我们就可以知道 SQL 语句是如何执行的。我们能发现分片键在查询中会直接影响到 SQL 的执行计划，所以我们可以通过合理的使用分片键来优化查询。
6.1 Shardingsphere Proxy 路由策略 #
这里针对 Shardingsphere Proxy 路由引擎展开一下，我们从第五个问题只能知道带不带分片键会影响到 SQL 的执行计划，但是具体的路由引擎是怎么路由的呢？参见下图：
https://shardingsphere.apache.org/document/5.1.1/cn/reference/sharding/route/
| 路由策略 | 分类 | 描述 |
|---|---|---|
| 直接路由 | 分片路由 | 直接路由到特定的数据分片上执行。如：hintManager.setDatabaseShardingValue(3); |
| 标准路由 | 分片路由 | 不包含关联查询或仅包含绑定表之间关联查询的 SQL，当分片运算符是等于号时，路由结果将落入单库（表），当分片运算符是 BETWEEN 或 IN 时，则路由结果不一定落入唯一的库（表）。 |
| 笛卡尔路由 | 分片路由 | 无法定位分片规则，表之间的关联查询需要拆解为笛卡尔积组合执行。如后文中的 order 和 order_item 表的关联查询 。 |
| 全库路由 | 广播路由 | 全库路由用于处理对数据库的操作，包括用于库设置的 SET 类型的数据库管理命令，以及 TCL 这样的事务控制语句。如：SET autocommit=0; |
| 全库表路由 | 广播路由 | 全库表路由用于处理对数据库中与其逻辑表相关的所有真实表的操作，主要包括不带分片键的 DQL 和 DML，以及 DDL 等。如 SELECT * FROM t_order WHERE good_prority IN (1, 10); |
| 全实例路由 | 广播路由 | 全实例路由用于 DCL 操作，授权语句针对的是数据库的实例。例如：CREATE USER [email protected] identified BY '123'; |
| 单播路由 | 广播路由 | 单播路由用于获取某一真实表信息的场景，它仅需要从任意库中的任意真实表中获取数据即可。例如：DESCRIBE t_order |
| 阻断路由 | 广播路由 | 阻断路由用于屏蔽 SQL 对数据库的操作，例如：USE order_db; 不会在真实数据库中执行。 |
6.2 绑定表 #
指分片规则一致的一组分片表。 例如：t_order表和t_order_item表，均按照order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为：
SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
在不配置绑定表关系时，假设分片键order_id将数值10路由至第0片，将数值11路由至第1片，那么路由后的SQL应该为4条，它们呈现为笛卡尔积：
SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
如果配置了绑定表关系，那么路由后的SQL应该为2条，它们不再呈现为笛卡尔积：
SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
绑定表配置需要在 ShardingSphere Proxy 的配置文件中配置，如下：
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
bindingTables: # 绑定表配置
- t_order, t_order_item
6.3 广播表 #
指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。
广播表配置需要在 ShardingSphere Proxy 的配置文件中配置，如下：
rules:
- !BROADCAST
tables: # 广播表规则列表
- t_country
- t_address
广播表有以下特点：
- 插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性
- 查询操作，只从一个节点获取
- 可以跟任何一个表进行 JOIN 操作
6.4 总结 #
通过前面的分析，我们大致可以得出以下几个优化查询的方法：
- 合理选择和使用分片键，让 SQL 能够被路由到特定的数据分片上执行，避免数据聚合和笛卡尔积。
- 可以通过配置绑定表来避免笛卡尔积。
- 同样的，合理的识别并设置广播表，可以提高查询效率（避免跨 库/实例）。
- 通过 DistSQL 的
PREVIEW语句，可以预览 SQL 的执行计划，从而优化 SQL。
在编写针对数据分片的 SQL 时，虽然 ShardingSphere Proxy 能做到大部分场景的透明化，但我们还是需要清晰的了解其路由策略和执行方式，这样才能了然于心，针对性的编写 SQL，提高查询效率。
总结 #
当我们选择分库分表的方案时，我们需要决定 分片的量，分片的算法和分片键，而这一切都基于我们对业务的理解和对分库分表的认知。而最终决定这一切的都是业务的需求。在决策时，我们往往需要考虑数据分片后是否会出现：分布不均匀，出现热点数据？ 分片算法扩展性不足，导致扩展时需要数据迁移？ 数据分片键选择不合理，不符合业务中的查询模式，影响查询性能？
分库分表不是万能的，也不是完全无损的。分库之后一定会带来的问题是：事务；join 查询；排序；分页；group 分组；所以决定分片前一定要结合业务分析能不能分片。