锐客网

  1. 首页 > 睿知 > it技术 > >

达梦SQL优化——基础篇

在数据库的使用中，书写正确的SQL语句只是完成了万里长征的第一步。事实上，在DBA的日常的工作中，SQL优化占据了很大的一部分的内容，本文就将给大家介绍一些SQL优化有关的基础知识。
1.基本概念
在理解如何优化SQL语句之前，我们首先要了解几个基本概念
1.1执行计划
执行计划是SQL语句的执行方式，由查询优化器为语句设计的执行方式，交给执行器去执行。在SQL命令行使用EXPLAIN可以打印出语句的执行计划。
1.2操作符
操作符是SQL执行的基本单元，所有的SQL语句最终都是转换成一连串的操作符最后在服务器上执行，得到需要的结果，
操作符也是读懂执行计划的基础，这里列举一下经常出现的操作符
CSCN SSCN SSEK CSEK BLKUP
这些操作符是SQL查询数据的原始来源，SQL里面出现的基表，都会出现在这些操作符的描述中，通过这些操作符来确定对应的表在执行计划中在何时以什么样的方式进入。
下面列出操作符的具体含义
CSCN ：基础全表扫描(a)，从头到尾，全部扫描
SSCN ：二级索引扫描(b), 从头到尾，全部扫描
SSEK ：二级索引范围扫描(b) ，通过键值精准定位到范围或者单值
CSEK ：聚簇索引范围扫描? ，通过键值精准定位到范围或者单值
BLKUP ：根据二级索引的ROWID 回原表中取出全部数据(b + a)
更多的操作符解释，可以参见DM7系统管理员手册附录4《执行计划操作符》
2.搭建实验环境
创建表T1并录入数据，相关SQL语句如下
SQL> CREATE TABLE T1(C1 INT,C2 INT);
SQL> insert into t1 select level,level from dualconnect by level < 10000;
3.检验执行计划
–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);
再看下面这个语句的计划
–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，无疑，多数情况下这样是比较有效率的。
【达梦SQL优化——基础篇】另外一点，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]
确实如此
聚簇索引是比较特殊的索引(对应操作符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);
那么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)
##这里查询中需要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
达梦SQL优化——基础篇(2) 01
多表连接操作符
在做多表连接查询时，我们可能会碰到的SQL操作符有以下几种类别：
NEST LOOP 嵌套循环连接
HASH JOIN 哈希连接
INDEX JOIN 索引连接
MERGE JOIN 归并连接
查询中出现的一般都不只一张表，不同的表会有一定的关系，处理多张表时就会涉及到这些操作符，此处我们只看两张表的情况，多表的情形可以类推。
02
搭建测试环境
构建测试用表并录入数据
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’);
03
相关测试
NEST LOOP INNER JOIN :最基础的连接方式，将一张表的一个值与另一张表的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行
达梦SQL优化——基础篇
文章图片

SEL9中的/+ENABLE_HASH_JOIN(0)/ 为优化器提示，此处是对ini进行语句级动态提示，意为不启用hash连接。
这里T1中存在5行数据，T2中存在8行数据，NEST LOOP JOIN 就是将这两个表无条件组成一张5 * 8 = 40 行的表，然后对这40行的表依次筛选出 T1.ID = T2.ID的数据（SLCT操作符）。
不难看出，这种方式是我们比较不希望看到的，如果T1,T2表非常大，那么生成的表会非常大，同样上层过滤条件需要执行的次数也非常多。输出上，结果集按左表（T1）涉及的索引有序
HASH JOIN：没有索引的情况下，大多数连接的处理方式，将一张表的连接列做成HASH表，另一张表的数据向这个HASH表匹配，满足条件的返回
计划的形式一般如下
达梦SQL优化——基础篇
文章图片

两张表进行等值连接时会默认选择HASH JOIN。以一张表的连接列为Hash键，构造HASH表，另一张表的连接列进行HASH探测，找到满足条件的记录。由于HASH命中率高，因此，在大数据量情况下，HASH JOIN的效率较NEST LOOP 会高很多，主要的计算量有三个部分
1.对左右表的全表扫描（T1,T2）
2.HASH 表的计算 （取决于HASH算法的计算复杂度）
3.右表(T2)每行数据进行匹配
由于所有的输出都是在扫描右表时完成的，HASH JOIN 的输出是按右表涉及的索引有序的
INDEX JOIN: 将一张表的数据拿出，去另外一张表上进行范围扫描找出需要的数据行，需要右表的连接列上存在索引
达梦SQL优化——基础篇
文章图片

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

