sql全文索引

Question

本帖最后由 ly1577592799 于 2010-09-02 11:31:33 编辑

Answer 1

-- 回答是肯定滴（以下是SQL Server 2008下)
-- 全文搜索功能提供了基于字符和二进制数据的智能单词(和短语)搜索，使用全文搜索的执行效率会比普通的LIKE查询好很多。-- 现在SQL Server 2008中，全文搜索功能集成到数据库中了。全文目录不再单独存储在文件系统中，现在也集成到了数据库本身当中。
-- 全文索引和查询支持功能也不再依赖于SQL Server早期版本中的分离的MSFTESQL服务。-- 注解
-- SQL Server 2008也在数据库中全面集成非索引字(以前称为干扰词)，允许创建自己的非索引字表和相关非索引字。
-- 早期版本在数据库外部使用干扰词文件。我将在6.1.6节中讨论这个功能。-- 6.1 全文索引和全文目录 ( P183 )
-- 全文索引(full-text index)能让我们更灵活地搜索非结构化的文本数据，并且提供了比LIKE查询好很多的性能。与普通的B树聚集索引过程或非聚集索引不同的是，
-- 全文索引是由文本数据的索引标记组成的压缩的索引结构。标记是SQL Server在索引过程中标识的单词或字符串。使用特殊的全文搜索功能可以在字符模式之上扩展单词或短语搜索，
-- 并基于其他单词的变形词、同义词、通配符和近义词进行搜索。-- 全文目录可以不包含全文索引，也可以包含数量不等的全文索引，从SQL Server 2008开始，全文目录存储在数据库中。(在早期的版本中，它存储在SQL Server实例服务器的本地硬盘上。)
-- 全文目录可包含用于在单个数据库内索引一个或多个表的全文索引。-- SQL Server使用几个T-SQL命令来创建、修改和移除全文目录和全文索引对象，在随后的一组技巧中将会演示。-- 6.1.1 创建全文目录 ( P183 )
-- 创建全文目录的最简单形式是只提供全文目录的名字。然而还有其他一些选项，CREATE FULLTEXT CATALOG的扩展语法如下：
CREATE FULLTEXT CATALOG catalog_name
    [ON FILEGROUP 'filegroup']
    [IN PATH 'rootpath']
    [WITH ACCENT_SENSITIVITY = {ON|OFF}]
    [AS DEFAULT]
    [AUTHORIZATION owner_name ]------------------------------------------------------------------------------------------------------------------------
|                                      表6-1      CREATE FULLTEXT CATALOG参数                                          |
------------------------------------------------------------------------------------------------------------------------
|         参       数         |                            描                      述                                  |
------------------------------------------------------------------------------------------------------------------------
| catalog_name                | 这个选项指定了新的全文目录的名称                                                       |
------------------------------------------------------------------------------------------------------------------------
| filegroup                   | 这个参数指定全文目录存放的文件组。如果未指定，将使用数据库默认的文件组                 |
------------------------------------------------------------------------------------------------------------------------
| rootpath                    | 这是SQL Server 2008中废弃的选项，已不再使用                                            |
------------------------------------------------------------------------------------------------------------------------
| ACCENT_SENSITIVITY={ON|OFF} | 此选项用于选择在此全文目录中创建全文索引是否区分重音，                                 |
|                             | ACCENT_SENSITIVITY定义了SQL Server是否区分重音字符和非重音字符                         |
------------------------------------------------------------------------------------------------------------------------
| AS DEFAULT                  | 此选项将该全文目录定义为默认全文目录，                                                 |
|                             | 在数据库中创建的、未显式指定全文目录的全文索引将使用该全文目录                         |
------------------------------------------------------------------------------------------------------------------------
| owner_name                  | AUTHORIZATION选项决定了新的全文目录的所有者，                                          |
|                             | 可以选择一个数据库用户或者是角色作为owner_name                                         |
-------------------------------------------------------------------------------------------------------------------------- 在下面的第一个例子中，在AdventureWorks数据库中创建了一个新的全文目录(注意，一个全文目录只属于一个数据库)：
USE AdventureWorks
GO
CREATE FULLTEXT CATALOG cat_Production_Document-- 在第二个例子中，建立一个区分重音的全文目录：
USE AdventureWorks
GO
CREATE FULLTEXT CATALOG cat_Production_Document_EX2
WITH ACCENT_SENSITIVITY = ON-- 解析
-- 在这个技巧中，演示了如何使用CREATE FULLTEXT CATALOG命令创建一个全文目录。这个命令创建了一个可以用来聚合一个或更多全文索引的实例逻辑实体。-- 全文目录创建后，可以在它下面创建全文索引，这些内容将在下面的技巧中阐述。-- 6.1.2 创建全文索引 ( P184 )
-- 在这个技巧中，演示如何在表的列上创建全文索引。然后我们就能利用本章稍后会讲述的更灵活的搜索能力。-- 创建全文索引的命令是CREATE FULLTEXT INDEX，部分语法如下所示：
CREATE FULLTEXT INDEX ON table_name
    [ ( { column_name
          [ TYPE COLUMN type_column_name ]
          [ LANGUAGE language_term ]
        } [ ,...n ]
      ) ]
    KEY INDEX index_name
        [ ON fulltext_catalog_name]
        [ WITH [ ( ] <with_option> [ ,...n ] [ ) ] ]
