shardingjdbc分库分表----取模和按时间分表

每天多学一点点~
话不多说，这就开始吧…

文章目录

1.前言

最近有个业务需要分库分表，遂研究了下sharing。网上列出了一些取模的分库分表实现，但是按照时间分库分表，或者二者结合的很少，遂在这里记录一下。
shrdingsphere官网 各位有兴趣的去官网看看，中文的。。。

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

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

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

在这里插入图片描述

这里我选择了JDBC 方式。

2. 取模分库分表配置

  1. 引入依赖
    简单的springboot工程
      <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>

        <!-- sharding apache最新孵化的版本 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.1</version>
        </dependency>
  1. 规则
    以订单为例，用 user_id 进行取模 分库 ， order_id 进行取模分表
    boke_0 boke_1 两个库，每个库 t_order_0 t_order_1 两张表 ，共4张表
CREATE TABLE `t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单id(雪花算法)',
  `user_id` int(20) NOT NULL COMMENT '用户id',
  `address_id` bigint(20) NOT NULL COMMENT '地址id',
  `status` char(1) DEFAULT NULL COMMENT '订单状态',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  1. properties配置
#配置数据源 boke_1 boke_2
spring.shardingsphere.datasource.names=b0,b1

# boke_0 数据源配置
spring.shardingsphere.datasource.b0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.b0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.b0.jdbc-url=jdbc:mysql://localhost:3306/boke_0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.b0.username=root
spring.shardingsphere.datasource.b0.password=root

# boke_1 数据源配置
spring.shardingsphere.datasource.b1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.b1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.b1.jdbc-url=jdbc:mysql://localhost:3306/boke_1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.b1.username=root
spring.shardingsphere.datasource.b1.password=root


#  分库策略（水平） 以 user_id 取模  进行 分库
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=b$->{user_id % 2}

# 绑定表  t_order 与  t_order_item
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item

#  t_order 分表策略
#  节点 b0.t_order_0,b1.t_order_1,b0.t_order_0,b1.t_order_1
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=b$->{0..1}.t_order_$->{0..1}
#  分表 id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
#  分表策略  order_id 取模
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2}
# 使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
#  雪花算法的workId  机器为标识 0-1024
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123

#  t_order_item 分表策略
# 节点 b0.t_order_item_0,b0.t_order_item_1,b1.t_order_item_0,b1.t_order_item_1
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=b$->{0..1}.t_order_item_$->{0..1}
#  分表策略  order_id 取模
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
# 分表策略 根据id取模,确定数据最终落在那个表中
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
# 使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123

# 广播表 address
spring.shardingsphere.sharding.broadcast-tables=t_address

这样配置好之后，其他就和springboot项目一样，直接用mybatis查询就可。

3. 分片策略简介

分片策略官网简介
包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供 5 种分片策略。

  1. 标准分片策略
    对应 StandardShardingStrategy。提供对 SQ L语句中的 =, >, <, >=, <=, IN 和 BETWEEN AND 的分片操作支持。 StandardShardingStrategy 只支持单分片键，提供 PreciseShardingAlgorithm 和 RangeShardingAlgorithm 两个分片算法。 PreciseShardingAlgorithm 是必选的，用于处理 = 和 IN 的分片。 RangeShardingAlgorithm 是可选的，用于处理 BETWEEN AND, >, <, >=, <=分片，如果不配置 RangeShardingAlgorithm，SQL 中的 BETWEEN AND 将按照全库路由处理。

  2. 复合分片策略
    对应 ComplexShardingStrategy。复合分片策略。提供对 SQL 语句中的 =, >, <, >=, <=, IN 和 BETWEEN AND 的分片操作支持。 ComplexShardingStrategy 支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。

  3. Hint分片策略
    对应 HintShardingStrategy。通过 Hint 指定分片值而非从 SQL 中提取分片值的方式进行分片的策略。

  4. 不分片策略
    对应 NoneShardingStrategy。不分片的策略。

4. 根据日期分库分表配置

  1. 规则
    以订单为例，db 以 订单id 取模 分库， table 以 订单创建时间 分表
    即 boke_0 boke_1 两个库，每个库中 t_order_2020_1 —t_order_2021_12 共24张表。

  2. 写两个类，实现 PreciseShardingAlgorithm 精确分片算法，一个用于db取模，一个用于table按月份分片。
    DBShardingAlgorithm 用于 db 取模

/*
 * @author ：zjq
 * @date ：2020/11/9 10:44
 * @description: TODO       数据表分表策略 TableShardingAlgorithm
 * @version: V1.0
 * @slogan: 天下风云出我辈，一入代码岁月催
 */
@Slf4j
public class DBShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {

        System.out.println("DB  PreciseShardingAlgorithm  ");
        // 真实节点
        availableTargetNames.stream().forEach((item) -> {
            log.info("actual node db:{}", item);
        });

        log.info("logic table name:{},rout column:{}", shardingValue.getLogicTableName(), shardingValue.getColumnName());

        //精确分片
        log.info("column value:{}", shardingValue.getValue());

        long orderId = shardingValue.getValue();

        long db_index = orderId & (2 - 1);

        for (String each : availableTargetNames) {
            if (each.equals("b"+db_index)) {
                return each;
            }
        }

        throw new IllegalArgumentException();
    }
}

TableShardingAlgorithm用于table按月份分片

/* 
 * @author ：zjq
 * @date ：2020/11/9 10:44
 * @description: TODO       数据表分表策略 TableShardingAlgorithm
 * @version: V1.0
 * @slogan: 天下风云出我辈，一入代码岁月催
 */
@Slf4j
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {

        System.out.println("table PreciseShardingAlgorithm ");
        // 真实节点
        availableTargetNames.stream().forEach((item) -> {
            log.info("actual node table:{}", item);
        });

        log.info("logic table name:{},rout column:{}", shardingValue.getLogicTableName(), shardingValue.getColumnName());

        //精确分片
        log.info("column value:{}", shardingValue.getValue());


        String tb_name = shardingValue.getLogicTableName() + "_";


        // 根据当前日期 来 分库分表
        Date date = shardingValue.getValue();
        String year = String.format("%tY", date);
        String mon =String.valueOf(Integer.parseInt(String.format("%tm", date))); // 去掉前缀0
        String dat = String.format("%td", date);


        // 选择表
        tb_name = tb_name + year + "_" + mon;
        System.out.println("tb_name:" + tb_name);

        for (String each : availableTargetNames) {
            System.out.println("t_order_:" + each);
            if (each.equals(tb_name)) {
                return each;
            }
        }

        throw new IllegalArgumentException();
    }
}
  1. properties 配置
    db 以 订单id 取模 table 以 订单创建时间 分库
#配置数据源 boke_1 boke_2
spring.shardingsphere.datasource.names=b0,b1
spring.shardingsphere.sharding.default-data-source-name=b0

# boke_0 数据源配置
spring.shardingsphere.datasource.b0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.b0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.b0.jdbc-url=jdbc:mysql://localhost:3306/boke_0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai&relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.b0.username=root
spring.shardingsphere.datasource.b0.password=root

# boke_1 数据源配置
spring.shardingsphere.datasource.b1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.b1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.b1.jdbc-url=jdbc:mysql://localhost:3306/boke_1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai&relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.b1.username=root
spring.shardingsphere.datasource.b1.password=root


spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=b$->{0..1}.t_order_$->{2020..2021}_$->{1..12}
# 自定义 分片算法
# 分库分片健      database-strategy 数据库策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=order_id
# 自定义 分片 策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.boke.order.sharding.time.DBShardingAlgorithm
#                  table-strategy   表 的 策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.boke.order.sharding.time.TableShardingAlgorithm



# 使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
#  雪花算法的workId  机器为标识 0-1024
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123

这样就大功告成，经过测试，插入，分页查询，范围，精确查询，事务都是ok，但是因为选择了精确分片算法 ，分页和范围查询，会路由到2个db的24张表上，会有效率问题，所以要好好取舍，再研究研究~

5.结语

世上无难事，只怕有心人，每天积累一点点，fighting！！！

Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

操作系统大会&openEuler Summit 2024参会指南，请查收！

avatar 开放原子开发者工作坊

推动工业软件核心技术攻关，开源工业软件算法集成大赛正式启动！

推动工业软件核心技术攻关，开源工业软件算法集成大赛正式启动！

avatar 开放原子开发者工作坊

第二届openEuler生态大会（中国·湖南）成功举办

10月30日，第二届openEuler生态大会（中国·湖南）成功举办。

avatar 开放原子开发者工作坊