DM SQL 排序优化-消除排序

在数据库查询优化中，ORDER BY 子句导致的排序操作往往是性能瓶颈之一。当前测试展示如何通过合理的索引设计来消除排序操作，显著提升查询性能。

场景介绍 我们有一个销售表 EMPLOYEE，包含以下字段：

• HIRE_DATE：入职时间
• EMPLOYEE_ID：员工ID
• SALARY：薪水

业务需求：查询最近11 年的员工信息，并按入职时间降序、员工ID 降序排列。

场景一：没有索引

初始查询语句：

select e.HIRE_DATE, e.EMPLOYEE_ID, e.SALARY from DMHR.EMPLOYEE e where e.HIRE_DATE >= trunc(sysdate) - interval '11' year order by e.HIRE_DATE desc, e.EMPLOYEE_ID desc;

查询出的数据：

HIRE_DATE EMPLOYEE_ID SALARY '2015-03-27' 8004 5000 '2015-03-27' 6004 5000

执行计划：

1 #NSET2: [1, 1, 33] 2 #PRJT2: [1, 1, 33]; exp_num(4), is_atom(FALSE) 3 #SORT3: [1, 1, 33]; key_num(2), partition_key_num(0), is_distinct(FALSE), top_flag(0), is_adaptive(0) 4 #SLCT2: [1, 1, 33]; e.HIRE_DATE >= var4 5 #CSCN2: [1, 856, 33]; INDEX33555499(EMPLOYEE as e); btr_scan(1)

场景二：只有一个单列索引

只在 HIRE_DATE 列上创建一个索引：

CREATE INDEX DMHR.IND_HIRE_DATE20251231 ON DMHR.EMPLOYEE("HIRE_DATE" ASC);

执行计划：

1 #NSET2: [1, 1, 33] 2 #PRJT2: [1, 1, 33]; exp_num(4), is_atom(FALSE) 3 #SORT3: [1, 1, 33]; key_num(2), partition_key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0) 4 #BLKUP2: [1, 1, 33]; IND_HIRE_DATE20251231(e) 5 #SSEK2: [1, 1, 33]; scan_type(DESC), IND_HIRE_DATE20251231(EMPLOYEE as e), scan_range[exp11,max], is_global(0)

关键发现：上面执行计划中出现了 SORT3 操作符，说明数据库在进行显式排序，可以通过ET 输出，可以判断使用了多少内存(MEM_USED(KB)，DISK_USED(KB))。

第一次优化：创建复合索引

create index IND_HIRE_ID on DMHR.EMPLOYEE(HIRE_DATE, EMPLOYEE_ID);

(删除旧索引后，实际测试没有删除页可以)重新执行查询，执行计划变为：

1 #NSET2: [1, 1, 33] 2 #PRJT2: [1, 1, 33]; exp_num(4), is_atom(FALSE) 3 #BLKUP2: [1, 1, 33]; IND_HIRE_ID(e) 4 #SSEK2: [1, 1, 33]; scan_type(DESC), IND_HIRE_ID(EMPLOYEE as e), scan_range[(exp11,min),(max,max)), is_global(0)

优化效果：SORT3 操作符消失了！数据库通过反向扫描索引直接获得了正确排序的结果。

场景二：混合排序场景

修改查询，要求按入职时间降序、员工ID升序排列：

select e.HIRE_DATE, e.EMPLOYEE_ID, e.SALARY from DMHR.EMPLOYEE e where e.HIRE_DATE >= trunc(sysdate) - interval '11' year order by e.HIRE_DATE desc, e.EMPLOYEE_ID asc;

执行计划：

1 #NSET2: [1, 1, 33] 2 #PRJT2: [1, 1, 33]; exp_num(4), is_atom(FALSE) 3 #SORT3: [1, 1, 33]; key_num(2), partition_key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0) 4 #BLKUP2: [1, 1, 33]; IND_HIRE_DATE20251231(e) 5 #SSEK2: [1, 1, 33]; scan_type(DESC), IND_HIRE_DATE20251231(EMPLOYEE as e), scan_range[exp11,max], is_global(0)

问题再现：SORT3 操作符再次出现！虽然使用了复合索引，但排序方向不匹配。

解决方案：匹配排序方向的索引

创建与 ORDER BY 子句完全一致的索引：

create or replace index IND_HIRE_ID on DMHR.EMPLOYEE(HIRE_DATE desc, EMPLOYEE_ID asc);

执行计划：

1 #NSET2: [1, 1, 33] 2 #PRJT2: [1, 1, 33]; exp_num(4), is_atom(FALSE) 3 #BLKUP2: [1, 1, 33]; IND_HIRE_ID(e) 4 #SSEK2: [1, 1, 33]; scan_type(ASC), IND_HIRE_ID(EMPLOYEE as e), scan_range[(null2,min),(exp11,max)), is_global(0)

最终效果：排序再次被消除！这次数据库通过正向扫描特殊设计的索引获得了正确结果。

核心原理总结

1. 索引消除排序的条件

• 当 ORDER BY 的顺序与索引扫描结果的顺序完全一致时，可消除排序
• 数据库可以正向或反向扫描索引来匹配不同的排序需求

2. 不同场景下的匹配规则

ORDER BY 子句 可消除排序的索引 扫描方向 A DESC, B DESC (A ASC, B ASC) 反向扫描 A ASC, B ASC (A ASC, B ASC) 正向扫描 A DESC, B ASC (A DESC, B ASC) 正向扫描

3. 达梦数据库的执行计划关键操作符

• SORT3：显式排序操作，消耗内存和CPU
• SSEK2：索引范围扫描，scan_type 显示扫描方向
• BLKUP2：通过索引回表取数据

实践建议

• 分析执行计划：关注 SORT 操作符的出现，这是优化的信号
• 创建复合索引：将 WHERE 条件和 ORDER BY 的列都考虑进索引
• 匹配排序方向：对于混合排序（ASC/DESC混合），创建对应方向的索引
• 权衡索引数量：虽然专用索引性能最佳，但要考虑维护成本

欢迎访问达梦技术分享社区 ECO

https://eco.dameng.com