单元31：索引

1、单元单元31：索引及其应用：索引及其应用问题引入问题引入对于数据库中的海量数据来讲，查询效率是至关对于数据库中的海量数据来讲，查询效率是至关重要的，那么：重要的，那么：数据在磁盘上是如何存储的？数据在磁盘上是如何存储的？系统如何找到这些数据？系统如何找到这些数据？问题解决问题解决使用索引加快查询速度使用索引加快查询速度实际的索引示例：查字典实际的索引示例：查字典每部字典一般都有拼音、偏旁部首等索引，依靠每部字典一般都有拼音、偏旁部首等索引，依靠它们，我们就不必遍历整部字典，只需按照索引它们，我们就不必遍历整部字典，只需按照索引找出所在的页，直接定位到该页即可。找出所在的页，直接定位到该页即可。

2、主要内容主要内容1索引概述索引概述2创建索引创建索引3管理和维护索引管理和维护索引1. 索引概述索引概述数据的存储与访问数据的存储与访问 1索引的作用索引的作用 2索引的类型索引的类型 31.1.1 数据的存储数据的存储在在SQL Server中，数据存储的基本单位是：中，数据存储的基本单位是：一个页的大小是：一个页的大小是：每页的开始部分是每页的开始部分是 字节的页首，用于存储字节的页首，用于存储系统信息，如页的类型、页的可用容量、拥有页的系统信息，如页的类型、页的可用容量、拥有页的对象对象ID等等数据页中包含除数据页中包含除text、ntext和和image数据外的所有数据外的所有数据，数

3、据紧接着页首顺序存放。页尾有一个行偏数据，数据紧接着页首顺序存放。页尾有一个行偏移表移表记录每行第一字节与页首的偏移量记录每行第一字节与页首的偏移量行不能跨页行不能跨页因此，一行数据最多包含的数据量是：因此，一行数据最多包含的数据量是：1.1.2 数据的访问数据的访问SQL Server 提供了两提供了两种数据访问的方法：种数据访问的方法：表扫描法表扫描法从表的第从表的第一行开始逐行查找，直一行开始逐行查找，直到找到符合要求的记录到找到符合要求的记录为止。为止。显然，耗费时间与表的显然，耗费时间与表的数据量称正比。系统响数据量称正比。系统响应时间太长。应时间太长。1.1.2 数据的访问数据的访

4、问索引法索引法当表中创建索引后，当表中创建索引后，SQL Server将在数据表中为将在数据表中为其建立索引页，每个索引页中的行都含有指向数其建立索引页，每个索引页中的行都含有指向数据页的指针。据页的指针。类似我们查字典的过程，不必遍历整本字典，只类似我们查字典的过程，不必遍历整本字典，只需找到拼音或偏旁（相当于建立索引的列）所指需找到拼音或偏旁（相当于建立索引的列）所指向的页即可向的页即可1.2 索引的作用索引的作用加快数据查询加快数据查询加快表的连接、排序和分组加快表的连接、排序和分组需要占用系统资源需要占用系统资源减慢数据修改的速度减慢数据修改的速度1.3 索引的分类索引的分类惟一索引：

5、值不重复惟一索引：值不重复非惟一索引：允许重复非惟一索引：允许重复聚集索引聚集索引:只有一个，默认只有一个，默认为主键，对表物理排序为主键，对表物理排序非聚集索引：不对表进行非聚集索引：不对表进行物理排序。物理排序。2. 创建索引创建索引1索引概述索引概述2创建索引创建索引3管理和维护索引管理和维护索引2. 创建索引创建索引索引可以在创建表的约束时由系统自动创建，也索引可以在创建表的约束时由系统自动创建，也可以通过可以通过SQL Server Management Studio或或CREATE INDEX语句来创建。语句来创建。在创建或修改表时，如果添加了一个主键或惟一在创建或修改表时，如果添

