浅谈达梦数据库性能优化思路

一、前言

通常SQL 调优作为业务上线前对数据库性能调优中最重要的一个环节，对数据库相关性能产生着重要的影响，影响SQL执行效率的原因有很多，比如：SQL编码不规范，表结构设计不合理，索引问题，慢SQL，统计信息不准确等， SQL调优的原则是最大程度减小系统资源开支，尤其减少I/O和CPU使用率、提高SQL命中率，获得最优的执行计划。

二、SQL 优化思路总结

定位慢 SQL

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

SQL 执行时间在十几秒到数十秒之间，但执行频率不高，此类 SQL 对数据库整体性能影响并不大，可以放到最后进行优化。

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

以下介绍两种定位慢 SQL 的方法，可记录下具体 SQL 语句以及对应执行时间，为后续 SQL 优化工作奠定基础。

• 开启跟踪日志记录

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

• 跟踪日志记录配置

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

SP_SET_PARA_VALUE(1,'SVR_LOG',1);

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

[dmdba@localhostDAMENG]$ 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

1. USERS =

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

SP_REFRESH_SVR_LOG_CONFIG();（各配置项详细说明请见达梦官网技术文档）

表设计优化

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

表类型选择

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

表类型

描述

主要特征

适用场景

行存储表

行存储是以记录为单位进行存储的，数据页面中存储的是完整的若干条记录

1．按行存储 2．每个表都创建一个 B 树，并在叶子上存放数据

适用于高并发 OLTP 场景。

列存储表（HUGE）

列存储是以列为单位进行存储的，每一个列的所有行数据都存储在一起，而且一个指定的页面中存储的都是某一个列的连续数据。

1．按列存储 2．非事务型 HUGE 表：LOG NONE、LOG LAST、LOG ALL3．事务型 HUGE 表

适用于海量数据分析场景

堆表

堆表是指采用了物理 ROWID 形式的表，即使用文件号、页号和页内偏移而得到 ROWID 值，这样就不需要存储 ROWID 值，可以节省空间

1．数据页都是通过链表形式存储 2．可设置并发分支

并发插入性能较高

• 水平分区表

（1）分区类型

• 范围(range)水平分区：对表中的某些列上值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上；

• 哈希（hash）水平分区：通过指定分区编号来均匀分布数据的一种分区类型，通过在 I/O 设备上进行散列分区，使得这些分区大小基本一致；

• 列表（list）水平分区：通过指定表中的某个列的离散值集，来确定应当存储在一起的数据。例如，可以对表上的 status 列的值在('A'，'H'，'O')放在一个分区，值在('B'，'I'，'P')放在另一个分区，以此类推；

• 多级分区表：按上述三种分区方法进行任意组合，将表进行多次分区，称为多级分区表。

（2）分区优势

• 减少访问数据

• 操作灵活：可以操作分区 truncate、分区 drop、分区add、分区 exchange

（3）举例说明

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

执行计划：

#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)]

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

全局临时表

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

（1）全局临时表类型

事务级-ON COMMIT DELETEROWS

会话级-ON COMMIT PRESERVEROWS

（2）全局临时表优势

不同 session 数据独立

自动清理

确定高负载的 SQL

在打开监控开关（ENABLE_MONITOR=1、MONITOR_TIME=1）后，可以通过查询动态视图 V$LONG_EXEC_SQLS或 V$SYSTEM_LONG_EXEC_SQLS 来确定高负载的 SQL 语句。前者显示最近 1000 条执行时间较长的 SQL 语句，后者显示服务器启动以来执行时间最长的 20 条 SQL 语句。例如：

SELECT *FROM V$LONG_EXEC_SQLS;

或者SELECT *FROM V$SYSTEM_LONG_EXEC_SQLS;

自动 SQL 调整

使用查询优化向导工具，输入需要进行调整的 SQL 语句，向导工具将在分析完执行计划后给出推荐索引的提示。用户只需按提示建立相应索引即可。

开发有效的 SQL 语句

SQL 语言是一种相当灵活的结构化查询语言。用户可以利用多种不同形式的查询语句完成相同的查询功能。为了使执行效率达到最优，用户需要参考以下原则以开发出有效的 SQL 语句：

避免使用 OR 子句

OR 子句在实际执行中会被转换为类似于 UNION 的查询。如果某一个 OR 子句不能利用上索引则会使用全表扫描造成效率低下，应避免使用。如果 OR 子句都是对同一列进行过滤，用户可以考虑使用 IN VALUE LIST 的过滤形式。如：

SELECT ... WHERE city = 'ShangHai' OR city = 'WuHan' OR city = 'BeiJing';

