05ShardingSphere-JDBC水平分片

1、准备服务器

随着业务的扩大，订单表数据量不断增加，数据库面临存储压力，开始考虑对订单表进行水平分片。

将t_order表扩展为server-order0中的t_order0和t_order1、server-order1中的t_order0和t_order1

服务器规划：使用docker方式创建如下容器

服务器：容器名server-order0，端口3310
服务器：容器名server-order1，端口3311

1.1、创建server-order0容器

step1：创建容器：

docker run -d \
-p 3310:3306 \
-v /dongguo/server/order0/conf:/etc/mysql/conf.d \
-v /dongguo/server/order0/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-order0 \
mysql:8.0.29
1
2
3
4
5
6
7

step2：登录MySQL服务器：

#进入容器：
docker exec -it server-order0 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码插件
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
1
2
3
4
5
6

step3：创建数据库：

注意：水平分片的id需要在业务层实现，不能依赖数据库的主键自增

CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order0 (
  id BIGINT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) 
);
CREATE TABLE t_order1 (
  id BIGINT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) 
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

1.2、创建server-order1容器

step1：创建容器：

docker run -d \
-p 3311:3306 \
-v /dongguo/server/order1/conf:/etc/mysql/conf.d \
-v /dongguo/server/order1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-order1 \
mysql:8.0.29
1
2
3
4
5
6
7

step2：登录MySQL服务器：

#进入容器：
docker exec -it server-order1 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码插件
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
1
2
3
4
5
6

step3：创建数据库：

和server-order0相同

注意：水平分片的id需要在业务层实现，不能依赖数据库的主键自增

CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order0 (
  id BIGINT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) 
);
CREATE TABLE t_order1 (
  id BIGINT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) 
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

查看容器

查看navicat

3310

3311

后续还需要server-user服务，启动该服务

2、基本水平分片

2.1、基本配置

application.yml

# 应用名称
spring:
  application:
    name: sharding-jdbc-demo
  profiles:
    active: dev
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    #读写分离
#    url: jdbc:shardingsphere:classpath:shardingsphere-readwrite.yaml
    #垂直分片
#    url: jdbc:shardingsphere:classpath:shardingsphere-sharding.yaml
    #水平分片-分库
    url: jdbc:shardingsphere:classpath:shardingsphere-sharding2.yaml
# Swagger配置
swagger:
  # 是否开启swagger
  enabled: true
  # 请求前缀
  pathMapping: /
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.2、数据源配置

shardingsphere-sharding2.yaml

# 数据源配置
dataSources:
  server-user:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3301/db_user
    username: root
    password: 123456
  server-order0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3310/db_order
    username: root
    password: 123456
  server-order1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3311/db_order
    username: root
    password: 123456
#规则配置
rules:
- !SHARDING
  tables:
    # 逻辑表名
    t_user:
      # 值由数据源名 + 表名组成，以小数点分隔。
      actualDataNodes: server-user.t_user
    t_order:
      actualDataNodes: server-order0.t_order_0,server-order0.t_order_1,server-order1.t_order_0,server-order1.t_order_1

#属性配置
props:
  sql-show: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

修改Order实体类的主键策略：

水平分片的id需要在业务层实现，不能依赖数据库的主键自增，将id生成策略设置成雪花算法生成id

package com.dongguo.shardingjdbc.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.math.BigDecimal;

@TableName("t_order")
@Data
public class Order {
    //@TableId(type = IdType.AUTO)//依赖数据库的主键自增策略
    @TableId(type = IdType.ASSIGN_ID)//分布式id
    private Long id;
    private String orderNo;
    private Long userId;
    private BigDecimal amount;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

在配置分片算法之前，确认每个分片节点都是可用。

测试：保留上面配置中的一个分片表节点分别进行测试，检查每个分片节点是否可用

    /**
     * 水平分片：插入数据测试
     */
    @Test
    public void testInsertOrder(){
        Order order = new Order();
        order.setOrderNo("SP202308140001");
        order.setUserId(1L);
        order.setAmount(new BigDecimal(100));
        orderMapper.insert(order);
    }
1
2
3
4
5
6
7
8
9
10
11

shardingsphere-sharding2.yaml

server-order0.t_order0

