达梦sql优化之执行计划

执行计划是什么呢？比如你执行一条sql语句，查询优化器会为这条sql语句设计执行方式，交给执行器去执行，查询优化器设计的执行方式就是执行计划。

EXPLAIN可以打印出语句的执行计划。

那么，执行计划主要是由什么组成的呢？答案是操作符(个人理解)。

执行计划是由各类操作符组成的一颗树，从内到外依次执行，缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外。

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

CSCN ：基础全表扫描(a)，从头到尾，全部扫描

SSCN ：二级索引扫描(b), 从头到尾，全部扫描

SSEK ：二级索引范围扫描(b) ，通过键值精准定位到范围或者单值

CSEK ：聚簇索引范围扫描© ，通过键值精准定位到范围或者单值

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

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

这里说明一下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 翻译成中文是书签查找

BLKUP2 官方文档说明是：定位查找

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数据库是行数据库，数据是一行一行存储的，而聚集索引是个特殊的索引，相当于这一行行记录的物理编号，描述这一行行数据的物理存储顺序。所以，一张表只会有一个聚集索引。

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

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