达梦DM8 SQL调优实践

在数据库运维体系中，SQL调优是提升系统性能的核心环节。本文基于《DM8系统管理员手册》故障排除和性能优化模块核心内容，从“执行计划解析”这一基础工具入手，结合DM8数据库实例初始化的示例库DMHR展开实践。

一、EXPLAIN执行计划介绍

SQL调优的前提是明确“当前SQL如何执行”，达梦提供的EXPLAIN执行计划正是揭示这一过程的核心工具。它能直观展示SQL的表扫描方式、连接逻辑、索引使用等关键信息，帮助我们定位“全表扫描”“重复扫描”等性能瓶颈。

1.1 执行计划的使用方法

即时查看执行计划：使用EXPLAIN + SQL语句可以查看DML语句的执行计划，以查询department_id为103的全部员工信息为例：

-- 查询SELECT * FROM dmhr.employee WHERE department_id = 103这句SQL的执行计划
EXPLAIN SELECT * FROM dmhr.employee WHERE department_id = 103

得到的执行计划如下：

1   #NSET2: [1, 21, 285] 
2     #PRJT2: [1, 21, 285]; exp_num(12), is_atom(FALSE) 
3       #SLCT2: [1, 21, 285]; EMPLOYEE.DEPARTMENT_ID = 103 SLCT_PUSHDOWN(TRUE)
4         #CSCN2: [1, 856, 285]; INDEX33555578(EMPLOYEE) NEED_SLCT(TRUE); btr_scan(1)

这个执行计划看起来就像一棵树，执行过程为：控制流从上向下传递，数据流从下向上传递。其中，类似[1, 21, 285]这样的三个数字，分别表示估算的操作符代价、处理的记录行数和每行记录的字节数。
执行计划的解读顺序遵循以下原则：

缩进越深的越先执行
同样缩进的上面的先执行，下面的后执行
上下的优先级高于内外

即时查看执行计划：使用EXPLAIN FOR + SQL语句语法，这种方法查询执行计划以结果集的方式返回，并且显示的执行计划信息更加丰富，除了常规计划信息，还包括创建索引议、
分区表的起止分区信息等。重要的是，语句的计划保存在数据表中，方便用户随时查看，进行计划对比分析，可以作为性能分析的一种方法
查看历史执行计划：开启监控开关ENABLE_MONITOR=1后，通过动态视图V$SQL_PLAN查询已执行SQL的历史执行计划，适合追溯慢SQL的执行逻辑。

实操示例：查看DMHR部门调薪统计SQL的执行计划

-- 方式1：即时查看执行计划
EXPLAIN FOR
WITH salary_log_2025 AS (
  SELECT 
    department_id,
    newsalary - oldsalary AS salary_increase
  FROM dmhr.log_addsalary
  WHERE LOGTIME BETWEEN '2025-01-01' AND '2025-12-31'
)
SELECT 
  d.department_id,
  d.department_name,
  COUNT(log.salary_increase) AS 调薪人数
FROM dmhr.department d
LEFT JOIN salary_log_2025 log 
  ON d.department_id = log.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(log.salary_increase) > 0;

-- 方式2：通过动态视图查历史计划（需先执行SQL）
SELECT 
  PLN_ADDR,    -- 计划缓存地址
  HASH_VALUE,  -- 计划HASH值
  SQLSTR,      -- 完整SQL语句
  OPTIMIZER,   -- 优化方式
  TABLEID,     -- 涉及的表ID
  PHD_TIME     -- 计划生成时间
FROM V$SQL_PLAN 
WHERE SQLSTR LIKE '%salary_log_2025%' 
  AND SQLSTR LIKE '%dmhr.log_addsalary%'
ORDER BY PHD_TIME DESC;

1.2 执行计划常见操作符及其含义

操作符名称	含义说明
CSCN2	聚集索引全扫描
CSEK2	聚集索引范围扫描
SSEK2	二级索引范围扫描
SSCN2	二级索引全扫描
BLKUP	根据二级索引的ROWID回原表中取出全部数据

注意：统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用，需定期用DBMS_STATS.GATHER_TABLE_STATS('DMHR','LOG_ADDSALARY')收集表统计信息。

二、SQL调优实践

DM8数据库实例初始化的示例库DMHR包含员工表（EMPLOYEE）、部门表（DEPARTMENT）、调薪日志表（LOG_ADDSALARY）等表，以下操作内容根据该库进行操作。

场景一：索引调优——解决全表扫描瓶颈

设计场景：HR部门每月需统计103研发部2010年1月1日后入职员工的薪资台账。

低效SQL及问题分析

