达梦SQL优化及执⾏计划解读
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 VARCH AR(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]; INDEX33555571(T1)
–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)
–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)
–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]
–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
–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)
–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)
–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]
–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)
–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]
–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]
–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)
⾄此，主要的执⾏计划操作符就介绍的差不多了，更多的操作符解释可以参考：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'
sql语句优化方式6#CSCN2: [1, 10000, 148]; INDEX33555571(T1)
7#CSCN2: [1, 10000, 148]; IDX_C1_T2(T2)
我们可以加上索引来进⾏优化：
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');
优化后执⾏计划：
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)
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)
需要注意：如果不是等值连接则会⾛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)
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)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。