增加子查詢表條件篩選提高效能

前提

客戶反應現場有sql很慢，故拿來最佳化下。

原有慢sql

select case
         when t1.sys_id = '200000000' then
          ''
         else
          t1.USER_NO
       end user_no,
       t2.USERCARD,
       t2.NAME,
       t2.USERID_WEIXIN
     from (select *
  from WEIXINUSER.t_user_relation tur11
 where not exists (select id
          from (select id
                  from (select distinct id, sys_id
                          from WEIXINUSER.t_user_relation
                         where sys_id in ('100000000', '200000000')
                         order by to_number(id))
                 group by id
                having count(id) > 1) tmp1
         where tmp1.id = tur11.id)
 order by id) t1
 left join WEIXINUSER.t_user t2
 on t1.id = t2.userid
 AND t2.collect_state = '1'
 where t1.sys_id in('100000000', '200000000')
 and t1.register_flag='1'
 and usercard = '411322199104084230';

執行計劃

1   #NSET2: [1350, 115781, 720] 
2     #PRJT2: [1350, 115781, 720]; exp_num(4), is_atom(FALSE) 
3       #SORT3: [1350, 115781, 720]; key_num(4), is_distinct(TRUE), top_flag(0), is_adaptive(0)
4         #PRJT2: [1322, 115781, 720]; exp_num(4), is_atom(FALSE) 
5           #UNION ALL: [1322, 115781, 720]
6             #PRJT2: [665, 39043, 720]; exp_num(4), is_atom(FALSE) 
7               #HASH2 INNER JOIN: [665, 39043, 720];  KEY_NUM(1); KEY(RE.ID=US.USERID) KEY_NULL_EQU(0)
8                 #PRJT2: [632, 39180, 480]; exp_num(2), is_atom(FALSE) 
9                   #HASH RIGHT SEMI JOIN2: [632, 39180, 480]; n_keys(1)   KEY(DMTEMPVIEW_16912689.colname=TUR1.ID) KEY_NULL_EQU(0)
10                    #PRJT2: [344, 39180, 144]; exp_num(1), is_atom(FALSE) 
11                      #PRJT2: [344, 39180, 144]; exp_num(1), is_atom(FALSE) 
12                        #SLCT2: [344, 39180, 144]; exp_sfun1 > var5
13                          #HAGR2: [318, 783619, 144]; grp_num(1), sfun_num(1); slave_empty(0) keys(DMTEMPVIEW_16912681.ID) 
14                            #PRJT2: [246, 871279, 144]; exp_num(1), is_atom(FALSE) 
15                              #DISTINCT: [246, 871279, 144]
16                                #HASH RIGHT SEMI JOIN2: [175, 871279, 144]; n_keys(1)   KEY(DMTEMPVIEW_16912692.colname=T_USER_RELATION.SYS_ID) KEY_NULL_EQU(0)
17                                  #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
18                                  #SSCN: [108, 872653, 96]; IDX_ZZJ_20210903_T_USER_RELATION_01(T_USER_RELATION)
19                    #SLCT2: [185, 775688, 480]; TUR1.SYS_ID = '100000000'
20                      #CSCN2: [185, 872653, 480]; INDEX33556450(T_USER_RELATION as TUR1)
21                #SLCT2: [15, 77008, 240]; (NOT(US.NAME IS NULL) AND US.COLLECT_STATE = '1')
22                  #CSCN2: [15, 92083, 240]; INDEX33556463(T_USER as US)
23            #PRJT2: [632, 76738, 728]; exp_num(4), is_atom(FALSE) 
24              #HASH2 INNER JOIN: [632, 76738, 728];  KEY_NUM(1); KEY(T2.USERID=T1.ID) KEY_NULL_EQU(0)
25                #SLCT2: [15, 77008, 240]; (NOT(T2.NAME IS NULL) AND T2.COLLECT_STATE = '1')
26                  #CSCN2: [15, 92083, 240]; INDEX33556463(T_USER as T2)
27                #PRJT2: [584, 140677, 488]; exp_num(3), is_atom(FALSE) 
28                  #HASH RIGHT SEMI JOIN2: [584, 140677, 488]; n_keys(1)  (ANTI),  KEY(TMP1.ID=TUR11.ID) KEY_NULL_EQU(0)
29                    #PRJT2: [344, 39180, 144]; exp_num(1), is_atom(FALSE) 
30                      #SLCT2: [344, 39180, 144]; exp_sfun1 > var7
31                        #HAGR2: [318, 783619, 144]; grp_num(1), sfun_num(1); slave_empty(0) keys(DMTEMPVIEW_16912685.ID) 
32                          #PRJT2: [246, 871279, 144]; exp_num(1), is_atom(FALSE) 
33                            #DISTINCT: [246, 871279, 144]
34                              #HASH RIGHT SEMI JOIN2: [175, 871279, 144]; n_keys(1)   KEY(DMTEMPVIEW_16912699.colname=T_USER_RELATION.SYS_ID) KEY_NULL_EQU(0)
35                                #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
36                                #SSCN: [108, 872653, 96]; IDX_ZZJ_20210903_T_USER_RELATION_01(T_USER_RELATION)
37                    #HASH RIGHT SEMI JOIN2: [210, 179858, 488]; n_keys(1)   KEY(DMTEMPVIEW_16912700.colname=TUR11.SYS_ID) KEY_NULL_EQU(0)
38                      #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
39                      #SLCT2: [187, 180545, 488]; TUR11.REGISTER_FLAG = '1'
40                        #CSCN2: [187, 872653, 488]; INDEX33556450(T_USER_RELATION as TUR11)

