主页 / dba / 问题 / 148523
Accepted
jesijesi
Asked: 2016-09-02 01:18:07 +0800 CST

步内统计值 >= 和 > 的基数估计

  • 772

我试图了解 SQL Server 如何尝试估计 SQL Server 2014 中的“大于”和“大于等于”where 子句。

我想我确实理解了基数估计，例如，如果我这样做的话

    select * from charge where charge_dt >= '1999-10-13 10:47:38.550'

基数估计为 6672，可以很容易地计算为 32(EQ_ROWS) + 6624(RANGE_ROWS) + 16 (EQ_ROWS) = 6672（下面屏幕截图中的直方图）

在此处输入图像描述

但是当我这样做的时候

    select * from charge where charge_dt >= '1999-10-13 10:48:38.550'

（将时间增加到 10:48，所以它不是一个步骤）

估计是4844.13。

这是怎么计算的？

sql-server sql-server-2014

2 个回答

  1. Best Answer

    唯一的困难是决定如何处理查询谓词区间部分覆盖的直方图步骤。如问题中所述，谓词范围涵盖的整个直方图步骤是微不足道的。

    遗留基数估计器

    F= 查询谓词所涵盖的步长范围的分数（介于 0 和 1 之间）。

    基本思想是使用F（线性插值）来确定有多少步内不同值被谓词覆盖。将此结果乘以每个不同值的平均行数（假设均匀性），然后添加步长等于行得出基数估计：

    基数 = EQ_ROWS + (AVG_RANGE_ROWS * F * DISTINCT_RANGE_ROWS)

    旧版 CE 中>使用了相同的公式。>=

    新的基数估计器

    新的 CE 稍微修改了以前的算法以区分>和>=。

    先取>公式为：

    基数 = EQ_ROWS + (AVG_RANGE_ROWS * (F * (DISTINCT_RANGE_ROWS - 1)))

    因为>=它是：

    基数 = EQ_ROWS + (AVG_RANGE_ROWS * ((F * (DISTINCT_RANGE_ROWS - 1)) + 1))

    这+ 1反映出当比较涉及相等时，会假设匹配（包含假设）。

    在问题示例中，F可以计算为：

    DECLARE 
    @Q datetime = '1999-10-13T10:48:38.550',
    @K1 datetime = '1999-10-13T10:47:38.550',
    @K2 datetime = '1999-10-13T10:51:19.317';

DECLARE
    @QR float = DATEDIFF(MILLISECOND, @Q, @K2), -- predicate range
    @SR float = DATEDIFF(MILLISECOND, @K1, @K2) -- whole step range

SELECT
    F = @QR / @SR;

    结果是0.728219019233034。>=将其与其他已知值一起代入公式：

    基数 = EQ_ROWS + (AVG_RANGE_ROWS * ((F * (DISTINCT_RANGE_ROWS - 1)) + 1))
            = 16 + (16.1956 * ((0.728219019233034 * (409 - 1)) + 1))
            = 16 + (16.1956 * ((0.728219019233034 * 408) + 1))
            = 16 + (16.1956 * (297.113359847077872 + 1))
            = 16 + (16.1956 * 298.113359847077872)
            = 16 + 4828.1247307393343837632
            = 4844.1247307393343837632
            = 4844.12473073933（浮动精度）

    此结果与问题中显示的估计值 4844.13 一致。

    使用遗留 CE 的相同查询（例如使用跟踪标志 9481）应该产生以下估计：

    基数 = EQ_ROWS + (AVG_RANGE_ROWS * F * DISTINCT_RANGE_ROWS)
            = 16 + (16.1956 * 0.728219019233034 * 409)
            = 16 + 4823.72307468722
            = 4839.72307468722

    >请注意，对于旧版 CE和>=使用旧版 CE的估计是相同的。

    • 14

  2. 当过滤器为“大于”或“小于”时，用于估计行的公式会变得有点愚蠢，但这是一个您可以得出的数字。

    号码

    使用步骤 193，这里是相关数字：

    RANGE_ROWS = 6624

    EQ_ROWS = 16

    AVG_RANGE_ROWS = 16.1956

    上一步的 RANGE_HI_KEY = 1999-10-13 10:47:38.550

    当前步骤的 RANGE_HI_KEY = 1999-10-13 10:51:19.317

    WHERE 子句中的值 = 1999-10-13 10:48:38.550

    公式

    1）找到两个范围高键之间的ms

    SELECT DATEDIFF (ms, '1999-10-13 10:47:38.550', '1999-10-13 10:51:19.317')

    结果是 220767 毫秒。

    2）调整行数

    我们需要找到每毫秒的行数，但在此之前，我们必须从 RANGE_ROWS 中减去 AVG_RANGE_ROWS：

    6624 - 16.1956 = 6607.8044 行

    3）用调整后的行数计算每毫秒的行数：

    6607.8044 行/220767 毫秒 = .0299311 行/毫秒

    4) 计算 WHERE 子句的值与当前步骤 RANGE_HI_KEY 之间的毫秒数

    SELECT DATEDIFF (ms, '1999-10-13 10:48:38.550', '1999-10-13 10:51:19.317')

    这给了我们 160767 毫秒。

    5）根据每秒行数计算这一步中的行数：

    .0299311 行/毫秒 * 160767 毫秒 = 4811.9332 行

    6) 还记得我们之前是如何减去 AVG_RANGE_ROWS 的吗？是时候把它们加回去了。现在我们已经完成了与每秒行数相关的计算，我们也可以安全地添加 EQ_ROWS：

    4811.9332 + 16.1956 + 16 = 4844.1288

    四舍五入，这是我们的 4844.13 估计值。

    测试公式

    我找不到任何关于为什么在计算每毫秒行数之前减去 AVG_RANGE_ROWS 的文章或博客文章。我能够确认它们已计入估算中，但只是在最后一毫秒——字面上。

    使用WideWorldImporters 数据库，我做了一些增量测试，发现行估计的减少是线性的，直到步骤结束，突然考虑了 1x AVG_RANGE_ROWS。

    这是我的示例查询：

    SELECT PickingCompletedWhen
FROM Sales.Orders
WHERE PickingCompletedWhen >= '2016-05-24 11:00:01.000000'

    我更新了 PickingCompletedWhen 的统计数据，然后得到了直方图：

    DBCC SHOW_STATISTICS([sales.orders], '_WA_Sys_0000000E_44CA3770')

    _WA_Sys_0000000E_44CA3770 的直方图（最后 3 个步骤）

    为了了解随着我们接近 RANGE_HI_KEY，估计的行数如何减少，我在整个步骤中收集了样本。减少是线性的，但表现得好像等于 AVG_RANGE_ROWS 值的行数不属于趋势……直到您达到 RANGE_HI_KEY，它们突然下降，就像未收回的债务被注销一样。您可以在样本数据中看到它，尤其是在图表中。

    在此处输入图像描述

    请注意行数稳步下降，直到我们达到 RANGE_HI_KEY，然后 BOOM 突然减去最后一个 AVG_RANGE_ROWS 块。在图表中也很容易发现。

    在此处输入图像描述

    总而言之，对 AVG_RANGE_ROWS 的奇怪处理使得行估计的计算更加复杂，但您始终可以协调 CE 正在做的事情。

    指数退避呢？

    指数退避是新的（自 SQL Server 2014 起）基数估计器在使用多个单列统计信息时用于获得更好估计的方法。因为这个问题是关于一个单列统计的，所以它不涉及EB公式。

    • 6

相关问题