ShardingSphere分库分表----取模和按日期分表

悠悠 2022-12-23 14:25 256阅读 0赞

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

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

### 文章目录 ###

*  shardingjdbc分库分表----取模和按时间分表
 *   *  1.前言
     *  2. 取模分库分表配置
     *  3. 分片策略简介
     *  4. 根据日期分库分表配置
     *  5.结语

## 1.前言 ##

最近有个业务需要分库分表，遂研究了下sharing。网上列出了一些取模的分库分表实现，但是按照时间分库分表，或者二者结合的很少，遂在这里记录一下。  
[shrdingsphere官网][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，又可称数据库网格。好像还在**规划中**。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70_pic_center]

这里我选择了JDBC 方式。

## 2. 取模分库分表配置 ##

1.  引入依赖  
    简单的springboot工程

<dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.0.0</version>
            </dependency>
    
            
            <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. 分片策略简介 ##

[分片策略官网简介][Link 1]  
包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供 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！！！**

[shrdingsphere]: https://shardingsphere.apache.org/index_zh.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/38abbb34aeb54df3b4297fd8c4daf0eb.png
[Link 1]: https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/sharding/