主页 / dba / 问题 / 345334
Accepted
Paul White
Asked: 2025-02-18 16:34:13 +0800 CST

没有必要使用 TOP PERCENT 进行排序？

  • 772

设置

-- Create a heap table of numbers from 1 to 100
SELECT TOP (100)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

测试查询

按主键顺序显示 9% 的行：

SELECT TOP (9e) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

db<>fiddle 演示

结果

SSMS 结果

执行计划

SSMS 执行计划

问题

当聚集索引提供确切的顺序时，为什么 SQL Server 会对该列进行排序？

更多行

如果我增加表中的行数，我会得到一个 Eager Spool 而不是 Sort，并且按顺序扫描索引：

-- Create a heap table of numbers from 1 to 1,000
SELECT TOP (1000)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

-- 0.9% now
SELECT TOP (9e-1) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

计划包含 1,000 行

sql-server

2 个回答

  1. Best Answer

    电子核心答案

    我最终会被来自新西兰的绅士（或者可能是来自橄榄球的绅士）扣篮™️，但我不会让这阻止我在 p 核忙着烤面包和泡茶时以最高的 e 核速度旋转。

    排序与假脱机

    Sorts 和 Eager Table Spools 都充当阻塞运算符来接收扫描中的所有行。它们的工作是计算行数。

    • 对于排序来说，行到达的顺序并不重要，但是它们都必须在排序开始按照要求的顺序进行之前到达。

    • 使用 Eager Spool 时，它只是一个 Table Spool，而不是 Index Spool，因此行必须按顺序到达。因此在这种情况下是有序扫描。Spool 可以保留顺序，但不提供排序机制。

    但他们的工作仍然一样！统计所有出现的行，以回答 Top 提出的重要问题。

    顶部

    Top 需要知道 9% 是多少。它直到 Sort 或 Spool 从 Scan 获得所有行后才知道。

    Top 的工作是将到达 Sort 或 Spool 的行数的 9% 设置为一个 Row Goal，每次请求一行，直到满足 Row Goal 为止。

    为了实现这一点，它进行了一些数学运算©️。

    这篇文章最初写在 Crayola Crayons

    • 16

  2. 理解为什么需要排序或假脱机的关键是思考计划是如何运作的。

    战略

    Top运算符需要将返回的行限制为潜在完整结果的某个百分比。要知道何时停止，它需要知道总行数，以计算该百分比代表多少行。

    在这个简单的示例中，行计数信息可能来自源表的已知基数。在具有连接、过滤器和聚合的更复杂情况下，除了在正确的点（即就在Top之前）物理地计数行之外，没有其他选择。

    为了确保我们计算了所有行，我们需要一个阻塞运算符。SQL Server 使用的两个候选运算符是Sort或Spool。

    成本核算

    当TOP PERCENT与子句相关联时ORDER BY，优化器首先考虑排序，因为始终需要有序输入。如果较低的运算符自然提供排序行（例如作为有序查找或扫描的结果），则该排序可能会在稍后被消除。

    如果完整计划中出现排序选项，且该选项足够便宜，则优化器不会费心寻找更便宜的选项。否则，它还会生成Spool替代方案并计算其成本。

    对于非常小的输入，成本模型为Sort产生的成本低于Spool 。这意味着即使优化器对两种替代方案都进行了成本计算，它仍然可能会选择 sort。

    我们在这里讨论的是很小的差异，所以这没什么大不了的。不过，成本模型假设Spool的启动成本略高于Sort ，而Sort 的每行​​成本更高。

    订购

    如果选择排序作为获取总行数的更便宜的选项，则不需要特别请求从其输入子树进行排序 - 无论哪种情况都会返回正确的结果。

    因此，索引扫描具有这样Ordered:False的特性，即由存储​​引擎决定如何获取行。在此示例中，这几乎肯定意味着行实际上将按聚集索引顺序返回，因为表太小而不符合分配有序扫描的条件。

    另一方面，Spool选项无法对行进行排序，因此需要其输入树来生成已排序的行，然后 spool 会保留这些行。优化器决定获取有序数据的最便宜方法是通过Ordered:True属性向存储引擎请求有序的聚集索引扫描。

    需要计算行数并按正确的顺序显示它们，这解释了为什么该计划包含Sort或Spool。

    内部

    当Open()在 Top 运算符上调用时，它会打开其子树。Sort或Spool会在此阶段消耗其全部输入。当执行返回到 Top 运算符（仍处于其打开阶段）时，Sort 或 Spool 已完全填充。表访问已结束（包括其调用）。 OpenClose()

    Top 的下一件事是重置。它可能会为Segment Top执行多次此操作，但在本例中，它只会在开始时发生一次。这是Top将指定百分比转换为确定的行数的地方。

    合作

    该计划唯一引人注目的地方是Top与其子Sort或Spool之间的协作，当Top向其子运算符询问其总行数时：

    顶部询问其子项进行统计排序
    顶部询问其子项进行统计排序

    Top 向其子 Spool 询问统计信息
    Top 向其子 Spool 询问统计信息

    将请求的百分比转换为确定的行数（如果结果有小数部分则向上舍入）后，Top继续在其阶段中正常处理，从Sort或SpoolGetRow()中一次请求一行，直到在Top上看到目标行数。

    为了完整起见，请注意Top每次也返回一行。这些结果行被打包到输出缓冲区中，准备传输到客户端。

    最后，有趣的是，如果优化器考虑了Spool选项，它会使用名为 的探索规则生成此替代方案EnforceHPandAccCard。此计划中不需要万圣节保护(HP)，但我们确实需要访问（或累积）卡的完整性。

    • 12

相关问题