SQL Server 使用全文索引进行页面搜索

概述  

全文引擎使用全文索引中的信息来编译可快速搜索表中的特定词或词组的全文查询。全文索引将有关重要的词及其位置的信息存储在数据库表的一列或多列中。全文索引是一种特殊类型的基于标记的功能性索引，它是由 SQL Server 全文引擎生成和维护的。生成全文索引的过程不同于生成其他类型的索引。全文引擎并非基于特定行中存储的值来构造 B 树结构，而是基于要编制索引的文本中的各个标记来生成倒排、堆积且压缩的索引结构。在 SQL Server 2008 中，全文索引大小仅受运行 SQL Server 实例的计算机的可用内存资源限制。

最近遇到一个需求，需要在一个100万的表中通过关键字对一个大型字符字段进行检索，类似于百度搜索引擎的搜索，查询出所有包含关键字的数据并进行分页处理，并且将匹配度最高的数据排在第一位，要求查询响应时间控制在1秒左右。

 测试环境：SQL Server 2008 r2

目录

•  概述
•  全文索引概念
•  创建全文索引
  •  启动服务
  •  创建全文目录
  •  创建全文索引
  •  全文谓词
•  需求
•  总结

全文索引概念

• 全文索引是针对数据表，只能对表创建全文索引，不能对数据库创建全文索引。
• 每个数据库可以不包含全文目录或包含多个全文目录，一个全文目录可以包含多个全文索引，但一个全文索引只能用于构成一个全文目录。
• 一个数据表只能创建一个全文索引，一个全文索引可以包含多个字段。
• 创建全文索引的表必须要有一个唯一的非空索引，并且这个唯一的非空的索引只能是一个字段，不能是组合字段。
• 每个表只允许有一个全文索引。若要对某个表创建全文索引，该表必须具有一个唯一且非 Null 的列。您可以对以下类型的列创建全文索引：char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 和 varbinary(max)，从而可对这些列进行全文搜索。对数据类型为 varbinary、varbinary(max)、image 或 xml 的列创建全文索引需要您指定类型列。类型列是用来存储每行中文档的文件扩展名（.doc、.pdf、xls 等）的表列。

全文搜索由全文引擎提供支持。全文引擎有两个角色：索引支持和查询支持。

全文搜索体系结构：

从 SQL Server 2008 开始，全文搜索体系结构包括以下进程：

• SQL Server 进程 (sqlservr.exe)
• 筛选器后台程序宿主进程 (fdhost.exe)。

SQL Server 进程组件：

• • 用户表
    这些表包含要进行全文索引的数据。
    
    
  • 全文收集器
    全文收集器使用全文爬网线程。它负责计划和驱动对全文索引的填充，并负责监视全文目录。
    
    
    
  • 同义词库文件
    这些文件包含搜索项的同义词。
    
    
  • 非索引字表对象
    非索引字表对象包含对搜索无用的常见词列表。
    
    
    
  • SQL Server 查询处理器
    查询处理器编译并执行 SQL 查询。如果 SQL 查询包含全文搜索查询，则在编译和执行期间该查询都会发送到全文引擎。查询结果将与全文索引相匹配。
    
    
  • 全文引擎
    SQL Server 中的全文引擎现已与查询处理器完全集成。全文引擎编译和执行全文查询。作为查询执行的一部分，全文引擎可能会接收来自同义词库和非索引字表的输入。在 SQL Server 2008 和更高版本中，SQL Server 的全文引擎在 SQL Server 查询处理器内部运行。
    
    
  • 索引编写器（索引器）
    索引编写器生成用于存储索引标记的结构。
    
    
  • 筛选器后台程序管理器
    筛选器后台程序管理器负责监视全文引擎筛选器后台程序宿主的状态。

筛选器后台程序宿主组件：

筛选器后台程序宿主是一个由全文引擎启动的进程。它运行下列全文搜索组件，这些组件负责对表中的数据进行访问、筛选和断字，同时还负责对查询输入进行断字和提取词干：

筛选器后台程序宿主的组件如下：

• • 协议处理程序
    此组件从内存中取出数据，以进行进一步的处理，并访问指定数据库的用户表中的数据。其职责之一是从全文索引列中收集数据，并将所收集的数据传递给筛选器后台程序宿主，从而由该宿主根据需要应用筛选和断字符。
    
    
  • 筛选器
    某些数据类型需要筛选，然后才能为文档中的数据（包括 varbinary、varbinary(max)、image 或 xml 列中的数据）创建全文索引。给定文档采用何种筛选器取决于文档类型。例如，Microsoft Word (.doc) 文档、Microsoft Excel (.xls) 文档和 XML (.xml) 文档分别使用不同的筛选器。然后，筛选器从文档中提取文本块区，删除嵌入的格式并保留文本，如有可能的话也会保留有关文本位置的信息。结果将以文本化信息流的形式出现。
    
    
  • 断字符和词干分析器
    断字符是特定于语言的组件，它根据给定语言的词汇规则查找词边界（“断字”）。每个断字符都与用于组合动词及执行变形扩展的特定于语言的词干分析器组件相关联。在创建索引时，筛选器后台程序宿主使用断字符和词干分析器来对给定表列中的文本数据执行语言分析。与全文索引中的表列相关的语言将决定为列创建索引时要使用的断字符和词干分析器。

CREATE FULLTEXT CATALOG [FD_HouseSearch]WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
AUTHORIZATION [dbo]

----查询断字表
SELECT TOP 1000 * FROM sys.dm_fts_index_keywords(db_id(''), object_id(''))

--语句少了很多默认参数，其它就按系统默认即可
CREATE FULLTEXT INDEX ON dbo.Housetest
(Description
)
KEY INDEX PK_Housetest
ON FD_HouseSearch

---下面的示例将查找包含 "Mountain"
USE AdventureWorks2008R2;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE CONTAINS(Name, 'Mountain');
GO

--下面的示例将查找包含 "Mountain"或 "Road"

USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Mountain" OR "Road" ')
GO

---下面的示例返回的所有产品名称中，其 Name 列中至少有一个词以前辍 chain 开头

USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
GO

--给出部分字段
CREATE TABLE [dbo].[Housetest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](200) NULL,
    [Description] [nvarchar](max) NOT NULL,
    [IsOnline] [tinyint] NOT NULL,
 CONSTRAINT [PK_Housetest] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE INDEX IX_Housetest ON Housetest
(Title DESC
)
INCLUDE
(ID,
Description,
IsOnline
)

select  * from (SELECT  ROW_NUMBER() OVER(Order by (case when Title='美园' Then 1 Else 0 End) desc ) as RowsNumber,ID,Title,Description
  From Housetest
  Where  contains(Description,'美园') and IsOnline=1) tab1 
 where RowsNumber between 1 and 20