提高DB2查询性能的常用方法

此查询用来列出所有不存在联系人的客户。对于这样的需求，开发人员会最自然的写出清单10中的查询，的确，对于大部分情况它具有最优的性能。该查询的查询代价为 178,430 timerons。让我们再来看看使用NOT IN 后查询的总代价，请看清单11。

清单11.查询示例

查询：
select cust_num
from temp.customer cust
where cust.cust_num not in (select cont.cust_num from temp.contact cont)
代价：12,648,897,536 timerons

可以看到 NOT EXISTS 的性能要比 NOT IN 高出许多。NOT IN是自内向外的操作，即先得到子查询的结果，然后执行最外层的查询，而 NOT EXISTS 恰好相反，是自外向内的操作。在上述例子中，temp.contact表中有 65 万条记录，使得 10.2 查询中的NOT IN 列表非常大，导致了使用NOT IN 的查询具有非常高的查询代价。下面我们对 10.1和10.2 的查询进行修改，将temp.contact表中的记录限制到 100 条，请看下面的查询：

清单12.查询示例

查询：
select cust_num
from temp.customer cust
where not exists (select 1 from temp.contact cont
here cust.cust_num = cont.cust_num
and cont.cnt_id < 100)
代价：42,015 timerons

清单13.查询示例

查询：
select cust_num
from temp.customer cust
where cust.cust_num not in (select cont.cust_num from temp.contact cont
where cont.cnt_id < 100)
代价：917,804 timerons

从 12和13中可以看出 NOT EXISTS 的查询代价随子查询返回的结果集的变化没有大幅度的下降，随着子查询的结果集从 65 万下降到 100 条，NOT EXISTS 的查询代价从 178,430 下降到 42,015，只下降 4 倍。但是 NOT IN 的查询代价却有着极大的变化，其查询代价从 12,648,897,536 下降到 917,804，下降了13782 倍。可见子查询的结果集对 NOT IN 的性能影响很大，但是这个简单的查询不能说明 NOT EXISTS 永远好于 NOT IN，因为同样存在一些因素对 NOT EXISTS 的性能有很大的影响。我们再看下面的例子：

清单14.查询示例

查询：
select cust_num
from temp.customer cust
where not exists (select 1 from temp.contact cont
where cust.cust_num = cont.cust_num
and cont.cnt_id in (select cnt_id from temp.contact_detail
where cnt_id<100))
代价：5,263,096 timerons

清单15.查询示例

查询：
select cust_num
from temp.customer cust
where cust_num not in (select cust_num from temp.contact cont
where cont.cnt_id in (select cnt_id from temp.contact_detail
where cnt_id<100))
代价：4,289,095 timerons

在上面的例子中，我们只是对查询增加了一个小改动，使用一个嵌套查询限制了在temp.contact中扫描的范围。但是在这两个新的查询中，NOT IN 的性能却又好于 NOT EXISTS。NOT EXISTS 的代价增加了125 倍，而 NOT IN 的代价却只增加了4 倍。这是由于 NOT EXISTS是自外向内，嵌套查询的复杂度对其存在较大的影响。因此在实际应用中，要考虑子查询的结果集以及子查询的复杂度来决定使用NOT EXISTS 或者 NOT IN。对于 IN，EXISTS和JOIN 等操作，大多数情况下 DB2 优化器都能形成比较一致的最终查询计划。