达梦：SQL调优

        SQL调优作为数据库性能调优中的最后一个环节，对查询性能产生着直接的影响。在进行正式的SQL调优前，用户首先要关注下列几点：

1．达梦数据库安装时的配置参数是否符合应用场景需求；

2．达梦数据库的INI配置文件中各项参数是否已经处于最优配置；

3．应用系统中数据库设计是否合理。

        本章将介绍定位高负载的SQL语句的方法，利用自动SQL调整功能进行优化，以及如何开发有效的SQL语句和使用优化器提示来影响执行计划。

.

一、调优目标

SQL调优的整体目标是使用最优的执行计划，这意味着IO以及CPU代价最小。具体而言调优主要关注下列方面：

1.1 表扫描

        通过查看执行计划，如果计划中对某大表使用了全索引扫描，那么用户需要关注是否存在着该表的某个查询条件使得过滤后可以淘汰至少一半的数据量。通过添加相应的索引，全索引扫描可能被转换为范围扫描或等值查找。添加的二级索引可以包含该表上所有被选择项以避免BLKUP2操作符的查找操作带来的第二次IO开销，但无疑这会增加二级索引的大小。用户需权衡二者的利弊以选择正确的处理方式。

1.2连接操作的顺序和类型

        多表连接时，不同的连接顺序会影响中间结果集数量的大小，这时调优的目标就是要找到一种能使中间结果保持最小的连接顺序。对于给定的一个连接或半连接，DM可以用HASH连接、嵌套循环连接、索引连接或者是归并连接实现。通过分析表的数据量大小和索引信息，SQL调优目标是选择最适宜的操作符。对半连接而言，HASH连接还可细分为左半HASH和右半HASH。用户可以通过始终对数据量小的一侧建立HASH来进行调优。

1.3分组操作

        分组操作往往要求缓存所有数据以找到属于同一组的所有数据，在大数据量情况下这会带来大量的IO。用户应该检查SQL查询和表上索引信息，如果可以利用包含分组列的索引，那么执行计划就会使用排序分组从而不用缓存中间结果。

二、确定高负载的SQL

        在打开监控开关（ENABLE_MONITOR=1、MONITOR_TIME=1）后，可以通过查询动态视图V$LONG_EXEC_SQLS或V$SYSTEM_LONG_EXEC_SQLS来确定高负载的SQL语句。前者显示最近1000条执行时间较长的SQL语句，后者显示服务器启动以来执行时间最长的20条SQL语句。例如：

SELECT * FROM V$LONG_EXEC_SQLS;

 或者

SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS;

三、自动SQL调整

        使用查询优化向导工具，输入需要进行调整的SQL语句，向导工具将在分析完执行计划后给出推荐索引的提示。用户只需按提示建立相应索引即可。

四、开发有效的SQL语句

        SQL语言是一种相当灵活的结构化查询语言。用户可以利用多种不同形式的查询语句完成相同的查询功能。为了使执行效率达到最优，用户需要参考以下原则以开发出有效的SQL语句：

4.1 避免使用OR子句

        OR子句在实际执行中会被转换为类似于UNION的查询。如果某一个OR子句不能利用上索引则会使用全表扫描造成效率低下，应避免使用。如果OR子句都是对同一列进行过滤，用户可以考虑使用INVALUELIST的过滤形式。如：

SELECT...WHERE CITY='SHANGHAI'OR CITY='WUHAN'OR CITY='BEIJING';

调整为

SELECT...WHERE CITY IN('SHANGHAI','WUHAN','BEIJING');

4.2 避免使用困难的正则表达式

        在SQL语言中，LIKE关键字支配通配符匹配，含通配符的表达式被称为正则表达式。有的正则表达式可以自动优化为非匹配的。

        例如：a LIKE 'L%'可以优化为a>='L' AND a<'M',这样就可以用到a上的索引。即使没有索引，转换后的比较也更快。

        再如：a LIKE 'LM_'可以转化为a>='LM' AND a< 'LN' AND a LIKE 'LM_'。虽然仍然包含着通配符匹配，但大大缩小了匹配的范围。所谓困难的正则表达式是指开头和结尾都为通配符的正则表达式，如'_L%'、'%L_'，优化器没办法缩小它们的匹配范围，也不可能用到索引而必须使用全表扫描。因此要尽可能避免这样的正则表达式。如果仅仅是开头为通配符，用户可以在列a上建立REVERSE(a)这样一个函数索引，利用函数索引反转待匹配项从而使用函数索引进行范围扫描。

4.3 灵活使用伪表(SYSDUAL)

4.4 SELECT项避免“*”

        除非用户确实要选择表中所有列，否则SELECT * 这种写法将让执行器背上沉重的负荷。因为每一列的数据不得不自下往上层层向上传递。不仅仅如此，如果用户查询的是列存储表，那么列存储所带来的IO优势将损耗殆尽。任何时候，用户都要了解表结构和业务需求，小心地选择需要的列并一一给出名称，避免直接用SELECT *。

4.5 避免功能相似的重复索引

        索引并非越多越好。抛开优化器面对众多索引逐一试探所耗费的时间不谈，如果表上增删改操作频繁，那么索引的维护将会成为大麻烦，尤其是函数索引的计算开销更不能忽略。

4.6 使用COUNT(*)统计结果行数

        如果对单表查询COUNT(*)且没有过滤条件，那么DM优化器会直接读取相关索引中存储的行数信息，加以回滚段中其他事务插入或删除元组的行数修正，迅速地给出最终结果而避免对实际数据的读取。相比之下，COUNT(列名)会对数据进行读操作，执行效率远低于COUNT(*)。即使查询中含有过滤条件，由于DM特有的批处理方式，COUNT(*)依旧快于其他写法。这是因为COUNT(*)无需取得行的具体值而仅仅需要行数这一信息。需要额外说明的是，COUNT(*)会将NULL值计算在内而COUNT(列名)是不包含NULL值的，因此用户要结合应用场景决定是否可以使用COUNT(*)。

4.7 使用EXPLAIN来查看执行计划

        在查询语句或者插入、删除、更新语句前增加EXPLAIN关键字，DM将显示其执行计划而无需实际执行它。查阅V$SQL_NODE_NAME表中每个操作符的含义，用户可以很方便且直观地了解数据如何被处理及传递。如果启用了统计信息收集，那么对照执行计划和对动态视图V$SQL_NODE_HISTORY，V$SQL_NODE_NAME的查询结果，用户就可以知道在实际执行中每一个操作符执行的时间，进而找出性能瓶颈。

4.8 UNION和UNION ALL的选择

        UNION和UNION ALL的区别是前者会过滤掉值完全相同的元组，为此UNION操作符需要建立HASH表缓存所有数据并去除重复，当HASH表大小超过了INI参数指定的限制时还会做刷盘。因此如果应用场景并不关心重复元组或者不可能出现重复，那么UNION ALL无疑优于UNION。

4.9 优化GROUP BY...HAVING

        GROUP BY最常见的实现有HASH分组（HAGR）和排序分组（SAGR）。前者需要缓存中间结果；如果用户在GROUPBY的列上建立索引，那么优化器就会判断并可能使用上该索引，这时的GROUPBY就会变为SAGR。HAVING是分组后对结果集进行的过滤，如果过滤条件无关集函数操作，用户可以考虑将过滤条件放在WHERE而不是HAVING中。DM优化器会判断并自动转换部分等效于WHERE的HAVING子句，但显式地给出最佳SQL语句会让优化器工作得更好。

4.10使用优化器提示（HINT）

        略

更多内容，请访问达梦社区地址：https:eco.dameng.com