创建和管理全文索引Create and Manage Full-Text Indexes

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题介绍了如何在 SQL Server 中创建、填充和管理全文索引。This topic describes how to create, populate, and manage full-text indexes in SQL Server.

先决条件 - 创建全文目录Prerequisite - Create a full-text catalog

必须具有全文目录,然后才能创建全文索引。Before you can create a full-text index, you have to have a full-text catalog. 目录是包含一个或多个全文索引的虚拟容器。The catalog is a virtual container for one or more full-text indexes. 有关详细信息,请参阅创建和管理全文目录For more info, see Create and Manage Full-Text Catalogs.

创建、更改或删除全文索引Create, alter, or drop a full-text index

创建全文索引Create a full-text index

更改全文索引Alter a full-text index

删除全文索引Drop a full-text index

填充全文索引Populate a full-text index

创建和维护全文索引的过程称为“填充” (也称为“爬网” )。The process of creating and maintaining a full-text index is called a population (also known as a crawl). 有三种类型的全文索引填充:There are three types of full-text index population:

  • 完全填充Full population
  • 基于更改跟踪的填充Population based on change tracking
  • 基于时间戳的增量填充。Incremental population based on a timestamp.

有关详细信息,请参阅填充全文索引For more info, see Populate Full-Text Indexes.

查看全文索引的属性View the properties of a full-text index

使用 Transact-SQL 查看全文索引的属性View the properties of a full-text index with Transact-SQL

目录视图或动态管理视图Catalog or Dynamic Management View 描述Description
sys.fulltext_index_catalog_usages (Transact-SQL)sys.fulltext_index_catalog_usages (Transact-SQL) 对于全文索引引用的每个全文目录,返回与其对应的一行。Returns a row for each full-text catalog to full-text index reference.
sys.fulltext_index_columns (Transact-SQL)sys.fulltext_index_columns (Transact-SQL) 对构成全文索引的每列都包含一行。Contains a row for each column that is part of a full-text index.
sys.fulltext_index_fragments (Transact-SQL)sys.fulltext_index_fragments (Transact-SQL) 全文索引使用内部表(称为“全文索引片断”)来存储倒排索引数据。A fulltext index uses internal tables called full-text index fragments to store the inverted index data. 可以使用此视图来查询有关这些片断的元数据。This view can be used to query the metadata about these fragments. 在此视图中,每个全文索引片断在每个包含全文索引的表中各占一行。This view contains a row for each full-text index fragment in every table that contains a full-text index.
sys.fulltext_indexes (Transact-SQL)sys.fulltext_indexes (Transact-SQL) 表对象的每个全文索引各占一行。Contains a row per full-text index of a tabular object.
sys.dm_fts_index_keywords (Transact-SQL)sys.dm_fts_index_keywords (Transact-SQL) 返回有关指定表的全文索引内容的信息。Returns information about the content of a full-text index for the specified table.
sys.dm_fts_index_keywords_by_document (Transact-SQL)sys.dm_fts_index_keywords_by_document (Transact-SQL) 返回有关指定表的文档级全文索引内容的信息。Returns information about the document-level content of a full-text index for the specified table. 给定关键字可以出现在几个文档中。A given keyword can appear in several documents.
sys.dm_fts_index_population (Transact-SQL)sys.dm_fts_index_population (Transact-SQL) 返回有关当前正在进行的全文索引填充的信息。Returns information about the full-text index populations currently in progress.

使用 Management Studio 查看全文索引的属性View the properties of a full-text index with Management Studio

  1. 在 Management Studio 中,在对象资源管理器中展开服务器。In Management Studio, in Object Explorer, expand the server.

  2. 展开“数据库” ,然后展开包含全文索引的数据库。Expand Databases, and then expand the database that contains the full-text index.

  3. 展开 “表”Expand Tables.

  4. 右键单击对其定义了全文索引的表,选择 “全文索引”,然后在 “全文索引”上下文菜单中单击 “属性”。Right-click the table on which the full-text index is defined, select Full-Text index, and on the Full-Text index context menu, click Properties. 此时将打开“全文索引属性” 对话框。This opens the Full-text index Properties dialog box.

  5. “选择页” 窗格中,您可以选择下列页中的任一页:In the Select a page pane, you can select any of the following pages:

    Page 描述Description
    常规General 显示全文索引的基本属性。Displays basic properties of the full-text index. 这些基本属性包括若干个可修改属性和多个不可更改属性,后者如数据库名称、表名和全文键列的名称。These include several modifiable properties and a number of unchangeable properties such as database name, table name, and the name of full-text key column. 可修改属性包括:The modifiable properties are:

    全文索引非索引字表Full-Text Index Stoplist

    全文索引已启用Full-Text Indexing Enabled

    更改跟踪Change Tracking

    搜索属性列表Search Property List
    “列”Columns 显示可用于全文索引的表列。Displays the table columns that are available for full-text indexing. 对于选中的列,均会创建全文索引。The selected column or columns are full-text indexed. 您可以根据需要选择将任意数目的可用列包括在全文索引中。You can select as many of the available columns as you want to include in the full-text index. 有关详细信息,请参阅填充全文索引For more info, see Populate Full-Text Indexes.
    计划Schedules 使用此页可以创建或管理 SQL Server 代理作业的计划,该作业用于启动全文索引填充的表增量填充。Use this page to create or manage schedules for a SQL Server Agent job that starts an incremental table population for the full-text index populations. 有关详细信息,请参阅填充全文索引For more info, see Populate Full-Text Indexes.

    注意:在退出“全文检索属性”对话框之后,所有新创建的计划都将与 SQL Server 代理作业(对 database_name.table_name 启动表增量填充)相关联 。Note: After you exit the Full-Text Index Properties dialog box, any newly created schedule is associated with a SQL Server Agent job (Start Incremental Table Population on database_name.table_name).
  6. 单击“确定”。 Click OK. 以保存任何更改并退出“全文索引属性”对话框。 to save any changes and exit the Full-text index Properties dialog box.

