• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

【SqlServer】管理全文索引(FULL TEXT INDEX)

开发技术 开发技术 2周前 (04-06) 8次浏览

Sql Server中的全文索引(下面统一使用FULLTEXT INDEX来表示全文索引),是一种特定语言搜索索引功能。它和LIKE的不一样,LIKE主要是根据搜索模板搜索数据,它的效率比FULLTEXT INDEX要低。在几百万的字符串中,LIKE需要花几分钟才能返回的结果,FULLTEXT INDEX可能只需要几秒钟。

FULLTEXT INDEX功能是Sql Server的可选项。你可以通过 SELECT FULLTEXTSERVICEPROPERTY(IsFullTextInstalled) 命令,来检查你是否安装了FULLTEXT INDEX。

 在开始介绍FULLTEXT INDEX之前,还需要介绍几个重要的相关概念。它们分别是catalog, fulltext index fragment,  

 

Catalog: 在创建FULLTEXT INDEX之前,都需要先创建一个CATALOG, CATALOG是一个虚拟容器对象,它就是用来存储FULLTEXT Index的,一个CATALOG可以关联多个FULLTEXT Index索引。CATALOG不属于任何文件组。

FULLTEXT Index Fragments:  通常一个FULLTEXT索引都是由多个内部表组成的,这些内部表就被称为Fragments。当用户更新表中的数据后,数据库会对改变的部分自动创建一个Fragment对象(前提是在这个表上建立了FULLTEXT INDEX,并且设置了自动跟踪改变)。

你可通过sys.fulltext_index_fragments表来查询所有fragments记录:

SELECT * FROM sys.fulltext_index_fragments;

输出:

【SqlServer】管理全文索引(FULL TEXT INDEX)

通过上面的输出结果,可以看出一个表有了两个fragment对象。如果像这样的fragment对象越来越多的话,是会影响FULTEXT INDEX的查询效率的。为了减少Fragment的数量,可以使用ALTER命令将所有的Fragment整合到一起。

ALTER FULLTEXT CATALOG [your-catalog-name] REORGANIZE;

【SqlServer】管理全文索引(FULL TEXT INDEX)

 

1. 创建和删除全文索引

创建测试表:

create table Text_Test_Table(
    id int not null IDENTITY(1,1) PRIMARY KEY,
    txt nvarchar(1000) null
);

创建CATALOG:

CREATE FULLTEXT CATALOG my_catalog;

创建全文索引

CREATE FULLTEXT INDEX ON Text_Test_Table
(  
    txt                         --Full-text index column name     
    Language 2057               --2057 is the LCID for British English  
)  
KEY INDEX PK__Text_Tes__3213E83F0F8DA0F2 ON my_catalog   --Unique index  
WITH CHANGE_TRACKING AUTO                                --Population type;  
GO  

上面的表中指定了全文索引的字段为txt,同时也指定了它的语言为英式英语。你也可以指定为其它语言,在SQL SERVER中用 select * from sys.fulltext_languages; 可以查询所有支持的语言编号。为字段指定正确的语言编号是必需的,因为不同的语言编号会使用不同的单词分割器,不同的单词分割器会分割出不同的关键词列表,当查询的时候就需要匹配关键词列表中的数据。

创建全文索引,需要当前表至少包含一个唯一索引(unique index)。上面案例中, PK__Text_Tes__3213E83F0F8DA0F2  是主键,满足唯一性的要求,因此可以用来创建全文索引。

CHANGE_TRACKING AUTO:是指当表中数据有变化时,会自动更新全文索引中的记录。(除了AUTO,还可以为MANUAL值,表示需要手动去更新全文索引的记录)。

 

在创建创建全文索引时,为什么需要指定一个 Unique 索引?

其实这个比较好理解,可以把全文索引理解为一张 关键词与数据表的关系对照表。只有数据表上有一个Unique索引时,全文索引才可以唯一地关联关键词与数据表之间的关系。

有点绕?直接上代码!

--插入测试数据
insert into Text_Test_Table values('She''s great fun, but she''s a few sandwiches short of a picnic.');
insert into Text_Test_Table values('She''s funny, but she only eat a few sandwiches.');

--查询数据表
select * from Text_Test_Table;

--查询全文索引关键词对照关系,
--数据库为Test,数据表为Text_Test_Table
SELECT * FROM sys.dm_fts_index_keywords_by_document(db_id('Test'), object_id('dbo.Text_Test_Table'))

效果对照图:

【SqlServer】管理全文索引(FULL TEXT INDEX)

