达梦SQL优化之-执行计划操作符解读

一、DM 优化器概述 ? ? ? ?

????????数据库管理系统的优化器是决定 SQL 执行性能的关键。DM 采用基于代价的优化器，服务器接收到 SQL 语句后，经过语法分析和语义分析，之后进行各种关系代数转换，形成一 系列备选的逻辑执行计划。DM 优化器通过对这些备选逻辑计划进行代价估算和优化，最终 生成一个最优的物理执行计划，交由执行器执行。DM 优化器的工作流程如下图所示。

????????优化器工作流程前面说的“生成一个最优的物理执行计划”只是一个相对的概念，计算出一个与实际执 行相接近的代价值是一件困难的事。影响实际执行代价的因素非常多，优化器不可能也没有 必要非常全面地考虑每一个细节，如系统封锁，并发等因素。DM 优化器主要关注的是执行 查询所涉及的表的记录行数，数据页的数量，可利用的索引和统计信息，以及内存，IO 和 CPU 的计算量等。

二、SQL执行顺序

在 SQL 中，当我们执行查询时，会经过以下大致步骤来完成操作：

1、FROM（从）子句指定要查询的表或视图。这里可能包含多个表名，用逗号分隔。如果有连接条件，则需要使用 JOIN 关键字将不同的表进行连接。
示例：SELECT * FROM table_name;
2、WHERE（条件）子句用于添加筛选条件，只返回符合特定条件的记录。通常与比较运算符（=、<、>等）结合使用。
示例：SELECT * FROM table_name WHERE column = value;
3、GROUP BY（按组分组）子句根据指定列对结果集进行分组。通常与聚合函数（SUM、COUNT、AVG等）一起使用。
示例：SELECT column1, SUM(column2) FROM table_name GROUP BY column1;
4、HAVING（具体条件）子句类似于WHERE子句，但是HAVING子句主要用于GROUP BY后面的筛选条件。
示例：SELECT column1, COUNT() FROM table_name GROUP BY column1 HAVING COUNT() > 5;
5、ORDER BY（排序）子句用于对结果集进行排序，默认为升序。也可以指定DESC（降序）。
示例：SELECT * FROM table_name ORDER BY column ASC/DESC;
6、LIMIT（限制）子句用于限制返回的记录数量。通常与OFFSET（偏移量）一起使用。
示例：SELECT * FROM table_name LIMIT number OFFSET offset;
7、UNION（联合）子句用于将两个或更多 SELECT 语句的结果集合并到一个结果集中。注意，每个 SELECT 语句必须具有相同的列数和数据类型。
示例：SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
8、DISTINCT（去重）关键字用于消除结果集中的重复值。
示例：SELECT DISTINCT column FROM table_name;
9、AS（别名）关键字用于为列或表设置别名，提高查询效率和可读性。
示例：SELECT column AS alias FROM table_name;
10、JOIN（连接）关键字用于将多个表基于共同的列进行连接。
示例：SELECT t1.column1, t2.column2 FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.table1_id;

如图所示：

三、达梦执行计划操作符

达梦执行计划涉及到的一些主要操作符有：

1. CSCN ：基础全表扫描(a)，从头到尾，全部扫描
2. SSCN ：二级索引扫描(b), 从头到尾，全部扫描
3. SSEK ：二级索引范围扫描(b) ，通过键值精准定位到范围或者单值
4. CSEK ：聚簇索引范围扫描? ，通过键值精准定位到范围或者单值
5. BLKUP ：根据二级索引的ROWID 回原表中取出全部数据(b + a)

一、执行计划解读

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、准备测试表和数据

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');-- 收集指定的索引的统计信息

????????这里说明一下SP_INDEX_STAT_INIT的两个参数分别是模式名和索引名。我这里指定的是USER，会默认查找当前登录用户同名的模式，如果这个用户下有多个模式，查不到其他模式。

2、NSET:收集结果集

说明：用于结果集收集的操作符， 一般是查询计划的顶层节点。

EXPLAIN SELECT * FROM T1;

3、PRJT：投影

