烦请协助一个SQL调优

[AntTech_ZUHSV7]

【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】4.2.2
【问题描述】由于OB在存储上的优势，我们计划将系统迁移从MySQL至OB，测试后发现，虽然DML语句变快了，但是，系统的很多复杂查询都变慢了。
其中有一个在我们系统中比较典型。
SELECT COUNT(DISTINCT kh.id_)
FROM ftsp_kh_khxx kh
JOIN ftsp_zt_ztxx zt on kh.id_=zt.kh_khxx_id_ and zt.is_default_=‘1’
JOIN ftsp_zj_zjxx zjxx ON kh.zj_zjxx_id_ = zjxx.id_
JOIN ftsp_kh_szhd_szsm szsm on szsm.kh_khxx_id_=kh.id_ and szsm.szlx_code_=‘06’
JOIN ftsp_infra_szsm infraSzsm on infraSzsm.smbh_=szsm.infra_szsm_smbh_
LEFT JOIN ftsp_sb_khxx_gs gs on gs.kh_khxx_id_=kh.id_ and gs.kj_qj_=‘202403’
LEFT JOIN ftsp_sb_sbxx sb on sb.kh_khxx_id_=kh.id_ and sb.ss_qj_=‘202403’ and sb.bb_code_=‘50003001’
LEFT JOIN ftsp_kh_jcxx jcxx ON jcxx.kh_khxx_id_ = kh.id_
LEFT join ftsp_kh_cjxx cj on cj.kh_khxx_id_=kh.id_
LEFT JOIN ftsp_sb_cstg cstg ON cstg.kh_khxx_id_ = kh.id_
LEFT JOIN ftsp_sb_gs_tgxx tg on tg.kh_khxx_id_ = kh.id_
LEFT join ftsp_sb_sbxx_status gsSjqr on gsSjqr.kh_khxx_id_=kh.id_ and gsSjqr.ss_qj_=‘202403’ and gsSjqr.bb_code_=‘50003001’
LEFT JOIN ftsp_dj_lzd lzd on lzd.kh_khxx_id_ = kh.id_ AND lzd.kj_qj_ = ‘202403’
LEFT JOIN ftsp_kh_dsf_xtzh xtzh ON xtzh.kh_khxx_id_ = kh.id_ AND xtzh.xtlx_code_ =‘04’
WHERE kh.fw_status_ = ‘1’ AND kh.is_delete_ = ‘0’ AND kh.ht_fw_zt_ = ‘1’
AND (IFNULL(jcxx.swzxh_,‘0’) = ‘0’ OR IFNULL(cj.ycqj_,‘210001’) > ‘202403’)
AND (zjxx.approval_account_ = ‘0’ OR EXISTS(SELECT 1 FROM ftsp_ht_htxx ht WHERE ht.kh_khxx_id_ = kh.id_ AND ht.status_ = ‘4’ AND ‘202403’ >= ht.fwqx_q_ AND ht.fwqx_z_ >= ‘202403’))
AND kh.zj_zjxx_id_ = ‘h0000000000000498723430515097600’
AND kh.id_ in
( SELECT DISTINCT pg_.kh_khxx_id_ FROM ftsp_kh_pg pg_,ftsp_zj_bmyh yh_ ,ftsp_zj_bmxx bm_
WHERE pg_.infra_user_id_ = yh_.infra_user_id_
AND yh_.zj_bmxx_id_ = bm_.id_
AND yh_.is_active_ = 1
AND yh_.is_delete_ = 0
AND pg_.infra_user_id_ = ‘h0000000000000399057290070958080’
AND bm_.zj_zjxx_id_= ‘h0000000000000498723430515097600’ );
MySQL执行时间：0.86s
MySQL执行计划：

