【分表分库】SpringBoot整合ShardingSphere---Sharding-JDBC

ShardingSphere介绍

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。

ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。 它与NoSQL和NewSQL是并存而非互斥的关系。NoSQL和NewSQL作为新技术探索的前沿，放眼未来，拥抱变化，是非常值得推荐的。反之，也可以用另一种思路看待问题，放眼未来，关注不变的东西，进而抓住事物本质。 关系型数据库当今依然占有巨大市场，是各个公司核心业务的基石，未来也难于撼动，我们目前阶段更加关注在原有基础上的增量，而非颠覆。

ShardingSphere已经在2020年4月16日从Apache孵化器毕业，成为Apache顶级项目。

Sharding-JDBC

定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

Sharding-Proxy

定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。 目前先提供MySQL/PostgreSQL版本，它可以使用任何兼容MySQL/PostgreSQL协议的访问客户端(如：MySQL Command Client, MySQL Workbench, Navicat等)操作数据，对DBA更加友好。

向应用程序完全透明，可直接当做MySQL/PostgreSQL使用。
适用于任何兼容MySQL/PostgreSQL协议的的客户端。

Sharding-Sidecar（TODO）

定位为Kubernetes的云原生数据库代理，以Sidecar的形式代理所有对数据库的访问。 通过无中心、零侵入的方案提供与数据库交互的的啮合层，即Database Mesh，又可称数据网格。

Database Mesh的关注重点在于如何将分布式的数据访问应用与数据库有机串联起来，它更加关注的是交互，是将杂乱无章的应用与数据库之间的交互有效的梳理。使用Database Mesh，访问数据库的应用和数据库终将形成一个巨大的网格体系，应用和数据库只需在网格体系中对号入座即可，它们都是被啮合层所治理的对象。

正文

整合Sharding-JDBC 4.1.1官方文档

逻辑表：水平拆分的数据库的相同逻辑和数据结构表的总称
真实表：在分片的数据库中真实存在的物理表。
数据节点：数据分片的最小单元。由数据源名称和数据表组成
绑定表：分片规则一致的主表和子表。
广播表：也叫公共表，指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中都完全一致。例如字典表。
分片键：用于分片的数据库字段，是将数据库(表)进行水平拆分的关键字段。SQL中若没有分片字段，将会执行全路由，性能会很差。
分片算法：通过分片算法将数据进行分片，支持通过=、BETWEEN和IN分片。分片算法需要由应用开发者自行实现，可实现的灵活度非常高。
分片策略：真正用于进行分片操作的是分片键+分片算法，也就是分片策略。在ShardingJDBC中一般采用基于Groovy表达式的inline分片策略，通过一个包含分片键的算法表达式来制定分片策略，如t_user_$->{u_id%8}标识根据u_id模8，分成8张表，表名称为t_user_0到t_user_7。

准备环境

拆分表，需求按年份来区分数据

