DB2 优化器中针对 JOIN 语句的结果集估计-db2 join用法

在所有 SQL 语句基估计过程中，以 JOIN 语句的计算过程最复杂，而 JOIN 语句恰恰是进行性能优化的重点。本文主要关注的是 DB2 优化器在进行基估计时采用的相关计算方法、输入等。

简介

优化器是 DB2 的心脏和灵魂(可以把它类比成宝马 730 或波音 747 的发动机引擎一样)。它分析 SQL 语句并确定可以满足每条语句的最有效的存取路径。 DB2 SQL 优化器可以估计每个备选访问计划的执行成本，并根据其估计结果选择一个最佳访问计划。

在优化器在优化一个 SQL 语句的过程中使用到两个非常重要的概念：selectivity 和 cardinality 。 selectivity 是指一个 SQL 操作的得出结果集占原来结果集的百分比，而 cardinality 就是指一个 SQL 操作的得出结果集的行数。

为正确地确定每种访问计划的成本，DB2 优化器都会对每个步骤产生的结果集大小即返回的行数进行估计，这就是优化器的基估计。 DB2 优化器需要准确的基数估计值。基数估计是这样一种过程：在应用了谓词或执行了聚集之后，优化器使用统计信息确定部分查询结果的大小。对于访问计划的每个操作符，优化器将估计该操作符的基数输出。一个或更多谓词的应用可以减少输出流基数。

JOIN 谓词

当我们在 SQL 里面需要对多个表进行 join 的时候，DB2 会首先选择其中的 2 个表进行 join，并获取到一个中间的结果集，然后 DB2 可能会用这个中间的结果集和第三个表做 join，再次获得中间的结果集(当然也可能是把另外 2 个表做 join，然后把两个中间的结果集进行 join 操作)，不管是怎么操作，DB2 一次能够 join 的表的个数肯定是两个。因此当优化器在考虑 Join 如何处理的时候，join 的顺序就是一个很重要的问题，因为我们总是希望能够在最开始就把结果集控制的尽量小。

一个 JOIN 谓词一般描述如下所示：

T1.joincol=T2.joincol

在实际应用过程中，Where 子句中除 JOIN 谓词外，一般都还有本地谓词，形式如下：

T1.joincol=T2.joincol and T1.filter=literal_1 and T2.filter=literal_2

谓词 T1.filter=literal_1 用于对 T1 表进行过来，T2.filter=literal_2 用于多 T2 表进行过滤，然后两个经过过滤的表进行 JOIN 操作。至于 JOIN 采用 hash join 还是 Merge Join 或者 NestLoop Join 取决于 DB2 的优化级别、参数设置以及成本估计。

DB2 Join 谓词选择性计算公式如下：

Selectivity (T1.y = T2.y)= 1/max(colcard(T1. joincol), colcard(T2. joincol))

其中，colcard(T1. joincol) 指 T1 表 joincol 列的不同值的个数，colcard(T2. joincol) 指 T2 表 joincol 列的不同值的个数，两者取较大的一个作为 Join 谓词计算依据。此公式存在两个假设：

包含性，即 T2. joincol 的所有取值都在 T1 joincol 取值范围内，反之也行。

均衡性，即两个连接列上的数据分布均匀。

DB2 优化器中针对 JOIN 语句的结果集估计

作者: 骆洪青, 　出处:IT专家网论坛,　责任编辑: 陈子琪,　2009-06-24 07:00

DB2 优化器在为 SQL 语句生成执行计划时，都会对每个步骤产生的结果集大小进行估计，这就是DB2 优化器的基估计。在所有 SQL 语句基估计过程中，以 JOIN 语句的计算过程最复杂，而 JOIN 语句恰恰是进行性能优化的重点。

DB2 Join 谓词基估计计算公式如下：

Join Cardinality =Join Selectivity *   
filtered cardinality(t1) *   
filtered cardinality(t2)

其中 filtered cardinality(t1) 是在 T1 表上应用本地谓词后获得结果集，filtered cardinality(t2) 是在 T2 表上应用本地谓词后获得结果集。

示例#p#

创建测试表

我们创建以下测试表：

T1 表拥有 10000 行数据。参加进行 JOIN 操作的列 join1 数据均匀分布，取值范围在 0 ～ 29 之间，没有空值。对 T1 进行过滤的列 filter1 数据也均匀分布，取值在在 0 ～ 24 之间。 V1 列从 0 自然增长到 9999 。

T2 表拥有 10000 行数据。参加进行 JOIN 操作的列 join2 数据均匀分布，取值范围在 0 ～ 29 之间，没有空值。对 T2 进行过滤的列 filte2r 数据也均匀分布，取值在在 0 ～ 24 之间。 V2 列从 0 自然增长到 9999 。