1	PRIMARY	zjxx		const	PRIMARY	PRIMARY	130	const	1	100.00
1	PRIMARY	yh_		ref	idx_ftsp_zj_bmyh_2,idx_ftsp_zj_bmyh_1	idx_ftsp_zj_bmyh_2	133	const,const,const	1	100.00	Start temporary
1	PRIMARY	bm_		eq_ref	PRIMARY,idx_ftsp_zj_bmxx_1	PRIMARY	130	satp_pre.yh_.zj_bmxx_id_	1	5.00	Using where
1	PRIMARY	pg_		ref	idx_ftsp_kh_pg_1,idx_ftsp_kh_pg_3	idx_ftsp_kh_pg_3	131	const	384	100.00	Using where; Using index
1	PRIMARY	kh		eq_ref	PRIMARY,idx_ftsp_kh_khxx_1,idx_ftsp_kh_khxx_4,idx_ftsp_kh_khxx_13	PRIMARY	130	satp_pre.pg_.kh_khxx_id_	1	5.00	Using where
1	PRIMARY	szsm		ref	uk_ftsp_kh_szhd_szsm,idx_ftsp_kh_szhd_szsm_1	uk_ftsp_kh_szhd_szsm	131	satp_pre.pg_.kh_khxx_id_	5	10.00	Using index condition; Using where; End temporary
1	PRIMARY	gs		eq_ref	uk_ftsp_sb_khxx_gs_1	uk_ftsp_sb_khxx_gs_1	158	satp_pre.pg_.kh_khxx_id_,const	1	100.00	Using index
1	PRIMARY	sb		eq_ref	uk_ftsp_sb_sbxx,idx_ftsp_sb_sbxx_2,idx_ftsp_sb_sbxx_3	uk_ftsp_sb_sbxx	289	satp_pre.pg_.kh_khxx_id_,const,const	1	100.00	Using index
1	PRIMARY	jcxx		eq_ref	idx_ftsp_kh_jcxx_1	idx_ftsp_kh_jcxx_1	131	satp_pre.pg_.kh_khxx_id_	1	100.00
1	PRIMARY	cj		eq_ref	ftsp_kh_cjxx_index	ftsp_kh_cjxx_index	131	satp_pre.pg_.kh_khxx_id_	1	100.00	Using where
1	PRIMARY	cstg		eq_ref	uk_ftsp_sb_cstg_1	uk_ftsp_sb_cstg_1	129	satp_pre.pg_.kh_khxx_id_	1	100.00	Using where; Using index
1	PRIMARY	tg		eq_ref	uk_ftsp_sb_gs_tgxx_1	uk_ftsp_sb_gs_tgxx_1	131	satp_pre.pg_.kh_khxx_id_	1	100.00	Using index
1	PRIMARY	gsSjqr		ref	uk_ftsp_sb_sbxx_status_1,idx_ftsp_sb_sbxx_status_2	uk_ftsp_sb_sbxx_status_1	289	satp_pre.pg_.kh_khxx_id_,const,const	1	100.00	Using index
1	PRIMARY	lzd		eq_ref	uk_ftsp_dj_lzd_1	uk_ftsp_dj_lzd_1	156	satp_pre.pg_.kh_khxx_id_,const	1	100.00	Using index
1	PRIMARY	xtzh		ref	idx_ftsp_kh_dsf_xtzh_3,idx_ftsp_kh_dsf_xtzh_1	idx_ftsp_kh_dsf_xtzh_1	174	satp_pre.pg_.kh_khxx_id_,const	1	100.00	Using index
1	PRIMARY	zt		ref	idx_ftsp_zt_ztxx_1,idx_ftsp_zt_ztxx_2	idx_ftsp_zt_ztxx_1	133	satp_pre.pg_.kh_khxx_id_,const	1	100.00	Using index
1	PRIMARY	infraSzsm		ref	idx_ftsp_infra_szsm_1	idx_ftsp_infra_szsm_1	51	satp_pre.szsm.infra_szsm_smbh_	1	100.00	Using where; Using index
2	DEPENDENT SUBQUERY	ht		ref	idx_ftsp_ht_htxx_1,idx_ftsp_ht_htxx_7,idx_ftsp_ht_htxx_11,idx_ftsp_ht_htxx_16,idx_ftsp_ht_htxx_14,idx_ftsp_ht_htxx_21,idx_ftsp_ht_htxx_13	idx_ftsp_ht_htxx_16	128	satp_pre.kh.id_	2	10.21	Using where; Using index