-- 查询103部门2010年1月1日后入职的员工薪资信息
SELECT employee_id, employee_name, salary, hire_date
FROM dmhr.employee
WHERE department_id = 103 
  AND hire_date >= TO_DATE('2010-01-01', 'YYYY-MM-DD');

通过EXPLAIN查看执行计划：

最下面一层操作符为“CSCN2”（全表扫描），需遍历整个EMPLOYEE表数据文件；
ROW_NUMS为856可以确认确实进行了全表扫描。
根本原因：过滤条件（department_id、hire_date）未建立索引，数据库无法快速定位目标数据。

优化思路

针对查询的“过滤条件+返回字段”创建复合索引：
索引前缀选择过滤高频字段（department_id优先，基数较低易筛选）+ 范围字段（hire_date）

调优SQL及效果验证

-- 创建复合索引
CREATE INDEX IDX_EMP_DEPT_HIRE_SAL ON dmhr.employee(department_id, hire_date) 

-- 调优后查询
SELECT employee_id, employee_name, salary, hire_date
FROM dmhr.employee
WHERE department_id = 103 
  AND hire_date >= TO_DATE('2010-01-01', 'YYYY-MM-DD');

再次查看执行计划：

操作符变为“SSEK2”（索引范围扫描），附加信息出现刚刚创建的索引“IDX_EMP_DEPT_HIRE_SAL”，确认索引命中；
SSEK2结果集降至7，相比原来的结果集大幅降低。

场景二：子查询改写场景——避免重复计算开销

设计场景：需要查询薪资高于本部门平均的员工信息。

低效SQL及问题分析

-- 查询薪资高于本部门平均的员工信息
SELECT e.employee_id, e.employee_name, e.department_id, e.salary
FROM dmhr.employee e
WHERE e.salary > (
  SELECT AVG(salary) 
  FROM dmhr.employee 
  WHERE department_id = e.department_id
);

执行计划及问题拆解：

相关子查询中，外层查询每遍历1条员工数据，就需执行1次“部门平均薪资计算”

优化思路

将“相关子查询”改写为“非相关子查询+JOIN”：

先通过非相关子查询一次性计算所有部门的平均薪资，仅扫描EMPLOYEE表1次；
将子查询结果作为临时结果集，与员工表通过department_id关联，筛选出薪资高于部门平均的员工，避免重复计算。

调优SQL及效果验证

-- 非相关子查询计算各部门平均薪资（仅扫描1次表），再关联员工表
SELECT e.employee_id, e.employee_name, e.department_id, e.salary, dept_avg.avg_salary
FROM dmhr.employee e
JOIN (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM dmhr.employee
  GROUP BY department_id -- 按部门分组计算平均薪资
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

再次查看执行计划：

行数据处理长度减少，在遇到数据量大的情况该sql性能更好

场景三：用WITH减少表扫描场景——复用公共结果集

设计场景：统计2025年各部门调薪人数、平均调薪幅度、最高调薪金额。

低效SQL及问题分析

-- 统计2025年各部门调薪指标（多次扫描LOG_ADDSALARY表）
SELECT 
  d.department_id,
  d.department_name,
  (SELECT COUNT(*) FROM dmhr.log_addsalary WHERE department_id = d.department_id AND LOGTIME BETWEEN '2025-01-01' AND '2025-12-31') AS 调薪人数,
  (SELECT AVG(newsalary - oldsalary) FROM dmhr.log_addsalary WHERE department_id = d.department_id AND LOGTIME BETWEEN '2025-01-01' AND '2025-12-31') AS 平均调薪幅度,
  (SELECT MAX(newsalary - oldsalary) FROM dmhr.log_addsalary WHERE department_id = d.department_id AND LOGTIME BETWEEN '2025-01-01' AND '2025-12-31') AS 最高调薪金额
FROM dmhr.department d
WHERE d.department_id IN (SELECT DISTINCT department_id FROM dmhr.log_addsalary);

查询执行计划：

3个标量子查询+1个IN子查询，共导致LOG_ADDSALARY表被扫描4次（10万行数据时，单次扫描耗时0.8秒，总扫描耗时3.2秒）；
子查询与部门表关联时，存在大量重复过滤逻辑（相同的LOGTIME范围），未复用中间结果。

优化思路

用WITH将公共部分进行提取：

减少对表的扫描，将筛选后的调薪数据缓存为临时结果集；
后续统计调薪人数、幅度等指标时，直接复用缓存的结果集，避免重复扫描；

调优SQL及效果验证

-- 1. 用WITH提取2025年调薪公共数据（仅扫描1次LOG_ADDSALARY表）
WITH salary_log_2025 AS (
  SELECT 
    department_id,
    newsalary - oldsalary AS salary_increase  -- 提前计算调薪幅度
  FROM dmhr.log_addsalary
  WHERE LOGTIME BETWEEN '2025-01-01' AND '2025-12-31'
)
-- 2. 关联部门表统计，多次复用公共结果集
SELECT 
  d.department_id,
  d.department_name,
  COUNT(log.salary_increase) AS 调薪人数,
  ROUND(AVG(log.salary_increase), 2) AS 平均调薪幅度,  -- 保留2位小数优化显示
  MAX(log.salary_increase) AS 最高调薪金额
FROM dmhr.department d
LEFT JOIN salary_log_2025 log 
  ON d.department_id = log.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(log.salary_increase) > 0;  -- 过滤无调薪记录的部门

再次查看执行计划：

执行计划高度大幅降低，仅扫描LOG_ADDSALARY表1次.

注意: with提取公因子的改写方式比较简单，在改写的过程中，一定要注意逻辑是否等价，是否可以提取公因式，还有连接的顺序。

三、达梦SQL调优部分常用工具

ET工具

ET工具介绍和启用方法

ET是达梦数据库内置的SQL性能分析工具，可统计执行计划中各操作符的实际耗时（将优化器估算的代价转化为具体时间），帮助精准定位性能瓶颈操作，为SQL优化提供明确方向。该工具默认处于关闭状态，使用前需手动启用。

-- 启用方法
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);     --不建议使用
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);   ---会话级
-- 确认ET是否开启
select * from v$parameter t where NAME IN( 'MONITOR_SQL_EXEC','ENABLE_MONITOR');

