达梦SQL 优化简介

目录

一、定位慢 SQL

（一）开启跟踪日志记录

1.跟踪日志记录配置

（二）通过系统视图查看

1.SQL 记录配置

2.查询方式

二、SQL分析方法

（一）执行计划

1.概述

2.查看执行计划

（二）常见操作符解读

1.NSET：结果集收集

2.PRJT：投影

3.SLCT：选择

4.AAGR：简单聚集

5.FAGR：快速聚集

6.HAGR：HASH 分组聚集

7.SAGR：流分组聚集

8.BLKUP：二次扫描 (回表)

9.CSCN：全表扫描

10.SSEK、CSEK、SSCN：索引扫描

11.NEST LOOP：嵌套循环连接

12.HASH JOIN：哈希连接

13.MERGE JOIN：归并排序连接

（三）ET 工具

1.功能的开启/关闭

（四）dbms_sqltune 工具

（五）存储过程调试

1.图形化界面工具进行调试

2.DMDBG 进行调试

三、SQL 语句优化

 （一）索引

（二）SQL 语句改写

1.优化 GROUP BY

2.用 UNION ALL 替换 UNION

3.用 EXISTS 替换 DISTINCT

4.多使用 COMMIT

 5.用WHERE子句替换HAVING子句

6.用TRUNCATE替换DELETE

7.用 EXISTS 替换 IN、用 NOT EXISTS 替换 NOT IN

8.半连接优化

9.反连接优化

（三）表设计优化

1.表类型选择

2.水平分区表

3.全局临时表

4.hint优化sql

一、定位慢 SQL

定位执行效率低的 SQL 语句是 SQL 优化的第一步。待优化的 SQL 可大致分为两类：SQL执行时间在十几秒到数十秒之间，但执行频率不高，此类 SQL 对数据库整体性能影响并不大，可以放到最后进行优化。

SQL 单独执行时间可能很快，在几百毫秒到几秒之间，但执行频率非常高，甚至达到每秒上百次，高并发下执行效率降低，很可能导致系统瘫痪，此类 SQL 是优化的首要对象。

（一）开启跟踪日志记录

跟踪日志文件是一个纯文本文件，以”dmsql_实例名_日期_时间命名.log”，默认生成在 DM 安装目录的 log 子目录下。跟踪日志内容包含系统各会话执行的 SQL 语句、参数信息、错误信息、执行时间等。跟踪日志主要用于分析错误和分析性能问题，基于跟踪日志可以对系统运行状态进行分析。

1.跟踪日志记录配置

（1）配置 dm.ini 文件，设置 SVR_LOG = 1 以启用 sqllog.ini 配置，该参数为动态参数，可通过调用数据库函数直接修改，如下所示：

SP_SET_PARA_VALUE(1,'SVR_LOG',1);

（2）配置数据文件目录下的 sqllog.ini 文件。

[dmdba@localhost DAMENG]$ cat sqllog.ini

BUF_TOTAL_SIZE  = 10240  #SQLs Log Buffer Total Size(K)(1024~1024000)

BUF_SIZE        = 1024   #SQLs Log Buffer Size(K)(50~409600)

BUF_KEEP_CNT    = 6      #SQLs Log buffer keeped count(1~100)

[SLOG_ALL]

    FILE_PATH       = ../log

    PART_STOR       = 0

    SWITCH_MODE     = 1

    SWITCH_LIMIT    = 100000

    ASYNC_FLUSH     = 0

    FILE_NUM        = 200

    ITEMS           = 0

    SQL_TRACE_MASK  = 2:3:23:24:25

    MIN_EXEC_TIME   = 0

    USER_MODE       = 0

    USERS           =

注意

为避免记录 SQL log 对服务器产生较大的影响，可以配置异步日志刷新（参数 ASYNC_FLUSH 设置为 1）。

（3）如果对 sqllog.ini 进行了修改，可通过调用以下函数即时生效，无需重启数据库，如下所示：

SP_REFRESH_SVR_LOG_CONFIG();

（二）通过系统视图查看

DM 数据库提供系统动态视图，可自动记录执行时间超过设定阈值的 SQL 语句。

1.SQL 记录配置

当 INI 参数 ENABLE_MONITOR=1、MONITOR_TIME=1 打开时，显示系统最近 1000 条执行时间超过预定值的 SQL 语句，默认预定值为 1000 毫秒。

以上两个参数可通过 SP_SET_PARA_VALUE 系统函数修改，通过 SF_GET_PARA_VALUE 系统函数查看当前值。

--修改参数值

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);

SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);

--查看参数值

select SF_GET_PARA_VALUE(1,'ENABLE_MONITOR');

select SF_GET_PARA_VALUE(1,'MONITOR_TIME');

注意

两个参数均为动态参数，可直接调用系统函数进行修改，无须重启数据库实例服务；
通过 SP_SET_PARA_VALUE 方式修改的参数值仅对当前会话以及新建会话生效，对其它已建立会话不生效。

2.查询方式

• 查询当前正在执行的会话信息。

