主页 / dba / 问题 / 96364
Accepted
crokusek
Asked: 2015-03-27 09:41:59 +0800 CST

是否有可能为 distinct/group by 获取基于搜索的并行计划？

  • 772

这个问题的一个例子表明 SQL Server 将选择全索引扫描来解决这样的查询：

select distinct [typeName] from [types]

[typeName] 上有一个非聚集的、非唯一的升序索引。他的示例有 2 亿行，但只有 76 个唯一值。对于这种密度（~76 多次二进制搜索），搜索计划似乎是更好的选择？

他的情况可以正常化，但问题的原因是我真的想解决这样的问题：

select TransactionId, max(CreatedUtc) 
from TxLog 
group by TransactionId

上有一个索引(TransactionId, MaxCreatedUtc)。

使用规范化源 (dt) 重写不会改变计划。

select dt.TransactionId, MaxCreatedUtc
 from [Transaction] dt -- distinct transactions
 cross apply
   (
        select Max(CreatedUtc) as MaxCreatedUtc 
          from TxLog tl
         where tl.TransactionId = dt.TransactionId         
   ) ca

仅将 CA 子查询作为标量 UDF 运行确实显示了 1 次查找的计划。

select max(CreatedUtc) as MaxCreatedUtc
 from Pub.TransactionLog 
 where TransactionID = @TxId;

在原始查询中使用该标量 UDF 似乎可行，但会失去并行性（UDF 的已知问题）：

select t.typeName, 
       Pub.ufn_TransactionMaxCreatedUtc(t.TransactionId) as MaxCreatedUtc
  from Pub.[Transaction] t

交叉应用计划，仅 UDF，使用 UDF

使用内联 TVF 重写会将其恢复为基于扫描的计划。

来自回答/评论@ypercube：

select TransactionId, MaxCreatedUtc        
 from Pub.[Transaction]  t
  cross apply
   (
        select top (1) CreatedUtc as MaxCreatedUtc 
        from Pub.TransactionLog l
        where l.TransactionID = t.TransactionId
        order by CreatedUtc desc                     
   ) ca

使用 top/order 计划

计划看起来不错。没有并行性但毫无意义，因为速度太快了。将不得不在某个时候尝试解决更大的问题。谢谢。

sql-server index

1 个回答

  1. Best Answer

    我有完全相同的设置，并且经历了相同的重写查询阶段。

    在我的例子中，表名和含义有点不同，但整体结构是一样的。你的表Transactions对应于我下面的表PortalElevators。它有大约 2000 行。你的桌子TxLog对应我的桌子PlaybackStats。它有大约 1.5 亿行。它有索引(ElevatorID, DataSourceRowID)，和你一样。

    我将对真实数据运行多个查询变体，并比较执行计划、IO 和时间统计信息。我正在使用 SQL Server 2008 标准版。

    使用 MAX 分组

    SELECT [ElevatorID], MAX([DataSourceRowID]) AS LastItemID
FROM [dbo].[PlaybackStats]
GROUP BY [ElevatorID]

    通过...分组

    按统计分组

    按io分组

    与您一样，优化器扫描索引并聚合结果。减缓。

    个人行

    让我们看看如果我MAX只请求一行，优化器会做什么：

    SELECT MAX([dbo].[PlaybackStats].[DataSourceRowID]) AS LastItemID
FROM [dbo].[PlaybackStats]
WHERE [dbo].[PlaybackStats].ElevatorID = 1

    个人

    优化器足够聪明，可以使用索引并且它会进行一次查找。顺便说一下，我们可以看到优化器使用TOP了运算符，即使查询中没有运算符。这是一个明显的迹象，表明引擎的优化路径MAX和TOP在引擎中有一些共同点，但正如我们将在下面看到的那样，它们是不同的。

    与 MAX 交叉应用

    SELECT
    [dbo].[PortalElevators].elevatorsId
    ,LastItemID
FROM
    [dbo].[PortalElevators]
    CROSS APPLY
    (
        SELECT MAX([dbo].[PlaybackStats].[DataSourceRowID]) AS LastItemID
        FROM [dbo].[PlaybackStats]
        WHERE [dbo].[PlaybackStats].ElevatorID = [dbo].[PortalElevators].elevatorsId
    ) AS CA
;

    与 max 交叉应用

    交叉应用最大统计数据

    与 max io 交叉应用

    优化器仍然扫描索引。它不够聪明，无法转换MAX成TOP并扫描成在这里寻找。减缓。我最初没有想到这个变体，我的下一个尝试是标量 UDF。

    标量 UDF

    我看到获取MAX单个行的计划有索引查找，所以我把这个简单的查询放在标量 UDF 中。

    CREATE FUNCTION [dbo].[GetElevatorLastID]
