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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文:SQL Server 使用全文索引进行页面搜索标签:SQL SERVER/MSSQL SERVER/数据库/DBA/全文索引 概述   全文引擎使用全文索引中的信息来编译可快速搜索表中的特定词或词组的全文查询。
原文: SQL Server 使用全文索引进行页面搜索

标签:SQL SERVER/MSSQL SERVER/数据库/DBA/全文索引

概述  

全文引擎使用全文索引中的信息来编译可快速搜索表中的特定词或词组的全文查询。全文索引将有关重要的词及其位置的信息存储在数据库表的一列或多列中。全文索引是一种特殊类型的基于标记的功能性索引,它是由 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 = ON
AS DEFAULT
AUTHORIZATION [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_Housetest ON FD_HouseSearch

 

全文谓词

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

CONTAINS 谓词可以搜索:

    • 词或短语。
    • 词或短语的前缀。
    • 与另一个词相邻的词。
    • 由另一个词的词形变化而生成的词(例如,drive 一词是 drives、drove、driving 和 driven 词形变化的词干)。
    • 使用同义词库确定的另一个词的同义词(例如,metal 一词可能有 aluminum 和 steel 等同义词)。
---下面的示例将查找包含 "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
 
   

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,需要添加文件流,在服务中启用该功能可以在字段中将文档以二进制的形式保存在字段当中,这样大型文档也可以随数据库一起备份,很多网站存储图片都是存储图片的路径,这样备份数据库的时候图片不会一起备份。

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

 

 如果文章对大家有帮助,帮忙点推荐,谢谢!!!

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

《欢迎交流讨论》

 

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
15天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
2月前
|
SQL 数据处理 数据库
SQL正则表达式应用:文本数据处理的强大工具——深入探讨数据验证、模式搜索、字符替换等核心功能及性能优化和兼容性问题
【8月更文挑战第31天】SQL正则表达式是数据库管理和应用开发中处理文本数据的强大工具,支持数据验证、模式搜索和字符替换等功能。本文通过问答形式介绍了其基本概念、使用方法及注意事项,帮助读者掌握这一重要技能,提升文本数据处理效率。尽管功能强大,但在不同数据库系统中可能存在兼容性问题,需谨慎使用以优化性能。
38 0
|
3月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
71 13
|
3月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
51 6
|
2月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
254 0
|
3月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
181 1
|
3月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
190 3
|
3月前
|
SQL 监控 数据库
SQL Server 查询超时问题排查
【7月更文挑战第8天】排查 SQL Server 查询超时涉及五个主要方面:检查复杂查询、评估服务器性能、审视配置参数、更新统计信息和分析执行计划。关注点包括查询的结构(如连接、子查询和索引),服务器资源(CPU、内存、网络延迟),连接和内存设置,以及统计信息的时效性。通过这些步骤可定位并解决性能瓶颈。
|
3月前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
101 0