签到成功

知道了

CNDBA社区CNDBA社区

DM 达梦 编写高效 SQL 语句 的建议

2023-03-20 21:32 1270 0 转载 DM 达梦
作者: dave

SQL 语言是一种相当灵活的结构化查询语言。用户可以利用多种不同形式的查询语句完成相同的查询功能。为了使执行效率达到最优,在编写SQL语句时建议参考以下原则。

1 避免使用 OR 子句

OR 子句在实际执行中会被转换为类似于 UNION 的查询。如果某一个 OR 子句不能利用上索引则会使用全表扫描造成效率低下,应避免使用。

如果 OR 子句都是对同一列进行过滤,用户可以考虑使用 IN VALUE LIST 的过滤形式。
如:

SELECT … WHERE city = ‘ShangHai’ OR city = ‘WuHan’ OR city = ‘BeiJing’;

调整为

SELECT … WHERE city IN( ‘ShangHai’,’WuHan’,’BeiJing’);

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)这样一个函数索引,利用函数索引反转待匹配项从而使用函数索引进行范围扫描。http://www.cndba.cn/dave/article/116499http://www.cndba.cn/dave/article/116499

3 灵活使用伪表(SYSDUAL)

首先,可以利用伪表进行科学计算,执行语句 SELECT 3*4 FROM SYSDUAL,则可以得到结果 12;
其次,在某些方面使用 SYSDUAL 可提高效率。例如:查询过程中要判断表 t1 中是否有满足 condition1 条件的记录存在,可执行以下语句:

SELECT COUNT(*) INTO x FROM t1 WHERE condition1;

然后,根据变量 x 的取值来判断。但是当 t1 非常大时该语句执行速度很慢,而且由于不知道 SELECT 返回的个数,不能用 SELECT *代替。事实上这个查询可以利用伪表来完成:

SELECT ‘A’ INTO y FROM SYSDUAL WHERE EXISTS (SELECT 1 FROM t1 WHERE condition1);

判断 y 值,如等于’A’则 T1 中有记录。调整后的语句执行速度明显比上一句高。

另外,在 DM 的语法里是可以省略 FROM 子句的,这时系统会自动加上 FROM SYSDUAL。因此前面的科学计算例子可以简化为 SELECT 3*4;

4 SELECT 项避免‘*’

除非用户确实要选择表中所有列,否则 SELECT *这种写法将让执行器背上沉重的负荷。因为每一列的数据不得不自下往上层层向上传递。不仅仅如此,如果用户查询的是列存储表,那么列存储所带来的 IO 优势将损耗殆尽。http://www.cndba.cn/dave/article/116499

任何时候,用户都要了解表结构和业务需求,小心地选择需要的列并一一给出名称,避免直接用 SELECT *。

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

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

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

如果对单表查询 COUNT(*)且没有过滤条件,那么 DM 优化器会直接读取相关索引中存储的行数信息,加以回滚段中其他事务插入或删除元组的行数修正,迅速地给出最终结果而避免对实际数据的读取。http://www.cndba.cn/dave/article/116499

相比之下,COUNT(列名)会对数据进行读操作,执行效率远低于 COUNT()。
即使查询中含有过滤条件,由于 DM 特有的批处理方式,COUNT(
)依旧快于其他写法。这是因为 COUNT(*)无需取得行的具体值而仅仅需要行数这一信息。

需要额外说明的是,COUNT()会将 NULL 值计算在内而 COUNT(列名)是不包含 NULL 值的,因此用户要结合应用场景决定是否可以使用 COUNT()。

7 使用 EXPLAIN 来查看执行计划

在查询语句或者插入、删除、更新语句前增加 EXPLAIN 关键字,DM 将显示其执行计划而无需实际执行它。

查阅 V$SQL_NODE_NAME 表中每个操作符的含义,用户可以很方便且直观地了解数据如何被处理及传递。如果启用了统计信息收集,那么对照执行计划和对动态视图 v$sql_node_history,v$sql_node_name 的查询结果,用户就可以知道在实际执行中每一个操作符执行的时间,进而找出性能瓶颈。http://www.cndba.cn/dave/article/116499http://www.cndba.cn/dave/article/116499http://www.cndba.cn/dave/article/116499http://www.cndba.cn/dave/article/116499

8 UNION 和 UNION ALL 的选择

UNION 和 UNIONALL 的区别是前者会过滤掉值完全相同的元组,为此 UNION 操作符需要建立 HASH 表缓存所有数据并去除重复,当 HASH 表大小超过了 ini 参数指定的限制时还会做刷盘。

因此如果应用场景并不关心重复元组或者不可能出现重复,那么 UNION ALL 无疑优于 UNION。

9 优化 GROUP BY … HAVING

GROUP BY 最常见的实现有 HASH 分组(HAGR)和排序分组(SAGR)。前者需要缓存中间结果;如果用户在 GROUP BY 的列上建立索引,那么优化器就会判断并可能使用上该索引,这时的 GROUP BY 就会变为 SAGR。

HAVING 是分组后对结果集进行的过滤,如果过滤条件无关集函数操作,用户可以考虑将过滤条件放在 WHERE 而不是 HAVING 中。DM 优化器会判断并自动转换部分等效于 WHERE 的 HAVING 子句,但显式地给出最佳 SQL 语句会让优化器工作得更好。http://www.cndba.cn/dave/article/116499

10 使用优化器提示(HINT)

利用经验对优化器的计划选择进行调整,HINT 是 SQL 调整不可或缺的一步。http://www.cndba.cn/dave/article/116499

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2248
    原创
  • 3
    翻译
  • 557
    转载
  • 188
    评论
  • 访问:7224377次
  • 积分:4284
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