DM数据库SQL优化初探-深圳市維司達科技有限公司

概述

SQL优化的基本准则包括限制结果集大小、‌减少数据库IO次数、‌合理使用内存、‌避免网络传输速度降低、‌合理利用覆盖索引、‌以及优化特定SQL语句的操作。‌

限制结果集大小：‌通过使用WHERE子句或TOP语句来减少返回的结果行数和字段列数，‌避免返回整个表的数据，‌特别是当表数据量很大时。‌这有助于减少磁盘IO，‌避免数据库缓冲区中的其他缓存数据被挤出，‌从而提高系统性能。‌
减少数据库IO次数：‌查询的数据越大，‌IO次数越多，‌因此应尽量减少查询的数据量。‌这包括避免不必要的全表扫描和排序，‌通过创建合适的索引来消除全表扫描和排序。‌
合理使用内存：‌查询的字段越多，‌消耗的内存越大，‌可能导致垃圾收集频繁，‌降低应用性能。‌因此，‌应尽量减少查询的字段数量，‌以减少内存消耗。‌
避免网络传输速度降低：‌查询的数据量越大，‌网络传输速度越慢。‌优化SQL语句以减少传输的数据量，‌可以提高应用响应速度。‌
合理利用覆盖索引：‌当查询的字段能够被索引覆盖时，‌可以避免额外的数据查找操作，‌从而提高查询性能。‌设计索引时，‌应考虑查询的字段和过滤条件，‌确保能够充分利用覆盖索引。‌
优化特定SQL语句的操作：‌对于执行时间较长、‌消耗资源较多的SQL语句，‌应分析其执行计划，‌找到开销较高的部分，‌并采取相应措施降低执行开销。‌这可能包括重新收集统计信息、‌改写SQL语句、‌创建或调整索引、‌固定执行计划、‌重新设计表或索引结构等

执行计划

执行计划是SQL语句的执行方式，由查询优化器（DM为CBO，基于代价）为语句设计的执行方式，交给执行器去执行。

操作符是SQL执行的基本单元，所有的SQL语句最终都是转换成一连串的操作符最后在服务器上执行，得到需要的结果，操作符也是读懂执行计划的基础。

简单介绍常见操作符

下面列出操作符的具体含义

CSCN2 ：聚集索引全扫描

SSCN2 ：二级索引全扫描

SSEK2 ：二级索引范围扫描，通过键值精准定位到范围或者单值

CSEK2 ：聚簇索引范围扫描，通过键值精准定位到范围或者单值

BLKUP2 ：根据二级索引的ROWID 回原表中取出全部数据

NSET2：结果集收集，通常无需优化。

PRJT2：投影操作，优化空间较小。

SLCT2：选择操作，可以关注执行计划中的估算结果集一列

AAGR2/FAGR2：聚集函数，主要出现在没有过滤条件或分组的情况下，聚集函数的计算。

HAGR2： HASH 分组聚集，分组列没有索引，只能走全表扫描，若该处代价较高，可以考虑对分组列添加索引。

SAGR2：有序的分组聚集，性能高于HAGR，可理解为HAGR加了索引后的表现。

表关联常见操作符

NEST LOOP FULL JOIN2 join_condition：连接条件嵌套循环全外连接
NEST LOOP INDEX JOIN2 join_condition：连接条件索引内连接
NEST LOOP INNER JOIN2 join_condition：连接条件嵌套循环内连接
NEST LOOP LEFT JOIN2 join_condition：连接条件嵌套循环左外连接
NEST LOOP SEMI JOIN2 join_condition：连接条件, (ANTI)：是否为反连接嵌套循环全外连接
HASH (INNER LEFT RIGHT SEMI) JOIN join_condition：连接条件哈希连接。
INDEX (INNER LEFT RIGHT SEMI) JOIN join_condition：连接条件索引连接
MERGE JOIN KEY：等值连接条件排序归并连接

