索引的概念和创建索引例子

1 索引的概念

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。表的存储由两部分组成，一部分用来存放数据页面，另一部分存放索引页面。通常，索引页面相对于数据页面来说小得多。数据检索花费的大部分开销是磁盘读写，没有索引就需要从磁盘上读表的每一个数据页，如果有索引，则只需查找索引页面就可以了。所以建立合理的索引，就能加速数据的检索过程。

SQL Server采用B-树结构的索引，根据索引的顺序与数据表的物理顺序是否相同可以分为：聚簇索引（clustered index）和非聚簇索引（nonclustered index）。

（1）聚簇索引重新组织表中的数据以按指定的一个或多个列的值排序。聚簇索引的叶节点包含实际的数据，因此用它查找数据很快，但每个表只能建一个聚簇索引。

（2）非聚簇索引不重新组织表中的数据，它的叶节点中存储了组成非聚簇索引的列的值和行定位指针。一个表可以建249 个非聚簇索引。

通俗的说，汉语字典的正文就是一个建立在拼音基础上的聚簇索引，以英文字母“a”开头并以“z”结尾。比如，我们要查“阿”字，就会翻开字典的第一页，因为“阿”的拼音是“a”，所以排在字典的前面。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明字典中没有这个字。同样的，如果查“做”字，就会把字典翻到最后。

字典的“偏旁部首”是非聚簇索引。比如我们要查“阿”字，在查部首之后，看到部首检字表中“阿”的页码是1页，“阿”的上面是“际”字，但页码却是277页，“阿”的下面是“陇”字，页码是416页。很显然，这些字并不是真正的分别位于“阿”字的上下方，现在看到的连续的“际、阿、陇”三字实际上就是他们在非聚簇索引中的排序，是字典正文中的字在非聚簇索引中的映射。

2 索引的使用

1）聚簇索引的使用

在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、<、<=、>、>=)或使用group by、order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行必然连在一起，不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。

聚簇索引的侯选列是：

u        经常按范围存取的列，如date>”20050101” and date< “20050131”；

u        经常在where子句中使用并且插入是随机的主键列；

u        在group by或order by中使用的列；

u        在连接操作中使用的列。

2）非聚簇索引的使用

由于非聚簇索引的叶级点不包含实际的数据，因此它检索效率较低，但一个表只能建一个聚簇索引，当用户需要建立多个索引时就需要使用非聚簇索引了。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。

在下面情况中使用非聚簇索引：

u        常用于集合函数(如Sum,....)的列；

u        常用于join, order by, group by的列；

u        查寻出的数据不超过表中数据量的20%。

3）创建索引需要注意的要点

1)    慎重选择作为聚簇索引的列

默认情况下，SQL Server用主键创建聚簇索引。这种做法常常造成聚簇索引的浪费。通常，我们会为每个表建立一个ID列，以区分每条数据，并且该列是自动增大的，步长一般为1。如果我们把这个列设为主键，SQL Server会将此列默认为聚簇索引。这样做可以使数据在数据库中按ID进行物理排序，但这种做法在实际应用中意义并不大。根据前面谈到的聚簇索引的定义和使用情况可以看出，使用聚簇索引的最大好处就是能够根据查询要求，迅速返回某个范围内的数据，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们不太可能用ID号来进行查询。这就使聚簇索引成为摆设，造成资源浪费。其次，让每个值都不同的ID列作为聚簇索引也不符合“大数目的不同值情况下不应建立聚簇索引”规则。

一般情况下，数据库应用系统进行数据检索都离不开“ 用户名（代码）”、“日期”字段。以笔者所用的HIS系统（医院管理信息系统）为例，我们进行费用、处方、检查单等信息检索时需要根据“住院号”和“日期”这两个字段来返回特定范围内的数据。下面我们分几种情况观察在不同索引条件下查询相同内容所用的时间。

假设病人费用表名为“brfy”，其中住院号字段名为“zyh”，日期字段名为“riqi”，要求是从表brfy中检索zyh为“028246”的病人2005年3月1日到20日的费用，对应的SQL语句如下：

Select * from brfy where zyh=’028246’ and riqi>=’20050301’ and riqi<=’20050320’;

第一种情况，用ID列建立聚簇索引，不为zyh和riqi建立索引，查询时间为87秒。

第二种情况，用ID列建立聚簇索引，为zyh和riqi两列建立非聚簇索引（zyh在前），查询时间为33秒。

第三种情况，用zyh和riqi两列建立聚簇索引（zyh在前），查询时间为2秒。

由以上分析可以看出聚簇索引是非常宝贵的，应该为经常用于检索某个范围内数据的列或group by、order by等子句的列建立聚簇索引，这样能够极大的提高系统性能。

