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

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

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

测试环境:SQL Server 2008 r2

目录

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

全文索引概念

  • 全文索引是针对数据表,只能对表创建全文索引,不能对数据库创建全文索引。
  • 每个数据库可以不包含全文目录或包含多个全文目录,一个全文目录可以包含多个全文索引,但一个全文索引只能用于构成一个全文目录。
  • 一个数据表只能创建一个全文索引,一个全文索引可以包含多个字段。
  • 创建全文索引的表必须要有一个唯一的非空索引,并且这个唯一的非空的索引只能是一个字段,不能是组合字段。
  • 每个表只允许有一个全文索引。若要对某个表创建全文索引,该表必须具有一个唯一且非 Null 的列。您可以对以下类型的列创建全文索引:charvarcharncharnvarchartextntextimagexmlvarbinaryvarbinary(max),从而可对这些列进行全文搜索。对数据类型为 varbinaryvarbinary(max)imagexml 的列创建全文索引需要您指定类型列。类型列是用来存储每行中文档的文件扩展名(.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 查询处理器内部运行。
  • 索引编写器(索引器) 索引编写器生成用于存储索引标记的结构。
  • 筛选器后台程序管理器 筛选器后台程序管理器负责监视全文引擎筛选器后台程序宿主的状态。

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

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

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

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

创建全文索引

启动服务

在SQL Server配置管理工具中,找到'SQL Full-text Filter Daemon Launcher'服务用本地用户启动。

创建全文目录

打开需要创建全文目录的数据库-存储-全文目录-右键新建全文目录

用语句创建全文目录

CREATE FULLTEXT CATALOG [FD_HouseSearch]WITH ACCENT_SENSITIVITY = ONAS DEFAULTAUTHORIZATION [dbo]

创建全文索引

右键需要创建全文索引的表-全文索引-定义全文索引

1.全文索引必须要有一个唯一非空索引,这里选择主键。

2.选择需要全文搜索的列,并且选择断字符语言,因为该字段主要用来存储中文,所以这里也选择了简体中文。

断字符:断字符用来对全文搜索数据进行语言分析,查找单词的边界,也就是怎样将一段很长的内容拆分成日常的词语或字。例如“全文搜索”,可能会断字成“全文”、‘搜索’、‘全’、‘文’、‘搜’、‘索’等符合中国人正常的习惯的词或字。

3.选择跟踪方式,这里选择自动跟踪,就是表发生更改时自动填充索引。

4.选择全文目录、索引文件、非索引字表

非索引字表:在刚才的断字中讲了怎样断字,这里就是将断的字保存在一张表中,该处选择系统默认的非索引字表.

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

5.填充计划

可以新建填充计划来填充全文索引,填充计划可以是完全填充、增量填充、更新填充。

用语句创建全文索引

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

全文谓词

全文查询使用全文谓词(CONTAINS 和 FREETEXT)以及全文函数(CONTAINSTABLE 和 FREETEXTTABLE)。它们支持复杂的 Transact-SQL 语法,这种语法支持各种形式的查询词。若要编写全文查询,必须了解何时以及如何使用这些谓词和函数。

CONTAINS 谓词可以搜索:

  • 词或短语。
  • 词或短语的前缀。
  • 与另一个词相邻的词。
  • 由另一个词的词形变化而生成的词(例如,drive 一词是 drives、drove、driving 和 driven 词形变化的词干)。
  • 使用同义词库确定的另一个词的同义词(例如,metal 一词可能有 aluminum 和 steel 等同义词)。

---下面的示例将查找包含 "Mountain"
USE AdventureWorks2008R2;GOSELECT Name, ListPriceFROM Production.ProductWHERE 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

FREETEXT谓词的用法这里就不做解释了!

需求

现在来说一下我最近的需求,表数据100万条,数据这里就不弄出来了,只把方案说一下,title类似于文章的标题,Description是内容也是全文索引字段

方案1:like,测试后果断排除

方案2:直接使用全文搜索进行,排序消耗大。

方案3:由于查询需要对Title进行排序,建Title字段的倒序索引包含其它字段,最后选择该方案(创建Title字段的倒序索引很重要)。

--给出部分字段

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

)

查询Description包含“美园”,并且如果Title是“美园”则排在第一位,并且以分页的形式显示,每页20条记录。

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

总结

大家不要拿这个与搜索引擎做对比,肯定是没法比的,因为我这里只需要解决需求就好,所以方案适合我目前的需求。

全文索引功能类似于百度的搜索引擎,但是百度这类搜索引擎有自己的数据字典,在关键字表中对关键字进行排序,保存关键字对应的 文档id,一个文档只会保留很少的关键字,就跟平时写文章要添加标签一样,一般一篇文章就几个标签,当搜索的时候匹配的速度就会非常快,这就需要一个很完善的数据字典表。

全文搜索还有另外的一个功能就是FileStream,需要添加文件流,在服务中启用该功能可以在字段中将文档以二进制的形式保存在字段当中,这样大型文档也可以随数据库一起备份,很多网站存储图片都是存储图片的路径,这样备份数据库的时候图片不会一起备份。

全文索引带来好处的同时也会对性能有一定的影响,特别是在进行筛选操作的时候对服务器性能会带来影响,所以选择一个功能的同时需要考虑对性能带来的影响。

原文发布于微信公众号 - 我为Net狂(dotNetCrazy)

原文发表时间:2015-10-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

共享池的调整与优化(Shared pool Tuning)

--=======================================

11920
来自专栏微信公众号:Java团长

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

12530
来自专栏Linyb极客之路

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

12930
来自专栏逸鹏说道

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

概述 全文引擎使用全文索引中的信息来编译可快速搜索表中的特定词或词组的全文查询。全文索引将有关重要的词及其位置的信息存储在数据库表的一列或多列中。全文索引是一...

38570
来自专栏*坤的Blog

注入学习1:SQL注入语句大全

之前做了xss预防,以及些许的注入预防了,但是不够全面,如果还是搜集了下一些常用的注入手段,以此用来进行更好的预防.

53420
来自专栏肖蕾的博客

安卓端强大数据库ORM框架(XAndrDB)的使用及介绍XAndrDB一、新建数据库二、或者你这样新建数据库三、查询操作四、新增操作五、删除操作六、更新数据操作

14740
来自专栏乐沙弥的世界

RMAN 提示符下执行SQL语句

       在手动恢复数据库时,有时候需要在SQL*Plus提示符以及操作系统提示符,RMAN提示符下来回切换显得有些繁琐。实际上RMAN为我们提供了命令行下...

12530
来自专栏林欣哲

MySQL数据库开发规范知识点速查

数据库设计规范 命名规范 基本设计规范 索引设计规范 字段设计规范 SQL开发规范 操作行为规范 命名规范 对象名称使用小写字母并用下划线分割 禁止使用MySQ...

427110
来自专栏数据库新发现

关于shared pool的深入探讨(五)

http://www.eygle.com/internal/shared_pool-5.htm

12520
来自专栏三流程序员的挣扎

Android 优化——存储优化

Google 推出的 Protocal Buffers 是一种更轻便高效的存储结构,但消耗内存较大。

12620

扫码关注云+社区

领取腾讯云代金券