如何优化sql，SQL优化：紧急情况下提高SQL性能竟是这样实现的！（文中有惊喜）

2023-09-22 阅读 17 评论 0

摘要：关注我们获得更多精彩作者 | 黄堋，多年一线 Oracle DBA 经验，长期服务电信、电网、医院、政府等行业客户。擅长数据库优化、数据库迁移升级、数据库故障处理。在某运营商的优化经历中曾经遇到了一条比较有意思的 SQL，具体如下：1该最开始的 sql

关注我们获得更多精彩

作者 | 黄堋，多年一线 Oracle DBA 经验，长期服务电信、电网、医院、政府等行业客户。擅长数据库优化、数据库迁移升级、数据库故障处理。

在某运营商的优化经历中曾经遇到了一条比较有意思的 SQL，具体如下：

1 该最开始的 sql 执行情况如下

SQL> SELECT
  2    NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId"
  3    FROM OFFER_SPEC_RELA T
  4    LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS
  5    ON T.RELA_GRP_ID     = SUBOS.OFFER_SPEC_GRP_ID
  6    AND subos.start_dt <= SYSDATE
  7    AND subos.end_dt    >= SYSDATE
  8    WHERE T.RELA_TYPE_CD = 2
  9    AND t.start_dt      <= SYSDATE
10    AND t.end_dt        >= SYSDATE
11    AND (T.OFFER_SPEC_ID = 109910000618
12    OR EXISTS
13      (SELECT A.OFFER_SPEC_GRP_ID
14      FROM OFFER_SPEC_GRP_RELA A
15      WHERE A.SUB_OFFER_SPEC_ID = 109910000618
16      AND T.OFFER_SPEC_GRP_ID   = A.OFFER_SPEC_GRP_ID
17      ))
18    AND rownum<500;
如何优化sql。
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1350156609

640?wx_fmt=png

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<500)
   2 - filter("T"."OFFER_SPEC_ID"=109910000618 OR EXISTS (SELECT 0 FROM
              "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND
              "A"."SUB_OFFER_SPEC_ID"=109910000618))
   3 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+))
   4 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND
              "T"."START_DT"<=SYSDATE@!)
   5 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!)
   6 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      12444 consistent gets
          0 physical reads
          0 redo size
        339 bytes sent via SQL*Net to client
        509 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

                  PLAN                     GET     DISK    WRITE              ROWS      ROWS USER_IO(MS) ELA(MS) CPU(MS) CLUSTER(MS)    PLSQL
END_TI I    HASH VALUE EXEC           PRE EXEC PRE EXEC PER EXEC ROW_P    PRE EXEC PRE FETCH    PER EXEC PRE EXEC PRE EXEC    PER EXEC PER EXEC

640?wx_fmt=png

Duang~

此处插播个小福利

本周四有一场你不得不看的讲座：由张甦老师开讲，带你走进不一样的 MySQL，为你讲解传统企业真正的痛点，帮你成为一名薪资在15k-20k的MySQL DBA工作者。

心动了没，扫描上方二维码把握薪资翻倍的机会，周四 20:00——21:00 不见不散！

2 第一次分析

此时应该有以下个地方值得注意

1) 该 sql 每天执行上千次，平均每次执行返回不到 10 行数据，但是平均逻辑读达到1.2W，可能存在性能问题。

2）ID 为 4,5 的执行计划路径中出现了两个全表扫描，看到这儿我们可以想到可能是没有合适的索引导致走了全表扫描从而执行效率低下。

3）ID 为 2 的执行计划路径出现了 FILTER，且 3，和 6 为其子路径，如果FILTER有两个及两个以上的子路径，那么他的执行原理将类似于嵌套循环，id 号最小的子路径如果返回行数较多，可能会导致多次执行id号更小的子路径，导致性能低下。一般存在 “OR EXISTS” 的时候会出现此情况，可以根据情况避免。

4）存在条件“ rownum<500 ”，但是从历史的执行情况来看，返回行数都远小于 500 行，此处我们先予以忽略。

3 第一次分析的处理

1）进过探查，发现存在两个表都有可用的索引，且两个表都只有几十 M 的大小。

提高性能问题？ 640?wx_fmt=png

640?wx_fmt=png

2）去掉“OR EXISTS”子句查看执行效率

此处可用看到，去掉 “OR EXISTS” 之后两个表走了合适的索引，并且执行效率极高。

SQL> SELECT
  2    NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId"
  3    FROM OFFER_SPEC_RELA T
  4    LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS
  5    ON T.RELA_GRP_ID     = SUBOS.OFFER_SPEC_GRP_ID
  6    AND subos.start_dt <= SYSDATE
  7    AND subos.end_dt    >= SYSDATE
  8    WHERE T.RELA_TYPE_CD = 2
  9    AND t.start_dt      <= SYSDATE
