DB2优化之:SQL语句编写
博客专区 > Goopand 的博客 > 博客详情
DB2优化之:SQL语句编写
Goopand 发表于3年前
DB2优化之:SQL语句编写
  • 发表于 3年前
  • 阅读 24
  • 收藏 0
  • 点赞 0
  • 评论 0

新睿云服务器60天免费使用,快来体验!>>>   

SQL语句编写(DB2)

本章来自王鹏飞舞动DB2系列《DB2设计与性能优化——原理、方法与实践》,P242


1.1谓词

首先要知道,不合理的谓词会限制优化器对索引和连接方法的选择。设计谓词时要注意下面的原则。

(1)保证选择谓词足够简洁。选择谓词要尽量采用简单的形式,如:列名 = 常数表达式,这样便于匹配索引。还应该避免使用类型转换,如果有类型转换应该显式地写出转换函数,并放在表达式的常数一边,例如:

cast( colum_char10 as int )= 100

应该写成:

colum_char10 = cast(100 aschar(10) )

另外,使用参数标记(Parameter Marker)时,要尽量利用Cast函数表明它的类型,以免产生不必要的类型转换。

同样,下面的谓词都应该写成更优化的形式:

XPRESSN(C) = 'constant'

INTEGER(TRANS_DATE) / 100 =200802

WHERE (CUST_ID * 100) +INT(CUST_CODE) = 123456 ORDER BY 1, 2, 3

它们的正确形式应该为:

C = INVERXPRESSN( 'constant')

TRANS_DATE BETWEEN 20080201 AND 20080229

WHERE CUST_ID = 1234 AND CUST_CODE= '56' ORDER BY 1, 2, 3

(2)使用合理的连接谓词(Join Predicate)。连接谓词是选择连接方法的基本依据。DB2有三种连接方法:嵌套循环、合并连接和散列连接。注意非等式谓词不能形成合并和散列连接。只有两个表之间出现至少一个列名相等的等式谓词时,合并和散列连接才会被考虑。散列连接更是要求等式两端的列的长度相等。

为了使DB2考了尽可能多的连接方法,连接谓词应该尽量简单,如列名1 = 列名2。对于等式谓词要做到等式两边的列名长度相等。如果是Varchar,Char类型,它们定义的长度应该相等,如果是Decimal类型,它们的精度应该相同。

(3)不要使用多余的谓词。多余的谓词不但增加了系统的计算代价,而且会导致中间结果的估算不准确,并产生较差的访问计划。例如对于下面的谓词,(COL_0MONTH =199903)实际上是多余的,应该去掉。

( COL_0MONTH = 199705

  OR COL_0MONTH = 199805 )

AND NOT

( COL_0MONTH = 199903 )


1.2多余的连接

有些连接看似合理,但也许是多余的。连接会涉及对表的多次扫描,去掉多余的连接,就会减少不必要的开销。比如,下面的查询:

select T1.*

from (T1 join T2 as Q2

        on T1.id = Q2.id and Q2.value = 1 ) join T2 as Q3

        on T1.id = Q3.id and Q3.value = 2

去掉不必要的连接后,其形式为:

select T1.*

from T1, (select id

        from T2

        where value in (1, 2)

        group by id

        having count(*) = 2) as Q1

where T1.id = Q1.id

这样减少了对T2表的一次连接,大幅度提高了查询效率。


1.3子查询

在SQL语言中,当一个查询语句嵌套在另一个查询的查询条件中时,称为子查询。如果一个来自外部查询的列出现在Where子句的子查询中,那么当外部查询中的列值改变后,子查询需要重新查询一次。查询嵌套的层次越多,效率越低。因此应当尽量避免子查询。如果子查询不可避免,那么首先首先要在子查询中尽量去掉多余的行,其次考虑将子查询转换成连接。子查询转换为连接后,可以使DB2有机会考虑索引以及更多的连接方法和顺序,从而生成更优的计划。特别是Exist和IN子句,可以方便的转换成连接。例如,

select *

from T1

where exists (select * fromT2 where T1.c1 = T2.c1)

可以转换为,

select distinct T2.c1, T1.*

from T1, T2

where T1.c1 = T2.c1

注意除非T2.c1有唯一性(unique),否则关键词distinct 是必需的,这是为了保证最终结果和原来的SQL一致。


1.4外连接

要避免不必要的外连接(Outer Join)。Outer Join会限制连接的顺序,从而导致一些较好的计划无法生成。因此要尽可能避免使用Outer Join,无论是Left、Right或者Full Outer Join。


1.5 UNION ALL的使用

在使用Union ALL时,我们要注意UNION ALL之上的谓词是否被下推。可以查看OptimizedStatement,看看是否每个选择谓词都被下推到相应的子查询中。如:

select *

from (select * from T1

union all

select * from T2) as V1

where c1 between 2000 and 2009

在OptimizedStatement中应该看到选择谓词c1 between 2000 and 2009被下推到Union All里面变成:

select *

from (select * from T1 where c1 between 2000 and 2009

union all

select * from T2 where c1 between 2000 and 2009) as V1

如果没有下推,可能由于选择谓词过于复杂,或者DB2的查询重写器无法判定下推谓词是否会提高效率。这时候如果确信下推后会产生较好的计划,可以手动改变SQL的书写形式。还可以考虑适当提高优化级别,使原来没有下推的谓词被下推。


1.6 Having子句

检查Having子句中的谓词是否可以下推。应尽可能把Having子句中的谓词。一般情况下,根据一定的规则,Having子句如果不含有聚合函数,会经过逻辑优化被下推到Where子句里面,但也会出现不下推的情况。为了避免出现没有下推的情况,在编写SQL是就应尽量将能下推的谓词写在Where子句里面。


1.7 OFNR和FFNR子句

OFNR(OPTIMIZE FOR NROWS)子句使优化器选择那些执行时能最快得到前N行结果的访问计划,不过查询仍将返回完整的结果集。FFNR(FETCH FIRST N ROWSONLY)子句显示查询结果只需返回N行,这样减少了返回结果集。根据应用程序的需要使用这两个子句可以提高SQL语句的性能。

注意DB2不会因为查询指定了FETCH FIRST NROWS ONLY而选择返回前N行结果最快的访问计划。所以应该在使用FETCH FIRST NROWS ONLY时,同时使用OPTIMIZE FOR N ROWS。

另外注意,如果应用程序要获取整个结果集,但却指定OPTIMIZE FOR NROWS,可能会使性能降低。这是因为快速返回前N行的访问计划并不一定是对于获取整个结果集最佳的访问计划。


1.8使用参数标记

DB2可以通过在动态语句高速缓存中保存访问计划和SQL文本,来避免重复编译一个已编译过的动态SQL语句。然而,只要谓词在字面上有一点不同,这个语句与高速缓存中类似的SQL就无法匹配。例如,下面两个语句在动态语句高速缓存中会被看做不同的语句。

SELECT EMP_ID, EMP_NAME AGE FROM MANAGER_INFO WHERE EMP_ID = 918233

SELECT EMP_ID, EMP_NAME AGE FROM MANAGER_INFO WHERE EMP_ID = 920122

应该考虑把上述语句改成使用参数标记把谓词常数值传递给DB,而不要显式地在SQL语句中包含它。不过注意,复杂的查询如果使用参数标记,得到的访问计划可能不是最优的。



  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 7
博文 176
码字总数 172218
×
Goopand
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: