DM数据库的SQL优化的一些技巧

        更多达梦数据库相关问题，请前往达梦技术社区 https://eco.dameng.com/ 

通常DM达梦数据库在完成安装部署的基础工作后，需要进行一些数据库参数方面的配置，以进行让数据库实例运行时发挥最大的性能。

        但是当系统部署安装是在实际环境中面对一个行业的业务系统时，在数据库端完成配置后，将数据库调整成最佳的运行效率时，并不能总是让业务系统运行显示出令人满意的效果，这时大多数情况是因为业务访问数据库的SQL语句运行效率问题。

        通常为了配合相应的SQL语句访问的表的方式，为相关表进行一些相关的创建索引或收集统计信息等其他数据库层面的操作，但同时也要在应用层面还要对那些不合理，不适宜的SQL进行改写。        

在SQL优化方面有几个常用的名词和术语。

谓词：	是SQL语句查询中搜索和筛选的条件语句。 SQL 语句中的WHERE 和having 和on 子句后面是会出现谓词。
基数：	是相对于表的某一列来说得。 通常是指对表的某一列，表的所有数据行的该列的不同值得个数，可以使用 select distinct 得出某列得基数。 比如： SELECT  COUNT(DISTINCT NAME)  FROM  SYSOBJECTS;
筛选率：	是指对于SQL语句中得谓词来说的。 通常是指SQL语句谓词选择出来的符合筛选条件的表种数据行数量，占表中总数据行数量的百分比，就是筛选率。 举例来说： 假设要给表 TAB_TEST1, 中有一个列名是sex，意思是性别字段，其中的字段值是男和女。 如果这个表里有100行数据，sex字段有50行数据是男，有50行数据是女，那么这个字段对于where谓词中有SEX字段的SQL语句来说，筛选率就是50%。

SQL优化可以整体上分为2部分：

一部分在获取数据行时进行的，一部分是在获取数据行之后进行。

对于在获取数据时进行的SQL优化部分有两个主要的方面：

一个是谓词上筛选字段的索引，另一个是多表查询是的连接方式。

改写SQL要适合关系型SQL语言的特点，遵循一些成熟的优化技巧可以借鉴。

比如最常见的对于between的谓词重写： 对于between 的重写：要使用 <=  and  >= ，要包含值在内。 对于not between 的重写: 要使用 < or >，不能包含值在内。

比如：谓词的使用优先顺序是： equal类型 >  RANGE类型  >   in 和exist 类型   >   like 类型 。 尽量将区间的谓词查询改为between 的谓词查询；

在经常重复查询某些表数据的情况下，可以引入创建并使用临时表； 示例： WITH  AAA_TMP as         (                 SELECT NAME, ID, PID, CRTDATE FROM SYSOBJECTS SO WHERE SO.ID < 30         ) SELECT ID FROM AAA_TMP;

注意SQL查询语句的谓词中返回NULL 词的情况的处理； 通常编写SQL语句在多数情况下可能不会遇到这种情况，所以开发人员往往忽视了语句中的谓词遇到null值得处理。所以在编写SQL 时要合理考虑NULL 值得处理，当心列为NULL ,也要当心返回NULL。

SQL语句中慎重编写case表达式； 因为case表达式的成本比较高，如果使用case表达式，要注意受限的顺序进行编写。

注意调优时order by 对索引的影响作用；

对于某些业务，只是浏览几页得数据结果，而不需要具体明确的结果集，这种SQL语句最好使用限定方式，每次返回适当得固定行数数据，比如设置100行，如果需要可以交互改写等。

根据多表的连接结果集中是否有重复行，来选择使用连接还是子查询。 子查询适合多个表查询的结果集中有重复的情况。

SQL语句注意表的处理顺序； 通常希望返回行数最少的表做为起始表。 使用内连接的方式的表影响不大，主要是使用外连接方式的多个表。

当需要验证表中是否有数据时，不要使用select count(*) 验证是否为空； 使用 top 1 或者 limit 1 ，进行验证表中是否有数据。

减少使用having 进行条件筛选，尽可能使用where谓词替换having 得条件；

在SQL语句调优时，可以增加 test.PKEY=  test.PKEY，其中test表是起始表; 注意，只有当一个SQL语句查询缓慢，需要性能调优时才这样写。 编写正常的业务逻辑SQL查询语句时不应该增加  test.PKEY=  test.PKEY 这种方式，这个写法不能保证SQL查询语句可以更高的执行，只是让优化器增加了一个访问路径的可能性。

如果SQL语句中必须使用了 IN 表达式，注意IN 列表的排序；

一些SQL优化方面的技巧：

1、利用最佳实践给SQL相关的表创建合理、优秀的索引。

比如一些创建索引的实践建议：

对于在WHERE 语句中and 使用的多个列，使用组合索引比较好；

索引中包含多个列，列的顺序比较重要，通常第一个列字段要是一个高基数的列。

对于order by 、group by 、distinct 语句使用的列创建索引；

SQL语句的WHERE 谓词尽量首先使用筛选率最高的谓词；

主键和外键列要创建索引；

最后要说明的是：

创建索引要基于SQL 而不是基于表对象。然而对于很多客户系统却要求在在创建表之后还没有执行SQL 就要DBA创建索引，这是不合适的。

2、SQL语句重写之在where 谓词的列上不要使用函数；

示例：

SQL语句： SELECT * FROM SYSOBJECTS WHERE YEAR(ID)=20; 执行计划：

SQL语句： SELECT * FROM SYSOBJECTS WHERE ID BETWEEN '01' AND '20' ; 执行计划：

从以上两条SQL语句的执行计划可以看出，第一条SQL语句的ID字段上使用了year函数，因此没有是用在ID字段上创建的索引。而第二条SQL语句是用了between 范围条件筛选ID字段，执行计划选择了SSEK2索引扫描； 

