sql|达梦SQL优化经验 java|数据库

目前的日常工作中，SQL优化占据了很大的一部分，该篇将用尽量通俗的语言讲述一下SQL优化的一般规律（限于DM7单机，大部分只涉及普通表），望抛砖引玉，共同提升专业能力
本篇将分为四个部分进行讲解
1.需要了解的一些基础知识
2.SQL运行的一些方式以及可能的优化手段
3.DM7 提供的监测手段
4.其他非常规的优化手段
#1.需要了解的一些基础知识
##1.1 操作符
操作符是SQL执行的基本单元，所有的SQL语句最终都是转换成一连串的操作符最后在服务器上执行，得到需要的结果，
操作符也是读懂执行计划的基础，这里会列举一下经常出现的操作符，为后面的讲解做一些准备
把操作符大致分为以下几类，
单表入口：CSCN SSCN SSEK CSEK BLKUP
这些操作符是SQL查询数据的原始来源，SQL里面出现的基表，都会出现在这些操作符的描述中，通过这些操作符来确定对应的表在执行计划中在何时以什么样的方式进入。
这里插入一些另外的基础知识，简单介绍一些简单结构的存储方式
a.一般普通表的物理存储都是按（CREATE TABLE T1(C1 INT,C2INT)）
ROWID,C1,C2 ROWID,C1,C2 ROWID,C1,C2 …，这种方式进行存储，这里ROWID我们称之为聚簇KEY，磁盘上面的数据按照ROWID顺序存储，每一行存储的表的完整数据
b.二级索引(create index i_test1 on table(c1))
C1,ROWID C1,ROWID C1,ROWID C1,ROWID …，可以看到，一般的二级索引不存储表的所有数据,仅按C1的顺序存储数据，但是附加存储了C1对应行的ROWID,通过ROWID，我们可以去基表上拿到整行的数据
c.聚簇索引(create cluster index i_test2 on table(c1))
C1,C2,ROWID C1,C2,ROWID C1,C2,ROWID … 这里同样是按C1的顺序存储数据，与二级索引不同的是保留了整行数据
下面列出操作符的具体含义
CSCN ：基础全表扫描(a)，从头到尾，全部扫描
SSCN ：二级索引扫描(b), 从头到尾，全部扫描
SSEK ：二级索引范围扫描(b) ，通过键值精准定位到范围或者单值
CSEK ：聚簇索引范围扫描? ，通过键值精准定位到范围或者单值
BLKUP ：根据二级索引的ROWID 回原表中取出全部数据(b + a)
现在我们来看操作符出现的情况

SQL> CREATE TABLE T1(C1 INT,C2 INT); 操作已执行已用时间: 2.313(毫秒). 执行号:807. SQL> insert into t1 select level,level from dual connect by level < 10000; 影响行数 9999 已用时间: 2.849(毫秒). 执行号:808. SQL> commit; 操作已执行已用时间: 0.310(毫秒). 执行号:809. 已用时间: 0.220(毫秒). 执行号:0. SQL> select * from t1 where c1 = 5; 行号C1C2 ---------- ----------- ----------- 155已用时间: 0.792(毫秒). 执行号:810. --SEL1 SQL> explain select * from t1 where c1 = 5; 1#NSET2: [1, 249, 16] 2#PRJT2: [1, 249, 16]; exp_num(3), is_atom(FALSE) 3#SLCT2: [1, 249, 16]; T1.C1 = 5 4#CSCN2: [1, 9999, 16]; INDEX33555446(T1)

我们创建了一个普通表，没有任何索引，过滤，从T1中取出数据只能走全表扫描CSCN
下面我们创建一条索引

SQL> create index i_test1 on t1(c1); 操作已执行已用时间: 4.695(毫秒). 执行号:814.

再看下面这个语句的计划
–SEL2

SQL> explain select c1 from t1; 1#NSET2: [1, 9999, 12] 2#PRJT2: [1, 9999, 12]; exp_num(2), is_atom(FALSE) 3#SSCN: [1, 9999, 12]; I_TEST1(T1)

–SEL3

SQL> explain select c2 from t1; 1#NSET2: [1, 9999, 12] 2#PRJT2: [1, 9999, 12]; exp_num(2), is_atom(FALSE) 3#CSCN2: [1, 9999, 12]; INDEX33555446(T1)

这个时候T1存在两个入口，CSCN T1基表，或者SSCN 二级索引 I_TEST1,SEL2中，只要求获取C1,二级索引上存在C1,且数据长度比基础表要少（多出一个C2），索引选择SSCN
对于SEL3，依然没有更好的入口，还是选择CSCN全表
一般来说，我们认为CSCN和SSCN的耗时是差不多了，SSCN和CSCN的区别在于，SSCN 扫描出来的数据是按索引列排序的，这一点在一些情况下可以利用
现在看SSEK的情况
–SEL4

SQL> explain select * from t1 where c1 = 5; 1#NSET2: [0, 249, 16] 2#PRJT2: [0, 249, 16]; exp_num(3), is_atom(FALSE) 3#BLKUP2: [0, 249, 16]; I_TEST1(T1) 4#SSEK2: [0, 249, 16]; scan_type(ASC), I_TEST1(T1), scan_range[5,5]