2)    重视以多个列创建的索引中列的顺序问题

一些用户认为只要合理的选择列建立索引，不必关心列的顺序就可以提高检索速度，这种观点是错误的。多列索引中列的先后顺序应该和实际应用中where、group by或order by等子句里列的放置位置相同。参考上面举的例子，在第二、第三种情况下，如果把riqi放在zyh前面，执行上述SQL语句就不会用到这两个索引，检索的时间也会变得很长。

3 索引的维护

数据库系统运行一段时间后，随着数据行的插入、删除和数据页的分裂，索引对系统的优化性能就会大大降低。这时候，我们需要对索引进行分析和重建。

SQL Server使用DBCC SHOWCONTIG确定是否需要重建表的索引。在 SQL Server的查询分析器中输入命令：

Use database_name

Declare @table_id int

Set @table_id=object_id ('Employee')

Dbcc showcontig (@table_id)

在命令返回的参数中Scan Density 是索引性能的关键指示器，这个值越接近100％越好，一般在低于90％的情况下，就需要重建索引。重建索引可以使用DBCC DBREINDEX，使用方式如下：

dbcc dbreindex('表名', 索引名, 填充因子)       /*填充因子一般为90或100*/

如果重建后，Scan Density还没有达到100%，可以重建该表的所有索引：

dbcc dbreindex('表名', '', 填充因子)

在良好的数据库设计基础上，有效地使用索引是数据库应用系统取得高性能的基础。然而，任何事物都具有两面性，索引也不例外。索引的建立需要占用额外的存储空间，并且在增、删、改操作中也会增加一定的工作量，因此，在适当的地方增加适当的索引并从不合理的地方删除次要的索引，将有助于优化那些性能较差的数据库应用系统。实践表明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。

1.创建表并插入数据

在Sql Server2008中创建测试数据库Test，接着创建数据库表并插入数据，sql代码如下：

USE Test
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'emp_pay')
   DROP TABLE emp_pay
GO
USE Test
IF EXISTS (SELECT name FROM sys.indexes 
      WHERE name = 'employeeID_ind')
   DROP INDEX emp_pay.employeeID_ind
GO
USE Test
GO
CREATE TABLE emp_pay
(
 employeeID int NOT NULL,
 base_pay money NOT NULL,
 commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
   VALUES (1, 500, .10)
INSERT emp_pay 
   VALUES (2, 1000, .05)
INSERT emp_pay 
   VALUES (6, 800, .07)
INSERT emp_pay
   VALUES (5, 1500, .03)
INSERT emp_pay
   VALUES (9, 750, .06)

执行完上述sql代码以后我们会发现在Test数据库中多出了一张emp_pay表，数据库表的内容如下图所示：

2.无索引查找

从上图我们可以看出数据库中存储的数据排列顺序与我们插入的先后顺序一致。接下来我们查询employeeID=5的字段，执行如下sql代码：

USE Test
SELECT * FROM emp_pay where employeeID=5

在SQL SERVER MANAGEMENT STUDIO中我们点击“显示估计的查询计划”，会出现如下图所示的查询计划图：

其中表扫描的内容为：

3.创建索引

接下来我们为上述表添加聚集唯一索引，代码如下：

SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
   ON emp_pay (employeeID)
GO

在执行完上述创建索引的代码以后，我们再次查询emp_pay的数据内容，如下图所示：

从上图我们可以发现数据内容已经按照employeeID进行了排序。

我们继续执行前面关于employeeID=5的查询，点击“显示估计的执行计划”，出现如下图所示内容：

聚集索引查找的内容为：

总结：

当我们为数据库表中的某一个字段创建索引，并且在查询语句中where子句中用到这样一个字段，那么查询效率会有所提高，我们上述实验因为数据量的关系查询效率提高不明显。

补充

我们上面添加的索引是唯一聚集索引，因此当插入的数据在employeeID字段出现重复时会报错。假如我们在创建索引之前数据字段出现重复，那么就不能创建唯一索引。

创建索引以后的排序（PS:2012-5-28）

执行如下sql语句

update emp_pay set employeeID=7 where employeeID=1;

然后再次执行全表查询，我们发现查询结果如下所示：

只要我们更新了employeeID，那么最后的更新结果都会按照employeeID的值进行升序排序。这是因为我们在employeeID上创建了索引的缘故。

删除索引（PS:2012-6-4）

我们可以通过sql server management studio这个工具删除索引，也可以通过sql语句进行索引的删除，假设我们要求删除在前面创建的索引employeeID_ind，那么sql语句如下代码所示：

DROP INDEX employeeID_ind ON emp_pay;