Db2 中计算 OR 关系组合列选择率的原理

问题与实例

问题非常简单，Db2 是如何估算如下语句所带谓词条件的选择率的：
SELECT * FROM testset WHERE c1=? or c2=? or c3=? or c4=?;
为了方便说明，我们从一个实际例子入手，在 Db2 中构造如下表，插入 12 条数据，并收集统计信息：

CREATE TABLE testset (c1 INT, c2 INT, c3 INT, c4 INT);
INSERT INTO testset VALUES 
(0,0,0,0),
(1,1,1,1),
(2,2,2,2),
(3,3,3,0),
(4,4,0,1),
(5,5,1,2),
(6,0,2,0),
(7,1,3,1),
(8,2,0,2),
(9,3,1,0),
(10,4,2,1),
(11,5,3,2);
RUNSTATS ON TABLE testset;

用 db2look -d sample -e -t testset -m 看下统计信息：

UPDATE SYSSTAT.COLUMNS
SET COLCARD=12,
    NUMNULLS=0,
    SUB_COUNT=-1,
    SUB_DELIM_LENGTH=-1,
    PCTENCODED=-1,
    AVGCOLLENCHAR=-1,
    PAGEVARIANCERATIO=-1.000000,
    AVGENCODEDCOLLEN=-1.000000,
    HIGH2KEY='10',
    LOW2KEY='1',
    AVGCOLLEN=5
WHERE COLNAME = 'C1' AND TABNAME = 'TESTSET' AND TABSCHEMA = 'DB2INST1';

UPDATE SYSSTAT.COLUMNS
SET COLCARD=6,
    NUMNULLS=0,
    SUB_COUNT=-1,
    SUB_DELIM_LENGTH=-1,
    PCTENCODED=-1,
    AVGCOLLENCHAR=-1,
    PAGEVARIANCERATIO=-1.000000,
    AVGENCODEDCOLLEN=-1.000000,
    HIGH2KEY='4',
    LOW2KEY='1',
    AVGCOLLEN=5
WHERE COLNAME = 'C2' AND TABNAME = 'TESTSET' AND TABSCHEMA = 'DB2INST1';

UPDATE SYSSTAT.COLUMNS
SET COLCARD=4,
    NUMNULLS=0,
    SUB_COUNT=-1,
    SUB_DELIM_LENGTH=-1,
    PCTENCODED=-1,
    AVGCOLLENCHAR=-1,
    PAGEVARIANCERATIO=-1.000000,
    AVGENCODEDCOLLEN=-1.000000,
    HIGH2KEY='2',
    LOW2KEY='1',
    AVGCOLLEN=5
WHERE COLNAME = 'C3' AND TABNAME = 'TESTSET' AND TABSCHEMA = 'DB2INST1';

UPDATE SYSSTAT.COLUMNS
SET COLCARD=3,
    NUMNULLS=0,
    SUB_COUNT=-1,
    SUB_DELIM_LENGTH=-1,
    PCTENCODED=-1,
    AVGCOLLENCHAR=-1,
    PAGEVARIANCERATIO=-1.000000,
    AVGENCODEDCOLLEN=-1.000000,
    AVGCOLLEN=5
WHERE COLNAME = 'C4' AND TABNAME = 'TESTSET' AND TABSCHEMA = 'DB2INST1';

可以看到，表中一共有 12 条数据，收集了统计信息后，Db2 优化器知道了 c1, c2, c3, c4 的 NDV（Number of Distinct Value）分别是 12, 6, 4, 3。
我们考虑如下语句：SELECT * FROM testset WHERE c1=? or c2=? or c3=? or c4=?;
我们先来猜测下 Db2 优化器对这个谓词条件评估的选择率是多少？
最自然的想法，是不是$\frac{1}{12}+\frac{1}{6}+\frac{1}{4}+\frac{1}{3}=0.833$呢？答案并不是，因为这只是简单相加，并不符合 OR 的含义。我们看下 Db2 优化器实际给出的估计：
EXPLAIN ALL FOR SELECT * FROM testset WHERE c1=? or c2=? or c3=? or c4=?
db2exfmt -d sample -1

Predicates:
----------
2) Sargable Predicate, 
	Comparison Operator: 		Not Applicable
	Subquery Input Required: 	No
	Filter Factor: 			0.618056

	Predicate Text:
	--------------
	((((Q1.C1 = ?) OR 
	   (Q1.C2 = ?)) OR 
	  (Q1.C3 = ?)) OR 
	 (Q1.C4 = ?))