CREATE TABLE `course_0` (
  `id` varchar(20) CHARACTER SET utf8 NOT NULL,
  `create_time` timestamp(6) NULL DEFAULT NULL COMMENT '创建时间',
  `create_by` varchar(20) DEFAULT NULL COMMENT '创建人',
  `update_time` timestamp(6) NULL DEFAULT NULL COMMENT '修改时间',
  `update_by` varchar(20) DEFAULT NULL COMMENT '修改人',
  `isdelete` int(2) NOT NULL DEFAULT '0' COMMENT '是否删除',
  `year` int(11) NULL DEFAULT NULL COMMENT '年份',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `course_1` (
  `id` varchar(20) CHARACTER SET utf8 NOT NULL,
  `create_time` timestamp(6) NULL DEFAULT NULL COMMENT '创建时间',
  `create_by` varchar(20) DEFAULT NULL COMMENT '创建人',
  `update_time` timestamp(6) NULL DEFAULT NULL COMMENT '修改时间',
  `update_by` varchar(20) DEFAULT NULL COMMENT '修改人',
  `isdelete` int(2) NOT NULL DEFAULT '0' COMMENT '是否删除',
  `year` int(11) NULL DEFAULT NULL COMMENT '年份',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

......
按照需求预计拆分对应的表数量，我这边用年份来区分每年产生的数据，所以建了10个
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Maven依赖：

• sharding-jdbc 4.1.1
• mysql 8.0.12
• druid 8.0.12
• spring-boot 2.3.8
• mybatis-plus 3.3.1

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.3.8.RELEASE</version>
    <relativePath/>
</parent>

<dependencies>

<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>4.1.1</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.12</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.22</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.1</version>
</dependency>

</dependencies>
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

YML配置

spring:
  shardingsphere:
    # SQL日志打印
    props:
      sql:
        show: true
    datasource:
      # 配置库名 多个数据库使用逗号分割如: m1,m2
      names: m1
    #配置数据源具体内容，
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url : jdbc:mysql://127.0.0.1:3306/chazhi
        username: root
        password: root
    sharding:
      tables:
        course:
          # 分表节点 多个数据库写法 m$->{1..2}.course_$->{0..10}
          actual-data-nodes: m1.course_$->{0..10}
          # 配置主键生成策略 采用雪花算法
          key-generator:
            column: id
            type: SNOWFLAKE
          # 分表策略
          table-strategy:
            # 行表达式分片策略
            inline:
              sharding-column: year
              # Groovy的表达式分片算法
              algorithm-expression: course_$->{year-2022}
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

保存数据

@PostMapping("/save")
public void demoTestOne(){
    for (int i = 0; i < 100; i++) {
        Course course = new Course();
        // DateUtil.yearOf(DateUtil.parse(getAddMonth(i))) 按月份自增i月后取出年份
        course.setYear(DateUtil.yearOf(DateUtil.parse(getAddMonth(i))));
        course.setUpdateTime(LocalDateTime.now());
        course.setCreateTime(LocalDateTime.now());
        courseMapper.insert(course);
    }
}
1
2
3
4
5
6
7
8
9
10
11

日志：可以看到步骤是先输出逻辑SQL，然后分片算法计算所在节点，最后执行最终SQL
我们的表结构year字段是int类型的，分片算法是{year-2022}，保存时Year字段设置的时当前时间也就是2023年

2023-01-29 09:15:04.721  INFO 26088 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2023-01-29 09:15:04.721  INFO 26088 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2023-01-29 09:15:04.725  INFO 26088 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 4 ms
2023-01-29 09:15:05.022  INFO 26088 --- [nio-8080-exec-1] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( id,create_time,update_time,year )  VALUES  ( ?,?,?,? )
2023-01-29 09:15:05.022  INFO 26088 --- [nio-8080-exec-1] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@7ffc371, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@133b2dc9), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@133b2dc9, columnNames=[id, create_time, update_time, year], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=73, stopIndex=73, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=76, stopIndex=76, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=80, stopIndex=80, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=85, stopIndex=85, parameterMarkerIndex=3)], parameters=[1619504367312351234, 2023-01-29 09:15:04.74, 2023-01-29 09:15:04.74, 2023])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=id, generated=false, generatedValues=[1619504367312351234])])
2023-01-29 09:15:05.022  INFO 26088 --- [nio-8080-exec-1] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( id,create_time,update_time,year )  VALUES  (?, ?, ?, ?) ::: [1619504367312351234, 2023-01-29 09:15:04.74, 2023-01-29 09:15:04.74, 2023]
1
2
3
4
5
6

查看数据库结果

我们可以看到数据表course_0是没有一行数据的，这是因为我们是从2022年开始计算分片节点的，我们保存数据的时间是在2023年所以course_0是没有数据的。

我们再看表course_1，这个时候就有数据了，看到这里说明我们的分片算法没问题

数据查询

支持分页、去重、排序、分组、聚合、关联查询（不支持跨库关联）、子查询不支持多级子查询只支持一级
具体使用规范查询官方文档，数据分片-SQL使用规范

--支持
SELECT COUNT(*) FROM (SELECT * FROM t_order o)

--不支持
SELECT COUNT(*) FROM (SELECT * FROM t_order o WHERE o.id IN (SELECT id FROM t_order WHERE status = ?))
1
2
3
4
5

因为我们使用的是以年份来进行数据分片的，所以我们查询的时候就分为了带年份查询与不带年份查询

带年份查询

@GetMapping("/shardingQueryYear")
public Object shardingQueryYear(Long year){
    QueryWrapper<Course> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("year",year);
    return courseMapper.selectList(queryWrapper);
}
1
2
3
4
5
6