    t_order:
      actualDataNodes: server-order0.t_order0
1
2

2023-08-14 20:49:25.528  INFO 1296 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-14 20:49:25.528  INFO 1296 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1691069489988296705, SP202308140001, 1, 100]
1
2
3
4
5
6
7
8
9
10
11

server-order0.t_order1

    t_order:
      actualDataNodes: server-order0.t_order1
1
2

2023-08-14 20:50:35.950  INFO 5476 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-14 20:50:35.951  INFO 5476 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1691069785481207809, SP202308140001, 1, 100]
1
2
3
4
5
6
7
8
9
10
11

server-order1.t_order0

    t_order:
      actualDataNodes: server-order1.t_order0
1
2

2023-08-14 20:51:12.503  INFO 11204 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-14 20:51:12.503  INFO 11204 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1691069939445686274, SP202308140001, 1, 100]
1
2
3
4
5
6
7
8
9
10
11

server-order1.t_order1

    t_order:
      actualDataNodes: server-order1.t_order1
1
2

2023-08-14 20:51:54.268  INFO 22500 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-14 20:51:54.268  INFO 22500 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1691070114000031746, SP202308140001, 1, 100]
1
2
3
4
5
6
7
8
9
10
11

每个分片节点都可用，每次测试完之后记得将数据删除。

2.3、行表达式

优化上一步的分片表配置

https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/

行表达式是为了解决配置的简化与一体化这两个主要问题。在繁琐的数据分片规则配置中，随着数据节点的增多，大量的重复配置使得配置本身不易被维护。通过行表达式可以有效地简化数据节点配置工作量。

对于常见的分片算法，使用 Java 代码实现并不有助于配置的统一管理。通过行表达式书写分片算法，可以有效地将规则配置一同存放，更加易于浏览与存储。

行表达式的使用非常直观，只需要在配置中使用 ${ expression } 或 $->{ expression } 标识行表达式即可。目前支持数据节点和分片算法这两个部分的配置。行表达式的内容使用的是 Groovy 的语法，Groovy 能够支持的所有操作，行表达式均能够支持。例如：

${begin..end} 表示范围区间 ${[unit1, unit2, unit_x]} 表示枚举值

行表达式中如果出现连续多个 ${ expression } 或 $->{ expression } 表达式，整个表达式最终的结果将会根据每个子表达式的结果进行笛卡尔组合。

shardingsphere-sharding2.yaml

# 数据源配置
dataSources:
  server-user:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3301/db_user
    username: root
    password: 123456
  server-order0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3310/db_order
    username: root
    password: 123456
  server-order1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3311/db_order
    username: root
    password: 123456
#规则配置
rules:
- !SHARDING
  tables:
    # 逻辑表名
    t_user:
      # 值由数据源名 + 表名组成，以小数点分隔。
      actualDataNodes: server-user.t_user
    t_order:
      actualDataNodes: server-order${0..1}.t_order${0..1}

#属性配置
props:
  sql-show: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

2.4、分片算法配置

ShardingSphere 内置提供了多种分片算法，按照类型可以划分为自动分片算法、标准分片算法、复合分片算法和 Hint 分片算法，能够满足用户绝大多数业务场景的需要。此外，考虑到业务场景的复杂性，内置算法也提供了自定义分片算法的方式，用户可以通过编写 Java 代码来完成复杂的分片逻辑。需要注意的是，自动分片算法的分片逻辑由 ShardingSphere 自动管理，需要通过配置 autoTables 分片规则进行使用。

水平分库

分片规则：order表中user_id为偶数时，数据插入server-order0服务器，user_id为奇数时，数据插入server-order1服务器。这样分片的好处是，同一个用户的订单数据，一定会被插入到同一台服务器上，查询一个用户的订单时效率较高。

分片算法相关参考：

https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/sharding/

shardingsphere-sharding2.yaml

标准分片算法-行表达式分片算法

# 数据源配置
dataSources:
  server-user:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3301/db_user
    username: root
    password: 123456
  server-order0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3310/db_order
    username: root
    password: 123456
  server-order1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3311/db_order
    username: root
    password: 123456