查询条件C1 = 多少，存在C1索引，需要注意的是操作符后面的描述scan_range[5,5]，表示精准定位到5，无疑，多数情况下这样是比较有效率的。
另外一点，SSEK 上面出现了BLKUP操作符，由于I_TEST1上没有C2的数据，而查询需要SELECT *，索引需要BLKUP回原表查找整行数据
很容易的，我们可以想到如果只查询C1，那么BLKUP操作符应该不存在，验证一下
–SEL5

SQL> explain select c1 from t1 where c1 = 5; 1#NSET2: [0, 249, 12] 2#PRJT2: [0, 249, 12]; exp_num(2), is_atom(FALSE) 3#SSEK2: [0, 249, 12]; scan_type(ASC), I_TEST1(T1), scan_range[5,5]

【sql|达梦SQL优化经验】确实如此聚簇索引是比较特殊的索引(对应操作符CSEK)，在DM7上，同一张表的聚簇索引只允许存在一个，默认建表时，基表就是一个ROWID聚簇索引,可以预见到对ROWID的精准定位应该会走CSEK
–SEL6

SQL> explain select c1 from t1 where rowid = 6; 1#NSET2: [0, 1, 12] 2#PRJT2: [0, 1, 12]; exp_num(2), is_atom(FALSE) 3#CSEK2: [0, 1, 12]; scan_type(ASC), INDEX33555446(T1), scan_range[exp_cast(6),exp_cast(6)]

如果我们创建了一个自定义聚簇索引

SQL> create cluster index i_index2 on t1(c2); 操作已执行已用时间: 38.936(毫秒). 执行号:815.

那么ROWID这个聚簇索引就不存在了，取而代指的是按C2为顺序的聚簇索引
–SEL7

SQL> explain select c1 from t1 where rowid = 6; 1#NSET2: [1, 249, 12] 2#PRJT2: [1, 249, 12]; exp_num(1), is_atom(FALSE) 3#SLCT2: [1, 249, 12]; T1.ROWID = var1 4#SSCN: [1, 9999, 12]; I_TEST1(T1)已用时间: 0.478(毫秒). 执行号:0.

这里查询中需要C1以及ROWID，而普通二级索引I_TEST1上正好都有，且比聚簇索引的长度要短，所以选择SSCN I_TEST1
–SEL8

SQL> explain select c1 from t1 where c2 = 6; 1#NSET2: [0, 249, 8] 2#PRJT2: [0, 249, 8]; exp_num(1), is_atom(FALSE) 3#CSEK2: [0, 249, 8]; scan_type(ASC), I_INDEX2(T1), scan_range[6,6]

我们可以看到，对ROWID的精准定位不再走精准定位的CSEK，而是全索引扫描I_TEST1,对C2的精准过滤走的CSEK，且不存在BLKUP
单表的操作符大致就是这些，这些是所有查询的数据来源，其他的条件等都是以此为基础进行的操作
多表关系处理：NEST LOOP (INNER LEFT RIGHT SEMI) ,HASH JOIN(INNER LEFT RIGHT SEMI) ·, INDEX JOIN (INNER LEFT RIGHT SEMI) , MERGE JOIN， SPL
查询中出现的一般都不只一张表，不同的表会有一定的关系，处理多张表时就会涉及到这些操作符，此处只讨论两张表的情况
这里用到最简单的测试用表

create table t1(id varchar); create table t2(id varchar); insert into t1 values('AMEE'),('AMSS'),('BURNING'),('ABED'),('CHALICE'); insert into t2 values('AAAA'),('AAAA'),('BBBB'),('CCCC'),('DDDD'),('AAME'),('AMEE'),('EEEE');

NEST LOOP INNER JOIN :最基础的连接方式，将一张表的一个值与另一张表的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行

--SEL9 SQL> EXPLAIN SELECT /*+ENABLE_HASH_JOIN(0)*/ * FROM T1,T2 WHERE T1.ID = T2.ID; 1#NSET2: [7, 20, 96] 2#PRJT2: [7, 20, 96]; exp_num(2), is_atom(FALSE) 3#SLCT2: [7, 20, 96]; T1.ID = T2.ID 4#NEST LOOP INNER JOIN2: [7, 20, 96]; 5#CSCN2: [0, 5, 48]; INDEX33555457(T1) 6#CSCN2: [0, 8, 48]; INDEX33555458(T2)

SEL9中/+ENABLE_HASH_JOIN(0)/ 为查询提示，暂时不用关注
这里T1中存在5行数据，T2中存在8行数据，NEST LOOP JOIN 就是将这两个表无条件组成一张5 * 8 = 40 行的表
然后对这40行的表依次筛选出 T1.ID = T2.ID的数据（查询计划中的第3行 SLCT操作符，后面会讲到）
不难看出，这种方式是我们比较不希望看到的，如果T1,T2表非常大，那么生成的基表会非常大，同样上层过滤条件需要执行的次数也非常多输出上，结果集按左表（T1）涉及的索引有序
HASH JOIN：没有索引的情况下，大多数连接的处理方式，将一张表的连接列做成HASH表，另一张表的数据向这个HASH表匹配，满足条件的返回
计划的形式一般如下

