第08章 销售管理数据库中索引的应用

1、 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社第八章第八章 销售管理数据库中索销售管理数据库中索引的应用引的应用 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社技能目标技能目标 理解索引的优缺点；学会根据实际需求设计销售理解索引的优缺点；学会根据实际需求设计销售管理数据库中的数据表的索引，提高数据检索的速度。管理数据库中的数据表的索引，提高数据检索的速度。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社索引的作用索引的作用 u索引是一个单独的、物理的数据库结构，是为了

2、加速索引是一个单独的、物理的数据库结构，是为了加速对表中的数据行的检索而创建的一种分散存储结构。对表中的数据行的检索而创建的一种分散存储结构。u索引是针对一个表而建立的，每个索引页面中的行都索引是针对一个表而建立的，每个索引页面中的行都含有逻辑指针，指向数据库表中的物理位置，以便加含有逻辑指针，指向数据库表中的物理位置，以便加速检索物理数据。速检索物理数据。u索引一旦创建就完全由系统自动选择和维护，不需要索引一旦创建就完全由系统自动选择和维护，不需要用户指定使用索引，也不需要用户执行打开索引或进用户指定使用索引，也不需要用户执行打开索引或进行重新索引等操作，这些都是由行重新索引等操作，这些都是

3、由SQL ServerSQL Server数据库管数据库管理系统自动完成的。理系统自动完成的。SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社索引的分类索引的分类 u 聚集索引聚集索引u非聚集索引非聚集索引u惟一索引惟一索引u包含性列索引包含性列索引u索引视图索引视图u全文索引全文索引uXMLXML索引索引 其中，聚集索引和非聚集索引是数据库引擎最基本的索引其中，聚集索引和非聚集索引是数据库引擎最基本的索引 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社索引的分类索引的分类u聚集索引（聚集索引（clustered

4、 indexclustered index，也称聚类索引、簇，也称聚类索引、簇集索引）集索引）u聚集索引是一种物理存储方式聚集索引是一种物理存储方式 。数据表中的。数据表中的数据是按照聚集索引指定方式或者说是顺序，数据是按照聚集索引指定方式或者说是顺序，保存在磁盘空间中。保存在磁盘空间中。因为数据行本身只能按一因为数据行本身只能按一个顺序排序，个顺序排序，所以一个数据表只能建立一个聚所以一个数据表只能建立一个聚集索引。集索引。u只有当表包含聚集索引时，表中的数据行才按只有当表包含聚集索引时，表中的数据行才按照排序顺序存储。如果表具有聚集索引，则该照排序顺序存储。如果表具有聚集索引，则该表称为聚

5、集表。如果表没有聚集索引，则其数表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。据行存储在一个称为堆的无序结构中。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社索引的分类索引的分类u非聚集索引（非聚集索引（nonclustered indexnonclustered index，也称非聚类，也称非聚类索引、非簇集索引）索引、非簇集索引）u非聚集索引是一种逻辑存储方式。索引的次序非聚集索引是一种逻辑存储方式。索引的次序并不影响数据的物理存储顺序。并不影响数据的物理存储顺序。SQL ServerSQL Server中中通过建立一

6、个页，存放按非聚集索引次序形成通过建立一个页，存放按非聚集索引次序形成的数据次序的指针，指向数据的实际存放地址。的数据次序的指针，指向数据的实际存放地址。u从非聚集索引中的索引行指向数据行的指针称从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器存储在堆中还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚是指向行的指针。对于聚集表，行定位器是聚集索引键集索引键 。SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社索引的优点索引的

7、优点u加快检索速度。加快检索速度。u加速对联接表查询和执行排序或分组操作的查询加速对联接表查询和执行排序或分组操作的查询u如果在创建索引时定义了唯一性，强制行的唯一性。如果在创建索引时定义了唯一性，强制行的唯一性。u查询优化器依赖于索引起作用。查询优化器依赖于索引起作用。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社索引的缺点索引的缺点u创建索引要花费时间并占有存储空间。创建索引要花费时间并占有存储空间。u降低数据维护的速度。在已索引的列上维护数据时，降低数据维护的速度。在已索引的列上维护数据时，SQL ServerSQL Server会更新相关的索

8、引。维护索引需要时间和会更新相关的索引。维护索引需要时间和资源。资源。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社索引设计原则索引设计原则u创建了索引的列几乎是立即响应，而不创建索引的创建了索引的列几乎是立即响应，而不创建索引的列则需要较长的等待时间。是不是可以为每一列创列则需要较长的等待时间。是不是可以为每一列创建索引？建索引？u哪些列可以创建索引，哪些列不考虑创建索引，为哪些列可以创建索引，哪些列不考虑创建索引，为什么不在每一列上创建索引？什么不在每一列上创建索引？SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大

