SQL Server 2005 中的 CHECKSUM() 冲突

Question

我有一个包含 5,651,744 行的表，主键由 6 列组成（int x 3、smallint、varchar(39)、varchar(2)）。 我希望提高此表和另一个表的性能，该表共享此主键，并添加了一个附加列，但有 3700 万行。

由于预期添加一列来创建哈希键，我进行了分析，发现了 18,733 个冲突。

SELECT  SUM(CT)
FROM    (
         SELECT HASH_KEY
               ,COUNT(*) AS CT
         FROM   (
                 SELECT CHECKSUM(DATA_DT_ID, BANK_NUM, COST_CTR_NUM,
                                 GL_ACCT_NUM, ACCT_NUM, APPN_CD) AS HASH_KEY
                 FROM   CUST_ACCT_PRFTBLT
                ) AS X
         GROUP BY HASH_KEY
         HAVING COUNT(*) > 1
        ) AS Y

SELECT  COUNT(*)
FROM    CUST_ACCT_PRFTBLT

使用 BINARY_CHECKSUM() 的情况大约是前者的两倍，

考虑到我所覆盖的目标空间的相对量较小，这是否显得太高 (.33%)？ 如果冲突如此之高，那么考虑到您仍然需要连接常规列来处理偶尔的冲突，那么在连接中首先连接这个制造的键是否有好处，而每行需要额外的 4 个字节？

Answer 1

我不知道添加校验和会给你带来那种程度的碰撞。 即使 1 次冲突也太多了，因为它会导致您连接到错误的数据。 如果您不能保证加入正确的记录，那么即使它提高了性能但会破坏数据完整性也是毫无意义的。 这似乎是财务数据，因此您最好确保您的查询不会返回不良结果。 如果存在任何冲突，您实际上可能会借记或贷记错误的账户。

如果您确实走这条路，马克是对的，您应该尽可能进行预计算（根据我的经验，添加必须对数百万记录表中的每条记录进行的计算不太可能提高性能）。 如果您可以执行预先计算的列（并且您需要触发器来使其保持最新），那么您可能不需要加入所有其他六个列来确保没有冲突。 那么你的表现可能会有所提高。 你所能做的就是测试你的理论。 但请确保没有发生任何碰撞。

您是否考虑过使用代理键，然后在六个自然键字段上使用唯一索引？ 然后您可以加入代理键，这可能会大大提高性能。 连接六列（一列是 varchar）而不是一个代理键效率不高。 从数据的大小来看，这可能比非生产系统更难重构，但实际上，为了永久修复持续的性能问题可能值得停机。 只有您可以说这个更改会有多复杂，以及将所有 sps 或查询更改为更好的连接会有多困难。 不过，尝试一下也许是可行的。

Answer 2

到目前为止，我看到很多人掩盖的是 CHECKSUM 有大量冲突，作者：微软自己承认。 它甚至比 MD5 更糟糕，MD5 也有相当多的有意义的冲突。

如果您希望获取哈希列，请考虑使用 HASHBYTES并指定了 SHA1。
与 MD5 或 CHECKSUM 相比，SHA1 的有意义的冲突要少得多。 因此，CHECKSUM 决不应该用于确定行是否唯一，而是用于快速检查两个值的保真度。 因此，您的 HASHBYTES 冲突率应该为 0%，除非您有重复的行（作为 PK，这种情况永远不会发生）。

请记住，HASHBYTES 将截断任何大于 8000 字节的内容，但您的 PK 远小于该值（全部串联），因此您应该不会遇到任何麻烦。

Answer 3

如果您的校验和将其降低到数据的 0.33%，那么我认为它工作正常......特别是如果您将此列与其他（索引）列结合使用。

当然，为了作为索引有效，您可能需要在插入/更新数据时使用非聚集索引来计算和存储该值。

当然，对相关列进行常规跨越索引可能会同样好甚至更好......

Answer 4

如果您的查询是选择性的，并且行表聚集索引很窄或不存在，那么行表中校验和的非聚集索引应该提供良好的性能。

在对头表应用任何标准后，它将使用校验和对非聚集索引执行索引查找。 您仍然需要在连接中包含 FK，但非校验和连接标准将应用于索引查找后、书签查找后。 非常高效。

您想要优化索引查找。 校验和已经是高度选择性的。 添加 FK 会增加索引大小和相应的 I/O，除非它包含足够多的其他字段来完全避免书签查找，否则不会有任何帮助。

由于非聚集索引将包含聚集键或堆指针，因此您需要 a) 一个小的聚集键（例如，int 标识列 - 4 字节指针）或 b) 根本没有聚集索引（8 字节指针） 。

如果您的查询不是选择性的，或者行表聚集索引很大（整个表减去几列），那么我不知道校验和是否有帮助（也许更快的索引导航？）。 在任何情况下，您都希望将其设为聚集索引或覆盖索引，并且如果头表不首先聚集在校验和上，则会进行大量排序。

如果您能负担得起存储和索引成本，那么一些覆盖索引（标题和详细信息）可能是不错的选择。

Answer 5

如果您的 PRIMARY KEY 是聚集的，那么您创建的每个索引都将包含此 PRIMARY KEY。

连接哈希值将使用以下步骤：

1. 在索引键中找到哈希值
   • 在索引数据中找到PRIMARY KEY值
   • 使用Clustered Index Seek 定位表中的PRIMARY KEY 行

行PRIMARY KEY 将仅使用步骤3。

然而，SQL Server 足够聪明，能够考虑到这一点，如果您像这样加入：

SELECT  *
FROM    main_table mt
JOIN    CUST_ACCT_PRFTBLT cap
ON      cap.HASH_KEY = mt.HASH_KEY
        AND cap.DATA_DT_ID = mt.DATA_DT_ID
        AND …
WHERE   mt.some_col = @filter_value

，它不会使用 HASH_KEY 上的索引，而是使用将使用单个Clustered Index Seek 和一个Filter 来确保哈希值匹配（而且它们总是会匹配）。

摘要：只需加入PRIMARY KEY即可。

使用二级索引，您首先需要进行无用的 HASH_KEY 搜索，然后仍然需要加入 PRIMARY KEY。