技术分享 | 国产达梦数据库DM8优化思路

本文为墨天轮数据库管理服务团队第52期技术分享，内容原创，作者为技术顾问muggle，如需转载请联系小墨（VX：modb666）并注明来源。

1、适用范围

达梦数据产品：DM8

2、表设计优化

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

2.1 表类型选择

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

表类型	描述	主要特征	适用场景
行存储表	行存储是以记录为单位进行存储的，数据页面中存储的是完整的若干条记录	1．按行存储 2．每个表都创建一个 B 树，并在叶子上存放数据	适用于高并发 OLTP 场景。
列存储表（HUGE）	列存储是以列为单位进行存储的，每一个列的所有行数据都存储在一起，而且一个指定的页面中存储的都是某一个列的连续数据。	1．按列存储 2．非事务型 HUGE 表：LOG NONE、LOG LAST、LOG ALL3．事务型 HUGE 表	适用于海量数据分析场景
堆表	堆表是指采用了物理 ROWID 形式的表，即使用文件号、页号和页内偏移而得到 ROWID 值，这样就不需要存储 ROWID 值，可以节省空间	1．数据页都是通过链表形式存储 2．可设置并发分支	并发插入性能较高

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

执行计划：

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 中有可利用的索引，普通表也可能比分区表性能高。

2.3 . 全局临时表

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

（1）全局临时表类型

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

（2）全局临时表优势

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

（3）举例说明

第一步：原始语句如下：

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

3、索引优化

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

索引结构：最常见的索引结构为 B*树索引，存储结构如下图所示：

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

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

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

索引适用范围：

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

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

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

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

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

例如对一张 10 万条记录的表进行条件查询，获取 5 万条数据，通过索引进行查找效率低于全表扫描，将放弃使用索引。

建立索引的原则：

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

4、SQL 语句改写

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

4.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;

4.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';

4.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.4. 多使用 COMMIT

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

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

4.5 用 WHERE 子句替换 HAVING 子句

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

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

4.6. 用 TRUNCATE 替换 DELETE

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

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

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

参考内容：DM8官方文档

---

墨天轮从乐知乐享的数据库技术社区蓄势出发，全面升级，提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境，并为数据库厂商提供中立的生态支持。

服务官网：https://www.modb.pro/service