ShardingSphere-JDBC实现日志系统分表优化实践

1. 项目背景与需求分析在日志服务系统的开发过程中随着业务量的持续增长单表数据量往往会呈现指数级上升。以我们最近开发的视频分析平台为例核心日志表在短短3个月内就积累了超过2000万条记录导致简单查询的响应时间从最初的200ms飙升至2s以上。这种性能劣化直接影响了系统的用户体验和运维效率。经过详细分析我们发现日志数据具有两个显著特征时间维度强关联性90%的查询操作都带有时间范围条件冷热数据区分明显最近3个月的数据访问频率是历史数据的10倍以上基于这些特征我们决定采用分表方案来解决性能问题。具体来说对任务执行日志tm_task_log采用按月分表策略每月自动创建新表对设备注册记录tm_vc_cam_register_record采用按周分表策略适合高频写入场景2. 技术选型与对比在分表方案的技术选型上我们重点评估了三种主流方案方案优点缺点适用场景应用层分表实现简单无需中间件业务侵入性强维护成本高简单业务分表规则固定MyCat功能全面支持复杂分片需要独立部署运维复杂度高大型分布式系统ShardingSphere-JDBC轻量级透明分片部分SQL限制需处理分布式事务中小型系统Java技术栈最终选择ShardingSphere-JDBC 4.1.1版本主要基于以下考虑无缝集成Spring Boot生态配置简单支持精确分片和范围分片算法满足我们的时间维度分表需求提供分布式事务支持为未来扩展预留空间活跃的社区和持续更新降低长期维护风险3. 环境准备与基础配置3.1 Maven依赖管理在pom.xml中需要添加以下核心依赖dependency groupIdorg.apache.shardingsphere/groupId artifactIdsharding-jdbc-spring-boot-starter/artifactId version4.1.1/version /dependency dependency groupIdcom.baomidou/groupId artifactIdmybatis-plus-boot-starter/artifactId version3.4.3/version /dependency dependency groupIdcn.hutool/groupId artifactIdhutool-all/artifactId version5.7.16/version /dependency注意ShardingSphere版本需要与Spring Boot版本匹配。对于Spring Boot 2.3.x推荐使用4.1.1版本以避免兼容性问题。3.2 数据库初始化虽然ShardingSphere可以自动路由数据到分表但表结构需要预先创建。建议采用以下初始化策略创建基础表结构脚本-- 按月分表基础结构 CREATE TABLE IF NOT EXISTS tm_task_log_202301 ( id BIGINT PRIMARY KEY, task_id VARCHAR(64) NOT NULL, status TINYINT DEFAULT 0, create_time DATETIME NOT NULL, INDEX idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 按周分表基础结构 CREATE TABLE IF NOT EXISTS tm_vc_cam_register_record_2023_w01 ( id BIGINT PRIMARY KEY, device_id VARCHAR(64) NOT NULL, ip_address VARCHAR(32), create_time DATETIME NOT NULL, INDEX idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;使用Flyway或Liquibase管理表结构变更确保所有分表结构一致。4. 按月分表实现细节4.1 精确分片算法实现精确分片算法处理等值查询、IN核心逻辑是将时间字段转换为月份后缀public class PreciseTaskLogSharding implements PreciseShardingAlgorithmDate { Override public String doSharding(CollectionString availableTargetNames, PreciseShardingValueDate shardingValue) { Date createTime shardingValue.getValue(); if (createTime null) { return tm_task_log_ DateUtil.format(new Date(), yyyyMM); } // 处理时区问题 String monthSuffix DateUtil.format(convertTimeZone(createTime), yyyyMM); String targetTable tm_task_log_ monthSuffix; // 验证表是否存在 if (!availableTargetNames.contains(targetTable)) { createNewMonthTable(monthSuffix); } return targetTable; } }关键实现细节时区处理建议在算法内部统一转换为系统时区避免因时区差异导致路由错误动态建表当检测到目标表不存在时自动创建提高系统容错性空值处理当分片键为null时默认使用当前月份4.2 范围分片算法优化范围分片算法需要处理BETWEEN、、等操作我们通过Redis缓存优化查询性能public class RangeTaskLogSharding implements RangeShardingAlgorithmDate { Override public CollectionString doSharding(CollectionString availableTargetNames, RangeShardingValueDate shardingValue) { // 从Redis获取有效的分表列表 SetString validTables redisService.getCacheSet(valid_log_tables); // 计算时间范围内涉及的所有月份 SetString result new LinkedHashSet(); Date current shardingValue.getValueRange().lowerEndpoint(); Date end shardingValue.getValueRange().upperEndpoint(); while (!current.after(end)) { String table tm_task_log_ DateUtil.format(current, yyyyMM); if (validTables.contains(table)) { result.add(table); } current DateUtil.offsetMonth(current, 1); } return result.isEmpty() ? validTables : result; } }性能优化点使用LinkedHashSet保持表名顺序有利于后续合并结果的顺序一致性限制最大查询范围如不超过12个月防止全表扫描添加缓存预热机制系统启动时自动加载有效表列表5. 按周分表特殊处理按周分表相比按月分表有以下特殊考量5.1 跨年周数处理ISO标准中每年的第一周可能包含上年度的日期。需要特殊处理public class PreciseRegisterRecordSharding implements PreciseShardingAlgorithmDate { Override public String doSharding(CollectionString availableTargetNames, PreciseShardingValueDate preciseShardingValue) { Date createTime preciseShardingValue.getValue(); Calendar cal Calendar.getInstance(Locale.CHINA); cal.setTime(createTime ! null ? createTime : new Date()); // 使用ISO周标准 int week cal.get(Calendar.WEEK_OF_YEAR); int year cal.get(Calendar.YEAR); // 处理跨年周 if (week 1 cal.get(Calendar.MONTH) Calendar.DECEMBER) { year; } else if (week 52 cal.get(Calendar.MONTH) Calendar.JANUARY) { year--; } return String.format(tm_vc_cam_register_record_%d_w%02d, year, week); } }5.2 周表索引优化按周分表的写入频率通常更高建议优化索引策略-- 按周分表建议索引 CREATE TABLE tm_vc_cam_register_record_2023_w01 ( id BIGINT PRIMARY KEY, device_id VARCHAR(64) NOT NULL, -- 其他字段... create_time DATETIME NOT NULL, INDEX idx_device_time (device_id, create_time), INDEX idx_time_status (create_time, status) ) ENGINEInnoDB;6. 生产环境最佳实践6.1 分表名缓存机制我们采用二级缓存策略提高性能本地缓存使用Caffeine缓存最近访问的表名Redis缓存存储全量表名列表定期更新缓存更新策略示例Scheduled(cron 0 0 3 * * ?) // 每天凌晨3点执行 public void refreshTableCache() { // 从数据库查询所有分表 ListString tables logMapper.selectAllTables(); // 更新Redis缓存 redisTemplate.opsForSet().add(valid_log_tables, tables.toArray(new String[0])); redisTemplate.expire(valid_log_tables, 48, TimeUnit.HOURS); // 更新本地缓存 localCache.invalidateAll(); tables.forEach(table - localCache.put(table, Boolean.TRUE)); }6.2 动态分表创建为避免手动创建分表实现自动化流程public void createNewMonthTable(String monthSuffix) { String sql String.format( CREATE TABLE IF NOT EXISTS tm_task_log_%s LIKE tm_task_log_template, monthSuffix); try { jdbcTemplate.execute(sql); // 更新缓存 redisTemplate.opsForSet().add(valid_log_tables, tm_task_log_ monthSuffix); } catch (Exception e) { log.error(创建分表失败: {}, monthSuffix, e); } }6.3 查询优化建议强制分片键对于关键查询添加分片键检查public ListTaskLog queryLogs(DateRange range) { if (range.getStart() null || range.getEnd() null) { throw new BusinessException(必须指定时间范围); } // 查询逻辑... }分页优化使用ShardingSphere提供的归并分页PageHelper.startPage(1, 10); ListTaskLog logs taskLogMapper.selectByRange(range);7. 常见问题排查7.1 全路由问题现象查询没有使用分片键导致扫描所有分表解决方案添加拦截器检查SQLIntercepts(Signature(type Executor.class, methodquery, args{MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})) public class ShardingKeyCheckInterceptor implements Interceptor { Override public Object intercept(Invocation invocation) throws Throwable { Object parameter invocation.getArgs()[1]; if (parameter instanceof ParameterMap !containsShardingKey((ParameterMap)parameter)) { throw new SQLException(查询必须包含分片键条件); } return invocation.proceed(); } }7.2 跨分片查询性能差现象范围查询跨越多个月份时响应慢优化方案添加查询时间范围限制使用并行查询spring: shardingsphere: props: max.connections.size.per.query: 5 # 每个查询最大连接数7.3 分布式事务问题现象跨分表更新出现部分成功解决方案启用XA事务spring: shardingsphere: props: xa-transaction-manager-type: Atomikos8. 监控与维护建议添加以下监控指标分表数据量监控SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema DATABASE() AND table_name LIKE tm_task_log_% ORDER BY table_rows DESC;查询性能监控// 使用Micrometer暴露指标 Bean public MeterRegistryCustomizerMeterRegistry metricsCommonTags() { return registry - registry.config().commonTags(application, log-service); }定期归档策略Scheduled(cron 0 0 2 * * ?) // 每天凌晨2点执行 public void archiveOldData() { // 归档3个月前的数据 Date archiveDate DateUtil.offsetMonth(new Date(), -3); String archiveTable tm_task_log_archive_ DateUtil.format(archiveDate, yyyyMM); // 执行归档 logMapper.archiveData(archiveTable, archiveDate); }在实际项目中我们通过这套方案成功将日志查询性能提升了5-8倍同时将存储成本降低了60%。系统运行6个月以来日均处理日志量超过300万条保持了稳定的响应时间。