OB执行时间：12.1s
OB执行计划：
OCEANBASE执行.txt (56.8 KB)

【复现路径】看着是一些地方用了全表进行HASH JOIN的代价比较高，能否提供一些思路。

[AntTech_ZUHSV7]

我们已经定位到问题了，感谢。

[王利博]

老师 麻烦问题是什么问题导致的呢。

[AntTech_ZUHSV7]

你好，oceanbase基于代价的查询改写，会将OR表达式改写为union all以方便使用不同字段的索引，但是，我们这个场景不需要进行查询改写，感觉是oceanbase在评估改写与不改写的时候cost出现了一些偏差。

执行计划上显示，不改写的代价更高，但是实际的情况是不改写快很多。

|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|

---

|0 |SCALAR GROUP BY | |1 |136707800 |

|1 |└─SUBPLAN FILTER | |6432495 |136591217 |

|2 | ├─HASH JOIN | |6432495 |3992410 |

|3 | │ ├─TABLE FULL SCAN |infraSzsm(idx_ftsp_infra_szsm_1)|1255 |22 |

|4 | │ └─HASH JOIN | |6112177 |1796236 |

|5 | │ ├─MERGE OUTER JOIN | |495511 |222119 |

|6 | │ │ ├─NESTED-LOOP OUTER JOIN | |202327 |78397 |

|7 | │ │ │ ├─NESTED-LOOP OUTER JOIN | |937 |58886 |

|8 | │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |571 |47005 |

|9 | │ │ │ │ │ ├─NESTED-LOOP JOIN | |441 |37825 |

|10| │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |428 |28910 |

|11| │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |428 |16242 |

|12| │ │ │ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |384 |8243 |

|13| │ │ │ │ │ │ │ │ │ ├─NESTED-LOOP JOIN CARTESIAN | |384 |241 |

|14| │ │ │ │ │ │ │ │ │ │ ├─MERGE JOIN | |384 |226 |

|15| │ │ │ │ │ │ │ │ │ │ │ ├─SUBPLAN SCAN |VIEW1 |384 |59 |

|16| │ │ │ │ │ │ │ │ │ │ │ │ └─MERGE DISTINCT | |384 |58 |

|17| │ │ │ │ │ │ │ │ │ │ │ │ └─NESTED-LOOP JOIN CARTESIAN | |385 |54 |

|18| │ │ │ │ │ │ │ │ │ │ │ │ ├─TABLE RANGE SCAN |pg_(idx_ftsp_kh_pg_3) |384 |15 |

|19| │ │ │ │ │ │ │ │ │ │ │ │ └─MATERIAL | |2 |28 |

|20| │ │ │ │ │ │ │ │ │ │ │ │ └─HASH JOIN | |2 |28 |

|21| │ │ │ │ │ │ │ │ │ │ │ │ ├─TABLE RANGE SCAN |yh_(idx_ftsp_zj_bmyh_2) |1 |9 |

|22| │ │ │ │ │ │ │ │ │ │ │ │ └─TABLE RANGE SCAN |bm_(idx_ftsp_zj_bmxx_1) |52 |6 |

|23| │ │ │ │ │ │ │ │ │ │ │ └─TABLE RANGE SCAN |kh(idx_ftsp_kh_khxx_13) |1131 |118 |

|24| │ │ │ │ │ │ │ │ │ │ └─MATERIAL | |1 |5 |