drop table db2inst1.t1;   
CREATE TABLE db2inst1.t1   
( Filter1 int, join1 int , v1 int, padding1 char(1)   
)   
NOT LOGGED INITIALLY   
;   
INSERT INTO db2inst1.t1 (filter1, join1, v1,padding1)   
WITH TEMP (COUNTER, filter1, join1, v1,padding1) AS   
( VALUES (0, MOD(INT(RAND() * 1000), 25),MOD(INT(RAND() * 1000), 30), 0, 'A')   
UNION ALL SELECT (COUNTER + 1),MOD(INT(RAND() * 1000), 25),   
MOD(INT(RAND() * 1000), 30), (COUNTER + 1), 'A' FROM TEMP WHERE (COUNTER + 1) < 10000   
)   
SELECT Filter1, join1, v1,padding1   
FROM TEMP   
;   
drop table db2inst1.t2;   
CREATE TABLE db2inst1.t2   
( Filter2 int, Join2 int , V2 int, Padding2 char(1)   
)   
NOT LOGGED INITIALLY   
;   
INSERT INTO db2inst1.t2 (filter2, join2, v2,padding2)   
WITH TEMP (COUNTER, filter2, join2, v2,padding2) AS   
( VALUES (0, MOD(INT(RAND() * 1000), 50),MOD(INT(RAND() * 1000), 40), 0, 'A')   
UNION ALL SELECT (COUNTER + 1),MOD(INT(RAND() * 1000), 50),MOD(INT(RAND() * 1000), 40),   
(COUNTER + 1), 'A' FROM TEMP WHERE (COUNTER + 1) < 10000   
)   
SELECT Filter2, join2, v2,padding2   
FROM TEMP   
;

在表创建完成后，我们收集 T1 和 T2 的统计信息，在收集统计信息是只包括表的基本统计和列的统计信息，不包括列的分布信息。

db2 "runstats on table db2inst1.t1 on all COLUMNS "   
db2 "runstats on table db2inst1.t2 on all COLUMNS "

使用 db2look 从系统统计视图中提取 T1、T2 的统计信息如下。

表 1. T1 统计信息

统计属性值说明

表 CARD 10000 表的行数

表 NPAGES 68 表占用的页面数

列 FILTER1 的 COLCARD 25 列的不同取值个数

列 FILTER1 的 NUMNULLS 0 列的空值行数

列 JOIN1 的 COLCARD 30 列的不同取值个数

列 JOIN1 的 NUMNULLS 0 列的空值行数

表 2. T2 统计信息

统计属性值说明

表 CARD 10000 表的行数

表 NPAGES 68 表占用的页面数

列 FILTER2 的 COLCARD 50 列的不同取值个数

列 FILTER2 的 NUMNULLS 0 列的空值行数

列 JOIN2 的 COLCARD 40 列的不同取值个数

列 JOIN2 的 NUMNULLS 0 列的空值行数

测试一

我们首先执行以下查询来验证公式。

select count(*)   
from (   
select   
t1.v1, t2.v1   
from   
t1,   
t2   
where   
t1.filter = 1   
and t2.join1 = t1.join1   
and t2.filter = 1 )   
as b;

在表创建完成后，我们收集 T1 和 T2 的统计信息，在收集统计信息是只包括表的基本统计和列的统计信息，不包括列的分布信息。

db2 "runstats on table db2inst1.t1 on all COLUMNS "   
db2 "runstats on table db2inst1.t2 on all COLUMNS "

使用 db2look 从系统统计视图中提取 T1、T2 的统计信息如下。

表 1. T1 统计信息

统计属性值说明

表 CARD 10000 表的行数

表 NPAGES 68 表占用的页面数

列 FILTER1 的 COLCARD 25 列的不同取值个数

列 FILTER1 的 NUMNULLS 0 列的空值行数

列 JOIN1 的 COLCARD 30 列的不同取值个数

列 JOIN1 的 NUMNULLS 0 列的空值行数

表 2. T2 统计信息

统计属性值说明

表 CARD 10000 表的行数

表 NPAGES 68 表占用的页面数

列 FILTER2 的 COLCARD 50 列的不同取值个数

列 FILTER2 的 NUMNULLS 0 列的空值行数

列 JOIN2 的 COLCARD 40 列的不同取值个数

列 JOIN2 的 NUMNULLS 0 列的空值行数

测试一

我们首先执行以下查询来验证公式。

select count(*)   
from (   
select   
t1.v1, t2.v1   
from   
t1,   
t2   
where   
t1.filter = 1   
and t2.join1 = t1.join1   
and t2.filter = 1 )   
as b;

以上的相关内容就是对DB2 优化器中针对 JOIN 语句的结果集估计的介绍，望你能有所收获。

【编辑推荐】