SELECT * FROM (SELECT 'SP_CLOSE_SESSION('||SESS_ID||');' AS CLOSE_SESSION,

       DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) sql_exectime,

       TRX_ID,

       CLNT_IP,

       B.IO_WAIT_TIME AS IO_WAIT_TIME,

       SF_GET_SESSION_SQL(SESS_ID) FULLSQL,

       A.SQL_TEXT

 FROM V$SESSIONS a,V$SQL_STAT B WHERE STATE IN ('ACTIVE','WAIT')

 AND A.SESS_ID = B.SESSID

 )

SQL_TEXT 列记录的是部分 SQL 语句；FULLSQL 列存储了完整的执行 SQL 语句。

• 查询超过执行时间阈值的 SQL 语句。

可通过查询 V$LONG_EXEC_SQLS 系统视图获取结果：

SELECT * FROM V$LONG_EXEC_SQLS;

查询结果字段详细信息介绍如下表所示：

二、SQL分析方法

（一）执行计划

1.概述

简单来说，执行计划就是一条 SQL 语句在数据库中的执行过程或访问路径的描述。SQL 语言是种功能强大且非过程性的编程语言，比如以下这条 SQL 语句：

SELECT * FROM T1, T2 WHERE T1.ID = T2.ID AND T1.ID = 6;

开发人员只关心 SQL 语句能否返回 T1 与 T2 表的关联查询结果，不需要指定该 SQL 如何执行，也就是说不关心该 SQL 是先访问 T1 表还是先访问 T2 表。对于 SQL 来说，两种访问方式就是两个执行计划，查询优化器 (CBO) 将根据代价也就是开销来选择最优的执行计划。以如下 SQL 语句执行计划为例：

SELECT * FROM SYSOBJECTS;

1   #NSET2: [0, 1282, 396]

2     #PRJT2: [0, 1282, 396]; exp_num(17), is_atom(FALSE)

3       #CSCN2: [0, 1282, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

执行计划的每行即为一个计划节点，主要包含三部分信息。

• 第一部分 NEST2、PRJT2、CSCN2 为操作符及数据库具体执行了什么操作。
• 第二部分的三元组为该计划节点的执行代价，具体含义为[代价，记录行数，字节数]。
• 第三部分为操作符的补充信息。

例如：第三个计划节点表示操作符是 CSCN2（即全表扫描），代价估算是 0 ms，扫描的记录行数是 1282 行，输出字节数是 396 个。

各计划节点的执行顺序为：缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外。缩进最深的，最先执行；缩进深度相同的，先上后下。口诀：最右最上先执行。

#CSCN2: [1, 2, 12]; INDEX33555496(TEST)

 操作符，[代价，行数，字节数] 描述

2.查看执行计划

达梦数据库可通过两种方式查看执行计划。

方式一：通过 DM 数据库配套管理工具查看。

方式二：使用 explain 命令查看。

以下对两种查看方式进行介绍。

（1）管理工具查看执行计划

（2）在 DM 配套管理工具中，选中待查看执行计划的 SQL 语句，点击工具栏中的按钮，或使用快捷键 F9，即可查看执行计划。

（3）使用 explain 命令查看执行计划

（4）在待查看执行计划的 SQL 语句前加 explain 执行 SQL 语句即可查看预估的执行计划：

explain select * from sysobjects;

--执行计划

1   #NSET2: [1, 986, 396]

2     #PRJT2: [1, 986, 396]; exp_num(17), is_atom(FALSE)

3       #CSCN2: [1, 986, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

（5）使用 disql 命令行查真实执行计划

SQL> set autotrace traceonly

SQL> select * from sysobjects;

2348 rows got

1   #NSET2: [1, 2348->2348, 397]

2     #PRJT2: [1, 2348->2348, 397]; exp_num(17), is_atom(FALSE)

3       #CSCN2: [1, 2348->2348, 397]; SYSINDEXSYSOBJECTS(SYSOBJECTS)

Statistics-----------------------------------------------------------------

        0           data pages changed

        0           undo pages changed

        67          logical reads

        0           physical reads

        0           redo size

        364191      bytes sent to client

        155         bytes received from client

        2           roundtrips to/from client

        0           sorts (memory)

        0           sorts (disk)

        2348        rows processed

        0           io wait time(ms)

        2           exec time(ms)

重点关注 logical reads（逻辑读）和 physical reads（物理读）相应的指标值，并结合 rows processed 返回处理行数多少来分析。如果返回行数少（并且 bytes sent to client 总量不大），应尽可能减少 IO 开销，让执行计划选择正确的索引路径。

Sort(disk) 一般因排序（ hash join 发生归并、order by、group by 场景）区内存不足，如果数据库服务器物理内存充足，可以适当上调排序区内存，尽量避免操作刷盘，否则会影响执行性能。

（二）常见操作符解读

下面通过几个例子来介绍一些常见操作符。准备测试表及数据如下：

DROP TABLE T1;

DROP TABLE T2;

CREATE TABLE T1(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );

CREATE TABLE T2(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );

INSERT INTO T1

SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL FROM DUAL

CONNECT BY LEVEL<=10000;

INSERT INTO T2

SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL  FROM DUAL

CONNECT BY LEVEL<=10000;

CREATE INDEX IDX_C1_T1 ON T1(C1);

SP_INDEX_STAT_INIT(USER,'IDX_C1_T1');

1.NSET：结果集收集

EXPLAIN SELECT * FROM T1;

