springboot整合sharding-jdbc 5.5.2 做单库分表

前言

1、为什么要分表？
当单表数据量超过500W以上，数据库处理速度就达不到理想的效果了，数据库压力大，有可能还会出现因为sql执行时间长造成连接超时等情况。
2、为什么要使用sharding-jdbc？
无代码入侵，整合操作简单。
3、为什么要采用时间分表？
我个人觉得后期对历史冷数据进行操作的时候比较方便，例如整合 hdfs+hive做数据转移和备份，按时间分表在转移的时候不用考虑锁表，长时间占用数据库连接等情况

整合 sharding-jdbc

项目采用springboot 2.6.13，mybatis-plus 3.5.2，sharding-jdbc版本5.5.2
5.5.2版本支持mysql的 insert update，但不支持insert select、ifnull…
把下面的配置复制进项目里，基本上就可以了

pom文件

  <dependencies><!-- Spring Boot 基础 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- MyBatis-Plus --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.2</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.30</version><optional>true</optional></dependency><dependency><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId><version>1.33</version></dependency><!-- ShardingSphere JDBC 核心 --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc</artifactId><exclusions><exclusion><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-test-util</artifactId></exclusion><exclusion><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId></exclusion><exclusion><groupId>com.fasterxml.jackson.dataformat</groupId><artifactId>jackson-dataformat-xml</artifactId></exclusion></exclusions><version>5.5.2</version></dependency><!-- MySQL 驱动 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.33</version></dependency><dependency><groupId>com.fasterxml.jackson.core</groupId><artifactId>jackson-databind</artifactId><version>2.13.5</version></dependency><dependency><groupId>com.fasterxml.jackson.core</groupId><artifactId>jackson-core</artifactId><version>2.13.5</version></dependency><dependency><groupId>com.fasterxml.jackson.dataformat</groupId><artifactId>jackson-dataformat-xml</artifactId><version>2.13.5</version></dependency></dependencies>

sharding-db-simple.yaml

sharding-jdbc相关配置

mode:type: Standalonerepository:type: JDBCdatabaseName: mysqldataSources:ds0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf8username: rootpassword: 123456rules:# # 其他所有表都路由到 ds0，但是不走分片- !SINGLEtables:- "ds0.*"- !SHARDINGtables:oper_detail:  #这个是需要分片的逻辑表名称#下面是配置分片逻辑，支持按月，按年，按id求余等，可根据自己需求来配置，${20250707..20251207} 代表的含义是表结构从20250707-20251207，每天都有一张表，但是建表逻辑需要自己写，拼接出来就是oper_detail_20250707这样actualDataNodes: ds0.oper_detail_${20250707..20260601}tableStrategy:standard:shardingColumn: create_time  #分片键，mysql表的字段，他会根据这个字段来确定是落到哪张表去执行sqlshardingAlgorithmName: sh_algorithm #分片规则，在执行sql之前会根据com.shardingjdbc.test.config.ShardingJdbcConfig的逻辑来确定落几张表xxx:  #如果还有其他表需要分片，继续写就可以actualDataNodes: ds0.xxx_${20250707..20260601}tableStrategy:standard:shardingColumn: xxx  #分片键shardingAlgorithmName: xxx_algorithm #分片规则defaultTableStrategy:none:  # 其他表不分片shardingAlgorithms:sh_algorithm:type: CLASS_BASEDprops:algorithmClassName: com.shardingjdbc.test.config.ShardingJdbcConfig #TODO 需要改成实际的配置路径strategy: standardxxx_algorithm:type: CLASS_BASEDprops:algorithmClassName: com.shardingjdbc.test.config.xxxstrategy: hint     #其他分片方式      
#配置是否重写sql等
props:sql-show: truesql-rewrite: true

application.yml

application.yml相关配置

server:port: 8080mybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplspring:application:name: sharding-jdbc-demodatasource:driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:sharding-db-simple.yamlusername: rootpassword: 123456main:allow-bean-definition-overriding: true

sharding数据源配置

import org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;import javax.sql.DataSource;
import java.io.*;@Configuration
public class ShardingDataSourceConfig {@Bean@Primarypublic DataSource shardingDataSource() {System.out.println("================== 开始创建 ShardingSphere 数据源 ==================");try (InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("sharding-db-simple.yaml")) {if (resourceAsStream == null) {throw new FileNotFoundException("sharding-db-simple.yaml not found in classpath");}return YamlShardingSphereDataSourceFactory.createDataSource(toByteArray(resourceAsStream));} catch (Exception e) {throw new RuntimeException(e);}}public static byte[] toByteArray(InputStream in) throws IOException {ByteArrayOutputStream buffer = new ByteArrayOutputStream();byte[] data = new byte[1024];int nRead;while ((nRead = in.read(data, 0, data.length)) != -1) {buffer.write(data, 0, nRead);}return buffer.toByteArray();}
}

