首页 > 数据库 >项目中统计SQL执行缓慢的方案-数据预处理

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

时间：2023-01-09 15:37:21浏览次数：81  
标签：count COMMENT DEFAULT 30 int SQL 缓慢 NULL 预处理

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

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

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

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

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

方案示例：
第一步：创建结果表

drop table if exists `user_study_statistics`;
CREATE TABLE `project_statistics` (
  `project_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '项目id',
  `project_unit_id` int(11) NOT NULL COMMENT '单位工程id',
  `unit_name` varchar(255) DEFAULT NULL COMMENT '单位工程名称',
  `project_name` varchar(255) DEFAULT NULL COMMENT '项目名称',
  `file_count` int(8) NOT NULL DEFAULT '0' COMMENT '文件总数',
  `file_count_30` int(8) NOT NULL DEFAULT '0' COMMENT '文件总数（30天内）',
  `file_count_90` int(8) NOT NULL DEFAULT '0' COMMENT '文件总数（90天内）',
  `file_count_180` int(8) NOT NULL DEFAULT '0' COMMENT '文件总数（180天内）',
  `check_count` int(8) DEFAULT NULL COMMENT '检查次数',
  `check_count_30` int(8) DEFAULT NULL COMMENT '检查次数（30天内）',
  `check_count_90` int(8) DEFAULT NULL COMMENT '检查次数（90天内）',
  `check_count_180` int(8) DEFAULT NULL COMMENT '检查次数（180天内）',
  `check_num` int(8) NOT NULL DEFAULT '0' COMMENT '检查文件总数',
  `check_num_30` int(8) NOT NULL DEFAULT '0' COMMENT '检查文件总数（30天内）',
  `check_num_90` int(8) NOT NULL DEFAULT '0' COMMENT '检查文件总数（90天内）',
  `check_num_180` int(8) NOT NULL DEFAULT '0' COMMENT '检查文件总数（180天内）',
  `check_scale` varchar(26) DEFAULT NULL COMMENT '检查文件占比',
  `check_scale_30` varchar(26) DEFAULT NULL COMMENT '检查文件占比（30天内）',
  `check_scale_90` varchar(26) DEFAULT NULL COMMENT '检查文件占比（90天内）',
  `check_scale_180` varchar(26) DEFAULT NULL COMMENT '检查文件占比（180天内）',
  `pass_scale` varchar(26) DEFAULT NULL COMMENT '通过率',
  `pass_scale_30` varchar(26) DEFAULT NULL COMMENT '通过率（30天内）',
  `pass_scale_90` varchar(26) DEFAULT NULL COMMENT '通过率（90天内）',
  `pass_scale_180` varchar(26) DEFAULT NULL COMMENT '通过率（180天内）',
  `correct_count` int(8) DEFAULT NULL COMMENT '整改条数',
  `correct_count_30` int(8) DEFAULT NULL COMMENT '整改条数（30天内）',
  `correct_count_90` int(8) DEFAULT NULL COMMENT '整改条数（90天内）',
  `correct_count_180` int(8) DEFAULT NULL COMMENT '整改条数（180天内）',
  `out_time_count` int(8) DEFAULT NULL COMMENT '逾期未完成整改条数',
  `out_time_count_30` int(8) DEFAULT NULL COMMENT '逾期未完成整改条数（30天内）',
  `out_time_count_90` int(8) DEFAULT NULL COMMENT '逾期未完成整改条数（90天内）',
  `out_time_count_180` int(8) DEFAULT NULL COMMENT '逾期未完成整改条数（180天内）'
) ENGINE=InnoDB DEFAULT CHARSCREATE TABLE `project_statistics_tmp` (
  `project_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '项目id',
  `project_unit_id` int(11) NOT NULL COMMENT '单位工程id',
  `unit_name` varchar(255) DEFAULT NULL COMMENT '单位工程名称',
  `project_name` varchar(255) DEFAULT NULL COMMENT '项目名称',
  `file_count` int(8) NOT NULL DEFAULT '0' COMMENT '文件总数',
  `file_count_30` int(8) NOT NULL DEFAULT '0' COMMENT '文件总数（30天内）',
  `file_count_90` int(8) NOT NULL DEFAULT '0' COMMENT '文件总数（90天内）',
  `file_count_180` int(8) NOT NULL DEFAULT '0' COMMENT '文件总数（180天内）',
  `check_count` int(8) DEFAULT NULL COMMENT '检查次数',
  `check_count_30` int(8) DEFAULT NULL COMMENT '检查次数（30天内）',
  `check_count_90` int(8) DEFAULT NULL COMMENT '检查次数（90天内）',
  `check_count_180` int(8) DEFAULT NULL COMMENT '检查次数（180天内）',
  `check_num` int(8) NOT NULL DEFAULT '0' COMMENT '检查文件总数',
  `check_num_30` int(8) NOT NULL DEFAULT '0' COMMENT '检查文件总数（30天内）',
  `check_num_90` int(8) NOT NULL DEFAULT '0' COMMENT '检查文件总数（90天内）',
  `check_num_180` int(8) NOT NULL DEFAULT '0' COMMENT '检查文件总数（180天内）',
  `check_scale` varchar(26) DEFAULT NULL COMMENT '检查文件占比',
  `check_scale_30` varchar(26) DEFAULT NULL COMMENT '检查文件占比（30天内）',
  `check_scale_90` varchar(26) DEFAULT NULL COMMENT '检查文件占比（90天内）',
  `check_scale_180` varchar(26) DEFAULT NULL COMMENT '检查文件占比（180天内）',
  `pass_scale` varchar(26) DEFAULT NULL COMMENT '通过率',
  `pass_scale_30` varchar(26) DEFAULT NULL COMMENT '通过率（30天内）',
  `pass_scale_90` varchar(26) DEFAULT NULL COMMENT '通过率（90天内）',
  `pass_scale_180` varchar(26) DEFAULT NULL COMMENT '通过率（180天内）',
  `correct_count` int(8) DEFAULT NULL COMMENT '整改条数',
  `correct_count_30` int(8) DEFAULT NULL COMMENT '整改条数（30天内）',
  `correct_count_90` int(8) DEFAULT NULL COMMENT '整改条数（90天内）',
  `correct_count_180` int(8) DEFAULT NULL COMMENT '整改条数（180天内）',
  `out_time_count` int(8) DEFAULT NULL COMMENT '逾期未完成整改条数',
  `out_time_count_30` int(8) DEFAULT NULL COMMENT '逾期未完成整改条数（30天内）',
  `out_time_count_90` int(8) DEFAULT NULL COMMENT '逾期未完成整改条数（90天内）',
  `out_time_count_180` int(8) DEFAULT NULL COMMENT '逾期未完成整改条数（180天内）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='项目检查状态统计表';

ET=utf8 COMMENT='项目检查状态统计表_临时表';

创建视图：

CREATE VIEW `v_project_statistics_info` AS
SELECT project_id,project_unit_id,unit_name,project_name,
COUNT(1) file_count,-- 文件总数
COUNT((datediff(date(now()),date(gmt_create)) <=30 ) OR null) file_count_30,
COUNT((datediff(date(now()),date(gmt_create)) <=90 ) OR null) file_count_90,
COUNT((datediff(date(now()),date(gmt_create)) <=180 ) OR null) file_count_180,
SUM(check_count) as check_count,-- 检查次数
SUM(CASE WHEN (datediff(date(now()),date(modify_time)) <=30 ) THEN check_count ELSE 0 END) AS check_count_30,
SUM(CASE WHEN (datediff(date(now()),date(modify_time)) <=90 ) THEN check_count ELSE 0 END) AS check_count_90,
SUM(CASE WHEN (datediff(date(now()),date(modify_time)) <=180 ) THEN check_count ELSE 0 END) AS check_count_180,
COUNT(check_count>0 OR null) as check_num,-- 检查文件总数
COUNT((datediff(date(now()),date(modify_time)) <=30 ) and check_count>0 OR null) as check_num_30,
COUNT((datediff(date(now()),date(modify_time)) <=90 ) and check_count>0 OR null) as check_num_90,
COUNT((datediff(date(now()),date(modify_time)) <=180 ) and check_count>0 OR null) as check_num_180, 
CONCAT(ROUND(COUNT(check_count>0 OR null)/COUNT(1)*100,0),'%') as check_tage, -- 检查文件占比
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time)) <=30 ) and check_count>0 OR null)/COUNT((datediff(date(now()),date(gmt_create)) <=30 ) OR null)*100,0),'%') as check_scale_30,
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time)) <=90 ) and check_count>0 OR null)/COUNT((datediff(date(now()),date(gmt_create)) <=90 ) OR null)*100,0),'%') as check_scale_90,
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time)) <=180 ) and check_count>0 OR null)/COUNT((datediff(date(now()),date(gmt_create)) <=180 ) OR null)*100,0),'%') as check_scale_180,
CONCAT(ROUND(COUNT(check_status=2 OR null)/COUNT(check_count>0 OR null)*100,0),'%') as pass_tage, -- 通过率
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time)) <=30 ) and check_status=2 OR null)/COUNT((datediff(date(now()),date(modify_time)) <=30 ) and check_count>0 OR null)*100,0),'%') as pass_scale_30,
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time)) <=90 ) and check_status=2 OR null)/COUNT((datediff(date(now()),date(modify_time)) <=90 ) and check_count>0 OR null)*100,0),'%') as pass_scale_90,
CONCAT(ROUND(COUNT((datediff(date(now()),date(modify_time)) <=180 ) and check_status=2 OR null)/COUNT((datediff(date(now()),date(modify_time)) <=180 ) and check_count>0 OR null)*100,0),'%') as pass_scale_180,
SUM(correct_count) as correct_count,-- 整改条数
SUM(correct_count_30) as correct_count_30,
SUM(correct_count_90) as correct_count_90,
SUM(correct_count_180) as correct_count_180,
SUM(out_time_count) as out_time_count, -- 逾期未完成整改条数
SUM(out_time_count_30) as out_time_count_30,
SUM(out_time_count_90) as out_time_count_90,
SUM(out_time_count_180) as out_time_count_180
from (

SELECT SUBSTRING_INDEX(d.bus_id,'_',1) as project_id,d.project_unit_id,d.node_id,ifnull(d.check_count,0) as check_count,d.check_status,ifnull(a.correct_count,0) as correct_count,ifnull(a.correct_count_30,0) as correct_count_30,ifnull(a.correct_count_90,0) as correct_count_90,ifnull(a.correct_count_180,0) as correct_count_180,ifnull(a.out_time_count,0) as out_time_count,ifnull(a.out_time_count_30,0) as out_time_count_30,ifnull(a.out_time_count_90,0) as out_time_count_90,ifnull(a.out_time_count_180,0) as out_time_count_180,u.unit_name,p.project_name,d.modify_time,d.gmt_create from v_doc_info d
INNER JOIN project_detail p on p.local_project_id=d.project_id
LEFT JOIN project_unit u on u.id=d.project_unit_id
left join( 
SELECT local_project_id,IFNULL(correct_soure_id,0)as node_id,COUNT(1) as correct_count
,COUNT(datediff(date(now()),date(create_time)) <=30) as correct_count_30
,COUNT(datediff(date(now()),date(create_time)) <=90) as correct_count_90
,COUNT(datediff(date(now()),date(create_time)) <=180) as correct_count_180
,count((have_deleted=0 and correct_end_time<NOW()) or null) as out_time_count
,count((have_deleted=0 and correct_end_time<NOW() and (datediff(date(now()),date(create_time)) <=30)) or null) as out_time_count_30
,count((have_deleted=0 and correct_end_time<NOW() and (datediff(date(now()),date(create_time)) <=90)) or null) as out_time_count_90
,count((have_deleted=0 and correct_end_time<NOW() and (datediff(date(now()),date(create_time)) <=180)) or null) as out_time_count_180  from doc_correct GROUP BY local_project_id,correct_soure_id
) a on a.node_id=d.node_id
UNION ALL
SELECT d.project_id,d.remote_project_id as project_unit_id,d.id as node_id,ifnull(d.check_count,0) as check_count,d.check_status,ifnull(a.correct_count,0) as correct_count,ifnull(a.correct_count_30,0) as correct_count_30,ifnull(a.correct_count_90,0) as correct_count_90,ifnull(a.correct_count_180,0) as correct_count_180,ifnull(a.out_time_count,0) as out_time_count,ifnull(a.out_time_count_30,0) as out_time_count_30,ifnull(a.out_time_count_90,0) as out_time_count_90,ifnull(a.out_time_count_180,0) as out_time_count_180,u.unit_name,p.project_name,d.modify_time,d.gmt_create from v_datumdata_info d
INNER JOIN project_detail p on p.local_project_id=d.project_id
LEFT JOIN project_unit u on u.id=d.remote_project_id
left join( 
SELECT local_project_id,IFNULL(correct_soure_id,0)as node_id,COUNT(1) as correct_count
,COUNT(datediff(date(now()),date(create_time)) <=30) as correct_count_30
,COUNT(datediff(date(now()),date(create_time)) <=90) as correct_count_90
,COUNT(datediff(date(now()),date(create_time)) <=180) as correct_count_180
,count((have_deleted=0 and correct_end_time<NOW()) or null) as out_time_count
,count((have_deleted=0 and correct_end_time<NOW() and (datediff(date(now()),date(create_time)) <=30)) or null) as out_time_count_30
,count((have_deleted=0 and correct_end_time<NOW() and (datediff(date(now()),date(create_time)) <=90)) or null) as out_time_count_90
,count((have_deleted=0 and correct_end_time<NOW() and (datediff(date(now()),date(create_time)) <=180)) or null) as out_time_count_180  from doc_correct GROUP BY local_project_id,correct_soure_id
) a on a.node_id=d.id
)t GROUP BY project_id,project_unit_id

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

    /**
     * 资料检查统计预加载定时任务
     */
    @PostConstruct //程序启动时就执行一次
    @Scheduled(cron = "${schedul.statistics.checkStatistics:0 0 * * * ?}") //默认定时频率每小时执行一次
    public void preProjectStatistics(){
        //0.删除临时表里的数据
        statisticsDao.deleteStatisticsTmpData();
        //1.将数据插入临时表
        statisticsDao.insertStatisticsTmpData();
        //2.删除正式表里的数据
        statisticsDao.deleteStatisticsData();
        //3.将临时表里的数据插入正式表
        statisticsDao.insertStatisticsData();
        //3.删除临时表里的数据
        statisticsDao.deleteStatisticsTmpData();
    }