--SEL 10 SQL> EXPLAIN SELECT * FROM T1,T2 WHERE T1.ID = T2.ID; 1#NSET2: [0, 20, 96] 2#PRJT2: [0, 20, 96]; exp_num(2), is_atom(FALSE) 3#HASH2 INNER JOIN: [0, 20, 96]; KEY_NUM(1); 4#CSCN2: [0, 5, 48]; INDEX33555457(T1) 5#CSCN2: [0, 8, 48]; INDEX33555458(T2)

这里由于HASH表的生成不容易直观表现，我们用例子的方式呈现
T1 C1 AMEE AMSS BURNING ABED CHALICE （5行）
T2 C1 AAAA AAAA BBBB CCCC DDDD AAME AMEE EEEE （8行）
我们指定一种HASH算法（一种简化的算法，服务器上的HASH算法并不是这样），将字符串转换成首字符 + 字符长度，比如 AMEE 转换成 A4 ,BURNING转换成B7，我们将转换后的值称为FOLD
具体执行时，首先或我们获取到T1的所有数据，将T1的数据按照上述的算法去处理
得到处理后的T1 HASH表数据
A4 (AMEE AMSS ABED) B7(BURNING) C7 (CHALICE)
然后我们将T2的所有数据获取，一样进行HASH算法转换，每一个转换的结果拿到 T1的HASH 表中进行对比，对比分为两个阶段，FOLD对比，内容对比，FOLD不相等直接跳过后一阶段
对比过程
AAAA -> A4 -> COMPARE A4 (AMEE AMSS ABED) -> 满足FOLD -> 轮询检查内容(AAAA -> AMEE AAAA -> AMSS AAAA -> ABED) -> 不满足任意条件 -> 返回空
AAAA -> A4 -> COMPARE A4 (AMEE AMSS ABED) -> 满足FOLD -> 轮询检查内容(AAAA -> AMEE AAAA -> AMSS AAAA -> ABED) -> 不满足任意条件 -> 返回空
BBBB -> B4 -> COMPARE A4 (AMEE AMSS ABED) -> 不满足FOLD -> COMPARE B7(BURNING) -> 不满足FOLD -> COMPARE C7 (CHALICE) -> 不满足FOLD -> 不满足任意条件 -> 返回空
CCCC -> C4 -> COMPARE A4 (AMEE AMSS ABED) -> 不满足FOLD -> COMPARE B7(BURNING) -> 不满足FOLD -> COMPARE C7 (CHALICE) -> 不满足FOLD -> 不满足任意条件 -> 返回空
DDDD -> D4 -> COMPARE A4 (AMEE AMSS ABED) -> 不满足FOLD -> COMPARE B7(BURNING) -> 不满足FOLD -> COMPARE C7 (CHALICE) -> 不满足FOLD -> 不满足任意条件 -> 返回空
AAME -> A4 -> COMPARE A4 (AMEE AMSS ABED) -> 满足FOLD -> 轮询检查内容(AAME -> AMEE AAME -> AMSS AAME -> ABED) -> 不满足任意条件 -> 返回空
AMEE -> A4 -> COMPARE A4 (AMEE AMSS ABED) -> 满足FOLD -> 轮询检查内容(AMEE -> AMEE) -> 满足条件 -> 返回AMEE
EEEE -> E4 -> COMPARE A4 (AMEE AMSS ABED) -> 不满足FOLD -> COMPARE B7(BURNING) -> 不满足FOLD -> COMPARE C7 (CHALICE) -> 不满足FOLD -> 不满足任意条件 -> 返回空
最终得到结果集 AMEE AMEE
可以看到，这样处理计算量相比NEST LOOP 会少了很多，主要的计算量有三个部分
1.对左右表的全表扫描（T1,T2）
2.HASH 表的计算（取决于HASH算法的计算复杂度）
3.右表(T2)每行数据进行匹配
（取决于每一个FOLD中存在多少个值，满足FOLD的情况下还需要进行多少次匹配，比如 A4中存在3个值，每一个值匹配时最多需要再匹配3次， INI参数,JOIN_HASH_SIZE控制的就是HASH表的FOLD个数，一般来说FOLD越多，每个FOLD中的值越少）
由于所有的输出都是在扫描右表时完成的，HASH JOIN 的输出是按右表涉及的索引有序的
INDEX JOIN: 将一张表的数据拿出，去另外一张表上进行范围扫描找出需要的数据行，需要右表的连接列上存在索引

SQL> create index i_test2 on t2(id); 操作已执行已用时间: 3.266(毫秒). 执行号:850. --SEL 11 SQL> EXPLAIN SELECT * FROM T1,T2 WHERE T1.ID = T2.ID; 1#NSET2: [0, 17, 96] 2#PRJT2: [0, 17, 96]; exp_num(2), is_atom(FALSE) 3#NEST LOOP INDEX JOIN2: [0, 17, 96] 4#CSCN2: [0, 5, 48]; INDEX33555457(T1) 5#SSEK2: [0, 3, 0]; scan_type(ASC), I_TEST2(T2), scan_range[T1.ID,T1.ID]

