sql - 从SQL Server全文索引中获取前n个最新条目

Question

我在SQL Server 2008 R2数据库中有一个表

Article (Id, art_text)

ID是主键。
art_text具有全文索引。

我搜索包含这样的单词“ house”的最新文章：

SELECT TOP 100 Id, art_text 
FROM Article
WHERE CONTAINS(art_text, 'house')
ORDER BY Id DESC

这将返回正确的结果，但速度较慢（〜5秒）。该表有2000万行
其中350,000个包含“房子”一词。我可以在查询计划中看到，对于全文索引返回的350,000个ID，在聚集索引中执行了索引扫描。

如果有一种方法可以使全文索引中仅包含词“ house”的最新100条条目获得查询，则查询速度可能会更快。有什么办法可以使查询更快吗？

Answer 1

简短的答案是肯定的，有多种方法可以使此特定查询变得更快，但对于2000万行的语料库，5秒并不算太坏。您需要认真考虑以下建议是否最适合您的FT搜索工作量，并权衡成本与收益。如果您盲目地实现这些功能，那将是一段糟糕的时光。



有关提高Sql Server全文搜索性能的一般建议

减小要搜索的全文索引的大小FT索引越小，查询速度越快。有两种减小FT索引大小的方法。前两个可能适用或可能不适用，而第三个则需要大量工作才能完成。


添加特定于领域的干扰词干扰词是不会为全文搜索查询增加价值的词，例如“ the”，“ and”，“ in”等。如果存在与业务相关的词没有增值被索引，您可能会受益于将它们从FT索引中排除。考虑MSDN库上的假设全文索引。诸如“ Microsoft”，“库”，“ include”，“ dll”和“引用”之类的术语可能不会为搜索结果增加价值。 （进入http://msdn.microsoft.com并搜索“ microsoft”是否有任何实际价值？）FT法律意见索引可能会排除诸如“被告”，“起诉”和“法律”等词。
使用iFilter去除多余的数据使用Windows iFilter的全文本搜索可从二进制文档中提取文本。这与窗口搜索功能用于搜索pdf和powerpoint文档的技术相同。这种情况特别有用的一种情况是，您具有一个可以包含HTML标记的描述列。默认情况下，SQL Server全文搜索将为所有内容建立索引，因此您将诸如“ font-family”，“ Arial”和“ href”之类的词作为可搜索词。使用HTML iFilter可以删除标记。

在FT索引中使用iFilter的两个要求是，索引列是VARBINARY，并且有一个“ type”列包含文件扩展名。这些都可以通过计算列来完成。

