达梦优化器介绍

达梦优化器

查询优化器通过分析可用的执行方式和查询所涉及的对象统计信息来生成最优的执行计划。此外，如果存在 HINT 优化提示，优化器还需要考虑优化提示的因素。

查询优化器的处理过程包括：

1. 优化器生成所有可能的执行计划集合；

2. 优化器基于字典信息的数据分布统计值、执行语句涉及到的表、索引和分区的存储特点来估算每个执行计划的代价。代价是指 SQL 语句使用某种执行方式所消耗的系统资源的估算值。其中，系统资源消耗包括 I/O 、 CPU 使用情况、内存消耗等；

3. 优化器选择代价最小的执行方式作为该条语句的最终执行计划。

优化器所做的操作有：查询转换、估算代价、生成计划。

1、查询转换

查询转换是指把经过语法、语义分析的查询块之间的连接类型、嵌套关系进行调整，生成一个更好的查询计划。常用的查询转换技术包括过滤条件的下放、相关子查询的去相关性。

1. 过滤条件下放：在连接查询中，把部分表的过滤条件下移，在连接之前先过滤，可以减少连接操作的数据量，提升语句性能；

2. 相关子查询的去相关性：把与子查询相关的外表与内表采用半连接的方式执行，放弃默认采取的嵌套连接方式，对性能有较大提升。

2、估算代价

估算代价是指对执行计划的成本进行估算。执行节点之间的代价值相关性较强，一个执行节点的代价包括该节点包含的子节点代价。代价衡量指标包括选择率、基数、代价。选择率是指满足条件的记录占总记录数的百分比。记录集可以是基表、视图、连接或分组操作的结果集。        

如果没有统计信息，则优化器依据过滤条件的类型来设置对应的选择率。例如，等值条件的选择率低于范围条件选择率。这些假定是根据经验值，认为等值条件返回的结果集最少。如果有统计信息，则可以使用统计信息来估算选择率。例如，对于等值谓词（name =― 韩梅梅‖ ），如果 name 列有 N 个不同值，那么，选择率是 N 分之一。

基数是指整个行集的行数，该行集可以是基表、视图、连接或分组操作的结果集。代价表示资源的使用情况。查询优化器使用磁盘 I/O 、 CPU 占用和内存使用作为代价计算的依据，所以代价可以用 I/O 数、CPU 使用率和内存使用一组值来表示。所有操作都可 以进行代价计算，例如扫描基表、索引扫描、连接操作或者对结果集排序等。

访问路径决定了从一个基表中获取数据所需要的代价。访问路径可以是基表扫描、索引扫描等。在进行基表扫描或索引扫描时，一次 I/O 读多个页，所以，基表扫描或索引全扫描的代价依赖于表的数据页数和多页读的参数值。二级索引扫描的代价依赖于 B 树的层次、需扫描的叶子块树以及根据 rowid 访问聚集索引的记录数。连接代价是指访问两个连接的结果集代价与连接操作的代价之和。

3、生成计划

生成计划指计划生成器对给定的查询按照连接方式、连接顺序、访问路径生成不同的执行计划，选择代价最小的一个作为最终的执行计划。连接顺序指不同连接项的处理顺序。连接项可以是基表、视图、或者是一个中间结果集。例如表 t1 、 t2 、 t3 的连接顺序是先访问 t1 ，再访问 t2 ，然后对 t1 与 t2 做连接生成结果集 r1 ，最后把 t3 与 r1 做连接。一个查询语句可能的计划数量是与 FROM 语句中连接项的数量成正比的，随着连接项的数量增加而增加。

达梦执行计划

1、EXPLAIN

        查看执行计划，在需要执行的sql前，加上explain关键字即可，若使用的manager，还可以选定要执行的sql，按F9即可调出执行计划，返回的执行计划如下图：

先对表字段进行说明：

名称：计划节点的操作符
附加信息：每个操作符执行的操作
代价：每个操作符花费的时间，这里以数字表示
结果集：每个操作符返回的结果条数
行数据处理长度：执行节点的字节数
描述：对每个操作符的简单描述
“名称”字段中操作符的说明：

NEST2：用于结果集收集的操作符

PRJT2：关系的投影运算，用于选择表达式的计算；
BLKUP2：二次扫描（回表）
SSEK2：二级索引扫描 

达梦中执行计划涉及到的一些主要操作符有如下表格，因执行计划操作都是英文缩写，为更好的理解操作符操作缩写含义，专门对其缩写前的英文描述进行整理。

整个sql的执行计划的说明： 
        执行计划的每一行，就是一个计划节点，计划节点里的操作符，就是这个计划节点干了什么事。根据左深二叉树，从末尾往上看，最末尾就是最开始执行的操作。上图中，根据where条件中的id列，可使用id列的二级索引（即主键索引，操作符SSEK2），通过该索引过滤符合条件的数据行，得到需要查到的值。但该select语句还要求查询其他字段的值，因此需要进行回表操作，即二次扫描（操作符BLKUP2），回到聚簇索引中，读取其他字段的值，并将查询结果进行投影和收集（操作符PRJT2和NEST2）。

接下来结合实例来介绍下这些操作符：

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

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]; INDEX33555483(T1)
 
已用时间: 0.458(毫秒). 执行号:0.
 NSET 是用于结果集收集的操作符，一般是查询计划的顶层节点，优化中无需对该操作符过多关注。

