我们已经介绍了3种类型的join。现在我们需要连接5张表获取一个人的完整信息,一个人信息包括:
- 多个移动电话
- 多个电子邮件
- 多个地址
- 多个银行账户
那么我们需要下面的查询语句:
SELECT * from PERSON, MOBILES, MAILS,ADRESSES, BANK_ACCOUNTS
WHERE
PERSON.PERSON_ID = MOBILES.PERSON_ID
AND PERSON.PERSON_ID = MAILS.PERSON_ID
AND PERSON.PERSON_ID = ADRESSES.PERSON_ID
AND PERSON.PERSON_ID = BANK_ACCOUNTS.PERSON_ID
作为一个查询优化器,必须找到获取数据的最好方式,但有两个问题:
- 对于每一次join操作应该使用哪种join?
我们又3种join (Hash Join, Merge Join, Nested Join) ,并且可以使用0、1或者2个索引。 - 应该以什么顺序来join?
下图展示了4张表3次join可能的顺序:
面对如此多的选择,我可以有如下方法:
1) 我用一种粗暴的方式
通过数据库统计信息,可以计算每一种执行计划的成本,这样就能选出最优的一个。但是一般会有太多可能的执行计划,对于给定的join顺序,会有3种join:HashJoin, MergeJoin, NestedJoin.。因此对于上述Person信息SQL,给定的join顺序会有3的4次方种执行计划。join顺序实际是一个二叉树排列问题,有(2*4)!/(4+1)个可能的顺序。所以看到了吧,对于这种简单的场景都有4*(2*4)!/(4+1)种可能的执行计划。
那么实际上是有27 216种可能性,如果再算上可能使用的0、1或2个B+Tree索引情况,可能性会变为210 000。简单的查询都是如此,更何况复杂点的查询呢?2) 我大声喊着结束工作
这是很诱人但我拿不到结果,我可能需要钱来解决它。3) 我只尝试其中一部分计划,并选择它们之中最好的
因为我不是超人,所以我不能计算每一种可能的执行计划的成本。因此我只能随机选择一个子集计算,给出子集中的最优计划。4) 我应用一些规则来降低可能计划的数量
有两个规则可以利用:
可以利用一些逻辑规则来降低可能性,但是一般不过过滤掉太多可能性。例如:当使用nested loop join时,内层关系的数据集必须是更小的那个。
我可以让步不找到最好的那个,应用一些强制规则大大降低可能性。例如:当其中一个关系都很小时,只应用 nested loop join而不考虑merge join和hash join。
在上述简单的例子中,有很多种可能性。在真实的查询中可能包括的关系操作还有OUTER JOIN, CROSS JOIN, GROUP BY, ORDER BY, PROJECTION, UNION, INTERSECT, DISTINCT …这意味着会有更多的可能性。
那么一个数据库会怎么来优化呢?