1-4 索引

原创

qianshao 2010-07-11 23:08:37 博主文章分类：SQLServer2005日记 ©著作权

文章标签 职场索引休闲 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者qianshao的原创作品，请联系作者获取转载授权，否则将追究法律责任

1-4 索引

u 学习索引的基本概念和特性

u 掌握堆、聚簇索引、非聚簇索引、唯一性索引的内涵

u 学习建立与删除索引的SQL语句

u 掌握获取及优化索引信息的方法

1-4-1　索引概述

SQL Server访问数据库的方式有两种：一种是扫描表的所有页，称之为“表扫描”，另一种是使用索引技术。当进行表扫描的时候，必须对整张表数据信息进行遍历查询，效率较慢，而通过索引可以提高查询的效率，如图1-18所示。

图1-18 两种不同的检索方式

用户对数据库进行最频繁的操作就是数据查询。一般情况下数据库进行查询时需要对整张表进行数据搜索，当表中的数据信息量相当大的时候就需要很长的时间。这就造成了服务器资源的浪费，为了提高检索数据的能力，数据库需要引进索引的概念和机制，我们可以将数据库看成是一本书，而索引文件就是这本书的目录。

索引的引入具有以下的几个特性：

1. 索引是一个单独的、物理的数据结构，它是某个表中一列或者若干列值的集合和相应的指向表中物理标示这些值的数据列的逻辑清单。

2. 索引是依赖表建立的，它提供了数据库中排列表中数据的内部方法。

3. 一个表中的存储是由两部分组成的，一部分用来存储表的数据页面，另一部分用来存储索引页面。索引就放置在索引页面上，通常索引页面相对于数据页面小得多，当进行数据检索时，系统先进行索引页面搜索，从中找到数据的指针，再通过数据指针从数据页面中找寻数据，具体表述如图1-19所示。

图1-19 索引基本表和索引之间的关系图

1-4-2　SQL Server 2005索引基本概念

SQL Server 2005索引基本概念有四种：堆、聚簇索引、非聚簇索引、唯一性索引。

1．堆

就是数据的堆积，本质为全表遍历查询，很显然这种方式的检索效率是非常低的。其主要特点是，首先没有聚集索引的表，其次不按特定顺序存储的页。

图1-20 索引基本表和索引之间的关系图

2．聚簇索引（Clustered Index）

根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型：聚簇索引与非聚簇索引。聚簇索引是指数据表的物理顺序和索引表的顺序相同，它根据表中的一列或多列的值排列记录。每一个表只能有一个聚簇索引，因为一个表的记录只能以一种物理顺序存放，在通常情况下，使用的都是聚簇索引。

聚簇索引有利于范围搜索，由于聚簇索引的顺序与数据行存放的物理顺序相同，因此，聚簇索引最适合于范围搜索，因为相邻的行将被物理地存放在相同或相邻近的页面上。

(1) 创建聚簇索引的几个注意事项：

q 每张表只能有一个聚簇索引。

q 由于聚簇索引改变表的物理顺序，所以应先建聚簇索引，后创建非聚簇索引。

q 创建索引所需的空间来自用户数据库，而不是TEMPDB数据库。

q 主键是聚簇索引的良好候选者。

(2) 不使用聚簇索引的场合：

q 数据项频繁进行更改的情形。

q 索引键为宽键(两个或以上属性共同构成键)的情况。

3．非聚簇索引（Nonclustered Index）

非聚簇性索引值在提高聚簇索引没有覆盖的常用查询的性能。如果你的表已经建立了聚簇性索引，并且你希望检索非键值属性列，那么你就别无选择，只能够建立非聚簇性索引，以提高查询效率。

对于非聚簇索引，表的物理顺序与索引顺序不同，即表的数据并不是按照索引列排序的。索引是有序的，而表中的数据是无序的。一个表可以同时存在聚簇索引和非聚簇索引，而且一个表可以有多个非聚簇索引。例如对记录网站活动的日志表可以建立一个对日期时间的聚簇索引和多个对用户名的非聚簇索引。

(1) 创建非聚簇索引的几个注意事项：

q 创建非聚簇索引实际上是创建了一个表的逻辑顺序的对象

q 索引包含指向数据页上的行的指针

q 一张表可创建多达249个非聚簇索引

q 创建索引时，缺省为非聚簇索引

(2) 使用非聚簇索引的场合：

q 通过内链接或者group by子句提高查询性能

q 期望提高非键值查询效率，而结果非大型结果集

q 提高宽键查询效率

q Where 语句中频繁涉及的属性

(3) 使用非聚簇索引注意事项：

q 除非指定为聚簇性索引，否则数据库引擎将建立非聚簇性索引

q 如果表中数据仅仅有少量信息不同，不要使用（非）聚簇索引，此时使用表扫描技术效率更好。

4．唯一性索引

