sql - 匹配列值作为前缀

Question

我在我的 SQL Server 表中存储字符串前缀，我想看看这些值中的任何一个是否是给定参数值的有效前缀。

例如假设我有一个电话拒接电话列表，其中包含一个禁止调用以“1425123”开头的号码的条目，而不是插入 10000 个号码(14251230000到 14251239999) 它存储前缀。

像这样:

CREATE TABLE Prefixes (
    Value varchar(10)
)

CREATE INDEX IX_Value UNIQUE Prefixes ( Value )

这样计算:

DECLARE @value varchar(10) = 'foobar'

SELECT
    *
FROM
    Prefixes
WHERE
    @value LIKE ( Value + '%' );

当我在 SQL Server Management Studio 的 Azure SQL 中运行它时，它说它正在执行索引扫描。 Azure SQL S1 数据库中大约有 70,000 个条目，查询需要 200 到 500 毫秒才能执行。该工具不建议对索引进行任何改进以提高性能。

为了进行比较，精确相等匹配 (Value = @value) 使用 Index Seek 并且几乎立即发生。

200-500 毫秒对我的应用来说太慢了。

一种选择是使用 Trie 将查找移动到我的应用程序代码中以进行高效的前缀搜索(这会引入同步问题)，但另一种方法是将查询更改为如下所示:

DECLARE @v1 varchar(1) = LEFT( @value, 1 )
DECLARE @v2 varchar(2) = LEFT( @value, 2 )
DECLARE @v3 varchar(3) = LEFT( @value, 3 )
DECLARE @v4 varchar(4) = LEFT( @value, 4 )
DECLARE @v5 varchar(5) = LEFT( @value, 5 )
DECLARE @v6 varchar(6) = LEFT( @value, 6 )
DECLARE @v7 varchar(7) = LEFT( @value, 7 )
DECLARE @v8 varchar(8) = LEFT( @value, 8 )
DECLARE @v9 varchar(9) = LEFT( @value, 9 )

SELECT
    *
FROM
    Prefixes
WHERE
    Value = @v1 OR
    Value = @v2 OR
    Value = @v3 OR
    Value = @v4 OR
    Value = @v5 OR
    Value = @v6 OR
    Value = @v7 OR
    Value = @v8 OR
    Value = @v9

当我运行它时，速度要快得多(使用索引查找)，但感觉就像一个 hack，但因为我知道长度少于 10 个字符，所以我可以接受它......暂时。

有没有更好的方法？ SQL Server 有没有一种方法可以在内部进行前缀匹配(即在我的最后一个示例中使用相同的逻辑，但不使用重复和脆弱的 SQL)？

Answer 1

这是辅助数字表可以帮助解决的问题。

因为您只需要 1-10，所以我在查询中内联了一个，而不是假设一个存在。

如果您有或可以创建一个永久数字表，您可以通过将派生表 V 替换为对永久数字表的引用来缩短代码。

SELECT IIF(EXISTS (SELECT *
                   FROM   (VALUES(1),(2),(3),
                                 (4),(5),(6),
                                 (7),(8),(9),(10)
                          ) V(number)
                          JOIN Prefixes P WITH(FORCESEEK)
                            ON P.Value = LEFT(@value, number)
                   WHERE  number <= LEN(@value)), 1, 0) AS PrefixExists 

  |--Compute Scalar(DEFINE:([Expr1014]=CASE WHEN [Expr1015] THEN (1) ELSE (0) END))
       |--Nested Loops(Left Semi Join, DEFINE:([Expr1015] = [PROBE VALUE]))
            |--Constant Scan
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Union1010]))
                 |--Filter(WHERE:([Union1010]<=len([@value])))
                 |    |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10))))
                 |--Index Seek(OBJECT:([tempdb].[dbo].[Prefixes].[IX_Value] AS [P]), SEEK:([P].[Value]=substring([@value],(1),[Union1010])) ORDERED FORWARD)