6、加了一个主键或惟一键约束，则系统将自动在该表上，以该键值作为键约束，则系统将自动在该表上，以该键值作为索引列，创建一个索引列，创建一个惟一索引惟一索引。主键约束列通常也是主键约束列通常也是聚集索引聚集索引 【例】客户信息表中，需要经常按【例】客户信息表中，需要经常按“姓名姓名”进行进行查找，所以需要在姓名列建立索引。姓名有可能查找，所以需要在姓名列建立索引。姓名有可能重名，因此需要建立非惟一索引，同时因为主键重名，因此需要建立非惟一索引，同时因为主键已经是聚集索引，因此要建立非唯一、非聚集索已经是聚集索引，因此要建立非唯一、非聚集索引引创建过程创建过程SQL Server Management

7、 Studio中，展开中，展开marketing数据库节点数据库节点打开打开“客户信息客户信息”的设计视图的设计视图选择选择“表和索引属性表和索引属性”按钮，并选择按钮，并选择“索引索引/键键”2.1 SQL Server Management Studio中创建索引中创建索引2.1 SQL Server Management Studio中创建索引中创建索引系统自动命名选择创建索引的列2.2 查询分析器中创建索引查询分析器中创建索引使用使用CREATE INDEX语句创建索引语句创建索引创建索引命令常用格式如下。创建索引命令常用格式如下。CREATEUNIQUECLUSTERED | NON

8、CLUSTEREDINDEX索引名索引名ON表名表名 (字段名字段名,n)WITH 索引选项索引选项 ,n ON 文件组文件组 蓝色表示创建索引使用的关键字蓝色表示创建索引使用的关键字2.2 查询分析器中创建索引查询分析器中创建索引【例】在订单信息表中创建名为【例】在订单信息表中创建名为“IX_订单信息订单信息_客户货品客户货品”的非聚集、复合索引，该索引基于的非聚集、复合索引，该索引基于“客户编号客户编号”和和“货品编码货品编码”CREATE NONCLUSTERED INDEX IX_订单信息订单信息_客户货品客户货品 ON 订单信息订单信息(客户编号客户编号,货品编号货品编号)GO查看订

9、单信息表的索引信息查看订单信息表的索引信息EXEC SP_HELPINDEX 订单信息订单信息注意事项：注意事项：执行执行INSERT和和UPDATE语句时，如果表中有聚集语句时，如果表中有聚集或惟一索引，则自动检验重复值或惟一索引，则自动检验重复值组合索引列相同但顺序不同，则索引不同组合索引列相同但顺序不同，则索引不同惟一索引将检验现存数据惟一索引将检验现存数据2.2 查询分析器中创建索引查询分析器中创建索引（1）用）用SELECT INTO语句生成表语句生成表“高档货品高档货品”，将货品信息表中售价大于平均价的货品插入该表将货品信息表中售价大于平均价的货品插入该表（2）然后为）然后为“高档

10、货品高档货品”创建基于创建基于“编号编号”列的列的惟一索引惟一索引“IX_高档货品高档货品”（3）验证惟一索引的功能）验证惟一索引的功能（1）SELECT * INTO 高档货品高档货品 FROM 货品信息货品信息WHERE 售价售价(SELECT AVG(售价售价) FROM 货品信息货品信息)（2）CREATE UNIQUE INDEX IX_高档货品高档货品 ON 高档货品高档货品(编号编号) 2.2 查询分析器中创建索引查询分析器中创建索引(3)执行如下命令执行如下命令INSERT 高档货品高档货品SELECT * FROM 货品信息货品信息 WHERE 售价售价=(SELECT MA

11、X(售价售价) FROM 货品信息货品信息 )3.管理和维护索引管理和维护索引1索引概述索引概述2创建索引创建索引3管理和维护索引管理和维护索引3. 管理和维护索引管理和维护索引查看和修改索引信息查看和修改索引信息SQL Server Management StudioSP_HELPINDEX删除索引删除索引SQL Server Management StudioDROP INDEXDROP INDEX语句不能删除由主键约束和惟一约语句不能删除由主键约束和惟一约束创建的索引，这些索引必须通过删除约束才能束创建的索引，这些索引必须通过删除约束才能自动删除。自动删除。删除聚集索引时，所有非聚集索引

