当在JOIN中使用Containstable的Sql Server全文搜索速度非常慢！

Question

我正在使用SQL 2008全文搜索功能，根据我使用Contains或ContainsTable的方式，性能存在严重问题。

这里是一个示例：（表1约有5000条记录，并且在表1上有一个覆盖索引，该索引包含所有出现在where子句中的字段。我试图简化语句，如果有语法问题，请原谅。）

情景1：

select * from table1 as t1
where t1.field1=90
and   t1.field2='something'
and   Exists(select top 1 * from containstable(table1,*, 'something') as t2 
where t2.[key]=t1.id)

结果：10 秒（非常慢）

场景 2：

select * from table1 as t1
join containstable(table1,*, 'something') as t2 on t2.[key] = t1.id
where t1.field1=90
and   t1.field2='something'

结果：10秒（非常慢）

场景3：

Declare @tbl Table(id uniqueidentifier primary key)
insert into @tbl select {key] from containstable(table1,*, 'something')

select * from table1 as t1
where t1.field1=90
and   t1.field2='something'
and  Exists(select id from @tbl as tbl where id=req1.id)

结果：秒级（超快）

总之，如果我在任何类型的连接或where子句条件中使用Containstable，并且该select语句还有其他条件，则性能非常差。此外，如果查看分析器，从数据库读取的数量会飙升。但是，如果我先执行全文搜索并将结果放入表变量中，然后使用该变量，一切都会变得非常快。读取的数量也要少得多。在“糟糕”的情况下，它似乎陷入了一个循环中，导致它多次从数据库中读取，但我当然不明白为什么。

现在的问题首先是为什么会发生这种情况？第二个问题是表变量的可扩展性如何？如果结果有成千上万条记录，它还会保持快速吗？

有任何想法吗？ 谢谢

- Bob

---

看起来和这个帖子中的问题很相似：https://dev59.com/HE3Sa4cB1Zd3GeqPv4U3#2749322。当您查看执行计划时，自由文本查询的预估行数和实际行数是多少？ - Martin Smith

哦，关于表变量的问题，如果你要在它们上面连接其他表，它们显然是不可扩展的。查询优化器总是假定它们只会返回1行，这可能会导致一些非常次优的计划，即使它们有成千上万条记录。临时表确实会为它们创建统计信息。 - Martin Smith

Answer 1

我花了很长时间研究这个问题，并根据运行多种情况，找出了以下结论：

如果您的查询中有Contains或ContainsTable，则该部分将首先独立执行。这意味着即使其余条件将搜索限制为仅一个记录，Contains和Containstable也不关心它们。因此，这就像并行执行一样。

由于全文搜索仅返回键字段，因此它会立即查找用于查询的其他索引的第一个字段作为键。所以对于上面的例子，它会查找具有[key]，field1，field2的索引。问题在于它根据where子句中的字段选择了用于查询的索引。因此，对于上面的例子，它选择了我拥有的覆盖索引，例如field1、field2、Id。（表的Id与从全文搜索返回的[Key]相同）。所以总结如下：

1. 执行containstable
2. 执行查询的其余部分并根据查询的where子句选择一个索引
3. 它尝试合并这两个结果。因此，如果它为其余查询选择的索引以[key]字段开头，那就没问题了。但是，如果索引没有[key]字段作为第一个键，则它开始循环。它甚至不会执行表扫描，否则遍历5000条记录不会那么慢。它执行循环的方式是运行FTS结果总数乘以其余查询结果的总数。因此，如果FTS返回2000条记录，而其余查询返回3000条记录，则它将执行2000 * 3000 = 6,000,000次循环。我不明白为什么要这样做。

因此，在我的情况下，它会执行全文搜索，然后执行其余部分的查询，但选择了基于field1、field2、id的覆盖索引（这是错误的），结果它混淆了。如果我将覆盖索引更改为Id、field1、field2，则所有内容都会非常快速。

我的期望是全文搜索返回一堆[key]，查询的其余部分返回一堆[Id]，然后应该将这些[ID]与[key]相匹配。

当然，我试图简化我的查询，但实际查询要复杂得多，而且我不能改变索引。 我还有一些情况，在这些情况下传递的文本为空，我甚至不想与containstable连接。 在这种情况下，更改我的覆盖索引以使ID字段成为第一个字段将导致灾难。

无论如何，目前我选择了临时表的解决方案，因为它对我有用。 当记录数量过高时，我也将结果限制在几千个以帮助处理潜在的性能问题。

谢谢

- Bob

Answer 2

通常情况下，它的运行速度非常快：

select t1.*, t2.Rank
    from containstable(table1, field2, 'something') as t2
        join table1 as t1 ON t1.id = t2.Key AND t1.field1=90
    order by t2.Rank desc

在JOIN和WHERE中放置搜索条件有很大的区别。

- Eugene Y.

Answer 3

我猜测你的问题与我之前链接的另一个帖子中的问题相同。你是否发现多个单词搜索术语会引起问题？如果是这样，那么我在那个帖子中的答案也适用。来自http://technet.microsoft.com/en-us/library/cc721269.aspx#_Toc202506240。

最重要的是选择正确的连接类型进行全文查询。在FulltextMatch STVF上进行基数估计非常重要，以得到正确的计划。因此，首先要检查FulltextMatch的基数估计。这是全文搜索字符串在索引中命中次数的估计值。例如，在图3中的查询中，它应该接近包含术语“word”的文档数量。在大多数情况下，它应该非常准确，但如果估计偏差很大，可能会生成错误的计划。单个术语的估计通常非常好，但估算短语或AND查询等多个术语更为复杂，因为无法根据索引中术语的频率知道交集将是什么。如果基数估计良好，则坏计划可能是由于查询优化器成本模型引起的。修复计划问题的唯一方法是使用查询提示来强制执行某种连接或OPTIMIZE FOR。

因此，根据它存储的信息，它根本不可能知道两个搜索术语是否很独立或通常一起出现。也许您应该有两个单独的过程，一个用于单词查询，让优化器完成其工作，另一个用于多词搜索术语，您可以强制使用“足够好”的计划（如果您想自己进行基数的粗略估计，则sys.dm_fts_index_keywords可能会有所帮助）。

如果您在单词查询中遇到问题，则此链接文章中的此段可能适用。

在SQL Server 2008全文搜索中，我们有能力根据使用的搜索项的基数估计来更改生成的计划。如果查询计划固定（如存储过程内的参数化查询），则不会发生此步骤。因此，即使对于给定的搜索项，编译的计划不理想，编译的计划仍始终为此查询提供服务。

因此，您可能需要使用RECOMPILE选项。

- Martin Smith

---

感谢Marting详细的回答。然而，我的问题不在于多个单词。事实上，对于全文搜索，无论是一个单词还是多个单词，搜索速度始终非常快。对我来说，问题在于当全文搜索与where子句中的其他条件相结合时，性能会急剧下降。由于我即将超出此评论大小的允许限制，请参见下一条评论以获取我的其余答案... - Bob

@Bob 仍然很奇怪，当你从表变量中加入并且它本质上必须执行与连接ID到键相同的操作时，它能正常工作。它是如何执行这个连接的 - 它选择了不同的索引还是不同的连接策略？此外，当您查看执行计划时，所有部分的预估和实际行数是否合理正确？ - Martin Smith