关于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);
    }