1.背景

在订单、流水等场景下，与时间段关联最紧密的大数据业务，单表很容易就超过了千万级别，这时MySQL的性能大打折扣，利用ShardingSphere我们可以完成按照时间分库分表，从而降低单表的数据量。

2.实现

其他代码参考前一篇文章

  1. spring:
  2.   shardingsphere:
  3.     datasource:
  4.       names: ds1
  5.       ds1:
  6.         type: com.alibaba.druid.pool.DruidDataSource
  7.         driver-class-name: com.mysql.cj.jdbc.Driver
  8.         url: jdbc:mysql://localhost:3306/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
  9.         username: root
  10.         password: 123456
  12.     sharding:
  13.         tables:
  14.           sensor_xmz:
  15.               actualDataNodes: ds$->{2021...2025}.sensor_xmz_$->{1..4}
  16.               # 分库策略
  17.               databaseStrategy:
  18.                standard:
  19.                   shardingColumn: create_time
  20.                   preciseAlgorithmClassName: com.zym.config.PreciseModuloDatabaseShardingAlgorithm
  21.               # 分表策略
  22.               tableStrategy:
  23.                standard:
  24.                   shardingColumn: create_time
  25.                   preciseAlgorithmClassName: com.zym.config.PreciseModuloTableShardingAlgorithm
  26.     props:
  27.       sql:
  28.         show: true
  30.   main:
  31.     allow-bean-definition-overriding: true

精确分片，则需要自己实现分片类

PreciseModuloDatabaseShardingAlgorithm.java

  1. package com.zym.config;
  3. import org.apache.commons.lang3.StringUtils;
  4. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
  5. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
  6. import java.util.Collection;
  8. public class PreciseModuloDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<String> {
  9.     @Override
  10.     public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
  11.         //配置的分库分片的sharding-column对应的值,也就是具体时间
  12.         String str=preciseShardingValue.getValue();
  13.         if (str.isEmpty()) {
  14.             throw new UnsupportedOperationException("分库字段为空");
  15.         }
  16.         //得到具体年,截取字符串要头不要尾
  17.         String value= StringUtils.substring(str,0,4);
  18.         for (String each:collection) {
  19.             if(each.endsWith(value)){
  20.                 return each;
  21.             }
  22.         }
  23.         return null;
  24.     }
  25. }

PreciseModuloTableShardingAlgorithm.java

  1. package com.zym.config;
  3. import org.apache.commons.lang3.StringUtils;
  4. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
  5. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
  6. import java.util.Collection;
  8. public class PreciseModuloTableShardingAlgorithm implements PreciseShardingAlgorithm<String> {
  9.     @Override
  10.     public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
  11.         //配置的分片的sharding-column对应的值
  12.         String timeValue = preciseShardingValue.getValue();
  13.         //判断timeValue是否为空
  14.         if(StringUtils.isBlank(timeValue)){
  15.             throw new UnsupportedOperationException("分表字段为空");
  16.         }
  17.         for (String each:collection) {
  18.             //得到具体月,截取字符串要头不要尾
  19.             String mouthValue= StringUtils.substring(timeValue,5,7);
  20.             int mouth = Integer.parseInt(mouthValue);
  21.             String suffix = null;
  22.             if (mouth >= 1 && mouth <= 3){
  23.                 suffix = "1";
  24.             }else if (mouth >= 4 && mouth <= 6){
  25.                 suffix = "2";
  26.             }else if (mouth >= 7 && mouth <= 9){
  27.                 suffix = "3";
  28.             }else if (mouth >= 10 && mouth <= 12){
  29.                 suffix = "4";
  30.             }
  31.             //循环每个库，看哪个库与当前条件匹配
  32.             if(each.endsWith(suffix)){
  33.                 return each;
  34.             }
  35.         }
  36.         return null;
  37.     }
  38. }

这样就实现了精确分片（按照时间分片）~