查看索引表和列的属性View the properties of indexed tables and columns

一些 Transact-SQLTransact-SQL 函数(例如 OBJECTPROPERTYEX)可用来获取各种全文索引属性的值。Several Transact-SQLTransact-SQL functions such as OBJECTPROPERTYEX can be used to obtain the value of various full-text indexing properties. 此信息可用于全文搜索的管理和故障排除。This information is useful for administering and troubleshooting full-text search.

下表列出了与索引表和列相关的全文属性及其相关 Transact-SQLTransact-SQL 函数。The following table lists the full-text properties related to indexed tables and columns and their related Transact-SQLTransact-SQL functions.

属性Property 描述Description 函数Function
FullTextTypeColumnFullTextTypeColumn 表中的 TYPE COLUMN,其中包含列的文档类型信息。TYPE COLUMN in the table that holds the document type information of the column. COLUMNPROPERTYCOLUMNPROPERTY
IsFulltextIndexedIsFulltextIndexed 列是否启用了全文索引。Whether a column has been enabled for full-text indexing. COLUMNPROPERTYCOLUMNPROPERTY
IsFulltextKeyIsFulltextKey 索引是否为表的全文键。Whether the index is the full-text key for a table. INDEXPROPERTYINDEXPROPERTY
TableFulltextBackgroundUpdateIndexOnTableFulltextBackgroundUpdateIndexOn 表是否具有全文后台更新索引。Whether a table has full-text background update indexing. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableFulltextCatalogIdTableFulltextCatalogId 表的全文索引数据所在的全文目录 ID。Full-text catalog ID in which the full-text index data for the table resides. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableFulltextChangeTrackingOnTableFulltextChangeTrackingOn 表是否启用了全文更改跟踪。Whether a table has full-text change-tracking enabled. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableFulltextDocsProcessedTableFulltextDocsProcessed 自开始全文检索以来所处理的行数。Number of rows processed since the start of full-text indexing. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableFulltextFailCountTableFulltextFailCount 全文搜索未编制索引的行数。Number of rows Full-Text Search did not index. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableFulltextItemCountTableFulltextItemCount 成功编制了全文索引的行数。Number of rows that were successfully full-text indexed. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableFulltextKeyColumnTableFulltextKeyColumn 全文唯一键列的列 ID。The column ID of the full-text unique key column. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableFullTextMergeStatusTableFullTextMergeStatus 具有全文索引的表当前是否正在合并。Whether a table that has a full-text index is currently in merging. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableFulltextPendingChangesTableFulltextPendingChanges 要处理的挂起更改跟踪项的数目。Number of pending change tracking entries to process. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableFulltextPopulateStatusTableFulltextPopulateStatus 全文表的填充状态。Population status of a full-text table. OBJECTPROPERTYEXOBJECTPROPERTYEX
TableHasActiveFulltextIndexTableHasActiveFulltextIndex 表是否具有活动的全文索引。Whether a table has an active full-text index. OBJECTPROPERTYEXOBJECTPROPERTYEX

获取关于全文键列的信息Get info about the full-text key column

通常情况下,CONTAINSTABLE 或 FREETEXTTABLE 行集值函数的结果需要与基表相联接。Typically, the result of CONTAINSTABLE or FREETEXTTABLE rowset-valued functions need to be joined with the base table. 在这样的情况下,需要知道唯一键列名称。In such cases, you need to know the unique key column name. 可以查询给定的唯一索引是否作为全文键使用,并且可以获取全文键列的标识符。You can inquire whether a given unique index is used as the full-text key, and you can obtain the identifier of the full-text key column.