带分片键查询则可以计算出对应的表，如下查询了course_1 表，而不是查所有表。

2023-01-29 10:30:36.317  INFO 24776 --- [nio-8080-exec-4] ShardingSphere-SQL                       : Logic SQL: SELECT  id,create_time,create_by,update_time,update_by,isdelete,year  FROM course WHERE  isdelete=0 AND (year = ?)
2023-01-29 10:30:36.317  INFO 24776 --- [nio-8080-exec-4] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@42bf1b2e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@88a7589), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@88a7589, projectionsContext=ProjectionsContext(startIndex=8, stopIndex=67, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty), ColumnProjection(owner=null, name=create_by, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=update_by, alias=Optional.empty), ColumnProjection(owner=null, name=isdelete, alias=Optional.empty), ColumnProjection(owner=null, name=year, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@64d8ae8, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@2721ef9, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@477080d3, containsSubquery=false)
2023-01-29 10:30:36.317  INFO 24776 --- [nio-8080-exec-4] ShardingSphere-SQL                       : Actual SQL: m1 ::: SELECT  id,create_time,create_by,update_time,update_by,isdelete,year FROM course_1 WHERE  isdelete=0 AND (year = ?) ::: [2023]
1
2
3

不带年份查询

注意：行内分片算法不支持范围查询

@GetMapping("/shardingQuery")
public Object shardingQuery(Long id){
    Course byId = courseMapper.selectById(id);
    return byId;
}
1
2
3
4
5

不带分片键查询则是查询所有表，然后汇总结果返回。

2023-01-29 10:24:32.452  INFO 27332 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2023-01-29 10:24:32.452  INFO 27332 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2023-01-29 10:24:32.456  INFO 27332 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 4 ms
2023-01-29 10:24:32.768  INFO 27332 --- [nio-8080-exec-1] ShardingSphere-SQL                       : Logic SQL: SELECT id,create_time,create_by,update_time,update_by,isdelete,year FROM course WHERE id=?  AND isdelete=0
2023-01-29 10:24:32.769  INFO 27332 --- [nio-8080-exec-1] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5d11c6fb, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a750ff0), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a750ff0, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=66, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty), ColumnProjection(owner=null, name=create_by, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=update_by, alias=Optional.empty), ColumnProjection(owner=null, name=isdelete, alias=Optional.empty), ColumnProjection(owner=null, name=year, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@3193be9f, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@12683709, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@419d0da1, containsSubquery=false)
2023-01-29 10:24:32.769  INFO 27332 --- [nio-8080-exec-1] ShardingSphere-SQL                       : Actual SQL: m1 ::: SELECT id,create_time,create_by,update_time,update_by,isdelete,year FROM course_0 WHERE id=?  AND isdelete=0 ::: [1619504369304645634]
2023-01-29 10:24:32.769  INFO 27332 --- [nio-8080-exec-1] ShardingSphere-SQL                       : Actual SQL: m1 ::: SELECT id,create_time,create_by,update_time,update_by,isdelete,year FROM course_1 WHERE id=?  AND isdelete=0 ::: [1619504369304645634]
2023-01-29 10:24:32.769  INFO 27332 --- [nio-8080-exec-1] ShardingSphere-SQL                       : Actual SQL: m1 ::: SELECT id,create_time,create_by,update_time,update_by,isdelete,year FROM course_2 WHERE id=?  AND isdelete=0 ::: [1619504369304645634]
1
2
3
4
5
6
7
8

以上就是基本分表的使用了，删除修改与查询类似都是需要指定具体的分片键才能对单表修改。

自定义分片算法

自定义分片算法，灵活度很高，可以满足很多场景只要开发者能实现即可分表分库都可以使用

精准分片算法

实现PreciseShardingAlgorithm接口doSharding方法

public class MyDBPreciseSharding implements PreciseShardingAlgorithm<Date> {
	
	/**
     * 开始分片数据时间
     */
    private static final int START_TIME = 2023;

