SQLServer系列教程.ppt

第三章 索引和全文索引 2 回顾 子查询联合查询DISTINCT子句COMPUTEBYSELECTINTO 3 目标 索引的含义创建和删除索引全文索引全文检索 4 索引概念 索引是对数据库表中一个或多个列的值进行排序的结构索引提供指针以指向存储在表中指定列的数据值 然后根据指定的排序次序排列这些指针 5 索引优点缺点 优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间 操纵数据的命令需要更长的处理时间 因为它们需要对索引进行更新 6 索引分类 表中各行的物理顺序与键值的逻辑 索引 顺序相同 表只能包含一个聚集索引非聚集索引指定表的逻辑顺序 7 Sysindexes表 数据库中的每个索引和表在表中各占一行该表存储在每个数据库中表indid字段表示索引ID1 聚集索引 1 非聚集255 具有text或image数据的表条目 8 查询示例 不使用索引进行查询 sysindexes IAM页 Heap 页127 页128 页129 页130 9 查询示例 使用非聚集索引进行查询 sysindexes Martin SELECTlastname firstnameFROMmemberWHERElastnameBETWEEN Masters AND Rudd 10 SELECTlastname firstnameFROMmemberWHERElastname Ota 使用聚集索引进行查询 Martin 11 适合创建索引情况 该列频繁用于进行搜索 该列用于对数据进行排序 不适合创建索引情况 列中仅包含几个不同的值 表中仅包含几行 创建索引的指导原则 12 创建索引1 2 CREATEINDEX语句用于为给定的表创建索引 该语句通过修改表的物理顺序 或者向查询优化器提供表的一个逻辑顺序以提高查询效率 只有表的所有者能为表创建索引 13 CREATEINDEX语句的语法为 创建索引2 2 CREATE UNIQUE CLUSTERED NONCLUSTERED INDEXindex nameONtable name column name column name WITH PAD INDEX FILLFACTOR x DROP EXISTING 14 填充因子1 3 创建聚集索引后 表中数据会按照索引列的值的顺序存储在数据页中 表中添加了新记录或索引列的值发生修改时 SQLServer需要重新组织数据存储模式 以便为新记录腾出空间并维持数据的有序存储 向已满的索引页中添加新行时 SQLServer会将大约一半的行移到新的一页上 以便为新行腾出空间 从而导致页拆分 15 填充因子2 3 页拆分会降低性能 且割裂了表中数据的存储 填充因子是在创建索引时指定的 它用来在索引的各个页上预留一定比例的可用空间 以供将来进行数据扩充时使用 填充因子指定了创建索引后要在页中输入数据的量 16 填充因子3 3 如果无须再修改数据 可以指定填充因子100 这样将完全占满索引页 因而所占用的存储空间最少 如果需要频繁地修改表中的数据 则应指定一个较低的填充因子 以便在数据页中保留更多的空白空间 17 聚集索引决定数据的物理存储顺序 一个表只能有一个聚集索引 索引可以包含多个数据列 示例 创建聚集索引 CREATECLUSTEREDINDEXCLINDX titleidONroysched title id 18 创建聚集索引的指导原则 选择唯一值比例高的数据列 一般应选择主键列先创建聚集索引 再创建非聚集索引在创建聚集索引时使用FILLFACTOR选项以确保在插入新行时有足够的可用空间 19 非聚集索引指定表的逻辑顺序 数据存储在一个位置 索引存储在另一个位置 索引中包含指向数据存储位置的指针 一个表可以有多个 最多249个 非聚集索引 示例 创建非聚集索引 CREATENONCLUSTEREDINDEXNCLINDX ordnumONsales ord num 20 除非绝对必要 否则请不要添加非聚集索引 这是因为非聚集索引需要额外的维护开销在频繁访问的列上建立索引默认情况下 CREATEINDEX语句创建的是非聚集索引在相异值多的数据列上创建非聚集索引 如名和姓的组合项 创建非聚集索引的指导原则 21 聚集索引和非聚集索引可以创建为唯一索引或组合索引 唯一索引不允许索引列中存在重复的值 组合索引允许在创建索引时使用两列或更多的列 索引的特性 22 由于每一行都包含一个唯一的值 唯一索引可以确保实体完整性 键列中的NULL值也算作唯一值 不能在包含重复数据的列上创建唯一索引 在创建唯一索引前必须更新或删除重复的数据 创建唯一索引的指导原则 23 创建组合索引的指导原则 当需要频繁地将两列或多列作为一个整体进行搜索时 可以创建组合索引 创建组合索引时先列出唯一性最好的列 组合索引中列的顺序和数量会影响查询的性能 24 创建索引后 可能需要查找有关索引的信息 例如查看在某个表上创建的索引的列表或者表中的索引列 系统存储过程sp helpindex报告有关表的索引的信息 语法 查看索引 sp helpindex 25 SQLServer使用查询优化器选择执行查询的最佳方式 包括选择要使用的索引 查询优化器以页I O为标准 尝试选出成本最低的方法 SQLServer中可以强制查询优化器选择某个特定的索引 制查询优化器选择某个特定的索引 语法 INDEX index name SELECT FROMsales INDEX nclindx ordnum WHEREord num P3087a 在查询强制使用中索引 示例 26 使用DROPINDEX语句可以将不需要的索引从数据库中删除 以腾出空间DROPINDEX语句不适用于用PRIMARYKEY或UNIQUE约束创建的索引DROPINDEX也不能用于删除系统表的索引语法 DROPINDEXtable name index name示例 删除索引 DROPINDEXsales NCLINDX ordnum 27 全文搜索 SQLServer使用全文索引执行全文搜索 可以在无结构文本上建立索引 以便能够在文本中搜索特定的条目 该功能是通过Microsoft搜索服务提供的 使用Microsoft搜索服务 可以用语言搜索条件实现复杂的搜索 语言搜索可用于搜索单词或短语 相邻的几个目标词和不同形式的词语 28 全文索引跟踪表中的重要词语 该索引用于包含索引词的搜索和高级搜索选项 如短语搜索 全文索引需要一个包含唯一键值的数据列 添加 修改或删除表中数据时 必须手动更新全文索引 全文索引 29 所有的全文索引都存储在全文目录中 全文目录只能通过Windows和搜索服务查看 默认情况下 一个数据库的所有全文索引都存放在一个全文目录中 如果索引非常大 系统管理员也可以将目录分割成多个目录 全文目录 30 使用全文索引 可以在企业管理器中使用全文索引向导创建全文索引 创建全文索引后 就可以对创建了该索引的表进行全文查询 全文查询使用CONTAINS和FREETEXT这两个谓词 31 搜索包含基于字符的数据类型的列 查找 单个词语或短语的匹配项特定距离内的词语或加权匹配项搜索不区分大小写 示例 SELECTtitleFROMtitlesWHERECONTAINS title computer or cooking or silicon CONTAINS谓词 32 FREETEXT关键字在列中搜索与搜索条件中的词语意义相匹配但形式却并不完全匹配的值搜索字符串被分割成多个搜索短词 然后再查找匹配项搜索字符串可以是一组单词或短语 甚至可以是一个完整的句子示例 SELECTtitleFROMtitlesWHEREFREETEXT title computercookingandsilicon FREETEXT谓词 33 索引的概念索引的优缺点聚集索引非聚集索引唯一索引组合索引全文索引全文搜索 总结