sql-server – 为什么这个连接基数估计如此之大？

发布时间：2021-01-12 07:37:27 所属栏目：MsSql教程来源：网络整理

导读：我正在经历我认为对以下查询的不可能高的基数估计： SELECT dm.PRIMARY_IDFROM( 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

副标题[/!--empirenews.page--]

我正在经历我认为对以下查询的不可能高的基数估计：

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 COALESCE on 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等同连接.运算符树还具有内连接最后一个,启发式连接重新排序无法将树向上移动到更优选的位置.除了所有标量和投影外,连接树是：

LogOp_Join [ Card=4.52803e+009 ]
    LogOp_LeftOuterJoin [ Card=481577 ]
        LogOp_LeftOuterJoin [ Card=481577 ]
            LogOp_LeftOuterJoin [ Card=481577 ]
                LogOp_LeftOuterJoin [ Card=481577 ]
                LogOp_Get TBL: X_DRIVING_TABLE(alias TBL: dt) [ Card=481577 ]
                LogOp_Get TBL: X_DETAIL_1(alias TBL: d1) [ Card=70 ]
                LogOp_Get TBL: X_DETAIL_LINK(alias TBL: lnk) [ Card=47 ]
            LogOp_Get TBL: X_DETAIL_2(alias TBL: d2) X_DETAIL_2 [ Card=119 ]
        LogOp_Get TBL: X_DETAIL_3(alias TBL: d3) X_DETAIL_3 [ Card=281 ]
    LogOp_Get TBL: X_LAST_TABLE(alias TBL: lst) X_LAST_TABLE [ Card=94025 ]

请注意,错误的最终估算已经到位.它打印为Card = 4.52803e 009,并在内部存储为双精度浮点值4.5280277425e 9(十进制4528027742.5).

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

1/3

尾页