电脑基础 · 2023年3月15日

Oracle调优日记

Oracle调优日记

  • 前言
  • 前置知识
    • 联表查询
      • Inner Join
      • left join
      • right join
  • 环境
    • 背景描述
    • 问题展示
      • 最初代码
    • 问题解决过程
      • 优化一
      • 优化二
      • 优化三
      • 接口测试
      • 检查数据库连接
  • 总结

前言

很难想想会在600条数据和4万条数据的两张表联表查询的的情况下,查询花了10多s。这里记录一下排查过程吧,最后的错误原因也抽象的。

前置知识

联表查询

Inner Join

显示两个表的交集,如果不在两张表中就不进行显示

left join

以表1为驱动表,表2为被驱动表的数据,查出来的结果显示左边的所有数据,然后右边显示的是和左边有交集部分的数据。

right join

以表2为驱动表,表1为被驱动表的数据,查出来的结果显示右边的所有数据,然后左边显示的是和右边有交集部分的数据。

环境

JDK8、Maven3.X、SSM项目、Oracle

背景描述

每两个小时拉取最新的结果数据,在这种情况下,生成一个统计报表。其中当前4万多条的被驱动表称为A,600+数据的驱动表称为B。

问题展示

最初代码

SELECT
t1.CAR_IMG,
t1.VEH_LIC_PLT_NO,
t1.ALARM_TM,
t1.WARN_TP,
t2.DIST_NM,
t2.ENT_NM,
t1.ENT_MGMT_MBR,
t1.CON_PHONE,
t2.IND_NM
FROM
T_VEHICLE_POLLUT_QA_INFO t1,
T_VEHICLE_POLLUT_ENT_BASE_INFO t2
WHERE
t2.ENT_CODE = t2.ENT_CODE
AND t1.ALARM_TM BETWEEN TO_DATE ('2023-03-02 11:55:15', 'yyyy-mm-dd hh24:mi:ss')
AND TO_DATE ('2023-03-07 11:55:15', 'yyyy-mm-dd hh24:mi:ss');

Oracle调优日记
没错跑了20S都没跑出来,直接依托答辩。

问题解决过程

优化一

left join 把数据量小的表作为驱动表,数据量大的作为被驱动表,同时提前将表按条件过滤,代码如下:

    <select id="selectByMap" resultMap="AlarmInfoMap">
        select t1.CAR_IMG,
        t1.VEH_LIC_PLT_NO,
        t1.ALARM_TM,
        t1.WARN_TP,
        t1.DIST_NM,
        t1.ENT_NM,
        t1.ENT_MGMT_MBR,
        t1.CON_PHONE,
        t2.IND_NM
        from
        (select ENT_CODE,IND_NM from T_VEHICLE_POLLUT_ENT_BASE_INFO
        <where>
            <if test="distCode != null and distCode != 0">
                and DIST_CODE = #{distCode,jdbcType=VARCHAR}
            </if>
            <if test="indTp != null and indTp != 0">
                and IND_NM = #{indTp,jdbcType=VARCHAR}
            </if>
        </where>
        )t2
        left join
        (
        select ENT_CODE,DIST_NM,ENT_NM,CAR_IMG,VEH_LIC_PLT_NO, ALARM_TM,WARN_TP,ENT_MGMT_MBR,CON_PHONE
        from T_VEHICLE_POLLUT_QA_INFO
        <where>
            <if test="distCode != null and distCode != 0">
                and DIST_CODE = #{distCode,jdbcType=VARCHAR}
            </if>
            <if test="startTime != null and endTime != null">
                and ALARM_TM between to_date(#{startTime,jdbcType=VARCHAR}, 'yyyy-mm-dd hh24:mi:ss') and
                to_date(#{endTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss')
            </if>
        </where>
        ) t1
         on t1.ENT_CODE = t2.ENT_CODE
    </select>

优化二

对所有的查询字段设置索引,同时对联表的外键也建立索引。

  • 被驱动表索引
    Oracle调优日记
  • 驱动表索引
    Oracle调优日记

优化三

既然查数据库很慢,那么就Juc 进行异步IO查数据库,当然这里异步查最后的响应时间也是取决于最耗费时间的任务。

CompletableFuture<List<GuardVo>> guardFuture = CompletableFuture.supplyAsync(() -> guardDao.selectByMap(params), ThreadPoolUtil.getExecutorService());
CompletableFuture<List<AlarmInfoVo>> alarmFuture = CompletableFuture.supplyAsync(() -> alarmInfoDao.selectByMap(params), ThreadPoolUtil.getExecutorService());

接口测试

自信满满,接口一测试,直接蒙蔽,这是人能看的?不过幸好从直接死机到现在能出来数据了,不过10S真的很难让人接受了
Oracle调优日记
莫非是程序问题?我查看IDEA对SQL的打印,竟然耗费了10s多,所以后端的代码应该没有问题。

我直接将解析的SQL复制到Navicat中一测竟然只有0.2ms,数据量也不大,这到底怎么回事?
Oracle调优日记

检查数据库连接

Oracle调优日记

检查了数据库的连接,一看用的是VPN连接的远程的数据库……调回本地数据库,再次测试。
Oracle调优日记

总结

刚接手很多任务的时候,我知道你很急,但是你先别急。