【SqlServer】管理全文索引（FULL TEXT INDEX）
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;
输出：
通过上面的输出结果，可以看出一个表有了两个fragment对象。如果像这样的fragment对象越来越多的话，是会影响FULTEXT INDEX的查询效率的。为了减少Fragment的数量，可以使用ALTER命令将所有的Fragment整合到一起。
ALTER FULLTEXT CATALOG [your-catalog-name] REORGANIZE;
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'))
效果对照图：
索引表中的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还有许多其它的用法，上面只是展示了一下基本用法。上面的查询语句仅仅是做了关键词匹配，但还是存在如下的一些缺陷。
- 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搜索这样的提供功能。
当在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;
效果图：
通过上面的脚本对比，我们就可以查询出catsh和catch是同音的，然后程序就可以拿到这个结果给用户做纠错提示了。
关于搜索提示这部分，非常遗憾，全文索引并未提供相关的功能。这部分需要程序开发自己来实现，这里笔者谈一谈自己的实现思路：
- 建立一张表单独维护搜索提示词（search_terms）
- 给search_terms表中插入一些预定义的搜索提示词
- 用户自己搜索的词，也可以插入到search_terms表中。但是最终需要运维审核后，才可以显示给前端的用户搜索。
- 当用户在搜索框输入的时，动态加载search_terms表中的审核数据显示到前端页面。完成搜索提示功能。
到这里的，全文索引的主体知识点自己都讲到了。全文索引就是一个特定语言的搜索功能（Language-Specific Search）,所以给自己的数据指定语言类型是非常重要的。全文索引的功能较LIKE的功能有所提升，但全文索引也不是万能的（我们也不能期望全文索引把所有功能都完成），比如：
- 同义词搜索，比如搜rat, 可以显示mouse 或 rodent.
- 同义句的搜索，比如搜索rat is big, 可以显示giant mouse monstor之类的。
- 搜索提示功能
- ...
