目录一、背景介绍技术栈版本二、核心依赖配置三、配置文件详解3.1 完整 application.yml3.2 配置要点说明四、MyBatis-Plus 配置类五、实体类与 Mapper5.1 LocationEntity5.2 LocationMapper六、业务代码示例七、分片原理详解7.1 路由过程7.2 INTERVAL 算法参数八、注意事项8.1 时间范围限制8.2 表需要预先创建8.3分片键查询要求九、优缺点总结优点缺点十、总结一、背景介绍在实际业务场景中单表数据量过大会导致查询性能下降、维护困难等问题。本文介绍如何使用ShardingSphere-JDBC对 PostgreSQL 数据库进行按月分表操作结合Druid连接池和MyBatis-Plus增强框架实现高效的数据分片管理。技术栈版本组件版本Spring Boot2.7.18JDK1.8ShardingSphere-JDBC5.2.1MyBatis-Plus3.5.3.1Druid1.2.20PostgreSQL驱动版本随 Spring Boot二、核心依赖配置!-- Druid 连接池 -- dependency groupIdcom.alibaba/groupId artifactIddruid-spring-boot-starter/artifactId version1.2.20/version /dependency !-- PostgreSQL 驱动 -- dependency groupIdorg.postgresql/groupId artifactIdpostgresql/artifactId scoperuntime/scope /dependency !-- MyBatis-Plus -- dependency groupIdcom.baomidou/groupId artifactIdmybatis-plus-boot-starter/artifactId version3.5.3.1/version /dependency !-- ShardingSphere-JDBC -- dependency groupIdorg.apache.shardingsphere/groupId artifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactId version5.2.1/version exclusions !-- 排除 SnakeYAML 使用统一版本避免版本冲突 -- exclusion groupIdorg.yaml/groupId artifactIdsnakeyaml/artifactId /exclusion /exclusions /dependency !-- SnakeYAML 统一版本 -- dependency groupIdorg.yaml/groupId artifactIdsnakeyaml/artifactId version1.33/version /dependency三、配置文件详解3.1 完整 application.ymlserver: port: 8777 servlet: context-path: /carrier multipart: max-file-size: 100MB max-request-size: 100MB tomcat: uri-encoding: utf-8 spring: mvc: pathmatch: matching-strategy: ant_path_matcher autoconfigure: exclude: - com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure shardingsphere: enabled: true datasource: names: master master: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: org.postgresql.Driver url: jdbc:postgresql://****:5432/lbs_manage?stringtypeunspecifieduseSSLfalseserverTimezoneAsia/Shanghai username: ***** password: ***** # Druid 连接池配置 initial-size: 5 min-idle: 5 max-active: 20 max-wait: 60000 validation-query: SELECT 1 test-while-idle: true rules: sharding: tables: location: # 逻辑表名称 actual-data-nodes: master.location_${202501..203012} table-strategy: standard: sharding-column: createtime sharding-algorithm-name: interval-sharding sharding-algorithms: interval-sharding: type: INTERVAL props: datetime-pattern: yyyy-MM-dd HH:mm:ss datetime-lower: 2025-01-01 00:00:00 datetime-upper: 2030-12-31 23:59:59 sharding-suffix-pattern: yyyyMM datetime-interval-amount: 1 datetime-interval-unit: MONTHS props: sql-show: true sql-simple: true check-table-metadata-enabled: true # MyBatis-Plus 配置 mybatis-plus: mapper-locations: classpath*:mapper/*.xml type-aliases-package: com.demo.carrier.entity global-config: db-config: table-underline: true logic-delete-field: del_flag logic-delete-value: 1 logic-not-delete-value: 0 configuration: map-underscore-to-camel-case: true cache-enabled: false log-impl: org.apache.ibatis.logging.stdout.StdOutImpl3.2 配置要点说明配置项说明spring.autoconfigure.exclude排除 Druid 自动配置由 ShardingSphere 统一管理actual-data-nodes使用表达式批量生成location_202501到location_203012共72张物理表sharding-column分片键为createtime字段datetime-interval-unit: MONTHS按月分片每个表存储一个月的数据四、MyBatis-Plus 配置类Slf4j Configuration MapperScan(basePackages com.cetcnav.carrier.mapper) public class MybatisPlusConfig { Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); // 分页插件 - PostgreSQL 数据库 PaginationInnerInterceptor paginationInterceptor new PaginationInnerInterceptor(DbType.POSTGRE_SQL); paginationInterceptor.setMaxLimit(5000L); paginationInterceptor.setOverflow(true); interceptor.addInnerInterceptor(paginationInterceptor); return interceptor; } }五、实体类与 Mapper5.1 LocationEntityData TableName(location) public class LocationEntity { TableId(type IdType.AUTO) private Long id; private String tid; private Date createtime; TableLogic private Integer delFlag; // 其他字段... }5.2 LocationMapperMapper public interface LocationMapper extends BaseMapperLocationEntity { // 继承 BaseMapper 即可获得基础 CRUD 能力 }六、业务代码示例Service public class LocationService { Autowired private LocationMapper locationMapper; public ApiResponseListLocationEntity queryByTimeRange(SearchDto searchDto) { LambdaQueryWrapperLocationEntity queryWrapper new LambdaQueryWrapper(); // 精确匹配 tid queryWrapper.eq(LocationEntity::getTid, searchDto.getTid()); // 时间范围查询 - 分片键会自动路由到对应物理表 queryWrapper.between( LocationEntity::getCreatetime, DateUtil.str2Timestamp(2025-05-01 00:00:00), DateUtil.str2Timestamp(2025-06-30 23:59:59) ); // 只查询指定字段 queryWrapper.select(LocationEntity::getCreatetime); // 按时间倒序 queryWrapper.orderByDesc(LocationEntity::getCreatetime); ListLocationEntity result locationMapper.selectList(queryWrapper); return ApiResponse.success(result); } }七、分片原理详解7.1 路由过程用户查询: createtime BETWEEN 2025-05-01 AND 2025-06-30 ↓ ShardingSphere 解析分片键 createtime ↓ 时间范围 → 2025年5月、2025年6月 ↓ 路由到物理表: location_202505, location_202506 ↓ 执行 UNION 查询后返回合并结果7.2 INTERVAL 算法参数参数值说明datetime-patternyyyy-MM-dd HH:mm:ss分片键解析格式datetime-lower2025-01-01 00:00:00最小时间边界datetime-upper2030-12-31 23:59:59最大时间边界sharding-suffix-patternyyyyMM表后缀格式datetime-interval-amount1间隔数量datetime-interval-unitMONTHS间隔单位月八、注意事项8.1 时间范围限制配置的datetime-lower和datetime-upper限制了查询范围超出范围的数据将无法路由datetime-lower: 2025-01-01 00:00:00 ← 早于此时间的数据无法查询 datetime-upper: 2030-12-31 23:59:59 ← 晚于此时间的数据无法查询8.2 表需要预先创建ShardingSphere不会自动创建物理表需要提前执行建表语句。8.3分片键查询要求查询条件必须包含分片键createtime否则会全表扫描所有物理表建议所有业务查询都带上时间范围条件九、优缺点总结优点优点说明应用层透明业务代码无需修改像操作单表一样使用灵活的分片策略支持时间、范围、哈希等多种算法连接池兼容支持 Druid、HikariCP 等主流连接池SQL 兼容性好支持 PostgreSQL、MySQL 等主流数据库缺点缺点说明分布式事务限制跨表操作不支持强一致性事务分页查询性能跨多表分页需要内存合并效率较低表需要预先创建不会自动建表需要脚本维护分片键约束查询必须带分片键否则全表扫描十、总结通过 ShardingSphere-JDBC 实现按月分表可以有效解决单表数据量过大的问题。本文提供的配置经过实际项目验证可以快速应用到生产环境中。关键点回顾排除 Druid 自动配置由 ShardingSphere 统一管理数据源使用 INTERVAL 算法实现按月分片LambdaQueryWrapper无需修改即可支持分片查询注意时间边界和表预先创建的要求