1   #NSET2: [1, 10000, 156]

2     #PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)

3       #CSCN2: [1, 10000, 156]; INDEX33556710(T1)

NSET 是用于结果集收集的操作符，一般是查询计划的顶层节点，优化工作中无需对该操作符过多关注，一般没有优化空间。

2.PRJT：投影

EXPLAIN SELECT * FROM T1;

1   #NSET2: [1, 10000, 156]

2     #PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)

3       #CSCN2: [1, 10000, 156]; INDEX33556710(T1)

PRJT 是关系的【投影】 (project) 运算，用于选择表达式项的计算。广泛用于查询，排序，函数索引创建等。优化工作中无需对该操作符过多关注，一般没有优化空间。

3.SLCT：选择

EXPLAIN SELECT * FROM T1 WHERE C2='TEST';

1   #NSET2: [1, 250, 156]

2     #PRJT2: [1, 250, 156]; exp_num(5), is_atom(FALSE)

3       #SLCT2: [1, 250, 156]; T1.C2 = TEST

4         #CSCN2: [1, 10000, 156]; INDEX33556717(T1)

SLCT 是关系的【选择】运算，用于查询条件的过滤。可比较返回结果集与代价估算中是否接近，如相差较大可考虑收集统计信息。若该过滤条件过滤性较好，可考虑在条件列增加索引。

4.AAGR：简单聚集

EXPLAIN SELECT COUNT(*) FROM T1 WHERE C1 = 10;

1   #NSET2: [0, 1, 4]

2     #PRJT2: [0, 1, 4]; exp_num(1), is_atom(FALSE)

3       #AAGR2: [0, 1, 4]; grp_num(0), sfun_num(1)

4         #SSEK2: [0, 1, 4]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

AAGR 用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算。

5.FAGR：快速聚集

EXPLAIN  SELECT MAX(C1) FROM T1;

1   #NSET2: [1, 1, 0]

2     #PRJT2: [1, 1, 0]; exp_num(1), is_atom(FALSE)

3       #FAGR2: [1, 1, 0]; sfun_num(1)

FAGR 用于没有过滤条件时，从表或索引快速获取 MAX、MIN、COUNT 值。

6.HAGR：HASH 分组聚集

EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C2;

1   #NSET2: [1, 100, 48]

2     #PRJT2: [1, 100, 48]; exp_num(1), is_atom(FALSE)

3       #HAGR2: [1, 100, 48]; grp_num(1), sfun_num(1)

4         #CSCN2: [1, 10000, 48]; INDEX33556717(T1)

HAGR 用于分组列没有索引只能走全表扫描的分组聚集，该示例中 C2 列没有创建索引。

7.SAGR：流分组聚集

EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C1;

1   #NSET2: [1, 100, 4]

2     #PRJT2: [1, 100, 4]; exp_num(1), is_atom(FALSE)

3       #SAGR2: [1, 100, 4]; grp_num(1), sfun_num(1)

4         #SSCN: [1, 10000, 4]; IDX_C1_T1(T1)

SAGR 用于分组列是有序的情况下，可以使用流分组聚集，C1 列上已经创建了索引，SAGR2 性能优于 HAGR2。

8.BLKUP：二次扫描 (回表)

EXPLAIN SELECT * FROM T1 WHERE C1=10;

1   #NSET2: [0, 1, 156]

2     #PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)

3       #BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)

4         #SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

BLKUP 先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列。

9.CSCN：全表扫描

EXPLAIN SELECT * FROM T1;

1   #NSET2: [1, 10000, 156]

2     #PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)

3       #CSCN2: [1, 10000, 156]; INDEX33556710(T1)

CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。全表扫描 I/O 开销较大，在一个高并发的系统中应尽量避免全表扫描。

10.SSEK、CSEK、SSCN：索引扫描

-- 创建所需索引

CREATE CLUSTER INDEX IDX_C1_T2  ON T2(C1);

CREATE  INDEX IDX_C1_C2_T1  ON T1(C1,C2);

SSEK

EXPLAIN SELECT * FROM T1 WHERE C1=10;

1   #NSET2: [0, 1, 156]

2     #PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)

3       #BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)

4         #SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

SSEK2 是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表。

CSEK

EXPLAIN SELECT * FROM T2 WHERE C1=10;

1   #NSET2: [0, 250, 156]

2     #PRJT2: [0, 250, 156]; exp_num(5), is_atom(FALSE)

3       #CSEK2: [0, 250, 156]; scan_type(ASC), IDX_C1_T2(T2), scan_range[10,10]

CSEK2 是聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作，如果 BLKUP 开销较大时，可考虑创建聚集索引。

SSCN

EXPLAIN SELECT C1,C2 FROM T1;

1   #NSET2: [1, 10000, 60]

2     #PRJT2: [1, 10000, 60]; exp_num(3), is_atom(FALSE)

3       #SSCN: [1, 10000, 60]; IDX_C1_C2_T1(T1)

SSCN 是索引全扫描，不需要扫描表。

11.NEST LOOP：嵌套循环连接

嵌套循环连接是最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。驱动表的行数就是循环的次数，将在很大程度上影响执行效率。

连接列是否有索引，都可以走 NEST LOOP，但没有索引，执行效率会很差，语句如下所示：