CREATE TABLE t (
....
description varbinary(max),
FTS_description as (CAST(description as VARBINARY(MAX)),
FTS_filetype as ( N'.html' )
)
-- Then create the fulltext index on FTS_description specifying the filetype.

表的索引部分并将结果缝合在一起有几种方法可以完成此操作，但是总体思路是将表拆分为较小的块，分别查询这些块并将结果组合在一起。例如，您可以创建两个索引视图，一个用于当前年份，一个用于历史年份，并带有全文索引。您要返回100行的查询更改如下：

DECLARE @rows int
DECLARE @ids table (id int not null primary key)

INSERT INTO @ids (id)   
    SELECT TOP (100) id 
    FROM vw_2013_FTDocuments WHERE CONTAINS (....) 
    ORDER BY Id DESC 
SET @rows = @@rowcount
IF @rows < 100
BEGIN
  DECLARE @rowsLeft int
  SET @rowsLeft = 100 - @rows
  INSERT INTO @ids (id) SELECT TOP (@rowsLeft) ......
  --Logic to incorporate the historic data
END
SELECT ... FROM t INNER JOIN @ids .....

这可以导致查询时间的大量减少，但以增加搜索逻辑的复杂性为代价。当搜索通常限于数据的子集时，此方法也适用。例如，craigslist可能有房屋的FT指数，“待售”的FT指数和“就业”的FT指数。从首页进行的所有搜索都将根据各个索引进行组合，而在类别内进行搜索的常见情况更为有效。


不受支持的技术可能会在将来的Sql Server版本中中断。

您将需要使用与生产数量和质量相同的数据进行广泛的测试。如果该行为在将来版本的Sql Server中发生更改，则您无权投诉。这是基于观察而非证明。使用风险自负！！

一点全文历史记录在Sql Server 2005中，全文搜索功能位于sqlservr.exe的外部进程中。将FTS纳入查询计划的方式是一个黑匣子。 Sql服务器将通过FTS查询，FTS将返回ID的流。这将计划限制为Sql Server可用，而这些计划基本上可以将FTS运算符视为表扫描。

在Sql Server 2008中，FTS已集成到引擎中，从而提高了性能。它还为优化器提供了FTS查询计划的新选项。具体来说，它现在可以选择在LOOP JOIN运算符中探查FTS索引，以检查单个行是否与FTS谓词相匹配（请参阅 http://sqlblog.com/blogs/joe_chang/archive/2012/02/19/query-optimizer-gone-wild-full-text.aspx对此问题的详细讨论以及出错的方式。）

我们的最佳FTS查询计划的要求要获得最佳查询计划，需要努力争取两个特征。


没有排序操作。排序速度很慢，我们不想对2000万行或35万行进行排序。
不要返回与FTS谓词匹配的所有35万行。我们需要尽可能避免这种情况。


这两个条件消除了任何使用哈希联接的计划，因为哈希联接需要消耗所有输入来构建哈希表。

对于具有循环联接的计划，有两种选择。向后扫描聚簇索引，并针对全文搜索引擎中的每一行探查该特定行是否匹配。从理论上讲，这似乎是一个不错的解决方案，因为一旦我们匹配100行，就完成了。我们可能必须尝试10,000个ID才能找到匹配的100个ID，但这可能比读取全部350k更好。如果每个探针都比较昂贵，那可能还会更糟（请参阅上面的链接至Joe Chang的博客），那么我们的10k探针可能比读取全部35万行花费的时间更长。

另一个循环连接选项是将FTS部分放在循环的外侧，并搜索聚簇索引。不幸的是，FTS引擎不喜欢以相反的顺序返回结果，因此我们必须读取所有350k，然后对它们进行排序以返回前100名。

障碍是让FTS引擎以相反的顺序返回行。如果我们能克服这个问题，那么我们可以将IO减少为仅读取匹配的最后100行。幸运的是，FTS引擎倾向于按创建索引时指定的唯一索引的键顺序返回行。 （这是FTS引擎使用的内部存储的自然副作用）

通过添加一个ID负数的计算列，并在创建FT索引时在该列上指定一个唯一索引，我们真的可以结束了。

CREATE TABLE t (id int not null primary key, txt varchar(max), neg_id as (-id) persisted )
CREATE UNIQUE INDEX IX_t_neg_id on t (neg_id)
CREATE FULLTEXT INDEX on t ( txt ) KEY INDEX IX_t_neg_id

现在，对于查询，我们将使用CONTAINSTABLE和一些LEFT-join技巧，以确保FTS谓词不会出现在LOOP JOIN内部。

SELECT TOP (100) t.id, t.txt 
FROM CONTAINSTABLE(t, txt, 'house') ft 
LEFT JOIN t on tf.[Key] = t.neg_id ORDER BY tf.[key]

生成的计划应该是一个循环连接，该连接仅读取FT索引的最后100行。

一阵阵小风会把这栋纸牌屋炸掉：


复杂的FTS查询（如使用多个术语或使用NOT或OR运算符，可能会导致Sql 2008+获得“智能”功能，并将逻辑转换为在查询计划中加入的多个FTS查询。
任何累积更新，Service Pack或主要版本升级都可能使此方法无效。
它可以在95％的情况下工作，而在其余5％的情况下可以超时。
它可能根本不适合您。


祝好运！