达梦8数据库性能优化思路浅谈

一、SQL优化的重要性

    作为一名DBA,SQL优化是我们工作中必不可少的技能，在投产的系统中，存在大量的SQL语句需要我们分析和快速做出处理。很是考验我们的经验。这些慢SQL的原因有很多，有SQL编码不规范，设计有缺陷，SQL场景未考虑全面，数据量未评估等，预先需要建立索引的表而未设计建立，需分表分区的未进行拆解，也有环境因素造成的缺陷。也有出厂测试之前没有问题，但实际部署到客户现场时候却出了问题。这些异常语句，如果不及时处理，会造成对数据库的一个性能瓶颈，直接影响着目标用户的使用和体验。所以数据库SQL的性能优化,快速处理显得尤为重要。

文章目标，希望读者掌握达梦SQL优化的基础，SQL性能分析思路，参数配置标准工艺化理念，性能辅助工具熟练应用。文章最后7，8章也整合了第三方的常见分析工具，辅助达梦数据库性能压测时候的一种分析手段。文章中没有过多对单个SQL性能优化深入讲解，主要原因还是部分SQL涉及公司代码不便发布，其次也是个人能力有限，也是在不断学习中。

• 预估执行计划生成及基础说明

我们以达梦8数据库为例，拿到一条SQL的时候，首先要下达梦手册中提出的有效SQL规范，及是否命中了特殊OR子句的不规范，是否用了复杂的正则表达式，避免重复很高的索引，UINON ALL 是否可以替换UNION操作等,某些场景INSTR函数导致的NEST LOOP效率等，再就是看下它的预估执行计划。预估执行计划，我们可以采用达梦客户端点击如下图解小按钮生成，也可以按快捷键F9生成，也可以在SQL前加EXPLAIN生成预估计划；

2.1、生成预估执行计划

达梦客户端直接点击菜单栏按钮或按F9生成：

 

执行EXPLAIN SELECT * FROM TEST_TAB1 WHERE DXID=11487150 生成：

 

2.2、操作符讲解

执行计划操作符，大家起初可能并不熟悉，这个可以参考达梦官方手册：DM8 DBA.pdf ，在附录3有执行计划操作符说明。常见操作符，需要掌握，比如：聚集索引扫描（CSCN2）,全表扫描(CSCN),索引扫描（SSEK）,二次回表(BLKUP)，除重（DISTINCT） ，二级索引定位（SSEK2）等等；多表关联，我们通常会遇到：(HASH链接)HASH JOIN，嵌套循环连接（NEST LOOP JOIN）,

还有MERGE 链接（MERGE JOIN ）；

大表和大表关联主要HASH JOIN更优，而如果小表和大表关联，通常是NEST LOOP JOIN会更优，实际情况都是和数据量有关，优化器大部分能选择到合适的操作符，少数情况需要人为干涉。

2.4、执行计划顺序讲解

执行计划我们可以理解是一个树型状，看懂执行计划顺序，会让我们更好的分析SQL性能问题，通常执行计划查看,大家可以在达梦客户端选择表格展示或文本方式，看执行顺序表格会更直观，分析执行计划，文本更适合，但是SQL的执行顺序，并不是简单的从下而上，她的解析是要遵守规律，具体大家可以这样理解：

1. 缩进越深的越先执行；
2. 同样缩进的上面的先执行，下面的后执行；

3、上下的优先级高于内外；

如上说明还是不够直观，我再图解一个样例让大家理解：

 

说明：越缩进越先执行，即最终缩进分叉了3，4，同样的缩进，从上由下优先级更高，所以这里的3先执行，3下面又有越深的越先执执行，又有1，2 ，所以整个排序执行顺序就出来了，开始看不习惯，大家多看几次就习惯了。

三、达梦性能分析思路

达梦数据库的优化，我平常使用的最多的还是，达梦数据库优化“三把斧”，在实践中发现可以排除93%（百分比数值只是个人以往解决性能问题的估算百分比）以上的性能问题，我说的三把斧主要三个方面（统计信息收集，合理的索引建立，DM.ini参数的优化），在使用三把斧过程中，我们首先要预检查下SQL，以及SQL编写的初级错误问题，例如：OR语句，困难正则表达式，未加过滤条件，笛卡尔，隐式转换，简单先过一遍。

