SQLServer管理全⽂索引
全⽂索引不同于常见的聚集索引或⾮聚集索引，这些索引的内部实现是平衡树（B-Tree）结构，⽽全⽂索引在物理上是由⼀系列的内部表（Internal tables）构成的，这些内部表称作全⽂索引⽚段（Fragment），每⼀个索引⽚段也叫做⼀个倒转索引（Inverted index），也就是说，每⼀个倒转索引都是由⼀个内部表（Internal Table）实现的。 有⼀些好奇的朋友可能会发问：“为什么⼀个全⽂索引是由⼀系列的倒转索引构成的，⽽不是⼀个？为什么叫做倒转索引，把什么倒转了？”
当新建全⽂索引之后，全⽂索引只有⼀个索引⽚段（Fragment），索引⽚段中没有冗余的数据，从全⽂索引中执⾏contains命令时，只需要从这⼀个倒转索引中查，返回结果即可。随着业务数据的更新，基础表（underlying table）的数据也会更新，这时，全⽂索引会创建新的倒转索引，但是，旧的数据没有被删除，这样会导致倒转索引的数量增加，也就是说，全⽂索引的⽚段增加，此时，全⽂索引就是由⼀系列的索引⽚段构成的。当全⽂索引的⽚段持续增多时，从全⽂索引中进⾏⽂本搜索需要查更多的数据⾏，导致全⽂搜索的性能下降。在管理全⽂索引时，必须对索引⽚段进⾏重组（reorganize）或重建（rebuild），把已删除的数据从索引⽚段中物理删除，减少全⽂索引的⽚段数量，提⾼全⽂搜索的性能。
⾄于为什么称作倒转索引，这跟倒转索引存储的数据有关。
⼀，倒转索引的结构
为了便于描述，把全⽂索引中存储的⼀⾏数据叫做⼀个⽂档，每⼀个⽂档都使⽤唯⼀的⽂档ID（DocID）标识，这个DocID也就是在创建全⽂索引之前，必须创建的唯⼀索引键。
⼤家知道，全⽂索引中存储的不是整个⽂本，⽽是把⽂本分词之后，存储单个标记（Token）的信息，标记（Token）是分词，及其位置等信息的统称。在填充全⽂索引的时候，分词器（word breader）将字符串拆分成多个单词。如果单词是⼀个停⽤词（stopword），那么该分词被过滤掉，不会存储到倒转索引中，但是停⽤词的位置（position）会被考虑，⼀个分词在全⽂索引中的位置（Position）是该分词在源⽂本中的位置。简⽽⾔之，倒转索引中存储的数据是分词和DocID之间的映射。 由DocID来查询分词，是正向的；⽽由分词来定位DocID，是倒转的，这就是倒转索引名称的由来。 例如，⼀个基础表Document有两列，DocumentID和Title，在字段Title上创建全⽂索引：
全⽂引擎⾸先要对Title字段的⽂本进⾏分词，倒转索引中主要包含四个字段：
Keyword字段：单个分词，从Title字段中抽取的⼀个标记（Token）。
ColId字段：列序号，⽤于标记全⽂索引的列。
DocId字段：⽂档ID，是8Byte的long类型，⽤于唯⼀标记当前的⽂档。如果唯⼀索引键是整数类型，那么DocID就是唯⼀索引键；如果唯⼀索引键不是整数类型，那么DocID经过中间的映射表、唯⼀映射到唯⼀索引键。因此，整数类型的唯⼀索引键能够优化全⽂查询的性能。
Occurrence字段：分词的位置，或者叫做偏移量（Offset）。
林荫道CreateTime字段：时间戳字段（timestamp ），⽤于记录倒转索引创建的时间。
例如，下图是Document表的索引⽚段Fragment1：
对于DocumentID=1的⽂档，分词器把Title字段拆分成5个分词，这5个分词分别是：Crank、Arm、and、Tire、Maintenance，出现的位置分别是1，2，3，4，5，由于分词and是⼀个停⽤词，过滤器会把分词and过滤掉，但是and分词的位置会计算在后续的分词上。因此，分词Tire的位置（Occurrence）是4，⽽不是3。
⼆，全⽂索引的拆分
全⽂索引通常会拆分成多个索引⽚段，⼀些索引⽚段可能包含新的数据，⽽⼀些索引⽚段可能包含已经被删除的数据。例如，如果有⼀个⽤户把DocumentID=3的⽂档的Title字段更新为Rear Reflector：
全⽂索引会新建⼀个索引⽚段Fragmeng2，如下图所⽰，
因此，如果有⽤户查询"Rear Reflector" ，DocID3将会被返回。每⼀个Fragment都会记录创建的时间，当相同的DocID出现在不同的索引⽚段中，创建时间晚的是最新的数据。索引⽚段的创建时间可以从系统视图：中查看。
三，全⽂索引⽚段的重组
当数据持续更新时，索引⽚段的数量也会持续增加，⽽全⽂查询必须⾸先搜索每⼀个索引⽚段，然后丢弃⽆⽤的⽼数据，这会导致全⽂查询的性能下降，必须减少索引⽚段的数量。由于每⼀个全⽂索引都属于⼀个全⽂⽬录（fulltext catalog），SQL Server使⽤TSQL 命令和REORGANIZE 选项对⽬录中的所有全⽂索引进⾏重组操作。
SQL Server使⽤master merge来重组全⽂索引，也就是说，把全⽂索引的各个索引⽚段归并到⼀个打的⽚段中，然后把废弃的⽂档从全⽂索引中删除，这样重组之后，全⽂索引中包含的都是纯净的数据：
在填充全⽂索引时，为了提⾼全⽂索引的填充速度，全⽂引擎使⽤Range来管理。Range是并⾏处理的进程，需要⼤量消耗CPU资源。batch是基础表（underlying table）的数据块，每个Range 都会产⽣多个batch，分batch处理数据能够提⾼全⽂索引填充（population）的
他者速度。SQL Server 从基础表中读取数据产⽣batch，每个batch经过全⽂引擎的处理，会产⽣⼀个索引⽚段。在填充操作完成后，SQL Server 会进⾏⼀次Master Merge 操作，将索引⽚段归并到Master Fragment。
通过查看当前正在被处理的Ranges，每个Range都会分batch来处理。SQL Server 处理Batches的过程，可以通过来监控，
其 crawl_memory_address column 指定其Parent Range。
全⽂索引的重组，可以设置调度程序（Schedule），通过Population Schedule tab，创建schedule和Job，按照schedule对全⽂索引进⾏重组（reorganize）：
四，配置全⽂索引的停⽤词
为了阻⽌全⽂索引把停⽤词填充到全⽂索引中，SQL Server允许⽤户⾃定义停⽤词列表，把常⽤词（这些词对查询没有任何帮助）添加到停⽤词列表中。在填充全⽂索引时，全⽂引擎会把停⽤词过滤掉，这意味着，全⽂查询不会搜索停⽤词，尽管全⽂索引会忽略停⽤词，但是，停⽤词的位置会被考虑进去，每个分词的位置是该分词在源⽂本中的偏移量。
通过创建停⽤词列表（StopLists），通过向停⽤词列表中增加和删除停⽤词（Stopword），通过更新
全⽂索引引⽤的停⽤词列表，实例代码如下：
vishnu
CREATE FULLTEXT STOPLIST stoplist_name
FROM SYSTEM STOPLIST;
ALTER FULLTEXT STOPLIST stoplist_name
ADD'stopword' LANGUAGE language_term;
ALTER FULLTEXT INDEX
ON table_name
SET STOPLIST =stoplist_name
[WITH NO POPULATION];
五，查看分词
分词是全⽂引擎的⼀项重要的功能，通过可以分词器对⽂本分词之后的结果，这也可以⽤于查看contains ⼦句产⽣的分词：
sys.dm_fts_parser('query_string', lcid, stoplist_id, accent_sensitivity)
1，查看语句的分词
SELECT fp.keyword,
fp.phrase_id,
fp.special_term,
fp.display_term,
pansion_type
when0then N'Single word case'
when2then N'Inflectional expansion'
when4then N'Thesaurus expansion/replacement'
end as expansion_type,
fp.source_term
FROM sys.dm_fts_parser (' "The Microsoft business analysis" or "MS revenue" or "multi-million" ', 1033, 0, 0) as fp
2，查看contains 谓词如何解析 FORMSOF ⼦句
查看同源词， 'query_string' 的格式是： 'FORMSOF( INFLECTIONAL, query_term )'
SELECT fp.keyword,
fp.phrase_id,
fp.special_term,
fp.display_term,
pansion_type
when0then N'Single word case'
when2then N'Inflectional expansion'
when4then N'Thesaurus expansion/replacement'
end as expansion_type,
fp.source_term
FROM sys.dm_fts_parser ('FORMSOF(INFLECTIONAL,run ) ', 1033, 0, 0) as fp
查看同义词, 'query_string' 的格式是： 'FORMSOF( THESAURUS, query_term )'
SELECT fp.keyword,
fp.phrase_id,
fp.special_term,
fp.display_term,
pansion_type
when0then N'Single word case'
when2then N'Inflectional expansion'
when4then N'Thesaurus expansion/replacement'
end as expansion_type,
fp.source_term
FROM sys.dm_fts_parser ('FORMSOF(THESAURUS,run ) ', 1033, 0, 0) as fp
六，查看全⽂索引的元数据
1，查看数据库中的全⽂索引
select
object_name(i.object_id) as TableName,
i.unique_index_id,
i.fulltext_catalog_id,
c.name as fulltext_catalog_name,
i.is_enabled,
i.change_tracking_state_desc,
i.has_crawl_completed,
iif(i.has_crawl_completed=1,datediff(awl_start_awl_end_date),0) as crawl_duration_minute, i.crawl_start_date,
i.incremental_timestamp,
i.stoplist_id,
i.data_space_id,
ds.name as data_space_Name,
from sys.fulltext_indexes i
inner join sys.data_spaces ds
on i.data_space_id=ds.data_space_id
inner join sys.fulltext_catalogs c
on i.fulltext_catalog_id=c.fulltext_catalog_id
View Code
2，查看全⽂索引的所有分词
declare@db_id int
declare@table_id int
set@db_id=db_id()
set@table_id=object_id(N'schema_name.table_name',N'U')
select kw.keyword,
kw.display_term,
东台杀人
kw.document_count
from sys.dm_fts_index_keywords(@db_id,@table_id) as kw
View Code
3，查看单个⽂档的分词
declare@db_id int
龙军金属declare@table_id int
set@db_id=db_id()
set@table_id=object_id(N'meetup.Events',N'U')
select kw.keyword,
汉阳铁厂博物馆kw.display_term,
kw.document_id,
from sys.dm_fts_index_keywords_by_document(@db_id,@table_id) as kw
View Code
4，查看全⽂索引的内部表（Internal Tables）
SELECT SCHEMA_NAME(itab.schema_id) AS[schema]
,itab.name AS internal_table_name
,typ.name AS column_data_type
,col.name as column_name
,lumn_id
,OBJECT_NAME(itab.parent_object_id) as base_table_name
FROM sys.internal_tables AS itab
INNER lumns AS col ON itab.object_id= col.object_id
INNER pes AS typ ON typ.user_type_id = col.user_type_id
where itab.internal_type_desc=N'FULLTEXT_COMP_FRAGMENT'
ORDER BY itab.name, lumn_id;
View Code
5，查看每⼀个倒转索引的⼤⼩和包含的数据⾏数
select object_name(table_id) as base_table_name,
object_name(fragment_object_id) as fragment_table_name,
fragment_id as Ordinal,
status,
data_size,
row_count,
[timestamp]
from sys.fulltext_index_fragments
View Code
字段 Status 表⽰索引⽚段的状态：
0 = Newly created and not yet used
1 = Being used for insert during fulltext index population or merge
4 = Closed. Ready for query
6 = Being used for merge input and ready for query
8 = Marked for deletion. Will not be used for query and merge source.
当状态值为4或6时，表⽰索引⽚段已经是全⽂索引的⼀部分，可以被查询，字段Timestamp表⽰该索
引⽚段创建的时间戳，时间较晚的索引碎⽚中存储的是最新的数据，⽽时间较早的索引⽚段中存储的是被删除/淘汰的数据。在执⾏全⽂查询时，返回的结果中会丢弃被淘汰的数据。
6，查看全⽂索引填充的状态
通过查看当前正在运⾏的填充，每⼀次填充都会分多个Ranges并⾏填充，每⼀个Range可以分多个Batch进⾏。
select ip.database_id,
object_name(ip.table_id,ip.database_id) as table_name,
c.name as catalog_name,
ip.population_type,ip.population_type_description,
ip.is_clustered_index_scan,
ip.range_count,
ippleted_range_count,
ip.outstanding_batch_count,
ip.status,
ip.status_description,
pr.session_id as Range_SessionID,
pr.processed_row_count,
ob.batch_id
from sys.dm_fts_index_population ip
left join sys.fulltext_catalogs c
on ip.catalog_id=c.fulltext_catalog_id
left join sys.dm_fts_population_ranges pr
_address=pr.parent_memory_address
left join sys.dm_fts_outstanding_batches ob
_awl_memory_address
order by ob.batch_id
View Code
参考⽂档：
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议