|25| │ │ │ │ │ │ │ │ │ │ └─TABLE GET |zjxx |1 |5 |

|26| │ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |gsSjqr(uk_ftsp_sb_sbxx_status_1)|1 |21 |

|27| │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |gs(uk_ftsp_sb_khxx_gs_1) |1 |21 |

|28| │ │ │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |cj(ftsp_kh_cjxx_index) |1 |29 |

|29| │ │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |zt(idx_ftsp_zt_ztxx_1) |1 |21 |

|30| │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |xtzh(idx_ftsp_kh_dsf_xtzh_1) |1 |21 |

|31| │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |sb(uk_ftsp_sb_sbxx) |1 |21 |

|32| │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |lzd(uk_ftsp_dj_lzd_1) |1 |21 |

|33| │ │ └─TABLE FULL SCAN |jcxx(idx_kh_khxx_id__) |1924625 |83671 |

|34| │ └─TABLE FULL SCAN |szsm |1242058 |650776 |

|35| └─DISTRIBUTED TABLE RANGE SCAN |ht(idx_ftsp_ht_htxx_14) |1 |21 |

=======================================================================================================================

Outputs & filters:

---

0 - output([T_FUN_COUNT(distinct kh.id_)]), filter(nil), rowset=256

  group(nil), agg_func([T_FUN_COUNT(distinct kh.id_)])

