使用UNION代替OR 提升查询性能

最新推荐文章于 2020-08-19 09:31:04 发布

最新推荐文章于 2020-08-19 09:31:04 发布 · 86 阅读

本文介绍了一个具体的SQL查询优化案例，通过重构SQL语句和创建组合索引的方式显著提高了查询效率，将执行时间从20秒降低到0.53秒，并详细展示了优化前后的执行计划及统计信息。

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

昨天一位铁哥们通过QQ找我帮忙，问下面这个SQL能否优化

SQL> set timing on SQL> set autotrace on SQL> select count(*) rowcount_lhy 2 from swgl_ddjbxx t 3 where t.fzgs_dm = '001085' 4 and (t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e' or exists 5 (select y.kh_id 6 from khgl_khywdlxx y 7 where y.kh_id = t.kh_id 8 and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e') or 9 t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e') 10 and t.xjbz = '9999' 11 and t.FROMNBGL1 = '0'; SQL> set line 300 SQL> / ROWCOUNT_LHY ------------ 60 已用时间: 00: 00: 20.53 执行计划 ---------------------------------------------------------- Plan hash value: 1217125969 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 86 | 28048 (1)| 00:05:37 | | 1 | SORT AGGREGATE | | 1 | 86 | | | |* 2 | FILTER | | | | | | |* 3 | TABLE ACCESS FULL | SWGL_DDJBXX | 5926 | 497K| 28048 (1)| 00:05:37 | |* 4 | TABLE ACCESS BY INDEX ROWID| KHGL_KHYWDLXX | 1 | 57 | 5 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IDX_KHGL_KHYWDLXX_KHID | 1 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' OR "T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e' OR EXISTS (SELECT 0 FROM "KHGL_KHYWDLXX" "Y" WHERE "Y"."KH_ID"=:B1 AND "Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')) 3 - filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085') 4 - filter("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e') 5 - access("Y"."KH_ID"=:B1) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 804560 consistent gets 71127 physical reads 0 redo size 516 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

看了5秒钟，回复哥们说把or展开成union,但是那哥们SQL确实太菜了呵呵(别骂我哈)，只好帮忙写一个了

SQL> select count(*) 2 from (select * 3 from swgl_ddjbxx t 4 where t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e' 5 and t.fzgs_dm = '001085' 6 and t.xjbz = '9999' 7 and t.FROMNBGL1 = '0' 8 union 9 select * 10 from swgl_ddjbxx t 11 where t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e' 12 and t.fzgs_dm = '001085' 13 and t.xjbz = '9999' 14 and t.FROMNBGL1 = '0' 15 union 16 select * 17 from swgl_ddjbxx t 18 where exists 19 (select y.kh_id 20 from khgl_khywdlxx y 21 where y.kh_id = t.kh_id 22 and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e') 23 and t.fzgs_dm = '001085' 24 and t.xjbz = '9999' 25 and t.FROMNBGL1 = '0'); COUNT(*) ---------- 60 已用时间: 00: 00: 06.89 执行计划 ---------------------------------------------------------- Plan hash value: 3846872744 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 52263 (1)| 00:10:28 | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | VIEW | | 5996 | | | 52263 (1)| 00:10:28 | | 3 | SORT UNIQUE | | 5996 | 2238K| 6344K| 52263 (47)| 00:10:28 | | 4 | UNION-ALL | | | | | | | |* 5 | TABLE ACCESS FULL | SWGL_DDJBXX | 59 | 19234 | | 28037 (1)| 00:05:37 | |* 6 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 10 | 3260 | | 1209 (1)| 00:00:15 | |* 7 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_KPRDM | 4748 | | | 34 (0)| 00:00:01 | |* 8 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 1 | 326 | | 5 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 5927 | 2216K| | 22527 (1)| 00:04:31 | | 10 | SORT UNIQUE | | 10165 | 565K| | 1916 (1)| 00:00:23 | | 11 | TABLE ACCESS BY INDEX ROWID| KHGL_KHYWDLXX | 10165 | 565K| | 1916 (1)| 00:00:23 | |* 12 | INDEX RANGE SCAN | IDX_KHGL_KHYWDLXX_SSKHJL | 10165 | | | 111 (0)| 00:00:02 | |* 13 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_KHID | 2 | | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' AND "T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085') 6 - filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085') 7 - access("T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e') 8 - filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085') 12 - access("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e') 13 - access("Y"."KH_ID"="T"."KH_ID") 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 128422 consistent gets 10308 physical reads 0 redo size 512 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed

SQL改写之后，执行时间由原来的20秒下降到6秒，逻辑读由804560降低到128422，性能还是有很大提升的，到了这里优化还没完，可以创建一个组合索引进一步优化

create index idx on swgl_ddjbxx(fzgs_dm,xjbz,FROMNBGL1);

创建索引之后，原始的SQL执行时间，执行计划，统计信息如下：

SQL> select count(*) rowcount_lhy 2 from swgl_ddjbxx t 3 where t.fzgs_dm = '001085' 4 and (t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e' or exists 5 (select y.kh_id 6 from khgl_khywdlxx y 7 where y.kh_id = t.kh_id 8 and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e') or 9 t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e') 10 and t.xjbz = '9999' 11 and t.FROMNBGL1 = '0'; ROWCOUNT_LHY ------------ 60 已用时间: 00: 00: 02.96 执行计划 ---------------------------------------------------------- Plan hash value: 3049366449 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 86 | 506 (0)| 00:00:07 | | 1 | SORT AGGREGATE | | 1 | 86 | | | |* 2 | FILTER | | | | | | | 3 | TABLE ACCESS BY INDEX ROWID| SWGL_DDJBXX | 5926 | 497K| 506 (0)| 00:00:07 | |* 4 | INDEX RANGE SCAN | IDX | 2370 | | 12 (0)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| KHGL_KHYWDLXX | 1 | 57 | 5 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IDX_KHGL_KHYWDLXX_KHID | 1 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' OR "T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e' OR EXISTS (SELECT 0 FROM "KHGL_KHYWDLXX" "Y" WHERE "Y"."KH_ID"=:B1 AND "Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')) 4 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0') 5 - filter("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e') 6 - access("Y"."KH_ID"=:B1) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 702767 consistent gets 0 physical reads 0 redo size 516 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

改写的SQL：

SQL> select count(*) 2 from (select * 3 from swgl_ddjbxx t 4 where t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e' 5 and t.fzgs_dm = '001085' 6 and t.xjbz = '9999' 7 and t.FROMNBGL1 = '0' 8 union 9 select * 10 from swgl_ddjbxx t 11 where t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e' 12 and t.fzgs_dm = '001085' 13 and t.xjbz = '9999' 14 and t.FROMNBGL1 = '0' 15 union 16 select * 17 from swgl_ddjbxx t 18 where exists 19 (select y.kh_id 20 from khgl_khywdlxx y 21 where y.kh_id = t.kh_id 22 and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e') 23 and t.fzgs_dm = '001085' 24 and t.xjbz = '9999' 25 and t.FROMNBGL1 = '0'); COUNT(*) ---------- 60 已用时间: 00: 00: 00.53 执行计划 ---------------------------------------------------------- Plan hash value: 2947849958 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3469 (1)| 00:00:42 | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | VIEW | | 5995 | | | 3469 (1)| 00:00:42 | | 3 | SORT UNIQUE | | 5995 | 2238K| 4760K| 3469 (86)| 00:00:42 | | 4 | UNION-ALL | | | | | | | |* 5 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 59 | 19234 | | 506 (0)| 00:00:07 | |* 6 | INDEX RANGE SCAN | IDX | 2370 | | | 12 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 10 | 3260 | | 50 (0)| 00:00:01 | | 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | | 9 | BITMAP AND | | | | | | | | 10 | BITMAP CONVERSION FROM ROWIDS| | | | | | | |* 11 | INDEX RANGE SCAN | IDX | 2370 | | | 12 (0)| 00:00:01 | | 12 | BITMAP CONVERSION FROM ROWIDS| | | | | | | |* 13 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_KPRDM | 2370 | | | 34 (0)| 00:00:01 | |* 14 | HASH JOIN RIGHT SEMI | | 5926 | 2216K| | 2423 (1)| 00:00:30 | | 15 | TABLE ACCESS BY INDEX ROWID | KHGL_KHYWDLXX | 10165 | 565K| | 1916 (1)| 00:00:23 | |* 16 | INDEX RANGE SCAN | IDX_KHGL_KHYWDLXX_SSKHJL | 10165 | | | 111 (0)| 00:00:02 | | 17 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 5926 | 1886K| | 506 (0)| 00:00:07 | |* 18 | INDEX RANGE SCAN | IDX | 2370 | | | 12 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e') 6 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0') 11 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0') filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085') 13 - access("T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e') 14 - access("Y"."KH_ID"="T"."KH_ID") 16 - access("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e') 18 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0') 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 25628 consistent gets 0 physical reads 0 redo size 512 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed

由于我不能直接连接到DB，这个SQL的优化就到此为止。