填充全文索引Populate Full-Text Indexes

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

创建和维护全文索引涉及使用称为“填充” (也称为“爬网” )的过程填充索引。Creating and maintaining a full-text index involves populating the index by using a process called a population (also known as a crawl).

Types of populationTypes of population

全文索引支持以下填充类型:A full-text index supports the following types of population:

  • 完全填充Full population
  • 基于更改跟踪的自动或手动填充Automatic or manual population based on change tracking
  • 基于时间戳的增量填充Incremental population based on a timestamp

完全填充Full population

在完全填充期间,为表或索引视图的所有行生成索引条目。During a full population, index entries are built for all the rows of a table or indexed view. 全文索引的完全填充为基表或索引视图的所有行生成索引条目。A full population of a full-text index, builds index entries for all the rows of the base table or indexed view.

默认情况下,一旦创建新的全文索引, SQL ServerSQL Server 便会对其进行完全填充。By default, SQL ServerSQL Server populates a new full-text index fully as soon as it is created.

  • 另一方面,完全填充会占用大量的资源。On the one hand, a full population can consume a significant amount of resources. 因此,当在高峰期创建全文索引时,最佳做法通常是将完全填充推迟到非高峰时段,当全文索引的基表非常大时更应如此。Therefore, when creating a full-text index during peak periods, it is often a best practice to delay the full population until an off-peak time, particularly if the base table of an full-text index is large.
  • 另一方面,索引所属的全文目录在填充其所有全文索引之后才可使用。On the other hand, the full-text catalog to which the index belongs is not usable until all of its full-text indexes are populated.

若要创建全文索引但不立即填充该索引,请在 CREATE FULLTEXT INDEX 语句中指定 CHANGE_TRACKING OFF, NO POPULATION 子句。To create a full-text index without populating it immediately, specify the CHANGE_TRACKING OFF, NO POPULATION clause in the CREATE FULLTEXT INDEX statement. 如果指定 CHANGE_TRACKING MANUAL,则在你使用 START FULL POPULATIONSTART INCREMENTAL POPULATION 子句执行 ALTER FULLTEXT INDEX 语句之前,全文引擎不会填充新的全文索引。If you specify CHANGE_TRACKING MANUAL, the Full-Text Engine doesn't populate the new full-text index until you execute an ALTER FULLTEXT INDEX statement using the START FULL POPULATION or START INCREMENTAL POPULATION clause.

示例 - 创建全文索引但不运行完全填充Example - Create a full-text index without running a full population

以下示例对 Production.Document 示例数据库的 AdventureWorks 表创建全文索引。The following example creates a full-text index on the Production.Document table of the AdventureWorks sample database. 此示例使用 WITH CHANGE_TRACKING OFF, NO POPULATION 来延迟初始完全填充。This example uses WITH CHANGE_TRACKING OFF, NO POPULATION to delay the initial full population.

CREATE UNIQUE INDEX ui_ukDoc ON Production.Document(DocumentID);  
CREATE FULLTEXT CATALOG AW_Production_FTCat;  
CREATE FULLTEXT INDEX ON Production.Document  
(  
    Document                         --Full-text index column name   
        TYPE COLUMN FileExtension    --Name of column that contains file type information  
        Language 1033                 --1033 is LCID for the English language  
)  
    KEY INDEX ui_ukDoc  
    ON AW_Production_FTCat  
    WITH CHANGE_TRACKING OFF, NO POPULATION;  
GO  
  

示例 - 对表运行完全填充Example - Run a full population on a table

以下示例对 Production.Document 示例数据库的 AdventureWorks 表运行完全填充。The following example runs a full population on the Production.Document table of the AdventureWorks sample database.

ALTER FULLTEXT INDEX ON Production.Document  
   START FULL POPULATION;  

基于更改跟踪的填充Population based on change tracking

