当前位置： 首页 > article >正文

sharding-jdbc四种分片策略

article 2025/1/3 0:10:48

一、标准分片策略（standard）

1、精确分片

配置文件

spring:
  shardingsphere:
    #开启sql显示
    props:
      sql:
        show: true
    datasource:
      # 配置数据源
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
    sharding:
      #唯一库数据
      default-data-source-name: db0
      #分库
      default-database-strategy:
        standard:
          # 添加数据分库字段(根据字段插入数据到那个表)
          sharding-column: id
          #精确分片
          precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
      #分表
      tables:
        #表名
        db_user:
          actual-data-nodes: db$->{0..1}.db_user_$->{0..2}
          key-generator:
            column: id  # 主键ID
            type: SNOWFLAKE  # 生成策略雪花id
          table-strategy:
            standard:
              sharding-column: id
              #精确分片
              precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm

分库规则

package com.example.sharding_test.strategy.database;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 * 精确分片
 *
 * @author shuai
 * @since 2023-03-19
 */
public class DatabasePreciseAlgorithm implements PreciseShardingAlgorithm<Long> {
    /**
     * 精确分片
     * @param collection 数据源集合
     * @param preciseShardingValue 分片参数
     * @return 数据库
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        //分片键的值
        Long value = preciseShardingValue.getValue();
        String dbName = "db" + (value % 2);
        if(!collection.contains(dbName)){
           throw new UnsupportedOperationException("数据源"+ dbName + "不存在");
        }
        return dbName;
    }
}

分表规则

package com.example.sharding_test.strategy.table;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 * 精确分片
 *
 * @author shuai
 * @since 2023-03-19
 */
public class TablePreciseAlgorithm implements PreciseShardingAlgorithm<Long> {
    /**
     * 精确分片
     * @param collection 数据源集合
     * @param preciseShardingValue 分片参数
     * @return 数据库
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        //分片键的值
        Long value = preciseShardingValue.getValue();
        String tableName = preciseShardingValue.getLogicTableName() + "_" + (value % 3);
        if(!collection.contains(tableName)){
           throw new UnsupportedOperationException("表"+ tableName + "不存在");
        }
        return tableName;
    }
}

2、范围分片

配置文件

spring:
  shardingsphere:
    #开启sql显示
    props:
      sql:
        show: true
    datasource:
      # 配置数据源
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
    sharding:
      #唯一库数据
      default-data-source-name: db0
      #分库
      default-database-strategy:
        standard:
          # 添加数据分库字段(根据字段插入数据到那个表)
          sharding-column: id
          #精确分片
          precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
          #范围分片
          range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm          
      #分表
      tables:
        #表名
        db_user:
          actual-data-nodes: db$->{0..1}.db_user_$->{0..2}
          key-generator:
            column: id  # 主键ID
            type: SNOWFLAKE  # 生成策略雪花id
          table-strategy:
            standard:
              sharding-column: id
              #精确分片
              precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
              #范围分片
              range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm

分库规则

package com.example.sharding_test.strategy.database;

import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Collection;

/**
 * 范围分片
 *
 * @author shuai
 * @since 2023-03-19
 */
public class DatabaseRangeAlgorithm implements RangeShardingAlgorithm<Long> {
    /**
     * 范围分片
     * @param collection 数据源集合
     * @param rangeShardingValue 分片参数
     * @return 直接返回源
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
        return collection;
    }
}

分表规则

package com.example.sharding_test.strategy.table;

import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Arrays;
import java.util.Collection;

/**
 * 范围分片
 *
 * @author shuai
 * @since 2023-03-19
 */
public class TableRangeAlgorithm implements RangeShardingAlgorithm<Long> {
    /**
     * 范围分片
     * @param collection 数据源集合
     * @param rangeShardingValue 分片参数
     * @return 直接返回源
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
        //逻辑表名称
        String logicTableName = rangeShardingValue.getLogicTableName();
        return Arrays.asList(logicTableName+"_0",logicTableName+"_1",logicTableName+"_2");
    }
}

行表达式分片策略（inline）

配置文件（需要注释其他的分片规则）

spring:
  shardingsphere:
    #开启sql显示
    props:
      sql:
        show: true
    datasource:
      # 配置数据源
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
    sharding:
      #唯一库数据
      default-data-source-name: db0
      #分库
      default-database-strategy:
#        standard:
#          # 添加数据分库字段(根据字段插入数据到那个表)
#          sharding-column: id
#          #精确分片
#          precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
#          #范围分片
#          range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm
        inline:
          # 添加数据分表字段(根据字段插入数据到那个表)
          sharding-column: id
          # 分片算法表达式 => 通过id取余
          algorithm-expression: db$->{id % 2}
      #分表
      tables:
        #表名
        db_user:
          actual-data-nodes: db$->{0..1}.db_user_$->{0..2}
          key-generator:
            column: id  # 主键ID
            type: SNOWFLAKE  # 生成策略雪花id
          table-strategy:
#            standard:
#              sharding-column: id
#              #精确分片
#              precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
#              #范围分片
#              range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm
            inline:
              # 添加数据分表字段(根据字段插入数据到那个表)
              sharding-column: id
              # 分片算法表达式 => 通过id取余
              algorithm-expression: db_user_$->{id % 3}

分库规则与分表规则可直接使用groovy脚本
例：db_user_$->{id % 3}

复合分片策略（complex）

配置文件

spring:
  shardingsphere:
    #开启sql显示
    props:
      sql:
        show: true
    datasource:
      # 配置数据源
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
    sharding:
      #唯一库数据
      default-data-source-name: db0
      #分库
      default-database-strategy:
#        standard:
#          # 添加数据分库字段(根据字段插入数据到那个表)
#          sharding-column: id
#          #精确分片
#          precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
#          #范围分片
#          range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm
#        #行分片
#        inline:
#          # 添加数据分表字段(根据字段插入数据到那个表)
#          sharding-column: id
#          # 分片算法表达式 => 通过id取余
#          algorithm-expression: db$->{id % 2}
        #复合分片
         complex:
           sharding-columns: id,age
           algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseComplexAlgorithm

      #分表
      tables:
        #表名
        db_user:
          actual-data-nodes: db$->{0..1}.db_user_$->{0..2}
          key-generator:
            column: id  # 主键ID
            type: SNOWFLAKE  # 生成策略雪花id
          table-strategy:
#            standard:
#              sharding-column: id
#              #精确分片
#              precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
#              #范围分片
#              range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm
#            inline:
#              # 添加数据分表字段(根据字段插入数据到那个表)
#              sharding-column: id
#              # 分片算法表达式 => 通过id取余
#              algorithm-expression: db_user_$->{id % 3}
            #复合分片
            complex:
              sharding-columns: id,age
              algorithm-class-name: com.example.sharding_test.strategy.table.TableComplexAlgorithm

分库规则

package com.example.sharding_test.strategy.database;

import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.assertj.core.util.Lists;

import java.util.Collection;
import java.util.List;

/**
 * 复合分片
 *
 * @author shuai
 * @since 2023-03-19
 */
public class DatabaseComplexAlgorithm implements ComplexKeysShardingAlgorithm<Integer> {


