达梦数据库SQL优化之执行计划

之前介绍过达梦数据库的SQL执行计划操作符，具体文章为：

达梦数据库SQL执行计划常用操作符

本文将介绍一下执行计划，希望达到的效果是无论某个SQL是否找的到优化的方法，我们起码可以将执行计划中的每一部分与原SQL对应起来。执行计划是优化的重中之重，这里我们主要讲解执行计划如何读，需要注意哪些地方，为优化做下一定的基础。

一、执行计划怎么看

首先，执行计划是由各类操作符组成的一颗树，也就是排序好的操作符的展现形式，从内到外依次执行。（看执行计划一般看达梦管理工具中执行计划文本的方式，这样看的更详尽一点，计划可以拷贝到文本编辑工具UE，notepad++中，这样缩进更为明显)

一般的执行计划格式为：

     OP1
         OP2
              OP3
              OP4
         OP5
              OP6
                  OP7
                  OP8

缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外，对于上面这个简单的例子，其执行顺序为：

OP3->OP4->OP2->OP7->OP8->OP6->OPT5->OP1

这里我们举一个现实的例子，我们拟定一个这样执行计划的SQL

SQL> CREATE TABLE TEST5(ID INT);
SQL> CREATE TABLE TEST6(ID INT);
SQL> CREATE TABLE TEST7(ID INT);
SQL> CREATE TABLE TEST8(ID INT);
SQL> insert into test5 values(3);
SQL> insert into test6 values(4);
SQL> insert into test7 select level %100 from dual connect by level < 10000;
SQL> insert into test8 select level %100 from dual connect by level < 10000;
SQL> commit;
SQL> 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;

这个例子（忽略/*+no_use_cvt_var*/）的执行计划，我们暂时不关注PRJT和NSET操作符，只看SQL的执行顺序

和前面的简单例子类似，执行顺序

6->7->5->12->13->11->9->3

那么实际SQL的执行情况为：首先执行TEST5和TEST6的HASH连接，然后执行TEST7,TEST8的HASH连接并将连接结果进行HASH分组，再将两个结果再次进行HASH连接得到最终结果集。

这个例子的SQL写法比较简单意义也是非常明确的，读懂SQL需要干什么可以把操作符顺序写下来不会很困难。同样的，只看到这个执行计划，我们需要能想出来这个SQL原本是什么样子。读懂SQL本身是关键，执行计划更多的是起一个提示作用，侧面告诉大家SQL需要做什么事情。

能正常读取执行计划描述的执行顺序后，我们关注下执行计划各个节点的详细信息，执行计划中所有操作符的后面都会有一个三元组，如：

#CSCN2: [1, 9999, 4]

[1, 9999, 4]就是我们提到的这个三元组，3个数字分别表示该操作符的估算代价，该操作符的输出行数，该操作符涉及数据的行长。

#CSCN2: [1, 9999, 4] 表示的意义为，这是一个全表扫描操作，涉及的行数为9999，每场数据长度为4，整体代价估算为1。

我们将三元组中的第二项称为估算行数(card)，在复杂查询中，估算行数对于执行计划以及SQL性能的影响很大。

二、统计信息对执行计划的影响

统计信息可以简单理解为将索引（包含原表ROWID聚簇索引）的某一列进行统计分析，列出其最大最小值，存在多少不同值，各个值存在多少个辅助信息。

对于没有统计信息的列，达梦简单的按照一定比例进行概率过滤。

涉及到的INI参数为：

SEL_RATE_EQU ，等值过滤选择率，默认0.025。
SEL_RATE_SINGLE， 一般条件选择率，默认 0.05。

来看例子

SQL> create table test10(id1 int,id2 varchar,id3 varchar,id4 varchar);
--方便起见，我们插入1W行数据，ID1从1-10000， ID2 为 0a - 4a, id3全为b, id为1c - 10000c
SQL> insert into test10 select level,level % 5 || 'a','b',level || 'c' from dual connect by level <= 10000;
--SEL20
SQL> explain select * from test10 where id1 = 5;

可以看到CSCN涉及1W行数据，这个没有问题，但是过滤条件SLCT的CARD标注为250行(#SLCT2: [1, 250, 156])，这个和我们的预期是不一致的，因为不存在统计信息。系统按10000 * 0.025直接给出250的结果。

如果存在多个等值条件呢？

--SEL21
--我们这里保障列与值类型相同 id2 varchar = '5'
SQL> explain select * from test10 where id1 = 5 and id2 = '5';

 SLCT的CARD为6，约等于10000 * 0.025 * 0.025 = 6.25

可以简单推测出存在多个条件，且不存在统计信息的情况下，CARD是多个选择率的乘积乘以下层输出行数。

再来看一般条件

--SEL22
SQL> explain select * from test10 where id1 > 5;

SLCT输出CARD为500，和INI默认SEL_RATE_SINGLE参数0.05一致 10000 * 0.05 = 500

一般来说，除开等值条件外的所有过滤条件我们都认为是一般条件。

同样的，一般条件和等值条件的组合，没有统计信息的情况下，最终选择率依然是按乘积计算。

--SEL23
SQL> explain select * from test10 where id1 > 5 and id2 = '5';

SLCT CARD = 12 = 10000 * 0.05 * 0.025 = 12.5

现在我们收集统计信息，推荐收集统计信息的方式有两种

--收集单列统计信息
STAT 100 ON 表(列)
--收集SQL语句涉及列的统计信息
CREATE VIEW VA AS SQL语句;
CALL SP_SQL_STAT_INIT('SELECT * FROM VA')
SQL> stat 100 on test10(id1);
操作已执行
已用时间: 26.350(毫秒). 执行号:860.
SQL> stat 100 on test10(id2);
操作已执行
 
收集完毕后，我们再看计划中的CARD值
SQL> explain select * from test10 where id1 = 5;

由上图执行计划得知，单列估算准确，ID1只存在一个为5的行

SQL> explain select * from test10 where id2 = '5';

 由上图执行计划得知，单列估算准确，CARD最小为1，ID2不存在为5的行

SQL> explain select * from test10 where id1 = 5 and id2 = '5';

由上图执行计划得知，多列估算准确，不存在满足两个条件的行

SQL> explain select * from test10 where id1 > 5;

由上图执行计划得知，单列一般条件估算准确，9995个ID1 > 5

SQL> explain select * from test10 where id1 > 5 and id2 = '5';

由上图执行计划得知，多列混合估算准确，不存在满足条件的行

由此可见，统计信息的收集可以大概率的修正对过滤行数的估算。