使用多列唯一索引与单哈希列 [英] Using Multi Column Unique Indexes vs Single Hashed Column

问题描述

我有一个表，需要对多列赋予唯一约束。但是，除了创建多列唯一索引之外，我还可以基于所有必填字段的哈希引入一个额外的列。那么哪一个在数据库性能方面会更有效？

I've a table which I need to give unique constraint to multiple columns. But instead of creating multi column unique index, I can also introduce an extra column based on hashing of all the required fields. So which one will be more effective in terms of database performance?

MySQL建议哈希列方法，但我找不到有关SqlServer的任何信息。

MySQL suggests the hashed column method but I couldn't find any information regarding SqlServer.

推荐答案

您提供的链接指出：

如果，此列为简短，合理唯一且具有索引功能，它可能比许多列上的宽索引要快。

If this column is short, reasonably unique, and indexed, it might be faster than a "wide" index on many columns.

因此，性能提升的确依赖于索引哈希比合并后的多列小得多。鉴于MD5为16字节，因此情况可能并非如此。我会考虑多列索引的平均索引键的宽度要多得多，老实说，我可能还是不会去理会哈希。

So the performance improvement really relies on the indexed hash being quite a bit smaller than the combined multiple columns. This could easily not be the case, given that an MD5 is 16 bytes. I'd consider how much wider the average index key would be for the multi-columnindex, and to be honest I'd probably not bother with the hash anyway.

您如果您愿意，可以使用两种方法对系统进行基准测试。而且，如果潜在的利益没有诱使您尝试这样做，我也不会再打扰了。

You could, if you feel inclined, benchmark your system with both approaches. And if the potential benefits don't tempt you into trying that, again I'd not bother.

我更经常地将该技术用于变更检测，在此检查表行中100个独立列的更改比比较两个散列的计算量大得多。

I've used the technique more often for change detection, where checking for a change in 100 separate columns of a table row is much more compute intensive than comparing two hashes.

这篇关于使用多列唯一索引与单哈希列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！