3、SQL语句重写之在where 谓词的列上不要进行数学计算；

示例：

SQL语句： SELECT * FROM SYSOBJECTS WHERE ID/10 = 9 ; 执行计划：

SQL语句： SELECT ID FROM SYSOBJECTS WHERE ID = 360/4; 执行计划：

从以上两条SQL语句的执行计划可以看出，第2条SQL语句走了ID字段上的索引。而第一个SQL语句由于在索引的ID字段上进行了计算，所以不能够是用索引。

4、SQL语句重写之SELECT 字段部分只写必需的列，即SELECT 最小化。

因为执行优化器首先会判断尤其是在应用代码里减少SELECT * 的SQL语句的使用。

示例：

SQL语句： SELECT * FROM SYSOBJECTS WHERE ID = 10 ; 执行计划：

SQL语句： SELECT ID FROM SYSOBJECTS WHERE ID = 10 ; 执行计划：

从以上两条SQL语句的执行计划可以看出，第2条SQL语句select选取了索引的字段值，因此执行过程中不需要产生BLKUP回表扫描获取其他不需要的字段。可以充分开出SELECT 选择字段不同而执行计划不同。

注：示例只是为了说明查询计划的不同，不是具有实际意义的SQL语句改写。

5、SQL语句重写之SQL语句中少用distinct函数 或者尽量不用；

DISTINCT 函数会进行一次排序，SQL语句中成本最高的函数之一。

如果必须使用distinct 来实现业务要求，可以使用某些其他方法进行改写，比如group by 和 exists 语句。

示例：

SQL语句： SELECT DISTINCT NAME FROM SYSOBJECTS;

SQL语句： SELECT NAME FROM SYSOBJECTS GROUP BY NAME; 执行计划：

也要说明一下如果distict 是在一个索引列上，没有引起排序，这个查询也可能不会导致效率低下。

注：示例只是为了说明查询计划的不同，不是具有实际意义的SQL语句改写。

6、SQL语句重写之使用UNION ALL  改写UNION；

因为union 会产生排序，来消除重复，而排序是SQL执行成本比较高的动作，

而且大多数业务需求也不需要这种去重的动作。

如果真的要预防重复，应该在其他业务模块保证重复的唯一性，

而不是在不相关的的模块执行SQL语句来增加一个验证成本。

 示例：

SQL语句： SELECT NAME ,ID FROM SYS.SYSOBJECTS SO UNION  SELECT NAME ,ID FROM SYS.SYSOBJECTS SOS 执行计划：

SQL语句： SELECT NAME ,ID FROM SYS.SYSOBJECTS SO UNION ALL SELECT NAME ,ID FROM SYS.SYSOBJECTS SOS 执行计划：

说明一下UNION 语句引发执行计划使用了distict ，引起排序。而将这个查询使用UNION ALL后，从执行计划中可以看到不使用DISTINCT进行排序。

注：示例只是为了说明查询计划的不同，不是具有实际意义的SQL语句改写。

7，SQL 语句重写之在WHERE条件中谓词使用了<> 时候，谓词是不走索引的。

示例：

SQL语句： SELECT ID FROM SYSOBJECTS WHERE ID = 99 ; 执行计划：

SQL语句： SELECT ID FROM SYSOBJECTS WHERE ID <> 99; 执行计划：

从示例中可以看出，ID=99 的where条件走的是SSEK2索引扫描，

而ID<>99 的where条件不走索引，是用的是表扫描，对与数据量大的表查询，会导致效率低下。

注：示例只是为了说明查询计划的不同，不是具有实际意义的SQL语句改写。

8、SQL语句中减少使用IN ，可以使用exists 改写；

IN 和 exists 能达到同样的查询效果，但是他们的查询性能和执行却不一样，

所以尽量在相同逻辑的查询情况下使用exists 改写in 语句。

示例：

SQL语句： SELECT NAME,ID,TYPE$ FROM SYSOBJECTS SO WHERE SO.TYPE$='SCH' AND SO.NAME IN (SELECT NAME FROM SYSOBJECTS SOB ); 执行计划：

SQL语句： SELECT NAME,ID,TYPE$ FROM SYSOBJECTS SO WHERE SO.TYPE$='SCH' AND EXISTS (SELECT NAME FROM SYSOBJECTS SOB WHERE SO.ID = SOB.ID ) ; 执行计划：

从示例中可以看出，使用了exists和使用in的结果是一样的，但是使用exist因为需要添加一系列条件，使得执行计划有机会选择合适的索引。

注：示例只是为了说明查询计划的不同，不是具有实际意义的SQL语句改写。

9、在SQL语句中减少使用NOT 这种非逻辑得查询，尽量进行等价改写；

示例：

SQL语句： SELECT ID,PID FROM SYSOBJECTS WHERE NOT PID = 10 ; 执行计划：

SQL语句： SELECT ID,PID FROM SYSOBJECTS WHERE PID >= 11 AND PID<=9;  执行计划：

从示例中可以看出，是用not 条件不走索引，使用表扫描。对数据量大的表查询，会导致效率低下。

注：示例只是为了说明查询计划的不同，不是具有实际意义的SQL语句改写。

SQL 重写注意事项：

1、SQL重写前后要把执行计划记录下来，将重写前后的执行计划进行对比确保性能提升的准确；

2、对于复杂的SQL，特别长的SQL语句或者涉及多个表连接的大SQL进行重写，务必要确认改写后执行sql语句的结果， 和原来的SQL 得到的数据结果是一样的。

更多DM达梦数据库技术知识，请到 达梦技术社区 https://eco.dameng.com