[;]
<with_option>::=
  {
   CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }
   | STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
  }--------------------------------------------------------------------------------------------------------------------------
|                                      表6-2        CREATE FULLTEXT INDEX参数                                            |
--------------------------------------------------------------------------------------------------------------------------
|         参       数         |                              描                       述                                 |
--------------------------------------------------------------------------------------------------------------------------
| table_name                  | 这个参数指定了要创建全文索引的表的名称，一个表只允许有一个全文索引                       |
--------------------------------------------------------------------------------------------------------------------------
| column_name                 | 这个参数定义了要建立全文索引的一列或多列的名字，数据类型只能为varchar、nvarchar、char、  |
|                             | nchar、xml、varbinary、text、ntext、image                                                |
--------------------------------------------------------------------------------------------------------------------------
| type_column_name            | TYPE COLUMN关键字标记用来指定表中的列，告诉全文索引在varbinary(max)或image数据类型的列   |
|                             | 中保存了什么类型的数据。SQL Server可以解释不同的文件类型，但是它必须准确地知道如何处理   |
--------------------------------------------------------------------------------------------------------------------------
| language_term               | LANGUAGE为可行关键字，也可以用在列列表中用来指出列中数据的语言。指出语言能帮助SQL Server |
|                             | 确定在全文索引处理过程中如何进行数据解析及如何进行语言解释。可以在sys.fulltext_languages |
|                             | 表中查询有哪些可使用的语言                                                               |
--------------------------------------------------------------------------------------------------------------------------
| index_name                  | 为了能在某个表上建立全文索引，表必须包含一个唯一的单键非空列。                           |
|                             | 例如，一个单列主键或一个非空的且具有UNIQUE约束的列。                                     |
|                             | CREATE FULLTEXT INDEX命令的KEY INDEX子句标识指定表上所需的唯一键列                       |
--------------------------------------------------------------------------------------------------------------------------
| fulltext_catalog_name       | ON子句指定全文索引要存储的全文目录。如果在全文索引创建前设定过默认全文目录，而且没有使用 |
|                             | 该选项，那么该全文索引将创建在默认的全文目录中。                                         |
|                             | 但是，如果没有定义默认全文目录，全文索引的创建将失败                                     |
--------------------------------------------------------------------------------------------------------------------------
| CHANGE_TRACKING             | 这个参数决定全文服务如何监测到用户数据的变化。基于这个设置，全文索引可以随着表数据的更新 |
| {MANUAL | AUTO | OFF [,     | 而自动更新。也可以选择在某个所选择的时间点或者计划表手动更新全文索引。指定AUTO选项后，   |
| NO POPULATION]}             | 全文索引会随着表数据的修改而自动更新。MANUAL选项意味着，全文索引要通过用户手动传播或是通 |
|                             | 过SQL Server代理按计划传播。OFF选项意味着，SQL Server不保留对索引数据的更改。声明含      |
|                             | NO POPULATION的OFF选项意味着，全文索引建立之后不会被立即填充。在使用这个选项时，全文索引 |
|                             | 只有在用户执行ALTER FULLTEXT INDEX后才会被填充。这个内容会在下个技巧中阐述               |
--------------------------------------------------------------------------------------------------------------------------
| STOPLIST [ = ]{OFF |        | 非索引字包含非索引字的列表，它是可以被搜索忽略的字符串。默认选项是SYSTEM，意思是使用默认 |
| SYSTEM | stoplist_name}     | 系统非索引字表。当这个选项设为OFF时，不使用非索引字表。否则，指定stoplist_name来使用用户 |
|                             | 定义的非索引字表                                                                         |
--------------------------------------------------------------------------------------------------------------------------