注意: ET会对数据库性能有影响，使用完后记得关闭以保证数据库性能不受影响

ET工具的使用

在manager或者disql中，执行sql之后，会有一个对应的SQL执行号，在开启et功能的情况下，可以使用ET(SQL执行号)的方式，来获取SQL的执行耗时情况
manager点击执行号获取SQL的执行耗时情况：

disql中可以使用ET(SQL执行号)的方式获取SQL的执行耗时情况

结果列部分符号含义分别如下

符号	含义
OP	操作符
TIME(US)	时间开销，单位为微秒
PERCENT	执行时间占总时间百分比
RANK	执行时间的耗时排序
SEQ	执行计划的节点号
N_ENTER	进入次数

AUTOTRACE工具

AUTOTRACE是达梦数据库的SQL执行分析工具，通过SET AUTOTRACE命令指定不同模式，可实现“仅看执行计划”“执行SQL并监控”等不同分析需求
语法如下：

SET AUTOTRACE <OFF(默认值) | NL | INDEX | ON | TRACE | TRACEONLY>

各模式详情如下：

SET AUTOTRACE OFF
停止AUTOTRACE功能，按常规方式执行SQL语句（不额外打印执行计划或监控信息）。
SET AUTOTRACE NL
开启AUTOTRACE功能，不执行SQL语句；若执行计划中包含嵌套循环（NEST LOOP）操作，将仅打印该操作符相关的执行计划详情。
SET AUTOTRACE INDEX（或 ON）
开启AUTOTRACE功能，不执行SQL语句；若执行计划涉及表扫描操作，将打印表扫描方式、关联表名及所用索引信息（INDEX与ON模式功能完全等价）。
SET AUTOTRACE TRACE（常用）
开启AUTOTRACE功能，会实际执行SQL语句，同时打印执行计划及执行过程中的部分监控信息。
注意：需先将dm.ini中3个监控参数（ENABLE_MONITOR、MONITOR_SQL_EXEC、ENABLE_MONITOR_DMSQL）均设置为1（开启状态），该模式才生效。
与EXPLAIN的核心区别：EXPLAIN仅生成估算执行计划，不实际执行SQL，可能存在偏差；而TRACE模式获取的是服务器真实执行的计划（可能重用缓存计划或新生成计划），准确性更高。
SET AUTOTRACE TRACEONLY（常用）
开启AUTOTRACE功能，会实际执行SQL语句，同时打印执行计划及执行过程中的部分监控信息（与TRACE模式的参数依赖一致）。
与TRACE模式的核心区别：执行查询类SQL时，仅输出执行计划和监控信息，不打印查询结果集（避免大结果集占用输出资源）。

四、总结

在遇到慢查询时可以采用EXPLAIN+SQL查询执行计划
可以根据操作符以及估算的操作符代价、处理的记录行数和每行记录的字节数等内容思考优化方向
达梦数据库也提供了例如ET和AUTOTRACE等工具协助进行SQL性能分析
尽量避免出现全表扫描、回表等情况，若出现可以通过设计合适的索引并更新统计信息去尽可能进行规避。
SQL中也有可以通过使用修改子查询或用WITH将公共部分进行提取减少扫描次数或减少重复计算提升性能的情况。