#规则配置
rules:
- !SHARDING
  tables:
    # 逻辑表名
    t_user:
      # 值由数据源名 + 表名组成，以小数点分隔。
      actualDataNodes: server-user.t_user
    t_order:
      actualDataNodes: server-order${0..1}.t_order${0..1}
      #分库策略
      databaseStrategy:
        standard:
          # 分片列名称
          shardingColumn: user_id
          # 分片算法名称
          shardingAlgorithmName: alg_inline_userid
  # 分片算法配置
  shardingAlgorithms:
    # 标准分片算法-行表达式分片算法
    alg_inline_userid:
      #基于行表达式的分片算法
      type: INLINE
      props:
        algorithm-expression: server-order${user_id % 2}
#属性配置
props:
  sql-show: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

配置了分片算法alg_inline_userid

注:在之前有些版本中shardingAlgorithmName对应的值好像不支持下划线，在ShardingSphere5.4.0版本是支持下划线命名的。

如果版本不支持下划线报错如下:

Caused by: org.apache.shardingsphere.spi.exception.ServiceProviderNotFoundException: No implementation class load from SPI `org.apache.shardingsphere.sharding.spi.ShardingAlgorithm` with type `null`.
1

为了方便测试，先设置只在 t_order0表上进行测试，测试分库策略-行表达式分片算法（当前未配置分表策略）

shardingsphere-sharding2.yaml

#规则配置
rules:
- !SHARDING
  tables:
    # 逻辑表名
    t_user:
      # 值由数据源名 + 表名组成，以小数点分隔。
      actualDataNodes: server-user.t_user
    t_order:
      actualDataNodes: server-order${0..1}.t_order0
      #分库策略
      databaseStrategy:
        standard:
          # 分片列名称
          shardingColumn: user_id
          # 分片算法名称
          shardingAlgorithmName: alg_inline_userid
  # 分片算法配置
  shardingAlgorithms:
    # 标准分片算法-行表达式分片算法
    alg_inline_userid:
      type: INLINE
      props:
        algorithm-expression: server-order${user_id % 2}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

    /**
     * 水平分片：分库插入数据测试
     */
    @Test
    public void testInsertOrderDatabaseStrategy(){

        for (long i = 1; i <= 4; i++) {
            Order order = new Order();
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(i);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2023-08-17 18:58:27.716  INFO 8660 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 18:58:27.716  INFO 8660 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692128729070862338, SP202308140001, 1, 100]
2023-08-17 18:58:27.742  INFO 8660 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 18:58:27.743  INFO 8660 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692128753561403393, SP202308140002, 2, 100]
2023-08-17 18:58:27.749  INFO 8660 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 18:58:27.749  INFO 8660 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692128753561403394, SP202308140003, 3, 100]
2023-08-17 18:58:27.754  INFO 8660 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 18:58:27.754  INFO 8660 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692128753561403395, SP202308140004, 4, 100]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

user_id为1,3（奇数）的数据保存在server-order1数据源的t_order_0表，user_id为2,4（偶数）的数据保存在server-order0数据源的t_order_0表

server-order0

server-order1

水平分表

分片规则：server-order0服务器中，id为偶数时，数据插入t_order0，id为奇数时，数据插入t_order1。

shardingsphere-sharding3.yaml

# 数据源配置
dataSources:
  server-user:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3301/db_user
    username: root
    password: 123456
  server-order0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3310/db_order
    username: root
    password: 123456
  server-order1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3311/db_order
    username: root
    password: 123456
#规则配置
rules:
- !SHARDING
  tables:
    # 逻辑表名
    t_order:
      actualDataNodes: server-order0.t_order${0..1}
      #分表策略
      tableStrategy:
        standard:
          # 分片列名称
          shardingColumn: id
          # 分片算法名称
          shardingAlgorithmName: alg_inline_id
  # 分片算法配置
  shardingAlgorithms:
    # 标准分片算法-行表达式分片算法
    alg_inline_id:
      #基于行表达式的分片算法
      type: INLINE
      props:
        algorithm-expression: t_order${id % 2} 
#属性配置
props:
  sql-show: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

