主页 / dba / 问题 / 164179
Accepted
Alexei
Asked: 2017-02-14 06:50:42 +0800 CST

如何处理 SQL Server 中许多大列的唯一性？

  • 772

我有下表：

CREATE TABLE dbo.Document
(
    DocumentId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DocumentPRIMARY KEY CLUSTERED,
    [Timestamp] datetime2(7) NOT NULL CONSTRAINT DF_Document_Timestamp  DEFAULT (getdate()),
    CreatedBy nvarchar(128) NOT NULL CONSTRAINT DF_Document_CreatedBy  DEFAULT (dbo.getCurrentUser()),
    MonthId int NOT NULL,
    TimeModeId int NOT NULL CONSTRAINT FK_Document_TimeMode REFERENCES usr.TimeMode,
    Key1 bit NOT NULL,
    Key2 int NULL,
    Key3 varchar(max) NULL,   -- sometimes above 8000chars
    Key4 varchar(max) NULL,   -- sometimes above 8000chars
    Key5 varchar(max) NULL,   -- sometimes above 8000chars
    Key6 varchar(max) NULL,   -- sometimes above 8000chars
    Key7 varchar(max) NULL,   -- sometimes above 8000chars
    Key8 int NOT NULL,
    CONSTRAINT FK_Document_BrandType FOREIGN KEY(Key8) REFERENCES dbo.BrandType (Key8),
)

尽管我一直坚持要找到一个更好的自然标识符，但我不得不处理以下自然唯一元组：

MonthId, TimeModeId, Key1, ... , Key8

这对于 UNIQUE 索引来说太大了（在 SQL Server 2014 中最多 900 字节或更少），所以我不得不想出一些办法。我的想法是计算这些列的哈希值，所以我有一个PERSISTED COMPUTED列，如上所示：

FiltersHash  AS (hashbytes('SHA2_256',(
        (((((((((((((((
            (CONVERT(varchar(10),MonthId)+'|') 
            + CONVERT(varchar(4),TimeModeId))
            +'|')+CONVERT(varchar(4),Key1))
            +'|')+isnull(CONVERT(varchar(max),Key2),''))
            +'|')+isnull(CONVERT(varchar(max),Key3),''))
            +'|')+isnull(CONVERT(varchar(max),Key4),''))
            +'|')+isnull(CONVERT(varchar(max),Key5),''))
            +'|')+isnull(CONVERT(varchar(max),Key6),''))
            +'|')+isnull(CONVERT(varchar(max),Key7),''))
            +'|')+isnull(CONVERT(varchar(4),Key8),''))
        ) PERSISTED,
CONSTRAINT UQ_Document_FiltersHash UNIQUE NONCLUSTERED (FiltersHash),

它被证明是有用的，因为通过一个复杂的场景，应用程序试图复制一个文档。

问题：这个解决方案是好的解决方案吗？对于大宽度唯一性问题是否有更简单或更有效的解决方案？

注意：在我的应用程序中，我可以放心地忽略碰撞（即使发生碰撞，后果也很小）。感谢您Aaron Bertrand指出。

sql-server sql-server-2014

1 个回答

  1. Best Answer

    哈希冲突的可能性非常大（正如 Stack Exchange 上其他地方所讨论的：https ://stackoverflow.com/a/4014407 ）。但是，您可以通过添加第二个键来进一步减少它：

    ...
FiltersHash  AS HASHBYTES('SHA2_256', /* Various fields */) PERSISTED,
KeyPrefixes  AS CAST(Key1 AS CHAR(1) + '|' + CAST(Key2 AS VARCHAR(10))
    + '|' + LEFT(Key3, 100) + '|' + LEFT(Key4, 100)
    + '|' + LEFT(Key5, 100) + '|' + LEFT(Key6, 100)
    + '|' + LEFT(Key7, 100) + '|' + CAST(Key8 AS VARCHAR(10)) PERSISTED
...
CREATE UNIQUE INDEX UQ_Docs_BizKey ON Documents (FiltersHash, KeyPrefixes)

    现在，两条记录必须在三个字段和另外五个字段的第一部分上匹配。如果您的数据通常包括垂直管道，请考虑使用替代分隔符。INSERTs 到表中会慢一点，但在您的数据量下，这可能不是问题。

    顺便说一句，假设人们偶尔会按日期范围（“6 月的所有文档”）进行搜索，但很少搜索文档 ID 范围，那么您最好关闭集群MonthID并保留DocumentID非集群 PK。

    • 2

相关问题