达梦数据库优化器&执行计划解读

dcps75321

已于 2024-03-21 10:15:47 修改

阅读量8.1k

点赞数 6

文章标签：数据库 database 性能优化

于 2022-04-12 21:25:57 首次发布

本文链接：https://blog.csdn.net/dcps75321/article/details/124120810

版权

说明：

1、达梦数据库执行计划

一条SQL语句在数据库中的执行过程或者访问路径的描述，通过执行计划，可以知道优化器对sql进行了哪些处理，使用了哪些方式去执行sql。执行计划看起来就像一棵树，执行过程为：控制流从上向下传递，数据流从下向上传递。

2、优化器

达梦数据库跟oracle、mysql一样，也有自己的优化器，优化器会根据需要执行的sql，计算出执行该sql使用的各种方案的代价（即时间），然后会选择一个代价最低的方案，去执行SQL。

3、查看执行计划

有2个地方，第一个是EXPLAIN返回的内容就是执行计划，第二个是通过执行号，可以调用ET工具，可以看到执行计划中操作符的实际开销。
此外，manager工具，可以选中需要查看的SQL语句，按F9查看它的执行计划。

准备：

操作系统最好带图形化界面，安装好DM8数据库，并新建一个单机实例。

新建一张表，并写入一定数据量，或使用现有数据表，注意根据实际情况备份该表后再进行。

1、EXPLAIN

查看执行计划，在需要执行的sql前，加上explain关键字即可，若使用的manager，还可以选定要执行的sql，按F9即可调出执行计划，返回的执行计划如下图：

执行计划就像一棵树，名叫左深二叉树，因为我使用的是最新版的开发版，因此默认使用新的优化器。

关于达梦数据库的优化器，有一个优化器控制参数OPTIMIZER_MODE，该参数意思是DM数据库优化器的模式，取值0或1，0表示使用老优化器，1表示使用新优化器，现最新版本默认为1，因此使用的是新优化器。该参数是动态参数（会话级），可使用以下命令查看
select * from v$parameter t where NAME = 'OPTIMIZER_MODE';

--TYPE是参数类型，SESSION表示动态参数(会话级)
--VALUE表示该参数现在的值
--SYS_VALUE表示内存中改参数的值
--FILE_VALUE表示数据库配置文件dm.ini中这个参数的值
--DESCRIPTION是该参数的说明
新优化器的执行计划就是一棵左深二叉树，控制流从上向下传递，数据流从下向上传递。本文整篇都会采用新优化器进行执行计划的讲解。

“关于新老优化器，可以查看该篇文章：关于达梦数据库的优化器参数_尼克老刘的博客-CSDN博客”

唠叨了那么多，重新说回explain执行计划

先对表字段进行说明：

名称：计划节点的操作符
附加信息：每个操作符执行的操作
代价：每个操作符花费的时间，这里以数字表示
结果集：每个操作符返回的结果条数
行数据处理长度：执行节点的字节数
描述：对每个操作符的简单描述

“名称”字段中操作符的说明：

NEST2：用于结果集收集的操作符
PRJT2：关系的投影运算，用于选择表达式的计算；
BLKUP2：二次扫描（回表）
SSEK2：二级索引扫描

常见的操作符还有：

SLCT：选择，用于查询条件的过滤；
AAGR：简单聚集，用于没有GROUP BY的COUNT、SUM等聚集函数的计算；
HASH JOIN：哈希连接，在没有索引或索引无法使用情况下的表的连接方式。

“更多操作符说明详见达梦社区：DM 执行计划解读 | 达梦技术文档”

性能优化 | 达梦技术文档 4.2.1章节，或安装达梦数据库（windows或Linux均可）后，在安装目录下找到doc目录，里面的官方文档《DM8系统管理员手册.pdf》里，“附录4”章节也有详细说明。

整个sql的执行计划的说明：
执行计划的每一行，就是一个计划节点，计划节点里的操作符，就是这个计划节点干了什么事。根据左深二叉树，从末尾往上看，最末尾就是最开始执行的操作。上图中，根据where条件中的id列，可使用id列的二级索引（即主键索引，操作符SSEK2），通过该索引过滤符合条件的数据行，得到需要查到的值。但该select语句还要求查询其他字段的值，因此需要进行回表操作，即二次扫描（操作符BLKUP2），回到聚簇索引中，读取其他字段的值，并将查询结果进行投影和收集（操作符PRJT2和NEST2）。

2、ET

前面讲到的执行计划，每个操作符和计划节点的代价，都比较抽象，为了提高效率，因此使用ET。

ET是达梦数据库自带的SQL 性能分析工具，能统计执行计划中，每个操作符的时间花费（将代价显示为具体的时间），从而定位到有性能问题的操作，指导我们去优化。

ET是默认关闭的，因此在使用ET之前，需要先开启：

--开启ET
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);

--关闭ET
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);

--查看ET是否开启
select * from v$parameter t where NAME = 'MONITOR_SQL_EXEC';
select * from v$parameter t where NAME = 'ENABLE_MONITOR';



--ENABLE_MONITOR，动态参数(系统级)
--MONITOR_SQL_EXEC，动态参数(会话级)

注意：ET会对数据库性能有影响，使用完后记得关闭以保证数据库性能不受影响。

在manager中，执行sql之后，可点击执行号：

或知道执行号后，也可以直接执行CALL ET(执行号)，例如：CALL ET(5909);

该sql的每个操作符的时间花费：

结果说明：

OP：操作符
TIME(US)：时间开销，单位为微秒
PERCENT：执行时间占总时间百分比
RANK：执行时间的耗时排序
SEQ：执行计划的节点号
N_ENTER：进入次数