12、都将重建删除聚集索引时，所有非聚集索引都将重建3. 管理和维护索引管理和维护索引索引的维护索引的维护如何进行数据查询如何进行数据查询，即查询中是否使，即查询中是否使用索引，使用了哪用索引，使用了哪些索引，都是由查些索引，都是由查询优化器决定，查询优化器决定，查询优化器为每个查询优化器为每个查询生成一个最佳的询生成一个最佳的执行计划执行计划用户对数据库的插用户对数据库的插入、修改和删除等入、修改和删除等操作，会使数据变操作，会使数据变得支离破碎，造成得支离破碎，造成索引性能的下降索引性能的下降索引的分析索引的分析l select a.* from dbo.订单信息订单信息 a,dbo.客户信客户

13、信息息 where 编号编号=客户编号客户编号3.1 索引的分析索引的分析（1）显示查询计划）显示查询计划SQL Server提供了两种显示查询中的数据处理步提供了两种显示查询中的数据处理步骤以及如何访问数据的方式。骤以及如何访问数据的方式。 以图形方式显示执行计划以图形方式显示执行计划客户订单查询的执行计划客户订单查询的执行计划 扫描订单信息表聚集索引的说明扫描订单信息表聚集索引的说明 3.1 索引的分析索引的分析 以表格方式显示执行计划以表格方式显示执行计划通过在查询语句中设置通过在查询语句中设置SHOWPLAN选项，我们选项，我们可以选择是否让可以选择是否让SQL Server显示查询计

14、划。显示查询计划。设置是否显示查询计划的命令为：设置是否显示查询计划的命令为： SET SHOWPLAN_ALL ON|OFF或或 SET SHOWPLAN_TEXT ON|OFF3.1 索引的分析索引的分析SET SHOWPLAN_ALL ONGOSELECT * FROM 客户信息客户信息 A INNER JOIN 订单订单信息信息 B ON A.编号编号=B.客户编号客户编号GO 3.1 索引的分析索引的分析（2）数据）数据I/O统计统计通过设置通过设置STATISTICS IO,可以显示磁盘可以显示磁盘I/O信息信息SET STATISTICS IO ONGOSELECT * FROM

15、 客户信息客户信息 A INNER JOIN 订单订单信息信息 B ON A.编号编号=B.客户编号客户编号GO 3.2 索引的维护索引的维护（1）统计信息更新）统计信息更新在在SQL Server Management Studio中通过设置中通过设置数据库的属性决定是否实现统计的自动更新。数据库的属性决定是否实现统计的自动更新。使用使用UPDATE STATISTICS命令更新命令更新“客户信息客户信息”表主键索引的统计信息。表主键索引的统计信息。 UPDATE STATISTICS 客户信息客户信息 PK_客户信息客户信息GO设置数据库的属性决定是否实现统计的自动更新 3.2 索引的维护

16、索引的维护（2）使用）使用DBCC SHOWCONTIG语句扫描表语句扫描表对表进行数据操作可能会导致表碎片，而表碎对表进行数据操作可能会导致表碎片，而表碎片会导致额外的页读取，从而造成数据库查询片会导致额外的页读取，从而造成数据库查询性能的降低。此时用户可以通过使用性能的降低。此时用户可以通过使用DBCC SHOWCONTIG语句来扫描表，并通过其返回语句来扫描表，并通过其返回值确定该表的索引页是否已经严重不连续。值确定该表的索引页是否已经严重不连续。DBCC SHOWCONFIG(客户信息客户信息, PK_客户信客户信息息)3.2 索引的维护索引的维护3.2 索引的维护索引的维护如果页密度百分比比较低，则需要清理表中碎片如果页密度百分比比较低，则需要清理表中碎片3.2 索引的维护索引的维护（3）使用）使用DBCC INDEXDEFRAG语句进行碎片语句进行碎片整理整理DBCC INDEXDEFRAG(marketing,客户信息客户信息, PK_