达梦数据库sql优化实践2

Sql执行过程中，对有索引的列上使用隐式类型转换或函数都会造成索引失效的情况，需要特别注意。
原sql语句如下
SELECT
TAB1.RECORDID ,
TAB1.F_CENTRAL_ID TAB1bh ,
TAB1.COLJSXID ,
(CASE WHEN TAB1.XCOLYIS NULL THEN ‘’ WHEN TAB1.XCOLY> CURRENT_DATE THEN ‘否’ ELSE ‘是’ END ) AS SFCQ,
TAB1.TAB2Q ,
TAB1.TAB1XS ,
TAB2.XM ,
TAB2.ZZ ,
TAB1.WTSD ,
TAB1.COLJMDDM ,
TAB1.NRFLDM ,
TAB1.GKXX ,
TAB1.DJRXM ,
TAB1.JTRXM ,
TAB1.DJBM ,
TAB1.XCOLKZSJ
FROM
TAB1XX TAB1
left JOIN TAB2XX TAB2
ON
TAB1.RECORDID = TAB2.TAB1ID
WHERE
TAB1.TAB1XS= 200
and exists
(
select
*
from
(
SELECT
B.TAB1ID,
B.TAB3SJ
FROM
TAB3FSXX B
WHERE
B.TAB3JGDM = ‘BW0061000000’
AND B.TAB3FSDM LIKE ‘03%’
AND B.TAB3ZT = 1
AND B.TAB3SJ >= TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD hh24:mi:ss’)
and TAB1.recordid=B.TAB1id
order by
b.TAB3sj desc
limit 1
)
T
where
EXISTS
(
SELECT
1
FROM
TABXXX TABX
WHERE
TABX.SSTAB1ID = T.TAB1ID
AND TABX.SJRKSJ >= TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD hh24:mi:ss’)
AND TABX.TABXRQ > T.TAB3SJ
)
)
AND NOT EXISTS
(
SELECT 1 FROM TAB1XX_SYQK SY WHERE SY.TAB1ID = TAB1.RECORDID
)
AND TAB1.DJSJ >= TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD hh24:mi:ss’)
AND TAB2.TAB2DJSJ >= TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD hh24:mi:ss’)
AND TAB1.DJSJ < TO_DATE(‘2021-09-14’, ‘YYYY-MM-DD hh24:mi:ss’) + 1
AND TAB2.TAB2DJSJ < TO_DATE(‘2021-09-14’, ‘YYYY-MM-DD hh24:mi:ss’) + 1
AND TAB1.COL1LIKE concat(’%’, ‘05’, ‘%’)
ORDER BY
TAB2Q DESC ；
原执行计划如下：
1 #NSET2: [12833, 62270, 776]
2 #PIPE2: [12833, 62270, 776]
3 #PRJT2: [12832, 62270, 776]; exp_num(16), is_atom(FALSE)
4 #SORT3: [12832, 62270, 776]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
5 #HASH RIGHT SEMI JOIN2: [12816, 62270, 776]; n_keys(1) (ANTI), KEY(SY.TAB1ID=TAB1.RECORDID) KEY_NULL_EQU(0)
6 #SSCN: [1, 5828, 48]; IDX_TAB1XX_SYQK_TAB1ID(TAB1XX_SYQK as SY)
7 #SLCT2: [12803, 68098, 776]; NOREFED_EXISTS_SSS[sss3]
8 #SLCT2: [12803, 68098, 776]; (TAB2.TAB2DJSJ >= var4 AND TAB2.TAB2DJSJ < var7)
9 #NEST LOOP INDEX JOIN2: [12803, 68098, 776]
10 #SLCT2: [8254, 68098, 620]; (exp_cast(TAB1.TAB1XS) = 200 AND exp11 > 0)
11 #TAB3KUP2: [8254, 4352032, 620]; TAB1XXINDEX2(TAB1)
12 #SSEK2: [8254, 4352032, 620]; scan_type(ASC), TAB1XXINDEX2(TAB1XX as TAB1), scan_range[exp11,exp11+exp_cast(1))
13 #TAB3KUP2: [445, 1, 0]; IND_TAB2XX_TAB1ID(TAB2)
14 #SSEK2: [445, 1, 0]; scan_type(ASC), IND_TAB2XX_TAB1ID(TAB2XX as TAB2), scan_range[TAB1.RECORDID,TAB1.RECORDID]
15 #SPL2: [1, 1, 194]; key_num(1), spool_num(0), is_atom(FALSE), has_variaTAB3e(1)
16 #PRJT2: [1, 1, 194]; exp_num(1), is_atom(FALSE)
17 #INDEX JOIN SEMI JOIN2: [1, 1, 194]; join condition((TABX.TABXRQ > T.TAB3SJ AND TABX.SJRKSJ >= exp11))
18 #PRJT2: [1, 1, 194]; exp_num(2), is_atom(FALSE)
19 #SORT3: [1, 1, 194]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
20 #SLCT2: [1, 1, 194]; (B.TAB3ZT = var8 AND B.TAB3SJ >= var9)
21 #TAB3KUP2: [1, 1, 194]; IDX_TAB3FSXX_TAB3JGDM_TAB1ID_TAB3FSDM_CRSJ(B)
22 #SSEK2: [1, 1, 194]; scan_type(ASC), IDX_TAB3FSXX_TAB3JGDM_TAB1ID_TAB3FSDM_CRSJ(TAB3FSXX as B), scan_range[(‘BW0061000000’,var3,‘03’,min),(‘BW0061000000’,var3,‘04’,min))
23 #TAB3KUP2: [1, 1, 0]; TABXXXINDEX2(TABX)
24 #SSEK2: [1, 1, 0]; scan_type(ASC), TABXXXINDEX2(TABXXX as TABX), scan_range[T.TAB1ID,T.TAB1ID]