调整为SELECT ...WHERE city IN( 'ShangHai','WuHan','BeiJing');

避免使用困难的正则表达式

在 SQL 语言中，LIKE 关键字支持通配符匹配，含通配符的表达式被称为正则表达式。有的正则表达式可以自动优化为非匹配的。例如：a LIKE 'L%'可以优化为 a>='L' AND a <'M',这样就可以用到 a 上的索引。即使没有索引，转换后的比较也更快。再如：a LIKE 'LM_'可以转化为 a>='LM' AND a<'LN' AND a LIKE 'LM_'。虽然仍然包含着通配符匹配，但大大缩小了匹配的范围。

所谓困难的正则表达式是指开头和结尾都为通配符的正则表达式，如'L%'、'%L'，优化器没办法缩小它们的匹配范围，也不可能用到索引而必须使用全表扫描。因此要尽可能避免这样的正则表达式。

如果仅仅是开头为通配符，用户可以在列 a 上建立 REVERSE(a)这样一个函数索引，利用函数索引反转待匹配项从而使用函数索引进行范围扫描。

灵活使用伪表(SYSDUAL)

首先，可以利用伪表进行科学计算，执行语句SELECT 3*4 FROM SYSDUAL，则可以得到结果 12；

其次，在某些方面使用 SYSDUAL 可提高效率。例如：查询过程中要判断表 t1 中是否有满足 condition1 条件的记录存在，可执行以下语句：

SELECTCOUNT(*) INTO x FROM t1 WHERE condition1;

然后，根据变量 x 的取值来判断。但是当 t1 非常大时该语句执行速度很慢，而且由于不知道 SELECT 返回的个数，不能用 SELECT *代替。事实上这个查询可以利用伪表来完成：

SELECT 'A'INTO y FROM SYSDUAL WHERE EXISTS (SELECT1 FROM t1 WHERE condition1);

判断 y 值，如等于'A'则 T1 中有记录。调整后的语句执行速度明显比上一句高。

另外，在 DM 的语法里是可以省略 FROM 子句的，这时系统会自动加上 FROM SYSDUAL。因此前面的科学计算例子可以简化为 SELECT 3*4;

SELECT 项避免‘*’

除非用户确实要选择表中所有列，否则 SELECT*这种写法将让执行器背上沉重的负荷。因为每一列的数据不得不自下往上层层向上传递。不仅仅如此，如果用户查询的是列存储表，那么列存储所带来的 IO 优势将损耗殆尽。

任何时候，用户都要了解表结构和业务需求，小心地选择需要的列并一一给出名称，避免直接用 SELECT *。

避免功能相似的重复索引

索引并非越多越好。抛开优化器面对众多索引逐一试探所耗费的时间不谈，如果表上增删改操作频繁，那么索引的维护将会成为大麻烦，尤其是函数索引的计算开销更不能忽略。

使用 COUNT(*)统计结果行数

如果对单表查询 COUNT()且没有过滤条件，那么 DM 优化器会直接读取相关索引中存储的行数信息，加以回滚段中其他事务插入或删除元组的行数修正，迅速地给出最终结果而避免对实际数据的读取。相比之下，COUNT(列名)会对数据进行读操作，执行效率远低于 COUNT()。

即使查询中含有过滤条件，由于 DM 特有的批处理方式，COUNT()依旧快于其他写法。这是因为 COUNT()无需取得行的具体值而仅仅需要行数这一信息。

需要额外说明的是，COUNT()会将 NULL 值计算在内而 COUNT(列名)是不包含 NULL 值的，因此用户要结合应用场景决定是否可以使用 COUNT()。

使用 EXPLAIN 来查看执行计划

在查询语句或者插入、删除、更新语句前增加EXPLAIN 关键字，DM 将显示其执行计划而无需实际执行它。查阅 V$SQL_NODE_NAME 表中每个操作符的含义，用户可以很方便且直观地了解数据如何被处理及传递。如果启用了统计信息收集，那么对照执行计划和对动态视图 v$sql_node_history，v$sql_node_name 的查询结果，用户就可以知道在实际执行中每一个操作符执行的时间，进而找出性能瓶颈。

UNION 和 UNION ALL 的选择

UNION 和UNIONALL 的区别是前者会过滤掉值完全相同的元组，为此 UNION 操作符需要建立 HASH 表缓存所有数据并去除重复，当 HASH 表大小超过了 ini 参数指定的限制时还会做刷盘。

因此如果应用场景并不关心重复元组或者不可能出现重复，那么 UNION ALL 无疑优于 UNION。

优化 GROUP BY ... HAVING

