注册
【与达梦同行】SQL去掉sort
技术分享/ 文章详情 /

【与达梦同行】SQL去掉sort

tdj305 2022/12/16 1752 3 1

SQL优化是一个比较复杂的事情,想要做好优化一要靠扎实的技术功底,二要靠丰富的实践经验,这里来谈一下SQL优化中的sort优化问题。
在之前的优化经验中,错误的任务只要结果集不大,含有order by的SQL可以不用考虑sort的消耗。在最近的一次测试中,在梁神的指导下,才发现原来单独启动sort这个算法都需要很大的开销。下面介绍下如何去掉sort。
1、 单表查询消sort
对于单表查询含有order by 的SQL,这个消掉sort比较简单,建个对应的索引即可,如:
建表:

CREATE TABLE TAB02
(
ID CHAR(10),
NAME CHAR(10),
ADDR CHAR(10));
插入测试数据:
insert into tab02 select level,'a'||level,sysdate-level from dual connect by level <=100;
commit;
select * from tab02 a where id > 98 order by id desc
执行计划:

1   #NSET2: [1, 5, 112] 
2     #PRJT2: [1, 5, 112]; exp_num(4), is_atom(FALSE) 
3       #SORT3: [1, 5, 112]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #SLCT2: [1, 5, 112]; A.ID > 98
5           #CSCN2: [1, 112, 112]; INDEX33555464(TAB02 as A)

创建索引并收集统计信息:

create index IDX_TAB02_ID on tab02(id desc);
sp_Index_stat_Init('SYSDBA','IDX_TAB02_ID');
执行计划:
1   #NSET2: [1, 2, 112] 
2     #PRJT2: [1, 2, 112]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [1, 2, 112]; IDX_TAB02_ID(A)
4         #SSEK2: [1, 2, 112]; scan_type(ASC), IDX_TAB02_ID(TAB02 as A), scan_range(null2,98)

我们可以看到sort已经去掉了。
再插入100条数据:

insert into tab02 select level,'a'||level,sysdate-level from dual connect by level <=100;
commit;
select * from tab02 a where id = 99 order by addr desc
查看执行计划:

1   #NSET2: [1, 2, 112] 
2     #PRJT2: [1, 2, 112]; exp_num(4), is_atom(FALSE) 
3       #SORT3: [1, 2, 112]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #BLKUP2: [1, 2, 112]; IDX_TAB02_ID_ADDR(A)
5           #SSEK2: [1, 2, 112]; scan_type(ASC), IDX_TAB02_ID_ADDR(TAB02 as A), scan_range[99,99]

发现sort又出来了,这个时候怎么去掉sort呢?我们可以建个组合索引:

drop index IDX_TAB02_ID_ADDR;
create index IDX_TAB02_ID_ADDR on tab02(id, addr desc );
sp_Index_stat_Init('SYSDBA','IDX_TAB02_ID_ADDR');
再看看执行计划:

1   #NSET2: [1, 2, 112] 
2     #PRJT2: [1, 2, 112]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [1, 2, 112]; IDX_TAB02_ID_ADDR(A)
4         #SSEK2: [1, 2, 112]; scan_type(ASC), IDX_TAB02_ID_ADDR(TAB02 as A), scan_range[(99,min),(99,max))

可以看到sort也没有了。

2、二表关联查询消sort
对与单表查询的SQL去掉sort相对简单点,对于二表关联的SQL,去掉sort还是比较麻烦的。

建表:create  table t1 (organ_no varchar2(20),parent_organ varchar2(20),organ_level varchar2(20),crt_time datetime,mod_time datetime,trans_Id varchar2(20));
create  table t2 (trans_Id varchar2(20),organ_no varchar2(20),parent_organ varchar2(20),organ_level varchar2(20),crt_time datetime,mod_time datetime,
	VOUCH_GROUP varchar2(10),IS_OPEN varchar2(5),up_flag varchar2(5));
插入测试数据:
insert into t1 select 1000000+level,   1000000+level-1,   mod(level,5) ,sysdate-level,sysdate-level,200000+level from dual connect by level<=100;
commit;
insert into t1 select 1000000+level,   1000000+level-1,   mod(level,5) ,sysdate-level,sysdate-level,200000+level from dual connect by level<=100;
commit;
insert into t2 select 200000+level,1000000+level,   1000000+level-1,   mod(level,5) ,sysdate-level,sysdate-level,999000+level,mod(level,3),mod(level,6) from dual connect by level<=100;
commit;

update t2 set t2.organ_no=t2.organ_No-1;
建索引:
create OR REPLACE index IDX_T1_ORGAN_NO on t1(ORGAN_NO);
create OR REPLACE index IDX_T2_TRANS_ID on t2(TRANS_ID,ORGAN_NO);
sp_index_stat_init('SYSDBA','IDX_T1_ORGAN_NO');
sp_index_stat_init('SYSDBA','IDX_T2_TRANS_ID');
查看SQL及执行计划:
SELECT
        t2.ORGAN_NO
FROM
        t1 ,
        t2 
WHERE
        t1.ORGAN_NO     = '1000038'
    AND t2.TRANS_ID     = '200038'
    AND t2.VOUCH_GROUP  = '999038'
    AND t2.IS_OPEN      >'0'
    AND t2.UP_FLAG      <'3'
    AND t1.PARENT_ORGAN = t2.ORGAN_NO
ORDER BY
        t1.ORGAN_LEVEL DESC