Answer 2

-- 在这个技巧的示例中，我们在AdventureWorks数据库的Production.Document表上建立一个新的全文索引(我们将在后面的技巧中讲到如何查询这个全文索引)。
-- 全文索引的列是DocumentSummary，FileExtension是包含了指向列的文档类型的指针的列：
USE AdventureWorks
GOCREATE FULLTEXT INDEX ON Production.Document
(DocumentSummary, Document TYPE COLUMN FileExtension)
KEY INDEX PK_Document_DocumentID
ON cat_Production_Document
WITH CHANGE_TRACKING AUTO,
    STOPLIST = SYSTEM......-- 要记住，只有要进行全文索引的列类型是varchar(max)或image时，才有必要使用TYPE COLUMN子句来帮助SQL Server解释存储的数据，
-- 普通的文件数据类型(如char、varchar、nchar、nvarchar、text、ntext和xml)不需要使用TYPE COLUMN子句。-- 全文索引建立后，就可以查询它了。但是在查询之前，还需要知道其他修改或删除全文索引和全文目录的命令。-- 6.1.3 修改全文目录 ( P186 )
-- 在这个技巧中，会演示ALTER FULLTEXT CATALOG命令，可以用它来实现以下几种操作。
-- *1) 修改区分重音设置，区分重音定义了SQL Server在查询过程中区分重音及非重音字符还是同等对待这两类字符。
-- *2) 设置全文目录为默认的数据库目录。
-- *3) REBUILD (重新建立)其中有全部的索引和全文目录。
-- *4) REORGANIZE (重新组织)全文目录，这个操作可以优化内部索引和目录全文结构。这个过程被称为主合并，
--     意思是将那些细小的分散的全文索引物理(不是逻辑地)处理到一个大的完整的全文索引中，以提升性能。-- 下面是ALTER FULLTEXT CATALOG的语法：
ALTER FULLTEXT CATALOG catalog_name
{ REBUILD [WITH ACCENT_SENSITIVITY = {ON|OFF} ]
    | REORGANIZE
    | AS DEFAULT
}------------------------------------------------------------------------------------------------------------------------
|                                      表6-3       ALTER FULLTEXT CATALOG参数                                          |
------------------------------------------------------------------------------------------------------------------------
|            参         数            |                        描                      述                              |
------------------------------------------------------------------------------------------------------------------------
| REBUILD                             | REBUILD选项会重生成目录                                                        |
------------------------------------------------------------------------------------------------------------------------
| [WITH ACCENT_SENSITIVITY = {ON|OFF} | ACCENT_SENSITIVITY选项必须要和REBUILD选项同时使用                              |
------------------------------------------------------------------------------------------------------------------------
| REORGANIZE                          | 这个选项可以使SQL Server优化目录结构和内部索引                                 |
------------------------------------------------------------------------------------------------------------------------
| AS DEFAULT                          | 这个选项将全文目录设置为默认的数据库目录                                       |
-------------------------------------------------------------------------------------------------------------------------- 在技巧的第一个例子中，用REORGANIZE关键字来优化全文目录：
ALTER FULLTEXT CATALOG cat_Production_Document
REORGANIZE-- 第二个例子中，将全文目录设置为数据库的默认全文目录：
ALTER FULLTEXT CATALOG cat_Production_Document
AS DEFAULT-- 在下面的例子中，重建了一个禁用区分重音的全文目录(和其中的全部索引)。
ALTER FULLTEXT CATALOG cat_Production_Document
REBUILD WITH ACCENT_SENSITIVITY = OFF-- 解析
-- 在这个技巧中，用ALTER FULLTEXT CATALOG优化全文索引及内部数据结构，将全文目录设置为数据库默认的全文目录，并重建全文目录和其中的索引。
-- 这个命令用来管理已建立的全文目录，保证索引后的表在数据修改时能有最佳的性能。-- 6.1.4 修改全文索引 ( P187 )
-- ALTER FULLTEXT INDEX命令用来改变全文索引的属性，也可以控制/启动全文索引的填充。语法如下：
ALTER FULLTEXT INDEX ON table_name
  { ENABLE
  | DISABLE
  | SET CHANGE_TRACKING { MANUAL | AUTO | OFF }
  | ADD ( column_name
    [ TYPE COLUMN type_column_name ]
    [ LANGUAGE language_term ] [,...n] )
    [ WITH NO POPULATION ]
  | DROP ( column_name [,...n] )
    [ WITH NO POPULATION ]
  | DROP ( column_name [,...n] )
    [WITH NO POPULATION ]
  | START { FULL | INCREMENTAL | UPDATE } POPULATION
  | {STOP | PAUSE | RESUME } POPULATION
  | SET STOPLIST { OFF| SYSTEM | stoplist_name }
    [WITH NO POPULATION] }------------------------------------------------------------------------------------------------------------------------
|                                      表6-4         ALTER FULLTEXT INDEX参数                                          |
------------------------------------------------------------------------------------------------------------------------
|            参         数            |                        描                      述                              |
------------------------------------------------------------------------------------------------------------------------
| table_name                          | 这个参数指定了要修改的全文索引所在的表名                                       |
------------------------------------------------------------------------------------------------------------------------
| ENABLE | DISABLE                    | ENABLE选项激活全文索引功能。DISABLE选项关闭全文索引功能。关闭全文索引功能意味  |
|                                     | 着以后对表字段的任何修改都不会被跟踪并体现在全文索引上                         |
|                                     | (即使DISABLE了，全文索引查询条件仍然有效)                                      |
------------------------------------------------------------------------------------------------------------------------
| SET CHANGE TRACKING                 | MANUAL指定源索引数据的更改跟踪是按计划启用还是由用户手动进行。AUTO指定在修改索 |
| {MANUAL|AUTO|OFF}                   | 引列中的数据时，SQL Server将自动更新全文索引。OFF指定关闭跟踪全文索引中的修改  |
------------------------------------------------------------------------------------------------------------------------
| ADD (column_name [, ...n] )         | 这个参数表示添加到既有全文索引中的一个或多个列的名称                           |
------------------------------------------------------------------------------------------------------------------------
| type_column_name                    | 这个选项指定了用于指定在varbinary(max)和image数据类型列中保存的数据的全文索引  |
|                                     | 文件类型的列                                                                   |
------------------------------------------------------------------------------------------------------------------------
| language_term                       | 这个参数表示在列列表中使用的可选LANGUAGE关键字，指定了列中保存数据的语言       |
------------------------------------------------------------------------------------------------------------------------
| WITH NO POPULATION                  | 如果指定这个参数，当添加或删除表的列后不填充全文索引                           |
------------------------------------------------------------------------------------------------------------------------
| DROP (column_name [,...n] )         | 这个参数指定了要从既有全文索引中移除的列的名字                                 |
------------------------------------------------------------------------------------------------------------------------
| START {FULL|INCREMENTAL|UPDATE}     | 这个选项指示全文索引根据FULL、INCREMENTAL和UPDATE来填充。FULL刷新表的每一行的  |
| POPULATION                          | 索引。INCREMENTAL只刷新那些自上次填充后修改的行。仅当表包含一个类型为timestamp |
|                                     | 的列时，才能应用 INCREMENTAL。UPDATE刷新自上次索引更新以来所有插入、           |
|                                     | 更新和删除的行的索引                                                           |
-------------------------------------------------------------------------------------------------------------------------- 在第一个例子中，我们将增加一个新的列到Production.Document表的全文索引中：
ALTER FULLTEXT INDEX ON Production.Document
ADD (Title)-- 下一步，启动一个全文索引填充操作：
ALTER FULLTEXT INDEX ON Production.Document
START FULL POPULATION-- 因为全文索引填充操作正在进行(在增加新的列到全文索引操作中我们没有指定WITH NO POPULATION选项)，该操作将会返回一个警告：-----------------------------------------------------------------------------------------
警告: 启动对表或索引视图 'Production.Document' 的全文索引填充的请求已被忽略，因为此表或索引视图当前已有活动的填充。
------------------------------------------------------------------------------------------- 在下面的例子中，将关闭表的全文索引的更改跟踪：
ALTER FULLTEXT INDEX ON Production.Document
SET CHANGE_TRACKING OFF-----------------------------------------------------------------------------------------
警告: 停止更改跟踪的请求已删除了表或索引视图 'Production' 上所有跟踪到的更改。
------------------------------------------------------------------------------------------- 在本技巧最后的例子中，我们将Title列从全文索引中删除：
ALTER FULLTEXT INDEX ON Production.Document
DROP (Title)-- 解析
-- 在这个技巧中，我们使用ALTER FULLTEXT INDEX命令来完成以下操作。
-- *1) 在既有的全文索引中增加一个新列。如果增加这个列能提升全文搜索性能，那么这个操作非常有用。
-- *2) 启动全文索引填充(在填充没有设置为自动更新的情况下可以这样做)。在超大型表中，你可能会希望手动控制全文索引填充而不用SQL Server随着时间手动填充。
-- *3) 关闭更改跟踪，这将移除发生在索引数据上的所有更改的日志。
-- *4) 从全文索引中删除一个列，例如，如果既有的一个全文索引中存在一个并不能提升性能的列，最好的做法就是把它从全文索引中删除，以节省更多的空间
--     (即这个全文索引数据占用的空间)和系统资源(即更新这个数据SQL Server消耗的资源)。-- 还有其他一些ALTER FULLTEXT INDEX可完成的操作，包括通过DISABLE选项关闭已激活的全文索引，这将禁用全文索引(但这些全文索引的元数据还在系统表中)。
-- 当然，也可以通过ENABLE选项激活已关闭的全文索引。

