ORACLE 千万条的数据优化

最新推荐文章于 2025-06-20 14:39:29 发布

原创最新推荐文章于 2025-06-20 14:39:29 发布 · 3.1k 阅读

1 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

26 篇文章

订阅专栏

面对一张包含千万条数据的账单明细表，通过删除无关字段、创建定时任务和统计表，实现了按月、年快速查询统计。首先通过去除查询结果中的无关字段显著提高了查询速度。接着，创建月、年统计表，并在低访问时段利用定时任务对明细表数据进行汇总，减少了实时查询的压力。通过这种方式，大幅提升了统计查询的效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

概述

场景是有一张千万条数据的表，数据类型是yyyy-MM-dd HH:mm:ss，场景是需要对账单明细表里的数据进行按月统计或者按年统计，但是发现数据查询过程太慢了，对此做一下优化的探索

先说一下，数据是如何来的呐？造出来的，没错，hhh，用存储过程模拟造的，而场景是需要对账单明细表里的数据进行统计，具体的话可以看看下面这两篇文章，数据优化是在此基础上进行改造的
https://blog.youkuaiyun.com/weixin_40598838/article/details/111144781
https://blog.youkuaiyun.com/weixin_40598838/article/details/111030581
https://blog.youkuaiyun.com/weixin_40598838/article/details/111045135

前置

明细表
在这里插入图片描述

无关字段删除

第一个思路的话仍然是从明细表中查询数据，但是前端返回了很多无关的字段，那么是否可以把无关字段去掉一下？

开始的返回数据是这样的，但是它的时间达到了3秒，有时候到8秒，这也太慢了
在这里插入图片描述

尝试一下

在这里插入图片描述

可以，时间为1秒，没有问题，期间有尝试过用在时间字段上加索引，但是时间达到了30多秒，太恐怖了，我想可能是索引在遍历B树所用的时间比查全表的时间长

定时任务统计

方法一SQL:

SELECT
SUM(FUND_AMOUNT)  FUND_AMOUNT,
MAX(TO_CHAR(ACCT, 'yyyy-MM' )) ACCT,
MAX(FUND_TYPE_NAME) FUND_TYPE_NAME
FROM FUND_DETAIL
WHERE FUND_TYPE_CODE = #{fundTypeCode}
AND USER_ID=#{userId}
AND TO_CHAR(ACCT,'yyyy-MM') BETWEEN #{startTime} AND #{endTime}
GROUP BY SUBSTR( TO_CHAR(ACCT,'yyyy-MM'), 0, LENGTH( ACCT ) - 2 )

简单的用ORACLE的执行计划查看了一下，大致时间耗费是对时间的截取和分组上，而用户每天都会产生很多账单数据，写入到明细表中，这样从明细表中查询数据就会很慢，业务功能是按月、年进行数据查询，那么是否建立两张统计表，将明细表中的数据，在某个低访问量的时候段，做定时任务，将数据据保存月统计数据表和年统计数据表，在查询的时候，分别从月统计和年统计数据表中查询不就可以了嘛？

ok，

基于这样的想法，就尝试做吧:

建表，月统计数据表
在这里插入图片描述

定时任务统计

@Scheduled(cron = "0 10 11 * * ?")
    public void monthStastic(){

        LocalDateTime startTime = LocalDateTime.now();
        String acct = startTime.toLocalDate().format(DateTimeFormatter.ofPattern("yyyy-MM"));
        QueryWrapper<FundMonthDetail> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(FundMonthDetail::getAcct,acct);
        //每天凌晨对当月数据汇总一遍
        iFundMonthDetailService.remove(queryWrapper);
        System.out.println("acct:"+acct);
        FundMonthStasticBackPram fundMonthStasticBackPram = new FundMonthStasticBackPram(acct);
        List<FundMonthDetail> monthDetails = iFundDetailService.findDataMonthGroupBy(fundMonthStasticBackPram);
        System.out.println("执行查询");
        LocalDateTime endTime = LocalDateTime.now();
        System.out.println("time:"+ Duration.between(startTime, endTime).getSeconds());
        iFundMonthDetailService.saveBatch(monthDetails);

    }

好一点的是，查出来的数据没有ID，但是用Mybatis plus执行的时候，会自动将ID写入到数据中