1   #NSET2: [1, 5, 384] 
2     #PRJT2: [1, 5, 384]; exp_num(1), is_atom(FALSE) 
3       #SORT3: [1, 5, 384]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #SLCT2: [1, 5, 384]; (T2.VOUCH_GROUP = '999038' AND T2.IS_OPEN > '0' AND T2.UP_FLAG < '3')
5           #NEST LOOP INDEX JOIN2: [1, 5, 384] 
6             #BLKUP2: [1, 5, 144]; IDX_T1_ORGAN_NO(T1)
7               #SSEK2: [1, 5, 144]; scan_type(ASC), IDX_T1_ORGAN_NO(T1), scan_range['1000038','1000038']
8             #BLKUP2: [1, 1, 96]; IDX_T2_TRANS_ID(T2)
9               #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_T2_TRANS_ID(T2), scan_range[('200038',T1.PARENT_ORGAN),('200038',T1.PARENT_ORGAN)]

一般情况下,我们认为这个结果很好了,可以不用优化了,但是在某些情况下,这个sort去掉的话,对整个性能提升还是有很大帮助的,下面就当前这种场景去掉sort。

create OR REPLACE index IDX_T1_ORGAN_NO on t1(ORGAN_NO,ORGAN_LEVEL DESC,PARENT_ORGAN);
create OR REPLACE index IDX_T2_TRANS_ID on t2(TRANS_ID,ORGAN_NO);
sp_index_stat_init('SYSDBA','IDX_T1_ORGAN_NO');
sp_index_stat_init('SYSDBA','IDX_T2_TRANS_ID');
再看看执行计划:
1   #NSET2: [1, 5, 384] 
2     #PRJT2: [1, 5, 384]; exp_num(1), is_atom(FALSE) 
3       #SLCT2: [1, 5, 384]; (T2.VOUCH_GROUP = '999038' AND T2.IS_OPEN > '0' AND T2.UP_FLAG < '3')
4         #NEST LOOP INDEX JOIN2: [1, 5, 384] 
5           #SSEK2: [1, 5, 144]; scan_type(ASC), IDX_T1_ORGAN_NO(T1), scan_range[('1000038',min,min),('1000038',max,max))
6           #BLKUP2: [1, 1, 96]; IDX_T2_TRANS_ID(T2)
7             #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_T2_TRANS_ID(T2), scan_range[('200038',T1.PARENT_ORGAN),('200038',T1.PARENT_ORGAN)]

我们可以看到sort已经没有了。
这里面主要,当t2的结果集只有1条,t1的结果集有2条的时候,默认的顺序是先做t2再做t1,这样的话sort也是没法消除的,可以通过增加hint改变t1,t2的顺序:

SELECT/*+order(t1 t2)*/
        t2.ORGAN_NO
FROM
        t1 ,
        t2 
WHERE
        t1.ORGAN_NO     = '1000038'
    AND t2.TRANS_ID     = '200038'
    AND t2.VOUCH_GROUP  = '999038'
    AND t2.IS_OPEN      >'0'
    AND t2.UP_FLAG      <'3'
    AND t1.PARENT_ORGAN = t2.ORGAN_NO
ORDER BY
        t1.ORGAN_LEVEL DESC

3、TOP + ORDER且ORDER BY列属于索引前导列
这种是文档上面的,参考文档上面的例子,建表并构造数据:

DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1(C1 INT, C2 INT);
CREATE TABLE T2(D1 INT, D2 INT);
INSERT INTO T1 SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 80000;
INSERT INTO T2 SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 80000;
COMMIT;
CREATE INDEX IND1 ON T1(C1, C2);
CREATE INDEX IND2 ON T2(D1);
SELECT /*+ TOP_ORDER_OPT_FLAG(0) */ TOP 10 C1, D1, D2 FROM T1 JOIN T2 ON C1 = D1 ORDER BY C1;
执行计划:
1   #NSET2: [4151, 10, 12] 
2     #PRJT2: [4151, 10, 12]; exp_num(3), is_atom(FALSE) 
3       #SORT3: [4151, 10, 12]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4         #HASH2 INNER JOIN: [33, 63296639, 12];  KEY_NUM(1); KEY(T1.C1=T2.D1) KEY_NULL_EQU(0)
5           #SSCN: [8, 80000, 4]; IND1(T1)
6           #CSCN2: [8, 80000, 8]; INDEX33555496(T2)

执行结果:
SELECT /*+ TOP_ORDER_OPT_FLAG(0) */ TOP 10 C1, D1, D2 FROM T1 JOIN T2 ON C1 = D1 ORDER BY C1;
执行成功, 执行耗时9毫秒. 执行号:2198
通过et看,耗时主要在sort上,如图:
image.png
调整hint:

SELECT /*+ TOP_ORDER_OPT_FLAG(1) */ TOP 10 C1, D1, D2 FROM T1 JOIN T2 ON C1 = D1 ORDER BY C1;
执行计划:
1   #NSET2: [2, 10, 12] 
2     #PRJT2: [2, 10, 12]; exp_num(3), is_atom(FALSE) 
3       #TOPN2: [2, 10, 12]; top_num(10)
4         #NEST LOOP INDEX JOIN2: [2, 600, 12] 
5           #SSCN: [1, 300, 4]; IND1(T1)
6           #BLKUP2: [1, 2, 4]; IND2(T2)
7             #SSEK2: [1, 2, 4]; scan_type(ASC), IND2(T2), scan_range[T1.C1,T1.C1]

执行结果:
SELECT /*+ TOP_ORDER_OPT_FLAG(1) */ TOP 10 C1, D1, D2 FROM T1 JOIN T2 ON C1 = D1 ORDER BY C1;
执行成功, 执行耗时1毫秒. 执行号:2217

评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服