这里是 0.618056，下面就解释下这个数字是怎么被计算出来的，可以用两种方法进行解释。

集合包容排斥原理方法

Recall 一下集合包容排斥原理（inclusion-exclusion principle）：
若$A_1,...A_n$为有限集合，则：
$|{\bigcup }_{i=1}^n{A}_i|={\sum }_{i=1}^n|A_i|-{\sum }_{1\le i<j\le n}|A_i\cap A_j|+{\sum }_{1\le i<j<k\le n}|A_i\cap A_j\cap A_k|-...+(-1{)}^{n-1}|A_1\cap ...\cap A_n|$
对于两个集合来说，所表示的就是：
$|A_1\cup A_2|=|A_1|+|A_2|-|A_1\cap A_2|$
对于三个集合则是：
$|A_1\cup A_2\cup A_3|=|A_1|+|A_2|+|A_3|-|A_1\cap A_2|-|A_1\cap A_3|-|A_2\cap A_3|+|A_1\cap A_2\cap A_3|$
示意图如下：

首先，选择率（Selectivity）、元素个数（Cardinality）都可以看做是一个集合的某种测度。
其次，对于优化器来说，估计 OR 关系组合列的选择率是比较困难的，而估计 AND 关系组合列的选择率则比较简单，比如可以收集组合列统计信息，也可以使用简单的乘法法则。因此就考虑用多个集合的交集元素个数来计算并集元素个数。
下一步，我们可以将不同的谓词条件看做是一个集合，如下：
$A_1$：{ SELECT * FROM testset WHERE c1=?}
$A_2$：{ SELECT * FROM testset WHERE c2=?}
$A_3$：{ SELECT * FROM testset WHERE c3=?}
$A_4$：{ SELECT * FROM testset WHERE c4=?}
$A_1\cup A_2\cup A_3\cup A_4$：{ SELECT * FROM testset WHERE c1=? or c2=? or c3=? or c4=?}
同时可以把不同谓词条件的“选择率”看作是集合元素的“个数”，前面说过，这两个都类似于“测度”，得到如下：
$|A_1|=\frac{1}{12},|A_2|=\frac{1}{6},|A_3|=\frac{1}{4},|A_4|=\frac{1}{3}$
$|A_1\cap A_2|=\frac{1}{72},|A_1\cap A_3|=\frac{1}{48},|A_1\cap A_4|=\frac{1}{36}$
$|A_2\cap A_3|=\frac{1}{24},|A_2\cap A_4|=\frac{1}{18},|A_3\cap A_4|=\frac{1}{12}$
$|A_1\cap A_2\cap A_3|=\frac{1}{288},|A_1\cap A_2\cap A_4|=\frac{1}{216},|A_1\cap A_3\cap A_4|=\frac{1}{144},|A_2\cap A_3\cap A_4|=\frac{1}{72}$
$|A_1\cap A_2\cap A_3\cap A_4|=\frac{1}{864}$
代入公式，可得：
$|A_1\cup A_2\cup A_3\cup A_4|$
$=\frac{1}{12}+\frac{1}{6}+\frac{1}{4}+\frac{1}{3}-\frac{1}{72}-\frac{1}{48}-\frac{1}{36}-\frac{1}{24}-\frac{1}{18}-\frac{1}{12}+\frac{1}{288}+\frac{1}{216}+\frac{1}{144}+\frac{1}{72}-\frac{1}{864}$
$=\frac{534}{864}$
$=0.618055555555556$
可以看到，和 Db2 优化器估算的一模一样，分毫不差。

概率方法

概率本质上也是一种测度，而用概率的方法比较简单，也非常易于理解。我们可以想象：
集合$A$：{ SELECT * FROM testset WHERE c1=? or c2=? or c3=? or c4=?}
它的补集$\bar{A}$：{ SELECT * FROM testset WHERE c1<>? and c2<>? and c3<>? and c4<>?}
$\bar{A}$的选择率计算是非常简单的，也就是
$|\bar{A}|=(1-\frac{1}{12})\times (1-\frac{1}{6})\times (1-\frac{1}{4})\times (1-\frac{1}{3})=\frac{330}{864}$
因此：$|A|=1-|\bar{A}|=\frac{534}{864}=0.618055555555556$
结果也是一样的。