主页 / dba / 问题 / 240999
Accepted
Archimedes Trajano
Asked: 2019-06-20 22:18:40 +0800 CST

索引 SHA 哈希代替 VARCHAR

  • 772

在索引 VARCHAR 列是个好主意/方法吗？这个概念是使用 VARCHAR 作为列。

我想知道，如果我们将SHA-1散列或SHA-256散列（如果我是偏执狂）存储为BINARY(20)列并在该列上建立索引。

在应用程序端对短字符串执行SHA-1计算已经足够快了，我们只需通过 SHA 值进行查询。

可能我认为长度VARCHAR会徘徊在 10 到 30 个字符左右，有些会更长但概率更低。

sql-server performance

4 个回答

  1. Best Answer

    这个问题有 [Performance] 标签，所以我怀疑您可能正在考虑哈希索引。在 SQL Server 中，非聚集索引的最大键长度为1700 字节。无法使用长字符串列作为键列来创建非聚集索引。例如，对于下表：

    DROP TABLE IF EXISTS #HASH_INDEX_DEMO;

CREATE TABLE #HASH_INDEX_DEMO (
    ID BIGINT NOT NULL,
    BIG_COLUMN_FOR_U VARCHAR(8000) NOT NULL,
    SMALL_COLUMN VARCHAR(10) NOT NULL
);

INSERT INTO #HASH_INDEX_DEMO WITH (TABLOCK)
SELECT RN, REPLICATE(CHAR(65 + RN % 26), (RN % 43) * (RN % 119)), 'SMALL'
FROM 
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

    尝试创建此索引：

    CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U);

    失败并出现此错误：

    消息 1946，级别 16，状态 3，第 19 行操作失败。索引“I”的长度为 1701 字节的索引条目超过了非聚集索引的最大长度 1700 字节。

    如果您需要对该列进行相等搜索，则查询优化器必须进行表扫描。例如，以下查询在我的机器上大约需要 0.6 秒：

    SELECT ID, SMALL_COLUMN
FROM #HASH_INDEX_DEMO
WHERE BIG_COLUMN_FOR_U = 'A'
OPTION (MAXDOP 1);

    在此处输入图像描述

    一种替代方法是在列上创建哈希索引，并对哈希索引和列本身执行相等搜索。CHECKSUM()可能是最好的选择，因为您不需要任何加密安全性，并且少量冲突是可以接受的。你主要想要一些小而快的东西。下面的代码添加一个计算列并在该列上创建一个索引：

    ALTER TABLE #HASH_INDEX_DEMO ADD BIG_COLUMN_FOR_U_CHECKSUM AS CHECKSUM(BIG_COLUMN_FOR_U);

CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U_CHECKSUM);

    下面的查询返回与初始查询相同的结果，但 SQL Server 能够使用索引。它在我的机器上在 0.01 秒内完成。

    SELECT ID, SMALL_COLUMN
FROM #HASH_INDEX_DEMO
WHERE BIG_COLUMN_FOR_U_CHECKSUM = CHECKSUM('A') AND BIG_COLUMN_FOR_U = 'A'
OPTION (MAXDOP 1);

    在此处输入图像描述

    当密钥长度太长而无法允许非聚集索引或磁盘空间非常宝贵时，哈希索引是一个不错的选择。在您的问题中，您估计列的长度约为 10 到 30 个字符，对于您的场景而言，增加的复杂性可能不值得。

    • 6

  2. VARCHAR 的长度将徘徊在 10 到 30 个字符左右，有些会更长

    如果数据平均每条记录大约 20 个字节或更多，那么您不会节省任何空间，因此在搜索值时不会减少页面访问，因此几乎可以肯定这样做没有任何好处，因此您会增加应用程序的复杂性没有收获。

    实际上，您将使用额外的空间，因为您需要存储原始值以及哈希结果，这可能会减慢需要键查找或执行扫描的查询，除非您预计会有很多重复值，在这种情况下您可以存储在另一个表中去重的实际值。

    您还将无法使用索引执行任何类型的范围查询（WHERE name LIKE 'D%'例如可能不需要的数据）。

    • 3

  3. 如果你真的很偏执，你根本不会使用 SHA，因为 SHA 被设计为一种快速执行的哈希算法，并且可以快速在 CPU 上被暴力破解，或者在 GPU 上非常快（每秒数百万次哈希），此外存在巨大的彩虹表......你应该考虑河豚/双鱼/三鱼/氩，因为这些算法被设计为在 GPU 上运行（非常）慢，使得暴力破解几乎不可能。

    我什至不会考虑在索引中添加散列列，因为数据库旨在尽可能快地返回，因为您可以做的更糟糕的事情是检查'a' = 'abdgahsdgdu'哪个是“快速”返回 false 数据库或多或少地做同样的事情在引擎盖下，这开启了（可能的）与定时攻击相关的攻击。-雷蒙德-奈兰

    以这种方式使用哈希实际上可以减少与数据长度/模式相关的时序攻击向量，因为查找和比较时间将更加一致。但无论如何，数据库并不是处理这个问题的正确层，IMO。-大卫斯皮莱特

    非常真实，应用程序需要简单地使用SELECT password FROM users WHERE user_name = '<username>'和使用时间安全的哈希比较函数来保证安全，其中用户名可以安全地被索引。-雷蒙德-奈兰

    • 0

  4. 我能想到一个额外的好处，但这主要是由于 JPA、Spring 和 MySQL 的限制。

    MySQL 默认不区分大小写，除非您使用 MySQL 特定的结构，如BINARYincolumnDefinition或utf8mb4_bin因此导致可移植性/排序问题。在这种情况下，解决方法是创建一个包含 SHA-1 的索引列，该列不会被 JPA/Spring/Hibernate/MySQL 翻译。

    • -1

相关问题