如何优化含varbinary(MAX)列的SQL Server全文检索性能？

兄弟，800多条数据查30秒这绝对不正常，大概率是全文搜索的配置或者执行逻辑出了问题，我给你梳理几个实战中有用的排查和优化方向：

• 确认全文索引的填充状态：首先得确保你的全文索引是完全填充完成的，要是还在增量填充或者没填充完，查询时SQL会临时去解析二进制文件，速度能慢到离谱。你可以跑这个命令检查：

  SELECT FULLTEXTCATALOGPROPERTY('你的全文目录名', 'PopulateStatus') AS PopulateStatus
  

  返回0代表填充完成，1是正在进行中，要是没完成，先等它跑完再测试。
• 检查文件类型筛选器的配置：因为你存在varbinary(MAX)里的是文件，SQL Server需要知道文件类型（比如.docx、.pdf）才能正确解析内容。创建全文索引的时候有没有指定TYPE COLUMN？比如你的表如果有个FileType列存后缀名，建索引时要加上TYPE COLUMN FileType，不然SQL会把二进制当成乱码解析，不仅搜不准，还会因为无效解析拖慢速度。可以用这个命令查看当前配置：

  EXEC sp_help_fulltext_columns '你的表名'
  

• *别直接SELECT 读取大文件列：你的查询是不是写了SELECT *？如果是的话，赶紧改成只选需要的列！返回713条数据的话，把整个varbinary(MAX)的文件内容都读出来，会占用巨量的IO和内存，哪怕是缓存了也慢得要死。如果必须要文件内容，分两步查：先查符合条件的主键ID，再按需去读取对应的文件内容。
• 检查执行计划是否用到了全文索引：执行查询时看一下执行计划，是不是走了全文索引扫描，还是走了表扫描？如果是表扫描，说明全文索引根本没生效，可能是查询写法有问题，或者索引损坏。可以试试用FREETEXTTABLE代替直接的FREETEXT，这种表值函数的执行计划通常更稳定，比如：

  SELECT t.*
  FROM 你的表名 t
  JOIN FREETEXTTABLE(你的表名, 二进制列名, '搜索关键词') ft ON t.ID = ft.[KEY]
  

• 给全文搜索分配足够的内存：SQL Server默认给全文搜索的内存配额可能不够，尤其是一次性解析大量文件的时候。你可以调整这两个配置（根据服务器CPU核心数调整，比如4核设4）：

  sp_configure 'show advanced options', 1;
  RECONFIGURE;
  sp_configure 'max full-text crawl range', 4;
  RECONFIGURE;
  sp_configure 'full-text crawl bandwidth (max)', 100;
  RECONFIGURE;
  

  改完后可能需要重启SQL服务才能生效，这样能提升全文索引的爬取和查询效率。
• 确保全文索引数据被缓存：可以通过sys.dm_fts_memory_buffers视图查看全文索引的缓存使用情况，如果缓存命中率低，说明服务器内存不够，得给SQL Server分配更多内存，让索引数据留在内存里，减少磁盘IO。

把文件的元数据（比如文件名、类型、上传时间）和实际的二进制文件内容拆成两张表：

• 一张FileMetadata表，存主键ID、文件名、FileType、甚至可以提前把文件内容提取成nvarchar(MAX)的ExtractedText列；
• 另一张FileContent表，只存主键ID和varbinary(MAX)的文件内容。

然后给FileMetadata的ExtractedText列建全文索引，查询时先搜元数据表，再按需去内容表取文件。这样能极大减少查询时的IO开销，因为全文搜索只需要处理文本列，不用碰大二进制文件。

内容的提问来源于stack exchange，提问作者Teeko