sql-server - VBScript-将SHA1作为数字或二进制值存储在SQL Server中

Question

我当前在SQL Server中将我的SHA1值存储为char（40）。我的印象是，可以通过将该字段更改为数字值来提高查找速度。但是，我不确定要使用哪种字段/数据类型将其存储在SQL Server中以及如何在VBScript中进行转换。我应该使用数字还是十进制，我需要使用几位数？

我读过某处建议使用Binary（20）。但是，在VBScript中使用Binary值似乎并不容易，因此我假设最好使用数字值。

当前，这是我的SHA1函数。我将返回的字符串值存储在数据库的char（40）字段中，并使用下面的第二位代码执行查找。

Private Function SHA1(s)
    Dim asc, enc, bytes, outstr, pos
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.SHA1CryptoServiceProvider")
    'Convert the string to a byte array and hash it
    bytes = asc.GetBytes_4(s) 'This is how you use .Net overloaded methods in VBScript
    bytes = enc.ComputeHash_2((bytes))
    outstr = ""
    'Convert the byte array to a hex string
    For pos = 1 To Lenb(bytes)
        outstr = outstr & LCase(Right("0" & Hex(Ascb(Midb(bytes, pos, 1))), 2))
    Next
    SHA1 = outstr
    Set asc = Nothing
    Set enc = Nothing
End Function

这是我的查询功能。它已经运行很快，但是我正在寻找可以优化我的代码的任何方法。如果我确实使用二进制存储数据，那么在查找数据时也将不得不使用它。我想我可能会使用存储过程，这将允许我使用SQL Server函数来回转换。也许那是一条更好的路线。请指教。

Function GetHTTPRefererIDBySHA1(s)
    Dim r
    Set r = Server.CreateObject("ADODB.Recordset")      
    r.open "SELECT httprefererid FROM httpreferer " & _
            "WHERE sha1 = '" & s & "'", con, adOpenForwardOnly, adLockReadOnly
    If Not (r.eof and r.bof) then
        GetHTTPRefererIDBySHA1 = r("httprefererid")
    End If
    r.close
    set r = nothing
End Function

编辑：
多亏了ScottE和Google，我得以显着加快查询速度。这是有关我的解决方案的一些信息。
1）我创建了一个名为SHA1Bin的字段。这是一个类型为binary（20）的字段。
2）当我插入新记录时，我使用存储过程。因为我不太担心空间，所以我将原始的httpreferer值和SHA1二进制值保存在同一表和同一行中。我的存储过程使用HashBytes函数（SQL Server 2008）将原始值转换为SHA1二进制。
3）我在VBScript中的SHA1函数与上面相同，但现在在查找时会使用它。这是GetReferer函数的修改版本：

Function GetHTTPRefererIDBySHA1(s)
    Dim r
    Set r = Server.CreateObject("ADODB.Recordset")      
    r.open "SELECT httprefererid FROM httpreferer WHERE " & _
            "sha1bin = CONVERT(binary(20), 0x" & SHA1(s) & ")", _
            tcon, adOpenForwardOnly, adLockReadOnly

    If Not (r.eof and r.bof) then
        GetHTTPRefererIDBySHA1 = r("httprefererid")
    Else
        '//Insert new record code intentionally omitted
    End If
    r.close
    set r = nothing
End Function

Answer 1

我认为您处在正确的轨道上；但是，您可以做一些事情来加快速度。

SHA1背景

无论您在何处看到SHA1正在使用binary（20），都几乎死了。 SHA1是一条160位消息（20个字节），我们通常以原始格式来处理它-正如您已经知道的那样，因为您是函数将原始二进制文件转换为字符串。

转换为NUMERIC

因此，不管20字节是20字节。您不能将其转换为其他内容以使其更快地执行数据库。尝试将其转换为数字将失败，因为会出现算术溢出错误（数字只有17个字节的空间）。

如何做得更好

您已经完成了一半的工作。如果在VBScript中更容易使用，则可以将数据保留为字符数据类型。另外，您可以将其存储为BINARY（20）;这是我对数据仓库项目采取的方法。如果要将其保留为字符串，请将其设置为CHAR（20）而不是CHAR（40）。 CHAR数据类型存储指定的字节数，即使其中一半为空（对于您来说几乎就是这种情况）。这里的一个“陷阱”是您的函数将希望在字符串的开头呈现一个“ 0x ...”，这在技术上不属于值的一部分，但是在构造您的值时必须指出该值是二进制的SQL语句。这样，您可以使用CHAR（22）或仅在必要时进行串联。无论哪种情况，通过减少字段定义中的字符数，SQL都会执行较少的读取操作来获取数据，从而加快了处理速度。另一种数据类型选择是VARCHAR，它将修剪字符串末尾的空白（同样，较少的读取使查询愉快）。

除此之外，就像完成操作一样对其编制索引。如果尚未执行此操作，请在SHA1列上创建索引，并将httprefererid包含在索引中，查询将仅使用索引进行选择，并且将是最快的查询，因为仅需要数据元素将被阅读。这称为covering index（因为它涵盖了过滤器和选定的列）。该索引看起来像：

create index ix_httpreferer_sha1 on dbo.httpreferer (sha1) include (httprefererid);

希望有帮助！