select /*+use_nl(t1,t2)*/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';

1   #NSET2: [17862, 24725, 296]

2     #PRJT2: [17862, 24725, 296]; exp_num(8), is_atom(FALSE)

3       #SLCT2: [17862, 24725, 296]; T1.C1 = T2.C1

4         #NEST LOOP INNER JOIN2: [17862, 24725, 296];

5           #SLCT2: [1, 250, 148]; T1.C2 = 'A'

6             #CSCN2: [1, 10000, 148]; INDEX33555594(T1)

7           #CSCN2: [1, 10000, 148]; INDEX33555595(T2)

可针对 T1 和 T2 的连接列创建索引，并收集统计信息，语句如下所示：

CREATE INDEX IDX_T1_C2  ON T1(C2);CREATE INDEX IDX_T2_C1  ON T2(C1);

DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T1_C2');

DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T2_C1');

再次查看执行计划可看出效率明显改善，代价有显著下降，语句如下所示：

select /*+use_nl(t1,t2)*/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';

1   #NSET2: [9805, 17151, 296]

2     #PRJT2: [9805, 17151, 296]; exp_num(8), is_atom(FALSE)

3       #SLCT2: [9805, 17151, 296]; T1.C1 = T2.C1

4         #NEST LOOP INNER JOIN2: [9805, 17151, 296];

5           #BLKUP2: [1, 175, 148]; IDX_T1_C2(T1)

6             #SSEK2: [1, 175, 148]; scan_type(ASC), IDX_T1_C2(T1), scan_range['A','A']

7           #CSCN2: [1, 10000, 148]; INDEX33555585(T2)

适用场景：

驱动表有很好的过滤条件

表连接条件能使用索引

结果集比较小

12.HASH JOIN：哈希连接

哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算。

select * from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';

1   #NSET2: [4, 24502, 296]

2     #PRJT2: [4, 24502, 296]; exp_num(8), is_atom(FALSE)

3       #HASH2 INNER JOIN: [4, 24502, 296];  KEY_NUM(1); KEY(T1.C1=T2.C1) KEY_NULL_EQU(0)

4         #SLCT2: [1, 250, 148]; T1.C2 = 'A'

5           #CSCN2: [1, 10000, 148]; INDEX33555599(T1)

6         #CSCN2: [1, 10000, 148]; INDEX33555600(T2)

哈希连接比较消耗内存如果系统有很多这种连接时，需调整以下 3 个参数：

13.MERGE JOIN：归并排序连接

归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并。

-- 对连接列创建索引CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2);

select /*+use_merge(t1 t2)*/t1.c1,t2.c1 from t1 inner join t2 on t1.c1=t2.c1 where t2.c2='b';

1   #NSET2: [13, 24725, 56]

2     #PRJT2: [13, 24725, 56]; exp_num(2), is_atom(FALSE)

3       #SLCT2: [13, 24725, 56]; T2.C2 = 'b'

4         #MERGE INNER JOIN3: [13, 24725, 56]; KEY_NUM(1); KEY(COL_0 = COL_0) KEY_NULL_EQU(0)

5           #SSCN: [1, 10000, 4]; IDX_C1_T1(T1)

6           #BLKUP2: [1, 10000, 52]; IDX_T2_C1(T2)

7             #SSCN: [1, 10000, 52]; IDX_T2_C1(T2)

（三）ET 工具

ET 工具是 DM 数据库自带的 SQL 性能分析工具，能够统计 SQL 语句执行过程中每个操作符的实际开销，为 SQL 优化提供依据以及指导。

1.功能的开启/关闭

ET 功能默认关闭，可通过配置 INI 参数中的 ENABLE_MONITOR=1、MONITOR_SQL_EXEC=1 开启该功能。

--两个参数均为动态参数，可直接调用系统函数进行修改

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

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);

SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);

注意

ET 功能的开启将对数据库整体性能造成一定影响，优化工作结束后尽量关闭该功能以提升数据库整体运行效率；DM数据库新版本的开启ET功能时需要设置：ENABLE_MONITOR = 1（默认打开）MONITOR_TIME = 1（默认打开）MONITOR_SQL_EXEC = 1（设置成1）。

（四）dbms_sqltune 工具

DBMS_SQLTUNE 包提供一系列实时 SQL 监控的方法。当 SQL 监控功能开启后，DBMS_SQLTUNE 包可以实时监控 SQL 执行过程中的信息，包括：执行时间、执行代价、执行用户、统计信息等情况。

使用前提：建议会话级开启参数 MONITOR_SQL_EXEC=1，而 MONITOR_SQL_EXEC 在达梦数据库中一般默认是 1，无需调整。

ALTER SESSION SET 'MONITOR_SQL_EXEC' = 1;

<执行待优化SQL>

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_EXEC_ID=>1213701) from dual;

dbms_sqltune 系统包相比 ET 功能更强大，能够获取 IO 操作量，查看真实执行计划，每个操作符消耗占比和相应的花费时间，还能看出每个操作符执行的次数，非常便于了解执行计划中瓶颈位置。

dbms_sqltune 功能远不止定位执行计划瓶颈，还拥有调优助手功能（建议性提示建某索引和收集某统计信息）。

