【sqlServer】唯一约束字段过长的问题及后续库表设计的反思

最近维护库表的时候经常遇到字段长度超出唯一约束字段最大长度的报错，例如：
Warning! The maximum key length is 900 bytes. The index ‘testa$BPK_AK_Key’ has maximum length of 8003 bytes. For some combination of large values, the insert/update operation will fail.
下面分享下笔者关于这个问题的思考和解决方法。

临时的解决方法

当时的做法是：

1. 和需求方沟通，能否更换唯一约束的相关字段，答复是由于业务的特殊性，不能更换唯一约束字段；
2. 由于实际字段内容很长（4000以上），但判断唯一性并不需要全部字段，所以增加了一个字段，取该字段的一部分，然后用该字段来做唯一约束；

关于唯一约束这个问题，当时思考的解决方法有三个：

1. 数据库层面唯一约束，但是字段长度有限制（用字段一部分做唯一约束有隐患）；
2. 程序层面，先查后插；
3. 数据直接入库，后续校验剔除。
   优劣：
   方法一从效率和实时性角度来说是最快的，但是对唯一约束的字段要求比较高，最好能做到短小精悍，避免内容作ID；
   方法二会减慢入库速度，而且字段过长也无法建立索引，随着数据的增长，入库速度会越来越慢；
   方法三会存在及时性问题。

后续反思还是想从数据库角度做一些优化

这个问题的从数据角度出发其实就是如何高效的生成长字段的ID，联想到以前用来做字段加密的MD5，打算尝试下生成字符串的HASH值，用HASH值来做唯一约束。

效率测试

set statistics time onset statistics io on--创建测试表if(object_id('testa') is not null )   drop table testaGOcreate table testa ([ID] bigint IDENTITY(1,1) NOT NULL  PRIMARY KEY,[CODE] nvarchar(MAX)  NOT NULL  ,[CODE_ID] varchar(32)  NOT NULL  ,[DATE_VAL] date  NULL )--Insert 100000rows DATAinsert into testa(code,code_id,date_val) (select scope,'' code_id,ESTABLISHMENT_DATE from testexample where ID <=100000)/*SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 3 ms.Table 'testa'. Scan count 0, logical reads 303787, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'qichacha_company'. Scan count 1, logical reads 4570, physical reads 0, read-ahead reads 0, lob logical reads 190793, lob physical reads 505, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 1735 ms,  elapsed time = 4864 ms.(100000 行受影响)*/--update code_idupdate testa set code_id=right(sys.fn_VarBinToHexStr(hashbytes('MD5',substring(code,1,datalength(code)/*原表字段text类型，需要先截取*/))),32) /*SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 2 ms.Table 'testa'. Scan count 1, logical reads 330452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 1, logical reads 239168, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 18609 ms,  elapsed time = 20742 ms.(100000 行受影响)*/--更新时插入HASHtruncate table testa;insert into testa(code,code_id,date_val) (select scope,right(sys.fn_VarBinToHexStr(hashbytes('MD5',substring(scope,1,datalength(scope)/*text类型，需要先截取*/))),32) code_id,ESTABLISHMENT_DATE from testexample where ID <=100000)/*SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 4 ms.Table 'testa'. Scan count 0, logical reads 336400, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'qichacha_company'. Scan count 1, logical reads 4570, physical reads 0, read-ahead reads 0, lob logical reads 481586, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 1, logical reads 257873, physical reads 0, read-ahead reads 0, lob logical reads 190922, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 24500 ms,  elapsed time = 26639 ms.(100000 行受影响)*/

测试下来开发环境生成HASH值的速度差不多5000rows/s，在正式环境效率会有不小的提升。基本满足入库的速度要求

唯一性测试

--hash唯一约束测试truncate table testa;--增加唯一约束ALTER TABLE dbo.testa ADD CONSTRAINT testa$BPK_AK_Key UNIQUE NONCLUSTERED(CODE_ID,DATE_VAL) WITH(ONLINE=ON,FillFactor=90)insert into testa(code,code_id,date_val) (select scope,right(sys.fn_VarBinToHexStr(hashbytes('MD5',substring(scope,1,datalength(scope)/*text类型，需要先截取*/))),32) code_id,ESTABLISHMENT_DATE from testexample where ID between 1001 and 2000)select * from testainsert into testa(code,code_id,date_val) values( '青藏高原天然优势资源的综合开发、利用、生产、销售及咨询服务；其他生物资源开发利用；矿产品加工、销售；经济信息咨询服务；实业投资及开发。' ,right(sys.fn_VarBinToHexStr(hashbytes('MD5', '青藏高原天然优势资源的综合开发、利用、生产、销售及咨询服务；其他生物资源开发利用；矿产品加工、销售；经济信息咨询服务；实业投资及开发。')),32) ,'1998-08-28' )/*消息 2627，级别 14，状态 1，第 104 行Violation of UNIQUE KEY constraint 'testa$BPK_AK_Key'. Cannot insert duplicate key in object 'dbo.testa'. The duplicate key value is (34a6e181f83aa27d26b1b01bb0c37b55, 1998-08-28).*/

唯一约束测试通过

小结：

1. 从表设计角度，唯一约束应当选择短小的字段（应该避免长字符串/文本内容做唯一约束，长字段从数据库系统的开销和维护上来说相当不利），唯一约束所有字段总的字段长度最好小于100（sqlserver数据库最大长度900，可以用datalength()函数查询长度）。
2. 若业务角度无法避免用长字符串/文本做唯一约束，建议取字段ID来做唯一约束，对于无法预先编制ID的长字符串/文本，建议用HASH值做ID，再用ID来做唯一约束的字段。
3. 关于生成HASH值的方式，建议在程序端生成，下沉到数据库生成会加大数据库CPU的负载压力（特别在入库数据量大，HASH计算量大的时候），影响入库效率。