索引基础
索引的创建和使用
索引的删除
除表以外，索引可能就是大型数据库系统中最重要的对象了。索引是一种树型结构，
如果使用正确，可以减少定位和查询数据所需的I/O操作。另一种说法是，索引可以加
快表中查找数据记录的速度。
索引
第章
SQL技术与网络数据库开发详解
·58·
5.1索引基础
索引是一种数据库对象。在有大量记录的数据表中查询数据时，如果使用索引可以很快查到想要
的数据。索引还有另外一种用途，那就是强制数据的唯一性。
5.1.1使用索引的原因
对于大部分数据库用户来说索引是一个非常陌生的概念。因为普通用户很少特意去使用索引，只
有那些管理着海量数据的DBA才会去特意创建索引和使用索引。使用索引有两个主要的原因：
null提供唯一的码值。
null提高查询性能。
当用户创建带有PRIMARYKEY或UNIQUE约束的数据表时，SQLServer或Oracle早已经在后
台为该表自动创建了唯一索引，并以此强制数据的唯一性。
使用索引能够提高性能的原因其实也很好理解。例如，要查询本书中关于Oracle的DECODE函数
的内容，可以使用两种方法。一种是从第1页开始一页一页地向后找；另一种是在目录中先找到
DECODE函数所在的页数，然后，直接翻到该页上。可想而知，在书比较厚的情况下，采用第二种方
法很快就能找到需要的内容。这里的索引就像本书的目录，因此使用索引会提高查询性能。
当然，假设本书只有3页，则使用第一种方法会更实惠。这就表明数据表中的记录越多，使用索
引就会得到越大的效益。反之，使用索引就没有什么价值了。
5.1.2索引的种类
SQLServer和Oracle等大型数据库系统，按存储结构的不同将索引分为两类，即聚簇索引和非聚
簇索引。
1．聚簇索引
一个聚簇索引就是一个在物理上与表融合在一起的视图。表和视图共享相同的存储区域。聚簇索
引在物理上以索引顺序重新整理了数据的行。这种体系结构中的一个表只允许有一个聚簇索引。
在SQLServer中，删除和重建一个聚簇索引对于改造一个表来说是一个常用的技术，这是一种保
证数据页在磁盘上邻近的方法，同时，也是重建表中一些空闲空间的好方法。
SQLServer的聚簇索引和Oracle的聚簇索引完全不同。Oracle聚簇索引在一个Oracle块中同时存
储两个或多个表中的数据。在建立聚簇索引时，先创建一个聚簇，然后在该聚簇上创建一个索引，最
后在CREATETABLE语句中指定该表存储在这个聚簇上。聚簇码通常是用来连接这两个或多个表的
连接字段。也就是说，如果用户需要使用两个表中的数据，那么只需要存取这一个Oracle块即可，而
不需要先访问一个表，然后再访问另一个表。在SQLServer中没有与Oracle相似的结构。
2．非聚簇索引
在非聚簇索引中，索引数据和表数据在物理上是分离的，表中的记录并不按照索引中的顺序存储。
第5章索引
·59·
非聚簇索引的查询效率相对于聚簇索引来说比较低，但由于一个数据表只能创建一个聚簇索引，所以
当用户需要使用多个索引时就只能创建非聚簇索引了。
5.2索引的创建和使用
本节将介绍创建索引前应当注意的内容，以及创建索引的标准语法和SQLServer、Oracle中的扩
展语法。此外，还介绍了创建和使用非聚簇索引和唯一索引的内容。
5.2.1创建索引前应当注意的内容
实际上，使用索引会提高查询性能这句话是有前提的，就是说并不是所有情况下使用索引都能提
高查询性能。所以，在创建并使用索引前应当注意下面的几点内容。
null对于只有少量数据记录的表或在Oracle中占有小于10个Oracle块的表来说，使用索引查询数
据没有任何好处。应当省掉存取和使用索引块的开销，直接执行全表扫描得到表中的所有数据，
这样会更快一些。
null如果索引字段中有很多不同的数据值和空值时，使用索引会极大地提高性能。
null如果执行查询后，返回的数据记录很少，则索引可以优化该查询。比较好的情况是返回记录数
少于全部数据的25%（根据DBMS的不同配置，该数字有所不同）。如果返回的数据记录很
多，则使用索引不会得到太多的好处。
null索引可以提高查询数据的速度，但它也降低了数据的更新速度。因此，如果要进行大量的更新
操作，在执行更新操作前应该删除一些不必要的索引，在更新完毕后再重新创建索引，这样会
提高效率。
null索引也会占用数据库空间，所以在设计数据库的可用空间时应当考虑索引所占用的空间。
null在某字段上创建索引时，应当考虑是否经常使用该字段筛选记录。如果不是，则不应该创建索
引，因为该索引不会起什么作用，反而在修改数据时会影响性能。
null尽量不要对经常需要更新或修改的字段创建索引，更新索引的开销会降低期望获得的性能。
null尽量不要将索引与表存储在同一个驱动器上，分开存储会避免访问冲突，从而能够提高性能。
5.2.2创建索引的SQL语句
在不同的DBMS中，创建索引通常都可以使用两种方式：一是GUI方式，二是SQL命令方式。
本小节要介绍的是使用SQL命令方式建立索引的方法。使用SQL语句创建索引的语法如下所示。
CREATEINDEXindex_name
ONtable_name(column[,...n])
具体说明如下：
nullindex_name：索引的名称。在SQLServer中，索引的名称在表内必须唯一，但在数据库中不
必唯一。在Oracle中，索引的名称在用户内必须唯一。
SQL技术与网络数据库开发详解
·60·
nulltable_name：包含将要在其上创建索引的字段的表。
nullcolumn：将要在其上创建索引的字段。这个位置可以放置多个字段，此时，创建的索引被称为
复合索引。
SQLServer和Oracle对上面创建索引的语句有不同的扩展。例如，在SQLServer中，创建索引的
语法格式为：
CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_name
ON{table|view}(column[ASC|DESC][,...n])
[WITH{PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|
STATISTICS_NORECOMPUTE|SORT_IN_TEMPDB}[,...n]]
[ONfilegroup]
而在Oracle中，创建索引的语法格式为：
CREATE[UNIQUE]INDEXindex_name
ONtable_name(column[ASC|DESC][,...n])
[INITRANSinteger]
[MAXTRANSinteger]
[TABLESPACEtablespace_name]
[STORAGEstorage_clause]
[PCTFREEinteger]
[NOSORT]
[RECOVERABLE|UNRECOVERABLE]
[PARALLELparallel_clause]
关于SQLServer和Oracle中创建索引的详细语法说明，请读者参考这两个数据库管理系统的参考
手册，在此不作详细说明。
5.2.3创建和使用非聚簇索引
在前面的内容中曾经提到，非聚簇索引的性能不如聚簇索引的性能好，但由于一个表只能创建一
个聚簇索引，因此还需要使用非聚簇索引。实际上，从大量的数据中查询数据时，使用非聚簇索引比
不使用索引要好得多。下面通过例题说明创建和使用非聚簇索引的方法，以及使用索引后查询性能的
改变。首先，创建例题中将要使用的TestIndex表，其创建语句如下所示。
CREATETABLEtestindex
(
c1char(1),
c2int
);
将上述语句，输入SQLServer的查询分析器中运行，其运行结果如图5.1所示。然后，使用下面
的语句向表TestIndex，插入100000（十万）条随机数据记录。
/声明整数变量@x/
DECLARE@xint
/给变量@x赋初值1/
第5章索引
·61·
SELECT@x=1
/循环十万次/
WHILE@x<=100000
BEGIN
/向数据表TestIndex插入随机数/
INSERTINTOtestindex
VALUES(CHAR(65+ROUND(RAND()24,0)),ROUND(RAND()100,0))
/给变量@x重新赋值/
SELECT@x=@x+1
END
说明：SQLServer中，在“/”和“/”之间的所有语句都是注释语句。也可以使用“--”注释语句。
运行结果如图5.2所示。
图5.1创建TestIndex表的运行结果图5.2向TestIndex表插入100000条记录的运行结果
【例5.1】比较在字段c2上创建非聚簇索引之前和创建索引之后的查询性能。
（1）在字段c2上创建非聚簇索引之前，运行下面的语句。
/声明日期时间型变量@x/
DECLARE@xdatetime
/赋给变量@x当前系统时间/
SELECT@x=GETDATE()
/执行查询语句，查找c2等于10的所有数据记录/
SELECTc2
FROMtestindex
WHEREc2=10
/显示查询语句所花费的时间/
SELECTGETDATE()-@x
运行结果如图5.3所示。
SQL技术与网络数据库开发详解
·62·
（2）在字段c2上创建非聚簇索引，其创建语句如下所示。
CREATEINDEXidx_testindex_c2
ONtestindex(c2)
运行结果如图5.4所示。
图5.3创建索引前查询花费的时间图5.4创建非聚簇索引的运行结果
说明：图5.3的结果为多次运行结果中，查询花费时间最短的一次。
（3）再次运行下面的语句。
/声明日期时间型变量@x/
DECLARE@xdatetime
/赋给变量@x当前系统时间/
SELECT@x=GETDATE()
/执行查询语句，查找c2等于10的所有数据记录/
SELECTc2
FROMtestindex
WHEREc2=10
/显示查询语句所花费的时间/
SELECTGETDATE()-@x
运行结果如图5.5所示。
图5.5创建索引后查询花费的时间
比较图5.3和图5.5中的结果可以发现，本例中使用了非聚簇索引后，大大提高了查询性能。
5.2.4创建和使用唯一索引
前面提到过，使用索引的主要原因之一就是提供唯一的字段值。唯一索引强制表中任意两条记录
的索引值互不相同。创建唯一索引需要使用UNIQUE关键字。下面通过例题说明创建和使用唯一索引
的方法。首先，创建例题中将要使用的TestUni表，其创建语句如下所示。
CREATETABLEtestuni
(
第5章索引
·63·
c1int,
c2int
);
【例5.2】在TestUni表的c1字段上创建一个唯一索引，并试验其效果。
（1）创建唯一索引idx_testuni_c1。
CREATEUNIQUEINDEXidx_testuni_c1
ONtestuni(c1)
运行结果如下所示。
命令已成功完成。
（2）执行如下插入语句，向表TestUni插入两条记录。
INSERTINTOtestuni
VALUES(10,20);
INSERTINTOtestuni
VALUES(20,20)
运行结果如下所示。
（所影响的行数为1行）
（所影响的行数为1行）
（3）执行下面的插入语句，准备向表TestUni插入一条记录。
INSERTINTOtestuni
VALUES(10,50);
运行结果如下所示。
服务器:消息2601，级别14，状态3，行1
不能在具有唯一索引''idx_testuni_c1''的对象''testuni''中插入重复键的行。
语句已终止。
通过本例可以知道，当向有唯一索引的表中字段（C1）插入非唯一值时，便会出现错误，这就很
好地保持了数据完整性。
5.3索引的删除
索引是一把双刃剑，虽然它提高了查询速度，但也降低了更新数据的速度，因为每当更新数据时，
都要维护一次索引。因此，当不再使用索引或者要向表插入大量数据时，应当删除索引。在SQL中，
删除索引的语法如下所示。
DROPINDEXindex_name
【例5.3】删除TestUni表的唯一索引idx_testuni_c1。
DROPINDEXtestuni.idx_testuni_c1
SQL技术与网络数据库开发详解
·64·
删除了唯一索引后再向表TestUni插入前面的数据。
INSERTINTOtestuni
VALUES(10,50);
运行下面的语句，查看TestUni表的内容。
SELECT
FROMtestuni
运行结果如图5.6所示。
图5.6TestUni表内容
从运行结果可以看出，删除了唯一索引后，就可以向表中过去的索引字段（C1）中插入相同值了。
|
|