这样的做法基本等价于，在右表(T2)上做N次（select * from t2 where id = ?）这样的语句，开销取决于select * from t2 where id = ?这样语句的结果集行数以及左表T1的行数，若两者都很小，那么这种方式是最理想的连接方式
这种连接方式是按T1的基表操作符涉及的索引有序输出的
MERGE JOIN：两张表都扫描索引，按照索引顺序进行归并

SQL> create index i_index2 on t1(id); 操作已执行已用时间: 2.508(毫秒). 执行号:851. --SEL12 SQL> EXPLAIN SELECT /*+enable_index_join(0) enable_hash_join(0)*/* FROM T1,T2 WHERE T1.ID = T2.ID; 1#NSET2: [0, 14, 96] 2#PRJT2: [0, 14, 96]; exp_num(2), is_atom(FALSE) 3#MERGE INNER JOIN3: [0, 14, 96]; 4#SSCN: [0, 5, 48]; I_INDEX2(T1) 5#SSCN: [0, 8, 48]; I_TEST2(T2)

需要同时SSCN 两条有序索引，将其中满足条件的值输出到结果集，效率比NEST LOOP 要高很多，不考虑其他条件，如果T1 和T2都很大的情况下跟HASH的效率相当的（HASH CSCN 两张基表，MERGE JOIN SSCN 相关索引）
这里的输出是按T1 的索引严格有序的
SPL：某一张表输出一行结果后，带入到另一个表中进行执行，满足条件则输出
–SEL13

SQL> explain select /*+REFED_EXISTS_OPT_FLAG(0) ENABLE_RQ_TO_NONREF_SPL(2)*/* from t1 a where exists (select * from t2 b where a.ID = b.ID); 1#NSET2: [0, 1, 56] 2#PIPE2: [0, 1, 56] 3#PRJT2: [0, 1, 56]; exp_num(2), is_atom(FALSE) 4#SLCT2: [0, 1, 56]; NOREFED_EXISTS_SSS[sss3] 5#SSCN: [0, 5, 56]; I_INDEX2(T1 as A) 6#SPL2: [0, 1, 48]; key_num(1), spool_num(0) 7#PRJT2: [0, 1, 48]; exp_num(1), is_atom(FALSE) 8#SSEK2: [0, 1, 48]; scan_type(ASC), I_TEST2(T2 as B), scan_range[var1,var1]

在这里两张表的情况下，我们看到首先是对T1进行扫描获取到数据，然后每一行结果放到T2中进行过滤（SEEK I_TEST2 scan_range[var1,var1]），两张表的情况下，这样的处理方式和INDEX JOIN 基本类似，但在一些更复杂的情况中
不能使用INDEX JOIN 的时候，这样的处理方式有助于提升处理效率
多表连接的操作符可以理解为复杂查询的基本单元，涉及到多表的复杂查询大多是这些操作符组合而成，我们暂时不考虑优化的方式，只了解什么时候会出现这些不同的连接处理方式
过滤条件：SLCT
这类操作符比较简单，是对结果集进行过滤，需要注意的是操作符的描述信息，从描述信息中我们可以看到对于下层操作有哪些可用的过滤条件，这些条件往往是优化方向的来源
–SEL 14

SQL> explain select * from t2 where id > 5 and id not like '%c%'; 1#NSET2: [0, 1, 56] 2#PRJT2: [0, 1, 56]; exp_num(2), is_atom(FALSE) 3#SLCT2: [0, 1, 56]; (exp_cast(T2.ID) > 5 AND exp11 <= 0) 4#CSCN2: [0, 8, 56]; INDEX33555447(T2)

需要关注的是SLCT 的描述部分 (exp_cast(T2.ID) > 5 AND exp11 <= 0)，这里括号中的内容
将ID > 5 标注为了EXP_CAST(T2.ID) > 5
ID NOT LIKE '%c%'标注为了 EXP11 <= 0
其中 EXP_CAST(T2.ID) > 5 提供的信息告诉我们，列ID 和数字5进行比较，是要将列作类型转换的，那么也就是说，就算ID列上存在索引，可能也不能进行范围扫描
因为索引范围扫描的输入要求是和索引列上的类型相同，我们验证一下

--SEL15 SQL> explain select * from t2 where id = 5; 1#NSET2: [0, 1, 56] 2#PRJT2: [0, 1, 56]; exp_num(2), is_atom(FALSE) 3#SLCT2: [0, 1, 56]; exp_cast(T2.ID) = 5 4#CSCN2: [0, 8, 56]; INDEX33555447(T2)

确实，在存在索引（ create index i_index3 on t2(id)）的情况下，单列等值查询也没有走索引进行查询
这个在此也只说明一个大致原理，在DM7中，内部提供多种对比数据的方法以及转换类型的方法，但对比方法和转换类型的方法是没有覆盖所有类型的，不同类型数据进行比较时
会先选取一种比较类型，再确定比较方法
比如在此例中，比较 ID(VARCHAR) = 5(INT),服务器优先选择把类型转换成INT 进行比较，所以导致ID列需要做类型转换，从而不能利用索引（没有索引存储了转换之后的数据）
碰到这种情况，我们需要把索引列的对比对象转换为和索引列一样的格式