确定给定的唯一索引是否用作全文键列Determine whether a given unique index is used as the full-text key column

使用 SELECT 语句调用 INDEXPROPERTY 函数。Use a SELECT statement to call the INDEXPROPERTY function. 在此函数的调用过程中,使用 OBJECT_ID 函数将表名 (table_name) 转换为表 ID,指定该表的唯一索引的名称,然后指定 IsFulltextKey 索引属性,如下所示:In the function call use the OBJECT_ID function to convert the name of the table (table_name) into the table ID, specify the name of a unique index for the table, and specify the IsFulltextKey index property, as follows:

SELECT INDEXPROPERTY( OBJECT_ID('table_name'), 'index_name',  'IsFulltextKey' );  

如果使用此索引来强制实现全文键列的唯一性,此语句返回 1,否则返回 0。This statement returns 1 if the index is used to enforce uniqueness of the full-text key column and 0 if it is not.

示例Example

下例查询 PK_Document_DocumentID 索引是否用于强制实现全文键列的唯一性,如下所示:The following example inquires whether the PK_Document_DocumentID index is used to enforce the uniqueness of the full-text key column, as follows:

USE AdventureWorks  
GO  
SELECT INDEXPROPERTY ( OBJECT_ID('Production.Document'), 'PK_Document_DocumentID',  'IsFulltextKey' )  

如果使用 PK_Document_DocumentID 索引来强制实现全文键列的唯一性,则此示例返回 1。This example returns 1 if the PK_Document_DocumentID index is used to enforce uniqueness of the full-text key column. 否则,它返回 0 或 NULL。Otherwise, it returns 0 or NULL. NULL 表示您使用的是无效索引名称,索引名称与表不对应,或表不存在,等等。NULL implies you are using an invalid index name, the index name does not correspond to the table, the table does not exist, or so forth.

查找全文键列的标识符Find the identifier of the full-text key column

每个启用全文的表都有一个列,该列用于强制实现表中行的唯一性(“唯一键列”) 。Each full-text enabled table has a column that is used to enforce unique rows for the table (the unique**key column). 从 OBJECTPROPERTYEX 函数获取的 TableFulltextKeyColumn 属性包含唯一键列的列 ID。The TableFulltextKeyColumn property, obtained from the OBJECTPROPERTYEX function, contains the column ID of the unique key column.

若要获取此标识符,可以使用 SELECT 语句调用 OBJECTPROPERTYEX 函数。To obtain this identifier, you can use a SELECT statement to call the OBJECTPROPERTYEX function. 使用 OBJECT_ID 函数将表名 (table_name) 转换为表 ID,并指定 TableFulltextKeyColumn 属性,如下所示:Use the OBJECT_ID function to convert the name of the table (table_name) into the table ID and specify the TableFulltextKeyColumn property, as follows:

SELECT OBJECTPROPERTYEX(OBJECT_ID( 'table_name'), 'TableFulltextKeyColumn' ) AS 'Column Identifier';  

示例Examples

下例返回全文键列的标识符或 NULL。The following example returns the identifier of the full-text key column or NULL. NULL 表示您使用的是无效索引名称,索引名称与表不对应,或表不存在,等等。NULL implies that you are using an invalid index name, the index name does not correspond to the table, the table does not exist, or so forth.

USE AdventureWorks;  
GO  
SELECT OBJECTPROPERTYEX(OBJECT_ID('Production.Document'), 'TableFulltextKeyColumn');  
GO  

下例说明如何使用唯一键列的标识符获取列的名称。The following example shows how to use the identifier of the unique key column to obtain the name of the column.

USE AdventureWorks;  
GO  
DECLARE @key_column sysname  
SET @key_column = Col_Name(Object_Id('Production.Document'),  
ObjectProperty(Object_id('Production.Document'),  
'TableFulltextKeyColumn')   
)  
SELECT @key_column AS 'Unique Key Column';  
GO  

此示例返回一个名为 Unique Key Column的结果集列,该结果集列显示单个行,该行包含 Document 表的唯一键列 DocumentID 的名称。This example returns a result set column named Unique Key Column, which displays a single row containing the name of the unique key column of the Document table, DocumentID. 请注意,如果此查询包含无效的索引名称,索引名称与表不对应或表不存在等,它将返回 NULL。Note that if this query contained an invalid index name, the index name did not correspond to the table, the table did not exist, and so forth, it would return NULL.

索引 varbinary(max) 和 xml 列Index varbinary(max) and xml columns

如果 varbinary(max)varbinaryxml 列是全文索引列,则与任何其他全文索引列一样,可以使用全文谓词(CONTAINS 和 FREETEXT)以及函数(CONTAINSTABLE 和 FREETEXTTABLE)来查询该列。If a varbinary(max), varbinary, or xml column is full-text indexed, it can be queried using the full-text predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE), like any other full-text indexed column.

