SQL Server去除重复行、键管理及表关联关系改造方案咨询

核心思路为结合哈希快速匹配和全字段兜底校验，同时满足性能和数据安全性要求。

表结构改造 #

• 保留联系人表原主键ContactID（可使用自增ID、雪花ID等常规唯一ID生成逻辑即可）
• 新增contact_hash字段，长度固定为64位（适配SHA256输出长度），为该字段创建普通B+树索引，不需要设置唯一约束

新增联系人写入逻辑 #

1. 先对联系人所有业务字段（排除ContactID）按固定字段顺序序列化后计算SHA256哈希值，得到contact_hash，示例代码如下：

# 按字段名升序排序后再序列化，避免字段顺序不同导致相同内容生成不同哈希
sorted_contact = sorted(contactDict.items(), key=lambda item: item[0])
contact_hash = sha256(json.dumps(sorted_contact).encode("utf-8")).hexdigest()

2. 用计算得到的contact_hash查询联系人表，由于该字段有索引，查询性能极高
   • 无匹配记录：直接插入新的联系人数据，写入所有业务字段和对应的contact_hash，返回新生成的ContactID即可
   • 有匹配记录：对查询到的所有结果做15个业务字段的全量精确匹配
     • 存在完全一致的记录：直接返回现有记录的ContactID
     • 无完全一致的记录：说明触发极小概率哈希碰撞，插入新的联系人数据，返回新生成的ContactID即可

存量数据清洗逻辑 #

• 给所有存量联系人记录批量计算contact_hash值
• 按contact_hash分组，组内再做全字段去重，每组仅保留唯一的全字段记录，生成新的ContactID
• 建立旧ContactID到新ContactID的映射表，批量更新交易表的BuyerID、SellerID为映射后的新ID
• 清理联系人表中的重复旧数据即可

方案优势 #

• 绝大多数场景下仅需走哈希索引查询，性能和纯哈希方案基本一致
• 增加全字段校验兜底，完全消除哈希碰撞带来的数据风险，安全性和全字段匹配方案一致
• 无需修改主键生成规则，兼容现有业务逻辑对ContactID的使用习惯

内容的提问来源于stack exchange，提问作者M. Stolte