--SEL16 SQL> explain select * from t2 where id = '5'; 1#NSET2: [0, 1, 56] 2#PRJT2: [0, 1, 56]; exp_num(2), is_atom(FALSE) 3#SSEK2: [0, 1, 56]; scan_type(ASC), I_INDEX3(T2), scan_range['5','5']

另外一个条件ID NOT LIKE ‘%c%’，转换为EXP11 <= 0 ,实际上是把NOT LIKE 转换成了 INSTR(ID,‘c’) <= 0, 原理在此暂不多赘述，我们希望做到的是
对于SLCT描述项中的每一个，都能在原始SQL中找到对应的条件，并看下是否存在优化的可能性
分组排序：HAGR SAGR DISTINCT AFUN SORT
这类操作符都是对取到的数据做一些处理，或归并，或排序，而归并和排序在某些情况下是互通的
我们先看这些操作符出现的基本情况
HAGR,SAGR:
存在GROUP 的语句，大概率会出现这两个之一（特殊情况，跳跃索引扫描之类暂不考虑）

SQL> create table t4(id int,id1 varchar); 操作已执行已用时间: 9.525(毫秒). 执行号:840. SQL> insert into t4 select level,level from dual connect by level < 10000; 影响行数 9999已用时间: 5.806(毫秒). 执行号:841. SQL> commit; 操作已执行已用时间: 7.966(毫秒). 执行号:842. --SEL17 SQL> explain select id,sum(id1) from t4 group by id; 1#NSET2: [2, 99, 52] 2#PRJT2: [2, 99, 52]; exp_num(2), is_atom(FALSE) 3#HAGR2: [2, 99, 52]; grp_num(1), sfun_num(1); 4#PRJT2: [1, 9999, 52]; exp_num(2), is_atom(FALSE) 5#CSCN2: [1, 9999, 52]; INDEX33555450(T4)

HAGR是最基础的分组方式，HASH AGR操作，对于没有优化条件的分组语句，都会按这种方式进行分组
分组原理和HASH INNER JOIN 的方式类似
将原表数据取出，每个计算FOLD，发现有FOLD相同，且满足后续条件的合并为一组（类似JOIN HASH SIZE,存在INI参数HAGR_HASH_SIZE）
不难发现，如果基表数据非常庞大，HAGR的计算量是不容忽视的，
那么满足一定条件的情况下，我们可以利用有序性走SAGR操作符

SQL> create or replace index i_test4 on t4(id,id1); 操作已执行已用时间: 21.989(毫秒). 执行号:845. --SEL18 SQL> explain select id,sum(id1) from t4 group by id; 1#NSET2: [2, 99, 52] 2#PRJT2: [2, 99, 52]; exp_num(2), is_atom(FALSE) 3#SAGR2: [2, 99, 52]; grp_num(1), sfun_num(1) 4#PRJT2: [1, 9999, 52]; exp_num(2), is_atom(FALSE) 5#SSCN: [1, 9999, 52]; I_TEST4(T4)

这里出现SAGR操作符，说明下层的输出是按分组列排序的，下层为SSCN I_TEST4,而I_TEST4为(ID,ID1)组合索引，按照ID有序，满足SAGR条件
SAGR， SORTED AGR操作，不同于HASH AGR,由于下层数据有序，同一分组的数据按照顺序取出就行，节省了大量的计算，但是下层数据量大时，开销依然需要注意
大部分情况下如果AGR的下层输出在我们人为判断是有序的，但没有出现SAGR操作符，则可以判断计划存在问题
##1.2 执行计划
上一个章节中，大家只需要关注操作符本身，这一章，我们将笼统的介绍一下执行计划，希望达到的效果是
#无论某个SQL是否找的到优化的方法，我们起码可以将执行计划中的每一部分与原SQL对应起来
执行计划是优化的重中之重，这里我们主要讲解执行计划如何读，需要注意哪些地方，为优化做下一定的基础
首先，执行计划是由各类操作符组成的一颗树，也就是排序好的操作符的展现形式，从内到外依次执行
（看执行计划一般看MANAGER中执行计划文本的方式，这样看的更详尽一点，计划可以拷贝到文本编辑工具UE,NOTEPAD++中，这样缩进更为明显)
一般的执行计划格式为
OP1
OP2
OP3
OP4
OP5
OP6
OP7
OP8
缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外，对于上面这个简单的例子，大家可以自己写一个执行顺序
这里做一些空行，看写的执行顺序是否和后面列出的一致
这个例子的执行顺序为
OP3->OP4->OP2->OP7->OP8->OP6->OPT5->OP1
我们拟定一个这样执行计划的SQL

