Spring Boot+MySQL+Redis实现用户学习进度实时查询系统

在实际技能学习平台或在线教育系统中用户进度的实时查询是一个高频且关键的需求。无论是课程学习、技能闯关还是考试练习用户都希望随时了解自己完成了多少、当前处于什么位置、下一步该做什么。而开发团队则需要一个稳定、准确、可扩展的方案来支撑这种实时数据查询避免因数据延迟或计算错误导致用户体验下降。本文将以一个典型的技能树学习场景例如编号为75的技能为例从零开始构建一个用户实时查询进度的后端服务。我们将重点解决几个核心问题如何设计数据模型来准确记录用户行为如何高效计算实时进度查询接口如何保证低延迟和高可用以及当进度数据出现不一致时应该如何排查和修复整个方案将基于常见的Web技术栈如Spring Boot MySQL/Redis实现但核心思路可以迁移到其他技术生态。文章会包含完整的环境准备、数据表设计、核心代码、接口测试和故障排查路径确保读者能够理解原理并落地实现。1. 理解用户进度查询的业务场景与技术挑战1.1 什么是用户进度查询用户进度查询本质上是一个聚合计算服务。它需要根据用户在某个学习内容如技能、课程、章节上的行为记录如开始学习、完成视频观看、通过测验、提交作业实时计算出该用户的完成状态。常见的进度表达方式包括百分比进度如已完成75%完成项统计如已完成8/10个章节状态标签如进行中、已完成、未开始最近活动如最近学习于2小时前在技能学习平台中进度数据不仅用于用户界面展示还可能影响解锁规则如完成前置技能才能解锁后续内容、奖励发放、学习提醒和个性化推荐。1.2 实时查询面临的技术挑战实现一个可靠的实时进度查询服务需要解决以下几个技术难点数据一致性用户行为可能来自多个终端Web、App、小程序如何保证分布式环境下的数据最终一致性计算性能进度计算可能涉及多表关联和聚合函数直接查询原始表在数据量大时性能堪忧。并发更新高并发场景下多个进度更新请求可能同时操作同一用户的进度记录。容错与恢复当计算服务或数据库出现故障时如何保证进度数据的准确恢复1.3 典型架构选型对比针对实时进度查询常见的架构方案有方案类型实现方式优点缺点适用场景实时计算用户行为触发即时计算数据准确度高无需额外存储计算压力大响应时间不稳定用户量小计算逻辑简单定时批处理定期全量计算用户进度计算压力可控架构简单数据有延迟实时性差对实时性要求不高的报表系统增量更新行为事件触发增量更新实时性好性能均衡逻辑复杂需要消息队列和去重大多数在线教育平台混合方案关键行为实时更新次要行为定时补偿平衡性能与实时性架构复杂维护成本高大型高并发平台本文将采用增量更新方案这是平衡实时性、性能和复杂度的最佳实践。2. 环境准备与项目结构设计2.1 技术栈与版本要求实现用户进度查询服务需要以下基础环境后端框架Spring Boot 2.7.x提供Web框架和自动配置JDK 8或11LTS版本稳定性有保障数据存储MySQL 5.7或8.0持久化存储用户行为和学习内容Redis 6.x缓存热点进度数据减少数据库压力开发工具Maven 3.6依赖管理IDEIntelliJ IDEA或Eclipse测试工具Postman或curl接口测试JUnit 5单元测试注意生产环境建议使用MySQL 8.0其在窗口函数、CTE等高级查询上有更好支持。Redis建议使用集群模式保证高可用。2.2 Maven依赖配置创建Spring Boot项目后在pom.xml中添加核心依赖dependencies !-- Spring Boot Starter Web -- dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-web/artifactId /dependency !-- Spring Boot Starter Data JPA -- dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-data-jpa/artifactId /dependency !-- MySQL Connector -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId scoperuntime/scope /dependency !-- Spring Boot Starter Data Redis -- dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-data-redis/artifactId /dependency !-- Jackson for JSON processing -- dependency groupIdcom.fasterxml.jackson.core/groupId artifactIdjackson-databind/artifactId /dependency !-- Lombok -- dependency groupIdorg.projectlombok/groupId artifactIdlombok/artifactId optionaltrue/optional /dependency !-- Test -- dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-test/artifactId scopetest/scope /dependency /dependencies2.3 配置文件说明在application.yml中配置数据库和Redis连接spring: datasource: url: jdbc:mysql://localhost:3306/skill_platform?useUnicodetruecharacterEncodingutf8serverTimezoneAsia/Shanghai username: root password: your_password driver-class-name: com.mysql.cj.jdbc.Driver jpa: hibernate: ddl-auto: update show-sql: true properties: hibernate: dialect: org.hibernate.dialect.MySQL8Dialect format_sql: true redis: host: localhost port: 6379 password: database: 0 lettuce: pool: max-active: 8 max-wait: -1ms max-idle: 8 min-idle: 0 timeout: 5000ms server: port: 8080 # 自定义配置 app: progress: cache-timeout: 300 # 进度缓存时间秒 batch-size: 1000 # 批量处理大小2.4 项目包结构设计采用分层架构明确各层职责src/main/java/com/example/skillprogress/ ├── SkillProgressApplication.java # 启动类 ├── config/ # 配置类 │ ├── RedisConfig.java # Redis配置 │ └── WebConfig.java # Web相关配置 ├── controller/ # 控制层 │ └── ProgressController.java # 进度查询接口 ├── service/ # 业务层 │ ├── ProgressService.java # 进度服务接口 │ └── impl/ │ └── ProgressServiceImpl.java # 进度服务实现 ├── repository/ # 数据访问层 │ ├── UserProgressRepository.java # 用户进度仓库 │ └── UserActionRepository.java # 用户行为仓库 ├── entity/ # 实体类 │ ├── UserProgress.java # 用户进度实体 │ ├── UserAction.java # 用户行为实体 │ └── Skill.java # 技能实体 ├── dto/ # 数据传输对象 │ ├── ProgressQueryDTO.java # 进度查询DTO │ └── ProgressResultDTO.java # 进度结果DTO └── util/ # 工具类 └── RedisKeyUtil.java # Redis键生成工具这种结构保证了代码的可维护性和可测试性每层职责单一便于后续扩展。3. 数据模型设计与核心业务逻辑3.1 数据库表结构设计用户进度查询的核心是数据模型设计。我们需要三张主表技能定义表、用户行为表、用户进度表缓存表。技能表skill存储学习内容结构CREATE TABLE skill ( id bigint(20) NOT NULL AUTO_INCREMENT, skill_id varchar(50) NOT NULL COMMENT 技能编号如75-SKill, skill_name varchar(200) NOT NULL COMMENT 技能名称, total_items int(11) NOT NULL DEFAULT 0 COMMENT 总学习项数量, required_items int(11) NOT NULL DEFAULT 0 COMMENT 必需完成项数量, is_active tinyint(1) NOT NULL DEFAULT 1 COMMENT 是否启用, created_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_skill_id (skill_id), KEY idx_active (is_active) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT技能定义表;用户行为表user_action记录用户具体学习行为CREATE TABLE user_action ( id bigint(20) NOT NULL AUTO_INCREMENT, user_id varchar(50) NOT NULL COMMENT 用户ID, skill_id varchar(50) NOT NULL COMMENT 技能编号, item_id varchar(100) NOT NULL COMMENT 学习项ID, action_type varchar(50) NOT NULL COMMENT 行为类型VIEW, COMPLETE, TEST_PASS等, action_value decimal(10,2) DEFAULT NULL COMMENT 行为数值如观看进度、得分, action_time datetime NOT NULL COMMENT 行为发生时间, created_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_skill (user_id,skill_id), KEY idx_action_time (action_time), KEY idx_skill_item (skill_id,item_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户行为记录表;用户进度表user_progress进度计算结果缓存CREATE TABLE user_progress ( id bigint(20) NOT NULL AUTO_INCREMENT, user_id varchar(50) NOT NULL COMMENT 用户ID, skill_id varchar(50) NOT NULL COMMENT 技能编号, completed_items int(11) NOT NULL DEFAULT 0 COMMENT 已完成项数量, total_items int(11) NOT NULL DEFAULT 0 COMMENT 总项数量, progress_percent decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT 进度百分比, last_action_time datetime DEFAULT NULL COMMENT 最近活动时间, status varchar(20) NOT NULL DEFAULT NOT_STARTED COMMENT 状态NOT_STARTED, IN_PROGRESS, COMPLETED, version int(11) NOT NULL DEFAULT 0 COMMENT 版本号乐观锁, created_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_skill (user_id,skill_id), KEY idx_skill_progress (skill_id,progress_percent), KEY idx_user_status (user_id,status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户进度缓存表;3.2 JPA实体类实现基于上述表结构创建对应的JPA实体类Skill实体Entity Table(name skill) Data public class Skill { Id GeneratedValue(strategy GenerationType.IDENTITY) private Long id; Column(name skill_id, unique true, nullable false) private String skillId; Column(name skill_name, nullable false) private String skillName; Column(name total_items, nullable false) private Integer totalItems 0; Column(name required_items, nullable false) private Integer requiredItems 0; Column(name is_active, nullable false) private Boolean active true; CreationTimestamp Column(name created_time, updatable false) private LocalDateTime createdTime; UpdateTimestamp Column(name updated_time) private LocalDateTime updatedTime; }UserAction实体Entity Table(name user_action) Data public class UserAction { Id GeneratedValue(strategy GenerationType.IDENTITY) private Long id; Column(name user_id, nullable false) private String userId; Column(name skill_id, nullable false) private String skillId; Column(name item_id, nullable false) private String itemId; Column(name action_type, nullable false) private String actionType; Column(name action_value) private BigDecimal actionValue; Column(name action_time, nullable false) private LocalDateTime actionTime; CreationTimestamp Column(name created_time, updatable false) private LocalDateTime createdTime; }UserProgress实体包含乐观锁版本控制Entity Table(name user_progress) Data public class UserProgress { Id GeneratedValue(strategy GenerationType.IDENTITY) private Long id; Column(name user_id, nullable false) private String userId; Column(name skill_id, nullable false) private String skillId; Column(name completed_items, nullable false) private Integer completedItems 0; Column(name total_items, nullable false) private Integer totalItems 0; Column(name progress_percent, nullable false) private BigDecimal progressPercent BigDecimal.ZERO; Column(name last_action_time) private LocalDateTime lastActionTime; Column(name status, nullable false) private String status NOT_STARTED; Version Column(name version, nullable false) private Integer version 0; CreationTimestamp Column(name created_time, updatable false) private LocalDateTime createdTime; UpdateTimestamp Column(name updated_time) private LocalDateTime updatedTime; }3.3 进度计算的核心业务逻辑进度计算的核心在于如何从用户行为记录中准确统计完成情况。在ProgressServiceImpl中实现关键逻辑Service Slf4j public class ProgressServiceImpl implements ProgressService { Autowired private UserActionRepository userActionRepository; Autowired private UserProgressRepository userProgressRepository; Autowired private SkillRepository skillRepository; Autowired private RedisTemplateString, Object redisTemplate; private static final String PROGRESS_CACHE_KEY progress:%s:%s; // progress:userId:skillId Override Transactional public ProgressResultDTO calculateUserProgress(String userId, String skillId) { // 1. 尝试从Redis缓存获取 ProgressResultDTO cachedProgress getProgressFromCache(userId, skillId); if (cachedProgress ! null) { return cachedProgress; } // 2. 从数据库查询进度可能不是最新的 UserProgress userProgress userProgressRepository .findByUserIdAndSkillId(userId, skillId) .orElseGet(() - createInitialProgress(userId, skillId)); // 3. 检查是否需要重新计算 if (needRecalculation(userProgress)) { userProgress recalculateProgress(userId, skillId, userProgress); } // 4. 转换为DTO并缓存 ProgressResultDTO result convertToDTO(userProgress); cacheProgress(userId, skillId, result); return result; } /** * 判断是否需要重新计算进度 */ private boolean needRecalculation(UserProgress userProgress) { // 如果最近5分钟内有更新且进度已完成则不需要重新计算 if (COMPLETED.equals(userProgress.getStatus()) userProgress.getUpdatedTime().isAfter(LocalDateTime.now().minusMinutes(5))) { return false; } // 如果最近10分钟内有更新则不需要频繁重新计算 return userProgress.getUpdatedTime().isBefore(LocalDateTime.now().minusMinutes(10)); } /** * 重新计算用户进度 */ private UserProgress recalculateProgress(String userId, String skillId, UserProgress existingProgress) { // 获取技能定义 Skill skill skillRepository.findBySkillId(skillId) .orElseThrow(() - new RuntimeException(Skill not found: skillId)); // 查询用户的有效完成行为 ListUserAction completedActions userActionRepository .findCompletedActions(userId, skillId, LocalDateTime.now().minusMonths(3)); // 只统计最近3个月 // 统计唯一完成的学习项 SetString completedItemIds completedActions.stream() .map(UserAction::getItemId) .collect(Collectors.toSet()); int completedCount completedItemIds.size(); BigDecimal progressPercent skill.getTotalItems() 0 ? BigDecimal.valueOf(completedCount * 100.0 / skill.getTotalItems()) : BigDecimal.ZERO; // 确定状态 String status calculateStatus(completedCount, skill.getRequiredItems(), skill.getTotalItems()); // 获取最近活动时间 LocalDateTime lastActionTime completedActions.stream() .map(UserAction::getActionTime) .max(LocalDateTime::compareTo) .orElse(existingProgress.getLastActionTime()); // 更新进度记录使用乐观锁防止并发更新 existingProgress.setCompletedItems(completedCount); existingProgress.setTotalItems(skill.getTotalItems()); existingProgress.setProgressPercent(progressPercent); existingProgress.setStatus(status); existingProgress.setLastActionTime(lastActionTime); try { return userProgressRepository.save(existingProgress); } catch (ObjectOptimisticLockingFailureException e) { // 乐观锁冲突重新查询并更新 log.warn(Optimistic lock conflict for user progress: {} - {}, retrying, userId, skillId); UserProgress freshProgress userProgressRepository .findByUserIdAndSkillId(userId, skillId) .orElse(existingProgress); return recalculateProgress(userId, skillId, freshProgress); } } /** * 根据完成情况计算状态 */ private String calculateStatus(int completedCount, int requiredItems, int totalItems) { if (completedCount 0) { return NOT_STARTED; } else if (completedCount requiredItems) { return COMPLETED; } else { return IN_PROGRESS; } } /** * 从缓存获取进度 */ private ProgressResultDTO getProgressFromCache(String userId, String skillId) { String cacheKey String.format(PROGRESS_CACHE_KEY, userId, skillId); try { return (ProgressResultDTO) redisTemplate.opsForValue().get(cacheKey); } catch (Exception e) { log.error(Redis cache error for key: {}, cacheKey, e); return null; } } /** * 缓存进度结果 */ private void cacheProgress(String userId, String skillId, ProgressResultDTO progress) { String cacheKey String.format(PROGRESS_CACHE_KEY, userId, skillId); try { redisTemplate.opsForValue().set(cacheKey, progress, Duration.ofSeconds(300)); // 缓存5分钟 } catch (Exception e) { log.error(Redis cache set error for key: {}, cacheKey, e); } } }3.4 数据访问层实现创建对应的Repository接口使用Spring Data JPA简化数据库操作UserProgressRepositoryRepository public interface UserProgressRepository extends JpaRepositoryUserProgress, Long { OptionalUserProgress findByUserIdAndSkillId(String userId, String skillId); Modifying Query(UPDATE UserProgress up SET up.completedItems :completedItems, up.progressPercent :progressPercent, up.status :status, up.lastActionTime :lastActionTime WHERE up.userId :userId AND up.skillId :skillId) int updateProgress(Param(userId) String userId, Param(skillId) String skillId, Param(completedItems) Integer completedItems, Param(progressPercent) BigDecimal progressPercent, Param(status) String status, Param(lastActionTime) LocalDateTime lastActionTime); ListUserProgress findBySkillIdAndStatus(String skillId, String status, Pageable pageable); }UserActionRepository包含复杂查询Repository public interface UserActionRepository extends JpaRepositoryUserAction, Long { Query(SELECT ua FROM UserAction ua WHERE ua.userId :userId AND ua.skillId :skillId AND ua.actionTime :sinceTime AND ua.actionType IN (COMPLETE, TEST_PASS) ORDER BY ua.actionTime DESC) ListUserAction findCompletedActions(Param(userId) String userId, Param(skillId) String skillId, Param(sinceTime) LocalDateTime sinceTime); Query(SELECT COUNT(DISTINCT ua.itemId) FROM UserAction ua WHERE ua.userId :userId AND ua.skillId :skillId AND ua.actionType IN (COMPLETE, TEST_PASS)) Integer countCompletedItems(Param(userId) String userId, Param(skillId) String skillId); Query(value SELECT ua.item_id, MAX(ua.action_time) as last_action_time FROM user_action ua WHERE ua.user_id :userId AND ua.skill_id :skillId GROUP BY ua.item_id ORDER BY last_action_time DESC LIMIT 10, nativeQuery true) ListObject[] findRecentLearningItems(Param(userId) String userId, Param(skillId) String skillId); }4. REST API设计与接口实现4.1 进度查询接口设计设计RESTful风格的进度查询接口支持多种查询方式RestController RequestMapping(/api/progress) Validated public class ProgressController { Autowired private ProgressService progressService; /** * 查询指定用户在指定技能上的进度 */ GetMapping(/user/{userId}/skill/{skillId}) public ResponseEntityProgressResultDTO getProgress( PathVariable String userId, PathVariable String skillId) { ProgressResultDTO progress progressService.calculateUserProgress(userId, skillId); return ResponseEntity.ok(progress); } /** * 批量查询用户在多技能上的进度 */ PostMapping(/batch) public ResponseEntityMapString, ProgressResultDTO getBatchProgress( RequestBody Valid ProgressBatchQueryDTO queryDTO) { MapString, ProgressResultDTO results progressService .batchCalculateProgress(queryDTO.getUserId(), queryDTO.getSkillIds()); return ResponseEntity.ok(results); } /** * 查询技能学习排行榜 */ GetMapping(/skill/{skillId}/leaderboard) public ResponseEntityListProgressResultDTO getLeaderboard( PathVariable String skillId, RequestParam(defaultValue 10) Integer limit) { ListProgressResultDTO leaderboard progressService .getSkillLeaderboard(skillId, Math.min(limit, 100)); return ResponseEntity.ok(leaderboard); } }4.2 DTO对象定义定义清晰的数据传输对象确保接口契约明确ProgressQueryDTOData public class ProgressQueryDTO { NotBlank(message 用户ID不能为空) private String userId; NotBlank(message 技能ID不能为空) private String skillId; private Boolean forceRefresh false; // 是否强制刷新缓存 }ProgressResultDTOData Builder public class ProgressResultDTO { private String userId; private String skillId; private String skillName; private Integer completedItems; private Integer totalItems; private BigDecimal progressPercent; private String progressStatus; // NOT_STARTED, IN_PROGRESS, COMPLETED private LocalDateTime lastActionTime; private LocalDateTime calculatedTime; private ListRecentItemDTO recentItems; // 最近学习项 // 预估完成时间基于学习频率 private LocalDateTime estimatedCompletionTime; // 相对于同龄用户的位置 private String percentile; }ProgressBatchQueryDTOData public class ProgressBatchQueryDTO { NotBlank(message 用户ID不能为空) private String userId; NotEmpty(message 技能ID列表不能为空) private ListString skillIds; }4.3 接口测试与验证使用Postman或curl测试接口功能单个进度查询测试# 查询用户123在技能75-SKill上的进度 curl -X GET http://localhost:8080/api/progress/user/123/skill/75-SKill \ -H Content-Type: application/json预期响应{ userId: 123, skillId: 75-SKill, skillName: Java高级编程, completedItems: 8, totalItems: 12, progressPercent: 66.67, progressStatus: IN_PROGRESS, lastActionTime: 2023-10-15T14:30:00, calculatedTime: 2023-10-15T15:45:30, recentItems: [ { itemId: item-8, itemName: 多线程编程, actionTime: 2023-10-15T14:30:00 } ], estimatedCompletionTime: 2023-10-20T00:00:00, percentile: 前25% }批量进度查询测试curl -X POST http://localhost:8080/api/progress/batch \ -H Content-Type: application/json \ -d { userId: 123, skillIds: [75-SKill, 76-SKill, 77-SKill] }5. 性能优化与缓存策略5.1 多级缓存架构设计为了应对高并发查询采用多级缓存策略本地缓存Caffeine缓存热点用户的进度数据过期时间短1分钟Redis分布式缓存缓存所有用户的进度数据过期时间中等5分钟数据库缓存user_progress表持久化存储进度计算结果配置Caffeine本地缓存Configuration public class CacheConfig { Bean public CacheManager cacheManager() { CaffeineCacheManager cacheManager new CaffeineCacheManager(); cacheManager.setCaffeine(Caffeine.newBuilder() .expireAfterWrite(1, TimeUnit.MINUTES) .maximumSize(1000) .recordStats()); return cacheManager; } }在Service层实现缓存逻辑Service public class ProgressServiceImpl implements ProgressService { Cacheable(value progress, key #userId : #skillId) Override public ProgressResultDTO calculateUserProgress(String userId, String skillId) { // 实现逻辑... } }5.2 数据库查询优化针对大数据量的用户行为表需要优化查询性能添加合适的索引-- 用户行为表的核心查询索引 ALTER TABLE user_action ADD INDEX idx_user_skill_time (user_id, skill_id, action_time); ALTER TABLE user_action ADD INDEX idx_skill_item_user (skill_id, item_id, user_id); -- 用户进度表的查询索引 ALTER TABLE user_progress ADD INDEX idx_user_status (user_id, status); ALTER TABLE user_progress ADD INDEX idx_skill_progress (skill_id, progress_percent);使用覆盖索引优化统计查询-- 优化完成项统计查询 CREATE INDEX idx_cover_complete_count ON user_action (user_id, skill_id, item_id, action_type) WHERE action_type IN (COMPLETE, TEST_PASS);5.3 异步计算与消息队列对于实时性要求不极高的场景可以使用消息队列异步更新进度Service public class ProgressUpdateService { Autowired private AmqpTemplate amqpTemplate; private static final String PROGRESS_UPDATE_QUEUE progress.update.queue; /** * 发送进度更新消息 */ public void sendProgressUpdate(String userId, String skillId, String itemId, String actionType) { ProgressUpdateMessage message ProgressUpdateMessage.builder() .userId(userId) .skillId(skillId) .itemId(itemId) .actionType(actionType) .timestamp(LocalDateTime.now()) .build(); amqpTemplate.convertAndSend(PROGRESS_UPDATE_QUEUE, message); } } Component Slf4j public class ProgressUpdateListener { Autowired private ProgressService progressService; RabbitListener(queues progress.update.queue) public void handleProgressUpdate(ProgressUpdateMessage message) { try { // 异步重新计算进度 progressService.calculateUserProgress(message.getUserId(), message.getSkillId()); log.info(Progress updated asynchronously for user: {}, skill: {}, message.getUserId(), message.getSkillId()); } catch (Exception e) { log.error(Failed to update progress asynchronously, e); } } }6. 常见问题排查与解决方案6.1 进度数据不一致问题进度数据不一致是最常见的问题通常表现为界面显示进度与实际情况不符。问题现象用户完成了学习项但进度百分比没有更新进度显示为0%但用户已有学习记录不同终端显示进度不一致排查步骤检查用户行为记录-- 查询指定用户的有效行为记录 SELECT * FROM user_action WHERE user_id 123 AND skill_id 75-SKill AND action_time DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY action_time DESC;检查进度缓存表-- 查询进度缓存记录 SELECT * FROM user_progress WHERE user_id 123 AND skill_id 75-SKill;检查Redis缓存# 查看Redis中的进度缓存 redis-cli GET progress:123:75-SKill检查计算日志查看应用日志中是否有进度计算错误或异常。解决方案问题原因解决方案预防措施行为记录未正确入库修复行为记录入库逻辑添加重试机制加强数据入库的异常处理和监控进度计算服务异常重启服务重新触发计算添加服务健康检查和自动恢复缓存未正确更新手动清除缓存触发重新计算完善缓存更新机制添加监控告警并发更新导致数据错乱使用乐观锁重试机制优化数据库事务隔离级别6.2 性能问题排查当进度查询响应变慢时需要系统性地排查性能瓶颈。性能排查清单数据库连接池检查// 监控数据库连接池状态 Bean public DataSource dataSource() { HikariDataSource dataSource new HikariDataSource(); dataSource.setMaximumPoolSize(20); dataSource.setMinimumIdle(5); dataSource.setIdleTimeout(300000); dataSource.setConnectionTimeout(20000); return dataSource; }慢查询分析-- 开启MySQL慢查询日志 SET GLOBAL slow_query_log 1; SET GLOBAL long_query_time 1; -- 分析慢查询日志 mysqldumpslow -s t /var/log/mysql/slow.logRedis性能监控# 查看Redis性能指标 redis-cli info stats redis-cli info memoryJVM监控使用JVisualVM或Arthas监控应用性能。优化建议对高频查询接口添加限流措施对大数据量表进行分库分表使用读写分离降低主库压力对历史数据进行归档处理6.3 数据修复脚本当出现数据不一致时需要准备数据修复脚本Component public class ProgressDataRepairService { Autowired private UserProgressRepository userProgressRepository; Autowired private UserActionRepository userActionRepository; /** * 修复指定用户的进度数据 */ Transactional public void repairUserProgress(String userId, String skillId) { // 1. 删除可能存在的错误进度记录 userProgressRepository.findByUserIdAndSkillId(userId, skillId) .ifPresent(progress - userProgressRepository.delete(progress)); // 2. 重新计算并保存进度 UserProgress newProgress recalculateProgress(userId, skillId); userProgressRepository.save(newProgress); // 3. 清除Redis缓存 clearRedisCache(userId, skillId); } /** * 批量修复某个技能的所有用户进度 */ public void batchRepairSkillProgress(String skillId, int batchSize) { int page 0; ListString userIds; do { // 分页获取需要修复的用户ID Pageable pageable PageRequest.of(page, batchSize); userIds userActionRepository.findDistinctUserIdsBySkillId(skillId, pageable); for (String userId : userIds) { try { repairUserProgress(userId, skillId); } catch (Exception e) { log.error(Failed to repair progress for user: {}, skill: {}, userId, skillId, e); } } page; } while (!userIds.isEmpty()); } }