索引 varbinary(max) 或 varbinary 数据Index varbinary(max) or varbinary data

单个 varbinary(max)varbinary 列可存储多种类型的文档。A single varbinary(max) or varbinary column can store many types of documents. SQL ServerSQL Server 支持安装了相应筛选器并且在操作系统中可用的任何文档类型。supports any document type for which a filter is installed and available in the operative system. 每个文档的文档类型由该文档的文件扩展名标识。The document type of each document is identified by the file extension of the document. 例如,对于 .doc 文件扩展名,全文搜索将使用支持 Microsoft Word 文档的筛选器。For example, for a .doc file extension, full-text search uses the filter that supports Microsoft Word documents. 有关可用文档类型的列表,请查询 sys.fulltext_document_types 目录视图。For a list of available document types, query the sys.fulltext_document_types catalog view.

请注意,全文引擎可以利用操作系统中安装的现有筛选器。Note that the Full-Text Engine can leverage existing filters that are installed in the operating system. 在您可以使用操作系统筛选器、断字符和词干分析器之前,您必须将它们加载到服务器实例中,如下所示:Before you can use operating-system filters, word breakers, and stemmers, you must load them in the server instance, as follows:

EXEC sp_fulltext_service @action='load_os_resources', @value=1  

若要对 varbinary(max) 列创建全文索引,全文引擎需要访问 varbinary(max) 列中文档的文件扩展名。To create a full-text index on a varbinary(max) column, the Full-Text Engine needs access to the file extensions of the documents in the varbinary(max) column. 此信息必须存储在一个称为“类型列”的表列中,该列必须与全文索引中的 varbinary(max) 列相关联。This information must be stored in a table column, called a type column, that must be associated with the varbinary(max) column in the full-text index. 在为文档创建索引时,全文引擎将使用类型列中的文件扩展名来标识要使用的筛选器。When indexing a document, the Full-Text Engine uses the file extension in the type column to identify which filter to use.

索引 xml 数据Index xml data

xml 数据类型列仅存储 XML 文档和片段,并且只有 XML 筛选器用于此类文档。An xml data type column stores only XML documents and fragments, and only the XML filter is used for the documents. 因此,无需类型列。Therefore, a type column is unnecessary. xml 列上,全文索引会为 XML 元素的内容创建索引,但会忽略 XML 标记。On xml columns, the full-text index indexes the content of the XML elements, but ignores the XML markup. 不为数值的属性值都会进行全文索引。Attribute values are full-text indexed unless they are numeric values. 元素标记用作标记边界。Element tags are used as token boundaries. 支持包含多种语言的格式正确的 XML 或 HTML 文档和片段。Well-formed XML or HTML documents and fragments containing multiple languages are supported.

有关 xml 列的索引编制和查询的详细信息,请参阅结合使用全文搜索和 XML 列For more info about indexing and querying on an xml column, see Use Full-Text Search with XML Columns.

为表禁用或重新启用全文索引Disable or re-enable tull-text indexing for a table

SQL ServerSQL Server中,默认情况下所有由用户创建的数据库都启用了全文索引。In SQL ServerSQL Server, all user-created databases are full-text enabled by default. 另外,在为表创建全文索引并将列添加到索引之后,就会自动为单个表启用全文索引。Additionally, an individual table is automatically enabled for full-text indexing as soon as a full-text index is created on it and a column is added to the index. 从表的全文索引中删除最后一列时,会自动为表禁用全文索引。A table is automatically disabled for full-text indexing when the last column is dropped from its full-text index.

对于具有全文索引的表,可以使用 SQL Server Management StudioSQL Server Management Studio手动为表禁用或重新启用全文索引。On a table that has a full-text index, you can manually disable or re-enable a table for full-text indexing using SQL Server Management StudioSQL Server Management Studio.

  1. 展开服务器组,展开 “数据库”,再展开包含要为其启用全文索引的表的数据库。Expand the server group, expand Databases, and expand the database that contains the table you want to enable for full-text indexing.

  2. 展开“表” ,然后右键单击要为其禁用或重新启用全文索引的表。Expand Tables, and right-click the table that you want to disable or re-enable for full-text indexing.

  3. 选择 “全文索引”,然后单击 “禁用全文索引”或 “启用全文索引”。Select Full-Text index, and then click Disable Full-Text index or Enable Full-Text index.

从表中删除全文索引Remove a full-text index from a table

  1. 在对象资源管理器中,右键单击要删除的全文索引所在的表。In Object Explorer, right-click the table that has the full-text index that you want to delete.

  2. 选择 “删除全文索引”。Select Delete Full-Text index.

  3. 在出现提示时,单击 “确定”,确认是否要删除该全文索引。When prompted, click OK to confirm that you want to delete the full-text index.