Answer 3

-- 6.1.5 检索全文目录和索引元数据 ( P189 )
-- 这个技巧展示如何使用系统目录视图在数据库中检索关于全文目录和索引的有用的信息。
-- sys.fulltext_catalogs系统目录视图返回当前数据库中全文目录的信息。例如：
SELECT name, path, is_default, is_accent_sensitivity_on
FROM sys.fulltext_catalogs-- 这个查询返回：
-----------------------------------------------------------------------------------------
name path is_default is_accent_sensitivity_on
cat_Production_Document NULL 1 0
cat_Production_Document_EX2 NULL 0 1
------------------------------------------------------------------------------------------- sys.fulltext_indexes系统目录视图列出了数据库中所有的全文索引。例如：
SELECT object_name(object_id) table_name,
       change_tracking_state_desc, stoplist_id
  FROM sys.fulltext_indexes-- 这个查询返回：
-----------------------------------------------------------------------------------------
table_name change_tracking_state_desc stoplist_id
Document OFF 0
------------------------------------------------------------------------------------------- sys.fulltext_index_columns系统目录视图列出了数据库中所有的全文索引的列。例如：
SELECT object_name(ic.object_id) tblname, c.name
FROM sys.fulltext_index_columns ic
INNER JOIN sys.columns c ON
  ic.object_id = c.object_id AND
  ic.column_id = c.column_id-- 这个查询返回表名和索引列的名称：