（五）存储过程调试

在 DM 数据库中，我们可以利用 DM PL/SQL 进行存储过程及函数的编写。有时，我们编写的程序块中可能会有 BUG 导致编译失败，这种情况下就需要用到相关工具来进行调试。本章节主要介绍两种存储过程的调试方法。

1.图形化界面工具进行调试

在有图形化界面的情况下，我们可以利用管理工具来进行匿名块的调试。

（1）使用匿名块调试

开启系统包 DEBUG SYSTEM。【工具包】->DEBUG SYSTEM-> 右键->【启用】；

点击上方的【调试】按钮进入调试。

（2）创建测试存储过程，并进行调试

创建测试存储过程。

--当i到3的时候就会报除0错误DECLARE

--变量

aa int;BEGIN

--调试语句

for i in 1..3

loop

aa = 1 / (3-i);

print aa;

end loop;

END

进入调试，点击【进入】按钮，会依照代码逻辑进行分步调试，显示区域会显示当前变量执行结果。若遇到报错会终止调试，并显示报错内容，可根据报错内容进行代码修改。

2.DMDBG 进行调试

当我们没有图形界面，或者通过远程连接服务器的时候，我们也可以利用 DM 提供的命令行调试工具 dmdbg ，来完成同样的任务。

（1）登录 dmdbg。dmdbg 与 disql 同级目录，都在 dmdbms/bin 目录下，登录方式也与 disql 类似：

cd /dmdbms/bin

./dmdbg SYSDBA/SYSDBA@LOCALHOST:5236

--可以使用 help 命令查看参数

DEG> help

（2）引用存储过程。在 disql 中创建如下测试存储过程，然后在 DBG 中把 call TEST_DMDBG; 放到 SQL 内执行，如下所示：

--注意调试前确保已经开启系统包 DEBUG SYSTEM--登录disql，在disql中创建如下测试存储过程

create or replace procedure TEST_DEBUGas

aa int;begin

for i in 1..3

loop

aa = 1 / (3-i);

print aa;

end loop;end;

--登录dmdbg，把 call TEST_DMDBG; 放到 SQL 内执行

DBG> sql call TEST_DMDBG;

（3）添加断点。从头开始调试，将断点放到最开始的位置，可以在需要的行数打上断点，如下所示：

DBG> B 0

Breakpoint 1 at @dbg_main, line: 1@{call TEST_DMDBG;}

（4）开始调试。

DBG> r

Breakpoint 1,  line: 1@{call TEST_DMDBG;}

（5）进入循环里调试。

DBG> s

SYSDBA.TEST_DMDBG line: 5       @{   for i in 1..3}

DBG> s

SYSDBA.TEST_DMDBG line: 7       @{     aa = 1 / (3-i);}

（6）查看当前的堆栈。

DBG> bt

\#0      SYSDBA.TEST_DMDBG()     line: 7@{     aa = 1 / (3-i);}

\#1      @dbg_main       line: 1@{call TEST_DMDBG;}

（7）查看当前变量的数值。

--可以通过 P 变量名的方式输出打印

DBG> p aa

$3 = 0--当 i 到3的时候就会报除0错误

DBG> s

[TEST_DMDBG] 除0错误.error code=-6103--报错停止

三、SQL 语句优化

关于查询语句，有以下几点特征：

• 返回数据越多，语句执行时间越长；
• 分页是一个优化重点，order by 排序大小由结果集大小决定，过大会在临时表空间排序，性能降低；
• 一些语句会隐式排序，比如 uinon group by；
• buffer 过小，数据页频繁的换入换出。

关于优化 sql 语句：

• 通过各种手段减少 sql 执行过程中的 IO 代价，内存中的计算，临时表使用等；
• 表与表之间的关系，即关联条件之间的数据对应关系；
• 表数据量大小，对于 OLTP 是否满足小表驱动大表；
• sql 返回结果集多少，如果返回结果集少，sql 优化余地较大；
• 合理利用索引（组合索引）的特点，虽然维护索引也需要代价，但是对于查询来说，很多时候效果立竿见影。

关于访问和连接方法：

• 全表扫描访问；
• 索引扫描访问；
• 嵌套循环连接；
• 哈希连接；
• 归并连接；
• 半连接、反连接；
• n 张表至少有 n-1 次连接。

 （一）索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

索引结构：最常见的索引结构为 B*树索引

最顶层的为根节点，最底层的为叶子节点，中间层为内节点。实际使用当中一般不止 3 层（取决于数据量大小），除根节点以及叶子节点以外仅为内节点。对于一个 m 阶（本例中 m=2）的 B*树存储结构有以下几个特点：

• 每个结点最多有 m 个子结点。
• 除了根结点和叶子结点外，每个结点最少有 m/2（向上取整）个子结点。
• 如果根结点不是叶子结点，那根结点至少包含两个子结点。
• 所有的叶子结点都位于同一层。
• 每个结点都包含 k 个元素，这里 m/2 ≤ k < m，这里 m/2 向下取整。
• 每个节点中的元素从小到大排列。
• 每个元素左结点的值都小于或等于该元素，右结点的值都大于或等于该元素。
• 所有的非叶子节点只存储关键字信息。
• 所有的叶子结点中包含了全部元素的信息。
• 所有叶子节点之间都有一个链指针。

