关系数据库的索引技术 -

andy136566

浏览: 290582 次
性别:
来自: 杭州

最近访客更多访客>>

formonitor

hhh_chang

horsehome

求学霸带走

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

关系数据库的索引技术

博客分类：

数据库

数据结构 SQL Server SQL Sybase Oracle

摘要：索引是影响关系数据库（RDBMS）性能的重要因素之一。当今常用的关系数据库，如SQL Server、Sybase、Oracle、DB2等，为了提高性能，都提供相应的索引机制。本文以微软的SQL Server数据库为例，介绍索引的概念、使用方法以及日常维护。

关键词：数据库、索引、检索

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%。

表1 索引使用情况分析表

情况描述	使用聚簇索引	使用非聚簇索引
用于返回某范围内数据的列	应	不应
经常被用作分组排序的列	应	应
小数目不同值的列	应	不应
连接操作使用的列	应	应
频繁更新、修改的列	不应	应
一个或极少不同值的列	不应	不应
大数目不同值的列	不应	应

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('表名', '', 填充因子)

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

看了这篇文章，面试还是被问到死，再看两篇，差不多就可以了：

http://www.cnblogs.com/KissKnife/archive/2009/03/30/1425534.html

http://blog.csdn.net/ant_yan/archive/2008/09/15/2932068.aspx

分享到：