唯一性索引是指不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统将拒绝违背唯一性的插入、更新操作。如果表有主键，则在建立物理表时候（执行create table或者alter table语句），该表将自动建立唯一性索引。默认情况下，这也是聚簇性索引。

(1) 唯一性索引的作用：

q 强制实施唯一性。

q 索引的目标将实现查询的效率，但这种索引将占据更多的空间。

(2) 主键与唯一性索引的差别：

q 主键一定是唯一性索引，但是唯一性索引不一定是主键

q 一个表可以有多个唯一性索引，但是主键只能够有一个。

q 主键不允许为空，但是唯一性索引允许为空。

在 SQL Server 中，索引是按 B 树结构进行组织的。索引 B 树中的每一页称为一个索引节点。B 树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。如图1-21所示。

如图1-22所示，如果LoginID被设置为唯一性索引（不一定为主键），则如果录入数据与前面数据相冲突，则系统将不允许存在相同的值。

图1-21 索引基本表和索引之间的关系图 1-22 设置为唯一性索引禁止录入相同值

1-4-3　建立与删除索引

创建索引使用的是CREATE INDEX语句，CREATE INDEX语句的语法形式如下：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name

ON table_name（column_name [ ASC | DESC ] [ ,...n ] ）

[WITH [PAD_INDEX],FILLFACTOR = fillfactor, DROP_EXISTING]]

而删除索引的一般格式为，DROP INDEX 表名.索引名

1．建立索引语法说明

(1) UNIQUE：指定创建的索引是唯一索引。如果不使用这个关键字，创建的索引就不是唯一索引。

(2) CLUSTERED|NONCLUSTERED：指定被创建索引的类型，使用CLUSTERED创建的是聚簇索引；使用NONCLUSTERED创建的是非聚簇索引，这两个关键字中只能选其中的一个。

(3) index_name：为新创建的索引指定的名字。

(4) table_name：创建索引的基表的名字。

(5) column_name：索引中包含的列的名字。

(6) ASC|DESC：确定某个具体的索引列是升序还是降序排序。默认设置为ASC升序。

(7) DROP_EXISTING：删除先前存在的、与创建索引同名的聚簇索引或非聚簇索引。

2．FILLFACTOR与PAD_INDEX参数说明

(1) FILLFACTOR参数的说明

FILLFACTOR的作用是，当系统新建或重建索引时，在每一个索引页上预先留出一部分空间，使得系统在新增索引信息时能够保持索引内容在索引页上尽量连续。它使得索引的页分裂度最小，并可以对性能微调。

(2) 设置FILLFACTOR值时，应考虑如下因素：

q 填充因子的值是从 0 到 100 之间的百分比数值，用来指定在创建索引后对数据页的填充比例。

q 值为 100 时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。

q 值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。

q 使用 sp_configure 系统存储过程可以在服务器级别设置默认的填充因子。

q 填充因子只在创建索引时执行；索引创建后，当表中进行数据的添加、删除或更新时，不会保持填充因子。

(3) PAD_INDEX参数的说明

Fillfactor只能指定叶级索引页的数据充满度。PAD_INDEX指定索引非叶级中每个索引页上保持开放的空间，即非叶级的索引页的数据充满度。PAD_INDEX 必须和Fillfactor一起使用，而且Fillfactor的值决定了PAD_INDEX指定的充满度。PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。

3．建立索引的基本方法

根据需要，可以动态地定义索引，即可以随时建立和删除索引。但是不允许用户在数据操作中引用索引，而索引如何使用完全由系统决定，这也支持了数据的物理独立性。索引建立的基本原则是：应该在使用频率高的、经常用于连接的列上建索引，虽然索引可以提高查询效率，并且一个表上可建多个索引，但是没有必要过多的建立索引，因为索引过多耗费空间，且降低了插入、删除、更新的效率。

实验1：通过SQL命令建立基本表的索引实验

--例1：建立单列唯一性索引

CREATE UNIQUE INDEX Stusno ON Student(Sno)；

--例2：建立单列唯一性索引

CREATE UNIQUE INDEX Councno ON Course(Cno)；

--例3：建立多列唯一性索引

CREATE UNIQUE INDEX Scno ON SCore (Sno ASC,Cno DESC)；

--例4：建立单列聚簇性索引

CREATE clustered INDEX Stusno ON Student(Sno)；

但是在建立例4索引时候会出现图1-23的错误，解决的办法可尝试将聚集性索引删除。

图1-23 删除索引错误

--例5：删除聚集索引

drop index student.Stusno

再次建立时候问题依旧（如图1-24所示），说明在建立student表时候指定了主键sno，默认根据主码建立唯一性索引和聚簇性索引，而一张表的聚簇性索引只能够有一个，因此出现错误。将提示的聚簇性索引删除。

--例6：删除主键的唯一性索引