执行

    /**
     * 水平分片：分表插入数据测试
     */
    @Test
    public void testInsertOrderTableStrategy(){
        for (long i = 1; i <= 10; i++) {
            Order order = new Order();
            order.setId(i);
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(i);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

id为2,4插入到t_order0表中，id为1,3插入到t_order1表中

2023-08-17 21:59:30.107  INFO 24352 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 21:59:30.107  INFO 24352 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1, SP202308140001, 1, 100]
2023-08-17 21:59:30.139  INFO 24352 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 21:59:30.139  INFO 24352 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [2, SP202308140002, 2, 100]
2023-08-17 21:59:30.144  INFO 24352 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 21:59:30.144  INFO 24352 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [3, SP202308140003, 3, 100]
2023-08-17 21:59:30.148  INFO 24352 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 21:59:30.148  INFO 24352 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [4, SP202308140004, 4, 100]
2023-08-17 21:59:30.151  INFO 24352 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

分库分表

分片规则：order表中user_id为偶数时，数据插入server-order0服务器，user_id为奇数时，数据插入server-order1服务器。

id为偶数时，数据插入t_order0，id为奇数时，数据插入t_order1。

shardingsphere-sharding4.yaml

# 数据源配置
dataSources:
  server-user:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3301/db_user
    username: root
    password: 123456
  server-order0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3310/db_order
    username: root
    password: 123456
  server-order1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3311/db_order
    username: root
    password: 123456
#规则配置
rules:
- !SHARDING
  tables:
    # 逻辑表名
    t_order:
      actualDataNodes: server-order${0..1}.t_order${0..1}
      #分库策略
      databaseStrategy:
        standard:
          # 分片列名称
          shardingColumn: user_id
          # 分片算法名称
          shardingAlgorithmName: alg_inline_userid
      #分表策略
      tableStrategy:
        standard:
          # 分片列名称
          shardingColumn: id
          # 分片算法名称
          shardingAlgorithmName: alg_inline_id
  # 分片算法配置
  shardingAlgorithms:
    # 标准分片算法-行表达式分片算法
    alg_inline_userid:
      #基于行表达式的分片算法
      type: INLINE
      props:
        algorithm-expression: server-order${user_id % 2}
    alg_inline_id:
      #基于行表达式的分片算法
      type: INLINE
      props:
        algorithm-expression: t_order${id % 2}
#属性配置
props:
  sql-show: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

    /**
     * 水平分片：分库分表插入数据
     */
    @Test
    public void testInsertOrderDatabaseAndTableStrategy(){
        for (long i = 1; i < 5; i++) {

            Order order = new Order();
            order.setId(i);
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(1L);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
        }

        for (long i = 5; i < 9; i++) {
            Order order = new Order();
            order.setId(i);
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(2L);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

可以看到userId为偶数的插入到server-order0，userId为奇数的插入到server-order1；

id为偶数的插入到t_table0，id为奇数的插入到table1中。

2023-08-17 22:09:59.399  INFO 23180 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 22:09:59.399  INFO 23180 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1, SP202308140001, 1, 100]
2023-08-17 22:09:59.443  INFO 23180 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 22:09:59.443  INFO 23180 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [2, SP202308140002, 1, 100]
2023-08-17 22:09:59.448  INFO 23180 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 22:09:59.448  INFO 23180 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [3, SP202308140003, 1, 100]
2023-08-17 22:09:59.452  INFO 23180 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 22:09:59.452  INFO 23180 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [4, SP202308140004, 1, 100]
2023-08-17 22:09:59.457  INFO 23180 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 22:09:59.457  INFO 23180 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [5, SP202308140005, 2, 100]
2023-08-17 22:09:59.462  INFO 23180 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 22:09:59.462  INFO 23180 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [6, SP202308140006, 2, 100]
2023-08-17 22:09:59.465  INFO 23180 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 22:09:59.465  INFO 23180 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [7, SP202308140007, 2, 100]
2023-08-17 22:09:59.468  INFO 23180 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-17 22:09:59.468  INFO 23180 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [8, SP202308140008, 2, 100]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

自动分片算法

为了帮助用户更好的使用分片功能，降低配置复杂度和提升使用体验，Apache ShardingSphere 5.0.0 版本推出了一种新的分片配置方式：AutoTable。

自动分片算法的分片逻辑由 ShardingSphere 自动管理，需要通过配置 autoTables 分片规则进行使用。

取模分片算法

shardingsphere-sharding5.yaml

使用取模分片算法，总共4个分片，两个数据源，每个数据源中有两个分片

# 数据源配置
dataSources:
  server-order0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3310/db_order
    username: root
    password: 123456
  server-order1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3311/db_order
    username: root
    password: 123456
#规则配置
rules:
  - !SHARDING
    autoTables:
      # 逻辑表名
      t_order:
        actualDataSources : server-order${0..1}
        #分片策略
        shardingStrategy:
          standard:
            # 分片列名称
            shardingColumn: user_id
            # 分片算法名称
            shardingAlgorithmName: alg_mod
    # 分片算法配置
    shardingAlgorithms:
      alg_mod:
        #自动分片算法-取模分片算法
        type: MOD
        props:
          sharding-count: 4
#属性配置
props:
  sql-show: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

这里值得注意的一点是自动分片算法的分片逻辑由 ShardingSphere 自动管理，所以之前创建的表可能不符合自动分片规则的，可以让shardingsphere根据自动分片规则为我们创建对应的表

    /**
     * 自动分片创建数据库表
     */
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Test
    public void testCreateAutoOrderMod() {
        jdbcTemplate.execute("CREATE TABLE t_order (\n" +
                "  id BIGINT,\n" +
                "  order_no VARCHAR(30),\n" +
                "  user_id BIGINT,\n" +
                "  amount DECIMAL(10,2),\n" +
                "  PRIMARY KEY(id) USING BTREE\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;");
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

将之前创建的表删除后执行

2023-08-18 07:59:52.896  INFO 10544 --- [           main] ShardingSphere-SQL                       : Logic SQL: CREATE TABLE t_order (
  id BIGINT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
2023-08-18 07:59:52.896  INFO 10544 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: CREATE TABLE t_order_1 (
  id BIGINT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
2023-08-18 07:59:52.896  INFO 10544 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: CREATE TABLE t_order_3 (
  id BIGINT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
2023-08-18 07:59:52.896  INFO 10544 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: CREATE TABLE t_order_0 (
  id BIGINT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
2023-08-18 07:59:52.896  INFO 10544 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: CREATE TABLE t_order_2 (
  id BIGINT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

很明显ShardingSphere根据分片规则为我们在server-order0创建了t_order_0、t_order_2；在server-order1中创建了t_order_1、t_order_3,这与我们之前手动创建的表名还是有些区别的。

插入数据

    /**
     * 分片插入数据测试
     */
    @Test
    public void testInsertOrderShardingStrategy(){

        for (long i = 1; i < 5; i++) {
            Order order = new Order();
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(1L);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
        }

        for (long i = 5; i < 9; i++) {
            Order order = new Order();
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(2L);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
        }

        for (long i = 10; i < 15; i++) {
            Order order = new Order();
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(3L);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
        }
        for (long i = 15; i < 19; i++) {
            Order order = new Order();
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(4L);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

根据取模分片算法推算下，userId为1的数据会插入到第一个分片server-order1的t_order1表中

userId为2的数据会插入到第一个分片server-order0的t_order2表中

userId为3的数据会插入到第一个分片server-order1的t_order3表中

userId为4的数据会插入到第一个分片server-order1的t_order0表中

执行果然如此

2023-08-18 08:06:31.169  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.169  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327051316428801, SP202308140001, 1, 100]
2023-08-18 08:06:31.207  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.208  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074754199554, SP202308140002, 1, 100]
2023-08-18 08:06:31.212  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.212  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074821308417, SP202308140003, 1, 100]
2023-08-18 08:06:31.214  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.214  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074821308418, SP202308140004, 1, 100]
2023-08-18 08:06:31.215  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.215  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_2  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074821308419, SP202308140005, 2, 100]
2023-08-18 08:06:31.219  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.219  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_2  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074821308420, SP202308140006, 2, 100]
2023-08-18 08:06:31.223  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.223  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_2  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074821308421, SP202308140007, 2, 100]
2023-08-18 08:06:31.225  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.225  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_2  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074821308422, SP202308140008, 2, 100]
2023-08-18 08:06:31.227  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.227  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_3  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074888417282, SP2023081400010, 3, 100]
2023-08-18 08:06:31.229  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.229  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_3  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074888417283, SP2023081400011, 3, 100]
2023-08-18 08:06:31.231  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.231  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_3  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074888417284, SP2023081400012, 3, 100]
2023-08-18 08:06:31.233  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.233  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_3  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074888417285, SP2023081400013, 3, 100]
2023-08-18 08:06:31.235  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.235  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_3  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074888417286, SP2023081400014, 3, 100]
2023-08-18 08:06:31.236  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.236  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074888417287, SP2023081400015, 4, 100]
2023-08-18 08:06:31.239  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.239  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074888417288, SP2023081400016, 4, 100]
2023-08-18 08:06:31.241  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.241  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074888417289, SP2023081400017, 4, 100]
2023-08-18 08:06:31.243  INFO 21164 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-18 08:06:31.244  INFO 21164 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692327074951331842, SP2023081400018, 4, 100]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187

