当前位置:   article > 正文

ShardingSphere 5.0.0 实现按月水平分表_shardingsphere按月分表

shardingsphere按月分表

背景:

        业务数据从大数据进行同步,数据量大概1个月1000W条,如果选择按字段进行hash取模分表时间久了数据量依然会很大,所以直接选择按月进行水平分表。

废话不多说,直接上代码

1.Maven引入依赖

  1. <dependency>
  2. <groupId>org.apache.shardingsphere</groupId>
  3. <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
  4. <version>5.0.0</version>
  5. </dependency>

2.application.yml配置

  1. spring:
  2. shardingsphere:
  3. props:
  4. # 是否显示sql
  5. sql-show: false
  6. datasource:
  7. ds0:
  8. url: jdbc:mysql://127.0.0.1:3306/db_test?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
  9. username: root
  10. password: root
  11. driver-class-name: com.mysql.jdbc.Driver
  12. type: com.alibaba.druid.pool.DruidDataSource
  13. names: ds0
  14. rules:
  15. sharding:
  16. key-generators:
  17. snowflake:
  18. type: SNOWFLAKE
  19. sharding-algorithms:
  20. ota-strategy-inline:
  21. props:
  22. strategy: standard
  23. # 自定义标准分配算法
  24. algorithmClassName: com.test.business.algorithm.OTAStrategyShardingAlgorithm
  25. type: CLASS_BASED
  26. tables:
  27. #逻辑表 下面是节点表,分表后还有数据在原来的表,所有查询节点需要加上原来的表
  28. ota_strategy_info:
  29. actual-data-nodes: ds0.ota_strategy_info_202$->{201..212}
  30. key-generate-strategy:
  31. column: id
  32. key-generator-name: snowflake
  33. # 配置分表策略
  34. table-strategy:
  35. #分片策略 以创建时候分表,实现类计算
  36. standard:
  37. sharding-column: create_time
  38. #对应下面的分表策略类
  39. sharding-algorithm-name: ota-strategy-inline
  40. # OTA升级策略表水平分表 ================================================================

3.数据分配算法类 OTAStrategyShardingAlgorithm.java

  1. import com.alibaba.fastjson.JSON;
  2. import com.google.common.collect.Range;
  3. import org.apache.commons.lang.StringUtils;
  4. import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
  5. import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
  6. import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
  7. import org.springframework.stereotype.Component;
  8. import java.time.LocalDateTime;
  9. import java.time.format.DateTimeFormatter;
  10. import java.util.*;
  11. /**
  12. * @Description: sharding分表规则:按单月分表
  13. * @Author: lg
  14. * @Date: 2022/6/9
  15. * @Version: V1.0
  16. */
  17. @Component
  18. public class OTAStrategyShardingAlgorithm implements StandardShardingAlgorithm<String> {
  19. private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
  20. private static final DateTimeFormatter yyyyMM = DateTimeFormatter.ofPattern("yyyyMM");
  21. /**
  22. * 【范围】数据查询
  23. */
  24. @Override
  25. public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
  26. // 逻辑表名
  27. String logicTableName = rangeShardingValue.getLogicTableName();
  28. // 范围参数
  29. Range<String> valueRange = rangeShardingValue.getValueRange();
  30. Set<String> queryRangeTables = extracted(logicTableName, LocalDateTime.parse(valueRange.lowerEndpoint(), formatter),
  31. LocalDateTime.parse(valueRange.upperEndpoint(), formatter));
  32. ArrayList<String> tables = new ArrayList<>(collection);
  33. tables.retainAll(queryRangeTables);
  34. System.out.println(JSON.toJSONString(tables));
  35. return tables;
  36. }
  37. /**
  38. * 根据范围计算表明
  39. *
  40. * @param logicTableName 逻辑表明
  41. * @param lowerEndpoint 范围起点
  42. * @param upperEndpoint 范围终端
  43. * @return 物理表名集合
  44. */
  45. private Set<String> extracted(String logicTableName, LocalDateTime lowerEndpoint, LocalDateTime upperEndpoint) {
  46. Set<String> rangeTable = new HashSet<>();
  47. while (lowerEndpoint.isBefore(upperEndpoint)) {
  48. String str = getTableNameByDate(lowerEndpoint, logicTableName);
  49. rangeTable.add(str);
  50. lowerEndpoint = lowerEndpoint.plusMonths(1);
  51. }
  52. // 获取物理表明
  53. String tableName = getTableNameByDate(upperEndpoint, logicTableName);
  54. rangeTable.add(tableName);
  55. return rangeTable;
  56. }
  57. /**
  58. * 根据日期获取表明
  59. * @param dateTime 日期
  60. * @param logicTableName 逻辑表名
  61. * @return 物理表名
  62. */
  63. private String getTableNameByDate(LocalDateTime dateTime, String logicTableName) {
  64. String tableSuffix = dateTime.format(yyyyMM);
  65. return logicTableName.concat("_").concat(tableSuffix);
  66. }
  67. /**
  68. * 数据插入
  69. *
  70. * @param collection
  71. * @param preciseShardingValue
  72. * @return
  73. */
  74. @Override
  75. public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
  76. String str = preciseShardingValue.getValue();
  77. if (StringUtils.isEmpty(str)) {
  78. return collection.stream().findFirst().get();
  79. }
  80. LocalDateTime value = LocalDateTime.parse(str, formatter);
  81. String tableSuffix = value.format(yyyyMM);
  82. String logicTableName = preciseShardingValue.getLogicTableName();
  83. String table = logicTableName.concat("_").concat(tableSuffix);
  84. System.out.println("OrderStrategy.doSharding table name: " + table);
  85. return collection.stream().filter(s -> s.equals(table)).findFirst().orElseThrow(() -> new RuntimeException("逻辑分表不存在"));
  86. }
  87. @Override
  88. public void init() {
  89. }
  90. @Override
  91. public String getType() {
  92. // 自定义 这里需要spi支持
  93. return null;
  94. }
  95. }

shardingsphere5.0版本开始,数据插入和数据查询都可以在一个类中实现,需要实现接口:StandardShardingAlgorithm

4.重写方法doSharding()、extracted()

  1. // 根据精准值查询逻辑表
  2. public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
  3. }
  4. // 根据范围查询逻辑表
  5. private Set<String> extracted(String logicTableName, LocalDateTime lowerEndpoint, LocalDateTime upperEndpoint) {
  6. }
  7. // 插入数据时根据分表关键词获取物理表
  8. public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {

5.初始化分表

6.插入测试

这里直接使用mybatis或者mybatisplus插入即可,在步骤4中的doSharding方法查看是否匹配到对应的物理表即可,结果直接从表里查看即可。

7.查询测试

 执行下述sql

select * from  ota_strategy_info where create_time = '2022-04-14 17:21:48'

结果:

这里的查询精确找到了4月的物理表,如果未找到则会查询所有表,同理会执行与表数量相对于的sql数量,所以查询的时候一定要命中物理表,否则效率不仅不会提高返回会降低!

总结:

官网文档:概览 :: ShardingSpherehttps://shardingsphere.apache.org/document/current/cn/overview/更多内容可以参考文档,查询sql是否会命中物理表得多测试,根据日志提示选择查询方法!

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/一键难忘520/article/detail/756712
推荐阅读
  

闽ICP备14008679号