drop index student.pk_student

图1-24 再次删除索引错误

再次出现错误提示，主要原因是受到主码约束，禁止删除主码附带的聚簇索引信息；解决方案是：去除主码信息。具体步骤如图1-25所示。

图1-25 去除主键的标识

此时，再次执行CREATE clustered INDEX Stusnocluster ON Student(Sno)，命令成功执行。

--例7：建立单列非聚簇性索引

CREATE nonclustered INDEX Stusno_noncluster ON Student(Sname)；

--注意：非聚簇性索引并不是唯一的，对于需要经常进行检索的属性项可以直接建立

--例8：在school数据库中，为“课程”表创建一个基于“课程号”的名为khh_index，唯一性聚簇索引，升序，填充因子50%

CREATE UNIQUE nonCLUSTERED INDEX khh_index

ON course(cno ASC )

WITH FILLFACTOR = 50

--例9：在school数据库中，为“成绩课”表创建一个基于“学生编号，课程号”组合列的聚集、复合索引xscj_index，升序，填充因子50%

CREATE nonCLUSTERED INDEX xscj_index ON score(sno ASC,cno ASC) WITH PAD_INDEX,FILLFACTOR = 50

--例10：索引的更名操作，将STUDENT数据库中“course”表的pk_kc索引名称更改为pk_kecheng。其程序清单如下：

EXEC sp_rename 'course.khh_index','kh_index‘

--注意：如果不加表名称则会出现错误！！

小问题：为什么针对主码建立聚集性索引必须去掉主码特性呢？聚集性索引和唯一性索引在建立时候有何差异呢？

聚簇性索引是随着物理表的建立，并且指定了主码的情况下一并建立的。这说明，一旦建立了主码信息，则同时将建立唯一性索引和聚簇性索引，但是唯一性索引可以允许建立多个，但是聚簇性索引只能够建立一个。而如果重新建立聚簇性索引，则必须将原有的主键属性去掉后，才允许重新建立新的聚簇性索引。另一方面，由于仅仅建立聚簇性索引，原有的唯一性索引就消失了，因为没有主码性质，所以该属性自然就不具有唯一性。见图1-26所示为建立了主码就是意味着同时建立了聚集性索引和唯一性索引。

图1-26 去除主键的标识

实验2：利用管理平台创建索引

第一步：选择student表，展开其索引项，右键选择“新建索引”项，如图1-27所示。

图1-27 新建索引项

第二步：在展开的新建索引控制平台中，填写索引名城，选定类型为非聚簇型索引，点击添加按钮，打开索引键表列项，勾选sname属性，并点击确定后，针对sname的非聚簇性索引建立完毕，，如图1-28所示。

图1-28 确定索引类型及索引项目

1-4-4　获取及优化索引信息的方法

某些情况下，我们需要获取索引的基本信息和类型，另一方面我们也需要针对数据库运行一段时间后的实际情况，对索引信息进行适当的优化工作，以提高数据库信息检索的效率。

1．获取索引信息

获取索引信息的方法主要有两种：通过管理平台获取（可视化形势）和命令行方式。下面我们通过案例具体说明如何获取索引的基本信息。

实验1：通过管理平台获取索引信息

第一步：选择course表，展开其索引项，右键选择“属性”项，如图1-29所示。

图1-29 查看course表的索引属性

第二步：在展开的course表索引信息界面既可以查看索引的相关信息内容，如图1-30所示。

图1-30 course表的索引属性具体内容界面

实验2：通过命令行获取索引信息

通过命令行获取某张表的索引信息和获取该表的全部信息，都需要依赖于系统存储过程sp_help和sp_helpindex。例如查询学生表的基本信息：

--查询学生表的全部信息，如图1-31为查询结果界面：

exec sp_help student

图1-31 student表的基本情况反馈

--仅查询学生表的索引信息

exec sp_helpindex student

2．优化索引

数据库经过一段时间的运行后，部分表的检索性能明显下降，我们可以通过查看这些表的事务日志，判别表索引是否出现大量的碎片，如果出现则需要我们对当前的索引进行优化，以提升这些表的检索速度。具体的查看碎片方法可以参见实验1的方法进行。

SQL Server2005通过数据库引擎优化顾问进行索引的优化工作，数据库引擎优化顾问是一种工具，用于分析在一个或多个数据库中运行的工作负荷的性能效果。工作负荷是对要优化的数据库执行的一组 Transact-SQL 语句。分析数据库的工作负荷效果后，数据库引擎优化顾问会提供在 Microsoft SQL Server 数据库中添加、删除或修改物理设计结构的建议。这些物理性能结构包括聚集索引、非聚集索引、索引视图和分区。实现这些结构之后，数据库引擎优化顾问使查询处理器能够用最短的时间执行工作负荷任务，如图1-32所示为数据库引擎的基本工作原理。