可以看出在该存储结构中查找特定数据的算法复杂度为 O(log2N)，查找速度仅与树高度有关。
    对于聚集索引叶子节点存储的元素是数据块即为整行数据，对于非聚集索引叶子节点存储的元素是索引字段的所对应的聚集索引的值或 rowid，如果需要获取其它字段信息需要根据聚集索引的值或 rowid 回表 (BLKUP) 进行查询。

索引适用范围：

在以下场景下可考虑创建索引：

• 仅当要通过索引访问表中很少的一部分行（1%~20%）。
• 索引可覆盖查询所需的所有列，不需额外去访问表。

注意

对于一个表来说索引并非越多越好，过多的索引将影响该表的 DML 效率。

存在下列情况将导致无法使用索引：

组合索引中，条件列中没有组合索引的首列。

• 条件列带有函数或计算。
• 索引排序是按照字段值进行排序的，字段值通过函数或计算后的值索引无法获取。
• 索引过滤性能不好时。

建立索引的原则：

• 建立唯一索引。唯一索引能够更快速地帮助我们进行数据定位；
• 为经常需要进行查询操作的字段建立索引；
• 对经常需要进行排序、分组以及联合操作的字段建立索引；
• 在建立索引的时候，要考虑索引的最左匹配原则（在使用 SQL 语句时，如果 where 部分的条件不符合最左匹配原则，可能导致索引失效，或者不能完全发挥建立的索引的功效）；
• 不要建立过多的索引。因为索引本身会占用存储空间；
• 如果建立的单个索引查询数据很多，查询得到的数据的区分度不大，则考虑建立合适的联合索引；
• 尽量考虑字段值长度较短的字段建立索引，如果字段值太长，会降低索引的效率。

（二）SQL 语句改写

DM 数据库针对 SQL 语句有以下常见几种改写方法：

1.优化 GROUP BY

提高 GROUP BY 语句的效率，可以在 GROUP BY 之前过滤掉不需要的内容。

--优化前

SELECT JOB,AVG(AGE) FROM TEMP

GROUP BY JOB HAVING JOB = 'STUDENT' OR JOB = 'MANAGER';

--优化后

SELECT JOB,AVG(AGE) FROM TEMP

WHERE JOB = 'STUDENT' OR JOB = 'MANAGER' GROUP BY JOB;

2.用 UNION ALL 替换 UNION

当 SQL 语句需要 UNION 两个查询结果集合时，这两个结果集合会以 UNION ALL 的方式被合并，在输出最终结果前进行排序。用 UNION ALL 替代 UNION, 这样排序就不必要了，效率就会因此得到提高。

注意

UNION 将对结果集合排序，这个操作会使用到 SORT_AREA_SIZE 这块内存，对于这块内存的优化也很重要；UNION ALL 将重复输出两个结果集合中相同记录，要从业务需求判断使用 UNION ALL 的可行性。

--优化前

SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20'

UNION

SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20';

 --优化后

SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20'

UNION ALL

SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20';

3.用 EXISTS 替换 DISTINCT

当 SQL 包含一对多表查询时，避免在 SELECT 子句中使用 DISTINCT，一般用 EXISTS 替换 DISTINCT 查询更为迅速。

--优化前

SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E

WHERE D.USER_ID= E.USER_ID;

--优化后

SELECT USER_ID,BILL_ID FROM USER_TAB1 D WHERE EXISTS(SELECT 1 FROM USER_TAB2 E WHERE E.USER_ID= D.USER_ID);

4.多使用 COMMIT

可以在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。
COMMIT 所释放的资源:

• 回滚段上用于恢复数据的信息；
• 被程序语句获得的锁；
• redo log buffer 中的空间；
• 为管理上述 3 种资源中的内部花销。

 5.用WHERE子句替换HAVING子句

避免使用 HAVING 子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、总计等操作，可以通过 WHERE 子句限制记录的数目。on、where、having 三个都可以加条件子句，其中，on 是最先执行，where 次之，having 最后。

• on 是先把不符合条件的记录过滤后才进行统计，在两个表联接时才用 on；
• 在单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，where 和 having 结果是一样的，但 where 比 having 快；
• 如果涉及到计算字段，where 的作用时间是在计算之前完成，而 having 是在计算后才起作用，两者的结果会不同；
• 在多表联接查询时，on 比 where 更早起作用。首先会根据各个表之间的关联条件，把多个表合成一个临时表后，由 where 进行过滤再计算，计算完再由 having 进行过滤。

6.用TRUNCATE替换DELETE

当删除表中的记录时，在通常情况下, 回滚段用来存放可以被恢复的信息。如果没有 COMMIT 事务，会将数据恢复到执行删除命令之前的状况；而当运用 TRUNCATE 时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。

注意

TRUNCATE 只在删除全表适用，TRUNCATE 是 DDL 不是 DML。

7.用 EXISTS 替换 IN、用 NOT EXISTS 替换 NOT IN

在基于基础表的查询中可能会需要对另一个表进行联接。在这种情况下, 使用 EXISTS (或 NOT EXISTS )通常将提高查询的效率。在子查询中，NOT IN 子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN 都是最低效的(要对子查询中的表执行一个全表遍历)，所以尽量将 NOT IN 改写成外连接( Outer Joins )或 NOT EXISTS。