哈希取模分片算法

shardingsphere-sharding6.yaml

# 数据源配置
dataSources:
  server-user:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3301/db_user
    username: root
    password: 123456
  server-order0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3310/db_order
    username: root
    password: 123456
  server-order1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.122.150:3311/db_order
    username: root
    password: 123456
#规则配置
rules:
- !SHARDING
  autoTables:
    # 逻辑表名
    t_order:
      actualDataSources : server-order${0..1}
      #分片策略
      shardingStrategy:
        standard:
          # 分片列名称
          shardingColumn: order_no
          # 分片算法名称
          shardingAlgorithmName: alg_hash_mod
  # 分片算法配置
  shardingAlgorithms:
    alg_hash_mod:
      type: HASH_MOD
      props:
        sharding-count: 4
#属性配置
props:
  sql-show: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

order_no是个字符串类型，值比如为"SP2023081400001"，不能直接取模，这样就不能对order_no使用取模分片算法，所以针对order_no使用哈希取模分片算法，注意这里的分片规则是order_no的hash值，hash值对4取模就可以看出该订单落在那个分片上。

执行

    /**
     * 哈希取模
     */
    @Test
    public void testInsertOrderHashModStrategy(){

        for (long i = 1; i < 10; i++) {
            Order order = new Order();
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(1L);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
            System.out.println("对订单号的hash值取模：" + order.getOrderNo().hashCode() % 4);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2023-08-19 08:24:05.112  INFO 20980 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-19 08:24:05.112  INFO 20980 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692693855566049282, SP202308140001, 1, 100]
对订单号的hash值取模：0
2023-08-19 08:24:05.164  INFO 20980 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-19 08:24:05.164  INFO 20980 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_3  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692693883210706946, SP202308140002, 1, 100]
对订单号的hash值取模：-3
2023-08-19 08:24:05.176  INFO 20980 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-19 08:24:05.176  INFO 20980 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_2  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692693883277815809, SP202308140003, 1, 100]
对订单号的hash值取模：-2
2023-08-19 08:24:05.182  INFO 20980 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-19 08:24:05.182  INFO 20980 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692693883340730370, SP202308140004, 1, 100]
对订单号的hash值取模：-1
2023-08-19 08:24:05.186  INFO 20980 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-19 08:24:05.186  INFO 20980 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692693883340730371, SP202308140005, 1, 100]
对订单号的hash值取模：0
2023-08-19 08:24:05.191  INFO 20980 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-19 08:24:05.191  INFO 20980 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_3  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692693883340730372, SP202308140006, 1, 100]
对订单号的hash值取模：-3
2023-08-19 08:24:05.196  INFO 20980 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-19 08:24:05.196  INFO 20980 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_2  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692693883407839233, SP202308140007, 1, 100]
对订单号的hash值取模：-2
2023-08-19 08:24:05.200  INFO 20980 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-19 08:24:05.200  INFO 20980 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692693883407839234, SP202308140008, 1, 100]
对订单号的hash值取模：-1
2023-08-19 08:24:05.204  INFO 20980 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-19 08:24:05.204  INFO 20980 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1692693883407839235, SP202308140009, 1, 100]
对订单号的hash值取模：0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