操作符在上方《1、EXPLAIN》已有说明，这里不再重复。

3、SQL优化讲解

3.1、优化案例一

3.1.1、执行sql

select count(id)+1 from t_test where name = 'M40A6' and product_status = 'ready';

该sql的执行计划：

该sql的ET结果：

3.1.2、分析过程

从执行计划能看出，首先进行了全表扫描（CSCN2 即全表扫描），然后对查询结果进行条件过滤，过滤后剩余68行数据。再看ET结果，全表扫描耗时最多，占总耗时的92.67%，因此可以针对该where条件，进行创建索引，避免全表扫描，减少耗时。

3.1.3、进行优化

根据where条件，需要创建联合索引，创建联合索引时，会将数据量较少的字段放在开头，也就是最左边，其他字段根据数据量逐一从左往右排序，这么做的目的是减少扫描次数，从而提高使用索引的效率，此时最左边的字段就会有很好的过滤性。

select count(id)+1 from t_test where name = 'M40A6' and product_status = 'ready';--10,9154,645,47

select count(*) from t_test where name = 'M40A6';--5,4267,578,52
select count(*) from t_test where product_status = 'ready';--6,4685,660,68

根据计算后可得出，字段name的过滤性更好一点，因此使用它作为联合索引的最左侧，创建联合索引。

create or REPLACE index idx_name_type_01 ON t_test(name,product_status);

更新并收集索引的统计信息：

--更新索引统计信息
SP_INDEX_STAT_INIT('SYSDBA','IDX_NAME_TYPE_01');

--收集索引统计信息
select * from sysstats where id=(select id from SYSOBJECTS where name = UPPER('IDX_NAME_TYPE_01'));

“关于索引统计信息，可查看此文章，本文不再说明：使用索引统计信息(Index Statistics)优化查询语句，提高查询效率_小_爱的博客-CSDN博客如果你查询优化统计还没有太多的认识和了解，那么建议你从头开始看，如果你已经很了解，那么可以直接跳到下面去看本文的重点了。什么是查询优化统计信息？查询优化的统计信息是一些对象，这些对象包含与值在表或索引视图的一列或多列中的分布有关的统计信息。查询优化器使用这些统计信息来估计查询结果中的基数或行数。通过这些基数估计，查询优化器可以创建高质量的查询计划。例如，查询优化器可以使用基https://blog.csdn.net/Beirut/article/details/8756135”

3.1.4、查看优化结果

再次查看原sql的执行计划：

效果立竿见影！优化器经过计算后，决定使用联合索引index idx_name_type_01，代价从15降低到1，执行耗时从10毫秒降低至2毫秒。但是发现一个情况，操作符BLKUP2，这是二次扫描（回表）操作，虽然条数不多，但也占用性能，若数据量很多的情况下，可能会使索引失效，那我们接着分析为何会有二次扫描操作。

因为select中写了字段id，但是索引里没有id字段，因此达梦数据库需要二次扫描，获取id列的值……

(ˉ▽ˉ；)...

嗯……这里算是个失误，竟然没发现select里还会读取其他字段的数据。那么遇到这种情况，有2种解决方案：

把字段id从select里去掉。
将字段id加入到联合索引中。

首先方案1估计很难实现，毕竟业务需求，根据实际情况来吧。

那么方案2看起来是个不错的办法，可是字段id本来就是主键，有主键索引，将其加入联合索引，会导致联合索引占用更多的空间，因此也需要根据实际情况而定。

以下是更新联合索引后的执行计划：

可以看到少了一个执行节点，操作符BLKUP2消失了，即没有二次扫描操作。

3.2、优化案例二

3.2.1、执行sql

select count(*) from t_test where product_type like '%NB%';

该sql的执行计划：

该sql的ET结果：

3.2.2、分析过程

从上图可看出，首先执行了全表扫描，最末尾处的执行节点操作符CSCN2就是全表扫描的意思。因此代价也是花在了此处。但根据过往经历，where条件里的 like '%……%' 一般都是走不了索引的，只能全表扫。但是在达梦数据库中，有个参数提供了查询条件中%的优化策略，该参数叫LIKE_OPT_FLAG，意思是LIKE查询的优化开关，如下所示：

select * from v$parameter t where NAME = 'LIKE_OPT_FLAG';

--TYPE是参数类型，SESSION表示动态参数(会话级)
--VALUE表示该参数现在的值
--SYS_VALUE表示内存中改参数的值
--FILE_VALUE表示数据库配置文件dm.ini中这个参数的值
--DESCRIPTION是该参数的说明

如上图所示，LIKE_OPT_FLAG参数默认是开启状态，所以这里我就不用执行命令开启了。若VALUE这里显示为0，表示关闭，需要手动开启，执行如下命令：

SP_SET_PARA_VALUE(1,'LIKE_OPT_FLAG',1);

--最末尾的1可以改为31，根据情况而定。

这里说明下，参数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 物理存储结构 | 达梦技术文档”

3.2.3、进行优化

开启LIKE_OPT_FLAG后，根据文档说明，我们这情况属于“1”，会将其转化为POSITION()函数，因此根据此方案设计索引：

create or REPLACE index idx_product_type on t_test(position('NB',product_type));

并更新索引统计信息：

SP_INDEX_STAT_INIT('SYSDBA','IDX_PRODUCT_TYPE');

3.2.4、查看优化结果

再次查看原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之后，别忘了关闭它，以免影响数据库性能。

社区地址：https://eco.dameng.com