注意“ TAB1.TAB1XS= 200”这一行，TAB1XS字段是varchar类型，等号后的200是数值型，产生了隐式类型转换，导致索引IDX_TAB1_TAB1XS_DJSJ没有被用到，修改为“ TAB1.TAB1XS= ‘200’”后执行计划变为：
1 #NSET2: [7170, 62270, 776]
2 #PIPE2: [7170, 62270, 776]
3 #PRJT2: [7169, 62270, 776]; exp_num(16), is_atom(FALSE)
4 #SORT3: [7169, 62270, 776]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
5 #HASH RIGHT SEMI JOIN2: [7154, 62270, 776]; n_keys(1) (ANTI), KEY(SY.TAB1ID=TAB1.RECORDID) KEY_NULL_EQU(0)
6 #SSCN: [1, 5828, 48]; IDX_TAB1XX_SYQK_TAB1ID(TAB1XX_SYQK as SY)
7 #SLCT2: [7141, 68098, 776]; NOREFED_EXISTS_SSS[sss3]
8 #SLCT2: [7141, 68098, 776]; (TAB2.TAB2DJSJ >= var4 AND TAB2.TAB2DJSJ < var7)
9 #NEST LOOP INDEX JOIN2: [7141, 68098, 776]
10 #SLCT2: [2592, 68098, 620]; exp11 > 0
11 #TAB3KUP2: [2592, 1358097, 620]; IDX_TAB1_TAB1XS_DJSJ(TAB1)
12 #SSEK2: [2592, 1358097, 620]; scan_type(ASC), IDX_TAB1_TAB1XS_DJSJ(TAB1XX as TAB1), scan_range[(‘200’,exp11),(‘200’,exp11+exp_cast(1)))
13 #TAB3KUP2: [445, 1, 0]; IND_TAB2XX_TAB1ID(TAB2)
14 #SSEK2: [445, 1, 0]; scan_type(ASC), IND_TAB2XX_TAB1ID(TAB2XX as TAB2), scan_range[TAB1.RECORDID,TAB1.RECORDID]
15 #SPL2: [1, 1, 194]; key_num(1), spool_num(0), is_atom(FALSE), has_variaTAB3e(1)
16 #PRJT2: [1, 1, 194]; exp_num(1), is_atom(FALSE)
17 #INDEX JOIN SEMI JOIN2: [1, 1, 194]; join condition((TABX.TABXRQ > T.TAB3SJ AND TABX.SJRKSJ >= exp11))
18 #PRJT2: [1, 1, 194]; exp_num(2), is_atom(FALSE)
19 #SORT3: [1, 1, 194]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
20 #SLCT2: [1, 1, 194]; (B.TAB3ZT = var8 AND B.TAB3SJ >= var9)
21 #TAB3KUP2: [1, 1, 194]; IDX_TAB3FSXX_TAB3JGDM_TAB1ID_TAB3FSDM_CRSJ(B)
22 #SSEK2: [1, 1, 194]; scan_type(ASC), IDX_TAB3FSXX_TAB3JGDM_TAB1ID_TAB3FSDM_CRSJ(TAB3FSXX as B), scan_range[(‘BW0061000000’,var3,‘03’,min),(‘BW0061000000’,var3,‘04’,min))
23 #TAB3KUP2: [1, 1, 0]; TABXXXINDEX2(TABX)
24 #SSEK2: [1, 1, 0]; scan_type(ASC), TABXXXINDEX2(TABXXX as TABX), scan_range[T.TAB1ID,T.TAB1ID]
Cost由12833降低到7270，执行时间由150秒降低到11秒。

社区地址：https://eco.dameng.com