或者,您可以在对全文索引进行初始完全填充之后使用更改跟踪对其进行维护。Optionally, you can use change tracking to maintain a full-text index after its initial full population. 将出现与更改跟踪关联的较小开销,因为 SQL ServerSQL Server 维护它用来跟踪自上次填充后对基表所做更改的表。There is a small overhead associated with change tracking because SQL ServerSQL Server maintains a table in which it tracks changes to the base table since the last population. 当你使用更改跟踪时,SQL ServerSQL Server 会维护基表或索引视图中已通过更新、删除或插入进行过修改的行的记录。When you use change tracking, SQL ServerSQL Server maintains a record of the rows in the base table or indexed view that have been modified by updates, deletes, or inserts. 通过 WRITETEXT 和 UPDATETEXT 所做的数据更改不会反映到全文索引中,也不能使用更改跟踪方法拾取。Data changes made through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking.

备注

对于包含 timestamp 列的表,可以使用增量填充来代替更改跟踪。For tables containing a timestamp column, you can use incremental population instead of change tracking.

如果你在创建索引期间启用更改跟踪,SQL ServerSQL Server 将在新全文索引创建之后立即对其进行完全填充。When you enable change tracking during index creation, SQL ServerSQL Server fully populates the new full-text index immediately after it is created. 其后,将跟踪更改并将更改传播到全文索引。Thereafter, changes are tracked and propagated to the full-text index.

启用更改跟踪Enable change tracking

有两种类型的更改跟踪:There are two types of change tracking:

  • 自动(CHANGE_TRACKING AUTO 选项)。Automatic (CHANGE_TRACKING AUTO option). 自动更改跟踪为默认行为。Automatic change tracking is the default behavior.
  • 手动(CHANGE_TRACKING MANUAL 选项)。Manual (CHANGE_TRACKING MANUAL option).

更改跟踪的类型确定全文索引的填充方式,如下所示:The type of change tracking determines how the full-text index is populated, as follows:

  • 自动填充Automatic population

    默认情况下,或者如果你指定 CHANGE_TRACKING AUTO,全文引擎将对全文索引使用自动填充。By default, or if you specify CHANGE_TRACKING AUTO, the Full-Text Engine uses automatic population on the full-text index. 完成首次完全填充之后,当修改基表中的数据时将跟踪更改并自动传播跟踪的更改。After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. 不过,由于全文索引是在后台更新的,因此传播的更改可能不会立即反映到索引中。The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.

    启动使用自动填充的跟踪更改To start tracking changes with automatic population

    示例 - 更改全文索引以使用自动更改跟踪Example - Alter a full-text index to use automatic change tracking
    以下示例更改 HumanResources.JobCandidate 示例数据库的 AdventureWorks 表的全文索引以使用自动填充的更改跟踪。The following example changes the full-text index of the HumanResources.JobCandidate table of the AdventureWorks sample database to use change tracking with automatic population.

    USE AdventureWorks;  
    GO  
    ALTER FULLTEXT INDEX ON HumanResources.JobCandidate SET CHANGE_TRACKING AUTO;  
    GO   
    
  • 手动填充Manual population

    如果您指定 CHANGE_TRACKING MANUAL,全文引擎将对全文索引使用手动填充。If you specify CHANGE_TRACKING MANUAL, the Full-Text Engine uses manual population on the full-text index. 完成首次完全填充之后,当修改基表中的数据时将跟踪更改。After the initial full population completes, changes are tracked as data is modified in the base table. 但是,这些更改不会传播到全文索引,直至你执行 ALTER FULLTEXT INDEX …START UPDATE POPULATION 语句手动应用更改。However, they are not propagated to the full-text index until you execute an ALTER FULLTEXT INDEX ... START UPDATE POPULATION statement. 您可以使用 SQL ServerSQL Server 代理来定期调用此 Transact-SQLTransact-SQL 语句。You can use SQL ServerSQL Server Agent to call this Transact-SQLTransact-SQL statement periodically.

    启动使用手动填充的跟踪更改To start tracking changes with manual population

    示例 - 使用手动更改跟踪创建全文索引Example - Create a full-text index with manual change tracking
    以下示例对 HumanResources.JobCandidate 示例数据库的 AdventureWorks 表创建全文索引,该全文索引将使用具有手动填充的更改跟踪。The following example creates a full-text index that will use change tracking with manual population on the HumanResources.JobCandidate table of the AdventureWorks sample database.

    USE AdventureWorks;  
    GO  
    CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);  
    CREATE FULLTEXT CATALOG ft AS DEFAULT;  
    CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume)   
       KEY INDEX ui_ukJobCand   
       WITH CHANGE_TRACKING=MANUAL;  
    GO  
    

    示例 - 运行手动填充Example - Run a manual population
    以下示例对 HumanResources.JobCandidate 示例数据库的 AdventureWorks 表的更改跟踪全文索引运行手动填充。The following example runs a manual population on the change-tracked full-text index of the HumanResources.JobCandidate table of the AdventureWorks sample database.

    USE AdventureWorks;  
    GO  
    ALTER FULLTEXT INDEX ON HumanResources.JobCandidate START UPDATE POPULATION;  
    GO  
    