统计SQL:

		SELECT
        MAX(FUND_TYPE_CODE) AS FUND_TYPE,
        MAX(FUND_TYPE_NAME) AS FUND_TYPE_NAME,
        SUM(FUND_AMOUNT) AS FUND_AMOUNT,
        TO_CHAR(ACCT,'yyyy-MM') AS ACCT,
        MAX(USER_ID) AS USER_ID
        FROM FUND_DETAIL
        WHERE TO_CHAR(ACCT,'yyyy-MM')=#{acct}
        GROUP BY USER_ID,FUND_TYPE_CODE,TO_CHAR(ACCT,'yyyy-MM')
        ORDER BY USER_ID,ACCT,FUND_TYPE

统计的SQL是，将按用户ID，资金类型，和时间分为一组进行统计，然后返回数据，这样做的好处是，不用对每个用户的不同资金类型都做一次数据统计，一次完成，而如下的做法则是需要对每个用户的不同资金类型都做数据统计，然后才得数据，这样做有点繁琐，而且可能需要用到多个SQL

一般来说，做数据统计的时候，一般是先分组再统计，为此需要对不同用户的不同类型做数据统计的时候，可以考虑除了用WHERE筛选数据，也可以考虑按某种相同类型对数据进行分组，然后进行统计

SELECT
        MAX(FUND_TYPE_CODE) AS FUND_TYPE,
        MAX(FUND_TYPE_NAME) AS FUND_TYPE_NAME,
        SUM(FUND_AMOUNT) AS FUND_AMOUNT,
        TO_CHAR(ACCT,'yyyy-MM') AS ACCT,
        MAX(USER_ID) AS USER_ID
        FROM FUND_DETAIL
        WHERE
        USER_ID=#{userId} AND FUND_TYPE_CODE=#{fundTypeCode}
        AND TO_CHAR(ACCT,'yyyy-MM') = '2020-12'
        GROUP BY TO_CHAR(ACCT,'yyyy-MM')
        ORDER BY ACCT

做个测试:

@GetMapping("findData")
    public Result findData(){

        LocalDateTime startTime = LocalDateTime.now();

        QueryWrapper<FundMonthDetail> queryWrapper = new QueryWrapper<>();

        queryWrapper.lambda().eq(FundMonthDetail::getFundType,"1")
                             .eq(FundMonthDetail::getUserId,"1");

        List<FundMonthDetail> list = iFundMonthDetailService.list(queryWrapper);
        
        LocalDateTime endTime =LocalDateTime.now();
        
        System.out.println("time:"+Duration.between(startTime,endTime).getSeconds());

        return new Result(list);

    }

so 快，太Nice了，这样吧，hhh
在这里插入图片描述

做年数据统计也类似，如下:

		SELECT
        MAX(FUND_TYPE_CODE) AS FUND_TYPE,
        MAX(FUND_TYPE_NAME) AS FUND_TYPE_NAME,
        SUM(FUND_AMOUNT) AS FUND_AMOUNT,
        TO_CHAR(ACCT,'yyyy') AS ACCT,
        MAX(USER_ID) AS USER_ID
        FROM FUND_DETAIL
        WHERE TO_CHAR(ACCT,'yyyy')=#{acct}
        GROUP BY USER_ID,FUND_TYPE_CODE,TO_CHAR(ACCT,'yyyy')
        ORDER BY USER_ID,ACCT,FUND_TYPE

 @Scheduled(cron = "0 12 13 * * ?")
    public void yearStastic(){

        LocalDateTime startTime = LocalDateTime.now();
        String acct = startTime.toLocalDate().format(DateTimeFormatter.ofPattern("yyyy"));
        QueryWrapper<FundYearDetail> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(FundYearDetail::getAcct,acct);
        //每天凌晨对当年数据汇总一遍
        iFundYearDetailService.remove(queryWrapper);
        System.out.println("acct:"+acct);
        FundYearStasticBackPram fundMonthStasticBackPram = new FundYearStasticBackPram(acct);
        List<FundYearDetail> yearDetails = iFundDetailService.findDataYearGroupBy(fundMonthStasticBackPram);
        System.out.println("执行查询");
        LocalDateTime endTime = LocalDateTime.now();
        System.out.println("time:"+ Duration.between(startTime, endTime).getSeconds());
        iFundYearDetailService.saveBatch(yearDetails);

    }