    /**
     * Sharding.
     *
     * @param availableTargetNames available data sources or tables's names
     * @param shardingValue        sharding value
     * @return sharding result for data source or table's name
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
        Date year = shardingValue.getValue();
        int yearOf = DateUtil.yearOf(year);
        int index = yearOf - START_TIME;
        String logicTableName = shardingValue.getLogicTableName();
        String tableName = logicTableName + "_" + index;
        if (availableTargetNames.contains(tableName)) {
            return tableName;
        }
        throw new UnsupportedOperationException("route " + tableName + " is not supported. please check your config");
    }
}

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

范围分片算法

实现RangeShardingAlgorithm接口doSharding方法

public class MyDBRangeSharding implements RangeShardingAlgorithm<Date> {

    /**
     * Sharding.
     *
     * @param availableTargetNames available data sources or tables's names
     * @param shardingValue        sharding value
     * @return sharding results for data sources or tables's names
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> shardingValue) {
        Date lower = shardingValue.getValueRange().lowerEndpoint();
        Date upper = shardingValue.getValueRange().upperEndpoint();
        List<String> tableNames = new ArrayList<>();
        availableTargetNames.forEach(s -> {
            if (s.startsWith(shardingValue.getLogicTableName())) {
                tableNames.add(s);
            }
        });
        //对于奇偶分离的场景 大概率两个表都要查
        return tableNames;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

复杂分片算法

实现ComplexKeysShardingAlgorithm接口doSharding方法

public class MyDBComplexSharding implements ComplexKeysShardingAlgorithm<Date> {
	
	/**
     * 开始分片数据时间
     */
    private static final int START_TIME = 2023;

    /**
     * Sharding.
     *
     * @param availableTargetNames available data sources or tables's names
     * @param shardingValue        sharding value
     * @return sharding result for data source or table's name
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Date> shardingValue) {
        Date year = shardingValue.getValue();
        int yearOf = DateUtil.yearOf(year);
        int index = yearOf - START_TIME;
        String logicTableName = shardingValue.getLogicTableName();
        String tableName = logicTableName + "_" + index;
        if (availableTargetNames.contains(tableName)) {
            return tableName;
        }
        throw new UnsupportedOperationException("route " + tableName + " is not supported. please check your config");
    }
}

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

Hint分片算法

实现HintShardingAlgorithm接口doSharding方法

public class MyDBHintSharding implements HintShardingAlgorithm<Date> {
	
	/**
     * 开始分片数据时间
     */
    private static final int START_TIME = 2023;

    /**
     * Sharding.
     *
     * @param availableTargetNames available data sources or tables's names
     * @param shardingValue        sharding value
     * @return sharding result for data source or table's name
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, HintShardingValue<Date> shardingValue) {
        Date year = shardingValue.getValue();
        int yearOf = DateUtil.yearOf(year);
        int index = yearOf - START_TIME;
        String logicTableName = shardingValue.getLogicTableName();
        String tableName = logicTableName + "_" + index;
        if (availableTargetNames.contains(tableName)) {
            return tableName;
        }
        throw new UnsupportedOperationException("route " + tableName + " is not supported. please check your config");
    }
}

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

YML配置

spring:
  shardingsphere:
  ...
      sharding:
      tables:
        course:
          ...
          table-strategy:
            # 标准分片策略
            standard:
              # 分片键
              sharding-column: year
              # 精确算法
              precise-algorithm-class-name: com.chazhi.shardingsphere.demo.config.MyDBPreciseSharding
              # 范围算法
              range-algorithm-class-name: com.chazhi.shardingsphere.demo.config.MyDBRangeSharding
            # 复合分片策略
            complex:
              # 复合分片算法
              algorithm-class-name: com.chazhi.shardingsphere.demo.config.MyDBComplexSharding
            # Hint分片策略
            hint:
              # Hint分片算法
              algorithm-class-name: com.chazhi.shardingsphere.demo.config.MyDBHintSharding
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

使用

和行内分片策略不同的是，行内分片的year字段是Int类型，而我自定义分片算法的yaer字段可以是Date类型、String类型

这里我们就是对设置的分片键进行了时间格式处理然后计算分片节点，由于效果与行业分片策略一致就不单独展示了。可以自己去测试效果

总结

分库分表包括分库和分表两个部分，在生产环境中通常包括：垂直分库、水平分库、垂直分表、水平分表四种方式。
分表分库有利有弊，使用前需要讨论好是否使用分表分库，怎么分、分多少。

以上写法仅为测试方便写法，请勿模仿！
好了简单的整合Sharding-JDBC到此结束了！其他后续其他的使用文章吧