sql-server – 为什么这个毗连基数预计云云之大？

我正在经验我以为对以下查询的不行能高的基数预计：

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).

（编辑：湖南网）

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