Nest loop inner join
最基础的一种连接方式，将一张表的每一个值分别与另一张表的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。
两层嵌套循环结构，有驱动表和被驱动表之分，选定一张表作为驱动表，遍历驱动表中的每一行，根据连接条件去匹配第二张表中的行。驱动表的行数就是循环的次数，这个很大程度影响了执行效率。
需注意的问题，一选择小表作为驱动表，统计信息尽量准确，保证优化器选对驱动表；二大量的随机读，如果没有索引，随机读很致命，每次循环只能读一块，不能读多块。使用索引可以解决这个问题。
使用场景：

驱动表有很好的过滤条件。
表连接条件能使用索引。
结果集比较小。

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

一般没索引或用不上索引时会使用该连接方式。
选择小的表(或row source)做hash表。
只适用等值连接中的情形。
由于hash连接比较消耗内存，如果系统有很多这种连接时，需调整以下3个参数:
HJ_BUF_GLOBAL_SIZE
HJ_BUF_SIZE
HJ_BLK_SIZE

Index join
将一张表（T1）的数据拿出，去另外一张表（T2）上进行范围扫描找出需要的数据行。索引连接需要右表的连接列上存在索引。

Merge join
需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并。这里需要同时 SSCN 两条有序索引，将其中满足条件的值输出到结果集，效率比 NEST LOOP 要高很多，不考虑其他条件，如果 T1 和 T2 都很大的情况下跟 HASH JOIN 的效率相当（HASH JOIN是CSCN两张基表，MERGE JOIN 则 SSCN 相关索引）

简单案例构造和分析

CREATE TABLE T1(C1 INT,C2 CHAR);

CREATE TABLE T2(D1 INT,D2 CHAR);

CREATE INDEX IDX_T1_C1 ON T1(C1);

INSERT INTO T1 VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D');

INSERT INTO T2 VALUES(1,'A'),(2,'B'),(5,'C'),(6,'D');

Commit;

EXPLAIN SELECT A.C1+1,B.D2 FROM T1 A, T2 B WHERE A.C1 = B.D1;

==================================执行计划==============================

1 #NSET2: [1, 12, 56]

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

3 #NEST LOOP INDEX JOIN2: [1, 12, 56]

4 #CSCN2: [1, 4, 52]; INDEX33555676(T2 as B); btr_scan(1)

5 #SSEK2: [1, 3, 4]; scan_type(ASC), IDX_T1_C1(T1 as A), scan_range[B.D1,B.D1], is_global(0)

首先，确认执行计划的解读顺序，遵顼以下原则

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

具体顺序为

1. 步骤4： #CSCN2: [1, 4, 52]; INDEX33555676(T2 as B); btr_scan(1)

2.步骤5：#SSEK2: [1, 3, 4]; scan_type(ASC), IDX_T1_C1(T1 as A), scan_range[B.D1,B.D1], is_global(0)

3. 步骤3：NEST LOOP INDEX JOIN2（嵌套循环连接）

4. 步骤2：PRJT2（投影操作）

5. 步骤1：NSET2（结果集输出）

备注：CSCN2:[1, 4, 52]为三元组合，3个数字分别表示【估算代价，结果条数，行数据的长度】，如果是通过AUTOTRACE TRACE 查看执行计划时，结果条数会有两个结果，一个是预估值，一个是实际值，如果偏差较大说明该步骤可能会引起性能问题，需要注意。

ET工具

ET是达梦数据库自带的SQL 性能分析工具，能统计执行计划中，每个操作符的时间花费（将代价显示为具体的时间），从而定位到有性能问题的操作，指导我们去优化。ET是默认关闭的，因此在使用ET之前，需要先开启。

开启和关闭ET的方法

开启ET功能

开启ET功能，要尽可能的只开启会话级参数（MONITOR_SQL_EXEC）：

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

