达梦数据库SQL执行计划常用操作符
一、如何查看执行计划
首先,执行计划是由各类操作符组成的一颗树,也就是排序好的操作符的展现形式,从内到外依次执行
(看执行计划一般看MANAGER中执行计划文本的方式,这样看的更详尽一点,计划可以拷贝到文本编辑工具UE,NOTEPAD++中,这样缩进更为明显)
一般的执行计划格式为
OP1
OP2
OP3
OP4
OP5
OP6
OP7
OP8
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
缩进越多的越先执行,同样缩进的上面的先执行,下面的后执行,上下的优先级高于内外,对于上面这个简单的例子,大家可以自己写一个执行顺序
这里做一些空行,看写的执行顺序是否和后面列出的一致
这个例子的执行顺序为
OP3->OP4->OP2->OP7->OP8->OP6->OPT5->OP1
- 1
这里我们举一个现实的例子,我们拟定一个这样执行计划的SQL
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
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;
- 1
- 2
- 3
这个例子(忽略/+no_use_cvt_var/)的执行计划,我们暂时不关注PRJT和NSET操作符,只看SQL的执行顺序
和前面的简单例子类似,执行顺序
6->7->5->12->13->11->9->3
- 1
换为人能理解的意思就是,首先执行TEST5和TEST6的HASH连接,然后执行TEST7,TEST8的HASH连接并将连接结果进行HASH分组,再将两个结果再次进行HASH连接得到最终结果集。
这个例子的SQL写法比较简单意义也是非常明确的,读懂SQL需要干什么可以把操作符顺序写下来不会很困难。同样的,只看到这个执行计划,我们需要能想出来这个SQL原本是什么样子。读懂SQL本身是关键,执行计划更多的是起一个提示作用,侧面告诉大家SQL需要做什么事情
能正常读取执行计划描述的执行顺序后,我们关注下执行计划各个节点的详细信息,执行计划中所有操作符的后面都会有一个三元组,如:
#CSCN2: [1, 9999, 4]
- 1
[1, 9999, 4]就是我们提到的这个三元组,3个数字分别表示该操作符的估算代价,该操作符的输出行数,该操作符涉及数据的行长。
#CSCN2: [1, 9999, 4] 表示的意义为,这是一个全表扫描操作,涉及的行数为9999,每场数据长度为4,整体代价估算为1。
我们将三元组中的第二项称为估算行数(card),在复杂查询中,估算行数对于执行计划以及SQL性能的影响很大。
二、统计信息
统计信息可以简单理解为将索引(包含原表ROWID聚簇索引)的某一列进行统计分析,列出其最大最小值,存在多少不同值,各个值存在多少个辅助信息。
对于没有统计信息的列,DM7简单的按照一定比例进行概率过滤。
涉及到的INI参数为:
SEL_RATE_EQU ,等值过滤选择率,默认0.025。
SEL_RATE_SINGLE, 一般条件选择率,默认 0.05。
来看例子
create table test10(id1 int,id2 varchar,id3 varchar,id4 varchar);
- 1
----方便起见,我们插入1W行数据,ID1从1-10000, ID2 为 0a - 4a, id3全为b, id为1c - 10000c
insert into test10 select level,level % 5 || 'a','b',level || 'c' from dual connect by level <= 10000;
- 1
----SEL20
SQL> explain select * from test10 where id1 = 5;
- 1
可以看到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';
- 1
SLCT的CARD为6,约等于10000 * 0.025 * 0.025 = 6.25
可以简单推测出存在多个条件,且不存在统计信息的情况下,CARD是多个选择率的乘积乘以下层输出行数。
----我们再来看一般条件
----SEL22
SQL> explain select * from test10 where id1 > 5;
- 1
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';
- 1
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);
操作已执行
- 1
- 2
- 3
- 4
- 5
收集完毕后,我们再看计划中的CARD值
SQL> explain select * from test10 where id1 = 5;
- 1
已用时间: 0.689(毫秒). 执行号:0.
----单列估算准确,ID1只存在一个为5的行
SQL> explain select * from test10 where id2 = '5';
- 1
已用时间: 0.779(毫秒). 执行号:0.
----单列估算准确,CARD最小为1,ID2不存在为5的行
SQL> explain select * from test10 where id1 = 5 and id2 = '5';
- 1
----多列估算准备,不存在满足两个条件的行
SQL> explain select * from test10 where id1 > 5;
- 1
----单列一般条件估算准确,9995个ID1 > 5
SQL> explain select * from test10 where id1 > 5 and id2 = '5';
- 1
----多列混合估算准确,不存在满足条件的行
由此可见,统计信息的收集可以大概率的修正对过滤行数的估算。