SQL 优化

SQL 优化方法

定位慢 SQL

定位执行效率低的 SQL 语句是 SQL 优化的第一步。待优化的 SQL 可大致分为两类：

SQL 执行时间在十几秒到数十秒之间，但执行频率不高，此类 SQL 对数据库整体性能影响并不大，可以放到最后进行优化。
SQL 单独执行时间可能很快，在几百毫秒到几秒之间，但执行频率非常高，甚至达到每秒上百次，高并发下执行效率降低，很可能导致系统瘫痪，此类 SQL 是优化的首要对象。

开启跟踪日志记录

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

Copy
SP_SET_PARA_VALUE(1,‘SVR_LOG’,1);
（2）配置数据文件目录下的 sqllog.ini 文件。

Copy
[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 =
（3）如果对 sqllog.ini 进行了修改，可通过调用以下函数即时生效，无需重启数据库，如下所示：

Copy
SP_REFRESH_SVR_LOG_CONFIG();

查询方法

sqllog.ini 文件配置成功后可在 dmsql 指定目录下生成 dmsql 开头的 log 日志文件。

select * from t1 left join t2 on t1.c1=t2.c1 and t1.c1=999933 exectime:33816(ms);
可以通过正则表达式在 dmsql 日志文件中查找执行时间超过一定阈值的 SQL 语句。例如：查找执行时间超过 10 秒的 SQL 语句。
[1-9][0-9][0-9][0-9][0-9](ms)
如需进行更为系统全面的分析，可使用 DMLOG 工具 进行分类汇总。

通过系统视图查看

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

查询方式

（1）查询当前正在执行的会话信息。

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 语句。

（2）查询超过执行时间阈值的 SQL 语句。

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

SELECT * FROM V$LONG_EXEC_SQLS;

SQL 分析方法

执行计划

简单来说，执行计划就是一条 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)
 操作符，[代价，行数，字节数] 描述

查看执行计划

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

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

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

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

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

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

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

在待查看执行计划的 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)

常见操作符

1. NSET：结果集收集: NSET 是用于结果集收集的操作符，一般是查询计划的顶层节点，优化工作中无需对该操作符过多关注，一般没有优化空间。
2. PRJT：投影 PRJT 是关系的【投影】 (project) 运算，用于选择表达式项的计算。广泛用于查询，排序，函数索引创建等。优化工作中无需对该操作符过多关注，一般没有优化空间。
3. SLCT：选择 SLCT 是关系的【选择】运算，用于查询条件的过滤。可比较返回结果集与代价估算中是否接近，如相差较大可考虑收集统计信息。若该过滤条件过滤性较好，可考虑在条件列增加索引。
4. AAGR：简单聚集 AAGR 用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算。
5. FAGR：快速聚集 FAGR 用于没有过滤条件时，从表或索引快速获取 MAX、MIN、COUNT 值。
6. HAGR：HASH 分组聚集 HAGR 用于分组列没有索引只能走全表扫描的分组聚集，该示例中 C2 列没有创建索引。
7. SAGR：流分组聚集 SAGR 用于分组列是有序的情况下，可以使用流分组聚集，C1 列上已经创建了索引，SAGR2 性能优于 HAGR2
8. BLKUP：二次扫描 (回表) BLKUP 先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列。
9. CSCN：全表扫描 CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。全表扫描 I/O 开销较大，在一个高并发的系统中应尽量避免全表扫描。
10. SSEK、CSEK、SSCN：索引扫描 SSEK2 是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表。 CSEK2 是聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作，如果 BLKUP 开销较大时，可考虑创建聚集索引。 SSCN 是索引全扫描，不需要扫描表。
11. NEST LOOP：嵌套循环连接 嵌套循环连接是最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。驱动表的行数就是循环的次数，将在很大程度上影响执行效率。
12. HASH JOIN：哈希连接 哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算
13. MERGE JOIN：归并排序连接 归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并
    更多内容请查看达梦官方网站：https://eco.dameng.com