9、学出版社考虑创建索引的列：考虑创建索引的列：u主键；主键；u连接中频繁使用的列（外键）；连接中频繁使用的列（外键）；u在某一范围内频繁搜索的列和按排序顺序频繁检索在某一范围内频繁搜索的列和按排序顺序频繁检索的列。的列。SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社不考虑创建索引的列：不考虑创建索引的列：包含太多重复值的列包含太多重复值的列查询中很少被引用的列查询中很少被引用的列值特别长的列值特别长的列具有很多具有很多NULLNULL值的列值的列需要经常插入、删除、修改的列需要经常插入、删除、修改的列记录较少的基本表记录较少的基本表需要进行频繁的大批量数

10、据更新的基本表需要进行频繁的大批量数据更新的基本表不能将不能将 ntextntext、texttext、imageimage数据类型的列指数据类型的列指定为索引列定为索引列 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社索引和约束的关系索引和约束的关系 对列定义对列定义PRIMARY KEYPRIMARY KEY约束和约束和 UNIQUEUNIQUE约束时，会自动创建索引。约束时，会自动创建索引。1 1、PRIMARY KEYPRIMARY KEY约束和索引约束和索引 如果创建表时，将一个特定列标识为主键，如果创建表时，将一个特定列标识为主键， 自动对

11、该列创自动对该列创建建PRIMARY KEY约束和惟一聚集索引。约束和惟一聚集索引。2 2、UNIQUE UNIQUE 约束和索引约束和索引u 默认情况下，创建默认情况下，创建UNIQUE约束，自动对该列创建惟一非聚约束，自动对该列创建惟一非聚集索引。集索引。u当用户从表中删除主键约束或惟一约束时，创建在这些约束当用户从表中删除主键约束或惟一约束时，创建在这些约束列上的索引也会被自动删除。列上的索引也会被自动删除。3 3、独立索引、独立索引 使用使用 CREATE INDEX 语句或语句或 SQL Server Management Studio 对象资源管理器中的【新建索引】对话框创建独立于

12、对象资源管理器中的【新建索引】对话框创建独立于约束的索引约束的索引 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建索引创建索引 u使用使用Management Studio Management Studio 【例】在员工表上创建员工编号的聚集索引。【例】在员工表上创建员工编号的聚集索引。u使用使用CREATE INDEXCREATE INDEX语句语句 CREATE UNIQUE CLUSTERED | NONCLUSTERED /* 索引的类型索引的类型*/INDEX 索引名索引名ON 表名表名|视图名视图名 (列名列名 ASC | DESC

13、,.n)【例】在销售管理系统中，经常要按照客户名称查询信息，希望【例】在销售管理系统中，经常要按照客户名称查询信息，希望提高查询速度。提高查询速度。Create unique index ix_cnameOn 客户客户(公司名称公司名称)SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社删除索引删除索引 u使用使用Management Studio Management Studio 删除独立于约束的索引删除独立于约束的索引u使用使用DROP INDEXDROP INDEX语句删除独立于约束的索引语句删除独立于约束的索引【例】删除【例】删除Provider

14、表的表的IX_name_Provider索引。索引。【例】删除【例】删除department表中表中PK_Department聚集索引。聚集索引。说明：说明： 由于由于PK_Department聚集索引是由聚集索引是由department表在创建表在创建主键约束时自动创建的索引，所以无法利用主键约束时自动创建的索引，所以无法利用DROP INDEX语语句删除索引。句删除索引。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社查看索引查看索引 u使用使用Management StudioManagement Studiou用系统存储过程用系统存储过程 sp

15、_helpindexsp_helpindex 可以返回表的所有索引信息，它的语法结构如下。可以返回表的所有索引信息，它的语法结构如下。 sp_helpindex objname=nameSQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社重命名索引重命名索引 利用系统存储过程利用系统存储过程Sp_renameSp_rename更改索引的名称，语更改索引的名称，语法格式如下。法格式如下。 Sp_rename 表名表名.原索引名称原索引名称, 新索引名称新索引名称SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社维护索引维护索引 u查看碎块信息查看碎块信息 使用使用 sys.dm_db_index_physical_stats 系统函数和使用系统函数和使用Management Studio 两种方式查看有关索引的碎片信息两种方式查看有关索引的碎片信息u维护统计信息维护统计信息 使用使用 DBCC SHOW_STATISTICS命令查看统计信息命令查看统计信息 。 【例】查看【例