解决项目中报表统计SQL执行缓慢的方案-数据预处理

使用场景：

由于表数据量巨大，导致一些统计相关的sql执行非常慢，使用户有非常不好的体验，并且sql和数据库已经没有优化空间了。(并且该统计信息数据实时性要求不高的前提下)

解决方案：

整体思路：创建预处理表——通过定时任务将数据插入到结果表——统计信息时直接通过结果表进行查询——大大提高响应速度

注：1.结果表中需要包含查询条件里的所有字段

        2.定时任务可以根据实际需要设定频率

        3.最好创建一个与结果表表结构一样的临时表用于数据暂存,防止在插入数据这个时间段上导致结果表数据空白。（直接将临时表数据插入到结果表速度很快这段时间可以忽略）

方案示例：

第一步：创建结果表

drop table if exists `user_study_statistics`;
CREATE TABLE `user_study_statistics` (
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `nick_name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `dept_id` bigint(20) DEFAULT NULL COMMENT '部门ID',
  `dept_name` varchar(30) DEFAULT '' COMMENT '部门名称',
  `dept_id_old` bigint(20) DEFAULT NULL COMMENT '历史部门ID',
  `plan_num` int(5) DEFAULT '0' COMMENT '任务总个数',
  `doing_num` int(5) DEFAULT '0' COMMENT '进行中个数',
  `finish_num` int(5) DEFAULT '0' COMMENT '已完成个数',
  `finish_rate` float DEFAULT NULL COMMENT '任务完成率',
  `overdue_num` int(5) DEFAULT '0' COMMENT '逾期个数',
  `overdue_rate` float DEFAULT NULL COMMENT '任务逾期率',
  `push_date` timestamp NULL DEFAULT NULL COMMENT '发布时间',
  `ancestors` varchar(500) DEFAULT '' COMMENT '祖级列表',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工学习统计数据';
drop table if exists `user_study_statistics_tmp`;
CREATE TABLE `user_study_statistics_tmp` (
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `nick_name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `dept_id` bigint(20) DEFAULT NULL COMMENT '部门ID',
  `dept_name` varchar(30) DEFAULT '' COMMENT '部门名称',
  `dept_id_old` bigint(20) DEFAULT NULL COMMENT '历史部门ID',
  `plan_num` int(5) DEFAULT '0' COMMENT '任务总个数',
  `doing_num` int(5) DEFAULT '0' COMMENT '进行中个数',
  `finish_num` int(5) DEFAULT '0' COMMENT '已完成个数',
  `finish_rate` float DEFAULT NULL COMMENT '任务完成率',
  `overdue_num` int(5) DEFAULT '0' COMMENT '逾期个数',
  `overdue_rate` float DEFAULT NULL COMMENT '任务逾期率',
  `push_date` timestamp NULL DEFAULT NULL COMMENT '发布时间',
  `ancestors` varchar(500) DEFAULT '' COMMENT '祖级列表',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工学习统计数据临时表';

第二步：创建预处理定时任务

/**
 * 预处理员工学习统计数据(每小时执行一次)
 */
@PostConstruct  //程序启动时就执行一次
@Scheduled(cron = "0 0 * * * ?") //定时频率每小时执行一次
@Override
public void preUserStudyStatistics() {
    //0.删除临时表里的数据
    mapper.deleteUserStudyStatisticsTmpData();
    //1.将数据插入临时表
    mapper.insertUserStudyStatisticsTmpData();
    //2.删除正式表里的数据
    mapper.deleteUserStudyStatisticsData();
    //3.将临时表里的数据插入正式表
    mapper.insertUserStudyStatisticsData();
    //3.删除临时表里的数据
    mapper.deleteUserStudyStatisticsTmpData();
}

对应的sql语句： 

<insert id="insertUserStudyStatisticsTmpData">
    insert into user_study_statistics_tmp
    select
        t.user_id,
        a.nick_name,
        a.dept_id,
        d.dept_name,
        b.dept_id as dept_id_old,
        COUNT(*) as plan_num,
        SUM(CASE WHEN b.status != 1 and b.total_progress &gt; 0 and c.end_time &gt;= now() then 1 ELSE 0 end) as doing_num,
        SUM(CASE WHEN b.status = 1 then 1 ELSE 0 end) as finish_num,
        ROUND(SUM(CASE WHEN b.status = 1 then 1 ELSE 0 end)/COUNT(*),2) as finish_rate,
        SUM(CASE WHEN b.status != 1 and c.end_time &lt; now() then 1 ELSE 0 end) as overdue_num,
        ROUND(SUM(CASE WHEN b.status != 1 and c.end_time &lt; now() then 1 ELSE 0 end)/COUNT(*),2) as overdue_rate,
        c.push_date,
        d.ancestors,
        NOW()
    FROM study_plan c
    INNER JOIN study_plan_user_rel t on t.plan_id = c.id
    INNER JOIN sys_user a on t.user_id = a.user_id
    INNER JOIN sys_dept d on a.dept_id = d.dept_id
    INNER JOIN study_progress b on t.user_id = b.user_id and t.plan_id = b.study_plan_id
    where a.del_flag = '0' and c.`type` = 0 and (c.status = 2 or c.status = 5)
    group by t.user_id,a.nick_name,a.dept_id
</insert>
<delete id="deleteUserStudyStatisticsData" parameterType="java.lang.Long">
    delete from user_study_statistics;
</delete>
<insert id="insertUserStudyStatisticsData">
    insert into user_study_statistics
    select * from user_study_statistics_tmp;
</insert>
<delete id="deleteUserStudyStatisticsTmpData" parameterType="java.lang.Long">
    delete from user_study_statistics_tmp;
</delete>

第三步： 修改之前的统计sql从多表关联到查询单表速度有了质的提升

<select id="getUserStudyStatistics" resultType="com.unicom.buintelligence.model.vo.UserStudyStatistics"
        parameterType="com.unicom.buintelligence.model.query.UserStudyStatisticsQuery">
    select
        t.*
    FROM user_study_statistics t
    where 1=1
    <if test="null != query.userId">
        and t.user_id = #{query.userId}
    </if>
    <if test="null != query.deptId">
        and (t.dept_id = #{query.deptId} or find_in_set(#{query.deptId}, t.ancestors))
    </if>
    <if test="null != query.nickName and '' != query.nickName">
        and t.nick_name like concat('%',#{query.nickName},'%')
    </if>
    <if test="null != query.startDate and '' != query.startDate">
        and DATE_FORMAT(t.push_date,'%Y-%m-%d') &gt;= #{query.startDate}
    </if>
    <if test="null != query.endDate and '' != query.endDate">
        and DATE_FORMAT(t.push_date,'%Y-%m-%d') &lt;= #{query.endDate}
    </if>
    <if test="null != query.params">
        ${query.params.dataScope}
    </if>
    <if test="null != query.sort and '' != query.sort">
        order by CONVERT(${query.sort} USING gbk)
        <if test='"desc" == query.sortType'>
            desc
        </if>
    </if>
</select>