sql-server - CONTAINSTABLE - FULL TEXT INDEX 中是否有等同于 OR 的子句

Question

我正在努力寻找解决方案以改进字符串搜索过程，我选择了FULL-TEXT INDEX 策略。

但是，在实现它之后，我仍然可以看到在使用多个带有 OR 子句的全文索引表使用多个字符串进行搜索时，性能会受到影响。

(例如 WHERE CONTAINS(F.*,'%Gayan%') OR CONTAINS(P.FirstName,'%John%'))

作为一种解决方案，我正在尝试使用 CONTAINSTABLE 以期提高性能。

现在，在使用 LEFT JOIN 连接表时，我遇到了 CONTAINSTABLE 问题

请看下面的例子。

查询 1

SELECT F.Name,p.*
        FROM P.Role PR
INNER JOIN P.Building F ON PR.PID = F.PID
LEFT JOIN CONTAINSTABLE(P.Building,*,'%John%') AS FFTIndex ON F.ID = FFTIndex.[Key]
LEFT JOIN P.Relationship PRSHIP ON PR.id = prship.ToRoleID
LEFT JOIN P.Role PR2 ON PRSHIP.ToRoleID = PR2.ID
LEFT JOIN P.Person p ON pr2.ID = p.PID
LEFT JOIN CONTAINSTABLE(P.Person,FirstName,'%John%') AS PFTIndex ON P.ID = PFTIndex.[Key]
WHERE F.Name IS NOT NULL

这会产生以下结果。

查询 2

SELECT F.Name,p.*
FROM P.Role PR
INNER JOIN P.Building F ON PR.PID = F.PID
INNER JOIN P.Relationship PRSHIP ON PR.id = prship.ToRoleID
INNER JOIN P.Role PR2 ON PRSHIP.ToRoleID = PR2.ID
INNER JOIN P.Person p ON pr2.ID = p.PID
WHERE CONTAINS(F.*,'%Gayan%') OR CONTAINS(P.FirstName,'%John%') 
AND F.Name IS NOT NULL

结果

期待以 SQL SERVER OR 子句的行为方式使用查询 1。据我所知，查询 1 的 CONTAINSTABLE 将数据与 building 表连接起来，其余结果将被忽略，因此 CONTAINSTABLE Person 表的 > 获取已包含从 building 表过滤的关键字的数据。

如果 keyword = Building，我想匹配两个表中的关键字，而不考虑在两个表中搜索已保存的记录。在每个表中有一条记录就足够了。

总结

查询 2 执行良好，但当索引中的单词增长时会导致速度变慢。查询 1 似乎已优化(涉及多个在线资源和 MS 文档时)，但是，它没有给我预期的输出。

有什么办法可以解决这个问题吗？

我并不严格依附于 CONTAINSTABLE。提出另一种优化方法也很重要。谢谢。

Answer 1

如果没有完整的数据集，很难下定论，但有几个选项可供探索

删除无效的 % 通配符

为什么要使用 '%SearchTerm%'？如果使用不带通配符 (%) 的搜索词，性能会提高吗？如果您想要一个与前缀匹配的单词，请尝试类似WHERE CONTAINS (String,'"SearchTerm*"')

试试临时表

我的猜测是 CONTAINS 比 CONTAINSTABLE 稍微快一些，因为它不计算排名，但我不知道是否有人尝试过对它进行基准测试。无论哪种方式，在加入其余表之前，我都会尝试将匹配项保存到临时表中。这将允许优化器创建更好的执行计划

SELECT ID INTO #Temp 
FROM YourTable 
WHERE CONTAINS (String,'"SearchTerm"')

SELECT *
FROM #Temp
INNER JOIN...

通过去除噪声词优化全文索引

您可能会发现您有一些嘈杂的词，也就是在您的数据中多次重复出现的词，这些词没有意义，例如“the”或一些商业术语。将这些添加到您的停止列表将意味着您的全文索引将忽略它们，从而使您的索引更小从而更快

下面的查询将列出最常用的索引词

Select *
From sys.dm_fts_index_keywords(Db_Id(),Object_Id('dbo.YourTable') /*Replace with your table name*/)
Order By document_count Desc

这个或那个标准

对于您的 WHERE CONTAINS(F.*,'%Gayan%') OR CONTAINS(P.FirstName,'%John%') 条件，您需要这个或那个，这很棘手。即使使用简单的相等运算符，OR 子句通常也能执行。我会尝试做两个查询并合并结果，例如:

SELECT * FROM Table1 F
/*Other joins and stuff*/
WHERE CONTAINS(F.*,'%Gayan%')
UNION
SELECT * FROM Table2 P 
/*Other joins and stuff*/
WHERE CONTAINS(P.FirstName,'%John%') 

或者这需要更多工作，但您可以将所有数据加载到包含所有列的巨型非规范化表中。然后对该表应用全文索引并以这种方式调整您的搜索条件。这可能是最快的搜索方法，但是您必须确保非规范化表和基础规范化表之间的数据同步

SELECT B.*,P.* INTO DenormalizedTable
FROM Building AS B
INNER JOIN People AS P

CREATE FULL TEXT INDEX ft ON DenormalizedTable
etc...