【干货攻略】达梦SQL之SORT优化

达梦E学 2023-02-23

2928

前言

SQL优化是一个比较复杂的事情，想要做好优化一要靠扎实的技术功底，二要靠丰富的实践经验，这里来谈一下SQL优化中的SORT优化问题。

在之前的优化经验中，错误的认为只要结果集不大，含有order by的SQL可以不用考虑SORT的消耗。其实数据库单独启动SORT这个算法都需要很大的开销。下面介绍下如何去掉SORT。

本章内容已在如下环境中测试：

操作系统：银河麒麟V10

数据库：DM8

相关关键字：SORT、排序ORDER BY、TOP_ORDER_OPT_FLAG

本次测试的DM8数据库版本号如下：

单表查询去掉SORT

对于单表查询含有order by的SQL，去掉SORT比较简单，创建对应的索引即可，如：

建表：

CREATE TABLE TAB02(
ID int,
NAME varchar(10),
ADDR datetime);

插入测试数据：

insert into tab02
select level,'a'||level,sysdate-level
from dual
connect by level <=100;
commit;

查看如下sql执行计划：

select * from tab02 a where id > 98 order by id desc

执行计划：

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

create index IDX_TAB02_ID on tab02(id);
sp_index_stat_init('SYSDBA','IDX_TAB02_ID');

查看执行计划，可以看到SORT已经去掉了：

如果使用如下sql查询，查看执行计划，可以看到仍然有SORT：

select * from tab02 a where id = 99 order by addr desc;

这个时候怎么去掉SORT呢？可以创建组合索引：

先删除原来的索引：

drop index IDX_TAB02_ID;
create index IDX_TAB02_ID_ADDR on tab02(id, addr desc );
sp_index_stat_init('SYSDBA','IDX_TAB02_ID_ADDR');

再看看执行计划：

可以看到使用了组合索引，第一个sql和第二个sql的SORT都没有了。

多表关联去掉SORT

单表查询的SQL去掉SORT相对简单，对于两表关联的SQL，去掉SORT稍复杂，可能需要配合DM数据库的相关排序调优参数。

建表，准备测试数据：

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<=10000;
commit;
insert into t1
select 1000000+level,   1000000+level-1,   mod(level,5) ,sysdate-level,sysdate-level,200000+level 
from dual connect by level<=10000;
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<=10000;
commit;
update t2 set t2.organ_no=t2.organ_No-1;
commit;

创建索引：

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);

查询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

一般情况下，我们认为这个结果很好了，可以不用优化了；但如果数据库并发较高，去掉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');

再看看执行计划：

可以看到执行计划中有一个 ACTRL 操作符，它说明优化器为这一条 SQL 语句生成了备用计划。ACTRL 是控制备用计划转换的操作符，其上面一层 NEST LOOP INDEX JOIN2为默认的主计划，再上面一层 HASH2 INNER JOIN 则为备用计划。ACTRL 操作符计算下层孩子节点的代价，决定采用默认主计划还是备用计划。

ACTRL操作符是因为DM数据集默认开启了自适应计划，由参数ADAPTIVE_NPLN_FLAG控制，此参数是动态会话级参数，我们可以将此参数改为0，关闭自适应计划。

alter session set 'ADAPTIVE_NPLN_FLAG'=0;

查看执行计划，可以看到优化器已经选择了去除SORT。

TOP_ORDER_OPT_FLAG参数说明

TOP_ORDER_OPT_FLAG 动态，会话级参数，默认值为0。

DM8新版本已经支持disql窗口下使用show parameter命令查看参数：

此参数用于优化带有 TOP 和 ORDER BY 子句的查询，使得 SORT 操作符可以省略。优化的效果是尽量使得 ORDER BY 的排序列所对应的基表可以使用包含排序列的索引，从而可以移除排序 SORT 操作符，减少排序操作。如果排序列不属于同一个基表，或者排序列不是基表列，则无法进行优化。取值：

0：不启用该优化；

1：对最优索引进行优化；

2：优先选择与排序列一致的可以消除排序的索引进行优化；

4：若查询项中包含 TOP 子句和集函数，且查询语句中不存在分组项，则移除 TOP 子句。仅当 TOP 值不为 0 时进行该优化；

8: 当前就有计划是 CSCN 的 TOP_ORDER 语句，根据估算需要 BLKUP 的行数判断是否依然使用 TOP_ORDER 优化。

此参数支持使用上述有效值的组合值，如 3 表示同时进行 1 和 2 的优化。可以使用如下命令修改当前会话的该参数值：

alter session set 'TOP_ORDER_OPT_FLAG'=1;

或者，上面SQL中，在自适应计划开启的情况下，如果加上hint提示，将TOP_ORDER_OPT_FLAG设置为1：

SELECT  /*+TOP_ORDER_OPT_FLAG(1) */
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;

查看执行计划，可以看到执行计划已经去掉SORT。

总结

1、使用索引可以消除执行计划中的SORT排序操作符。

2、多表关联，优化器没有选择组合索引去除SORT时，可以使用TOP_ORDER_OPT_FLAG参数帮助优化器做选择；TOP查询排序也可以使用此参数。

3、系统高并发时，频繁执行的sql应尽量减少SORT，不管排序的结果集是否大。因为数据库启动SORT需要很大的开销，如果排序SQL频繁执行，会很消耗资源。

2023年，达梦干货栏目会一如继往的给大家分享达梦数据库的相关知识。如果大家在工作中有好的想法也可以整理成文章发给我们，我们将在公众号分享给大家。欢迎投稿，期待与大家一同进步！

END

达梦知识普及

扫码关注我们

学习共享

知识普及

原文：引用自达梦在线服务平台，具体请点击“阅读原文”

编辑：青城

排版：哈哈

sql sort sql优化排序算法索引

文章转载自达梦E学，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【干货攻略】达梦SQL之SORT优化

评论