说明：关系的“投影”(project)运算，用于选择表达式项的计算;广泛用于查询，排序，函数索引创建等。

EXPLAIN SELECT * FROM T1;

4、SLCT：选择

说明：关系的“选择” 运算，用于查询条件的过滤。

EXPLAIN SELECT * FROM T1 WHERE C2='TEST'

5、AAGR：简单聚集

说明：用于没有group by的count，sum，age，max，min等聚集函数的计算。

EXPLAIN SELECT COUNT(*) FROM T1 WHERE C1 = 10;

6、FAGR：快速聚集

说明：用于没有过滤条件时从表或索引快速获取 MAX/MIN/COUNT值，DM数据库是世界上单表不带过滤条件下取COUNT值最快的数据库。

EXPLAIN SELECT COUNT(*) FROM T1;

7、HAGR：HASH分组聚集

说明：用于分组列没有索引只能走全表扫描的分组聚集，C2列没有创建索引。

EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C2;

8、SAGR：流分组聚集

说明：用于分组列是有序的情况下可以使用流分组聚集，C1上已经创建了索引，SAGR2性能优于HAGR2。

EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C1;

官方解释是：如果输入流是有序的，则使用流分组，并计算聚集函数。

9、BLKUP：二次扫描

说明：先使用2级别索引定位，再根据表的主键、聚集索引、 rowid等信息定位数据行。

EXPLAIN SELECT * FROM T1 WHERE C1=10;

bookmark lookup 翻译成中文是书签查找

10、CSCN：全表扫描

说明：CSCN2是CLUSTER INDEX SCAN的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。在一个高并发的系统中应尽量避免全表扫描。

EXPLAIN SELECT * FROM T1;

11、SSEK、CSEK、SSCN：索引扫描

1) SSEK

说明：SSEK2是二级索引扫描即先扫描索引，再通过主键、聚集索引、ROWID等信息去扫描表；

EXPLAIN SELECT * FROM T1 WHERE C1=10;

2）CSEK

说明：CSEK2是聚集索引扫描只需要扫描索引，不需要扫描表；

CREATE CLUSTER INDEX IDX_C1_T2 ON T2(C1);

EXPLAIN SELECT * FROM T2 WHERE C1=10

3）SSCN

说明：SSCN是索引全扫描，不需要扫描表。

官方解释是：直接使用二级索引进行扫描。

CREATE INDEX IDX_C1_C2_T1 ON T1(C1,C2);

EXPLAIN SELECT C1,C2 FROM T1;

三、简单连接查询例子

CREATE TABLE T1(C1 INT,C2 CHAR);
CREATE TABLE T2(D1 INT,D2 CHAR);
CREATE INDEX IDX_T1_C1 ON T1(C1);
INSERT INTO T1 VALUES(1,'A');
INSERT INTO T1 VALUES(2,'B');
INSERT INTO T1 VALUES(3,'C');
INSERT INTO T1 VALUES(4,'D');
INSERT INTO T2 VALUES(1,'A');
INSERT INTO T2 VALUES(2,'B');
INSERT INTO T2 VALUES(5,'C');
INSERT INTO T2 VALUES(6,'D');

SELECT A.C1+1,B.D2 FROM T1 A, T2 B WHERE A.C1 = B.D1;
EXPLAIN SELECT A.C1+1,B.D2 FROM T1 A, T2 B WHERE A.C1 = B.D1;

用到的操作符说明：

NSET2??? 结果集(result set)收集，一般是查询计划的顶层节点

PRJT2??? 关系的―投影‖(project)运算，用于选择表达式项的计算

NEST LOOP INDEX JOIN2?? 索引内连接

CSCN2??? 聚集索引扫描

SSEK2??? 二级索引数据定位

该计划的大致执行流程如下：

1) CSCN2: 扫描 T2 表的聚集索引，数据传递给父节点索引连接；

2) NEST LOOP INDEX JOIN2: 当左孩子有数据返回时取右侧数据；

3) SSEK2: 利用 T2 表当前的 D1 值作为二级索引 IDX_T1_C1 定位查找的 KEY，返回结果给父节点；

