• 分库分表二：ShardingJDBC进阶实战案例上

    文章目录

    ShardingJDBC进阶实战上

    对于ShardingJDBC的环境搭建和入门案例请移步ShardingSphere介绍和入门实战

    案例三：Hint分片策略实现sql零入侵

    自定义Hint算法

    //库的算法
public class MyDBHintSharding implements HintShardingAlgorithm<Integer> {
	@Override
	public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue) {
		//这里的值是通过 HintManager 传进来的 和sql无关
		String logicTableName = shardingValue.getLogicTableName();
		Collection<Integer> list = shardingValue.getValues();
		List<String> dbNames = new ArrayList<>();
		if (list.isEmpty()) throw new UnsupportedOperationException(" route db is null. please check your HintManager");
		list.forEach(s->{
			String dbName = "m"+s;
			if (availableTargetNames.contains(dbName)) dbNames.add(dbName) ;
		});
		if (dbNames.isEmpty()) throw new UnsupportedOperationException(" route "+dbNames+" is not supported. please check your config");
		return dbNames;
	}
}

//表的算法
public class MyTableHintSharding implements HintShardingAlgorithm<Integer> {
	@Override
	public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue) {
		//这里的值是通过 HintManager 传进来的 和sql无关
		String logicTableName = shardingValue.getLogicTableName();
		Collection<Integer> list = shardingValue.getValues();
		List<String> tableNames = new ArrayList<>();
		if (list.isEmpty()) throw new UnsupportedOperationException(" route table is null. please check your HintManager");
		list.forEach(s->{
			String dbName = logicTableName+s;
			if (availableTargetNames.contains(dbName)) tableNames.add(dbName) ;
		});
		if (tableNames.isEmpty()) throw new UnsupportedOperationException(" route "+tableNames+" is not supported. please check your config");
		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
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    yml配置

    shardingsphere:
    props:
      # 开启sql打印
      sql:
       show: true
    datasource:
      # 配置逻辑库名
      names: m1,m2
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
        username: root
        password: root
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx/xmkf_zt?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
        username: xxx
        password: xxx
    sharding:
      tables:
        # 配置逻辑表名
        sharding_user:
          actual-data-nodes:
            # 逻辑库逻辑表对应真实库、表的关系
            m$->{1..2}.sharding_user$->{1..2}
          # 配置主键生成策略
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: 1
          # 分库策略
          database-strategy:
            hint:
              # 分片算法
              algorithm-class-name: org.springblade.common.shardingJDBC.MyDBHintSharding
          # 分表策略
          table-strategy:
            hint:
              # 分片算法
              algorithm-class-name: org.springblade.common.shardingJDBC.MyTableHintSharding

    • 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

    插入操作中指定库和表，而不在通过分片键和sql来分库分表

    @GetMapping("/shardingTest")
	@ApiOperation(value = "测试分库分表")
	@ApiOperationSupport(order = 9, author = "lsx")
	public R shardingTest(String name,Integer age){
		HintManager instance = HintManager.getInstance();
		//根据业务需求指定存到哪个库
		instance.addDatabaseShardingValue("sharding_user",1);
		//根据业务需求指定存到哪个表
		instance.addTableShardingValue("sharding_user",1);
		//通过HintManager 的配置 以下数据都保存到 m1的sharding_user1 当中
		List<ShardingUser> list = new ArrayList<>();
		for (int i = 0;i<100;i++){
			ShardingUser user = new ShardingUser();
			user.setUserName(name+i);
			user.setAge(i);
			list.add(user);
		}
		try {
			shardingUserService.saveBatch(list);
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			//线程安全，所有用完要注意关闭。
			instance.close();
		}
		return R.success("成功");
	}

    • 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

    Hint分片策略的特点在于 人为指定库和表，而不是通过sql去判断。难点在于如何动态的根据业务需求动态的指定库和表。
    查询也是一样，跟sql没有关系，而是通过HintManager 进行配置。这样的好处是解放了sql的限制，可以在查询的时候写复杂的sql。

    案例四：复合分片策略实现多分片键

    在实际生产中，我们通常根据id作为分片键，但是往往在查询的时候会根据时间来查，比如订单，某个时间段，或者根据某个时间段进行统计订单的数量，这种情况如果只用id作为分片键，就会导致全表全库扫描。
    比如有这样一个需求：根据季度来区分订单，查询的时候大概率也是按照季度来查询订单。对于这种需求我们可以根据订单时间和id作为复合的分片键。
    例如，第一季度(1-3月)和第二季度(4-6月)放到m1的数据库中，第三季度(7-9月)和第四季度(10-12月)放到m2的数据库中。然后每个库根据id的奇偶数分配到表1和表2中。
    那么接下来就实现这个功能。
    功能实现
    自定义分库算法

    public class MyDbComplexSharding implements ComplexKeysShardingAlgorithm {
	@Override
	public Collection<String> doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {
		Map<String, Collection> keyMap = shardingValue.getColumnNameAndShardingValuesMap();
		Map<String, Range> rangeMap = shardingValue.getColumnNameAndRangeValuesMap();
		//精确匹配
		Collection collection = keyMap.get("create_time");
		if (!CollectionUtil.isEmpty(collection)){
			List<Date> list = new ArrayList<>(collection);
			//解析月份 确定库
			int i = parseMonth(list.get(0));
			if(i != 0){
				return Arrays.asList("m"+i);
			}
		}
		//范围匹配
		if (CollectionUtil.isEmpty(rangeMap)) return availableTargetNames;
		String lower = "";
		String upper = "";
		SimpleDateFormat sim=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Set<String> set =new HashSet<>();
		try {
			if (rangeMap.get("create_time") != null){
				lower = rangeMap.get("create_time").lowerEndpoint().toString();
				Date lowerDate = sim.parse(lower);
				//解析月份 确定库
				int i = parseMonth(lowerDate);
				set.add("m"+i);
			}
			if (rangeMap.get("create_time") != null){
				upper = rangeMap.get("create_time").upperEndpoint().toString();
				Date upperDate = sim.parse(upper);
				int i = parseMonth(upperDate);
				set.add("m"+i);
			}
			return set;
		}catch (Exception e){
			e.printStackTrace();
			return availableTargetNames;
		}
	}

	private int parseMonth(Date date){
		Calendar c = Calendar.getInstance();
		c.setTime(date);
		int month = c.get(Calendar.MONTH) + 1;
		if (month>=1 && month<=6){
			return 1;
		}else if (month>=7 && month<=12){
			return 2;
		}
		return 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

    自定义分表算法

    public class MyTableComplexSharding implements ComplexKeysShardingAlgorithm {
	@Override
	public Collection<String> doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {
		Map<String, Collection> keyMap = shardingValue.getColumnNameAndShardingValuesMap();
		Map<String, Range> rangeMap = shardingValue.getColumnNameAndRangeValuesMap();
		Collection collection = keyMap.get("id");
		if (!CollectionUtil.isEmpty(collection)){
			String id = collection.iterator().next()+"";
			char lastIndex = id.charAt(id.length() - 1);
			//根据id奇偶数 确定表
			int a = Integer.valueOf(lastIndex) % 2 + 1;
			String logicTableName = shardingValue.getLogicTableName();
			return Arrays.asList(logicTableName + a);
		}
		//id不存在范围查询  所以没有做范围的判断
		return availableTargetNames;
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    yml配置

    shardingsphere:
    props:
      # 开启sql打印
      sql:
       show: true
    datasource:
      # 配置逻辑库名
      names: m1,m2
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
        username: root
        password: root
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx/xmkf_zt?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
        username: xxx
        password: xxx
    sharding:
      tables:
        # 配置逻辑表名
        sharding_user:
          actual-data-nodes:
            # 逻辑库逻辑表对应真实库、表的关系
            m$->{1..2}.sharding_user$->{1..2}
          # 配置主键生成策略
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: 1
          # 分库策略
          database-strategy:
            complex:
              # 分片键
              sharding-columns: id,create_time
              # 分片算法
              algorithm-class-name: org.springblade.common.shardingJDBC.MyDbComplexSharding
          table-strategy:
            complex:
              # 分片键
              sharding-columns: id,create_time
              # 分片算法
              algorithm-class-name: org.springblade.common.shardingJDBC.MyTableComplexSharding

    • 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

    测试插入

    	@GetMapping("/shardingTest")
	@ApiOperation(value = "测试分库分表")
	@ApiOperationSupport(order = 9, author = "lsx")
	public R shardingTest(String name, Integer age) {
		List<ShardingUser> list = new ArrayList<>();
		for (int i = 0; i < 100; i++) {
			ShardingUser user = new ShardingUser();
			user.setUserName(name + i);
			user.setAge(i);
			Calendar c = Calendar.getInstance();
			c.set(2022,new Random().nextInt(12),new Random().nextInt(29));
			user.setCreateTime(c.getTime());
			list.add(user);
		}
		shardingUserService.saveBatch(list);
		return R.success("成功");
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    m1库表1的数据
    在这里插入图片描述
    月份都是6月以下，id都是偶数。其他不展示了

    测试查询

    //根据id查询
	@GetMapping("/shardingQuery")
	@ApiOperation(value = "测试分库分表查询")
	@ApiOperationSupport(order = 10, author = "lsx")
	public R shardingQuery(String id) {
		ShardingUser one = shardingUserService.getById(Long.valueOf(id));
		return R.data(one);
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    在这里插入图片描述
    通过id可以判断出是表一还是表2，但是无法判断出那个库，所以实际执行的sql 两个库都查了，但是只查了表1

    范围查询

    @GetMapping("/shardingQuery")
	@ApiOperation(value = "测试分库分表查询")
	@ApiOperationSupport(order = 10, author = "lsx")
	public R shardingQuery(String id) {
		QueryWrapper<ShardingUser> query = new QueryWrapper<>();
		query.between("create_time", "2022-01-21 19:30:47", "2022-05-26 19:30:47");
		List<ShardingUser> list = shardingUserService.list(query);
		return R.data(list);
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    在这里插入图片描述
    根据时间可以确定了要查哪些库，但是没法确定查哪些表，所以根据时间范围都是6月份以内，所以查1库，由于没法确定id奇偶数所以表1表2都查
    虽然到此功能实现了，但是还有一些瑕疵，比如根据id查询的时候如何确定查询那个库而不是全部都查，其实这个可以把时间添加到id上面去，这需要自定义主键生成策略，这个后面在优化。

    案例五：广播表

    广播表定义是所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中都完全一致。例如字典表。
    在两个数据库创建字典表作为广播表

    CREATE TABLE `sharding_dict` (
  `id` bigint(20) NOT NULL,
  `dict_key` varchar(255) DEFAULT NULL,
  `dict_value` varchar(255) DEFAULT NULL,
  `source` varchar(255) DEFAULT 'zt',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    同样每个库分为表1和表2
    在这里插入图片描述
    实体类

    @Data
@TableName("sharding_dict")
public class ShardingDict {
	@TableId(type = IdType.NONE)
	private Long id;
	private String dictKey;
	private String dictValue;
	private String source;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    yml

    shardingsphere:
    props:
      # 开启sql打印
      sql:
       show: true
    datasource:
      # 配置逻辑库名
      names: m1,m2
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
        username: root
        password: root
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxx/xmkf_zt?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
        username: xxx
        password: xxx
    sharding:
      # 分库分表配置
      tables:
        sharding_dict:
          actual-data-nodes:
            # 逻辑库逻辑表对应真实库、表的关系
            m$->{1..2}.sharding_dict$->{1..2}
          # 配置主键生成策略
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: 1
          # 分库策略
          database-strategy:
            inline:
              # 分片键
              sharding-column: id
              # 分片算法
              algorithm-expression: m$->{id%2+1}
          # 分表策略
          table-strategy:
            inline:
              # 分片键
              sharding-column: id
              # 分片算法
              algorithm-expression: sharding_dict$->{(id%4).intdiv(2)+1}
      # 配置广播表
      broadcast-tables: sharding_dict

    • 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

    测试

    @GetMapping("/shardingRadio")
	@ApiOperation(value = "测试广播表")
	@ApiOperationSupport(order = 11, author = "lsx")
	public R shardingRadio() {
		List<ShardingDict> list = new ArrayList<>();
		for (int i = 0; i < 20; i++) {
			ShardingDict dict = new ShardingDict();
			dict.setDictKey("aaa"+i);
			dict.setDictValue(i+"");
			list.add(dict);
		}
		dictService.saveBatch(list);
		return R.success("成功");
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    在这里插入图片描述
    在这里插入图片描述
    有打印的sql可以看出虽然我们配置了分表，但是实际上两个数据都有插入，并且插入的都是广播表而不是具体的分表。
    也就是说
    如果配置了sharding_dict作为广播表，那么sharding_dict1和sharding_dict2都不会插入数据，而m1和m2的数据库会插入数据到sharding_dict并且数据是一样的。

  • 相关阅读:
    MySql模糊查询大全
    curl命令介绍
    [LeetCode/力扣][Java] 0315. 计算右侧小于当前元素的个数（Count of Smaller Numbers After Self）
    数据结构名词解释详细总结
    [4G/5G/6G专题基础-158]: 5G VoNR（Voice over NR）与VoLTE共同组成5G三大语音方案
    【LeetCode】3. 无重复字符的最长子串
    Oracle Data Pump与加密
    Gateway--服务网关限流
    LLVM 文档收集
    Springboot多数据源及事务实现方案
  • 原文地址：https://blog.csdn.net/admin522043032/article/details/126855882