CREATE TABLE TEST5(ID INT); CREATE TABLE TEST6(ID INT); CREATE TABLE TEST7(ID INT); CREATE TABLE TEST8(ID INT); insert into test5 values(3); insert into test6 values(4); insert into test7 select level %100 from dual connect by level < 10000; insert into test8 select level %100 from dual connect by level < 10000; --SEL19 SQL> explain select /*+no_use_cvt_var*/* from ( select test5.id from test5,test6 where test5.id = test6.id ) a, ( select id from (select test7.id from test7,test8 where test7.id = test8.id) group by id ) b where a.id = b.id ; 1#NSET2: [70, 1, 16] 2#PRJT2: [70, 1, 16]; exp_num(2), is_atom(FALSE) 3#HASH2 INNER JOIN: [70, 1, 16]; KEY_NUM(1); 4#PRJT2: [0, 1, 8]; exp_num(1), is_atom(FALSE) 5#HASH2 INNER JOIN: [0, 1, 8]; KEY_NUM(1); 6#CSCN2: [0, 1, 4]; INDEX33555453(TEST5) 7#CSCN2: [0, 1, 4]; INDEX33555454(TEST6) 8#PRJT2: [68, 9801, 8]; exp_num(1), is_atom(FALSE) 9#HAGR2: [68, 9801, 8]; grp_num(1), sfun_num(0); 10#PRJT2: [4, 980100, 8]; exp_num(1), is_atom(FALSE) 11#HASH2 INNER JOIN: [4, 980100, 8]; KEY_NUM(1); 12#CSCN2: [1, 9999, 4]; INDEX33555455(TEST7) 13#CSCN2: [1, 9999, 4]; INDEX33555456(TEST8) 这个例子（忽略/*+no_use_cvt_var*/）的执行计划，我们暂时不关注PRJT和NSET操作符，只看SQL的执行顺序 3#HASH2 INNER JOIN: [70, 1, 16]; KEY_NUM(1); 5#HASH2 INNER JOIN: [0, 1, 8]; KEY_NUM(1); 6#CSCN2: [0, 1, 4]; INDEX33555453(TEST5) 7#CSCN2: [0, 1, 4]; INDEX33555454(TEST6) 9#HAGR2: [68, 9801, 8]; grp_num(1), sfun_num(0); 11#HASH2 INNER JOIN: [4, 980100, 8]; KEY_NUM(1); 12#CSCN2: [1, 9999, 4]; INDEX33555455(TEST7) 13#CSCN2: [1, 9999, 4]; INDEX33555456(TEST8)

和前面的简单例子类似，执行顺序
6->7->5->12->13->11->9->3
换为人能理解的意思就是，首先执行TEST5和TEST6的HASH连接，然后执行TEST7,TEST8的HASH连接并将连接结果进行HASH分组，再将两个结果再次进行HASH连接得到最终结果集
这个例子的SQL写法比较简单意义也是非常明确的，读懂SQL需要干什么可以把操作符顺序写下来不会很困难
同样的，只看到这个执行计划，我们需要能想出来这个SQL原本是什么样子
这里可能会有些绕，多做几次就好了
这里提供一个例子练习一下比较长0 0，耐心看，SQL和计划如下（未优化）