ShardingJdbcConfig

sharding-jdbc路由表的配置，可以在这里实现具体路由到哪张表以及路由规则，本文是以天来分表，所以下面代码也是根据天去获取路由配置，如果你使用的是月或者ID取余，也可以在doSharding实现自己的逻辑

/*** 这里获取到的时间是前端传过来的时间，本文采用 yyyyMMddHHmmss 格式* StandardShardingAlgorithm 包含了精确查询 PreciseShardingValue 和 范围查询 RangeShardingValue，如果只需要一个，就实现单独的interface就行*/
public class ShardingJdbcConfig implements StandardShardingAlgorithm<String> {private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");private static String getTableDay(LocalDateTime localDateTime) {return localDateTime.format(TABLE_DAY);}private static DateTimeFormatter TABLE_DAY = DateTimeFormatter.ofPattern("MMdd");/*** @param availableTargetNames 从配置文件 actualDataNodes的逻辑取到的所有表名* @param shardingValue        查询时间* @return*/@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {System.out.println("----------------------精确查询");//查询时间，前端传的时间 yyyyMMddHHmmss 截取yyyyMMdd作为拼接表名String date = shardingValue.getValue().substring(0, 8);//shardingValue.getLogicTableName()是逻辑表名，例如oper_detail 实际表名oper_detail_yyyyMMddString actualTable = shardingValue.getLogicTableName() + "_" + date;if (availableTargetNames.contains(actualTable)) {return actualTable;} else {throw new UnsupportedOperationException("No matching table found for: " + date);}}@Overridepublic Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {System.out.println("----------------------范围查询");String startValue = rangeShardingValue.getValueRange().lowerEndpoint(); // 起始时间String endValue = rangeShardingValue.getValueRange().upperEndpoint();   // 结束时间Set<String> tableRangeDay = this.getTableRangeDay(startValue, endValue, collection, rangeShardingValue.getLogicTableName());System.out.println(tableRangeDay);return tableRangeDay;}private Set<String> getTableRangeDay(String start, String end, Collection<String> collection, String table) {LocalDateTime startTime = LocalDateTime.parse(start, FORMATTER);LocalDateTime endTime = LocalDateTime.parse(end, FORMATTER);Set<String> result = new HashSet<>();// 遍历开始时间-结束时间的每一天，生成对应的表名while (!startTime.isAfter(endTime)) {String suffix = "_" + startTime.getYear() + getTableDay(startTime);for (String actualTable : collection) {if (actualTable.endsWith(suffix)) {result.add(actualTable);}}startTime = startTime.plusDays(1);}//TODO 这里 result.add(table); table是逻辑表名，例如oper_detail，加这个的原因是我在引入shardingjdbc之前使用的是单表，之前的数据都在逻辑表里面，为了不再对之前的数据去单独做处理，就在这里也加了一个逻辑表名，每次范围查询的时候也都要去逻辑表执行一次查询，这么弄的坏处就是每次查询都多了一次逻辑表的io//假设前端传的时间是20250801000000-20250802235959，这里处理完所有的表名之后，得到的结果就为{"oper_detail","oper_detail_20250801","oper_detail_20250802"}，如果不需要查询逻辑表oper_detail，把下面这个add(table)删了就行result.add(table);return result;}@Overridepublic String getType() {return "CLASS_BASED";}@Overridepublic void init(Properties properties) {}
}

MybatisPlusConfig

mybatis-plus配置文件

@Configuration
public class MybatisPlusConfig {@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));return interceptor;}
}

我遇到的问题

1、sharding-jdbc和其他的相比，好处就是无代码入侵，只用加一些配置就可以，但是他不支持的也很多
，低版本不支持 insert update，5.5.2虽支持insert update，不支持insert select，ifnull等，具体不支持的可以去查看一下文档。
2、举例：如果按天去分表，假设今天是20250801，ymal文件中配置表逻辑为ds0.oper_detail_${20250707…20300601}，那么在库里面，20250707之后的表都必须存在，否则查询会报错 table or views ‘xxx’ 不存在，也必须在项目启动前就创建表，按ID取余这个没试过，不清楚会不会报错。
3、需要自己实现手动建表逻辑，比如定时任务每天建几天之后的表。