SQL索引(081124上课用).pptVIP

2004-11-1 第10章 索引 索引的创建和使用 一、 索引的概念 二、 索引的分类 三、使用T-SQL语句创建索引 四、索引的管理 一、 索引的概念 1.索引的概念 1)索引是指表中的数据和其相应存储位置的列表。它类似于书中的目录，可以快速地查找表中的数据而不必扫描整个数据表。 例如对teacher表的birth列建立索引后形成的表及索引表如图二、图三所示。 2) 索引是依附于表存在数据库对象。 3)索引的设计也是数据库物理设计的内容之一。 2. 数据检索方式 在SQL SERVER中，可管理的最小空间是页，向表插入数据时，数据是按插入时间的先后顺序放置在数据页上，一个页放满后再放入下一页，由此构成数据存储的物理顺序。对表数据的检索，有表扫描和索引查询两种方式： 表扫描：对整个表依次扫描所有的数据页，查找满足条件的记录，即使只有一条满足条件的记录，并且记录就在第一页上，系统也不得不在所有的数据页中查找，因而对于记录数很多的表来说，查找速度较慢。（如查找某班级的学生……） 索引查询：对查询条件中的列建立索引（如班级号），系统先只沿着索引排列的顺序对仅有一列数据的索引表读取查询条件（如g99402），然后再沿着索引指针的指向转移到数据表上，查找到相应的记录。由于索引总是按照一定的顺序排列，所以对索引进行扫描的速度大大快于对表扫描的速度。 SQL SERVER对检索方式的选择： SQL SERVER在检索数据时，由系统确定数据表是否建有索引，若没有，则使用表扫描方法，若建有索引，则使用索引查询的方法。 3. 索引的作用 提高数据检索性能、加快数据查找速度。 但是，由于创建和维护索引需要耗费大量的时间，并且占用额外的存储空间，因此不能无限制的创建索引。 二、 索引的分类 1、按索引结构不同分为聚簇索引与非聚簇索引 所谓索引结构不同，是指索引的顺序与数据表的物理顺序是否相同。 (1)聚簇索引（Clustered Index） 1)含义 数据表的物理顺序与索引顺序相同。 建立聚簇索引后的表将改变原来的物理顺序，产生按索引关键字值升序排列的逻辑顺序。 例如对student表按sex列建立聚簇索引，则数据表的记录顺序以改变为按性别的顺序排列。 2)说明 ① 每个数据表只能建立一个聚簇索引； ②聚簇索引建立后会破坏数据表原来的物理顺序，且不可恢复。因此，若需要保留原来的物理顺序，必须先对数据表作备份。； ③聚簇索引在其数据存储页记录了数据表的真实记录内容，并且按索引关键字的顺序排列 ④建立聚簇索引需要足够的磁盘空间，大约是表空间的1.2倍； (2)非聚簇索引（Nonclustered Index） 1)含义 数据表的物理顺序与索引顺序不同。 建立非聚簇索引后的表并不改变原来的物理顺序，只是产生了一个按索引关键字值升序排列的索引表，该索引表只记录索引关键字的值及指向与关键字值对应的数据行的指针(用页号和行号表示)。 如对student表按student_name建立非聚簇索引，则查询该表时看到表的记录顺序并未发生变化，因此建立非聚簇索引只是生成相应的索引表，其作用是提高Where子句、Order By子句、Group By子句检索数据的速度。 2)说明 ① 每个数据表可建立多个非聚簇索引(最多249个)，当对同一个表需要产生多种排列顺序时非常适合使用非聚簇索引； ② 若未指定索引类型，则默认为非聚簇索引。 2、按索引实现的功能不同分为唯一索引与非唯一索引 “唯一索引”既可以采用聚簇索引的结构，又可以采用非聚簇索引的结构。 唯一索引的特征： ① 不允许索引列上出现重复值。 ② 可用于实施实体完整性。 ③ 在创建主键约束和唯一约束时自动创建唯一索引。 在已有数据的表上创建唯一索引时，如果在该列数据存在重复值，那么系统将返回错误信息。在实际的编程应用中会经常使用到唯一索引。因为在一个表中，可能会有很多列的列值需要保证其唯一性，如：有身份证号、工号、学号等，可在这些列上创建唯一索引。 三、 使用T-SQL语句创建索引 (1) 创建索引的T-SQL语句语法 创建索引使用的是CREATE INDEX语句。CREATE INDEX语句的基本格式如下： create [uniqun][clustered/nonclustered] index 索引标识名 on表名(列名1[,列名2，……]) [with fillfactor=填充率] 功能:在指定表中,以指定的列名为索引关键字创建指定类型的索引。 语句中几个常用子句的含义为： Uniqun：表示建立唯一索引，强行实施某个索引列的唯一性，未建索引前，若该 索引列有重复值，则语句执行失败，建立索引后，若插入重复值，则系统拒绝接收，并给出错误信