关于sql使用MybatisPlus导致mysql5.7占用100%服务器cpu事件
1.故障发生
前几天平台用户反映卡顿发生，我尝试登录平台，发现进入登录页面卡顿严重（平台前后端都在一个服务器中），后尝试进入服务器（移动云），发现远程无法连接，从云进入服务器后查看cpu占用100%，打开任务管理器发现mysql服务占用100%。重启mysql后服务暂时正常，打开程序日志，定位到sql。
// 多次出现报错日志
2025-03-17 08:26:51.771 [http-nio-8093-exec-125] ERROR druid.sql.Statement:148 - {conn-10213, pstmt-35504} execute error. SELECT COUNT(*) FROM (SELECT DISTINCT t1.ID_, t1.NAME_, t1.START_TIME_ AS CREATE_TIME_, t1.ASSIGNEE_, t1.DESCRIPTION_, t1.TASK_DEF_KEY_, t1.DURATION_, t2.BUSINESS_KEY_, t2.PROC_INST_ID_, t2.PROC_DEF_ID_, t2.NAME_ AS proc_inst_name, t2.START_TIME_, t2.END_TIME_, t3.current_user_code AS start_person_code, t3.process_status AS process_status, t7.realname AS start_person, t9.business_url, t3.model_key, t3.case_name, t3.depart_name AS startDepartName FROM act_hi_taskinst t1 INNER JOIN act_hi_procinst t2 ON t1.PROC_INST_ID_ = t2.PROC_INST_ID_ INNER JOIN act_re_procdef t8 ON t2.PROC_DEF_ID_ = t8.ID_ INNER JOIN tbl_flow_extend_hisprocinst t3 ON t1.PROC_INST_ID_ = t3.process_instance_id LEFT JOIN act_hi_identitylink t6 ON t6.TASK_ID_ = t1.ID_ INNER JOIN sys_user t7 ON t7.username = t3.current_user_code LEFT JOIN tbl_flow_model_info t9 ON t8.KEY_ = t9.model_key WHERE (t1.ASSIGNEE_ = ? OR ((t1.ASSIGNEE_ IS NULL OR t1.ASSIGNEE_ = '') AND (t6.USER_ID_ = ? OR t6.GROUP_ID_ IN (SELECT g.group_id_ FROM act_id_membership g WHERE g.user_id_ = ?)))) GROUP BY t2.PROC_INST_ID_ ORDER BY t1.START_TIME_ DESC) TOTAL
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet successfully received from the server was 9,749 milliseconds ago. The last packet sent successfully to the server was 9,751 milliseconds ago.
2.事件处理
定位该sql为MybatisPlus插件的分页功能sql，将我的分页sql包装后查询分页总数，比对二者效率。
2.1原始查询sql与效率
// 原始分页sql
SELECT DISTINCT
t1.ID_,
t1.NAME_,
t1.START_TIME_ as CREATE_TIME_,
t1.ASSIGNEE_,
t1.DESCRIPTION_,
t1.TASK_DEF_KEY_,
t1.DURATION_,
t2.BUSINESS_KEY_,
t2.PROC_INST_ID_,
t2.PROC_DEF_ID_,
t2.NAME_ AS proc_inst_name,
t2.START_TIME_,
t2.END_TIME_,
t3.current_user_code AS start_person_code,
t3.process_status AS process_status,
t7.realname AS start_person,
t9.business_url,
t3.model_key,
t3.case_name,
t3.depart_name as startDepartName
FROM act_hi_taskinst t1
INNER JOIN act_hi_procinst t2
ON t1.PROC_INST_ID_ = t2.PROC_INST_ID_
INNER JOIN act_re_procdef t8 ON t2.PROC_DEF_ID_=t8.ID_
INNER JOIN tbl_flow_extend_hisprocinst t3
ON t1.PROC_INST_ID_ = t3.process_instance_id
LEFT JOIN act_hi_identitylink t6
ON t6.TASK_ID_ = t1.ID_
INNER JOIN sys_user t7
ON t7.username = t3.current_user_code
LEFT JOIN tbl_flow_model_info t9 ON t8.KEY_=t9.model_key
<where>
<if test="params.userCode!=null and params.userCode!=''">
AND (
t1.ASSIGNEE_ = #{params.userCode,jdbcType=VARCHAR}
OR (
(
t1.ASSIGNEE_ IS NULL
OR t1.ASSIGNEE_ = ''
)
AND (
t6.USER_ID_ = #{params.userCode,jdbcType=VARCHAR}
OR t6.GROUP_ID_ IN (
SELECT
g.group_id_
FROM
act_id_membership g
WHERE
g.user_id_ = #{params.userCode,jdbcType=VARCHAR})
)
)
)
</if>
<if test="params.keyword!=null and params.keyword!=''">
AND t2.NAME_ LIKE CONCAT('%',#{params.keyword},'%')
</if>
<if test="params.startTime!=null">
AND t2.START_TIME_ >= #{params.startTime}
</if>
<if test="params.endTime!=null">
AND #{params.endTime} >= t2.START_TIME_
</if>
<if test="params.businessKey!=null and params.businessKey!=''">
AND t2.BUSINESS_KEY_ LIKE CONCAT('%',#{params.businessKey},'%')
</if>
<!-- <if test="params.flowStatus = 2">-->
<!-- AND (t3.process_status = 'BJ' or t3.process_status = 'BH')-->
<!-- </if>-->
</where>
group by t2.PROC_INST_ID_
order by t1.START_TIME_ DESC
分页的sql执行效率
2.2 MybatisPlus包装后总数sql执行效率
// MybatisPlus查询分页组件总数包装sql
SELECT
COUNT(*)
FROM
(
SELECT DISTINCT
t1.ID_,
t1.NAME_,
t1.START_TIME_ AS CREATE_TIME_,
t1.ASSIGNEE_,
t1.DESCRIPTION_,
t1.TASK_DEF_KEY_,
t1.DURATION_,
t2.BUSINESS_KEY_,
t2.PROC_INST_ID_,
t2.PROC_DEF_ID_,
t2.NAME_ AS proc_inst_name,
t2.START_TIME_,
t2.END_TIME_,
t3.current_user_code AS start_person_code,
t3.process_status AS process_status,
t7.realname AS start_person,
t9.business_url,
t3.model_key,
t3.case_name,
t3.depart_name AS startDepartName
FROM
act_hi_taskinst t1
INNER JOIN act_hi_procinst t2 ON t1.PROC_INST_ID_ = t2.PROC_INST_ID_
INNER JOIN act_re_procdef t8 ON t2.PROC_DEF_ID_ = t8.ID_
INNER JOIN tbl_flow_extend_hisprocinst t3 ON t1.PROC_INST_ID_ = t3.process_instance_id
LEFT JOIN act_hi_identitylink t6 ON t6.TASK_ID_ = t1.ID_
INNER JOIN sys_user t7 ON t7.username = t3.current_user_code
LEFT JOIN tbl_flow_model_info t9 ON t8.KEY_ = t9.model_key
WHERE
(
t1.ASSIGNEE_ = 'admin'
OR ((
t1.ASSIGNEE_ IS NULL
OR t1.ASSIGNEE_ = 'admin'
)
AND (
t6.USER_ID_ = 'admin'
OR t6.GROUP_ID_ IN ( SELECT g.group_id_ FROM act_id_membership g WHERE g.user_id_ = 'admin' ))))
GROUP BY
t2.PROC_INST_ID_
ORDER BY
t1.START_TIME_ DESC
) TOTAL
包装后sql执行效率
总结
由于我的sql中包含分组查询，经过MybatisPlus的分页组件包装后，子查询扫描行数过大，故将该sql去除IPage参数，更改sql传入limit分页参数后,单独查询总数解决。
List<TaskVo> getApplyedTasksPagerModel( @Param("params") TaskQueryParamsVo params);
#sql增加
limit #{params.page},#{params.size}
#方法
@Override
public PagerModel<TaskVo> getApplyedTasksPagerModel(TaskQueryParamsVo paramsVo, Query query) {
// log.info("参数:"+ JSON.toJSONString(paramsVo));
// log.info("参数:"+ JSON.toJSONString(query));
//mybatis plus的分页查询查询数量时进行全表扫描，故修改原始分页
paramsVo.setPage((query.getPageNum()-1)*query.getPageSize());
paramsVo.setSize(query.getPageSize());
List<TaskVo> taskVoList = flowableTaskMapper.getApplyedTasksPagerModel(paramsVo);
List<Integer> count = flowableTaskMapper.getApplyedTasksPagerModelCount(paramsVo);
this.setTaskVoData(taskVoList);
return new PagerModel<TaskVo>(count.size(), taskVoList);
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
