关于SQL Server 2000 索引及其特性的简析

SQL Server 2000索引及其特性的简析Tang, Dunyou, 28 May 2003摘要本文简要地介绍了SQL Server 2000聚族（Clustered）和非聚族〔Nonclustered〕索引（Index）的机制以及其物理结构。

并且，通过一个演示数据库（Database）的索引建立，数据的测试, 以及测试的结果，归纳了两种索引的一些主要特性。

使读者了解在设计数据库和索引时值得注意考虑的事项。

目录1. 引言2. SQL Server 2000 索引的概述3. 测试解析4. 测试结果的归纳附录A: SQL Server 2000构造体系涉及索引的主要技术指标的上限附录B: 批文件（dtindexlab2k1.bat）附录C: SQL脚本程序（dtBuildDBTest1.sql）附录D: SQL脚本程序（dtBuildTableStock.sql）附录E: 数据格式文件（dtbcpv2k.fmt）附录F: 数据文本文件（dtStockData.txt）附录H: SQL脚本程序（IndexTest.SQL）参考信息1. 引言建立索引（Index）是一项矛盾的对立统一。

理论上讲，从数据的检索（Search），查询（Query）方面来说，只要存储空间允许，建立的索引越多，检索的速度就越快，效率也越高。

但另一方面，从对数据的添加（Add），删除（Delete）和修改（Modify）的角度来说，索引又应控制在越少越好。

通俗的讲，每个索引实际上是一个系统管理表。

每次添加，删除和修改数据，SQL Server都自动更新这张索引表。

这就反过来影响了检索的速度和性能。

所以，设计和建立索引不仅是设计（Design），开发（Developing），调试（Tuning）关系型数据库〔Relational Database〕的重要组成部分，而且是最普遍，最困难的问题之一。

SQL Server 2000 (例如，Enterprises 版本)提供了一些方便，有效的GUI （Graph User Interface）辅助工具像Enterprise Manager, Query Analyser, Profiler 等，来帮助开发者〔Developer〕和管理者（DBA-Database Administrator）开发，调试，优化以及管理数据库。

本文不是就如何设计索引和性能的优化调试作具体介绍，而是想通过一些SQL 脚本（SQL Scripts）的演示来说明索引的一些特性，帮助加深对索引的理解，以及在设计索引需考虑的问题。

2. SQL Server 2000 索引的概述索引是一种有效组织数据记录的方法。

索引建立的好坏直接影响数据库的运行性能（Performance）。

特别是对于大型，复杂的关系型数据库，索引的设计和建立更是必不可少。

因为索引的设计以及关系型数据库性能的优化调试，没有固定的模式可寻，所以，从某种意义上说，索引的设计和数据库性能的优化调试较数据库本身的设计（数据库结构设计:数据表（Table）以及其关系正交化（Normalization））更为困难。

SQL Server 的索引根据其数据存储的物理结构分为聚族和非聚族索引。

无论是聚族还是非聚族索引存储的机理都是按照平衡树（Balanced-tree，或B-tree）的索引结构实现的。

其主要特点是在添加任何新页时，数据都是在旧页和新页上平衡分配。

SQL Server数据存储的最小物理单元称为页（Page）。

SQL Server 2000，将页的尺寸由原来的2K（版本7.0）增加到8K。

各页之间都以双向链表实现。

B树索引结构的搜寻是逐级进行。

及从根（Root），中间级（Intimidate），叶（Leaf），最后找到数据（Data）。

打个简单的比喻，聚族索引就像一本英文字典。

所有的数据都以字母排序。

在查找单词时，只要根据单词字母的排序直接对页进行查找。

所以，每个数据表只能有一个聚族索引。

而非聚族索引可被看作一本书。

当查找内容时，可快速地通过目录找到查找内容的页码，再根据页码找到查寻的内容。

此外，也可以对全书进行浏览查找。

SQL Server 2000的每个数据表最多能有249个非聚族索引。

显而易见，一般情况下，聚族索引查找速度要比非聚族索引快。

由于篇幅有限，这里不作深层展开。

对此问题感兴趣的读者，可参阅本文最后所列的或其他的有关书籍。

3. 测试的解析本文的测试基于一个取名为股票〔Stocks〕的演示数据表（Table）。

该表很简单，只含两个数据项〔field〕，股票-StockName和股票的编号-SedolCode〖1〗。

表中的数据是从伦敦国际股市交易所列有的股票中随取了12，000种。

〖1〗SEDOL 的是Stock Exchange Daily Official List的缩写。

Sedol Code是伦敦股市常用的股票编码之一。

为了读者方便起见，本文从附录B到附录F，列出了所有用于创建演示数据库和数据表的批文件（Batch file）dtindexlab2k1.bat和SQL脚本程序（Scripts）。

