在索引 VARCHAR 列是个好主意/方法吗?这个概念是使用 VARCHAR 作为列。
我想知道,如果我们将SHA-1
散列或SHA-256
散列(如果我是偏执狂)存储为BINARY(20)
列并在该列上建立索引。
在应用程序端对短字符串执行SHA-1
计算已经足够快了,我们只需通过 SHA 值进行查询。
可能我认为长度VARCHAR
会徘徊在 10 到 30 个字符左右,有些会更长但概率更低。
在索引 VARCHAR 列是个好主意/方法吗?这个概念是使用 VARCHAR 作为列。
我想知道,如果我们将SHA-1
散列或SHA-256
散列(如果我是偏执狂)存储为BINARY(20)
列并在该列上建立索引。
在应用程序端对短字符串执行SHA-1
计算已经足够快了,我们只需通过 SHA 值进行查询。
可能我认为长度VARCHAR
会徘徊在 10 到 30 个字符左右,有些会更长但概率更低。
这个问题有 [Performance] 标签,所以我怀疑您可能正在考虑哈希索引。在 SQL Server 中,非聚集索引的最大键长度为1700 字节。无法使用长字符串列作为键列来创建非聚集索引。例如,对于下表:
尝试创建此索引:
失败并出现此错误:
如果您需要对该列进行相等搜索,则查询优化器必须进行表扫描。例如,以下查询在我的机器上大约需要 0.6 秒:
一种替代方法是在列上创建哈希索引,并对哈希索引和列本身执行相等搜索。
CHECKSUM()
可能是最好的选择,因为您不需要任何加密安全性,并且少量冲突是可以接受的。你主要想要一些小而快的东西。下面的代码添加一个计算列并在该列上创建一个索引:下面的查询返回与初始查询相同的结果,但 SQL Server 能够使用索引。它在我的机器上在 0.01 秒内完成。
当密钥长度太长而无法允许非聚集索引或磁盘空间非常宝贵时,哈希索引是一个不错的选择。在您的问题中,您估计列的长度约为 10 到 30 个字符,对于您的场景而言,增加的复杂性可能不值得。
如果数据平均每条记录大约 20 个字节或更多,那么您不会节省任何空间,因此在搜索值时不会减少页面访问,因此几乎可以肯定这样做没有任何好处,因此您会增加应用程序的复杂性没有收获。
实际上,您将使用额外的空间,因为您需要存储原始值以及哈希结果,这可能会减慢需要键查找或执行扫描的查询,除非您预计会有很多重复值,在这种情况下您可以存储在另一个表中去重的实际值。
您还将无法使用索引执行任何类型的范围查询(
WHERE name LIKE 'D%'
例如可能不需要的数据)。如果你真的很偏执,你根本不会使用 SHA,因为 SHA 被设计为一种快速执行的哈希算法,并且可以快速在 CPU 上被暴力破解,或者在 GPU 上非常快(每秒数百万次哈希),此外存在巨大的彩虹表......你应该考虑河豚/双鱼/三鱼/氩,因为这些算法被设计为在 GPU 上运行(非常)慢,使得暴力破解几乎不可能。
我什至不会考虑在索引中添加散列列,因为数据库旨在尽可能快地返回,因为您可以做的更糟糕的事情是检查
'a' = 'abdgahsdgdu'
哪个是“快速”返回 false 数据库或多或少地做同样的事情在引擎盖下,这开启了(可能的)与定时攻击相关的攻击。-雷蒙德-奈兰以这种方式使用哈希实际上可以减少与数据长度/模式相关的时序攻击向量,因为查找和比较时间将更加一致。但无论如何,数据库并不是处理这个问题的正确层,IMO。-大卫斯皮莱特
非常真实,应用程序需要简单地使用
SELECT password FROM users WHERE user_name = '<username>'
和使用时间安全的哈希比较函数来保证安全,其中用户名可以安全地被索引。-雷蒙德-奈兰我能想到一个额外的好处,但这主要是由于 JPA、Spring 和 MySQL 的限制。
MySQL 默认不区分大小写,除非您使用 MySQL 特定的结构,如
BINARY
incolumnDefinition
或utf8mb4_bin
因此导致可移植性/排序问题。在这种情况下,解决方法是创建一个包含 SHA-1 的索引列,该列不会被 JPA/Spring/Hibernate/MySQL 翻译。