SELECT count(0) FROM SB_YXDNB DNB INNER JOIN SB_YXZDDNBGX y ON y.YXDNBID= DNB.YXDNBID AND DNB.YXZTDM <> '03' INNER JOIN SB_YXZD yxzd ON yxzd.ZDID = y.ZDID LEFT JOIN SB_ZDZC zdzc ON zdzc.ZDZCID = yxzd.ZDZCID LEFT JOIN SB_DNBZC dnbzc ON dnbzc.DNBZCID = DNB.DNBZCID LEFT JOIN DW_BDZ bdz ON bdz.BDZID = yxzd.BDZID INNER JOIN KH_JLDDNBGX jldgx ON jldgx.YXDNBID = DNB.YXDNBID INNER JOIN KH_JLD jld ON jld.JLDID = jldgx.JLDID LEFT JOIN DW_TQ tq ON tq.TQID = jld.TQID LEFT JOIN DW_XLXD xlxd ON xlxd.XLXDID = jld.XLXDID INNER JOIN KH_YDKH ydkh ON jld.YHID = ydkh.YHID INNER JOIN XT_ZZJG jg ON jg.ZZID = ydkh.ZZID INNER JOIN m_main_tree t ON DNB.YXDNBID= t.archive_id AND t.archive_type = 2001 and ( t.path like '1' ) inner join ( Select LEVELas rownum_id, 1543939200 + (LEVEL-1) * 1543939200 as sj from SYS_DUAL connect by LEVEL <=(1543939200-1543939200)/(1543939200) + 1 ) tmp on tmp.rownum_id <= (1543939200-1543939200)/(1543939200) + 1 INNER JOIN CJ_DNSZQX c ON C.YXDNBID = DNB.YXDNBID AND tmp.sj= c.sjsj AND C.SJLYDM <> 1 AND C.SJLYDM <> 21#NSET2: [153272, 1, 816] 2#PIPE2: [153272, 1, 816] 3#PRJT2: [153271, 1, 816]; exp_num(1), is_atom(FALSE) 4#AAGR2: [153271, 1, 816]; grp_num(0), sfun_num(1) 6#NEST LOOP INDEX JOIN2: [34, 1, 816] 7#NEST LOOP INDEX JOIN2: [34, 1, 778] 8#NEST LOOP INDEX JOIN2: [34, 1, 680] 9#HASH2 INNER JOIN: [34, 1, 612]; KEY_NUM(1); 10#SLCT2: [1, 1, 0]; exp_cast(TMP.ROWNUM_ID) <= var8 11#HIERARCHICAL QUERY: [0, 1, 0]; KEY_NUM(0); 12#CSCN2: [0, 1, 0]; INDEX33561057(SYS_DUAL) 13#SLCT2: [0, 1, 0]; var18 <= var23 14#CSCN2: [0, 1, 0]; INDEX33561057(SYS_DUAL) 15#NEST LOOP INDEX JOIN2: [31, 7435, 612] 16#NEST LOOP INDEX JOIN2: [0, 1, 496] 17#NEST LOOP INDEX JOIN2: [0, 1, 368] 18#NEST LOOP INDEX JOIN2: [0, 1, 300] 19#SLCT2: [0, 1, 232]; DNB.YXZTDM <> '03' 20#NEST LOOP INDEX JOIN2: [0, 1, 232] 21#SLCT2: [0, 1, 116]; T.ARCHIVE_TYPE = var10 22#BLKUP2: [0, 1, 116]; M_MAIN_TREE_PATH(T) 23#SSEK2: [0, 1, 116]; scan_type(ASC), M_MAIN_TREE_PATH(M_MAIN_TREE as T), scan_range['1','1'] 24#BLKUP2: [0, 1, 0]; INDEX33558043(DNB) 25#SSEK2: [0, 1, 0]; scan_type(ASC), INDEX33558043(SB_YXDNB as DNB), scan_range[T.ARCHIVE_ID,T.ARCHIVE_ID] 26#BLKUP2: [0, 1, 0]; JLDDNBGX_INDEX_YXDNBID(JLDGX) 27#SSEK2: [0, 1, 0]; scan_type(ASC), JLDDNBGX_INDEX_YXDNBID(KH_JLDDNBGX as JLDGX), scan_range[DNB.YXDNBID,DNB.YXDNBID] 28#BLKUP2: [0, 1, 0]; YXZDDNBGX_INDEX_YXDNBID(Y) 29#SSEK2: [0, 1, 0]; scan_type(ASC), YXZDDNBGX_INDEX_YXDNBID(SB_YXZDDNBGX as Y), scan_range[DNB.YXDNBID,DNB.YXDNBID] 30#BLKUP2: [0, 1, 0]; INDEX33557992(JLD) 31#SSEK2: [0, 1, 0]; scan_type(ASC), INDEX33557992(KH_JLD as JLD), scan_range[JLDGX.JLDID,JLDGX.JLDID] 32#PARALLEL: [27, 8238, 0]; scan_type(FULL), key_num(0, 0, 0) 33#BLKUP2: [27, 8238, 0]; INDEX33558980(C) 34#SSEK2: [27, 8238, 0]; scan_type(ASC), INDEX33558980(CJ_DNSZQX as C), scan_range[(DNB.YXDNBID,min),(DNB.YXDNBID,max)) 35#BLKUP2: [0, 1, 0]; INDEX33558004(YDKH) 36#SSEK2: [0, 1, 0]; scan_type(ASC), INDEX33558004(KH_YDKH as YDKH), scan_range[JLD.YHID,JLD.YHID] 37#SSEK2: [0, 1, 0]; scan_type(ASC), INDEX33558056(SB_YXZD as YXZD), scan_range[Y.ZDID,Y.ZDID] 38#SSEK2: [0, 1, 0]; scan_type(ASC), INDEX33558157(XT_ZZJG as JG), scan_range[YDKH.ZZID,YDKH.ZZID]

读懂SQL本身是关键，执行计划更多的是起一个提示作用，侧面告诉大家SQL需要做什么事情
能正常读取执行计划描述的执行顺序后，我们关注下执行计划各个节点的详细信息
执行计划中所有操作符的后面都会有一个三元组
如：#CSCN2: [1, 9999, 4]
[1, 9999, 4]就是我们提到的这个三元组，3个数字分别表示该操作符的估算代价，该操作符的输出行数，该操作符涉及数据的行长
#CSCN2: [1, 9999, 4] 表示的意义为，这是一个全表扫描操作，涉及的行数为9999，每场数据长度为4，整体代价估算为1
更多的时候，我们只需要关注的是第二项，第二项的数值越少，这个操作符的代价越小，三元组中的首项和尾项仅有参考意义
我们将三元组中的第二项称为估算行数(card)，###在复杂查询中，估算行数对于执行计划以及SQL性能的影响很大
CARD受统计信息的影响
这里简单介绍一下统计信息，统计信息简单理解为将索引（包含原表ROWID聚簇索引）的某一列进行统计分析，
列出其最大最小值，存在多少不同值，各个值存在多少个辅助信息
对于没有统计信息的列，DM7简单的按照一定比例进行概率过滤
涉及到的INI参数为
SEL_RATE_EQU 等值过滤选择率默认0.025
SEL_RATE_SINGLE 一般条件选择率默认 0.05
来看例子

create table test10(id1 int,id2 varchar,id3 varchar,id4 varchar); --方便起见，我们插入1W行数据，ID1从1-10000， ID2 为 0a - 4a, id3全为b, id为1c - 10000c insert into test10 select level,level % 5 || 'a','b',level || 'c' from dual connect by level <= 10000; --SEL20 SQL> explain select * from test10 where id1 = 5; 1#NSET2: [1, 250, 156] 2#PRJT2: [1, 250, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 250, 156]; TEST10.ID1 = 5 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10)