--优化前

SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0

AND A.ID IN(SELECT ID FROM TEMP1 WHERE NAME ='TOM');

--优化后

SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0

AND EXISTS(SELECT 1 FROM TEMP1 WHERE A.ID= ID AND NAME='TOM');

8.半连接优化

半连接也是子查询的一种，查询只返回主表数据，子查询作为条件过滤使用。exists 关注是否有返回行，取决于关联列，in 关注是否存在过滤数据，在半连接改写中理解这点很重要。

优化改写：半连接改 join 。

--改写前，已下两种写法特征就是执行计划出现 semi 关键字

--写法一：

select EMPNO, ENAME, JOB, MGR, HIREDATE

  from emp2

 where deptno in (select deptno from dept2)

--写法二：

 select EMPNO, ENAME, JOB, MGR, HIREDATE

  from emp2

 where exists (select deptno from dept2 where dept2.deptno = emp2.deptno)

--改写优化--当子查询中部门表中部门编号不存在重复改写如下：

    select emp2.EMPNO,

           emp2.ENAME,

           emp2.JOB,

           emp2.MGR,

           emp2.HIREDATE

      from emp2inner join dept2

        on dept2.deptno = emp2.deptno

--若存在数据重复先根据关联列去重再关联select dept2.*

  from (select distinct deptno from emp2) emp2

 inner join dept2on dept2.deptno = emp2.deptno

9.反连接优化

同半连接一样，查询也只返回主表数据，通过 not in 和 not exists 过滤，再改写的过程中特别要注意反连接 not in 对空值敏感。

--ept2 deptno 列不存在空值时，以下两种写法等价，当 not in 存在空时，无数据行返回，因此 not exists 改写 not in 需要加上 not is null

select * from emp2 where deptno not in (select deptno from dept2);

select * from emp2 e where not exists (select * from dept2 d where d.deptno = e.deptno)

--not in、 not exists 改写 left join

select * from emp2 E  where deptno not in (select deptno from dept2 D)

--反连接驱动是 E 表，被驱动是 D 表，所以改写 left join ,not in 表示不在此范围，即 emp2 有的部门编号，dept2 没有

--左连接会将右表没有的内容用 NULL 表示，所以关联后取 d.deptno is null 过滤select e.*

  from emp2 e

  left join dept2 d

    on d.deptno = e.deptno

 where d.deptno is null

（三）表设计优化

表设计优化可以从三个方面入手：选择合适的表类型、设置分区表、设置全局临时表。

1.表类型选择

达梦数据库提供了三种表类型：行存储表、列存储表（HUGE）和堆表。运维人员可根据实际需求选择合适的表类型。

2.水平分区表

分区类型

• 范围(range)水平分区：对表中的某些列上值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上；
• 哈希（hash）水平分区：通过指定分区编号来均匀分布数据的一种分区类型，通过在 I/O 设备上进行散列分区，使得这些分区大小基本一致；
• 列表（list）水平分区：通过指定表中的某个列的离散值集，来确定应当存储在一起的数据。例如，可以对表上的 status 列的值在('A'，'H'，'O')放在一个分区，值在('B'，'I'，'P')放在另一个分区，以此类推；
• 多级分区表：按上述三种分区方法进行任意组合，将表进行多次分区，称为多级分区表。

分区优势

• 减少访问数据
• 操作灵活：可以操作分区 truncate、分区 drop、分区 add、分区 exchange

举例说明

select *  from range_part_tab

where deal_date >= TO_DATE('2019-08-04','YYYY-MM-DD')

and deal_date <= TO_DATE('2019-08-07','YYYY-MM-DD');

执行计划：

1 #NSET2:[24,18750,158]

2  #PRJT2:[24,18750,158];exp_num(6),is_atom(FALSE)

3   #PARALLEL:[24,18750,158];scan_type(GE_LE),key_num(0,1,1)

4    #SLCT2:[24,18750,158];[(RANGE_PART_TAB.DEAL_DATE >= var2 AND RANGE_PART_TAB.DEAL_DATE <= var4)]

5      #CSCN2:[73,500000,158];INDEX33555933(RANGE_PART_TAB)

--#PARALLEL：控制水平分区子表的扫描

• 对主表和所有子表都收集统计信息
• 对索引收集统计信息

注意

如果 SQL 中有可利用的索引，普通表也可能比分区表性能高。

3.全局临时表

当处理复杂的查询或事务时，由于在数据写入永久表之前需要暂时存储一些行信息或需要保存查询的中间结果，可能需要一些表来临时存储这些数据。DM 允许创建临时表来保存会话甚至事务中的数据。在会话或事务结束时，这些表上的数据将会被自动清除。

全局临时表类型

• 事务级-ON COMMIT DELETE ROWS
• 会话级-ON COMMIT PRESERVE ROWS

全局临时表优势

• 不同 session 数据独立
• 自动清理

举例说明

第一步：原始语句如下：

--T_1 视图（与 oracle 的 dblink 全表查询）

--T_1 视图的结构为

--(INIT_DATE int , BRANCH_NO int , FUND_ACCOUNT int , BUSINESS_FLAG int , remark varchar(32))