在实际过程中，大部分SQL优化可以通过收集统计信息，索引新增，索引调整，达梦DM.INI参数调整等得到优化处理，而进阶一些的比较难分析的，就涉及到了增加HINT，SQL改写，在处理过程中，都需要根据具体SQL来判断，常见的有关联临时表查询，层次查询等容易需要Hint辅助，文章中给出浅显的例子来说明，实际优化过程中，还是需要不断的去实践，根据不同的SQL，分析执行计划，这样才能积累更加丰富的经验。

3.1、统计信息收集基础讲解

达梦数据库是基于代价的优化器，达梦数据库统计信息不准，会影响到执行计划的估算，导致SQL解析到错误的执行计划，如何判断统计信息有没有收集呢？

我们看如下SQL，主要看NUM_ROWS字段，如果没有收集通常是空，第二个LAST_ANALYZED 代表收集的时间，如果没有收集这个字段也是空，第三个TEMPORARY字段附带说明，N表示不是临时表，Y表示是临时表，通常如果TEMPORARY=Y 是不用收集的，

这个大家查询的时候注意下，临时表无需收集统计信息；

用例讲解：

SELECT NUM_ROWS,LAST_ANALYZED,TEMPORARY 

   FROM USER_TABLES WHERE TABLE_NAME='TEST_TAB1';

我们需要对表进行查询，查询语句如下：

 

当一个表缺失了统计信息我通常用如下语句修复：

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_TAB1',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO'); 

END;

DBMS_STATS.GATHER_TABLE_STATS参数说明：

以上参数注释：

第一个参数：USER是所属用户，如果你要指定其它用户，单独填写，默认USER是当前用户下的表；

第二个参数：就是你所要收集的表；

第三个参数：一般不是分区表名填写NULL即可，默认也是 NULL，区分大小写；

第四个参数：采样百分比，收集的百分比，范围为 0.000001~100，默认系统自定;第五个参数：TRUE,保留参数，是否使用随机块代替随机行，默认我们填写 TRUE即可,第六个参数：控制列的统计信息集合和直方图的创建的格式默认我们填写FOR ALL  COLUMNS SIZE AUTO即可，表示所有列收集，需要深入理解的可以参考达梦官方手册：DM8 - System Packages.pdf，平常我比较常用的还是DBMS_STATS.GATHER_TABLE_STATS；其次官方也提供了一些函数;

3.2、统计信息收集简单案例

什么时候需要收集统计信息？

    通常执行计划估算数据量不准的时候就需要收集了，相差不大一般没有影响，如果比较大就要收集了，否则会影响性能；这里用以下例子说明，只是对表未收集统计信息导致执行计划估算有差异的案例：

--步骤1：直接创建2个表，一般新创建的表统计信息是不会自动收集的（否则开启AUTO_STAT_OBJ全表监控的自动收集），我们查一下它的执行计划，这个时候结果集应该是估算有差异的；

CREATE TABLE  TEST1208_1 AS 

select * from dba_TAB_COLUMNS  WHERE TABLE_NAME='ALL_ALL_TABLES';

CREATE TABLE  TEST1208_2 AS

select * from dba_TAB_COLUMNS  WHERE TABLE_NAME='ALL_ALL_TABLES';

--客户端按F9,查看预估执行计划：

 

步骤2：获取真实执行计划：

DISQL登录当前用户

E:\dmdbms\bin>DISQL SYSDBA/[email protected]

然后执行如下命令：

ALTER SESSION SET 'MONITOR_SQL_EXEC'=1;

set autotrace trace;

SELECT A.COLUMN_NAME FROM TEST1208_1 A ,TEST1208_2 B WHERE A.COLUMN_NAME=B.COLUMN_NAME;

 

最后展示服务端执行计划：

 

从服务器上的执行计划这里可以看出，实际56，估算174;

步骤3：达梦客户端，我们对这连个表统计信息进行收集；

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST1208_1',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO'); 

DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST1208_2',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO'); 

END;

/

步骤4：客户端F9,我们再来看预估执行计划：

 

收集统计信息后，预估执行计划是已经正确了；

步骤5：我们重复步骤2看真实SQL计划，那继续再看收集统计信息后执行计划情况：

 

发现并没有变化，这是为什么呢？

我们思考一下就会想到，因为之前在未收集统计信息的时候，我们执行了一遍这个SQL，所以SQL缓存了计划，那遇到这样的情况，我们应该对这个SQL的缓存计划进行清理;

步骤6，清理方法如下：

数值1034139736是通过SELECT * FROM v$cachepln where SQLSTR LIKE