4) NEST LOOP INDEX JOIN2: 如果右孩子有数据则将结果传递给父节点 PRJT2，否则继续取左孩子的下一条记录；

5) PRJT2: 进行表达式计算 C1+1, D2；

6) NSET2: 输出最后结果；

7) 重复过程 1) ~ 4)直至左侧 CSCN2 数据全部取完。

应该是这么个意思，取右侧的一个值，去左侧匹配，然后到PRJT2去计算，接着再去取右侧一个值，再去左侧匹配，如此循环，直到匹配完，不知道我理解的对不对。

四、单表

CREATE TABLE T1(C1 INT,C2 INT);

insert into t1 select level,level from dual connect by level < 10000;

1、全表扫描（无索引时）

explain select * from t1 where c1 = 5;

用到的操作符说明：

NSET2? 结果集(result set)收集，一般是查询计划的顶层节点

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

SLCT2? 关系的―选择‖（select）运算，用于查询条件的过滤

CSCN2? 聚集索引扫描

说明：创建了一个普通表，没有任何索引，过滤，从T1中取出数据只能走全表扫描CSCN

2、t1(c1)加索引i_test1

create index i_test1 on t1(c1);

1）直接使用二级索引扫描

explain select c1 from t1;

用到的操作符说明：

NSET2? 结果集(result set)收集，一般是查询计划的顶层节点

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

SSCN? 直接使用二级索引进行扫描

说明：这个时候T1存在两个入口，CSCN T1基表(全表扫描T1)，或者SSCN 二级索引I_TEST1,本例只要求获取C1,二级索引上存在C1,且数据长度比基础表要少（基表多出一个C2），索引选择SSCN。

2）全表扫描（有索引时）

explain select c2 from t1;

用到的操作符说明：

NSET2? 结果集(result set)收集，一般是查询计划的顶层节点

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

CSCN2? 聚集索引扫描

说明：依然没有更好的入口，还是选择CSCN全表

3）定位查找

explain select * from t1 where c1 = 5;

用到的操作符说明：

NSET2? 结果集(result set)收集，一般是查询计划的顶层节点

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

BLKUP2? 定位查找

SSEK2? 二级索引数据定位

说明：查询条件C1 = 多少，存在C1索引，需要注意的是操作符后面的描述scan_range[5,5]，表示精准定位到5，无疑，多数情况下这样是比较有效率的。

另外一点，SSEK 上面出现了BLKUP操作符，由于I_TEST1上没有C2的数据，而查询需要SELECT *，索引需要BLKUP回原表查找整行数据。

很容易的，我们可以想到如果只查询C1，那么BLKUP操作符应该不存在，验证一下。

explain select c1 from t1 where c1 = 5;

3、聚簇索引

1）ROWID聚簇索引

聚簇索引是比较特殊的索引(对应操作符CSEK)，在DM7上，同一张表的聚簇索引只允许存在一个，默认建表时（不建堆表的情况下），基表就是一个ROWID聚簇索引,可以预见到对ROWID的精准定位应该会走CSEK。

explain select c1 from t1 where rowid = 6;

?

用到的操作符说明：

NSET2? 结果集(result set)收集，一般是查询计划的顶层节点

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

CSEK2? 聚集索引数据定位

2) t1(c2)自定义聚簇索引i_index2

create cluster index i_index2 on t1(c2);

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

explain select c1 from t1 where rowid = 6;

用到的操作符说明：

NSET2? 结果集(result set)收集，一般是查询计划的顶层节点

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

SLCT2? 关系的―选择‖（select）运算，用于查询条件的过滤

SSCN? 直接使用二级索引进行扫描

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

explain select c1 from t1 where c2 = 6;

用到的操作符说明：

NSET2? 结果集(result set)收集，一般是查询计划的顶层节点

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

CSEK2? 聚集索引数据定位

五、复杂连接查询

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

用到的操作符说明：

NSET2? 结果集(result set)收集，一般是查询计划的顶层节点

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

HASH2 INNER JOIN HASH 内连接

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

HASH2 INNER JOIN HASH 内连接

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