可以看到CSCN涉及1W行数据，这个没有问题，但是过滤条件SLCT的CARD标注为250行(#SLCT2: [1, 250, 156])，这个和我们的预期是不一致的，因为不存在统计信息
系统按10000 * 0.025直接给出250的结果
如果存在多个等值条件呢？

--SEL21 --我们这里保障列与值类型相同 id2 varchar = '5' SQL> explain select * from test10 where id1 = 5 and id2 = '5'; 1#NSET2: [1, 6, 156] 2#PRJT2: [1, 6, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 6, 156]; (TEST10.ID1 = 5 AND TEST10.ID2 = '5') 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10) SLCT的CARD为6，约等于10000 * 0.025 * 0.025 = 6.25

可以简单推测出存在多个条件，且不存在统计信息的情况下，CARD是多个选择率的乘积乘以下层输出行数
–我们再来看一般条件

--SEL22 SQL> explain select * from test10 where id1 > 5; 1#NSET2: [1, 500, 156] 2#PRJT2: [1, 500, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 500, 156]; TEST10.ID1 > 5 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10)

SLCT输出CARD为500，和INI默认SEL_RATE_SINGLE参数0.05一致 10000 * 0.05 = 500
一般来说，除开等值条件外的所有过滤条件我们都认为是一般条件
同样的，一般条件和等值条件的组合，没有统计信息的情况下，最终选择率依然是按乘积计算

--SEL23 SQL> explain select * from test10 where id1 > 5 and id2 = '5'; 1#NSET2: [1, 12, 156] 2#PRJT2: [1, 12, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 12, 156]; (TEST10.ID2 = '5' AND TEST10.ID1 > 5) 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10) SLCT CARD = 12 = 10000 * 0.05 * 0.025 = 12.5

现在我们收集统计信息，推荐收集统计信息的方式有两种

--收集单列统计信息 STAT 100 ON 表(列) --收集SQL语句涉及列的统计信息 CREATE VIEW VA AS SQL语句; CALL SP_SQL_STAT_INIT('SELECT * FROM VA')SQL> stat 100 on test10(id1); 操作已执行已用时间: 26.350(毫秒). 执行号:860. SQL> stat 100 on test10(id2); 操作已执行收集完毕后，我们再看计划中的CARD值 SQL> explain select * from test10 where id1 = 5; 1#NSET2: [1, 1, 156] 2#PRJT2: [1, 1, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 1, 156]; TEST10.ID1 = 5 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10)已用时间: 0.689(毫秒). 执行号:0. --单列估算准确，ID1只存在一个为5的行SQL> explain select * from test10 where id2 = '5'; 1#NSET2: [1, 1, 156] 2#PRJT2: [1, 1, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 1, 156]; TEST10.ID2 = '5' 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10) 已用时间: 0.779(毫秒). 执行号:0. --单列估算准确，CARD最小为1，ID2不存在为5的行SQL> explain select * from test10 where id1 = 5 and id2 = '5'; 1#NSET2: [1, 1, 156] 2#PRJT2: [1, 1, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 1, 156]; (TEST10.ID1 = 5 AND TEST10.ID2 = '5') 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10) --多列估算准备，不存在满足两个条件的行SQL> explain select * from test10 where id1 > 5; 1#NSET2: [1, 9995, 156] 2#PRJT2: [1, 9995, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 9995, 156]; TEST10.ID1 > 5 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10) --单列一般条件估算准确，9995个ID1 > 5SQL> explain select * from test10 where id1 > 5 and id2 = '5'; 1#NSET2: [1, 1, 156] 2#PRJT2: [1, 1, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 1, 156]; (TEST10.ID2 = '5' AND TEST10.ID1 > 5) 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10)

–多列混合估算准确，不存在满足条件的行
由此可见，统计信息的收集可以大概率的修正对过滤行数的估算，但是并非所有情况都适用，
我们知道，统计信息的收集是基于基础列做统计分析，如果列值上被套有函数，则统计信息会失效

--SEL24 SQL> explain select * from test10 where TRIM(id1) > 5 ; 1#NSET2: [1, 500, 156] 2#PRJT2: [1, 500, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 500, 156]; exp_cast(exp11) > 5 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10)

ID1的值实际上没有变化，但是SLCT CARD 不准确
另外需要小心的是隐式转换

SQL> explain select * from test10 where id2 > 5; 1#NSET2: [1, 1, 156] 2#PRJT2: [1, 1, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 1, 156]; exp_cast(TEST10.ID2) > 5 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10) --这个和下面的等价 SQL> explain select * from test10 where cast(id2 as int) > 5; 1#NSET2: [1, 1, 156] 2#PRJT2: [1, 1, 156]; exp_num(5), is_atom(FALSE) 3#SLCT2: [1, 1, 156]; exp_cast(TEST10.ID2) > 5 4#CSCN2: [1, 10000, 156]; INDEX33555457(TEST10)

不过目前的版本应该把简单的CAST 的选择率已经修正了，这里的SLCT CARD 都是正常
另外，统计信息同样影响各种连接的估算行数
待更
更多资讯请上达梦技术社区了解：https://eco.dameng.com