其中，ENABLE_MONITOR，动态参数(系统级)，MONITOR_SQL_EXEC，动态参数(会话级)。因ET会对数据库性能有影响，使用完后记得关闭以保证数据库性能不受影响。

ET说明

ET的应用说明

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

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

AUTOTRACE 工具

AUTOTRACE工具主要用于执行计划和统计信息的跟踪。

AUTOTRACE语法说明

语法如下：

SET AUTOTRACE TRACE（常用，重要）

SET AUTOTRACE TRACE 时，开启 AUTOTRACE 功能，执行语句，打印执行计划。此功能与服务器 EXPLAIN 语句的区别在于，EXPLAIN 只生成执行计划，并不会真正执行SQL 语句，因此产生的执行计划有可能不准。而 TRACE 获得的执行计划，是服务器实际执行的计划。

select /*+ENABLE_HASH_JOIN(0)*/* from DMHR.EMPLOYEE a,dmhr.DEPARTMENT b where a.manager_id=b.MANAGER_ID;

SET AUTOTRACE TRACEONLY（常用，重要）

SET AUTOTRACE TRACEONLY 时，开启 AUTOTRACE 功能，执行语句，打印执行计划。此功能与 TRACE 区别在于对于查询语句集不打印结果集。

select /*+ENABLE_HASH_JOIN(0)*/* from DMHR.EMPLOYEE a,dmhr.DEPARTMENT b where a.manager_id=b.MANAGER_ID;

统计信息

基于代价的优化器CBO可以根据统计信息选择最佳的查询执行计划。统计信息准确与否会影响SQL执行效率。
DM支持自动、手动的方式采集统计信息。
需要注意的是,采集统计信息时会消耗系统资源,需要在业务空闲的时间段谨慎执行。

采集方式

自动采集

在 INI 参数 AUTO_STAT_OBJ 为 1 或 2 前提下，执行 SP_CREATE_AUTO_STAT_ TRIGGER 过程实现自动收集。

例如以下SQL表示从2023/8/10开始,每天22:20自动执行统计信息收集。

SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'22:20', '2023/8/10',0,1);

手动采集

通过存储过程收集

#收集TEST_DB.TEST_TB的索引统计信息 SP_TAB_INDEX_STAT_INIT ('TEST_DB', 'TEST_TB'); #对TEST_DB的IDX_1索引收集 SP_INDEX_STAT_INIT ('TEST_DB', 'IDX_1'); #对TEST_DB.TEST_TB的ID字段收集 SP_COL_STAT_INIT('TEST_DB', 'TEST_TB","ID"); #对TEST_DB.TEST_TB所有字段收集 SP_TAB_COL_STAT_INIT('TEST_DB', 'TEST_TB'); #指定采用率对TEST_DB.TEST_TB所有字段收集 SP_STAT_ON_TABLE_COLS('TEST_DB', 'TEST_TB',10); #对某张表和索引生成统计信息 SP_TAB_STAT_INIT('TEST_DB', 'TEST_TB');

通过stat语法收集

对字段按百分比收集 STAT 30 ON TEST_DB.TEST_TB (ID); STAT 30 ON TEST_DB.TEST_TB (PID,NAME); 对索引按百分比收集 STAT 50 ON INDEX PURCHASING.S1; 对表生成统计信息 STAT ON SYS.SYSOBJECTS;

执行计划相关参数

动态采集参数OPTIMIZER_DYNAMIC_SAMPLING
当统计信息不可用时是否启用动态统计信息。取值范围:0-12。0：不启用；1-10：启用，采用率 10%-100%；11：启用，由优化器确定采样率（0.1%-99.9%）；12：同 11，但收集的结果会持久化保存。默认值为0

HINT

INI 参数hint