10    AND t.end_dt        >= SYSDATE
11    AND T.OFFER_SPEC_ID = 109910000618;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 510876366

640?wx_fmt=png

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@!
AND "T"."START_DT"<=SYSDATE@!)
   3 - access("T"."OFFER_SPEC_ID"=109910000618)
   4 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!)
   5 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+))

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
        339 bytes sent via SQL*Net to client
        510 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

3）去掉 “OR EXISTS” 中的子句查看执行效率。

此处可用看到 “ OR EXISTS ” 中的子句单独执行返回行数并不多，且效率依旧很快。

SQL> SELECT A.OFFER_SPEC_GRP_ID
  2    FROM OFFER_SPEC_GRP_RELA A
  3    WHERE A.SUB_OFFER_SPEC_ID = 109910000618;

OFFER_SPEC_GRP_ID
-----------------
        100000048
        109090086
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4223340843

640?wx_fmt=png

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."SUB_OFFER_SPEC_ID"=109910000618)

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
        597 bytes sent via SQL*Net to client
        521 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          2 rows processed

4）我们把该条 sql 语句分为 “OR EXISTS” 的子句和其他部分两块，到此我们可以看到，两块的执行效率都很高，但是合在一起就低了很多。在这种情况下，几乎可以确认，将该存在 “OR EXISTS” 的子句改写为 union 必将提升效率。

SQL> SELECT *
  2 FROM
  3    (SELECT NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId"
  4    FROM OFFER_SPEC_RELA T
  5    LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS
  6    ON T.RELA_GRP_ID     = SUBOS.OFFER_SPEC_GRP_ID
  7    AND subos.start_dt <= SYSDATE
  8    AND subos.end_dt    >= SYSDATE
  9    WHERE T.RELA_TYPE_CD = 2
10    AND t.start_dt      <= SYSDATE
11    AND t.end_dt        >= SYSDATE
12    AND T.OFFER_SPEC_ID = 109910000618
13    UNION
14    SELECT NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId"
15    FROM OFFER_SPEC_RELA T
16    LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS
17    ON T.RELA_GRP_ID     = SUBOS.OFFER_SPEC_GRP_ID
18    AND subos.start_dt <= SYSDATE
19    AND subos.end_dt    >= SYSDATE
20    WHERE T.RELA_TYPE_CD = 2
21    AND t.start_dt      <= SYSDATE
22    AND t.end_dt        >= SYSDATE
23    AND EXISTS
24      (SELECT A.OFFER_SPEC_GRP_ID
25      FROM OFFER_SPEC_GRP_RELA A
26      WHERE A.SUB_OFFER_SPEC_ID = 109910000618
27      AND T.OFFER_SPEC_GRP_ID   = A.OFFER_SPEC_GRP_ID
28      )
29    )
30 WHERE rownum<500;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3072450155

640?wx_fmt=png

Predicate Information (identified by operation id):

---------------------------------------------------
   1 - filter(ROWNUM<500)
   3 - filter(ROWNUM<500)
   6 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@!)
   7 - access("T"."OFFER_SPEC_ID"=109910000618)
   8 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!)
   9 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+))
  13 - access("A"."SUB_OFFER_SPEC_ID"=109910000618)
  14 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@!)
  15 - access("T"."OFFER_SPEC_GRP_ID"="A"."OFFER_SPEC_GRP_ID")
       filter("T"."OFFER_SPEC_GRP_ID" IS NOT NULL)
  16 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!)
  17 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+))
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         11 consistent gets
          0 physical reads
          0 redo size
        339 bytes sent via SQL*Net to client
        510 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
          0 rows processed

此处我们可以看到，改写之后逻辑读仅仅 11，较优化前提升了上千倍。到了此处，我们已经将 sql 优化到几乎最快的效率了。

4 第二次分析，确实改写能够提升效率，但是如果改写sql会涉及到修改代码，当前能否在不修改代码的情况下对其进行优化。

1）我们再来回顾一下最开始的执行计划路径。

提升sql执行效率。 640?wx_fmt=png

我们可以看到 “ OR EXISTS ” 中的子句是在 ID 为 6 的路径才开始执行的，这儿有一个知识点即为一个 sql 中的子句，一般情况下默认会将其放到最后执行。

2）ID 为 4 , 5 的执行计划路径中在有高效索引的情况下却出现了两个全表扫描，可以推断 CBO 可能没有正常评估执行的 cost。

3）“OR EXISTS” 中的子句执行效率很快，返回行数并不多，我们可以考虑提升 CBO 将其提前执行，看能否影响 CBO 选择出更高效的执行计划。

  SQL> SELECT NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId"
  2 FROM OFFER_SPEC_RELA T
  3 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS
  4 ON T.RELA_GRP_ID     = SUBOS.OFFER_SPEC_GRP_ID
  5 AND subos.start_dt <= SYSDATE
  6 AND subos.end_dt    >= SYSDATE
  7 WHERE T.RELA_TYPE_CD = 2
  8 AND t.start_dt      <= SYSDATE
  9 AND t.end_dt        >= SYSDATE
