sql - 为什么 SUBSTRING 或 LEFT 会使查询变慢？

Question

我有一个永远不应该联系的人的黑名单。当我想查看某个人是否在此列表中时，我会执行以下操作:

-- Query 1
SELECT * 
FROM bldb.dbo.blacklist l
WHERE l.matchcode
    = dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert')

查询运行得非常快，因为 matchcode 列上有一个索引，并且 fn_matchcode 是确定性的。

将 matchcode 视为地址和名称的压缩形式，这有助于我不受街道名称等拼写错误的影响。它由 22 个字符组成:地址 13 个，名称 9 个。当我想查看 1 Sesame Street, 12345 中的任何人是否在黑名单中时，我执行以下操作:

-- Query 2
SELECT * 
FROM bldb.dbo.blacklist l
WHERE LEFT(l.matchcode,13)
    = LEFT(dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert'),13)

这运行时间非常长...

相反，这运行得更快:

-- Query 3
SELECT * 
FROM bldb.dbo.blacklist l
WHERE LEFT(l.matchcode,13)
    = (SELECT LEFT(dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert'),13))

这意味着，为每一行计算 where 条件的右侧!但为什么？ UDF 是确定性的。 LEFT() 是不是确定性的？

编辑:

到目前为止的答案声称这是因为索引没有被使用。但是，我仍然不清楚为什么会发生以下情况。

当我这样写查询时:

-- Query 4
SELECT * 
FROM bldb.dbo.blacklist
WHERE matchcode LIKE LEFT(dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert'),13) + '%'

仍然需要几分钟才能完成。请注意，fn_matchcode 只是进行一些字符串操作并立即返回。

当我将 fn_matchcode 的结果硬编码到查询中时:

-- Query 5
SELECT * 
FROM bldb.dbo.blacklist
WHERE matchcode LIKE '12345SSMSTRT1%'

这需要几毫秒!你会如何解释？

Answer 1

在您的问题更新后，您能否查看查询 #4 和 #5 的两个执行计划，看看它是否对一个进行聚集索引扫描，对另一个进行非聚集索引搜索？我想知道是不是因为它在编译时知道文字的统计信息，但不知道函数调用的统计信息。由于它不知道只会返回少量记录，因此它会谨慎行事，避免进行大量书签查找。

如果是这种情况，那么以下是否有帮助？

SELECT * 
FROM bldb.dbo.blacklist WITH (FORCESEEK)
WHERE matchcode LIKE 
  LEFT(dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert'),13) + '%'