索引和全文索引

创建索引的指导原则
• 适合创建索引情况： 适合创建索引情况： 创建索引情况
–该列频繁用于进行搜索。 该列频繁用于进行搜索 该列频繁用于进行搜索。 –该列用于对数据进行排序。 该列用于对数据进行排序 该列用于对
• 不适合创建索引情况： 不适合创建索引情况：
–列中仅包含几个不同的值。 列 仅包含几个不同的值 几个不同的值。 –表中仅包含几行。 表 仅包含几行。
数据。 数据。
SQL Server 关系数据库
创建组合索引的指导原则
•当需要频繁地将两列或多列作为一个整 当需要频繁地将两列或多列作为一个整 搜索时，可以创建组合索引。 体进行 搜索时，可以创建组合索引。 •创建组合索引时先列出唯一性最好的列。 创建组合索引时先列出唯一性最好的列。 创建组合索引时先列出唯一性最好的列 •组合索引中列的顺序和数量会影响查询 组合索引中列的顺序和数量会影响查询 的性能。 的性能。
SQL Server 关系数据库
在查询强制使用中索引
• SQL Server使用查询优化器选择执行查询的最 使用查询优化器选择执行查询的最
包括选择要使用的索引， 佳方式 ，包括选择要使用的索引，查询优化器 为标准， 以页I/O为标准，尝试选出成本最低的方法 。 • SQL Server中可以强制查询优化器选择某个特定 的索引。 的索引。 •制查询优化器选择某个特定的索引。 制查询优化器选择某个特定的索引。 语法
SQL Server 关系数据库
查看索引
Fra Baidu bibliotek
•创建索引后，可能需要查找有关索引的信息，例 创建索引后，可能需要查 有关索引的信息， 索引的信息
如查看在某个表上创建的索引的列表或者表中 的索引列。 的索引列。 •系统存储过程 sp_helpindex 报告 有关 表的索引 的 报告有关 表的索引的 有关表的索引 信息。 信息。 语法： 语法： sp_helpindex <Table_name>
SQL Server 关系数据库
创建索引2 创建索引2-2
• CREATE INDEX 语句的语法为： 语句的语法为：
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name(column_name[, column_name]…) [WITH [PAD_INDEX] [[,]FILLFACTOR=x] [[,]DROP_EXISTING] ]
使用全文索引
sp_fulltext_catalog创建或者删除一个全文目录， 创建或者删除一个全文目录， 创建或者删除一个全文目录 启动或者停止一个全文目录的索引操作 --创建一个全文目录 创建一个全文目录 EXEC sp_fulltext_catalog 'FT_pubs', 'create', 'F:\Microsoft SQL Server\MSSQL\FTDATA' --对全文目录进行填充 对全文目录进行填充 EXEC sp_fulltext_catalog 'FT_pubs', 'start_full'
•如果索引非常大，系统管理员也可以将目录分割 如果索引非常大，
成多个目录。 成多个目录。
SQL Server 关系数据库
使用全文索引
•可以在企业管理器中使用全文索引向导创 可以在企业管理器中使用全文索引向导创 建全文索引。 建全文索引。 •创建全文索引后，就可以对创建了该索引 创建全文索引后， 创建全文索引后 的表进行全文查询。 的表进行全文查询。 •全文查询使用 全文查询使用CONTAINS和FREETEXT这两个 全文查询使用 和 这两个 谓词。 谓词。
例子
--建立全文索引 建立全文索引 EXEC sp_fulltext_table titles, 'activate' --填充全文索引目录 填充全文索引目录 EXEC sp_fulltext_catalog 'FT_pubs', 'start_full'
SQL Server 关系数据库
例子
--建立全文目录 建立全文目录FT_pubs 建立全文目录 EXEC sp_fulltext_catalog 'FT_pubs', 'create' --为titles表建立全文索引数据元 为 表建立全文索引数据元 EXEC sp_fulltext_table titles, 'create', 'FT_pubs', 'UPKCL_titleidind'
SQL Server 关系数据库
Sysindexes表 Sysindexes表
数据库中的每个索引和表在表中各占一行 该表存储在每个数据库中 表 indid字段表示索引 ID 索引
1 = 聚集索引 >1 = 非聚集 255 = 具有 text 或 image 数据的表条目
SQL Server 关系数据库
SQL Server 关系数据库
全文搜索
•SQL Server使用全文索引执行全文搜索。 使用全文索引执行全文搜索。 使用全文索引执行全文搜索 •可以在无结构文本上建立索引，以便能够在 可以在无结构文本上建立索引， 可以在无结构文本上建立索引 中搜索特定的条目。 文本 中搜索特定的条目。 •该功能是通过 该功能是通过Microsoft搜索服务提供的。使 搜索服务提供的。 该功能是通过 搜索服务提供的 搜索服务， 用Microsoft搜索服务，可以用语言搜索条件实 搜索服务 现复杂的搜索。 现复杂的搜索。 •语言搜索可用于搜索单词或短语、相邻的几 语言搜索可用于搜索单词或短语、 语言搜索可用于搜索单词或短语 个目标词和不同形式的词语。 个目标词和不同形式的词语。
SQL Server 关系数据库
使用全文索引
--设置全文搜索属性 设置全文搜索属性 --设置时间超时为 秒 设置时间超时为60秒 设置时间超时为 EXEC sp_fulltext_service 'connect_timeout', 15 --前提条件 前提条件 启动Microsoft Search服务 启动 服务 启动方法：服务管理器（图形界面） 启动方法：服务管理器（图形界面） c:\net start mssearch（命令行） （命令行）
SQL Server 关系数据库
例子
--设置全文索引列名 设置全文索引列名 EXEC sp_fulltext_column 'titles', 'title', 'add' GO EXEC sp_fulltext_column 'titles', 'notes', 'add'
SQL Server 关系数据库
SQL Server 关系数据库
例子
--启动 启动Microsoft Search服务 启动 服务 C:\net start mssearch --打开数据库 打开数据库 USE pubs GO
SQL Server 关系数据库
例子
--判断 判断pubs库目前是否支持全文索引，如果 库目前是否支持全文索引， 判断 库目前是否支持全文索引 不支持， 不支持，则使用存储过程 sp_fulltext_database打开全文索引的功能 打开全文索引的功能 if(SELECT DatabaseProperty('pubs', 'IsFullTextEnabled')) = 0 EXEC sp_fulltext_database 'enable'
SQL Server 关系数据库
使用全文索引
sp_fulltext_column指定全文索引的表中那些 指定全文索引的表中那些 列参加全文索引 USE pubs GO EXEC sp_fulltext_column 'titles', 'title', 'add' GO EXEC sp_fulltext_column 'titles', 'notes', 'add'
数据库中删除，以腾出空间 数据库中删除， • DROP INDEX语句不适用于用PRIMARY KEY或 UNIQUE 约束创建的索引 • DROP INDEX也不能用于删除系统表的索引 语法： 语法：
DROP INDEX table_name.index_name
示例： 示例：
DROP INDEX sales.NCLINDX_ordnum
SQL Server 关系数据库
创建索引1-2 创建索引
• CREATE INDEX 语句用于为给定的表创建 索引。 索引。 •该语句通过修改表的物理顺序，或者向 该语句通过修改表的物理顺序， 该语句通过修改表的物理顺序 查询优化器提供表的一个逻辑顺序以提高 查询效率。 查询效率。 •只有表的所有者能为表创建索引。 只有表的所有者能为表创建索引。 只有表的所有者能为表创建索引
(INDEX=index_name)
示例
SELECT * FROM sales (INDEX =nclindx_ordnum) WHERE ord_num = 'P3087a'
SQL Server 关系数据库
删除索引
•使用DROP INDEX语句可以将不需要的索引从 语句可以将不需要 需要的索引从
SQL Server 关系数据库
第六章
索引和全文索引
SQL Server 关系数据库
目标
• 索引的含义 • 创建和删除索引 • 全文索引 • 全文检索
SQL Server 关系数据库
索引概念
索引是对数据库表中一个或多个列的值进行 排序的结构 索引提供指针以指向存储在表中指定列的数 据值， 据值，然后根据指定的排序次序排列这些指 针
全文索引。 全文索引。
SQL Server 关系数据库
全文目录
•所有的全文索引都存储在全文目录中。 所有的全文索引都存储在全文目录中。 •全文目录只能通过 Windows 和搜索服务查看。 全文目录只能通过 和搜索服务查看。 •默认情况下，一个数据库的所有全文索引都存放 默认情况下，
在一个全文目录中。 在一个全文目录中。
SQL Server 关系数据库
创建唯一索引的指导原则
可以确保实体完整性。 可以确保实体完整性。
•由于每一行都包含一个唯一的值，唯一索引 由于每一行都包含一个唯一的值， •键列中的NULL 值也算作唯一值。 值也算作唯一值。 •不能在包含重复数据的列上创建唯一索引。 不能在包含重复数据的列上创建唯一索引。 •在创建唯一索引前必须更新或删除重复的 在创建唯一索引前必须更新或删除重复的
SQL Server 关系数据库
索引优点缺点
优点
– 加快访问速度 – 加强行的唯一性
缺点
– 带索引的表在数据库中需要更多的存储空间。 带索引的表在数据库中需要更多的存储空间。 – 操纵数据的命令需要更长的处理时间，因为 操纵数据的命令需要更长的处理时间， 它们需要对索引进行更新。 它们需要对索引进行更新。
SQL Server 关系数据库
使用全文索引
sp_fulltext_database初始化全文索引或者删 初始化全文索引或者删 除数据库中的所有全文索引 --初始化全文索引 初始化全文索引 USE pubs EXEC sp_fulltext_database 'enable'
SQL Server 关系数据库
SQL Server 关系数据库
全文索引
• 全文索引跟踪表中的重要词语。 全文索引跟踪表中的重要词语。 • 该索引用于包含索引词的搜索和高级搜索选项， 该索引用于包含索引词的搜索和高级搜索选项，
如短语搜索。 如短语搜索。
• 全文索引需要一个包含唯一键值的数据列。 全文索引需要一个包含唯一键值的数据列。 • 添加、修改或删除表中数据时，必须手动更新 添加、修改或删除表中数据时 或删除表中数据时，
SQL Server 关系数据库
使用全文索引
sp_fulltext_table将表标识为全文索引表或者非全 将表标识为全文索引表或者非全 文索引表 --将表 将表titles标识为全文索引表 将表 标识为全文索引表 EXEC sp_fulltext_table titles, 'create', 'FT_pubs', 'UPKCL_titleidind' --激活表的全文索引 备注：需要在指定参加全文 激活表的全文索引(备注 激活表的全文索引 备注： 索引的列之后才可以进行此项操作，见下页) 索引的列之后才可以进行此项操作，见下页 EXEC sp_fulltext_table titles, 'activate'