sql-server - 在 JOIN 中使用时，带有 Containstable 的 Sql 服务器全文搜索非常慢!

Question

我正在使用 sql 2008 全文搜索，并且根据我使用 Contains 或 ContainsTable 的方式，我遇到了严重的性能问题。

以下是示例:(表一大约有 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)

结果:1​​0秒(非常慢)

场景 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'

结果:1​​0秒(非常慢)

场景 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)

结果:不到一秒(超快)

底线，似乎如果我在任何类型的连接或也有其他条件的 select 语句的 where 子句条件中使用 Containstable，性能真的很糟糕。此外，如果您查看分析器，您会发现从数据库读取的数量达到了顶峰。但是，如果我首先进行全文搜索并将结果放入表变量中并使用该变量，一切都会变得非常快。阅读次数也少得多。似乎在“糟糕”的情况下，它不知何故陷入了循环，导致它从数据库读取多次，但我当然不明白为什么。

现在的问题首先是为什么会发生这种情况？问题二是表变量的可扩展性如何？如果结果是数十万条记录怎么办？还会很快吗？

有什么想法吗？谢谢

Answer 1

我在这个问题上花了相当长的时间，并且基于运行了许多场景，这就是我的结论:

如果查询中的任何位置都有 Contains 或 ContainsTable，那么这部分将首先执行并且相当独立。这意味着即使其余条件将您的搜索限制为仅一条记录，Contains 或 containstable 都不会关心该记录。所以这就像并行执行。

现在，由于全文搜索仅返回一个 Key 字段，因此它会立即查找该 Key 作为为查询选择的其他索引的第一个字段。因此，对于上面的示例，它会查找包含 [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，一切都会非常快。

我的期望是 FTS 返回一堆 [key]，查询的其余部分返回一堆 [Id]，然后 Id 应该与 [key] 匹配。

当然，我试图在这里简化我的查询，但实际查询要复杂得多，我不能只更改索引。我也确实遇到过以全文传递的文本为空白的情况，在这些情况下我什至不想加入 containstable。在这些情况下，更改我的覆盖索引以将 id 字段作为第一个字段，将会产生灾难。

无论如何，现在我选择了临时表解决方案，因为它对我有用。我还将结果限制为几千，这有助于解决当记录数量过高时表变量的潜在性能问题。

谢谢