可以通过 HINT 方式对 INI 参数的值进行语句级的指定。语句中的 HINT 对 INI参数值的设置优先级高于 INI 文件中参数值的设置。通过 HINT 方式只会修改 INI 参数的在本会话中的值，不会改变它在 INI 文件中的值。
支持使用 HINT 的 INI 参数可通过 V$HINT_INI_INFO 动态视图查询。支持 HINT 的INI 参数分为两类：一是 HINT_TYPE 为―OPT，表示分析阶段使用的参数；二是 HINT_TYPE
为―EXEC，表示运行阶段使用的参数，运行阶段使用的参数对于视图无效。
例如：
SELECT /+ENABLE_HASH_JOIN(1)/ * FROM T1,T2 WHERE C1=D1;
上面的语句中使用了HINT，指明在执行此SQL时参数ENABLE_HASH_JOIN被置为1。

索引hint

使用特定索引
语法：
表名 + INDEX + 索引名或 /*+ INDEX (表名[,] 索引名) {INDEX (表名[,] 索引名)}/
示例：
SELECT * FROM T1 INDEX IDX_T1_ID WHERE ID > 2011 AND NAME < 'XXX';
或
SELECT /+INDEX(T1, IDX_T1_ID)/ * FROM T1 WHERE ID > 2011 AND NAME < 'XXX';
不使用特定索引
/+ NO_INDEX (表名[,] 索引名) { NO_INDEX (表名[,] 索引名)} */

连接方式hint

可以通过指定两个表间的连接方法来检测不同连接方式的查询效率，指定的连接可
能由于无法实现或代价过高而被忽略。如果连接方法提示中的表名(别名)或索引名无效也会
被自动忽略。

USE_HASH
强制两个表间使用指定顺序的哈希连接，例如：
EXPLAIN SELECT /*+ USE_HASH(T1, T2) */ * FROM T1, T2 WHERE T1.ID = T2.ID;
NO_USE_HASH
强制两个表间不能使用指定顺序的哈希连接，例如：
EXPLAIN SELECT /*+ NO_USE_HASH(T1, T2) */ * FROM T1, T2 WHERE T1.ID = T2.ID;
NO_USE_HASH(T1, T2)表示不允许 T1 作为左表， T2 作为右表的哈希连接，但 T1 作
为右表的哈希连接还是允许的。
USE_NL
强制两个表间使用嵌套循环连接，例如：
EXPLAIN SELECT /*+ USE_NL(A, B) */ * FROM T1 A, T2 B WHERE A.ID = B.ID;
NO_USE_NL
强制两个表间不能使用嵌套循环连接，例如：
EXPLAIN SELECT /*+ NO_USE_NL(A, B) */ * FROM T1 A, T2 B WHERE A.ID = B.ID;
USE_NL_WITH_INDEX
当连接情况为左表+右表索引时，强制两个表间使用索引连接，例如：
EXPLAIN SELECT /*+ USE_NL_WITH_INDEX(T1, IDX_T2_ID) */ * FROM T1, T2 WHERE
T1.ID = T2.ID;
NO_USE_NL_WITH_INDEX
当连接情况为左表+右表索引时，强制两个表间不能使用索引连接，例如：
EXPLAIN SELECT /*+ NO_USE_NL_WITH_INDEX(T1, IDX_T2_ID) */ * FROM T1, T2 WHERE T1.ID
= T2.ID;
USE_MERGE
强制两个表间使用归并连接。归并连接所用的两个列都必须是索引列。例如：
EXPLAIN SELECT /*+ USE_MERGE(T1,T2) */ * FROM T1, T2 WHERE T1.ID = T2.ID AND
T1.ID < 1 AND T2.ID < 1;
当连接类型为外连接时，无法使用归并连接，此时即使指定 USE_MERGE，也不起作用。
NO_USE_MERGE
强制两个表间不能使用归并连接，例如：
EXPLAIN SELECT /+ NO_USE_MERGE(T1,T2)/ * FROM T1, T2 WHERE T1.ID = T2.ID AND T1.ID > 1 AND T2.ID > 1;