sql-server - 如何优化 SQL Server 全文搜索

Question

我想将全文搜索用于自动完成服务，这意味着我需要它快速运行!最多两秒。

搜索结果来自不同的表，因此我创建了一个将它们连接在一起的 View 。我使用的 SQL 函数是 FREETEXTTABLE()。

查询运行非常缓慢，有时长达 40 秒。

为了优化查询执行时间，我确保基表有一个聚集索引列，该列是整数数据类型(而不是 GUID)

我有两个问题:首先，关于如何使全文搜索更快的任何其他想法？ (不包括升级硬件...)第二，为什么每次重建全文目录后，搜索查询都非常快(不到一秒)，但仅限于第一次运行。我第二次运行查询时，它又花了几秒钟，从那里开始一切都在下山……知道为什么会这样吗？

Answer 1

你重建目录后第一次查询很快的原因可能很简单:

当您删除目录并重建它时，必须重建索引，这需要一些时间。如果您在重建完成之前进行查询，他们的查询速度会更快，这仅仅是因为数据较少。您还应该注意到，您的查询结果包含较少的行。

因此，只有索引重建完成后，测试查询速度才有意义。

以下选择可能会方便地检查索引的大小(以及碎片)。当大小停止增长时，索引的重建就完成了；)

-- Compute fragmentation information for all full-text indexes on the database
SELECT c.fulltext_catalog_id, c.name AS fulltext_catalog_name, i.change_tracking_state,
    i.object_id, OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS object_name,
    f.num_fragments, f.fulltext_mb, f.largest_fragment_mb,
    100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0)  AS fulltext_fragmentation_in_percent
FROM sys.fulltext_catalogs c
JOIN sys.fulltext_indexes i
    ON i.fulltext_catalog_id = c.fulltext_catalog_id
JOIN (
    -- Compute fragment data for each table with a full-text index
    SELECT table_id,
        COUNT(*) AS num_fragments,
        CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_mb,
        CONVERT(DECIMAL(9,2), MAX(data_size/(1024.*1024.))) AS largest_fragment_mb
    FROM sys.fulltext_index_fragments
    GROUP BY table_id
) f
    ON f.table_id = i.object_id