索引表中的document_id对应的是Text_Test_Table表中的id字段。display_term是关键词。occurence_count是关键词的出现次数。document_id就是对应了数据表中唯一约束数据的编号,当唯一约束作用在数字字段上时,约束字段的值就会和document_id一样。如果唯一约束作用在字符字段上的话,那么document_id将会是对应数据的编号。 

2. 使用全文索引查询数据

 

2.1 匹配查询含有所有关键词的语句(必需包含所有关键词)

 如果要完全匹配查询的关键字,那么可以用contains函数。

语法:

CONTAINS(字段,关键词)

关键词需要用冒号””括起来,多个关键词用空格隔开。格式:”关键词1 关键词2 关键词3…”。contains中的关键词就相当于AND关键词,这些关键词并不需要连在一起,但是需要所有的关键词都存在。

案例:

-- 查询Text_Test_Table表中txt字段包含 a 和 few 和 sandwiches 的关键词的所有数据
declare @terms nvarchar(1000) = '"a few sandwiches"';
select * from Text_Test_Table where contains(txt, @terms);

-- 可以查询出:
-- she has a few sandwiches
-- a few lemon and sandwiches
-- a man and few person both have sandwiches

2.2 匹配查询含有关键词的语句(只要含有一个关键词就行了)

 如果要包含所有的关键词,那么应使用FREETEXT函数。

语法:

FREETEXT(字段, 关键词)

关键词需要用冒号””括起来,多个关键词用空格隔开。格式:”关键词1 关键词2 关键词3…”。freetext中的关键词就相当于OR关联词,这些关键词并不需要连在一起,只要有一个关键词存在数据就会被选取。

-- 查询Text_Test_Table表中txt字段包含 a 或 few 或 sandwiches 的关键词的所有数据
declare @terms nvarchar(1000) = '"a few sandwiches"';
select * from Text_Test_Table where freetext(txt, @terms);

-- 可以查询出:
-- she has a few sandwiches
-- sandwiches is good
-- There are few apples

 

注意:

Freetext函数还可以匹配关键词的变体,比如通过关键词catch匹配相应的变体caught, catching, 和 catches 等。但Contains函数想要查询变体,就必需使用FORMSOF语句。请移步FORMSOF Predicate获取更详细的信息。

 

2.3 案例:查询有不同匹配精确度的多个字段

有一张products表,其中有id,name和description字段, id是主键。name 和 description是nvarchar字段类型。现在要查询Products中的数据,name必须要包含所有的查询关键字,description至少需要匹配一个提供的关键词。

create table Products(
    id int not null IDENTITY(1,1) PRIMARY KEY,
    [name] nvarchar(1000) null,
    [description] nvarchar(1000) null
);

CREATE FULLTEXT INDEX ON Products
(  
    [name]  Language 2057,
    [description] Language 2057
)  
KEY INDEX PK__Products__3213E83F67020225 ON my_catalog   --Unique index  
WITH CHANGE_TRACKING AUTO                                --Population type;  
GO  

insert into Products values('Mouse Anti-Cattle FOXP3','This is Mouse Anti-Cattle FOXP3 introduction');
insert into Products values('Rabbit Anti-Mouse KRT13','This is Rabbit Anti-Mouse KRT13 introduction');
insert into Products values('Mouse Anti-C elegans FOXP3','This is Mouse Anti-C elegans FOXP3 introduction');

案例查询SQL语句:

declare @terms nvarchar(255) = 'Mouse Anti';
declare @termsQuote nvarchar(255) = '"'+@terms+'"';
select * from Products  p
JOIN (
SELECT tp.[id],
   (case 
   when name like @terms+'%' then 4
   when name like '%'+@terms then 3
   when contains(name,@termsQuote) then 2
   else 1
   end) as 'sort'
  FROM [Products] tp
  where FREETEXT(description,@termsQuote) or Contains(name,@termsQuote)
  ) tp
ON p.id = tp.id 
order by tp.sort desc;

上面的查询比使用传统的LIKE查询功能要增强不少。其实Contains和Freetext还有许多其它的用法,上面只是展示了一下基本用法,。上面的查询语句仅仅是做了关键词匹配,在完成接下来的章节学习后,将增加如下功能:

  • 结合SOUNDEX和全文索引,完成查询语句纠错机制。
  • 结合全文索引,完成搜素提示功能。

 

  • 1. 全文索引不能查询同音词。比如:不同通过rabbet查rabbit。
  • 2. 全文索引不能查询同义词。比如:不能通过rat查mouse。
  • 3. 全文索引缺乏纠错机制,虽然可以通过freetext函数查询关键词的变体,但是不能纠正输入错误的单词。比如:可以通过catch查出相应的变体caught, catching, catches等,但如果我输入的是catsh就匹配不到catch单词。
  • 4. 全文索引缺少搜索提示模块,全文索引仅仅是完成搜索部分,关于搜索提示却需要开发者自己去架构实现相应的功能,这增加了开发者的工作量。 

