北大青鸟 SQLA (6).ppt

1、第六章,索引和全文索引,2,回顾,子查询 联合查询 DISTINCT 子句 COMPUTE BY SELECT INTO,3,目标,索引的含义 创建和删除索引 全文索引 全文检索,4,索引概念,索引是对数据库表中一个或多个列的值进行排序的结构 索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针,5,索引优点缺点,优点 加快访问速度 加强行的唯一性 缺点 带索引的表在数据库中需要更多的存储空间。 操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新。,6,索引分类,表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引 非聚集索引指定表的逻辑顺

2、序。,7,Sysindexes表,数据库中的每个索引和表在表中各占一行 该表存储在每个数据库中 表 indid字段表示索引 ID 1 = 聚集索引1 = 非聚集255 = 具有 text 或 image 数据的表条目,8,查询示例不使用索引进行查询,sysindexes,IAM页,Heap,页 127,页128,页129,页130,9,查询示例使用非聚集索引进行查询,sysindexes,Martin,SELECT lastname, firstname FROM member WHERE lastname BETWEEN Masters AND Rudd,10,SELECT lastname

3、, firstname FROM member WHERE lastname = Ota,使用聚集索引进行查询,Martin,11,适合创建索引情况： 该列频繁用于进行搜索。 该列用于对数据进行排序。 不适合创建索引情况： 列中仅包含几个不同的值。 表中仅包含几行。,创建索引的指导原则,12,创建索引1-2,CREATE INDEX 语句用于为给定的表创建索引。 该语句通过修改表的物理顺序，或者向查询优化器提供表的一个逻辑顺序以提高查询效率。 只有表的所有者能为表创建索引。,13,CREATE INDEX 语句的语法为：,创建索引2-2,CREATE UNIQUE CLUSTERED|NONC

4、LUSTERED INDEX index_name ON table_name(column_name, column_name) WITH PAD_INDEX ,FILLFACTOR=x ,DROP_EXISTING ,14,填充因子1-3,创建聚集索引后，表中数据会按照索引列的值的顺序存储在数据页中。 表中添加了新记录或索引列的值发生修改时，SQL Server 需要重新组织数据存储模式，以便为新记录腾出空间并维持数据的有序存储。 向已满的索引页中添加新行时，SQL Server 会将大约一半的行移到新的一页上，以便为新行腾出空间，从而导致页拆分。,15,填充因子2-3,页拆分会降低性能，

5、且割裂了表中数据的存储。 填充因子是在创建索引时指定的，它用来在索引的各个页上预留一定比例的可用空间，以供将来进行数据扩充时使用。 填充因子指定了创建索引后要在页中输入数据的量。,16,填充因子3-3,如果无须再修改数据，可以指定填充因子100，这样将完全占满索引页，因而所占用的存储空间最少。 如果需要频繁地修改表中的数据，则应指定一个较低的填充因子，以便在数据页中保留更多的空白空间 。,17,聚集索引决定数据的物理存储顺序。 一个表只能有一个聚集索引。 索引可以包含多个数据列。 示例：,创建聚集索引,CREATE CLUSTERED INDEX CLINDX_titleid ON roysc

6、hed (title_id),18,创建聚集索引的指导原则,选择唯一值比例高的数据列，一般应选择主键列 先创建聚集索引，再创建非聚集索引 在创建聚集索引时使用FILLFACTOR选项以确保在插入新行时有足够的可用空间。,19,非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向 数据存储位置的指针。 一个表可以有多个（最多249个）非聚集索引。 示例：,创建非聚集索引,CREATE NONCLUSTERED INDEX NCLINDX_ordnum ON sales (ord_num),20,除非绝对必要，否则请不要添加非聚集索引，这是因为非聚集索引需要额外的维

7、护开销 在频繁访问的列上建立索引 默认情况下，CREATEINDEX语句创建的是非聚集索引 在相异值多的数据列上创建非聚集索引，如名和姓的组合项,创建非聚集索引的指导原则,21,聚集索引和非聚集索引可以创建为唯一索引或组合索引。 唯一索引不允许索引列中存在重复的值。 组合索引允许在创建索引时使用两列或更多的列。,索引的特性,22,由于每一行都包含一个唯一的值，唯一索引可以确保实体完整性。 键列中的NULL 值也算作唯一值。 不能在包含重复数据的列上创建唯一索引。 在创建唯一索引前必须更新或删除重复的数据。,创建唯一索引的指导原则,23,创建组合索引的指导原则,当需要频繁地将两列或多列作为一个整

8、体进行 搜索时，可以创建组合索引。 创建组合索引时先列出唯一性最好的列。 组合索引中列的顺序和数量会影响查询的性能。,24,创建索引后，可能需要查找有关索引的信息，例如查看在某个表上创建的索引的列表或者表中的索引列。 系统存储过程sp_helpindex报告有关表的索引的信息。 语法：,查看索引,sp_helpindex ,25,SQL Server使用查询优化器选择执行查询的最佳方式 ，包括选择要使用的索引，查询优化器以页I/O为标准，尝试选出成本最低的方法 。 SQL Server中可以强制查询优化器选择某个特定的索引。 制查询优化器选择某个特定的索引。 语法 (INDEX=index_n

9、ame),SELECT * FROM sales (INDEX =nclindx_ordnum) WHERE ord_num = P3087a,在查询强制使用中索引,示例,26,使用DROP INDEX语句可以将不需要的索引从 数据库中删除，以腾出空间 DROP INDEX语句不适用于用PRIMARY KEY或 UNIQUE 约束创建的索引 DROP INDEX也不能用于删除系统表的索引 语法： DROP INDEX table_name.index_name 示例：,删除索引,DROP INDEX sales.NCLINDX_ordnum,27,全文搜索,SQL Server使用全文索引执行

10、全文搜索。 可以在无结构文本上建立索引，以便能够在文本 中搜索特定的条目。 该功能是通过Microsoft搜索服务提供的。使用Microsoft搜索服务，可以用语言搜索条件实现复杂的搜索。 语言搜索可用于搜索单词或短语、相邻的几个目标词和不同形式的词语。,28,全文索引跟踪表中的重要词语。 该索引用于包含索引词的搜索和高级搜索选项，如短语搜索。 全文索引需要一个包含唯一键值的数据列。 添加、修改或删除表中数据时，必须手动更新全文索引。,全文索引,29,所有的全文索引都存储在全文目录中。 全文目录只能通过 Windows 和搜索服务查看。 默认情况下，一个数据库的所有全文索引都存放在一个全文目录

11、中。 如果索引非常大，系统管理员也可以将目录分割成多个目录。,全文目录,30,使用全文索引,可以在企业管理器中使用全文索引向导创建全文索引。 创建全文索引后，就可以对创建了该索引的表进行全文查询。 全文查询使用CONTAINS和FREETEXT这两个谓词。,31,搜索包含基于字符的数据类型的列，查找： 单个词语或短语的匹配项 特定距离内的词语或 加权匹配项 搜索不区分大小写。 示例,SELECT title FROM titles WHERE CONTAINS(title,computer or cooking or silicon),CONTAINS 谓词,32,FREETEXT关键字在列中搜索与搜索条件中的词语意义相匹配但形式却并不完全匹配的值 搜索字符串被分割成多个搜索短词，然后再查找