一次生产慢查询优化实例

技术

背景：项目上线后，发现某查询接口比较慢，定位到是SQL查询问题，查询时间大约在3～5s。数据库是Oracle。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--待优化SQL
select
  STRATEGY_ID,
  CIF_NO,
  BIZ_TYPE,
  T_TYPE,
  FINANCE,
  START_TIME,
  END_TIME,
  STRATEGY_NAME,
  DIRECTION,
  ...
from
  XXX_XXX_STRATEGY
where
  (
    CIF _NO = ?
    and T_TYPE = 'A'
    and DIRECTION = ?
  )
  or T_TYPE = 'B'
  and START_TIME <= ? and END_TIME >= ? order by START_TINE desc

XXX_XXX_STRATEGY表的索引情况：

​ 唯一索引：STRATEGY_ID

​ 普通索引：CIF_NO,START_TIME,DIRECTION,END_TIME,T_TYPE

定位原因：

OR前半段查询条件相对比较精确，查询结果是比较少的。

OR后半段的 T_TYPE ='B' 在生产上查出了大量数据（万级），但是在后续业务处理时大部分被去重了（剩余数据为 百级）。

这里同事给出方案一：分表：将TYPE = 'B'的数据存放在一个新建的表中，但这样问题是不管放在哪总是查询出了大量数据，总是快不了的。由于数据量大肯定走了全表扫描，使用索引也是无效的。

所以要将优化重点放到SQL本身上。

首先肯定需要拆成两个SQL，首先将OR优化为 UNION ALL试试情况：查询时间还是跟之前一样。

这时，我们看到这里的查询结果其实都被之后的去重逻辑给去掉了，我们能不能在查询的时候就给他去重呢？

答案是肯定的。

1
2
3
4
5
6
7
8
9
10
11
12
13
--拆分出来的SQL
SELECT
  STRATEGY_ID,
  T_TYPE,
  FINANCE,
  START_TIME,
  END_TIME,
  ...
FROM
  XXX_XXX_STRATEGY
WHERE  
  T_TYPE = 'B'
  AND START_TIME <= ? AND END_TIME >= ? ORDER BY START_TINE DESC

根据业务逻辑，类型为'B'的数据主要使用到的字段是FINANCE，生产中推送入库时推送了大量的该字段的重复数据进来。这一点在UAT环境中是没有预料到的。我们立马就想到了用分组来过滤，但是需要将FINANCE字段相同的数据取最新的一条，这样分组同时还需要排序。先看最后优化的SQL。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
  *
FROM
  (
    SELECT
      STRATEGY_ID,
      T_TYPE,
      FINANCE,
      START_TIME,
      END_TIME,
      ...,
      row_number() over(partition by e.FINANCE order by e.START_TIME desc) rankNo
    from
      XXX_XXX_STRATEGY e
    where
      e.T_TYPE = 'B' AND START_TIME <= ? and END_TIME >= ? 
  ) r
  where r.rankNo = 1;

我们主要使用了over()函数:它给 partition by的分组结果给提供了一个封闭的区间，而row_number()函数：会在over()的每个区间范围内重新分配一个递增的行号。这里我们取行号为1的数据即是取按时间排序第一位的数据。

SQL去重后在十万级的数据量下查询速度约为0.4s，虽然还是不算快，但是已经算解决了问题啦。