禁用更改跟踪Disable change tracking

基于时间戳的增量填充Incremental population based on a timestamp

增量填充是手动填充全文索引的一种替代机制。An incremental population is an alternative mechanism for manually populating a full-text index. 如果对表进行大量插入操作,则使用增量填充会较使用手动填充有效。If a table experiences a high volume of inserts, using incremental population can be more efficient that using manual population.

您可以对 CHANGE_TRACKING 设置为 MANUAL 或 OFF 的全文索引运行增量填充。You can run an incremental population for a full-text index that has CHANGE_TRACKING set to MANUAL or OFF.

增量填充要求索引表必须具有 timestamp 数据类型的列。The requirement for incremental population is that the indexed table must have a column of the timestamp data type. 如果 timestamp 列不存在,则无法执行增量填充。If a timestamp column does not exist, incremental population cannot be performed.

SQL ServerSQL Server 使用 timestamp 列标识自上次填充后发生更改的行。uses the timestamp column to identify rows that have changed since the last population. 然后,增量填充在全文索引中更新上次填充的当时或之后添加、删除或修改的行。The incremental population then updates the full-text index for rows added, deleted, or modified after the last population, or while the last population was in progress. 在填充结束时,全文引擎将记录新的 timestamp 值。At the end of a population, the Full-Text Engine records a new timestamp value. 此值是 SQL 收集器找到的最大 timestamp 值。This value is the largest timestamp value that SQL Gatherer has found. 下一次启动增量填充时,将使用此值。This value will be used when the next incremental population starts.

在某些情况下,针对增量填充的请求会导致完全填充。In some cases, the request for an incremental population results in a full population.

  • 对不含 timestamp 列的表请求增量填充会导致完全填充操作。A request for incremental population on a table without a timestamp column results in a full population operation.
  • 如果全文索引的第一个填充是增量填充,它将对所有行编制索引并使其等效于完全填充。If the first population on a full-text index is an incremental population, it indexes all rows, making it equivalent to a full population.
  • 如果影响表全文索引的任意元数据自上次填充以来发生了变化,则增量填充请求将作为完全填充来执行。If any metadata that affects the full-text index for the table has changed since the last population, incremental population requests are implemented as full populations. 这包括更改任何列、索引或全文索引定义所引起的元数据更改。This includes metadata changes caused by altering any column, index, or full-text index definitions.

运行增量填充Run an incremental population

若要运行增量填充,请使用 START INCREMENTAL POPULATION 子句执行 ALTER FULLTEXT INDEX 语句。To run an incremental population, execute an ALTER FULLTEXT INDEX statement using the START INCREMENTAL POPULATION clause.

