《数据库原理与应用》09.索引的创建和使用.ppt
孙 发 勤 扬州大学教育科学与技术学院Y,数据库原理与应用第九讲 索引的创建和使用,本章的学习要点:一.索引的概念和优点二.索引的分类三.创建索引的方法四.对索引进行管理,索引概述,一.索引的概念:索引是由表中的一列或若干列字段值以及相应的指向表中物理标识这些值的数据页的逻辑指针构成的.,索引概述,一.索引的优点:使用索引对数据进行各种操作可以极大的提高系统性能,尤其是在数据查询方面.1.利用索引的数据查询,索引概述,2.不利用索引的数据查询（直接查找）系统直接在数据表中查询，从表的第一条记录开始，依次扫描比较数据表中的所有记录，直到找到所需的记录或扫描完数据表的所有记录。,索引概述,二.索引的缺点:1.创建的索引文件将要占用一定容量的存储 空间.2.当每次对数据表进行更新时,数据库还要执 行额外的操作来维护索引.降低对数据表的更 新速度.总结:利用索引可以提高对表的查询速度,但是会降低对表的更新速度,所以过多的索引不一定能提高系统的性能.我们必须科学的设计索引,才能带来数据库性能的提高.,索引概述,三.建立索引需参照的原则(一)建议创建索引的列1.在经常用来检索的列上创建索引(如经常在WHERE、GROUP BY、ORDER BY子句中出现的列).2.在表的主键、外键上创建索引.3.在聚集索引中，避免包括不必要的列，尽可能使用较小的数据类型，例如用VARCHAR代替CHAR。,索引概述,三.建立索引需参照的原则(一)建议创建索引的列4.在经常用于表间连接的字段上建立索引.5.在条件表达式中经常用到的不同值较多的字段上建立索引，在不同值较少的字段上不要建立索引.6.如果经常同时使用多个字段排序，可以在这些字段上建立复合索引。7.具有高选择性的列是索引的好候选列，具有高密度的列是索引最糟糕的候选列。,索引概述,(二)一般不考虑用于创建索引的列.1.在查询中几乎不涉及的列2.包含太多重复值的列(如性别)3.当数据表的读写性能比查询更重要时,应少建或不建索引.4.数据类型为TEXT、NTEXT和IMAGE的列（不能用于创建索引）。,索引分类,一.从数据表存储结构的角度分类:1.聚集索引2.非聚集索引二.从索引字段是否允许存在重复值来分类:1.唯一索引2.普通索引三.从创建索引依据的字段个数来分类:1.单字段索引2.复合索引(多字段索引，最多可包含16个字段，但列的数据类型的长度之和不能超过900个字节),聚集索引,一.概念:聚集索引将表中的记录在物理数据页中的位置按索引字段值重新排列.二.创建聚集索引的注意事项:1.每个表只能有一个聚集索引2.因为聚集索引改变了表中行的物理顺序,所以在创建任何非聚集索引之前要首先创建聚集索引.3.在聚集索引的创建过程中,SQL SERVER会临时使用当前数据库的磁盘空间(大约为原表的1.2倍),所以要保证有足够的空间创建聚集索引.,非聚集索引,一.概念:非聚集索引与表中数据行的实际存储结构无关,非聚集索引存储的是索引关键字的值与记录的存储位置的对应关系.二.创建非聚集索引的注意事项:1.使用非聚集索引的查询速度要比使用聚集索引的查询速度慢.2.一个表可以创建多个非聚集索引(最多可达249个).3.当删除、新建或重建聚集索引时，系统会自动更新所有的非聚集索引。,创建索引,一.由系统自动创建例如:当创建主键约束或唯一性约束时系统会自动的创建唯一性索引.二.用企业管理器创建索引打开企业管理器右击需创建索引的表所有任务管理索引,创建索引,二.用企业管理器创建索引注意事项:在索引页已填满时,SQL SERVER就必须花时间拆分该索引页,以便为新行腾出空间.选择合适的填充引子值可以在叶级索引页上分配出一定百分比的可用空间,以减少页拆分.提高数据库的性能.1.填充索引:用于指定索引中间级即非叶节点中每个页(节点)上保持开放的空间.其值使用填充因子所指定的大小.2.填充因子:在创建索引时用于指定每个索引页的数据占索引页大小的百分比.,创建索引,二.用企业管理器创建索引填充因子大小的设置原则:1.对于那些频繁进行大量数据插入或删除的表,填充因子可大约设为10.2.对数据不更改、只读的表,填充因子可以设到95以上.,创建索引,三.利用TRANSACT-SQL语句创建索引语法格式:CREATE UNIQUE CLUSTERED|NONCLUSTEREDINDEX INDEX_NAME ON TABLE|VIEW(COLUMN NAME ASC|DESC)ON FILEGROUPNAME,创建索引,三.利用TRANSACT-SQL语句创建索引例1：为AAA数据库中的学生表创建一个唯一聚集索引CC，依据学号字段进行降序排列。USE AAACREATE UNIQUE CLUSTERED INDEX CCON 学生表（学号 DESC）,创建索引,三.利用TRANSACT-SQL语句创建索引例2：CREATE INDEX DDD ON 成绩表（语文，数学 DESC，英语）,创建索引,三.利用TRANSACT-SQL语句创建索引例3:先接入学成绩降序再按出生日期升序对为学生表创建聚集索引,索引名为Q.,删除索引,语法：DROP INDEX 表名.索引名|视图名.索引 名,.n例:CREATE UNIQUE INDEX A ON 学生表(姓名)DROP INDEX 学生表.A,删除索引,关于删除索引的注意事项:1.执行 DROP INDEX 语句后，SQL Server 收回被索引占用的磁盘空间.2.不能用 DROP INDEX 语句删除 PRIMARY KEY 约束或 UNIQUE 约束创建的索引。必须先删除约束，使索引自动删除.3.删除表的时候，表上的所有索引亦同时被删除4.删除聚集索引的时候，所有表上的非聚集索引都会自动被重建.5.DROP INDEX 语句不能在系统表上使用,如何获取某表现有索引的信息,1、在企业管理器中直接查看2、EXEC sp_helpindex 表名 该系统存储过程返回指定表的所有索引信息3、EXEC sp_help 表名该系统存储过程返回指定表的所有相关信息,索引优化向导,一.概念：索引优化向导是SQL SERVER 提供的创建索引的工具,它使用查询优化器分析工作负荷中的查询任务,向有大量工作负荷的数据库推荐最佳的索引混合方式,以加快数据库的查询速度,优化整个查询语句的性能.,索引优化向导,二.使用索引优化向导创建或更新索引的前提条件:需要工作负荷,工作负荷来源于SQL SERVER捕捉的轨迹和包含SQL语句的文件.,索引优化向导,三.利用索引优化向导创建索引的步骤:打开查询分析器查询索引优化向导,
