SQL Server 2005数据库应用技术（第二版）,第六章 表的管理与使用（3）,复习,数据查询:SELECT ALL|DISTINCT|TOP N PERCENT字段列表 INTO 目标数据表 FROM 源数据表或视图,.n WHERE 条件表达式 GROUP BY 分组表达式 HAVING 搜索表达式 ORDER BY 排序表达式 ASC|DESC COMPUTE 行聚合函数名(表达式),.n ，BY 分类表达式 ,.n ,导入： 执行如下查询：SELECT * FROM XSDA WHERE 学号=200606 如何查询所需的数据行？访问数据库中数据的方法：表扫描和索引查找,第6章 表的管理与使用,SQL Server 访问数据的方式,表扫描 SQL Server 扫描表的所有页 索引 SQL Server 使用索引页找到行 查询时,首先确定是否存在 索引,然后通过查询优化器 确定表扫描与索引哪种方 法更有效.,6.1.1 SQL Server 访问数据的方式,所以：为了提高检索速度，采用索引技术，以学号列建立索引。查询时不必扫描所有的行，只需根据索引，找到满足条件的值（200606），然后根据索引中的位置信息确定表中的行。 由于索引进行了分类，并且由于索引的行和列比较少，所以搜索索引是很快的。,第6章 表的管理与使用,6-6 索引,6-6-1 索引的特点和用途索引是一个单独的、物理的数据库结构。它是对数据表中的一个或者多个字段进行排序的一种结构，以加快在表中查询数据的速度。 索引是依赖于表建立的，它提供了数据库中编排表数据的内部方法。一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上。,6-6 索引,6-6 索引,一个表的存储是由两部分组成的，一部分用来存放表的数据页，另一部分存放索引页，通常索引页面以数据页来说小得多。在进行数据检索时，系统首先搜索索引页面，从中找到所需数据的指针，然后直接通过该指针从数据页面中读取数据，从而提高查询速度。优点： 快速查询数据； 保证数据记录的惟一性（惟一性索引）； 加速表与表之间的连接，实现表与表之间的参照完整性； 在使用分组和排序子句进行数据检索时，利用索引可以减少排序和分组的时间。 通过使用索引，可以在查询过程中使用优化隐藏器，提高系统性能。,6-6 索引,缺点：1. 使用索引存储地址将占用磁盘空间，如果要建立聚集索引,需要的空间更大;2. 创建与维护索引要耗费时间,并随着数据量的增加而增加;3. 在执行数据的插入、修改和删除操作时，为了自动维护索引，SQL Server将花费一定的时间。,访问数据的方式- 索引分类,在SQL SERVER中存在三种和索引相关的选项,分别是堆、聚集索引和非聚集索引。 堆：数据的堆积、没有任何排列顺序，查询时只能是全表遍历查询。 聚集索引：数据按照一定的物理顺序存储。该物理顺序就是索引的逻辑顺序。 非聚集索引：逻辑顺序与数据的物理存储顺序不一致。 例:字典：数据 ，目录：索引 字母排序的目录：聚集索引 其它目录如组词、知识点等不具备顺序关系：非聚集索引。 非聚集索引必须通过聚集索引才能找到数据所在的位置。,堆,SQL Server 2005 堆内的数据页和行没有任何特定的顺序，也不链接在一起。在下列情况下可以使用堆：频繁添加、删除和更新行的易变数据的表 包含少量数据的表 包含写入但极少读取的数据 包含重复数据行的表,聚集索引,索引页叶节点,一张表只能有一个聚集索引 B树按索引键的顺序存储数据页 每个索引行包含一个键值和一个指针，指向B树中的中间级别页或索引的叶级别页中的数据行。,sys.partitions,索引页中间级,根索引页,6.1.3 聚集索引,聚集索引,1、使用聚集索引的场合 使用BETWEEN、=、=等运算符返回一系列值。 返回使用ORDER BY 或GROUP BY 子句排序的数据 返回使用JOIN子句合并的数据 返回大型结果集。 2、使用聚集索引的注意事项 并不是所有的列都适合做聚集索引键。 应以尽可能少的列定义索引键，聚集键值小，页中索引行的数量就会增加，B树的级数就会降低。,聚集索引,索引键尽可能使用唯一或包含多个不同值的列，避免使用有重复值的列 使用经常用排序从表中检索数据的列 使用顺序访问的列3、不使用聚集索引的场合 索引列中的数据频繁更改 索引键是宽键,非聚集索引,非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点： 基础表的数据行不按非聚集键的顺序排序和存储。 非聚集索引的叶层是由索引页而不是由数据页组成 既可以使用聚集索引来为表或视图定义非聚集索引，也可以根据堆来定义非聚集索引。 非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。 如果表是堆，则行定位器是指向行的指针。 如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。,非聚集索引,1、使用非聚集索引的场合 使用JOIN或GROUP BY子句提高查询性能，可对涉及联接和分组操作的列创建多个非聚集索引 表的更新频率低，但是包含大量数据。 查询不会返回大量的结果集 对查询搜索条件频繁涉及的列进行索引 对包含多个不同值的列进行索引。2、注意事项： 如果表数据更新频繁，应避免大量使用非聚集索引 如果聚集索引进行删除、重建、更改，非聚集索引会重建。,其它分类,唯一性索引 视图索引 包含性列索引 全文索引 XML,6-6 索引,创建索引的约束:尽可能对空表建立索引,因为对一个已经存有数据的表创建索引会对性能产生一定的影响.如果在一个表中既要创建聚集索引，又要创建非聚集索引时，应先创建聚集索引，然后创建非聚集索引。在缺省情况下，所创建的索引是非聚集索引；在每一个表上面，可以创建不多于249个非聚集索引。由于非聚集索引使用索引页存储，因此它比聚集索引需要更多的存储空间，且检索效率较低。在一个表中使用太多的索引，会影响数据库的性能。对于一个经常会改变的表，应该尽量限制表只使用1个聚集索引，和不超4个非聚集索引。因此，在经常查询的字段上建立索引；在那些重复值比较多、查询较少的列上不要建立索引。,6-6 索引,6-6-3 创建索引1.SSMS中创建索引,6-6 索引,6-6-3 创建索引 2.使用T-SQL语句创建索引：CREATE INDEX CREATE UNIQUE -惟一索引 CLUSTERED | NONCLUSTERED -聚集索引|非聚集索引 INDEX index_name -索引名称 ON 表 | 视图 (列名 ASC | DESC ) -指定建立索引的表、视图及字段INCLUDE (列名，n) -指定要添加到非聚集索引的叶级别的非键列WITH 索引选项 -指定索引选项(详情请参阅帮助)ON 文件组名|分区方案|DEFAULT -若没有该短语,则索引与基础表或视图使用相同的文件组.说明： 为表定义一个主键，将会自动地在主键所在列上创建一个唯一索引，称之为主键索引。 创建唯一索引时，则应确定索引所包含的列均不允许NULL值，否则容易出错。 只有在指定的索引名称存在时，才能使用DROPEXISTING选项，该项说明首先删除指定表的索引后再重新构造它。,6-6 索引,6-6-3 创建索引【例】为XS数据库中XSDA表的学号列创建索引。 USE XS CREATE INDEX xh_ind ON XSDA(学号) GO【例】为KCXX表的课程编号列创建唯一聚集索引。 USE XS CREATE UNIQUE CLUSTERED INDEX kcbh_ind ON KCXX(课程编号) GO,创建多列索引,复合索引 最多可组合 16 个列以及键的长度不超过900 B 首先定义唯一性最高的列 【例】根据XSCJ表的学号列和课程编号列创建惟一复合索引。 USE XS CREATE UNIQUE INDEX xh_kcbh_ind ON XSCJ(学号,课程编号) GO,包含性列 带包含性列的索引 提供查询范围，提高查询性能 非键列 可以是不允许作为索引键列的数据类型 在计算索引键列的数目或索引键大小时，不会被考虑在内，解决了索引大小的限制（最大键列数为16，最大索引键大小为900B）,USE AdventureWorks CREATE INDEX IX_Document_Title ON Production.Document (Title, Revision) INCLUDE (FileName),创建多列索引,6-6 索引,6-6-4 查看索引1使用SSMS: 在对象资源管理器中,对现有的索引名称右键选择”重新组织” 或属性2使用系统存储过程sp_helpindex 语法格式： sp_helpindex objname= name【例】查看表XSDA中的索引情况。 sp_helpindex XSDA,6-6 索引,6-6-5 修改索引1使用SSMS 右键选中要修改的索引,选择”属性”2T-SQL语言 ALTER INDEX 索引名 | ALL ON 表名|视图名 REBUILD WITH 索引选项 -重新生成索引 | DISABLE -禁用索引| REORGANIZE -重新组织索引 | SET ( ,.n ) -为索引指定相关选项例： ALTER INDEX kcbh_ind ON KCXX REBUILD,6-6 索引,6-6-6 删除索引1使用SSMS2使用T-SQL的DROP INDEX命令删除索引。 语法格式： DROP INDEX table.index|view.index,.n 其中table|view：是索引列所在的表或视图；index：为要除去的索引名称。【例】删除XSDA表中的索引xh_ind。 USE XS IF EXISTS(select name from sysindexes where name=xh_ind DROP INDEX XSDA.xh_ind GO,小结,索引的概念索引的分类:按组织方式可以分为聚集索引和非聚集索引 索引的创建修改查看删除,Thank You !,