创建或更改增量填充计划Create or change a schedule for incremental population

  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.

    右键单击对其定义了全文索引的表,选择 “全文索引”,然后在 “全文索引”上下文菜单中单击 “属性”。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.

    重要

    如果基表或视图不包含 timestamp 数据类型的列,则无法执行增量填充。If the base table or view does not contain a column of the timestamp data type, incremental population is not possible.

  4. 在“选择页”窗格中选择“计划”。 In the Select a page pane, select Schedules.

    使用此页可以创建或管理 SQL Server 代理作业的计划,该作业用于启动对全文索引基表或索引视图的表增量填充。Use this page to create or manage schedules for a SQL Server Agent job that starts an incremental table population on the base table or indexed view of the full-text index.

    选项如下所示:The options are as follows:

    • 若要创建新计划,请单击“新建”。 To create a new schedule, click New.

      此时将打开“新建全文索引表计划” 对话框,你可以在此对话框中创建计划。This opens the New Full-Text Indexing Table Schedule dialog box, where you can create a schedule. 若要保存计划,请单击 “确定”To save the schedule, click OK.

      重要

      在退出“全文索引属性” 对话框之后,SQL Server 代理作业(对 database_name.table_name 启动表增量填充)将与新计划相关联。A SQL Server Agent job (Start Incremental Table Population on database_name.table_name) is associated with a new schedule after you exit the Full-Text Index Properties dialog box. 如果你针对同一全文索引创建多个计划,这些计划都将使用同一作业。If you create multiple schedules for the same full-text index, they all use the same job.

    • 若要更改现有的计划,选择该计划,然后单击“编辑”。 To change an existing schedule, select the existing schedule and click Edit.

      此时将打开“新建全文索引表计划” 对话框,你可以在此对话框中修改计划。This opens the New Full-Text Indexing Table Schedule dialog box, where you can modify the schedule.

      备注

      有关修改 SQL Server 代理作业的信息,请参阅修改作业For information about modifying a SQL Server Agent job, see Modify a Job.

    • 若要删除现有的计划,选择该计划,然后单击“删除”。 To remove an existing schedule, select the existing schedule and click Delete.

  5. 单击“确定”。 Click OK.

期待您的反馈 :如果在本文中发现过时或不正确的内容(如步骤或代码示例),请告诉我们。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 可以单击此页底部的“反馈” 部分中的“本页” 按钮。You can click the This page button in the Feedback section at the bottom of this page. 我们通常在第二天阅读有关 SQL 的每项反馈。We read every item of feedback about SQL, typically the next day. 谢谢。Thanks.

排查全文填充(爬网)错误Troubleshoot errors in a full-text population (crawl)

如果在爬网期间发生了错误,全文搜索的爬网日志功能会创建并维护一个爬网日志,该日志是一个纯文本文件。When an error occurs during a crawl, the Full-Text Search crawl logging facility creates and maintains a crawl log, which is a plain text file. 每个爬网日志都对应于某一个全文目录。Each crawl log corresponds to a particular full-text catalog. 默认情况下,给定实例(在此示例中为默认实例)的爬网日志文件位于 %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG 文件夹中。By default, crawl logs for a given instance (in this example, the default instance) are located in %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG folder.

爬网日志文件遵循以下命名方案:The crawl log file follows the following naming scheme:

SQLFT<DatabaseID><FullTextCatalogID>.LOG[<n>]

爬网日志文件名的可变部分如下。The variable parts of the crawl log file name are the following.

  • <DatabaseID> - 数据库的 ID。<DatabaseID> - The ID of a database. <dbid > 是一个带有前导零的五位数。<dbid> is a five digit number with leading zeros.
  • <FullTextCatalogID> - 全文目录 ID。<FullTextCatalogID> - Full-text catalog ID. <catid >是一个带有前导零的五位数。<catid> is a five digit number with leading zeros.
  • <n> 是一个整数,指示同一全文目录现有的一个或多个爬网日志。<n> - Is an integer that indicates one or more crawl logs of the same full-text catalog exist.

例如,SQLFT0000500008.2 是一个数据库 ID 为 5、全文目录 ID 为 8 的数据库爬网日志文件。For example, SQLFT0000500008.2 is the crawl log file for a database with database ID = 5, and full-text catalog ID = 8. 文件名结尾的 2 指示此数据库/目录对具有两个爬网日志文件。The 2 at the end of the file name indicates that there are two crawl log files for this database/catalog pair.

另请参阅See Also

sys.dm_fts_index_population (Transact-SQL) sys.dm_fts_index_population (Transact-SQL)
全文搜索入门 Get Started with Full-Text Search
创建和管理全文索引 Create and Manage Full-Text Indexes
CREATE FULLTEXT INDEX (Transact-SQL) CREATE FULLTEXT INDEX (Transact-SQL)
ALTER FULLTEXT INDEX (Transact-SQL)ALTER FULLTEXT INDEX (Transact-SQL)