分析1

根據執行計劃看，大量有全表掃描CSCN2存在，企圖用加索引的方式來提高效能，但效果不大。

分析2

1.根據分析，t_user的and usercard = '411322199104084230'篩選很高，但涉及此欄位的索引並沒有起到實際作用，反而走的是全表掃描。
2.關聯表t_user雖然是left join主驅動表，但其實最後篩選and usercard = '411322199104084230'還是取的並集。

最佳化

由上面分析2判斷，將關聯表的範圍提前縮小，再去hash，故嘗試修改sql。
修改sql最佳化(4s提升到0.03s)

select case
         when t1.sys_id = '200000000' then
          ''
         else
          t1.USER_NO
       end user_no,
       t2.USERCARD,
       t2.NAME,
       t2.USERID_WEIXIN
     from (select *
  from WEIXINUSER.t_user_relation tur11
  --增加條件篩選
 where id in (select userid from WEIXINUSER.t_user where usercard = '411322199104084230') and
  not exists (select id
          from (select id
                  from (select distinct id, sys_id
                          from WEIXINUSER.t_user_relation
                         where sys_id in ('100000000', '200000000'))
                 group by id
                having count(id) > 1) tmp1
         where tmp1.id = tur11.id)) t1
  left join WEIXINUSER.t_user t2
 on t1.id = t2.userid
 AND t2.collect_state = '1'
where t1.sys_id in('100000000', '200000000')
 and t1.register_flag='1'
 --and usercard = '411322199104084230'

最佳化後執行計劃

1   #NSET2: [5, 1, 584] 
2     #PRJT2: [5, 1, 584]; exp_num(4), is_atom(FALSE) 
3       #INDEX JOIN LEFT JOIN2: [5, 1, 584] join condition(T2.COLLECT_STATE = '1') ret_null(0)
4         #PRJT2: [5, 1, 584]; exp_num(3), is_atom(FALSE) 
5           #NEST LOOP SEMI JOIN2: [5, 1, 584];  (ANTI), join condition(TUR11.ID = TMP1.ID)[with var]
6             #HASH RIGHT SEMI JOIN2: [1, 1, 584]; n_keys(1)   KEY(DMTEMPVIEW_16912747.colname=TUR11.SYS_ID) KEY_NULL_EQU(0)
7               #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
8               #SLCT2: [1, 1, 584]; TUR11.REGISTER_FLAG = '1'
9                 #NEST LOOP INDEX JOIN2: [1, 1, 584] 
10                  #DISTINCT: [1, 1, 96]
11                    #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE) 
12                      #BLKUP2: [1, 1, 96]; USERCARD_INDEX(T_USER)
13                        #SSEK2: [1, 1, 96]; scan_type(ASC), USERCARD_INDEX(T_USER), scan_range['411322199104084230','411322199104084230']
14                  #BLKUP2: [1, 1, 0]; IDX_USER_RELATION_ID(TUR11)
15                    #SSEK2: [1, 1, 0]; scan_type(ASC), IDX_USER_RELATION_ID(T_USER_RELATION as TUR11), scan_range[DMTEMPVIEW_16912735.colname,DMTEMPVIEW_16912735.colname]
16            #PRJT2: [2, 1, 96]; exp_num(1), is_atom(FALSE) 
17              #SLCT2: [2, 1, 96]; exp_sfun1 > var6
18                #HAGR2: [2, 1, 96]; grp_num(1), sfun_num(1); slave_empty(0) keys(DMTEMPVIEW_16912731.ID) 
19                  #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE) 
20                    #DISTINCT: [1, 1, 96]
21                      #NEST LOOP INDEX JOIN2: [1, 1, 96] 
22                        #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
23                        #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_ZZJ_20210903_T_USER_RELATION_01(T_USER_RELATION), scan_range[(DMTEMPVIEW_16912746.colname,var4),(DMTEMPVIEW_16912746.colname,var4)]
24        #BLKUP2: [1, 1, 0]; IDX_USER_ID(T2)
25          #SSEK2: [1, 1, 0]; scan_type(ASC), IDX_USER_ID(T_USER as T2), scan_range[T1.ID,T1.ID]

DM 武漢達夢資料庫股份有限公司
24小時免費服務熱線：400 991 6599