GROUP BY 最常见的实现有 HASH 分组（HAGR）和排序分组（SAGR）。前者需要缓存中间结果；如果用户在 GROUP BY 的列上建立索引，那么优化器就会判断并可能使用上该索引，这时的GROUP BY 就会变为 SAGR。

HAVING 是分组后对结果集进行的过滤，如果过滤条件无关集函数操作，用户可以考虑将过滤条件放在 WHERE 而不是 HAVING 中。DM优化器会判断并自动转换部分等效于 WHERE 的 HAVING子句，但显式地给出最佳 SQL 语句会让优化器工作得更好。

索引选择

使用索引

目前 DM 提供的 HINT 为表索引的选择 HINT，它指示使用指定索引进行数据检索。

语法：表名 + INDEX + 索引名或/*+ INDEX (表名[,] 索引名){INDEX (表名[,] 索引名)} */一个语句中最多指定 8 个索引。在后一种语法格式中，如果查询中给出了表的别名那么必须使用别名。

假设表 t1 上 id 和 name 列上都存在着单列索引。

//数据准备

DROP TABLE T1 CASCADE;

CREATE TABLE T1 (ID INTEGER,NAMEVARCHAR(128));

CREATE INDEX IDX_T1_ID ON T1(ID);

CREATE INDEX IDX_T1_NAME ON T1(NAME);

例 1 在查询语句中指定索引。

SELECT * FROMt1 WHERE id > 2011 AND name < 'xxx';

如果 id 列上能过滤更多数据，建议指示用索引 idx_t1_id。

SELECT * FROMt1 index idx_t1_id WHERE id > 2011 AND name < 'xxx';

或

SELECT/*+INDEX(t1, idx_t1_id) */ * FROM t1 WHERE id > 2011 AND name < 'xxx';

例 2 当有多个索引时，要指定使执行计划最优的。

SELECT * FROMt1 WHERE id > 2011 AND name < 'xxx' ORDER BY name;

考虑到后面的 name 列排序操作，建议指示使用 name 列的索引 idx_t1_name，因为这样可以在执行过程中省略掉排序操作（执行计划中可以看出来），比使用 id 列索引代价小。

SELECT * FROMt1 INDEX idx_t1_name WHERE id > 2011 AND name < 'xxx' ORDER BY name;

或

SELECT /+INDEX(a idx_t1_name)/ * FROM t1 a WHERE id > 2011 AND name < 'xxx' ORDERBY name;

不使用索引

语法：/*+NO_INDEX (表名[,] 索引名) { NO_INDEX(表名[,] 索引名)} */

可以指定多个索引，则这些索引都不能被使用。一个语句中最多指定 8 个索引。

收集统计信息

统计信息对于 CBO（基于代价的优化器）生成执行计划具有直接影响。例如在嵌套循环连接（链接）中需要选择小表作为驱动表，两个关联表哪个是小表完全取决于统计信息中记录的数据量信息。此外，访问一个表是否要走索引，关联查询能否采用其它关联方式等都是 CBO 基于统计信息确定的。因此，统计信息的准确是生成最优执行计划的必要前提，DM 收集统计信息的方法分为手动收集和自动收集。

手动收集

--收集指定用户下所有表所有列的统计信息：

DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

--收集指定用户下所有索引的统计信息：

DBMS_STATS.GATHER_SCHEMA_STATS('usename',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');

--或 收集单个索引统计信息：

DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X');

--收集指定用户下某表统计信息：

DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

--收集某表某列的统计信息：

STAT 100 ON table_name(column_name);

自动收集

DM 数据库支持统计信息的自动收集，当全表数据量变化超过设定阈值后可自动更新统计信息。

--打开表数据量监控开关，参数值为 1 时监控所有表，2 时仅监控配置表

SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2);

--设置 SYSDBA.T 表数据变化率超过 15% 时触发自动更新统计信息

DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T','STALE_PERCENT',15);

--配置自动收集统计信息触发时机

SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'14:36', '2020/3/31',60,1);

更新统计信息

--更新已有统计信息

DBMS_STATS.UPDATE_ALL_STATS();

删除统计信息

--表

DBMS_STATS.DELETE_TABLE_STATS('模式名','表名','分区名',...);

--模式

DBMS_STATS.DELETE_SCHMA_STATS('模式名','','',...);

--索引

DBMS_STATS.DELETE_INDEX_STATS('模式名','索引名','分区表名',...);

--字段

DBMS_STATS.DELETE_COLUMN_STATS('模式名','表名','列名','分区表名',...);

** 达梦学习社区：**https://eco.dameng.com