PRJT投影

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]; INDEX33555483(T1)
 
已用时间: 0.544(毫秒). 执行号:0.

投影(project)运算，就是选择查询的列，用于选择表达式项的计算;广泛用于查询，排序，函数索引创建等。

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]; INDEX33555483(T1)
 
已用时间: 0.634(毫秒). 执行号:0.
 SLCT操作符，用于查询条件的过滤，如果代价比较高，要考虑是否在查询条件相关列添加索引，及是分析是否有合适的索引。

CSCN全表扫描

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]; INDEX33555483(T1)
 
已用时间: 0.465(毫秒). 执行号:0.
CSCN操作符是 CLUSTER INDEX SCAN 的缩写，通过聚集索引扫描全表，全表扫描 I/O 开销较大，如果没有选择谓词，或者没有索引可以利用，则一般只能做全表扫描。表数据量大的情况，应该避免通过添加有索引的查询条件。

BLKUP二次扫描

SQL> EXPLAIN SELECT * FROM T1 WHERE C1=10;
 
1   #NSET2: [1, 1, 156] 
2     #PRJT2: [1, 1, 156]; exp_num(5), is_atom(FALSE) 
3       #BLKUP2: [1, 1, 156]; IDX_C1_T1(T1)
4         #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]
 
已用时间: 0.757(毫秒). 执行号:0.

SSCN索引全扫描

SQL> CREATE INDEX IDX_C1_C2_T1 ON T1(C1,C2);
操作已执行
已用时间: 21.082(毫秒). 执行号:9101.
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)
 
已用时间: 0.656(毫秒). 执行号:0.
 SSCN 是索引全扫描，不需要扫描表。

2、ET

前面讲到的执行计划，每个操作符和计划节点的代价，都比较抽象，为了提高效率，因此使用ET。

        ET是达梦数据库自带的SQL 性能分析工具，能统计执行计划中，每个操作符的时间花费（将代价显示为具体的时间），从而定位到有性能问题的操作，指导我们去优化。

        ET是默认关闭的，因此在使用ET之前，需要先开启：

--开启ET
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);
 
--关闭ET
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);
 
--查看ET是否开启
select * from v$parameter t where NAME = 'MONITOR_SQL_EXEC';
select * from v$parameter t where NAME = 'ENABLE_MONITOR';
 
--ENABLE_MONITOR，动态参数(系统级)
--MONITOR_SQL_EXEC，动态参数(会话级)

注意：ET会对数据库性能有影响，使用完后记得关闭以保证数据库性能不受影响。

结果说明：

• OP：操作符
• TIME(US)：时间开销，单位为微秒
• PERCENT：执行时间占总时间百分比
• RANK：执行时间的耗时排序
• SEQ：执行计划的节点号
• N_ENTER：进入次数 

3、SQL优化案例

3.1执行sql

select count(*) from t_test where product_type like '%NB%';

该sql的执行计划：

该sql的ET结果：

3.2分析过程

从上图可看出，首先执行了全表扫描，最末尾处的执行节点操作符CSCN2就是全表扫描的意思。因此代价也是花在了此处。但根据过往经历，where条件里的 like '%……%' 一般都是走不了索引的，只能全表扫。但是在达梦数据库中，有个参数提供了查询条件中%的优化策略，该参数叫LIKE_OPT_FLAG，意思是LIKE查询的优化开关，如下所示：

select * from v$parameter t where NAME = 'LIKE_OPT_FLAG';
 
--TYPE是参数类型，SESSION表示动态参数(会话级)
--VALUE表示该参数现在的值
--SYS_VALUE表示内存中改参数的值
--FILE_VALUE表示数据库配置文件dm.ini中这个参数的值
--DESCRIPTION是该参数的说明

如上图所示，LIKE_OPT_FLAG参数默认是开启状态，所以这里我就不用执行命令开启了。若VALUE这里显示为0，表示关闭，需要手动开启，执行如下命令：SP_SET_PARA_VALUE(1,'LIKE_OPT_FLAG',1); 
--最末尾的1可以改为31，根据情况而定。

这里说明下，参数LIKE_OPT_FLAG，取值如下：

0：不优化；
1：对于LIKE表达式首尾存在通配符的情况，优化为POSITION()函数；对于LIKE表达式首部存在通配符，并且条件列存在REVERSE()函数索引时，优化为REVERSE()函数；
2：对于COL1 LIKE COL2 || '%'的情况，优化为POSITION()函数；
4：对于COL1 LIKE ‘A’||‘B%’的情况，优化为COL1 LIKE ‘AB%’；
8：对于可计算的LIKE表达式，优化为常量；
16：对于控制函数索引列的LIKE表达式，优化为BETWEEN…AND…表达式。

3.3进行优化

开启LIKE_OPT_FLAG后，根据文档说明，我们这情况属于“1”，会将其转化为POSITION()函数，因此根据此方案设计索引：

create or REPLACE index idx_product_type on t_test(position('NB',product_type));

并更新索引统计信息：

SP_INDEX_STAT_INIT('SYSDBA','IDX_PRODUCT_TYPE');

3.4查看优化结果

优化器经过计算后使用了创建的二级索引idx_product_type，代价从13降低到了1，再看下ET的结果：

社区地址：https://eco.dameng.com