1 - output([kh.id_]), filter([cast(zjxx.approval_account_, DECIMAL(4, 0)) = cast(‘0’, DECIMAL(1, -1)) OR (T_OP_EXISTS, subquery(1))]), rowset=256

  exec_params_([kh.id_(:0)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false

2 - output([kh.id_], [zjxx.approval_account_]), filter(nil), rowset=256

  equal_conds([infraSzsm.smbh_ = szsm.infra_szsm_smbh_]), other_conds([IFNULL(cast(jcxx.swzxh_, VARCHAR(4)), '0') = '0' OR IFNULL(cj.ycqj_, '210001')

  > '202403'])

3 - output([infraSzsm.smbh_]), filter(nil), rowset=256

  access([infraSzsm.smbh_]), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([infraSzsm.smbh_], [infraSzsm.id_]), range(MIN,MIN ; MAX,MAX)always true

4 - output([kh.id_], [zjxx.approval_account_], [cj.ycqj_], [jcxx.swzxh_], [szsm.infra_szsm_smbh_]), filter(nil), rowset=256

  equal_conds([szsm.kh_khxx_id_ = kh.id_]), other_conds(nil)

5 - output([kh.id_], [zjxx.approval_account_], [cj.ycqj_], [jcxx.swzxh_]), filter(nil), rowset=256

  equal_conds([jcxx.kh_khxx_id_ = kh.id_]), other_conds(nil)

  merge_directions([ASC])

6 - output([kh.id_], [zjxx.approval_account_], [cj.ycqj_]), filter(nil), rowset=256

  conds(nil), nl_params_([kh.id_(:12)]), use_batch=false

7 - output([kh.id_], [zjxx.approval_account_], [cj.ycqj_]), filter(nil), rowset=256

  conds(nil), nl_params_([kh.id_(:8)]), use_batch=false

8 - output([kh.id_], [zjxx.approval_account_], [cj.ycqj_]), filter(nil), rowset=256

  conds(nil), nl_params_([kh.id_(:13)]), use_batch=false

9 - output([kh.id_], [zjxx.approval_account_], [cj.ycqj_]), filter(nil), rowset=256

  conds(nil), nl_params_([kh.id_(:3)]), use_batch=false

10 - output([kh.id_], [zjxx.approval_account_], [cj.ycqj_]), filter(nil), rowset=256

  conds(nil), nl_params_([kh.id_(:10)]), use_batch=false

11 - output([kh.id_], [zjxx.approval_account_]), filter(nil), rowset=256

  conds(nil), nl_params_([kh.id_(:7)]), use_batch=false

12 - output([kh.id_], [zjxx.approval_account_]), filter(nil), rowset=256

  conds(nil), nl_params_([kh.id_(:11)]), use_batch=false

13 - output([kh.id_], [zjxx.approval_account_]), filter(nil), rowset=256

  conds(nil), nl_params_(nil), use_batch=false

14 - output([kh.id_]), filter(nil), rowset=256

  equal_conds([kh.id_ = VIEW1.kh_khxx_id_]), other_conds(nil)

  merge_directions([ASC])

15 - output([VIEW1.kh_khxx_id_]), filter(nil), rowset=256

  access([VIEW1.kh_khxx_id_])

16 - output([pg_.kh_khxx_id_]), filter(nil), rowset=256

  distinct([pg_.kh_khxx_id_])

17 - output([pg_.kh_khxx_id_]), filter(nil), rowset=256

  conds(nil), nl_params_(nil), use_batch=false

18 - output([pg_.kh_khxx_id_]), filter(nil), rowset=256

  access([pg_.kh_khxx_id_]), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([pg_.infra_user_id_], [pg_.kh_khxx_id_], [pg_.create_date_], [pg_.id_]), range(h0000000000000399057290070958080,MIN,MIN,MIN ; h0000000000000399057290070958080,

  MAX,MAX,MAX),

  range_cond([pg_.infra_user_id_ = 'h0000000000000399057290070958080'])

19 - output(nil), filter(nil), rowset=256

20 - output(nil), filter(nil), rowset=256

  equal_conds([yh_.zj_bmxx_id_ = bm_.id_]), other_conds(nil)

21 - output([yh_.zj_bmxx_id_]), filter(nil), rowset=256

  access([yh_.id_], [yh_.zj_bmxx_id_]), partitions(p0)

  is_index_back=true, is_global_index=false,

  range_key([yh_.infra_user_id_], [yh_.is_delete_], [yh_.is_active_], [yh_.id_]), range(h0000000000000399057290070958080,0,1,MIN ; h0000000000000399057290070958080,

  0,1,MAX),

  range_cond(['h0000000000000399057290070958080' = yh_.infra_user_id_], [yh_.is_active_ = 1], [yh_.is_delete_ = 0])

22 - output([bm_.id_]), filter(nil), rowset=256

  access([bm_.id_]), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([bm_.zj_zjxx_id_], [bm_.dept_no_], [bm_.id_]), range(h0000000000000498723430515097600,MIN,MIN ; h0000000000000498723430515097600,MAX,MAX),

   

  range_cond([bm_.zj_zjxx_id_ = 'h0000000000000498723430515097600'])

23 - output([kh.id_]), filter(nil), rowset=256

  access([kh.id_]), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([kh.zj_zjxx_id_], [kh.is_delete_], [kh.fw_status_], [kh.ht_fw_zt_], [kh.id_]), range(h0000000000000498723430515097600,0,1,1,MIN ; h0000000000000498723430515097600,

  0,1,1,MAX),

  range_cond([cast(kh.fw_status_, DECIMAL(4, 0)) = cast('1', DECIMAL(1, -1))], [cast(kh.is_delete_, DECIMAL(4, 0)) = cast('0', DECIMAL(1, -1))], [cast(kh.ht_fw_zt_,

   DECIMAL(4, 0)) = cast('1', DECIMAL(1, -1))], [kh.zj_zjxx_id_ = 'h0000000000000498723430515097600'])

24 - output([zjxx.approval_account_]), filter(nil), rowset=256

25 - output([zjxx.approval_account_]), filter(nil), rowset=256

  access([zjxx.approval_account_]), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([zjxx.id_]), range[h0000000000000498723430515097600 ; h0000000000000498723430515097600],

  range_cond([zjxx.id_ = 'h0000000000000498723430515097600'])

26 - output(nil), filter(nil), rowset=256

  access(nil), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([gsSjqr.kh_khxx_id_], [gsSjqr.ss_qj_], [gsSjqr.bb_code_], [gsSjqr.hdlx_code_], [gsSjqr.shadow_pk_0]), range(MIN ; MAX),

  range_cond([gsSjqr.ss_qj_ = '202403'], [gsSjqr.bb_code_ = '50003001'], [gsSjqr.kh_khxx_id_ = :11])

27 - output(nil), filter(nil), rowset=256

  access(nil), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([gs.kh_khxx_id_], [gs.kj_qj_], [gs.shadow_pk_0]), range(MIN ; MAX),

  range_cond([gs.kj_qj_ = '202403'], [gs.kh_khxx_id_ = :7])

28 - output([cj.ycqj_]), filter(nil), rowset=256

  access([cj.id_], [cj.ycqj_]), partitions(p0)

  is_index_back=true, is_global_index=false,

  range_key([cj.kh_khxx_id_], [cj.shadow_pk_0]), range(MIN ; MAX),

  range_cond([cj.kh_khxx_id_ = :10])

29 - output(nil), filter(nil), rowset=256

  access(nil), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([zt.kh_khxx_id_], [zt.is_default_], [zt.id_]), range(MIN ; MAX),

  range_cond([cast(zt.is_default_, DECIMAL(1, 0)) = cast('1', DECIMAL(1, -1))], [:3 = zt.kh_khxx_id_])

30 - output(nil), filter(nil), rowset=256

  access(nil), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([xtzh.kh_khxx_id_], [xtzh.xtlx_code_], [xtzh.id_]), range(MIN ; MAX),

  range_cond([xtzh.xtlx_code_ = '04'], [xtzh.kh_khxx_id_ = :13])

31 - output(nil), filter(nil), rowset=256

  access(nil), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([sb.kh_khxx_id_], [sb.ss_qj_], [sb.bb_code_], [sb.shadow_pk_0]), range(MIN ; MAX),

  range_cond([sb.ss_qj_ = '202403'], [sb.bb_code_ = '50003001'], [sb.kh_khxx_id_ = :8])

32 - output(nil), filter(nil), rowset=256

  access(nil), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([lzd.kh_khxx_id_], [lzd.kj_qj_], [lzd.shadow_pk_0]), range(MIN ; MAX),

  range_cond([lzd.kj_qj_ = '202403'], [lzd.kh_khxx_id_ = :12])

33 - output([jcxx.kh_khxx_id_], [jcxx.swzxh_]), filter(nil), rowset=256

  access([jcxx.kh_khxx_id_], [jcxx.swzxh_]), partitions(p0)

  is_index_back=false, is_global_index=false,

  range_key([jcxx.kh_khxx_id_], [jcxx.swzxh_], [jcxx.id_]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true

34 - output([szsm.kh_khxx_id_], [szsm.infra_szsm_smbh_]), filter([szsm.szlx_code_ = ‘06’]), rowset=256

  access([szsm.kh_khxx_id_], [szsm.szlx_code_], [szsm.infra_szsm_smbh_]), partitions(p0)

  is_index_back=false, is_global_index=false, filter_before_indexback[false],

  range_key([szsm.id_]), range(MIN ; MAX)always true

35 - output([1]), filter([ht.status_ = ‘4’], [‘202403’ >= ht.fwqx_q_]), rowset=256

  access([ht.id_], [ht.status_], [ht.fwqx_q_]), partitions(p0)

  limit(1), offset(nil), is_index_back=true, is_global_index=false, filter_before_indexback[true,false],

  range_key([ht.kh_khxx_id_], [ht.fwqx_z_], [ht.status_], [ht.htlx_], [ht.id_]), range(MIN,MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX,MAX)always true,

  range_cond([ht.kh_khxx_id_ = :0], [ht.fwqx_z_ >= '202403'])