这里只是简单的介绍下AuTotable,更多分片算法可以参考：

https://blog.csdn.net/ctwy291314/article/details/130561221

2.5、分布式序列算法

实现动机

传统数据库软件开发中，主键自动生成技术是基本需求。而各个数据库对于该需求也提供了相应的支持，比如 MySQL 的自增键，Oracle 的自增序列等。数据分片后，不同数据节点生成全局唯一主键是非常棘手的问题。同一个逻辑表内的不同实际表之间的自增键由于无法互相感知而产生重复主键。虽然可通过约束自增主键初始值和步长的方式避免碰撞，但需引入额外的运维规则，使解决方案缺乏完整性和可扩展性。

目前有许多第三方解决方案可以完美解决这个问题，如 UUID 等依靠特定算法自生成不重复键，或者通过引入主键生成服务等。为了方便用户使用、满足不同用户不同使用场景的需求， Apache ShardingSphere 不仅提供了内置的分布式主键生成器，例如 UUID、SNOWFLAKE，还抽离出分布式主键生成器的接口，方便用户自行实现自定义的自增主键生成器。

内置的主键生成器

UUID

采用 UUID.randomUUID() 的方式产生分布式主键。

SNOWFLAKE

在分片规则配置模块可配置每个表的主键生成策略，默认使用雪花算法（snowflake）生成 64bit 的长整型数据。

