考试微服务-改卷管理-接口优化
2022年6月29日
提测结果
业务流程说明
功能：分页查询当前管理员需要改卷的考试列表，列表中对象如下：
"examId": "20220628150358be9e5e442c95efa44c8aa0",
"examName": "考试名称512",
"questionCount": 9, 题目数量
"subjectiveQuestionCount": 3, 主观题数量
"totalScore": 44,
"passScore": 26,
"submitCount": 500, 交卷人数
"reviewedCount": 0, 已改卷人数
"unReviewedCount": 500, 待改卷人数
"examStartTime": "2020-04-28 02:18:00",
"examEndTime": "2024-07-09 02:18:00",
"createBy": "admin",
"secretLevel": null,
"examStatus": 1
表结构、关联关系、主要字段：
exam_reviewer考试阅卷人表:exam_id、reviewer_id(ALL为所有管理员可阅卷，管理员id则为指定某个管理员可阅卷)
exam考试表：exam_id(主键)、exam_name(考试名次)、total_score(总分)、pass_score(及格分)、start_time(考试开始时间)、end_time(考试结束时间)、create_by(创建者)、secret_level(密卷等级)、source_type(导入试卷1 引入试卷2 组卷3)
answer_record答题记录表:id(主键)、is_post(是否交卷)、is_del(逻辑删除、0则为最后一次答题记录，1则为历史答题记录，每次答题会逻辑删除以前的)、is_check_finish(改卷是否完成：默认值0)
answer_record_detail(答题记录详情表):record_id(答题记录表主键)、question_id(所答题目的id，根据考试添加类型查不同的表)
exam_question表(导入考试和引入考试的题目表，引入考试会从test_paper_question复制题目和选项到这张表)：id(题目主键)、question_type题目类型(1：单选 2：多选 3：填空 4：判断 5：问答)
思路(优化前)：
- 查出当前管理员可以阅卷的所有考试的id：examIdList，即reviewer_id为ALL或当前登录用户id的记录的exam_id字段的集合
- 筛选examIdList中有最后一次答题记录且对应的答题记录详情中包含主观题(question_type为3或5)的examId
- 再根据筛选后的examIdList使用id in，配合用户输入的查询条件来查考试表(exam)，查出考试列表，用stream封装返回结果。
spring-actuator监控结果
数据库一直在返回数据，网络一直占着
代码分析定位：
@Override
public PageInfo<ExamCorrectExamListDTO> getExamCorrectExamList(
ExamCorrectExamListQueryDTO examCorrectExamListQueryDTO) {
// 参数
String examName = examCorrectExamListQueryDTO.getExamName();
Integer pageNo = examCorrectExamListQueryDTO.getPageNo();
Integer pageSize = examCorrectExamListQueryDTO.getPageSize();
// 发布时间范围
String startTime = examCorrectExamListQueryDTO.getStartTime();
String endTime = examCorrectExamListQueryDTO.getEndTime();
DateUtil.validStr2Date(startTime);
DateUtil.validStr2Date(endTime);
String userId = UserThreadContext.getUserId();
PageInfo<Exam> sqlPageInfo = new PageInfo<>();
sqlPageInfo.setPageSize(pageSize);
sqlPageInfo.setPageNum(pageNo);
// 1 获取当前用户可以改卷的试卷id列表
List<String> examIds = examReviewerService.getExamIdListByUserId(userId);
LambdaQueryWrapper<AnswerRecord> query = new LambdaQueryWrapper<AnswerRecord>();
query.in(AnswerRecord::getExamId, examIds);
List<AnswerRecord> answerRecordList = baseMapper.selectList(query);
Map<String, List<AnswerRecord>> answerRecordListMap = answerRecordList.stream()
.collect(Collectors.groupingBy(AnswerRecord::getExamId));
// 2 过滤掉没有人工阅卷的答题记录的试卷id
if (!CollectionUtils.isEmpty(examIds)) {
examIds =
examIds.stream()
.filter(
examId -> {
List<AnswerRecord> answerRecords =
baseMapper.selectList(
new LambdaQueryWrapper<AnswerRecord>()
.eq(AnswerRecord::getExamId, examId));
List<AnswerRecord> answerRecordList = answerRecords.stream().filter(answerRecord -> {
int checkMethod = examQuestionService.getExamCheckMethodByRecordId(
answerRecord.getId());
return checkMethod == ExamConstant.EXAM_IS_USER_CHECK;
}).collect(Collectors.toList());
return !CollectionUtils.isEmpty(answerRecordList);
})
.collect(Collectors.toList());
}
List<ExamCorrectExamListDTO> examCorrectExamListDTOList = new ArrayList<>();
if (!CollectionUtils.isEmpty(examIds)) {
// 3 按照id查出考试列表
LambdaQueryWrapper<Exam> examWrapper =
new LambdaQueryWrapper<Exam>()
.in(Exam::getId, examIds)
.eq(Exam::getExamType, ExamTypeEnum.EXAM.getCode())
.ge(StringUtils.isNotBlank(startTime), Exam::getPublishTime, startTime)
.le(StringUtils.isNotBlank(endTime), Exam::getPublishTime, endTime)
.like(StringUtils.isNotBlank(examName), Exam::getExamName, examName)
.orderByDesc(Exam::getPublishTime, Exam::getCreateTime);
sqlPageInfo =
PageHelper.startPage(pageNo, pageSize)
.doSelectPageInfo(() -> examService.list(examWrapper));
// 4 构建返回结果
examCorrectExamListDTOList =
sqlPageInfo.getList().stream()
.map(
exam -> {
// 该考试的答题记录
List<AnswerRecord> answerRecords =
baseMapper.selectList(
new LambdaQueryWrapper<AnswerRecord>()
.eq(AnswerRecord::getExamId, exam.getId()));
// 交卷人数
int submitCount = (int) answerRecords.stream()
.filter(answerRecord -> answerRecord.getIsPost() == ExamConstant.EXAM_IS_POST).count();
// 已改卷数
int reviewedCount =
(int)
answerRecords.stream()
.filter(
v -> v.getIsCheckFinish() == ExamConstant.EXAM_IS_CHECK_FINISH
&& v.getIsPost() == ExamConstant.EXAM_IS_POST)
.count();
// 未改卷数
int unReviewedCount =
(int)
answerRecords.stream()
.filter(
v ->
v.getIsCheckFinish()
== ExamConstant.EXAM_IS_NOT_CHECK_FINISH
&& v.getIsPost() == ExamConstant.EXAM_IS_POST)
.count();
ExamCorrectExamListDTO examCorrectExamListDTO = ExamCorrectExamListDTO.builder()
.examId(exam.getId())
.examName(exam.getExamName())
.totalScore(exam.getTotalScore())
.passScore(exam.getPassScore())
.submitCount(submitCount)
.reviewedCount(reviewedCount)
.unReviewedCount(unReviewedCount)
.examStartTime(
DateUtil.formatToStr(exam.getStartTime(), DateHelper.YYYYMMDD_HHMMSS))
.examEndTime(
DateUtil.formatToStr(exam.getEndTime(), DateHelper.YYYYMMDD_HHMMSS))
.examStatus(
exam.getEndTime().getTime() > System.currentTimeMillis()
? ExamConstant.EXAM_STATUS_ON
: ExamConstant.EXAM_STATUS_NOT_ON)
.createBy(exam.getCreateBy())
.secretLevel(exam.getSecretLevel())
.build();
Integer examQuestionCount;
Integer examSubjectiveQuestionCount;
if (Integer.parseInt(exam.getSourceType())
== QuestionSourceTypeEnum.ENTER_SOURCE.getCode()) {
examQuestionCount = schemaDetailService.getQuestionCountBySchemaId(exam.getSchemaId());
examSubjectiveQuestionCount = schemaDetailService.getSubjectiveQuestionCountBySchemaId(
exam.getSchemaId());
} else {
examSubjectiveQuestionCount = examQuestionService.getSubjectiveQuestionCount(
exam.getId());
examQuestionCount = examService.getExamQuestionCount(exam.getId());
}
examCorrectExamListDTO.setSubjectiveQuestionCount(examSubjectiveQuestionCount);
examCorrectExamListDTO.setQuestionCount(examQuestionCount);
return examCorrectExamListDTO;
})
.collect(Collectors.toList());
}
// 5分页
PageInfo<ExamCorrectExamListDTO> resultPageInfo = new PageInfo<>();
BeanUtils.copyProperties(sqlPageInfo, resultPageInfo);
resultPageInfo.setList(examCorrectExamListDTOList);
return resultPageInfo;
}
影响性能原因：
查询当前用户可以改卷的考试列表id时未做分页，如果可以改卷的考试非常多，光这一步就需要耗费很长时间,且查出很多的多余数据
// 1 获取当前用户可以改卷的试卷id列表 List<String> examIds = examReviewerService.getExamIdListByUserId(userId);在循环中查询数据，创建回话过多
// 2 过滤掉没有人工阅卷的答题记录的试卷id if (!CollectionUtils.isEmpty(examIds)) { examIds = examIds.stream() .filter( examId -> { List<AnswerRecord> answerRecords = baseMapper.selectList( new LambdaQueryWrapper<AnswerRecord>() .eq(AnswerRecord::getExamId, examId)); List<AnswerRecord> answerRecordList = answerRecords.stream().filter(answerRecord -> { int checkMethod = examQuestionService.getExamCheckMethodByRecordId( answerRecord.getId()); return checkMethod == ExamConstant.EXAM_IS_USER_CHECK; }).collect(Collectors.toList()); return !CollectionUtils.isEmpty(answerRecordList); }) .collect(Collectors.toList()); }循环中还调用了较为耗费性能的方法(考试发布后，题目可以修改，所以需要根据答题记录id判断阅卷方式):
@Override public int getExamCheckMethodByRecordId(String recordId) { List<String> questionIdList = answerRecordDetailService.getQuestionIdListByAnswerRecordId( recordId); AnswerRecord answerRecord = answerRecordService.getById(recordId); Exam exam = examService.getById(answerRecord.getExamId()); if (null == exam || CollectionUtils.isEmpty(questionIdList)) { return ExamConstant.EXAM_IS_SYSTEM_CHECK; } List<Integer> questionType; Integer sourceType = Integer.parseInt(exam.getSourceType()); if (sourceType.equals(QuestionSourceTypeEnum.EXCEL_SOURCE.getCode()) || sourceType .equals(QuestionSourceTypeEnum.LIB_SOURCE.getCode())) { // 导入和引入试卷 List<ExamQuestion> questions = getListByExamIdAndQuestionIdListIgnoreLogicallyDelete( answerRecord.getExamId(), questionIdList); questionType = questions.stream().map(ExamQuestion::getQuestionType).collect(Collectors.toList()); } else { List<LibQuestion> questions = libQuestionService.getListByQuestionIdListIgnoreLogicallyDelete( questionIdList); questionType = questions.stream().map(LibQuestion::getQuestionType).collect(Collectors.toList()); } if (questionType.contains(ExamQuestionTypeEnum.QUESTION_TYPE_QA.getType()) || questionType .contains(ExamQuestionTypeEnum.QUESTION_TYPE_ClOZE.getType())) { return ExamConstant.EXAM_IS_USER_CHECK; } else { return ExamConstant.EXAM_IS_SYSTEM_CHECK; } }
优化方案
改为sql联表查询，而非mybatis-plus单表查询
思路：
exam表是主表，拼接条件查询这张表即可
判断是否有阅卷权限时不适用id in，使用内连接
SELECT ee.* FROM ex_exam ee ,ex_exam_reviewer er WHERE ee.id = er.exam_id and (er.reviewer_id = "ALL" OR er.reviewer_id ='')判断考试是否包含人工阅卷，使用子查询，且是分页查
select 1 a from ex_answer_record_detail a, ex_exam_question q, ex_answer_record ear where a.exam_id = ee.id and a.question_id = q.id and q.question_type in (3,5) and a.record_id = ear.id and ear.is_del = 0 limit 1由于有两种情况，Excel导入的考试和资源库引用的考试查exam_question表，组卷的考试查lib_question表，所以使用union all
select 1 a from ex_answer_record_detail a, ex_lib_question q ,ex_answer_record ear where a.exam_id = ee.id and a.question_id = q.id and q.question_type in (3,5) and a.record_id = ear.id and ear.is_del = 0 limit 1采用exists判断是否需要查询该考试数据
and exists ( (select 1 a from ex_answer_record_detail a, ex_exam_question q, ex_answer_record ear where a.exam_id = ee.id and a.question_id = q.id and q.question_type in (3,5) and a.record_id = ear.id and ear.is_del = 0 limit 1 ) union all (select 1 a from ex_answer_record_detail a, ex_lib_question q ,ex_answer_record ear where a.exam_id = ee.id and a.question_id = q.id and q.question_type in (3,5) and a.record_id = ear.id and ear.is_del = 0 limit 1) )最终sql
SELECT ee.* FROM ex_exam ee ,ex_exam_reviewer er WHERE ee.id = er.exam_id and (er.reviewer_id = "ALL" OR er.reviewer_id ='') and exists ( (select 1 a from ex_answer_record_detail a, ex_exam_question q, ex_answer_record ear where a.exam_id = ee.id and a.question_id = q.id and q.question_type in (3,5) and a.record_id = ear.id and ear.is_del = 0 limit 1 ) union all (select 1 a from ex_answer_record_detail a, ex_lib_question q ,ex_answer_record ear where a.exam_id = ee.id and a.question_id = q.id and q.question_type in (3,5) and a.record_id = ear.id and ear.is_del = 0 limit 1) ) and ee.exam_type = 1 and ee.publish_time >= '' and ee.publish_time <= '' and ee.ex_exam like "%examName%" ORDER BY ee.publish_time,ee.create_time执行耗时
方案落地
将步骤1和步骤2改为sql查询，后续步骤暂时不优化。
<select id="getCorrectExamList" resultMap="BaseResultMap">
SELECT
ee.id as id,
ee.exam_type as exam_type,
ee.exam_name as exam_name,
ee.description as description,
ee.category_id as category_id,
ee.start_time as start_time,
ee.exam_time_count as exam_time_count,
ee.end_time as end_time,
ee.total_score as total_score,
ee.pass_score as pass_score,
ee.degree_difficult as degree_difficult,
ee.is_allow_late as is_allow_late,
ee.check_paper_method as check_paper_method,
ee.source_type as source_type,
ee.schema_id as schema_id,
ee.is_re_exam as is_re_exam,
ee.re_exam_count as re_exam_count,
ee.is_view_answer as is_view_answer,
ee.is_view_score as is_view_score,
ee.is_view_ranking as is_view_ranking,
ee.is_question_seq as is_question_seq,
ee.is_option_seq as is_option_seq,
ee.publish_by as publish_by,
ee.is_publish as is_publish,
ee.sort_no as sort_no,
ee.is_available as is_available,
ee.is_del as is_del,
ee.create_by as create_by,
ee.create_time as create_time,
ee.update_by as update_by,
ee.update_time as update_time,
ee.org_id as org_id,
ee.customer_id as customer_id,
ee.is_train as is_train,
ee.add_method as add_method,
ee.equipment as equipment,
ee.excel_flag as excel_flag
FROM
ex_exam ee ,ex_exam_reviewer er
WHERE
ee.id = er.exam_id
and
(er.reviewer_id = "ALL" OR er.reviewer_id =#{userId})
and exists (
(select 1 a from ex_answer_record_detail a, ex_exam_question q, ex_answer_record ear where a.exam_id = ee.id
and a.question_id = q.id and q.question_type in (3,5) and a.record_id = ear.id and ear.is_del = 0 limit 1 )
union all
(select 1 a from ex_answer_record_detail a, ex_lib_question q ,ex_answer_record ear where a.exam_id = ee.id and
a.question_id = q.id and q.question_type in (3,5) and a.record_id = ear.id and ear.is_del = 0 limit 1)
)
and ee.exam_type = 1
<if test="startTime!=null">
and ee.publish_time >> #{startTime}
</if>
<if test="endTime!=null">
and #{endTime} >> ee.publish_time
</if>
<if test="examName!=null">
and ee.exam_name like CONCAT('%',#{examName},'%')
</if>
ORDER BY ee.publish_time,ee.create_time
</select>
@Override
public PageInfo<ExamCorrectExamListDTO> getExamCorrectExamList(
ExamCorrectExamListQueryDTO examCorrectExamListQueryDTO) {
// 参数
String examName = examCorrectExamListQueryDTO.getExamName();
Integer pageNo = examCorrectExamListQueryDTO.getPageNo();
Integer pageSize = examCorrectExamListQueryDTO.getPageSize();
// 发布时间范围
String startTime = examCorrectExamListQueryDTO.getStartTime();
String endTime = examCorrectExamListQueryDTO.getEndTime();
DateUtil.validStr2Date(startTime);
DateUtil.validStr2Date(endTime);
String userId = UserThreadContext.getUserId();
PageInfo<Exam> sqlPageInfo = new PageInfo<>();
sqlPageInfo.setPageSize(pageSize);
sqlPageInfo.setPageNum(pageNo);
// 1 获取当前用户可以改卷的试卷id列表
List<String> examIds = examReviewerService.getExamIdListByUserId(userId);
// 2 过滤掉没有人工阅卷的答题记录的试卷id
List<ExamCorrectExamListDTO> examCorrectExamListDTOList = new ArrayList<>();
if (!CollectionUtils.isEmpty(examIds)) {
sqlPageInfo =
PageHelper.startPage(pageNo, pageSize)
.doSelectPageInfo(() -> examService.getCorrectExamList(startTime,endTime, examName, userId));
// 4 构建返回结果
examCorrectExamListDTOList =
sqlPageInfo.getList().stream()
.map(
exam -> {
// 该考试的答题记录
List<AnswerRecord> answerRecords =
baseMapper.selectList(
new LambdaQueryWrapper<AnswerRecord>()
.eq(AnswerRecord::getExamId, exam.getId()));
// 交卷人数
int submitCount = (int) answerRecords.stream()
.filter(answerRecord -> answerRecord.getIsPost() == ExamConstant.EXAM_IS_POST).count();
// 已改卷数
int reviewedCount =
(int)
answerRecords.stream()
.filter(
v -> v.getIsCheckFinish() == ExamConstant.EXAM_IS_CHECK_FINISH
&& v.getIsPost() == ExamConstant.EXAM_IS_POST)
.count();
// 未改卷数
int unReviewedCount =
(int)
answerRecords.stream()
.filter(
v ->
v.getIsCheckFinish()
== ExamConstant.EXAM_IS_NOT_CHECK_FINISH
&& v.getIsPost() == ExamConstant.EXAM_IS_POST)
.count();
ExamCorrectExamListDTO examCorrectExamListDTO = ExamCorrectExamListDTO.builder()
.examId(exam.getId())
.examName(exam.getExamName())
.totalScore(exam.getTotalScore())
.passScore(exam.getPassScore())
.submitCount(submitCount)
.reviewedCount(reviewedCount)
.unReviewedCount(unReviewedCount)
.examStartTime(
DateUtil.formatToStr(exam.getStartTime(), DateHelper.YYYYMMDD_HHMMSS))
.examEndTime(
DateUtil.formatToStr(exam.getEndTime(), DateHelper.YYYYMMDD_HHMMSS))
.examStatus(
exam.getEndTime().getTime() > System.currentTimeMillis()
? ExamConstant.EXAM_STATUS_ON
: ExamConstant.EXAM_STATUS_NOT_ON)
.createBy(exam.getCreateBy())
.secretLevel(exam.getSecretLevel())
.build();
Integer examQuestionCount;
Integer examSubjectiveQuestionCount;
if (Integer.parseInt(exam.getSourceType())
== QuestionSourceTypeEnum.ENTER_SOURCE.getCode()) {
examQuestionCount = schemaDetailService.getQuestionCountBySchemaId(exam.getSchemaId());
examSubjectiveQuestionCount = schemaDetailService.getSubjectiveQuestionCountBySchemaId(
exam.getSchemaId());
} else {
examSubjectiveQuestionCount = examQuestionService.getSubjectiveQuestionCount(
exam.getId());
examQuestionCount = examService.getExamQuestionCount(exam.getId());
}
examCorrectExamListDTO.setSubjectiveQuestionCount(examSubjectiveQuestionCount);
examCorrectExamListDTO.setQuestionCount(examQuestionCount);
return examCorrectExamListDTO;
})
.collect(Collectors.toList());
}
// 5分页
PageInfo<ExamCorrectExamListDTO> resultPageInfo = new PageInfo<>();
BeanUtils.copyProperties(sqlPageInfo, resultPageInfo);
resultPageInfo.setList(examCorrectExamListDTOList);
return resultPageInfo;
}
优化结果
可以正常查询出数据
Loading...