    /**
     *
     * @param collection 数据源集合
     * @param complexKeysShardingValue 分片键的值集合
     * @return 需要查找的数据源集合
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<Integer> complexKeysShardingValue) {
        //获取age的值
        Collection<Integer> ageValues = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("age");
        List<String> dbs = Lists.newArrayList();
        //通过age取模
        ageValues.forEach(item->{
            String dbName = "db"+((item+3)%2);
            dbs.add(dbName);
        });
        return dbs;
    }
}

分表规则

package com.example.sharding_test.strategy.table;

import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.assertj.core.util.Lists;

import java.util.Arrays;
import java.util.Collection;
import java.util.List;

/**
 * 复合分片
 *
 * @author shuai
 * @since 2023-03-19
 */
public class TableComplexAlgorithm implements ComplexKeysShardingAlgorithm<Integer> {

    @Override
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<Integer> complexKeysShardingValue) {
        //获取age的值
        Collection<Integer> ageValues = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("age");
        List<String> dbs = Lists.newArrayList();
        //通过age取模
        ageValues.forEach(item->{
            String dbName = "db_user_"+((item+3)%3);
            dbs.add(dbName);
        });
        return dbs;
    }
}

Hint分片策略（hint）

配置文件（需要注释其他的分片规则）

spring:
  shardingsphere:
    #开启sql显示
    props:
      sql:
        show: true
    datasource:
      # 配置数据源
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
    sharding:
      #唯一库数据
      default-data-source-name: db0
      #分库
      default-database-strategy:
#        standard:
#          # 添加数据分库字段(根据字段插入数据到那个表)
#          sharding-column: id
#          #精确分片
#          precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
#          #范围分片
#          range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm
        #行分片
        inline:
          # 添加数据分表字段(根据字段插入数据到那个表)
          sharding-column: id
          # 分片算法表达式 => 通过id取余
          algorithm-expression: db$->{id % 2}
#        #复合分片
#         complex:
#           sharding-columns: id,age
#           algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseComplexAlgorithm

      #分表
      tables:
        #表名
        db_user:
          actual-data-nodes: db$->{0..1}.db_user_$->{0..2}
          key-generator:
            column: id  # 主键ID
            type: SNOWFLAKE  # 生成策略雪花id
          table-strategy:
#            standard:
#              sharding-column: id
#              #精确分片
#              precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
#              #范围分片
#              range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm
#            inline:
#              # 添加数据分表字段(根据字段插入数据到那个表)
#              sharding-column: id
#              # 分片算法表达式 => 通过id取余
#              algorithm-expression: db_user_$->{id % 3}
#            #复合分片
#            complex:
#              sharding-columns: id,age
#              algorithm-class-name: com.example.sharding_test.strategy.table.TableComplexAlgorithm
            #强制分片
            hint:
              algorithm-class-name: com.example.sharding_test.strategy.table.TableHintAlgorithm

分表规则

package com.example.sharding_test.strategy.table;

import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;

import java.util.Arrays;
import java.util.Collection;

/**
 * 精确分片
 *
 * @author shuai
 * @since 2023-03-19
 */
public class TableHintAlgorithm implements HintShardingAlgorithm<Integer> {


    @Override
    public Collection<String> doSharding(Collection<String> collection, HintShardingValue<Integer> hintShardingValue) {
        String logicTableName = hintShardingValue.getLogicTableName();
        String dbName = logicTableName+"_"+hintShardingValue.getValues().toArray()[0];
        return Arrays.asList(dbName);
    }
}

测试hint

    @Test
    void selectHintData(){
        HintManager manager = HintManager.getInstance();
        manager.addTableShardingValue("db_user",2);
        LambdaQueryWrapper<DbUser> wrapper = Wrappers.lambdaQuery();
        wrapper.eq(DbUser::getAge,34);
        List<DbUser> dbUsers = dbUserMapper.selectList(wrapper);
        dbUsers.forEach(System.out::println);
    }

http://www.kler.cn/a/1720.html

相关文章：

  • 机器学习算法基础知识1：决策树
  • 计算机网络面试常见知识框架以及常见面试题解
  • 32单片机从入门到精通之开发环境——库文件（六）
  • 当下热点系列 篇二：大消费题材解析和股票梳理
  • Druid连接Oracle数据库，连接失效导致SQL无法执行
  • 如何在 Ubuntu 22.04 上安装并开始使用 RabbitMQ
  • 复制带随机指针的复杂链表
  • 【二】一起算法---队列：STL queue、手写循环队列、双端队列和单调队列、优先队列
  • 【微信小程序】-- 分包 - 独立分包 分包预下载（四十五）
  • MySQL高级功能：存储过程、触发器、事务、备份和恢复
  • windows安装包管理工具Chocolatey
  • Java 到底是值传递还是引用传递？
  • java基础面试题（一）
  • Windows安装部署nginx
  • 【JavaEE】进程和线程
  • 自动驾驶TPM技术杂谈 ———— 超声波雷达系统测距
  • C++修炼之筑基期第一层——认识类与对象
  • 函数指针-＞回调函数
  • 大环境不好，找工作太难？三面阿里，幸好做足了准备，已拿offer
  • 【Java web】-转发和重定向
  • C++单继承和多继承
  • 智能生活垃圾检测与分类系统（UI界面+YOLOv5+训练数据集）
  • Kubernetes学习（七）补充：基于自定义指标进行扩缩容
  • 浅析“面向对象编程思想”
  • 【C语言】字符串函数和内存函数
  • 【Spring】我抄袭了Spring，手写一套MySpring框架。。。