如果读者手头没有现成的数据或觉得用批文件和BCP太麻烦，本文在附录G中给出了一个SQL脚本直接建立一个数据库和一个模拟数据表（包含12,000个模拟数据）。

这同样可以达到测试并分析结果的目的。

对于批文件与批量复制程序BCP不熟悉的读者，可参阅本作者的「批文件与BCP一种复制大批量数据的方法」一文，也可参阅本文最后所列的或其他的有关书籍。

注：为了使所有的程序能在你的PC上成功地运行，请按你具体的情况对机器名，文件路径，数据库名，用户名以及口令作相应的替换。

在建立了数据库TEST1和数据表Stocks后，就可以来建立和测试不同的索引了。

下面就脚本程序（IndexTest.SQL）如何建立各种不同的索引及其组合，并测试各种情况下的一些指标逐步进行讨论。

注: 附录H列出了完整SQL脚本程序（IndexTest.SQL）。

1)打开Query Analyser，并关闭方案开关SHOWPLAN_ALL。

SET SHOWPLAN_ALL OFF2)建立一个临时表，并保留测试将要重复删除和插入的数据。

use testdrop table tempstCREATE TABLE tempdc (stockname varchar(30), sedolcode varchar(10))INSERT tempst SELECT * FROM stocks WHERE sedolcode = '4455'A. 无任何索引的数据表3)删除已有的非聚族索引st_ncsedolcode。

DROP INDEX stocks.st_ncsedolcode4)打开出入通道，运行时间的统计开关STATISTICS IO，STATISTICSTIME。

SET STATISTICS IO ONSET STATISTICS TIME ON5)利用系统的存储过程（Stored Procedure）sp_spaceused列出并观测数据和索引占用的存储空间。

sp_spaceused stocks6)执行对数据表的搜寻，删除和插入数据。

观测逻辑读取次数和运行时间。

print '** Select'SELECT * FROM stocks WHERE sedolcode = '4455'print '** Delete'DELETE FROM stocks WHERE sedolcode = '4455'print '** Insert'INSERT stocks SELECT * FROM tempstB. 含有一个聚族索引的数据表7)对SedolCode项（Field）建立一个聚族索引st_clsedolcode。

CREATE CLUSTERED INDEX st_clsedolcode ON stocks(sedolcode)8)重复步骤5，6。

即在该表状况下，列出并观测数据和索引占用的存储空间。

执行对数据表的搜寻，删除和插入数据，观测分别的逻辑读取次数和运行时间。

C. 含有一个非聚族索引的数据表9)删除已有的聚族索引st_clsedolcode并对SedolCode项建立一个非聚族索引st_ncsedolcode。

DROP INDEX stocks.st_clsedolcodeCREATE INDEX st_ncsedolcode ON stocks(sedolcode)10)重复步骤5，6。

列出和观测在该表状况下，数据和索引占用的存储空间。

在执行对数据表的搜寻，删除和插入数据时，分别的逻辑读取次数和运行时间。

D. 含有一个聚族索引和一个非聚族索引的数据表11)对SedolCode项加入一个聚族索引st_clsedolcode。

CREATE CLUSTERED INDEX st_clsedolcode ON stocks(sedolcode)12)同样，重复步骤5，6。

列出并观测数据和索引占用的存储空间。

执行对数据表的搜寻，删除和插入数据。

观测在该表状况下，分别的逻辑读取次数和运行时间。

E. 含有一个聚族索引和二个非聚族索引的数据表13)这里对SedolCode项加入第二个非聚族索引st_ncsedolcode2。

CREATE INDEX st_ncsedolcode2 ON stocks(sedolcode)14)再次重复步骤5，6。

列出并观测数据和索引占用的存储空间。

执行对数据表的搜寻，删除和插入数据，观测在该状况下，逻辑读取次数和运行时间分别的变化。

F. 含有一个聚族索引和三个非聚族索引的数据表15)这里对项StockName加入了一个非聚族索引st_ncstockname。

CREATE INDEX st_ncstockname ON stocks(stockname)16)最后，再次重复步骤5，6。

得出各项结果。

4. 测试结果的归纳表1列出了各种情况下的测试结果。

表1：测试结果现在，只要对表1的数据略加分析，就不难归纳出下面的一些结论：1)在一个数据表中，随着索引的增加，数据表所占用的存储空间将随着增加。

可以想像这是自然的！2)聚族索引比非聚族的效率要高－即逻辑读取次数要少，检索的响应时间要快。

3)对同一表格项（Field）来说，非聚族索引所占用的存储空间要多于聚族索引所占用的存储空间。

4)在一个数据表中，建立的非聚族索引越多，在使用删除（DELETE）和插入（INSERT）时，读取的次数就越多。

从该表中，你还捕捉到一些什么其他结论？从这些结论中你又可以得到一些什么推论？希望这些对读者在今后设计数据库和索引时有些帮助。