(
    @ParamElevatorID int
)
RETURNS bigint
AS
BEGIN
    DECLARE @Result bigint;
    SELECT @Result = MAX([dbo].[PlaybackStats].[DataSourceRowID])
    FROM [dbo].[PlaybackStats]
    WHERE [dbo].[PlaybackStats].ElevatorID = @ParamElevatorID;
    RETURN @Result;
END

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,[dbo].[GetElevatorLastID]([dbo].[PortalElevators].elevatorsId) AS LastItemID
FROM
    [dbo].[PortalElevators]
;

    udf

    udf统计

    音频文件

    它确实运行得很快。至少，比Group by. 不幸的是，执行计划不显示 UDF 的详细信息，更糟糕的是，它不显示真实的 IO 统计信息（它不包括 UDF 生成的 IO）。您需要运行 Profiler 来查看函数的所有调用及其统计信息。该计划仅显示 6 个读数。单个行的计划有 4 个读取，因此实数将接近：6 + 2779 * 4 = 6 + 11,116 = 11,122。

    与 TOP 交叉应用

    最终，我发现了CROSS APPLY以及如何应用它 ;-) 在这种情况下。

    SELECT
    [dbo].[PortalElevators].elevatorsId
    ,LastItemID
FROM
    [dbo].[PortalElevators]
    CROSS APPLY
    (
        SELECT TOP(1) [dbo].[PlaybackStats].[DataSourceRowID] AS LastItemID
        FROM [dbo].[PlaybackStats]
        WHERE [dbo].[PlaybackStats].ElevatorID = [dbo].[PortalElevators].elevatorsId
        ORDER BY [dbo].[PlaybackStats].[DataSourceRowID] DESC
    ) AS CA
;

    与顶部交叉应用

    交叉应用顶级统计数据

    与top io交叉申请

    这里的优化器足够聪明，可以进行约 2000 次查找。您可以看到读取次数远低于 的读取次数group by。快速地。

    有趣的是，这里的读取次数 (11,850) 比我用 UDF 估计的读取次数 (11,122) 多一点。表 IO 统计数据CROSS APPLY具有 11,844 次读取和 2,779 次大表扫描计数，这给出了11,844 / 2,779 ~= 4.26每次索引查找的读取次数。最有可能的是，寻找一些值使用 4 次读取和一些 5 次读取，平均 4.26。有 2,779 次查找，但只有 2,130 行的值。正如我所说，在没有分析器的情况下，很难通过 UDF 获得实际的读取次数。

    递归 CTE

    正如评论中指出的那样，Paul White 描述了一种递归索引跳过扫描方法，可以在不执行完整索引扫描的情况下在大型表中查找不同的值，而是递归地进行索引查找。要开始递归，我们需要找到锚点的MINorMAX值，然后递归的每一步都一个一个地添加下一个值。该帖子对其进行了详细解释。

    WITH RecursiveCTE
AS
(
    -- Anchor
    SELECT TOP (1) [ElevatorID], [DataSourceRowID]
    FROM [dbo].[PlaybackStats]
    ORDER BY [ElevatorID] DESC, [DataSourceRowID] DESC

    UNION ALL

    -- Recursive
    SELECT R.[ElevatorID], R.[DataSourceRowID]
    FROM
    (
        -- Number the rows
        SELECT
            T.[ElevatorID], T.[DataSourceRowID]
            ,ROW_NUMBER() OVER (ORDER BY T.[ElevatorID] DESC, T.[DataSourceRowID] DESC) AS rn
        FROM
            [dbo].[PlaybackStats] AS T
            INNER JOIN RecursiveCTE AS R ON R.[ElevatorID] > T.[ElevatorID]
    ) AS R
    WHERE
        -- Only the row that sorts lowest
        R.rn = 1
)
SELECT [ElevatorID], [DataSourceRowID]
FROM RecursiveCTE
OPTION (MAXRECURSION 0);

    递归的

    递归统计

    递归 io

    它非常快，尽管它执行的读取量几乎是CROSS APPLY. 它执行 12,781 次读取Worktable和 8,524 次读取PlaybackStats。另一方面，它执行与大表中不同值一样多的查找。CROSS APPLYwithTOP执行与小表中的行一样多的查找。在我的例子中，小表有 2,779 行，但大表只有 2,130 个不同的值。

    概括

                             Logical Reads       Duration
CROSS APPLY with MAX           482,121          6,604
GROUP BY with MAX              482,123          6,581
Scalar UDF                    ~ 11,122            728
Recursive                       21,305             30
CROSS APPLY with TOP            11,850              9 (nine!)

    我对每个查询运行了三次并选择了最佳时间。没有物理读取。

    结论

    在这个特殊的greatest-n-per-group问题案例中，我们有：

    • n=1;
    • 组数远小于表中的行数；
    • 有合适的指标；

    两种最佳方法是：

    1. 如果我们有一个包含组列表的小表，最好的方法是CROSS APPLY使用TOP.

    2. 如果我们只有大表，最好的方法是Recursive Index Skip Scan。

    • 11

相关问题