上面的四点问题中,在讨论完下一节《4. 如何查看自己的数据都被分解成了那些关键字》后,笔者会解决上面的局限性问题,以及提供一些合理的建议。

 

3. 如何查看自己的数据都被分解成了那些关键词

当我们建立全文索引时,首先需要给全文索引指定一个语言编号,然后全文索引会根据不同的语言编号使用不同的语言分割器,不同的分割器会分割出不同的关键词列表。当使用全文索引查询数据时,就会匹配分割得到的关键词列表。

 查看所有注册了的词语分割器列表

EXEC sp_help_fulltext_system_components 'wordbreaker';  

查看所有支持的语言列表

select * from sys.fulltext_languages

使用dm_fts_parser分解语句获得关键词列表

-- 分解语句:She catches a cat
SELECT * FROM sys.dm_fts_parser (
'"She catches a cat"',  --待分解的语句
1033,  --语言编号, 1033代表English,语言编号信息可以通过查看sys.fulltext_languages表获取
0,  -- stoplist: 0表示使用默认的,NULL表示不使用。
0  -- accent_sensitivity,0表示insensitivity,1表示sensitivity
);

--查看一个表被分解成的所有关键词列表
SELECT * FROM sys.dm_fts_index_keywords_by_document(
db_id('Test'),  -- 数据库对象ID
object_id('dbo.Text_Test_Table') -- 表对象ID
)

到这里,我们已经知道如何分解语句获取关键词列表。接下来我们将继续优化上面的查询案例,第一点是如何结合SOUNDEX和全文索引建立关键词搜索纠错机制,第二点是如何建立一个搜索提示功能。

当用户搜索某关键词语句时,如果有其它的关键词和用户搜索的关键词发音是一样的,那么就需要提示给用户。就类似Google搜索这样的提供功能。

【SqlServer】管理全文索引(FULL TEXT INDEX)

当在Google搜索 she catsh a cat的时候,Google会提示是否是指 she catch a cat.  类似这样的纠错提示,我们可以通过下面这个T-SQL实现一个简单的版本。

declare @searchTerms nvarchar(1000) = N'"she catsh a cat"';
select * from (
select display_term,SOUNDEX(display_term) as st_soundindex from sys.dm_fts_parser(
    @searchTerms,
    (select lcid from sys.fulltext_languages where [name] = 'British English'),--语言需要和Products表使用的语言保持一致
    NULL,
    0
)) st
join (SELECT display_term,SOUNDEX(display_term) o_soundindex
FROM sys.dm_fts_index_keywords_by_document(db_id('Test'), object_id('dbo.Products'))) ot
on st.display_term != ot.display_term and st.st_soundindex = ot.o_soundindex;

效果图:

【SqlServer】管理全文索引(FULL TEXT INDEX)

 通过上面的脚本对比,我们就可以查询出catsh和catch是同音的,然后程序就可以拿到这个结果给用户做纠错提示了。

 

关于搜索提示这部分,非常遗憾,全文索引并未提供相关的功能。这部分需要程序开发自己来实现,这里笔者谈一谈自己的实现思路:

  1. 建立一张表单独维护搜索提示词(search_terms)
  2. 给search_terms表中插入一些预定义的搜索提示词
  3. 用户自己搜索的词,也可以插入到search_terms表中。但是最终需要运维审核后,才可以显示给前端的用户搜索。
  4. 当用户在搜索框输入的时,动态加载search_terms表中的审核数据显示到前端页面。完成搜索提示功能。

到这里的,全文索引的主体知识点自己都讲到了。全文索引就是一个特定语言的搜索功能(Language-Specific Search),所以给自己的数据指定语言类型是非常重要的。全文索引的功能较LIKE的功能有所提升,但全文索引也不是万能的(我们也不能期望全文索引把所有功能都完成),比如:

  • 同义词搜索,比如搜rat, 可以显示mouse 或 rodent.
  • 同义句的搜索,比如搜索rat is big, 可以显示giant mouse monstor之类的。
  • 搜索提示功能

4. 参考文献

1. sys.dm_fts_index_keywords_by_document (Transact-SQL)

2. Full-Text Search

3. Hands on Full-Text Search in SQL Server


程序员灯塔
转载请注明原文链接:【SqlServer】管理全文索引(FULL TEXT INDEX)
喜欢 (0)