SELECT dm.PRIMARY_ID
FROM
(
SELECT COALESCE(d1.JOIN_ID,d2.JOIN_ID,d3.JOIN_ID) PRIMARY_ID
FROM X_DRIVING_TABLE dt
LEFT OUTER JOIN X_DETAIL_1 d1 ON dt.ID = d1.ID
LEFT OUTER JOIN X_DETAIL_LINK lnk ON d1.LINK_ID = lnk.LINK_ID
LEFT OUTER JOIN X_DETAIL_2 d2 ON dt.ID = d2.ID
LEFT OUTER JOIN X_DETAIL_3 d3 ON dt.ID = d3.ID
) dm
INNER JOIN X_LAST_TABLE lst ON dm.PRIMARY_ID = lst.JOIN_ID;
估计的计划是here.我正在制作表格的统计副本,因此我不能包含实际计划.但是,我不认为这与这个问题非常相关.
sql Server估计将从“dm”派生表返回481577行.然后,它估计在执行到X_LAST_TABLE的连接后将返回4528030000行,但JOIN_ID是X_LAST_TIME的主键.我希望连接基数估计在0到481577行之间.相反,行估计似乎是交叉连接外表和内表时将获得的行数的10％.这个数学计算得出舍入：481577 * 94025 * 0.1 = 45280277425,四舍五入为4528030000.
我主要是寻找这种行为的根本原因.我也对简单的解决方法感兴趣,但请不要建议更改数据模型或使用临时表.此查询是视图中逻辑的简化.我知道在几列上做COALESCE并加入它们并不是一个好习惯.这个问题的部分目标是弄清楚我是否需要建议重新设计数据模型.
我正在使用传统的基数估算器在Microsoft sql Server 2014上进行测试. TF 4199和其他人都在.如果最终相关,我可以提供跟踪标志的完整列表.
这是最相关的表定义：
CREATE TABLE X_LAST_TABLE (
JOIN_ID NUMERIC(18,0) NOT NULL
CONSTRAINT PK_X_LAST_TABLE PRIMARY KEY CLUSTERED (JOIN_ID ASC)
);
我也是scripted out all of the table creation scripts along with their statistics如果有人想在他们的一台服务器上重现这个问题.
为了增加我的一些观察结果,使用TF 2312修正了估算值,但这对我来说不是一个选择. TF 2301无法确定估算值.删除其中一个表可以修复估算值.奇怪的是,更改X_DETAIL_LINK的加入顺序也会修复估算值.通过更改连接顺序,我的意思是重写查询,而不是强制提示连接顺序.这是一个estimated query plan,只是改变了连接的顺序.
解决方法
I know that doing
COALESCEon a few columns and joining on them isn’t a good practice.
当模式是3NF(带有键和约束)并且查询是关系的并且主要是SPJG(选择 – 投影 – 连接 – 组)时,生成良好的基数和分布估计是很难的. CE模型建立在这些原则之上.查询中存在的异常或非关系特征越多,越接近基数和选择性框架可以处理的边界.走得太远,CE会放弃并猜测.
大多数MCVE例子是简单的SPJ(无G),虽然主要是外部等值连接(模拟为内连接加反半连接),而不是更简单的内部等值连接(或半连接).所有关系都有键,但没有外键或其他约束.除了一个连接之外的所有连接都是一对多的,这很好.
X_DETAIL_1和X_DETAIL_LINK之间的多对多外部联接是个例外. MCVE中此连接的唯一功能是可能在X_DETAIL_1中复制行.这是一种不同寻常的事情.
简单的等式谓词(选择)和标量运算符也更好.例如,属性compare-equal属性/常量通常在模型中很有效.修改直方图和频率统计以反映这种谓词的应用是相对“容易”的.
COALESCE建立在CASE之上,后者又在内部实现为IIF(在IIF出现在Transact-sql语言之前就已经存在了). CE将IIF模型化为具有两个相互排斥的子项的UNION,每个子项包含一个关于输入关系选择的项目.每个列出的组件都有模型支持,因此将它们组合起来相对简单.即便如此,一层抽象越多,最终结果就越不准确 – 这就是为什么较大的执行计划往往不那么稳定和可靠的原因.
另一方面,ISNULL是发动机固有的.它不是使用任何更基本的组件构建的.例如,将ISNULL的效果应用于直方图就像将步骤替换为NULL值一样简单(并根据需要进行压缩).它仍然是相对不透明的,就像标量运算符一样,因此最好尽可能避免.尽管如此,它通常比基于CASE的备用更友好(不太优化 – 不友好).
CE(70和120)非常复杂,即使按sql Server标准也是如此.这不是将简单逻辑(带有秘密公式)应用于每个运算符的情况. CE知道密钥和功能依赖性;它知道如何估计使用频率,多变量统计和直方图;并且有大量的特殊情况,改进,检查和平衡和支持结构.它通常估计例如以多种方式(频率,直方图)加入,并根据两者之间的差异决定结果或调整.
最后要介绍的基本内容：初始基数估算从下到上运行查询树中的每个操作.选择性和基数首先是叶运算符(基本关系).为父运算符导出修改的直方图和密度/频率信息.我们走的树越往上,估计的质量就越低,因为误差往往会累积.
这个单一的初始综合评估提供了一个起点,并且在对最终执行计划给予任何考虑之前很久就会发生(它甚至在普通计划编制阶段之前发生).此时的查询树倾向于非常密切地反映查询的书面形式(尽管删除了子查询,并应用了简化等)
在初始估计之后,sql Server立即执行启发式连接重新排序,松散地说,尝试重新排序树以放置较小的表和高选择性连接.它还尝试在外连接和交叉产品之前定位内连接.它的能力不广泛;它的努力并非详尽无遗;并且它不考虑物理成本(因为它们尚不存在 – 仅存在统计信息和元数据信息).启发式重新排序在简单的内部等值树上是最成功的.它的存在是为基于成本的优化提供“更好的”起点.
Why is this join cardinality estimate so large?
MCVE具有“不寻常的”多数冗余的多对多连接,并且在谓词中与COALESCE等同连接.运算符树还具有内连接最后一个,启发式连接重新排序无法将树向上移动到更优选的位置.除了所有标量和投影外,连接树是：
@R_502_395@p_Join [ Card=4.52803e+009 ] @R_502_395@p_LeftOuterJoin [ Card=481577 ] @R_502_395@p_LeftOuterJoin [ Card=481577 ] @R_502_395@p_LeftOuterJoin [ Card=481577 ] @R_502_395@p_LeftOuterJoin [ Card=481577 ] @R_502_395@p_Get TBL: X_DRIVING_TABLE(alias TBL: dt) [ Card=481577 ] @R_502_395@p_Get TBL: X_DETAIL_1(alias TBL: d1) [ Card=70 ] @R_502_395@p_Get TBL: X_DETAIL_LINK(alias TBL: lnk) [ Card=47 ] @R_502_395@p_Get TBL: X_DETAIL_2(alias TBL: d2) X_DETAIL_2 [ Card=119 ] @R_502_395@p_Get TBL: X_DETAIL_3(alias TBL: d3) X_DETAIL_3 [ Card=281 ] @R_502_395@p_Get TBL: X_LAST_TABLE(alias TBL: lst) X_LAST_TABLE [ Card=94025 ]
请注意,错误的最终估算已经到位.它打印为Card = 4.52803e 009,并在内部存储为双精度浮点值4.5280277425e 9(十进制4528027742.5).
原始查询中的派生表已被删除,并且投影已标准化.执行初始基数和选择性估计的树的sql表示形式为：
SELECT
PRIMARY_ID = COALESCE(d1.JOIN_ID,d3.JOIN_ID)
FROM X_DRIVING_TABLE dt
LEFT OUTER JOIN X_DETAIL_1 d1
ON dt.ID = d1.ID
LEFT OUTER JOIN X_DETAIL_LINK lnk
ON d1.LINK_ID = lnk.LINK_ID
LEFT OUTER JOIN X_DETAIL_2 d2
ON dt.ID = d2.ID
LEFT OUTER JOIN X_DETAIL_3 d3
ON dt.ID = d3.ID
INNER JOIN X_LAST_TABLE lst
ON lst.JOIN_ID = COALESCE(d1.JOIN_ID,d3.JOIN_ID)
(另外,重复的COALESCE也存在于最终计划中 – 一次在最终的Compute Scalar中,一次在内部连接的内侧).
注意最后的加入.此内连接(根据定义)是X_LAST_TABLE和前面的连接输出的笛卡尔积,其中应用了lst.JOIN_ID = COALESCE(d1.JOIN_ID,d3.JOIN_ID)的选择(连接谓词).笛卡尔积的基数仅为481577 * 94025 = 45280277425.
为此,我们需要确定并应用谓词的选择性.不透明的扩展COALESCE树(根据UNION和IIF,记住)的组合以及对关键信息,派生直方图和早期“不寻常”多数冗余多对多外连接组合的频率的影响意味着CE无法以任何正常方式得出可接受的估计值.
结果,它进入猜猜逻辑.猜测逻辑中等复杂,尝试了各种“受过教育”的猜测和“没那么受过教育”的猜测算法.如果没有找到更好的猜测基础,模型使用最后的猜测,对于相等比较,它是：sqllang！x_Selectivity_Equal = fixed 0.1 selective(10％guess)：
06002
结果是对笛卡儿积的选择性为0.1：481577 * 94025 * 0.1 = 4528027742.5(~4.52803e 009),如前所述.
重写
当有问题的连接被注释掉时,产生了更好的估计,因为避免了固定选择性“最后的猜测”(关键信息由1-M连接保留).估计的质量仍然很低,因为COALESCE连接谓词根本不是CE友好的.我想,修改后的估计对人类来说至少看起来更合理.
当查询使用外部联接写入最后放置的X_DETAIL_LINK时,启发式重新排序可以将其与最终内部联接交换到X_LAST_TABLE.将内连接放在问题外连接旁边,可以提供有限的早期重新排序机会以改进最终估计,因为大多数冗余的“不寻常”多对多外连接的影响来自于棘手的选择性估计对于COALESCE.同样,估计数比固定猜测要好一些,并且可能不会经得起在法庭上确定的交叉询问.
重新排序内部和外部联接的混合是困难且耗时的(即使第2阶段完全优化仅尝试有限的理论移动子集).
在Max Vernon的回答中建议的嵌套ISNULL设法避免纾困的固定猜测,但最终估计是一个不可能的零行(为了正派而提升到一行).对于计算所具有的所有统计基础,这也可以是1行的固定猜测.
I would expect a join cardinality estimate between 0 and 481577 rows.
这是一个合理的期望,即使人们接受基数估计可以在不同的时间(基于成本的优化期间)在物理上不同但逻辑上和语义上相同的子树上发生 – 最终计划是一种最好的拼接在一起的最好的(每个备忘录组).缺乏计划范围的一致性保证并不意味着个人加入应该能够蔑视尊重,我明白了.
另一方面,如果我们最终猜测最后的手段,希望已经失去了,那么为什么还要费心呢.我们尝试了所有熟悉的技巧,然后放弃了.如果没有别的,那么狂野的最终估计是一个很好的警示标志,在编译和优化此查询期间并非所有内容都在CE内部完成.
当我尝试MCVE时,120 CE为原始查询产生了零(= 1)行最终估计(如嵌套的ISNULL),这与我的思维方式一样令人无法接受.
真正的解决方案可能涉及设计变更,允许在没有COALESCE或ISNULL的情况下进行简单的等连接,理想情况下是外键和其他约束对查询编译有用.