-----------------------------------------------------------------------------------------
tblname name
Document DocumentSummary
Document Document
------------------------------------------------------------------------------------------- 也可以使用FULLTEXTCATALOGPROPERTY系统函数来返回指定目录的信息。语法如下所示：
FULLTEXTCATALOGPROPERTY('catalog_name','property')-- 该函数有两个参数，目录的名称和要评估的属性的名称。一些比较有用的属性选项在表6-5中有描述。------------------------------------------------------------------------------------------------------------------------
|                                      表6-5      FULLTEXTCATALOGPROPERTY参数                                          |
------------------------------------------------------------------------------------------------------------------------
|            参         数            |                        描                      述                              |
------------------------------------------------------------------------------------------------------------------------
| AccentSensitivity                   | 返回1表示区分重音，0为不区分重音                                               |
------------------------------------------------------------------------------------------------------------------------
| IndexSize                           | 以MB返回全文目录的大小                                                         |
------------------------------------------------------------------------------------------------------------------------
| MergeStatus                         | 当进行重组时返回1，未进行重组时返回0                                           |
------------------------------------------------------------------------------------------------------------------------
| PopulateStatus                      | 返回数字值表示目录当前的填充状态----例如，0为空闲，1为正在进行填充，2为暂停，  |
|                                     | 7为正在生成索引，8为磁盘满                                                     |
-------------------------------------------------------------------------------------------------------------------------- 在这个示例中，返回全文目录的填充状态：
SELECT FULLTEXTCATALOGPROPERTY ('cat_Production_Document','PopulateStatus') PoplateStatus-- 这个查询返回0，表示空间
-----------------------------------------------------------------------------------------
PoplateStatus
0
------------------------------------------------------------------------------------------- 解析
- 这个技巧使用了三个不同的目录视图和系统函数来返回当前数据库中全文目录和全文索引的信息。为了跟踪它们的存在以及跟踪活动和设置的当前状态，你会需要这些信息。