一条SQL语句在数据库中的执行过程或者访问路径的描述,通过执行计划,可以知道优化器对sql进行了哪些处理,使用了哪些方式去执行sql。执行计划看起来就像一棵树,执行过程为:控制流从上向下传递,数据流从下向上传递。
达梦数据库跟oracle、mysql一样,也有自己的优化器,优化器会根据需要执行的sql,计算出执行该sql使用的各种方案的代价(即时间),然后会选择一个代价最低的方案,去执行SQL。
有2个地方,第一个是EXPLAIN返回的内容就是执行计划,第二个是通过执行号,可以调用ET工具,可以看到执行计划中操作符的实际开销。
此外,manager工具,可以选中需要查看的SQL语句,按F9查看它的执行计划。
操作系统最好带图形化界面,安装好DM8数据库,并新建一个单机实例。
新建一张表,并写入一定数据量,或使用现有数据表,注意根据实际情况备份该表后再进行。
查看执行计划,在需要执行的sql前,加上explain关键字即可,若使用的manager,还可以选定要执行的sql,按F9即可调出执行计划,返回的执行计划如下图:
执行计划就像一棵树,名叫左深二叉树,因为我使用的是最新版的开发版,因此默认使用新的优化器。
关于达梦数据库的优化器,有一个优化器控制参数OPTIMIZER_MODE,该参数意思是DM数据库优化器的模式,取值0或1,0表示使用老优化器,1表示使用新优化器,现最新版本默认为1,因此使用的是新优化器。该参数是动态参数(会话级),可使用以下命令查看
新优化器的执行计划就是一棵左深二叉树,控制流从上向下传递,数据流从下向上传递。本文整篇都会采用新优化器进行执行计划的讲解。
“关于新老优化器,可以查看该篇文章:关于达梦数据库的优化器参数_尼克老刘的博客-CSDN博客”
唠叨了那么多,重新说回explain执行计划
先对表字段进行说明:
“名称”字段中操作符的说明:
常见的操作符还有:?
SLCT:选择,用于查询条件的过滤;
AAGR:简单聚集,用于没有GROUP BY的COUNT、SUM等聚集函数的计算;
HASH JOIN:哈希连接,在没有索引或索引无法使用情况下的表的连接方式。“更多操作符说明详见达梦社区:DM 执行计划解读 | 达梦技术文档”
整个sql的执行计划的说明:?
? ? ? ? 执行计划的每一行,就是一个计划节点,计划节点里的操作符,就是这个计划节点干了什么事。根据左深二叉树,从末尾往上看,最末尾就是最开始执行的操作。上图中,根据where条件中的id列,可使用id列的二级索引(即主键索引,操作符SSEK2),通过该索引过滤符合条件的数据行,得到需要查到的值。但该select语句还要求查询其他字段的值,因此需要进行回表操作,即二次扫描(操作符BLKUP2),回到聚簇索引中,读取其他字段的值,并将查询结果进行投影和收集(操作符PRJT2和NEST2)。
前面讲到的执行计划,每个操作符和计划节点的代价,都比较抽象,为了提高效率,因此使用ET。
ET是达梦数据库自带的SQL 性能分析工具,能统计执行计划中,每个操作符的时间花费(将代价显示为具体的时间),从而定位到有性能问题的操作,指导我们去优化。
ET是默认关闭的,因此在使用ET之前,需要先开启:
注意:ET会对数据库性能有影响,使用完后记得关闭以保证数据库性能不受影响。
?
在manager中,执行sql之后,可点击执行号:
或知道执行号后,也可以直接执行CALL ET(执行号),例如:CALL ET(5909);?
该sql的每个操作符的时间花费:?
结果说明:
?操作符在上方《1、EXPLAIN》已有说明,这里不再重复。
该sql的执行计划:?
?该sql的ET结果:
从执行计划能看出,首先进行了全表扫描(CSCN2 即全表扫描),然后对查询结果进行条件过滤,过滤后剩余68行数据。再看ET结果,全表扫描耗时最多,占总耗时的92.67%,因此可以针对该where条件,进行创建索引,避免全表扫描,减少耗时。
根据where条件,需要创建联合索引,创建联合索引时,会将数据量较少的字段放在开头,也就是最左边,其他字段根据数据量逐一从左往右排序,这么做的目的是减少扫描次数,从而提高使用索引的效率,此时最左边的字段就会有很好的过滤性。
根据计算后可得出,字段name的过滤性更好一点,因此使用它作为联合索引的最左侧,创建联合索引。
?更新并收集索引的统计信息:
再次查看原sql的执行计划:
?效果立竿见影!优化器经过计算后,决定使用联合索引index idx_name_type_01,代价从15降低到1,执行耗时从10毫秒降低至2毫秒。但是发现一个情况,操作符BLKUP2,这是二次扫描(回表)操作,虽然条数不多,但也占用性能,若数据量很多的情况下,可能会使索引失效,那我们接着分析为何会有二次扫描操作。
因为select中写了字段id,但是索引里没有id字段,因此达梦数据库需要二次扫描,获取id列的值……
(ˉ▽ˉ;)...
嗯……这里算是个失误,竟然没发现select里还会读取其他字段的数据。那么遇到这种情况,有2种解决方案:
首先方案1估计很难实现,毕竟业务需求,根据实际情况来吧。
那么方案2看起来是个不错的办法,可是字段id本来就是主键,有主键索引,将其加入联合索引,会导致联合索引占用更多的空间,因此也需要根据实际情况而定。
以下是更新联合索引后的执行计划:
可以看到少了一个执行节点,操作符BLKUP2消失了,即没有二次扫描操作。
该sql的执行计划:
?该sql的ET结果:?
?
?
从上图可看出,首先执行了全表扫描,最末尾处的执行节点操作符CSCN2就是全表扫描的意思。因此代价也是花在了此处。但根据过往经历,where条件里的 like '%……%' 一般都是走不了索引的,只能全表扫。但是在达梦数据库中,有个参数提供了查询条件中%的优化策略,该参数叫LIKE_OPT_FLAG,意思是LIKE查询的优化开关,如下所示:
如上图所示,LIKE_OPT_FLAG参数默认是开启状态,所以这里我就不用执行命令开启了。若VALUE这里显示为0,表示关闭,需要手动开启,执行如下命令:
这里说明下,参数LIKE_OPT_FLAG,取值如下:
- 0:不优化;
- 1:对于LIKE表达式首尾存在通配符的情况,优化为POSITION()函数;对于LIKE表达式首部存在通配符,并且条件列存在REVERSE()函数索引时,优化为REVERSE()函数;
- 2:对于COL1 LIKE COL2 || '%'的情况,优化为POSITION()函数;
- 4:对于COL1 LIKE ‘A’||‘B%’的情况,优化为COL1 LIKE ‘AB%’;
- 8:对于可计算的LIKE表达式,优化为常量;
- 16:对于控制函数索引列的LIKE表达式,优化为BETWEEN…AND…表达式。
支持使用上述有效值的组合值,如31表示同时进行1、2、4、8和16的优化。“详见达梦社区:DM 物理存储结构 | 达梦技术文档”
开启LIKE_OPT_FLAG后,根据文档说明,我们这情况属于“1”,会将其转化为POSITION()函数,因此根据此方案设计索引:
?并更新索引统计信息:
再次查看原sql的执行计划:
?可以看到效果立竿见影!优化器经过计算后使用了创建的二级索引idx_product_type,代价从13降低到了1,再看下ET的结果:
?执行耗时从8毫秒降低至1毫秒左右,创建的索引使 like '%……%' 的查询效率大幅提高!
达梦数据库的优化器同oracle、mysql一样,都是计算出各种方案的代价,并选择一个代价最低的执行方式,去执行sql。
如上所述,在oracle、mysql中常用的sql优化经验,在达梦数据库也是基本适用的,包括常用的联合索引过滤条件,根据过滤性选择放在最左侧,遇到等于、大于、小于时,等于号的条件放在最左侧,注意查询时,where条件里的条件值,要与字段的数据类型对应,以防发生隐式转换,导致优化器计算后代价过高不走索引,比如 id = '10' ,因为id字段的类型为number,加了单引号的10,会被数据库认为是字符串,就会发生隐式转换,因此优化器可能会不走索引。
达梦数据库针对 like? '%……%' 的查询,提供了参数LIKE_OPT_FLAG,可以参考此参数给出的方案,进行sql优化。
使用disql时,执行计划的内容有所不同,但其实跟manager返回的是一样的,可以查看“社区文档:DM 执行计划解读 | 达梦技术文档”
? ? ? ? 最后,使用完ET之后,别忘了关闭它,以免影响数据库性能。