使用动态喜欢提高 SQL 查询的性能

Question

我需要搜索其名字包含在其他人的名字中(子字符串(的人。

SELECT DISTINCT top 10 people.[Id], peopleName.[LastName], peopleName.[FirstName]       
FROM [dbo].[people] people
INNER JOIN [dbo].[people_NAME] peopleName on peopleName.[Id] = people.[Id]
WHERE EXISTS (SELECT * 
FROM [dbo].[people_NAME] peopleName2 
WHERE peopleName2.[Id] != people.[id] 
AND peopleName2.[FirstName] LIKE '%' + peopleName.[FirstName] + '%')

太慢了！我知道这是因为"'%' + peopleName.[FirstName] + '%'"，因为如果我用像'%G%'这样的硬编码值替换它，它会立即运行。

以我的动态喜欢，我的前 10 名需要 10 秒的时间！ 我希望能够在更大的数据库上运行它。

我能做什么？

Answer 1

看看我关于使用运算符的答案 这里LIKE

如果您使用一些技巧，它可能会非常出色

如果您使用排序规则，您可以获得更快的速度，请尝试以下操作：

SELECT DISTINCT TOP 10 p.[Id], n.[LastName], n.[FirstName]       
FROM [dbo].[people] p
INNER JOIN [dbo].[people_NAME] n on n.[Id] = p.[Id]
WHERE EXISTS (
SELECT 'x' x
FROM [dbo].[people_NAME] n2
WHERE n2.[Id] != p.[id]     
AND 
lower(n2.[FirstName]) collate latin1_general_bin 
LIKE 
'%' + lower(n1.[FirstName]) + '%' collate latin1_general_bin
)

如您所见，我们使用二进制比较而不是字符串比较，这要高得多。

请注意，您正在使用人名，因此您可能会遇到特殊的 unicode 字符或奇怪的重音等问题。

通常EXISTS子句比INNER JOIN子句好，但你也使用了一个DISTINCT，它是所有列的GROUP BY..那么为什么不使用它呢？

您可以切换到INNER JOIN并使用GROUP BY而不是DISTINCT，因此测试COUNT(*)>1的性能将(很少(比测试WHERE n2.[Id] != p.[id]高，尤其是在 TOP 子句提取许多行的情况下。

试试这个：

SELECT TOP 10 p.[Id], n.[LastName], n.[FirstName]
FROM [dbo].[people] p
INNER JOIN [dbo].[people_NAME] n on n.[Id] = p.[Id]
INNER JOIN [dbo].[people_NAME] n2 on 
lower(n2.[FirstName]) collate latin1_general_bin 
LIKE 
'%' + lower(n1.[FirstName]) + '%' collate latin1_general_bin
GROUP BY n1.[Id], n1.[FirstName]
HAVING COUNT(*)>1

在这里，我们也匹配名称本身，因此我们将为每个名称找到至少一个匹配项。 但是我们只需要与其他名称匹配的名称，因此我们将只保留匹配计数大于 1 的行(count(*(=1 表示该名称仅与自身匹配(。

编辑：我使用具有100000行的随机名称表进行了所有测试，发现在这种情况下，LIKE运算符的正常使用比二进制比较差约三倍。

Answer 2

这是一个难题。 我认为全文索引没有帮助，因为您想比较两列。

这并没有留下好的选择。 一种可能性是实现ngrams。 这些是来自字符串的字符序列(例如，连续 3 个(。 从我的名字来看，你会有：

gor
ord
rdo
don

然后，您可以使用它们在另一列上进行直接匹配。然后，您必须执行其他工作以查看一列的全名是否与另一列匹配。 但是 ngram 应该会显着减少工作空间。

此外，实现 ngram 需要工作。 一种方法使用触发器计算每个名称的 ngram，然后将它们插入到 ngram 表中。

我不确定所有这些工作是否值得努力解决您的问题。 但是可以加快搜索速度。

Answer 3

你可以这样做，

With CTE as
(                       
SELECT  top 10 peopleName.[Id], peopleName.[LastName], peopleName.[FirstName]       
FROM 
[dbo].[people_NAME] peopleName on peopleName.[Id] = people.[Id]
WHERE EXISTS (SELECT 1 
FROM [dbo].[people_NAME] peopleName2 
WHERE peopleName2.[Id] != people.[id] 
AND peopleName2.[FirstName] LIKE '%' + peopleName.[FirstName] + '%')
order by peopleName.[Id]                    
)

如果需要的话，在这里将 CTE 与people表连接

select * from CTE

如果不需要与people连接，则无需CTE。

Answer 4

您是否尝试过 JOIN 而不是相关查询？

由于无法使用索引，它不会具有最佳性能，但它应该比相关子查询好一点。

SELECT DISTINCT top 10 people.[Id], peopleName.[LastName], peopleName.[FirstName]       
FROM [dbo].[people] people
INNER JOIN [dbo].[people_NAME] peopleName on peopleName.[Id] = people.[Id]
INNER JOIN [dbo].[people_NAME] peopleName2 on peopleName2.[Id] <> people.[id] AND
peopleName2.[FirstName] LIKE '%' + peopleName.[FirstName] + '%'