Java量表系统架构设计：构建企业级问卷调查平台

张

张建站

2026/4/24 21:32:37

10分钟阅读

1. 摘要量表系统问卷调查系统是数据采集的核心基础设施广泛应用于市场调研、用户反馈、医疗评估、人才测评等场景。与电商订单系统不同量表系统面临动态表单生成、复杂逻辑跳转、海量答卷存储、实时统计分析等独特挑战。本文将从零开始深入剖析Java量表系统的架构设计涵盖量表元数据建模、答卷存储方案、逻辑跳转引擎、实时统计聚合、导出与安全控制提供可直接落地的企业级方案。2. 量表系统的核心挑战挑战维度具体问题业务后果动态表单题目类型多样单选/多选/填空/矩阵/评分前端渲染复杂存储结构难设计逻辑跳转基于答案的题目联动、跳过、显示隐藏引擎复杂度高易出bug海量答卷单份量表可达10万答卷查询统计慢存储成本高实时统计需要实时计算各选项占比、得分分布聚合计算压力大多租户隔离SaaS平台需支持不同组织数据隔离权限与数据安全3. 量表系统的业务边界核心职责量表/问卷的CRUD及版本管理题目元数据管理类型、选项、校验规则逻辑跳转引擎答卷数据收集与校验实时统计与报表生成多格式数据导出不负责用户认证授权对接统一认证消息推送对接消息中心高级BI分析对接专业BI4. 量表元数据模型设计核心这是量表系统的核心数据结构需要支持无限层级的题目组织和多种题型。4.1 量表主表t_surveysqlCREATE TABLE t_survey ( id bigint(20) NOT NULL COMMENT 量表ID, survey_uuid varchar(64) NOT NULL COMMENT 量表唯一标识对外暴露, tenant_id bigint(20) NOT NULL COMMENT 租户ID多租户隔离, title varchar(200) NOT NULL COMMENT 量表标题, description text COMMENT 量表说明, logo_url varchar(500) COMMENT 量表Logo, status tinyint(4) NOT NULL DEFAULT 1 COMMENT 状态1草稿2发布3关闭4归档, version int(11) NOT NULL DEFAULT 1 COMMENT 版本号, start_time datetime COMMENT 生效开始时间, end_time datetime COMMENT 生效结束时间, max_submit_count int(11) DEFAULT 1 COMMENT 每个用户最大提交次数0不限, need_login tinyint(1) DEFAULT 1 COMMENT 是否需要登录, anonymous tinyint(1) DEFAULT 0 COMMENT 是否匿名答卷, thank_message varchar(500) COMMENT 提交后感谢语, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_survey_uuid (survey_uuid), KEY idx_tenant_status (tenant_id, status), KEY idx_status_time (status, start_time, end_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT量表主表;4.2 题目表t_question支持复杂嵌套sqlCREATE TABLE t_question ( id bigint(20) NOT NULL AUTO_INCREMENT, survey_id bigint(20) NOT NULL COMMENT 所属量表ID, parent_question_id bigint(20) DEFAULT 0 COMMENT 父题目ID用于矩阵题等嵌套, question_uuid varchar(64) NOT NULL COMMENT 题目唯一标识, question_type varchar(32) NOT NULL COMMENT 题型radio/checkbox/text/textarea/rating/matrix/nps/sort, title text NOT NULL COMMENT 题目内容, description text COMMENT 题目说明/提示, options json COMMENT 选项配置JSON格式, validation json COMMENT 校验规则JSON, sort_order int(11) NOT NULL DEFAULT 0 COMMENT 排序号, required tinyint(1) DEFAULT 0 COMMENT 是否必填, visible tinyint(1) DEFAULT 1 COMMENT 是否可见用于逻辑控制, score int(11) DEFAULT 0 COMMENT 题目分值用于测评类量表, dimension varchar(64) COMMENT 维度/标签用于分组统计, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_survey_id (survey_id), KEY idx_parent_id (parent_question_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT题目表;4.3 选项配置JSON结构示例json// 单选题的options字段 { options: [ {value: A, label: 非常满意, score: 5}, {value: B, label: 满意, score: 4}, {value: C, label: 一般, score: 3}, {value: D, label: 不满意, score: 2}, {value: E, label: 非常不满意, score: 1} ], random_order: false, other_option: false, max_select: 1 } // 矩阵题的options字段 { rows: [ {value: q1, label: 产品易用性}, {value: q2, label: 界面美观度}, {value: q3, label: 响应速度} ], columns: [ {value: 1, label: 非常不满意}, {value: 2, label: 不满意}, {value: 3, label: 一般}, {value: 4, label: 满意}, {value: 5, label: 非常满意} ], column_scores: [1,2,3,4,5] }4.4 逻辑跳转表t_survey_logicsqlCREATE TABLE t_survey_logic ( id bigint(20) NOT NULL AUTO_INCREMENT, survey_id bigint(20) NOT NULL, source_question_id bigint(20) NOT NULL COMMENT 源题目ID, source_option_value varchar(100) NOT NULL COMMENT 触发选项值, target_question_id bigint(20) NOT NULL COMMENT 目标题目ID, action varchar(20) NOT NULL DEFAULT show COMMENT 动作show/hide/jump, priority int(11) DEFAULT 0 COMMENT 优先级多条规则冲突时, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_survey_source (survey_id, source_question_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT逻辑跳转规则表;5. 答卷数据存储方案两种模式5.1 模式一EAV模型适合灵活查询sql-- 答卷主表 CREATE TABLE t_answer ( id bigint(20) NOT NULL AUTO_INCREMENT, answer_uuid varchar(64) NOT NULL COMMENT 答卷唯一标识, survey_id bigint(20) NOT NULL, user_id bigint(20) DEFAULT NULL COMMENT 用户ID需登录时, respondent_id varchar(100) COMMENT 受访者标识匿名时, ip_address varchar(45) COMMENT IP地址, user_agent varchar(500) COMMENT 设备信息, answer_duration int(11) DEFAULT 0 COMMENT 答题耗时秒, total_score int(11) DEFAULT 0 COMMENT 总得分, status tinyint(1) DEFAULT 1 COMMENT 状态1有效2无效, submit_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_answer_uuid (answer_uuid), KEY idx_survey_user (survey_id, user_id), KEY idx_survey_time (survey_id, submit_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT答卷主表; -- 答卷明细表EAV CREATE TABLE t_answer_detail ( id bigint(20) NOT NULL AUTO_INCREMENT, answer_id bigint(20) NOT NULL, question_id bigint(20) NOT NULL, question_type varchar(32) NOT NULL, answer_value text COMMENT 答案值根据题型不同格式不同, score int(11) DEFAULT 0 COMMENT 本题得分, create_time datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_answer_id (answer_id), KEY idx_question_id (question_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT答卷明细表;5.2 模式二JSON聚合适合高写入性能sql-- JSON聚合模式推荐用于高并发场景 CREATE TABLE t_answer_aggregate ( id bigint(20) NOT NULL AUTO_INCREMENT, answer_uuid varchar(64) NOT NULL, survey_id bigint(20) NOT NULL, user_id bigint(20) DEFAULT NULL, answers json NOT NULL COMMENT 所有答案聚合, total_score int(11) DEFAULT 0, submit_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_answer_uuid (answer_uuid), KEY idx_survey_time (survey_id, submit_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT答卷聚合表; -- answers JSON结构示例 { q_1001: {type: radio, value: A, score: 5}, q_1002: {type: checkbox, value: [A, C], score: 8}, q_1003: {type: text, value: 用户体验很好} }选型建议EAV模式适合需要按题目内容复杂查询、生成明细报表的场景JSON模式适合高并发写入、主要用于统计而非明细查询的场景混合模式热数据用JSON冷数据迁移至EAV或数仓6. 逻辑跳转引擎设计核心难点6.1 引擎架构6.2 跳转引擎核心代码javaComponent public class LogicJumpEngine { Autowired private SurveyLogicMapper logicMapper; /** * 根据当前答案计算下一批可见题目 */ public ListLong calculateVisibleQuestions( Long surveyId, MapLong, Object currentAnswers, // 当前已答题目及答案 ListQuestion allQuestions) { // 所有题目 // 获取所有跳转规则 ListSurveyLogic rules logicMapper.selectBySurveyId(surveyId); // 按题目分组规则 MapLong, ListSurveyLogic ruleMap rules.stream() .collect(Collectors.groupingBy(SurveyLogic::getSourceQuestionId)); // 存储被隐藏的题目 SetLong hiddenQuestions new HashSet(); // 遍历每个规则 for (Map.EntryLong, ListSurveyLogic entry : ruleMap.entrySet()) { Long sourceId entry.getKey(); Object answerValue currentAnswers.get(sourceId); if (answerValue null) continue; for (SurveyLogic rule : entry.getValue()) { // 匹配条件 if (matchCondition(rule, answerValue)) { if (hide.equals(rule.getAction())) { hiddenQuestions.add(rule.getTargetQuestionId()); } else if (show.equals(rule.getAction())) { hiddenQuestions.remove(rule.getTargetQuestionId()); } else if (jump.equals(rule.getAction())) { // 跳转逻辑忽略跳转后的所有题目 return getQuestionsBeforeTarget(allQuestions, rule.getTargetQuestionId()); } } } } // 返回未被隐藏的题目 return allQuestions.stream() .map(Question::getId) .filter(id - !hiddenQuestions.contains(id)) .collect(Collectors.toList()); } private boolean matchCondition(SurveyLogic rule, Object answerValue) { String ruleValue rule.getSourceOptionValue(); if (answerValue instanceof String) { return ruleValue.equals(answerValue); } else if (answerValue instanceof List) { // 多选题只要包含规则值就触发 return ((List?) answerValue).contains(ruleValue); } return false; } }6.3 前端联动实现Vue3示例vuetemplate div v-forquestion in visibleQuestions :keyquestion.id component :isgetComponentByType(question.type) :questionquestion v-modelanswers[question.id] update:model-valueonAnswerChange(question.id) / /div /template script setup import { ref, watch } from vue import { logicJumpEngine } from /api/survey const answers ref({}) const visibleQuestions ref([]) const onAnswerChange async (questionId) { // 调用后端引擎计算下一批可见题目 const result await logicJumpEngine.calculate({ surveyId: props.surveyId, currentAnswers: answers.value }) visibleQuestions.value result.visibleQuestions } /script7. 实时统计聚合设计7.1 统计指标体系统计类型指标实现方式整体概览总提交数、独立用户数、平均耗时COUNT DISTINCT单选题各选项数量、占比、饼图数据GROUP BY多选题各选项被选次数可多选JSON解析聚合评分题平均分、中位数、分布直方图AVG 分位数计算NPS题推荐者比例、贬损者比例按分值分组矩阵题各行列平均分、热力图数据多维度聚合7.2 统计服务实现javaService public class SurveyStatisticsService { Autowired private AnswerDetailMapper answerDetailMapper; Autowired private RedisTemplateString, Object redisTemplate; /** * 获取量表统计带缓存 */ Cacheable(value survey:stats, key #surveyId _ #version) public SurveyStatistics getStatistics(Long surveyId, Integer version) { SurveyStatistics stats new SurveyStatistics(); // 1. 整体统计 stats.setTotalCount(answerDetailMapper.countBySurveyId(surveyId)); stats.setAvgDuration(answerDetailMapper.avgDurationBySurveyId(surveyId)); // 2. 获取所有题目 ListQuestion questions questionMapper.selectBySurveyId(surveyId); // 3. 逐题统计 for (Question question : questions) { QuestionStatistics qs calculateQuestionStatistics(question); stats.getQuestionStats().put(question.getId(), qs); } return stats; } private QuestionStatistics calculateQuestionStatistics(Question question) { QuestionStatistics stats new QuestionStatistics(); switch (question.getQuestionType()) { case radio: // 单选统计 ListOptionCount counts answerDetailMapper.countRadioOptions( question.getSurveyId(), question.getId() ); stats.setOptionsCount(counts); break; case checkbox: // 多选统计需要展开JSON数组 ListOptionCount multiCounts answerDetailMapper.countCheckboxOptions( question.getSurveyId(), question.getId() ); stats.setOptionsCount(multiCounts); break; case rating: // 评分统计 Double avgScore answerDetailMapper.avgScore( question.getSurveyId(), question.getId() ); stats.setAvgScore(avgScore); stats.setDistribution(getScoreDistribution(question)); break; case matrix: // 矩阵统计按行列聚合 stats.setMatrixData(calculateMatrixStatistics(question)); break; } return stats; } /** * 实时更新统计高并发场景使用Redis原子操作 */ public void updateRealTimeStat(Long surveyId, Long questionId, String optionValue, Integer score) { String key String.format(survey:realtime:%d:q:%d, surveyId, questionId); // 使用Hash结构存储各选项计数 redisTemplate.opsForHash().increment(key, optionValue, 1); // 评分题维护总和与计数 if (score ! null) { redisTemplate.opsForHash().increment(key, _sum, score); redisTemplate.opsForHash().increment(key, _count, 1); } } }7.3 统计查询优化物化视图sql-- 创建统计物化表定时刷新 CREATE TABLE t_survey_stat_snapshot ( survey_id bigint(20) NOT NULL, snapshot_date date NOT NULL, total_answers int(11) DEFAULT 0, avg_score decimal(10,2) DEFAULT NULL, question_stats json COMMENT 各题目统计数据快照, create_time datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (survey_id, snapshot_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT量表统计快照; -- 定时任务每天凌晨刷新 Scheduled(cron 0 0 2 * * ?) public void refreshStatisticsSnapshot() { // 计算前一天的统计数据并存入快照表 // 前端展示历史趋势时直接查询快照表 }8. 高并发提交处理8.1 防重复提交javaComponent public class DuplicateSubmitGuard { Autowired private RedisTemplateString, String redisTemplate; /** * 基于用户量表Token的防重 */ public boolean trySubmit(String surveyUuid, Long userId, String token) { String key String.format(submit:lock:%s:%d:%s, surveyUuid, userId, token); // SET NX EX 3030秒内相同token不能重复提交 Boolean success redisTemplate.opsForValue() .setIfAbsent(key, 1, Duration.ofSeconds(30)); return Boolean.TRUE.equals(success); } }8.2 异步提交与批量入库javaService public class AnswerSubmitService { Autowired private RocketMQTemplate mqTemplate; Autowired private AnswerBuffer answerBuffer; // 本地缓冲队列 /** * 提交答卷高并发版本 */ public String submitAnswer(AnswerSubmitReq req) { // 1. 防重检查 if (!duplicateGuard.trySubmit(req.getSurveyUuid(), req.getUserId(), req.getToken())) { throw new BusinessException(请勿重复提交); } // 2. 生成答卷ID String answerUuid UUID.randomUUID().toString(); // 3. 发送MQ异步处理 AnswerMessage msg new AnswerMessage(answerUuid, req); mqTemplate.send(answer-submit-topic, msg); // 4. 立即返回成功 return answerUuid; } /** * 批量消费入库 */ Component RocketMQMessageListener(topic answer-submit-topic, consumerGroup answer-group) public class AnswerConsumer implements RocketMQListenerAnswerMessage { private final ListAnswerAggregate buffer new ArrayList(); Override public void onMessage(AnswerMessage msg) { buffer.add(buildAggregate(msg)); // 批量入库每100条或每秒刷新 if (buffer.size() 100) { batchInsert(buffer); buffer.clear(); } } Scheduled(fixedDelay 1000) public void flushBuffer() { if (!buffer.isEmpty()) { batchInsert(buffer); buffer.clear(); } } } }9. 数据导出设计9.1 多格式导出支持javaService public class ExportService { /** * 导出答卷数据支持Excel/CSV/SPSS */ public void exportAnswers(Long surveyId, ExportFormat format, HttpServletResponse response) { // 1. 获取量表元数据作为表头 ListQuestion questions questionMapper.selectBySurveyId(surveyId); // 2. 流式查询答卷避免OOM CursorAnswerDetail cursor answerDetailMapper.streamBySurveyId(surveyId); // 3. 根据格式生成文件 switch (format) { case EXCEL: exportToExcel(questions, cursor, response); break; case CSV: exportToCsv(questions, cursor, response); break; case SPSS: exportToSpss(questions, cursor, response); break; } } private void exportToExcel(ListQuestion questions, CursorAnswerDetail cursor, HttpServletResponse response) { SXSSFWorkbook workbook new SXSSFWorkbook(100); // 内存中保留100行 Sheet sheet workbook.createSheet(答卷数据); // 创建表头 Row header sheet.createRow(0); for (int i 0; i questions.size(); i) { header.createCell(i).setCellValue(questions.get(i).getTitle()); } // 写入数据流式 int rowNum 1; MapLong, MapLong, String answerMap new HashMap(); for (AnswerDetail detail : cursor) { answerMap.computeIfAbsent(detail.getAnswerId(), k - new HashMap()) .put(detail.getQuestionId(), detail.getAnswerValue()); // 当收集完一个答卷的所有题目后输出一行 if (isAnswerComplete(detail.getAnswerId(), questions.size(), answerMap)) { Row row sheet.createRow(rowNum); for (int i 0; i questions.size(); i) { String value answerMap.get(detail.getAnswerId()) .getOrDefault(questions.get(i).getId(), ); row.createCell(i).setCellValue(value); } answerMap.remove(detail.getAnswerId()); } } // 响应输出 response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); workbook.write(response.getOutputStream()); workbook.dispose(); } }9.2 大数据量异步导出javaService public class AsyncExportService { Autowired private OSSClient ossClient; /** * 异步导出适用于10万答卷 */ public String asyncExport(Long surveyId, String callbackUrl) { String taskId UUID.randomUUID().toString(); // 提交异步任务 CompletableFuture.runAsync(() - { try { // 执行导出 File excelFile doExport(surveyId); // 上传到OSS String ossUrl ossClient.upload(excelFile); // 回调通知 httpClient.post(callbackUrl, Map.of(taskId, taskId, url, ossUrl)); } catch (Exception e) { log.error(导出失败, e); httpClient.post(callbackUrl, Map.of(taskId, taskId, error, e.getMessage())); } }); return taskId; } }10. 多租户与权限控制10.1 数据隔离方案java// 方案一数据库隔离每个租户独立数据库 // 动态数据源路由 Component public class TenantDataSourceRouter implements AbstractRoutingDataSource { Override protected Object determineCurrentLookupKey() { String tenantId TenantContext.getCurrentTenant(); return datasource_ tenantId; } } // 方案二表隔离使用tenant_id字段★推荐 Component public class TenantInterceptor implements HandlerInterceptor { Override public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) { String tenantId extractTenantFromToken(request); TenantContext.setCurrentTenant(tenantId); // MyBatis Plus自动注入tenant_id TenantSqlInjector.setTenantId(tenantId); return true; } }10.2 权限模型sql-- 量表权限表 CREATE TABLE t_survey_permission ( id bigint(20) NOT NULL AUTO_INCREMENT, survey_id bigint(20) NOT NULL, user_id bigint(20) NOT NULL, permission_type varchar(50) NOT NULL COMMENT view/edit/delete/export, create_time datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_survey_user_permission (survey_id, user_id, permission_type) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;11. 性能优化清单优化点方案效果题目元数据缓存Caffeine本地缓存5分钟TTL减少80%数据库查询统计结果缓存Redis缓存1小时TTLP99耗时从2秒降至50ms答卷批量入库本地缓冲批量INSERTTPS提升10倍逻辑跳转预计算前端本地规则引擎JSON下发减少80%后端调用历史数据归档3个月前数据迁移至归档表主表查询速度提升60%导出流式处理SXSSFWorkbook 游标查询支持百万级导出12. 总结与最佳实践关注点最佳实践元数据设计用JSON字段存储选项配置避免频繁DDL支持版本管理答卷存储小数据量用EAV模型大数据量用JSON聚合数仓同步逻辑跳转后端引擎计算可见题目前端渲染复杂规则支持权重实时统计统计结果缓存定时快照 Redis原子更新高并发提交防重令牌 MQ削峰批量入库异步处理数据导出流式导出异步任务 OSS存储多租户表字段隔离 MyBatis Plus自动注入通过以上架构设计Java量表系统能够支持万级并发提交日吞吐量百万级答卷千级题目量表复杂逻辑跳转流畅毫秒级实时统计带缓存亿级答卷存储分表归档