0、概述
在数据库的使用中，数据库的性能往往是至关重要的问题，而数据库的性能问题最终基本都要涉及到SQL优化。本文就将详细介绍一些达梦中SQL优化的知识。
1、执行计划详解
1.1、执行计划解读
无论是什么数据库，一般SQL优化我们都需要去查看SQL的执行计划，了解SQL具体是慢在哪里，才知道从哪里开始优化。
那么什么是执行计划呢？
执行计划是SQL语句的执行方式，由查询优化器为语句设计的执行方式，交给执行器去执行。在达梦中我们可以在SQL命令行使用EXPLAIN可以打印出语句的执行计划。
例如下面就是一个最基本的执行计划：
SQL> explain select * from SYSOBJECTS;
1 #NSET2: [0, 1531, 396]
2 #PRJT2: [0, 1531, 396]; exp_num(17), is_atom(FALSE)
3 #CSCN2: [0, 1531, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)
从上面的执行计划中我们可以看到哪些信息呢？
首先，一个执行计划由若干个计划节点组成，如上面的1、2、3。
然后我们看到，每个计划节点中包含操作符(CSCN2)和它的代价([0, 1711, 396])等信息。
代价由一个三元组组成[代价,记录行数,字节数]。
代价的单位是毫秒，记录行数表示该计划节点输出的行数，字节数表示该计划节 点输出的字节数。
拿上面第三个计划节点举例:操作符是CSCN2即全表扫描，代价估算是0ms，扫描的记录行数是1711行，输出字节数是396个。
1.2、执行计划操作符介绍
达梦中执行计划涉及到的一些主要操作符有：
CSCN ：基础全表扫描(a)，从头到尾，全部扫描
SSCN ：二级索引扫描(b), 从头到尾，全部扫描
SSEK ：二级索引范围扫描(b) ，通过键值精准定位到范围或者单值
CSEK ：聚簇索引范围扫描© ，通过键值精准定位到范围或者单值
BLKUP ：根据二级索引的ROWID 回原表中取出全部数据(b + a)
接下来我们结合实例来介绍下这些操作符：
–准备测试表和数据：
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
2
3
4
5
6
7
–NSET:收集结果集
说明：用于结果集收集的操作符， 一般是查询计划的顶层节点。
SQL> 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]; INDEX33555571(T1)
1
2
3
4
5
–PRJT：投影
说明：关系的“投影”(project)运算，用于选择表达式项的计算;广泛用于查询，排序，函数索引创建等。
SQL> 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]; INDEX33555571(T1)
1
2
3
4
5
–SLCT：选择
说明：关系的“选择” 运算，用于查询条件的过滤。
SQL> 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]; INDEX33555571(T1)
1
2
3
4
5
6
–AAGR：简单聚集
说明：用于没有group by的count，sum，age，max，min等聚集函数的计算。
SQL> 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]
1
2
3
4
5
6
–FAGR：快速聚集
说明：用于没有过滤条件时从表或 索引快速获取 MAX/MIN/COUNT值，DM数据库是世界上单表不带过滤条件下取COUNT值最快的数据库。
SQL> EXPLAIN SELECT COUNT(*) 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),
SQL> EXPLAIN SELECT MAX(C1) FROM T1;
1 #NSET2: [1, 1, 4]
2 #PRJT2: [1, 1, 4]; exp_num(1), is_atom(FALSE)
3 #FAGR2: [1, 1, 4]; sfun_num(1), IDX_C1_T1
1
2
3
4
5
6
7
8
9
10
11
–HAGR：HASH分组聚集
说明：用于分组列没有索引只能走全表扫描的分组聚集，C2列没有创建索引。
SQL> EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C2;
1 #NSET2: [2, 100, 48]
2 #PRJT2: [2, 100, 48]; exp_num(1), is_atom(FALSE)
3 #HAGR2: [2, 100, 48]; grp_num(1), sfun_num(1);
4 #CSCN2: [1, 10000, 48]; INDEX33555571(T1)
1
2
3
4
5
6
–SAGR：流分组聚集
说明：用于分组列是有序的情况下可以使用流分组聚集，C1上已经创建了索引，SAGR2性能优于HAGR2。
SQL> EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C1;
1 #NSET2: [2, 100, 4]
2 #PRJT2: [2, 100, 4]; exp_num(1), is_atom(FALSE)
3 #SAGR2: [2, 100, 4]; grp_num(1), sfun_num(1)
4 #SSCN: [1, 10000, 4]; IDX_C1_T1(T1)
1
2
3
4
5
6
–BLKUP：二次扫描
说明：先使用2级别索引定位，再根据表的主键、聚集索引、 rowid等信息定位数据行。
SQL> 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]
1
2
3
4
5
6
–CSCN：全表扫描
说明：CSCN2是CLUSTER INDEX SCAN的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。在一个高并发的系统中应尽量避免全表扫描。
SQL> 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]; INDEX33555571(T1)
1
2
3
4
5
–SSEK、CSEK、SSCN：索引扫描
说明：
SSEK2是二级索引扫描即先扫描索引，再通过主键、聚集索引、ROWID等信息去扫描表；
CSEK2是聚集索引扫描只需要扫描索引，不需要扫描表；
SSCN是索引全扫描，不需要扫描表。
–SSEK
SQL> 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]
1
2
3
4
5
6
–CSEK
SQL> CREATE CLUSTER INDEX IDX_C1_T2 ON T2(C1);
SQL> 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]
1
2
3
4
5
6
–SSCN
SQL> CREATE INDEX IDX_C1_C2_T1 ON T1(C1,C2);
SQL> 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)
1
2
3
4
5
6
7
至此，主要的执行计划操作符就介绍的差不多了，更多的操作符解释可以参考：DM7系统管理员手册附录4《执行计划操作符》。
2、表连接详解
2.1、嵌套循环连接
NEST LOOP原理:
两层嵌套循环结构，有驱动表和被驱动表之分。 选定一张表作为驱动表，遍历驱动表中的每一行，根据连接条件去匹配第二 张表中的行。驱动表的行数就是循环的次数，这个很大程度影响了执行效率。
需注意的问题:
选择小表作为驱动表。统计信息尽量准确，保证优化器选对驱动表。
大量的随机读。如果没有索引，随机读很致命，每次循环只能读一块， 不能读多块。使用索引可以解决这个问题。
使用场景：
驱动表有很好的过滤条件。
表连接条件能使用索引。
结果集比较小。
例子：
过滤列和连接列都没有索引，也可以走nest loop，但是该计划很差。如下面的计划代价就很大。
SQL> explain select /*+use_nl(t1,t2)*/*
from t1 inner join t2
on t1.c1=t2.c1 where t1.c2='A';
1 #NSET2: [17862, 24950, 296]
2 #PRJT2: [17862, 24950, 296]; exp_num(8), is_atom(FALSE)
3 #SLCT2: [17862, 24950, 296]; T1.C1 = T2.C1
4 #NEST LOOP INNER JOIN2: [17862, 24950, 296];
5 #SLCT2: [1, 250, 148]; T1.C2 = 'A'
6 #CSCN2: [1, 10000, 148]; INDEX33555571(T1)
7 #CSCN2: [1, 10000, 148]; IDX_C1_T2(T2)
1
2
3
4
5
6
7
8
9
10
11
我们可以加上索引来进行优化：
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');
1
2
优化后执行计划：
SQL> explain select /*+use_nl(t1,t2)*/*
from t1 inner join t2
on t1.c1=t2.c1 where t1.c2='A';
1 #NSET2: [17821, 24950, 296]
2 #PRJT2: [17821, 24950, 296]; exp_num(8), is_atom(FALSE)
3 #SLCT2: [17821, 24950, 296]; T1.C1 = T2.C1
4 #NEST LOOP INNER JOIN2: [17821, 24950, 296];
5 #BLKUP2: [0, 250, 148]; IDX_T1_C2(T1)
6 #SSEK2: [0, 250, 148]; scan_type(ASC), IDX_T1_C2(T1), scan_range['A','A']
7 #CSCN2: [1, 10000, 148]; IDX_C1_T2(T2)
1
2
3
4
5
6
7
8
9
10
11
2.2、哈希连接
hash join原理：
使用较小的Row source 作为Hash table和Bitmap， 而第二个row source被hashed，根据bitmap与第一个row source生成的hash table 相匹配，bitmap查找的速度极快。
hash join特点：
一般没索引或用不上索引时会使用该连接方式。
选择小的表(或row source)做hash表。
只适用等值连接中的情形。
由于hash连接比较消耗内存，如果系统有很多这种连接时，需调整以下3个参数:
HJ_BUF_GLOBAL_SIZE
HJ_BUF_SIZE
HJ_BLK_SIZE
例子：
SQL> explain select *
from t1 inner join t2
on t1.c1=t2.c1 where t1.c2='A';
1 #NSET2: [1, 24950, 296]
2 #PRJT2: [1, 24950, 296]; exp_num(8), is_atom(FALSE)
3 #HASH2 INNER JOIN: [1, 24950, 296]; KEY_NUM(1);
4 #NEST LOOP INDEX JOIN2: [1, 24950, 296]
5 #ACTRL: [1, 24950, 296];
6 #BLKUP2: [0, 250, 148]; IDX_T1_C2(T1)
7 #SSEK2: [0, 250, 148]; scan_type(ASC), IDX_T1_C2(T1), scan_range['A','A']
8 #CSEK2: [1, 2, 0]; scan_type(ASC), IDX_C1_T2(T2), scan_range[T1.C1,T1.C1]
9 #CSCN2: [1, 10000, 148]; IDX_C1_T2(T2)
1
2
3
4
5
6
7
8
9
10
11
12
13
需要注意：如果不是等值连接则会走nest loop连接。
SQL> explain select *
from t1 inner join t2
on t1.c1 > t2.c1 where t1.c2='A';
1 #NSET2: [2, 125000, 296]
2 #PRJT2: [2, 125000, 296]; exp_num(8), is_atom(FALSE)
3 #NEST LOOP INDEX JOIN2: [2, 125000, 296]
4 #BLKUP2: [0, 250, 148]; IDX_T1_C2(T1)
5 #SSEK2: [0, 250, 148]; scan_type(ASC), IDX_T1_C2(T1), scan_range['A','A']
6 #CSEK2: [2, 375, 0]; scan_type(ASC), IDX_C1_T2(T2), scan_range(null2,T1.C1)
1
2
3
4
5
6
7
8
9
10
2.3、排序合并连接
MERGE SORT的特点:
无驱动表之分，随机读很少。
两个表都需要按照连接列排序，需要消耗大量的cpu和额外的内存。
应用场景：
通常情况下，merge sort join需要消耗大量的cpu和内存，效率都不会太高。如果存在相关索引可以消除sort，那么CBO可能会考虑该连接方式。
例子：
SQL> explain 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: [4, 24950, 56]
2 #PRJT2: [4, 24950, 56]; exp_num(2), is_atom(FALSE)
3 #SLCT2: [4, 24950, 56]; T2.C2 = 'b'
4 #MERGE INNER JOIN3: [4, 24950, 56];
5 #SSCN: [1, 10000, 4]; IDX_C1_T1(T1)
6 #CSCN2: [1, 10000, 52]; IDX_C1_T2(T2)
1
2
3
4
5
6
7
8
9
3、查询转换
3.1、什么是查询转换？
查询转换是优化器自动做的，在生成执行计划之前，等价改写 查询语句的形式，以便提升效率和产生更好的执行计划。它决 定是否重写用户的查询，常见的转换有谓词传递、视图拆分、 谓词推进、关联/非关联子查询改写等。
了解优化器查询转换的特性，会帮助我们更好的看懂执行计划， 也会对我们优化sql起到指导的作用。优化器的查询转换有很 多限制条件，我们可以根据类似的原理举一反三，进行手工的 sql改写，从到得到更好的执行计划。
3.2、谓词转换
什么是谓词转换呢？大致就是指我们可以根据A=B,B=C,可以推导出A=C的形式。如下面的SQL：
select * from t1 inner join t2
on t1.c2=t2.c2 where t1.c1=100
and t2.c1=t1.c1
1
2
3
CBO经过谓词转换后，实际执行的语句其实是：
select * from t1 inner join t2
on t1.c2=t2.c2 where t1.c1=100
and t2.c1=t1.c1
and t2.c1=100 –-谓词传递
1
2
3
4
3.3、视图拆分
我们先创建一个视图：
SQL> create or replace view v_t1 as select t1.c1+t2.c1 as c11,
t2.c2,t1.c1 from t1,t2
2 3 where t1.c2=t2.c2;
操作已执行
1
2
3
4
我们看看视图定义里面SQL的执行计划：
SQL> explain select t1.c1+t2.c1 as c11,
t2.c2,t1.c1 from t1,t2
where t1.c2=t2.c2;
1 #NSET2: [5, 980099, 104]
2 #PRJT2: [5, 980099, 104]; exp_num(3), is_atom(FALSE)
3 #HASH2 INNER JOIN: [5, 980099, 104]; KEY_NUM(1);
4 #SSCN: [1, 10000, 52]; IDX_C1_C2_T1(T1)
5 #CSCN2: [1, 10000, 52]; INDEX33555575(T2)
1
2
3
4
5
6
7
8
9
而我们查询使用到视图时：
SQL> explain select a.c11,b.c2 from v_t1 a,t1 b where a.c1=b.c1 and a.c1=100;
1 #NSET2: [5, 98, 156]
2 #PRJT2: [5, 98, 156]; exp_num(2), is_atom(FALSE)
3 #NEST LOOP INNER JOIN2: [5, 98, 156];
4 #SSEK2: [0, 1, 52]; scan_type(ASC), IDX_C1_C2_T1(T1 as B), scan_range[(100,min),(100,max))
5 #PRJT2: [2, 98, 104]; exp_num(1), is_atom(FALSE)
6 #HASH2 INNER JOIN: [2, 98, 104]; KEY_NUM(1);
7 #SSEK2: [0, 1, 52]; scan_type(ASC), IDX_C1_C2_T1(T1), scan_range[(100,min),(100,max))
8 #CSCN2: [1, 10000, 52]; INDEX33555575(T2)
1
2
3
4
5
6
7
8
9
10
观察上面sql的执行计划，发现视图部分的子计划已经没有了。说明优化器进行等价改写，将视图的查询拆散了，和其他部分作为一个整体来生 成计划。视图拆分有很多限制，如果视图查询中含有distinc、union、 group by等操作，优化器就无法进行视图拆分。
Sql中使用过多的视图，会使sql变得复杂，优化器也难以生成最佳的执行计划，不能过度依赖优化器进行视图拆分。开发时应尽量减少视图的使用。
3.4、谓词推进
我们先看看下面这样一个SQL，可以看到子查询x相当于一个内联视图。
SQL> explain select * from
(select c1,c2 from t1 where c2='C') x where c1=100;
1 #NSET2: [0, 1, 60]
2 #PRJT2: [0, 1, 60]; exp_num(3), is_atom(FALSE)
3 #PRJT2: [0, 1, 60]; exp_num(3), is_atom(FALSE)
4 #SSEK2: [0, 1, 60]; scan_type(ASC), IDX_C1_C2_T1(T1), scan_range[(100,'C'),(100,'C')]
1
2
3
4
5
6
7
观察上面的执行计划，由于C2字段无索引，子查询X部分本应该走全表扫描， 但是计划中却走了C1字段的索引。说明优化器对原始sql做了如下的等价改写，将条件c1=100推到子查询X中:
–查询转换
select * from
(select c1,c2 from t1 where c2='C' and c1=100) x;
1
2
3.5、查询转换例子
–非关联子查询的转换：
SQL> explain select * from t1
where c1 in (select c1 from t2 ) and c2='A';
1 #NSET2: [1, 250, 156]
2 #PRJT2: [1, 250, 156]; exp_num(5), is_atom(FALSE)
3 #INDEX JOIN SEMI JOIN2: [1, 250, 156];
4 #SLCT2: [1, 250, 156]; T1.C2 = 'A'
5 #CSCN2: [1, 10000, 156]; INDEX33555571(T1)
6 #SSEK2: [1, 2, 0]; scan_type(ASC), IDX_T2_C1(T2), scan_range[T1.C1,T1.C1]
1
2
3
4
5
6
7
8
9
观察原始sql，T2的子查询是个非关联的子查询，完全可以把它生成一个独立的子计划。但是计划中TI和T2做了关联，说明优化器进行了如下的等价改写:
select * from t1
where exists (select 1 from t2 where t1.c1=t2.c1) and c2='A';
1
2
相关INI参数: REFED_EXISTS_OPT_FLAG，影响in和exists子查询的转换。
–外连接转换：
SQL> explain select t1.c1,t2.c2 from t1 left join t2
on t1.c1=t2.c1 where t2.c1=100 and t1.c2='A';
1 #NSET2: [0, 250, 104]
2 #PRJT2: [0, 250, 104]; exp_num(2), is_atom(FALSE)
3 #NEST LOOP INNER JOIN2: [0, 250, 104];
4 #SSEK2: [0, 1, 52]; scan_type(ASC), IDX_C1_C2_T1(T1), scan_range[(100,'A'),(100,'A')]
5 #BLKUP2: [0, 250, 52]; IDX_T2_C1(T2)
6 #SSEK2: [0, 250, 52]; scan_type(ASC), IDX_T2_C1(T2), scan_range[100,100]
1
2
3
4
5
6
7
8
9
观察上面的计划发现，原始sql是外连接，计划中却变成了内连接。这是优化器根 据sql语义判断，就是等价于下面的内连接:
select t1.c1,t2.c2
from t1 inner join t2 on t1.c1=t2.c1 where t2.c1=100 and t1.c2='A';
1
2
4、总结
关于SQL优化主要还是需要先分析系统当前哪些语句是性能影响最大的，一般是那些单个SQL执行慢且执行频率高的。
然后再结合执行计划去进行优化，优化大致思路为：
使用索引：选择合适的索引。
改写SQL：
将left join等价改为inner join；
避免隐式转换不走索引；
将过滤条件上拉，走索引；
用分析函数，减少表扫描。