时间戳

首页 > 解决方案 > SQL Server 可以自己执行基于哈希的行相等（用于 MERGE 操作）吗？

sql-server - SQL Server 可以自己执行基于哈希的行相等（用于 MERGE 操作）吗？

问题描述

我正在开发的系统使用 SQL Server 将查询结果存储在表中。行组代表特定日期 ( DataDate) 的数据快照，最终随着新数据的替换而过期。该表保留旧数据而不是覆盖它。这是一个简单的例子：

CREATE TABLE query1234Results (
    -- Metadata columns:
    AccountId   int      NOT NULL,
    DataDate    date     NOT NULL,
    Appeared    datetime NOT NULL,
    Disappeared datetime     NULL,
    Updated     datetime NOT NULL,

    -- Query result columns:
    TotalSales                money,
    NewCustomers              int,
    CountHovercraftFullOfEels float,
    SumScratchedTobacconists  decimal
    -- etc...

    PRIMARY KEY ( AccountId, DataDate, Appeared )
)

当应用程序接收到该表的数据时，它会使用这样的MERGE查询将其插入到表中；

MERGE query1234Results AS t
USING @tableValuedParamter AS s ON
    t.AccountId = s.AccountId AND
    t.DataDate  = s.DataDate  AND
    t.Disappeard IS NULL AND

    t.TotalSales   = s.TotalSales AND
    t.NewCustomers = s.NewCustomers AND
    t.CountHovercraftFullOfEels = s.CountHovercraftFullOfEels AND
    t.SumScratchedTobacconists = s.SumScratchedTobacconists,
    -- etc

WHEN MATCHED THEN UPDATE SET
    t.Updated = GETUTCDATE()

WHEN NOT MATCHED BY TARGET THEN
    INSERT (
        AccountId,
        Appeared,
        Disappeared,
        Updated,

        TotalSales,
        NewCustomers,
        CountHovercraftFullOfEels,
        SumScratchedTobacconists,
        -- etc
    )
    VALUES (
        s.AccountId,
        s.Appeared,
        NULL,
        GETUTCDATE(),

        s.TotalSales,
        s.NewCustomers,
        s.CountHovercraftFullOfEels, 
        s.SumScratchedTobacconists,
        -- etc
    )

WHEN NOT MATCHED BY SOURCE AND
    t.AccountId = s.AccountId AND
    t.Disappeared IS NULL

    THEN UPDATE SET
        t.Disappeared = GETUTCDATE(),
        t.Updated     = GETUTCDATE()

这种设计适用于小型数据集，但是当MERGE查询需要INSERT超过 100,000 条记录（即单个 100,000 天的数据AccountId）并且表具有 30+ 查询结果列时，它会遇到您所期望的性能问题。

部分问题是 SQL Server 需要对每个非元数据列执行相等性检查，以便确定它是否应该INSERT数据或UPDATE它。

显然，这可以通过添加正确的索引来改进，但我觉得我需要添加一个包含每个非元数据列的索引，以便MERGE快速运行比较。我认为这种方法不会很好地扩展。

存在一种解决方法，使用哈希值策略来计算所有非元数据值的哈希值，并仅将其用于MERGE连接条件，如下所示：

  1. 添加另一个元数据列，Hash bigint该列存储从即将成为的数据的非元数据列计算的 64 位 MurmurHashV2 MERGE。

  2. 更改MERGE为：

    MERGE query1234Results AS t
USING @tableValuedParamter AS s ON
    t.AccountId = s.AccountId AND
    t.DataDate  = s.DataDate  AND
    t.Disappeard IS NULL      AND
    t.Hash      = s.Hash

WHEN MATCHED...

存在哈希冲突风险的问题，因此MERGE查询仍必须对所有值字段进行比较 - 但 SQL 不提供短路AND/OR运算符，所以我不知道如何指示 SQL Server 仅仅当哈希比较成功时才进行昂贵的值比较。

这让我想到，实际上在行本身中包含值的散列违反了关注点分离：散列值应该存储在 DBMS 维护的某个索引中，并且只有 DBMS 应该关注这一点，不是我的应用程序逻辑。

...但我知道，如果我确实在所有非元数据列上创建索引，它将创建一个传统的基于 B-Tree 的索引，该索引必然会很大，因为它将存储源表中的每个唯一值。

我的问题：SQL Server 有没有办法创建一个基于哈希的索引来优化多列相等性检查，而无需在我的应用程序域中合并这个哈希逻辑？如果不是，我提出的将哈希存储在表中的方法是最好的替代方法吗？

标签： sql-server

解决方案

部分问题在于 SQL Server 需要对每个非元数据列执行相等性检查，以便确定是应该插入数据还是更新数据。

在我回答其余的问题之前，我想确保这确实是问题所在。您如何确定相等比较的次数是问题所在？我个人很难确定这一点。您可以通过删除大部分比较并使用您知道不需要条件的测试数据来测试它。

我怀疑这不会是问题。

虽然我无法告诉您 SQL Server 是否会缩短这些AND条件（but SQL doesn't provide for short-circuiting AND/OR operators），但我怀疑它确实会。的规范AND没有谈论评估，但它没有。它只谈论语义。语义不允许您区分是否存在短路。因此，SQL Server 可以执行此优化。这似乎是一个常见的优化案例。

当然，如果数据使得大多数条件无论如何都必须进行评估，那么短路可能无济于事。但是，我再次怀疑这一点是没有实际意义的。

显然，这可以通过添加正确的索引来改善

添加一个足够具体的索引就足够了，以便大多数比较由索引处理。索引的目的是避免二次检查成本（每个源行对每个目标行）。添加所有其他列将无助于提高性能。SQL Server 相等性是在索引操作期间还是在后续筛选步骤中进行比较并不重要。也许过滤器甚至更快（？）。

存在一种解决方法，使用哈希值策略来计算所有非元数据值的哈希值，并仅将其用于 MERGE 连接条件，如下所示：

如果您发现这些比较确实会损害性能，那么这种策略是有意义的。如果您希望哈希在大多数情况下不匹配，请先进行比较。很可能，由于短路，所有其他比较都会消失。如果哈希在大多数情况下都匹配，那么它将无济于事，因为无论如何都必须评估其他比较。您可以使用加密哈希，这样您就不必检查每一列。对于实际问题，加密哈希是完全可靠的。

这让我想到，实际上在行本身中包含值的散列是违反关注点分离的

如果它对你有用，那没关系。这真的是唯一重要的标准。不要根据这样的一揽子规则做出决定。考虑每一个问题，权衡它们并决定查看整个解决方案。

SQL Server 有没有办法创建基于哈希的索引

Hekaton 有一个基于哈希的索引。这会奏效。但这也意味着每个查询必须散列源数据以匹配。你不能预先计算它。

推荐阅读