--T_2 表

--T_2 表的结构为

--(BRANCH_NO int,FUND_ACCOUNT int , prodta_no int,v_config_4662 varchar(32))

select a.init_date as oc_date,a.BRANCH_NO,a.FUND_ACCOUNT,a.BUSINESS_FLAG,a.remark,b.BRANCH_NO,b.FUND_ACCOUNT,b.prodta_no

from T_1 a,T_2 b

where init_date = 20181120

AND a.BRANCH_NO = b.BRANCH_NO

AND a.FUND_ACCOUNT = b.FUND_ACCOUNT

and instr(v_config_4662, ',' || b.prodta_no || ',')>0

and a.BUSINESS_FLAG in (2629,2630)

and nvl(a.remark,' ')not like '%实时TA%';

第二步：创建临时表 T1_20181122，将 T_1 视图中部分数据插入临时表中。

CREATE GLOBAL TEMPORARY TABLE "T1_20181122"

(init_date int,  BRANCH_NO int, FUND_ACCOUNT int,BUSINESS_FLAG int,remark varchar(32));

--插入dblink获取的数据到临时表

insert into T1_20181122

select *

from T_1 a

where init_date = 20181120

and a.BUSINESS_FLAG in (2629,2630)

and nvl(a.remark,' ')not like '%实时TA%';

第三步：语句改写。

select a.init_date as oc_date,a.BRANCH_NO,a.FUND_ACCOUNT,a.BUSINESS_FLAG,a.remark,b.BRANCH_NO,b.FUND_ACCOUNT,b.prodta_no

from T1_20181122 a, T_2 b

where a.BRANCH_NO = b.BRANCH_NO

AND a.FUND_ACCOUNT = b.FUND_ACCOUNT

and instr(v_config_4662, ',' || b.prodta_no || ',')>0;

执行计划：50 分钟 >>1 分钟。

--原语句执行计划

1 #NSET2:[11,1,1644]

1. #PRJT2:[11,1,1644];exp_num(41),is_atom(FALSE)

3 #HASH2 INNER JOIN:[11,1,1644];KEY_NUM(2);

4 #SLCT2:[0,1,270];exp11>0

5 #CSCN2:[0,1,270];INDEX33560908(T_HSOTCPRODCASHACCT as B)

6 #HASH RIGHT SEMI JOIN2:[10,380,1374];n_keys(1)

7 #CONST VALUE LIST:[0,2,30];row_num(2),col_num(1),

8 #SLCT2:[10,380,1374];(A.INIT_DATE = var4 AND NOT(exp11 LIKE '%实时TA%'))

9 #PRJT2:[10,1000,1374];exp_num(13),is_atom(FALSE)

10 #REMOTE SCAN:[0,0,0] HIS_FUNDJOUR@HS08HIS

--改写后执行计划

1   #NSET2: [1, 1, 124]

2     #PRJT2: [1, 1, 124]; exp_num(8), is_atom(FALSE)

3       #HASH2 INNER JOIN: [1, 1, 124];  KEY_NUM(2); KEY(B.BRANCH_NO=A.BRANCH_NO AND B.FUND_ACCOUNT=A.FUND_ACCOUNT) KEY_NULL_EQU(0, 0)

4         #SLCT2: [1, 1, 60]; exp11 > 0

5           #CSCN2: [1, 1, 60]; INDEX33555476(T_2 as B)

6         #CSCN2: [1, 1, 64]; INDEX33555478(T1_20181122 as A)

4.hint优化sql

当统计信息已收集，且索引也按照需求建立，sql 执行效率仍然不符合预期，可以考虑添加 hint 方式来进行优化。

--例如：多个单表查询通过 union 连接后组成一个视图，然后视图通过 where 过滤，过滤条件在每个单表中过滤性较好，且存在索引，通过视图过滤无法使用索引，执行时间55s。select * from dms.view_da_base where ahdm = '00005fe8-b171-4292-b776-b53c3fd65923';

使用 hint 修改参数 VIEW_FILTER_MERGING 的值对视图条件进行优化，当参数值取 1 时表示尽可能地进行视图条件合并。

select /*+ VIEW_FILTER_MERGING(1) */ * from dms.view_da_base where ahdm = '00005fe8-b171-4292-b776-b53c3fd65923';

当系统在运行过程中，出现慢 sql 需要紧急进行优化处理时，可以在数据库端通过 SF_INJECT_HINT 函数将 hint 与 sql 进行绑定对慢 SQL 进行优化。

--设置 INI 参数 ENABLE_INJECT_HINT 为 1

SP_SET_PARA_VALUE(1,'ENABLE_INJECT_HINT',1);   

 --计划添加 hint 后的 SQL

select    /*+ VIEW_FILTER_MERGING(1) */  * from dms.view_da_base where ahdm = '00005fe8-b171-4292-b776-b53c3fd65923';

---模糊匹配 sql 进行 hint 绑定

 SF_INJECT_HINT('select * from dms.view_da_base where    ,', 'VIEW_FILTER_MERGING(1)', 'VIEW_HINT', 'to testfunction of injecting hint', TRUE, TRUE);

注意

此优化方式不推荐作为常规优化方法使用，特定场景优化或应急处理时使用。

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台