10 AND (T.OFFER_SPEC_ID = 109910000618
11 OR EXISTS
12    (SELECT /*+ push_subq */ A.OFFER_SPEC_GRP_ID
13    FROM OFFER_SPEC_GRP_RELA A
14    WHERE A.SUB_OFFER_SPEC_ID = 109910000618
15    AND T.OFFER_SPEC_GRP_ID   = A.OFFER_SPEC_GRP_ID
16    ))
17 AND rownum<500;
Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 849330408

640?wx_fmt=png

---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<500)
   3 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@! AND
              ("T"."OFFER_SPEC_ID"=109910000618 OR EXISTS (SELECT /*+ PUSH_SUBQ */ 0 FROM
              "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND
              "A"."SUB_OFFER_SPEC_ID"=109910000618)))
   4 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1)
   5 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!)
   6 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+))

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       2531 consistent gets
          0 physical reads
          0 redo size
        339 bytes sent via SQL*Net to client
        510 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processedSYS@crmadb1> SELECT NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId"
  2 FROM OFFER_SPEC_RELA T
  3 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS
  4 ON T.RELA_GRP_ID     = SUBOS.OFFER_SPEC_GRP_ID
  5 AND subos.start_dt <= SYSDATE
  6 AND subos.end_dt    >= SYSDATE
  7 WHERE T.RELA_TYPE_CD = 2
  8 AND t.start_dt      <= SYSDATE
  9 AND t.end_dt        >= SYSDATE
10 AND (T.OFFER_SPEC_ID = 109910000618
11 OR EXISTS
12    (SELECT /*+ push_subq */ A.OFFER_SPEC_GRP_ID
13    FROM OFFER_SPEC_GRP_RELA A
14    WHERE A.SUB_OFFER_SPEC_ID = 109910000618
15    AND T.OFFER_SPEC_GRP_ID   = A.OFFER_SPEC_GRP_ID
16    ))
17 AND rownum<500;
Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 849330408
---------------------------------------------------------------------------------------------------------------

640?wx_fmt=png

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<500)
   3 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@! AND
              ("T"."OFFER_SPEC_ID"=109910000618 OR EXISTS (SELECT /*+ PUSH_SUBQ */ 0 FROM
              "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND
              "A"."SUB_OFFER_SPEC_ID"=109910000618)))
   4 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1)
   5 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!)
   6 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+))

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       2531 consistent gets
          0 physical reads
          0 redo size
        339 bytes sent via SQL*Net to client
        510 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

此处我们在子句中加了一个 HINT /*+ push_subq */ ，该HINT的作用即使提醒 CBO 将子句提前执行。我们可以看到，执行效率较之前也得到了显著提升，逻辑读降低了 7 倍作用，虽然相对于改写效率还是高很多，但是在急需处理的情况下该方案还是更加可取的，此时对执行计划进行绑定即可，无需修改代码。

4）最后执行计划中还是存在全表扫描，我使用 hint 使其强制走索引查看情况：

SQL>   SELECT /*+   index(@"SEL$9E43CB6E" "T"@"SEL$2") */ NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId"
  2    FROM OFFER_SPEC_RELA T
  3    LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS
  4    ON T.RELA_GRP_ID     = SUBOS.OFFER_SPEC_GRP_ID
  5    AND subos.start_dt <= SYSDATE
  6    AND subos.end_dt    >= SYSDATE
  7    WHERE T.RELA_TYPE_CD = 2
  8    AND t.start_dt      <= SYSDATE
  9 AND t.end_dt        >= SYSDATE
10 AND (T.OFFER_SPEC_ID = 109910000618
11    OR EXISTS
12      (SELECT /*+ push_subq */ A.OFFER_SPEC_GRP_ID
13      FROM OFFER_SPEC_GRP_RELA A
14      WHERE A.SUB_OFFER_SPEC_ID = 109910000618
15      AND T.OFFER_SPEC_GRP_ID   = A.OFFER_SPEC_GRP_ID
16    ))
17   AND rownum<500;
Elapsed: 00:00:00.69

Execution Plan
----------------------------------------------------------
Plan hash value: 355757032

---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

640?wx_fmt=png

   1 - filter(ROWNUM<500)
   3 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@! AND
              ("T"."OFFER_SPEC_ID"=109910000618 OR EXISTS (SELECT /*+ PUSH_SUBQ */ 0 FROM
              "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND
              "A"."SUB_OFFER_SPEC_ID"=109910000618)))
   5 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1)
   6 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!)
   7 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+))

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
      10527 consistent gets
        406 physical reads
          0 redo size
        339 bytes sent via SQL*Net to client
        510 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed