确定性加密T-SQL/CLR函数需求：加密用户代理表索引优化问询

针对你遇到的用户代理字符串表加密后索引失效的问题，结合确定性对称加密的特性，我有几个实操性的解决方案，帮你重新找回查询性能：

方案1：直接对加密列创建索引（最直接高效） #

既然你用的是确定性对称加密（相同明文生成固定密文），那完全可以直接在UserAgentStringValue列上创建索引。核心思路是：查询时先把要搜索的明文加密，再用加密后的密文去匹配加密列，这样查询就能直接走索引，和旧结构用哈希列查询的效率几乎一致。

操作步骤： #

1. 给加密列建索引：

CREATE INDEX IX_UserAgentString_EncryptedValue ON UserAgentString(UserAgentStringValue);

2. 查询时的正确姿势（先加密搜索明文，再匹配）：

-- 1. 准备要搜索的明文
DECLARE @TargetUserAgent NVARCHAR(4000) = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 ...';

-- 2. 用相同的密钥加密明文（必须和存储时的加密逻辑完全一致）
DECLARE @EncryptedTarget VARBINARY(8000);
OPEN SYMMETRIC KEY UserAgentSymmetricKey 
DECRYPTION BY CERTIFICATE UserAgentEncryptionCert;

SET @EncryptedTarget = EncryptByKey(
    Key_GUID('UserAgentSymmetricKey'),
    @TargetUserAgent
    -- 注意：如果存储时用了@add_authenticator或其他参数，这里必须完全匹配
);

CLOSE SYMMETRIC KEY UserAgentSymmetricKey;

-- 3. 用加密后的值查询，直接走索引
SELECT UserAgentStringID
FROM UserAgentString
WHERE UserAgentStringValue = @EncryptedTarget;

关键注意点： #

• 必须确保加密是确定性的：如果加密时用了@add_authenticator = 1或者随机因子，相同明文的密文会不同，这个方案就失效了。
• 加密逻辑要完全一致：存储和查询时的密钥、编码、是否加验证器等参数必须丝毫不差，否则密文匹配不上。

方案2：保留哈希列思路（兼容旧习惯，更安全） #

如果你担心加密列索引的开销（比如VARBINARY(8000)列的索引体积大），可以借鉴旧结构的哈希列思路，在新表中新增一个明文加盐哈希列，然后对这个哈希列建索引。查询时先匹配哈希快速缩小范围，再验证加密的明文（防止哈希碰撞）。

操作步骤： #

1. 修改表结构，新增哈希列：

ALTER TABLE UserAgentString
ADD UserAgentStringHASH BINARY(32) NOT NULL; -- 用SHA2_256哈希，长度32字节

-- 建哈希列索引
CREATE INDEX IX_UserAgentString_Hash ON UserAgentString(UserAgentStringHASH);

2. 存储数据时，同时计算明文的加盐哈希（建议固定盐，方便查询）：

DECLARE @Plaintext NVARCHAR(4000) = 'Mozilla/5.0 ...';
DECLARE @FixedSalt VARBINARY(16) = 0x1234567890ABCDEF1234567890ABCDEF; -- 自定义固定盐

-- 计算加盐哈希
DECLARE @Hash BINARY(32) = HASHBYTES('SHA2_256', CONCAT(@FixedSalt, @Plaintext));

-- 加密明文（和之前逻辑一致）
DECLARE @Encrypted VARBINARY(8000);
OPEN SYMMETRIC KEY UserAgentSymmetricKey 
DECRYPTION BY CERTIFICATE UserAgentEncryptionCert;
SET @Encrypted = EncryptByKey(Key_GUID('UserAgentSymmetricKey'), @Plaintext);
CLOSE SYMMETRIC KEY UserAgentSymmetricKey;

-- 插入数据
INSERT INTO UserAgentString (UserAgentStringID, UserAgentStringValue, UserAgentStringHASH)
VALUES (1, @Encrypted, @Hash);

3. 查询时的逻辑：

DECLARE @SearchValue NVARCHAR(4000) = 'Mozilla/5.0 ...';
DECLARE @FixedSalt VARBINARY(16) = 0x1234567890ABCDEF1234567890ABCDEF;

-- 计算搜索值的加盐哈希
DECLARE @SearchHash BINARY(32) = HASHBYTES('SHA2_256', CONCAT(@FixedSalt, @SearchValue));

-- 先通过哈希快速筛选，再验证加密明文（避免哈希碰撞）
SELECT u.UserAgentStringID
FROM UserAgentString u
WHERE u.UserAgentStringHASH = @SearchHash
AND DecryptByKey(u.UserAgentStringValue) = @SearchValue;

优势： #

• 哈希列体积小，索引更紧凑，查询速度更快；
• 加盐哈希比旧结构的纯哈希更安全，能抵御彩虹表攻击。

方案3：使用确定性计算列（自动维护，减少手动操作） #

如果不想手动维护哈希列，可以创建一个基于加密列的确定性计算列，对计算列建索引。这个方案让SQL Server自动维护哈希值，不用手动插入或更新。

操作步骤： #

1. 添加确定性计算列并建索引：

ALTER TABLE UserAgentString
ADD EncryptedValueHash AS HASHBYTES('SHA2_256', UserAgentStringValue) PERSISTED;
-- 必须加PERSISTED才能建索引，且HASHBYTES是确定性函数

CREATE INDEX IX_UserAgentString_EncryptedHash ON UserAgentString(EncryptedValueHash);

2. 查询时的逻辑：

DECLARE @SearchValue NVARCHAR(4000) = 'Mozilla/5.0 ...';
DECLARE @EncryptedSearch VARBINARY(8000);

OPEN SYMMETRIC KEY UserAgentSymmetricKey 
DECRYPTION BY CERTIFICATE UserAgentEncryptionCert;
SET @EncryptedSearch = EncryptByKey(Key_GUID('UserAgentSymmetricKey'), @SearchValue);
DECLARE @SearchHash BINARY(32) = HASHBYTES('SHA2_256', @EncryptedSearch);
CLOSE SYMMETRIC KEY UserAgentSymmetricKey;

-- 先匹配哈希快速定位，再验证加密列
SELECT UserAgentStringID
FROM UserAgentString
WHERE EncryptedValueHash = @SearchHash
AND UserAgentStringValue = @EncryptedSearch;

注意点： #

• 计算列必须是确定性的：HASHBYTES函数在使用固定算法时是确定性的，满足要求；
• 同样依赖确定性加密，否则加密值变化会导致哈希值不固定。

内容的提问来源于stack exchange，提问作者gotqn