对应的sql语句：

<insert id="insertStatisticsTmpData">
        insert into project_statistics_tmp SELECT * from v_project_statistics_info
    </insert>
    <delete id="deleteStatisticsData" parameterType="java.lang.Long">
        delete from project_statistics;
    </delete>
    <insert id="insertStatisticsData">
        insert into project_statistics
    select * from project_statistics_tmp;
    </insert>
    <delete id="deleteStatisticsTmpData" parameterType="java.lang.Long">
        delete from project_statistics_tmp;
    </delete>

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

博客园排版可能有点乱，可转至我个人链接地址：https://raokun.top/raokun/archives/项目中统计sql执行缓慢的方案-数据预处理

标签：count,COMMENT,DEFAULT,30,int,SQL,缓慢,NULL,预处理
From： https://www.cnblogs.com/raok/p/17037184.html

相关文章

  • SQL211 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
    SQL211获取当前薪水第二多的员工的emp_no以及其对应的薪水salary题目描述有一个薪水表salaries请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，若有多个员工......
  • MySQL22 - SQL性能分析
    SQL性能分析SQL执行频率通过SHOW[SESSION|GLOBAL]STATUS命令可以提供服务器状态信息，通过以下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频......
  • MySQL21 - 索引
    索引什么是索引索引相当于一本书的目录，为了缩小扫描范围而存在、提高查询效率的一种数据结构一张表的一个字段可以添加索引，多个字段也可以联合起来添加索引在任何数据......
  • MySQL入门之查询操作
    单表查询SELECT[DISTINCT]*|<字段名1,字段名2,字段名3,...> FROM<表名> [WHERE<条件查询表达式1>] [GROUPBY<字段名>[HAVING<条件表达式2>]] [ORDERBY<字段......
  • mysql性能优化explain了解
    总结索引的设计原则：1.**最适合**索引的列是出现在**WHERE子句**和连接子句中的列。2.索引列的基数越大（取值多、重复值少），索引的效果就越好。3.使用**前缀索引**可以......
  • MySQL入门之表和数据的操作
    数据表的操作创建数据表时，要先use数据库名；来确定要操作的数据库。创建数据表CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名(字段名字段类型[字段属性]...)[表选项]......
  • MySQL 同步多张表到目标库
    vi/etc/my.cnf#目标库[mysql]host=172.16.1.10user=rootpassword=xxxxxxxx#源数据库[mysqldump]host=127.0.0.1user=rootpassword=xxxxxxxxssl-mode=DISABLED=====......
  • 使用SQL语句中的Group by分组并计算每组的数量
        ......
  • SQL join
    ####oraclejoin测试######SQLjoin用于把来自两个或多个表的行结合起来。##重要信息：例子来源于runoob，并且https://www.runoob.com/sql/sql-join.html图示解释这7......
  • 【学习笔记】动态SQL
    动态SQL1.概念动态SQL：动态SQL是MyBatis的强大特性之一。如果你使用过JDBC或其它类似的框架，你应该能理解根据不同条件拼接SQL语句有多痛苦，例如拼接时要确保不能......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99