HASH2 INNER JOIN HASH 内连接

CSCN2? 聚集索引扫描

CSCN2? 聚集索引扫描

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

HAGR2? HASH 分组，并计算聚集函数

PRJT2? 关系的―投影‖(project)运算，用于选择表达式项的计算

HASH2 INNER JOIN HASH 内连接

CSCN2? 聚集索引扫描

CSCN2? 聚集索引扫描

no_use_cvt_var?? 不考虑变量改写方式实现连接，仅 OPTIMIZER_MODE=1 有效。

执行顺序 6->7->5->12->13->11->9->3

首先执行TEST5和TEST6的HASH连接，然后执行TEST7,TEST8的HASH连接并将连接结果进行HASH分组，再将两个结果再次进行HASH连接得到最终结果集。

六、多表连接的三种方式

一般来说：

等值连接条件一般会选择哈希连接；

非等值连接条件会采用嵌套连接；

连接列均为索引列时，会采用归并连接。

创建测试环境：

create table tab1(c1 int,c2 int ,c3 int);
create table tab2(c1 int,c2 int ,c3 int);
insert into tab1 select level,level,level from DUAL CONNECT by level <100000;
insert into tab2 select level,level,level from DUAL CONNECT by level <100000;
create index ind_tab1 on tab1(c1);
create index ind_tab2 on tab2(c1);
select * from user_indexes where table_name in ('TAB1','TAB2') ;

1、HASH JOIN（哈希连接）

Hash join的工作方式是将一个表（通常是小一点的那个表）做hash运算，将列数据存储到hash列表中，从另一个表中抽取记录，做hash运算，到hash 列表中找到相应的值，做匹配。

1）HASH2 INNER JOIN（HASH内连接）

explain select tab1.c1,tab2.c2 from tab1 , tab2 where tab1.c1=tab2.c1;

2)HASH LEFT JOIN2（HASH左外连接）

explain select tab1.c1 from tab1 left? join tab2 on tab1.c1=tab2.c1;

3) HASH FULL JOIN2（HASH 全外连接）

explain select tab2.c1 from tab1 full outer join tab2 on tab2.c1=tab1.c1 ;

全连接的查询结果是左外连接和右外连接查询结果的并集，即使一些记录关联不上，也能够把部分信息查询出来。

4）HASH LEFT SEMI MULTIPLE JOIN（多列not in）

explain select * from tab1 where (c1,c2) not in (select c1,c2 from tab2) ;

5）HASH LEFT SEMI JOIN2(HASH 左半连接)

子查询和非等值连接出现

explain select * from tab1 where c1 not in (select c1 from tab2)

?????? and c2 not in (select c2 from tab2);

6）HASH RIGHT JOIN2(HASH右外连接)

explain select t.c1 ,tab1.c3 from tab1 left join (select * from tab2 where c1=10)t on t.c1=tab1.c2 ;

2、MERGE JOIN（归并连接，也叫排序归并连接）

Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多

1) 模拟：（归并内连接）

连接列有索引且只需要返回索引列归并更合适

explain select tab1.c1 from tab1 , tab2 where tab1.c1=tab2.c1;

3、NESTED LOOP（嵌套循环连接）

Nested loops 工作方式是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，nested loops适用的场合是当一个关联表比较小的时候，效率会更高。

1）NEST LOOP INNER JOIN2（嵌套循环内连接）

explain select tab1.c1 from tab1 ,tab2 where tab1.c2>tab2.c2;

2）NEST LOOP LEFT JOIN2（嵌套循环左连接）

explain select tab1.c1 from tab1 left join tab2 on tab1.c2>tab2.c2;

七、补充下索引的知识点

聚集（clustered）索引，也叫聚簇索引。聚簇索引的索引和数据是存储在一起的。

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

说实话，看着有点拗口，用大白话说就是，我们的sql数据库是行数据库，数据是一行一行存储的，而聚集索引是个特殊的索引，相当于这一行行记录的物理编号，描述这一行行数据的物理存储顺序。所以，一张表只会有一个聚集索引。

除了聚集索引外的其他索引类型都属于二级索引。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。