数据库优化——SQL语句编写（DB2）

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

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

（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 20080201AND 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 )

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

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表的一次连接，大幅度提高了查询效率。

在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一致。

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

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

select *

from (select * from T1

union all

select * from T2) as V1

where c1 between 2000 and2009

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

select *

from (select * from T1 wherec1 between 2000 and 2009

union all

select * from T2 where c1between 2000 and 2009) as V1

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

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

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行的访问计划并不一定是对于获取整个结果集最佳的访问计划。

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

SELECT EMP_ID, EMP_NAME AGEFROM MANAGER_INFO WHERE EMP_ID = 918233

SELECT EMP_ID, EMP_NAME AGE FROMMANAGER_INFO WHERE EMP_ID = 920122

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