需要同时SSCN 两条有序索引，将其中满足条件的值输出到结果集，效率比NEST LOOP 要高。这里的输出是按T1 的索引有序的。
SPL：某一张表输出一行结果后，带入到另一个表中进行执行，满足条件则输出
达梦SQL优化——基础篇
文章图片

在这里两张表的情况下，我们看到首先是对T1进行扫描获取到数据，然后每一行结果放到T2中进行过滤（SEEK I_TEST2 scan_range[var1,var1]），两张表的情况下，这样的处理方式和INDEX JOIN 基本类似，但在一些更复杂的情况中不能使用INDEX JOIN 的时候，这样的处理方式有助于提升处理效率。
达梦SQL优化——基础篇(3) 在前两期的分享中，我们介绍了在做单表及多表查询时用到的SQL操作符。本次，我们就来讲一讲过滤和分组排序时用到的操作符。
过滤条件：SLCT
这类操作符比较简单，是对结果集进行过滤，需要注意的是操作符的描述信息，从描述信息中我们可以看到对于下层操作有哪些可用的过滤条件，这些条件往往是优化方向的来源。
达梦SQL优化——基础篇
文章图片

需要关注的是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列上存在索引，可能也不能进行范围扫描，因为索引范围扫描的输入要求是和索引列上的类型相同，我们验证一下
达梦SQL优化——基础篇
文章图片

确实，在存在索引（ create indexi_index3 on t2(id)）的情况下，单列等值查询也没有走索引进行查询，这个在此也只说明一个大致原理，不同类型数据进行比较时会先选取一种比较类型，再确定比较方法。比如在此例中，比较 ID(VARCHAR)= 5(INT),服务器优先选择把类型转换成INT 进行比较，所以导致ID列需要做类型转换，从而不能利用索引（没有索引存储了转换之后的数据）。碰到这种情况，我们需要把索引列的对比对象转换为和索引列一样的格式
达梦SQL优化——基础篇
文章图片

另外一个条件ID NOT LIKE ‘%c%’，转换为EXP11 <= 0 ,实际上是把NOT LIKE 转换成了 INSTR(ID,‘c’) <= 0, 原理在此暂不多赘述，我们希望做到的是
对于SLCT描述项中的每一个，都能在原始SQL中找到对应的条件，并看下是否存在优化的可能性
分组：HAGR,SAGR
这类操作符都是对取到的数据做一些处理，或归并，或排序，而归并和排序在某些情况下是互通的我们先看这些操作符出现的基本情况。存在GROUP 的语句，大概率会出现这两个之一
SQL> create table t4(id int,id1varchar);
SQL> insert into t4 select level,levelfrom dual connect by level < 10000;
SQL> commit;
达梦SQL优化——基础篇
文章图片

HAGR是最基础的分组方式，HASH AGR操作，对于没有优化条件的分组语句，一般都会按这种方式进行分组，分组原理和HASH INNER JOIN 的方式类似,将原表数据取出，每个计算FOLD，发现有FOLD相同，且满足后续条件的合并为一组（,不难发现，如果基表数据非常庞大时，HAGR的计算量是不容忽视的，那么满足一定条件的情况下，我们可以利用有序性走SAGR操作符
SQL> create or replace index i_test4 ont4(id,id1);
达梦SQL优化——基础篇
文章图片

这里出现SAGR操作符，说明下层的输出是按分组列排序的，下层为SSCN I_TEST4,而I_TEST4为(ID,ID1)组合索引，按照ID有序，满足SAGR条件
SAGR， SORTED AGR操作，不同于HASH AGR,由于下层数据有序，同一分组的数据按照顺序取出就行，节省了大量的计算
排序：SORT:
SORT是做排序操作时使用到的操作符，我们可以进行如下实验。
达梦SQL优化——基础篇
文章图片

可以看到，这里并没有出现SORT操作符。如前文所说，索引I_TEST已经是以ID有序的了，所以在下层的SSCN中已经把排序的任务做完了。如果我们换一列来测试。
SQL> explain select id,id1 from t4order by id1 desc;
达梦SQL优化——基础篇
文章图片

如果是对id1列进行排序，则在SSCN之后，还需要针对id1做一次SORT，这对数据库也是一笔不小的开销。所以在实际运用中，我们也可以想办法利用索引的有序性来完成排序的操作。

    推荐阅读


    上一篇：达梦数据库获取慢sql

    下一篇：海都校园故事之初识独柏（上）