雪花算法是由 Twitter 公布的分布式主键生成算法，它能够保证不同进程主键的不重复性，以及相同进程主键的有序性。

实现原理

在同一个进程中，它首先是通过时间位保证不重复，如果时间相同则是通过序列位保证。同时由于时间位是单调递增的，且各个服务器如果大体做了时间同步，那么生成的主键在分布式环境可以认为是总体有序的，这就保证了对索引字段的插入的高效性。例如 MySQL 的 Innodb 存储引擎的主键。

使用雪花算法生成的主键，二进制表示形式包含 4 部分，从高位到低位分表为：1bit 符号位、41bit 时间戳位、10bit 工作进程位以及 12bit 序列号位。

符号位（1bit）

预留的符号位，恒为零。

时间戳位（41bit）

41 位的时间戳可以容纳的毫秒数是 2 的 41 次幂，一年所使用的毫秒数是：365 * 24 * 60 * 60 * 1000。通过计算可知：

Math.pow(2, 41) / (365 * 24 * 60 * 60 * 1000L);
1

结果约等于 69.73 年。 Apache ShardingSphere 的雪花算法的时间纪元从 2016年11月1日 零点开始，可以使用到 2086 年，相信能满足绝大部分系统的要求。

工作进程位（10bit）

该标志在 Java 进程内是唯一的，如果是分布式应用部署应保证每个工作进程的 id 是不同的。该值默认为 0，可通过属性设置。

序列号位（12bit）

该序列是用来在同一个毫秒内生成不同的 ID。如果在这个毫秒内生成的数量超过 4096 (2 的 12 次幂)，那么生成器会等待到下个毫秒继续生成。

雪花算法主键的详细结构见下图。

时钟回拨

服务器时钟回拨会导致产生重复序列，因此默认分布式主键生成器提供了一个最大容忍的时钟回拨毫秒数。如果时钟回拨的时间超过最大容忍的毫秒数阈值，则程序报错；如果在可容忍的范围内，默认分布式主键生成器会等待时钟同步到最后一次主键生成的时间后再继续工作。最大容忍的时钟回拨毫秒数的默认值为 0，可通过属性设置。

雪花算法：

水平分片需要关注全局序列，因为不能简单的使用基于数据库的主键自增。

这里有两种方案：一种是基于MyBatisPlus的id策略；一种是ShardingSphere-JDBC的全局序列配置。

基于MyBatisPlus的id策略：将Order类的id设置成如下形式

@TableId(type = IdType.ASSIGN_ID)
private Long id;
1
2

基于ShardingSphere-JDBC的全局序列配置：和前面的MyBatisPlus的策略二选一

配置分布式序列策略配置和分布式序列算法配置

#规则配置
rules:
- !SHARDING
  autoTables:
    # 逻辑表名
    t_order:
      actualDataSources : server-order${0..1}
      #分片策略
      shardingStrategy:
        standard:
          # 分片列名称
          shardingColumn: order_no
          # 分片算法名称
          shardingAlgorithmName: alg_hash_mod
      #分布式序列策略配置
      keyGenerateStrategy:
      # 分布式序列列名称
        column: id
        # 分布式序列算法名称
        keyGeneratorName: snowflake
  # 分片算法配置
  shardingAlgorithms:
    alg_hash_mod:
      type: HASH_MOD
      props:
        sharding-count: 4
  # 分布式序列算法配置      
  keyGenerator
    snowflake:
      # 分布式序列算法类型
      type: SNOWFLAKE      
#属性配置
props:
  sql-show: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

此时，需要将实体类中的id策略修改成以下形式：

//当配置了shardingsphere-jdbc的分布式序列时，自动使用shardingsphere-jdbc的分布式序列
//当没有配置shardingsphere-jdbc的分布式序列时，自动依赖数据库的主键自增策略
@TableId(type = IdType.AUTO)
1
2
3

执行方法

    /**
     * 哈希取模
     */
    @Test
    public void testInsertOrderHashModStrategy(){

        for (long i = 1; i < 10; i++) {
            Order order = new Order();
            order.setOrderNo("SP20230814000" + i);
            order.setUserId(1L);
            order.setAmount(new BigDecimal(100));
            orderMapper.insert(order);
            System.out.println("对订单号的hash值取模：" + order.getOrderNo().hashCode() % 4);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2023-08-20 10:12:23.145  INFO 27620 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-20 10:12:23.145  INFO 27620 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1693083499130646529, SP202308140001, 1, 100]
对订单号的hash值取模：0
2023-08-20 10:12:23.179  INFO 27620 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-20 10:12:23.179  INFO 27620 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_3  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1693083525789642754, SP202308140002, 1, 100]
对订单号的hash值取模：-3
2023-08-20 10:12:23.185  INFO 27620 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-20 10:12:23.185  INFO 27620 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_2  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1693083525789642755, SP202308140003, 1, 100]
对订单号的hash值取模：-2
2023-08-20 10:12:23.188  INFO 27620 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-20 10:12:23.188  INFO 27620 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1693083525789642756, SP202308140004, 1, 100]
对订单号的hash值取模：-1
2023-08-20 10:12:23.192  INFO 27620 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-20 10:12:23.192  INFO 27620 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1693083525852557313, SP202308140005, 1, 100]
对订单号的hash值取模：0
2023-08-20 10:12:23.196  INFO 27620 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-20 10:12:23.196  INFO 27620 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_3  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1693083525852557314, SP202308140006, 1, 100]
对订单号的hash值取模：-3
2023-08-20 10:12:23.199  INFO 27620 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-20 10:12:23.200  INFO 27620 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_2  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1693083525852557315, SP202308140007, 1, 100]
对订单号的hash值取模：-2
2023-08-20 10:12:23.203  INFO 27620 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-20 10:12:23.203  INFO 27620 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order1 ::: INSERT INTO t_order_1  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1693083525852557316, SP202308140008, 1, 100]
对订单号的hash值取模：-1
2023-08-20 10:12:23.206  INFO 27620 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( id,
order_no,
user_id,
amount )  VALUES  ( ?,
?,
?,
? )
2023-08-20 10:12:23.207  INFO 27620 --- [           main] ShardingSphere-SQL                       : Actual SQL: server-order0 ::: INSERT INTO t_order_0  ( id,
order_no,
user_id,
amount )  VALUES  (?, ?, ?, ?) ::: [1693083525852557317, SP202308140009, 1, 100]
对订单号的hash值取模：0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

分布式序列算法

https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/keygen/

相关阅读:
Web3时代的战争
 【攻防世界】学习记录-crypto（系数2 part2）
解决YYYY-MM-dd格式化日期获得的年份不正确问题
 RPA流程调试：准确定位错误原因及位置
 索引优化与查询优化
 线上FullGC问题排查实践——手把手教你排查线上问题
 科技云报道：车云协同，云计算下一个主战场？
Django网站开发技术的应用（理论篇）
《动手学深度学习 Pytorch版》 7.2 使用块的网络(VGG)
一文搞懂│php 中的 DI 依赖注入
原文地址：https://blog.csdn.net/m0_37450089/article/details/132863688