摘要：SQLSrvr是占有市场份额较大的一个关系数据库管理系统。本文讨论在数据库设计阶段的一项优化技术，估算非聚集索引的大小。索引的设计将最终决定数据库运行的性能。除去必要的聚集索引，非聚集索引也是数据库设计中的重要环节。
关键词：SQLSrvr；数据库大小；非聚集索引；估算非聚集索引大小
中图分类号：G6420文献标志码：A文章编号：674-9324（208）6-0268-03
SQLSrvr作为微软的数据库管理系统主要解决了中小企业数据管理需求。在设计应用软件的数据库部分时，我们经常遇到一个问题，那就是：数据库的初始大小定义多少更合理呢？这就是估算数据库大小的问题。数据库的大小由数据库中所有表的大小决定，即数据库的大小是全部数据表之和。估算数据表占用存储空间的大小时，有如下两种情况：（）没有设计主键的表我们称之为堆；（2）设计了主键（唯一聚集索引）的表。在这两种表中我们都可以为经常查询的字段或字段组合创建索引，这种索引属于非聚集索引（可以唯一也可以不唯一）。
在SQLSrvr中一个表有且仅有一个唯一聚集索引就是主键，但是可以有多个非聚集索引，针对应用程序中经常查询需要用到的字段或字段组合我们可以为其设计非聚集索引。本文以studt表为例，为经常需要查询的字段“姓名”建立非唯一非聚集索引，并通过3个步骤对该非聚集索引的大小进行估算。首先说明SQLSrvr中对索引的存储。当我们为表创建了一个非聚集索引时，数据库管理系统将建立起一颗B-树用来存储该索引。在B-树中有两类节点：（）非叶级节点；（2）叶级节点。非叶级节点保存了键值之间的排列关系，而叶级节点用来存储指针（该指针指向记录的实际存储位置）。所以两类节点的估算方法不同。我们先用T-SQL语句声明studt表的表结构并为“姓名”字段设计非聚集索引。
CREATETABLEstudt
（Stu_ubrCHAR（6）CONSTRAINTPK_ubr_STUDENTPRIMARYKEYNOTNULL，
NVARCHAR（20）NOTNULL，--非聚集索引的索引关键字（索引键），只有一个字段
SpltyCHAR（20）NOTNULL，
GdrBITNOTNULLCONSTRAINTDFT_Gdr_STUDENTDEFAULT，--注釋：男，0女
BrthdySMALLDATETIMENOTNULL，
Ttl_rdtsTINYINTNULL
）--创建studt表
GO
CREATEINDEXIND__STUDENTONstudt（N）--为“姓名”字段创建非聚集索引，索引的名字为IND_NAME_STUDENT，考虑到现实中的重名的情况，该索引不唯一。
下面我们就通过3个步骤来计算studt表的IND__STUDENT索引所占用的存储空间。
一、计算用于存储非聚集索引的非叶级节点的空间
预估表中的行数：Nu_Rs=，000，000，因该表定义了聚集索引（主键），当行数超过0，000，000时，存储聚集索引的B树深度会增加，使得存储空间与记录行之间不再是线性渐变关系，因此我们将表中的行数设定在，000，000。此问题可参看《RltlDtArhtturRfdthStrSpEstt》。
2指定索引键中固定长度和可变长度列的数量，并计算存储所需的空间：索引键列可以包括固定长度和可变长度列。要估计内部级别索引行的大小，需计算每组列在索引行中所占据的空间。列的大小取决于该列的数据类型和长度。
Nu_Ky_Cls=总键列数（固定长度和可变长度）=lu
Fxd_Ky_Sz=所有固定长度键列的总字节大小=0byt
Nu_Vrbl_Ky_Cls=可变长度键列的数量=lu
Mx_Vr_Ky_Sz=所有可变长度键列的最大字节大小=20byt
3如果索引不是唯一的，对数据行定位符的计算方法如下：如果非聚集索引不是唯一的，数据行定位符将与非聚集索引键组合使用，以便为每一行生成唯一的键值。
（）如果非聚集索引在堆上，则数据行定位符是堆RID。其大小是8个字节，公式如下。但在stdut表中由于定义了主键，因此IND__STUDENT索引不是定义在堆上，不适用此种情况，只给出公式不做计算。
Nu_Ky_Cls=Nu_Ky_Cls+
Nu_Vrbl_Ky_Cls=Nu_Vrbl_Ky_Cls+
Mx_Vr_Ky_Sz=Mx_Vr_Ky_Sz+8
（2）如果非聚集索引在聚集索引之上，则数据行定位符是聚集键。必须与非聚集索引键结合使用的列是聚集键中的以下列：不在非聚集索引键列集中的列。Studt表符合该情况，因此计算如下：
Nu_Ky_Cls=Nu_Ky_Cls+不在非聚集索引键列集中的聚集键列数（如果聚集索引不唯一，则加）=+=2
Fxd_Ky_Sz=Fxd_Ky_Sz+不在非聚集索引键列集中的固定长度聚集键列的总字节大小=0+6=6byt
Nu_Vrbl_Ky_Cls=Nu_Vrbl_Ky_Cls+
不在非聚集索引键列集中的可变长度聚集键列数（如果聚集索引不唯一，则加）=+0=
Mx_Vr_Ky_Sz=Mx_Vr_Ky_Sz+不在非聚集索引键列集中的可变长度聚集键列的最大字节大小（如果聚集索引不唯一，则加4）=20+0=20byt
说明：studt表的主键PK_ubr_STUDENT是唯一聚集索引，它只有固定长度为6byt的个列，没有可变长度的列，因此：不在非聚集索引键列集中的聚集键列数为，不在非聚集索引键列集中的固定长度聚集键列的总字节大小为6byt，不在非聚集索引键列集中的可变长度聚集鍵列数=0lu，不在非聚集索引键列集中的可变长度聚集键列的最大字节大小=0byt。
4保留行的一部分（称为“空位图”），以管理列的为空性。计算大小：如果索引键中有可为空的列（包括步骤一3中所述的所有必要的聚集键列），则保留索引行的一部分，以用于空位图。
Idx_Null_Btp=2+（（可为空值的键列数+7）/8）对表达式取整。如果没有可为空的键列，将Idx_Null_Btp设置为0。由于IND__STUDENT的索引键中没有可以为空的列。所以将Idx_Null_Btp=0
5计算可变长度数据大小：如果索引键中有可变长度的列（包括所有必要的聚集索引键列），确定存储索引行中的这些列需使用的空间：Vrbl_Ky_Sz=2+（Nu_Vrbl_Ky_Cls×2）+Mx_Vr_Ky_Sz此时我们假定页的填满度为00%。如果页的填满度低，可以按照比例调整Mx_Vr_Ky_Sz值，从而对整个表大小得出一个更准确的估计。如果没有可变长度列，将Vrbl_Ky_Sz设置为0。在studt表中，Vrbl_Ky_Sz=2+（×2）+20=24byt
6计算索引行大小：Idx_R_Sz=Fxd_Ky_Sz+Vrbl_Ky_Sz+Idx_Null_Btp
+（对应于索引行的行标题开销）+6（对应于子页ID指针）=6+24+0++6=37byt
7计算每页的索引行数（每页可存储8096字节）：Idx_Rs_Pr_P=8096/（Idx_R_Sz+2）由于索引不能跨页断行，因此每页的索引行数向下取整。公式中的数值2是计算行数时引入的行大小余量。Idx_Rs_Pr_P=8096/（37+2）=207r
8计算索引中的级别数（即B-树的深度）：Lvls=+lIdx_Rs_Pr_P（Nu_Rs/Idx_Rs_Pr_P）=+l207（，000，000/207）=59≈2hht
9计算存储索引所需的页数：Nu_Idx_Ps=Lvl（Idx_Rs_Pr_P）其中，<=Lvl<=Lvls，本例中Nu_Idx_Ps=207+207=208p
0计算用于存储非聚集索引的非叶级节点的空间大小（每页可存储892字节）：Idx_Sp_Usd=892×Nu_Idx_Ps=892×208=，703，936byt=625MB≈2MB
二、计算用于存储非聚集索引的叶级节点的空间
指定叶级的固定长度列和可变长度列的数量，并计算存储这些列所需的空间：如果非聚集索引的索引键是单一的列，则使用步骤中的值（以步骤一3中修改后为准）：
Nu_Lf_Cls=Nu_Ky_Cls=2lu
Fxd_Lf_Sz=Fxd_Ky_Sz=6byt
Nu_Vrbl_Lf_Cls=Nu_Vrbl_Ky_
Cls=lu
Mx_Vr_Lf_Sz=Mx_Vr_Ky_Sz=20byt
如果非聚集索引的索引键是多个列的组合，并且这些列中既有可变长度列，也有固定长度列，则对步骤中的值加上适当的值（以步骤一3中修改后为准）。列的大小取决于数据类型和长度的规定。
Nu_Lf_Cls=Nu_Ky_Cls+包含列数
Fxd_Lf_Sz=Fxd_Ky_Sz+固定长度包含列的总字节大小
Nu_Vrbl_Lf_Cls=Nu_Vrbl_Ky_
Cls+可变长度包含列数
Mx_Vr_Lf_Sz=Mx_Vr_Ky_Sz+可变长度包含列的最大字节大小
本示例的IND__STUDENT索引键只有一个可变长度VARCHAR（20）的列NAME，因此无需增加数值。
2数据行定位符的计算：如果非聚集索引不是唯一的，若已在步骤一3中考虑了数据行定位符的开销且不需要进行其他的修改，则转到下一步。如果非聚集索引是唯一的，则必须在叶级的所有行中说明数据行定位符。
（）如果非聚集索引在堆上，则数据行定位符是堆RID（大小为8字节）。
Nu_Lf_Cls=Nu_Lf_Cls+
Nu_Vrbl_Lf_Cls=Nu_Vrbl_Lf_
Cls+
Mx_Vr_Lf_Sz=Mx_Vr_Lf_Sz+8
（2）如果非聚集索引在聚集索引之上，则数据行定位符是聚集键。必须与非聚集索引键结合使用的列是聚集键中的以下列：不在非聚集索引键列集中的列。
Nu_Lf_Cls=Nu_Lf_Cls+不在非聚集索引键列集中的聚集键列数（如果聚集索引不唯一，则加）
Fxd_Lf_Sz=Fxd_Lf_Sz+不在非聚集索引键列集中的固定长度聚集键列数
Nu_Vrbl_Lf_Cls=Nu_Vrbl_Lf_
Cls+不在非聚集索引键列集中的可变长度聚集键列数（如果聚集索引不唯一，则加）
Mx_Vr_Lf_Sz=Mx_Vr_Lf_Sz+不在非聚集索引键列集中的可变长度聚集键列的字节大小（如果聚集索引不唯一，则加4）
说明：由于非聚集索引IND__STUDENT的键值不是唯一的（考虑现实中重名的情况），因此跳过二2步骤，转到二3步骤。
3计算空位图大小：Lf_Null_Btp=2+（（Nu_Lf_Cls+7）/8）对表达式向下取整，因此Lf_Null_Btp=2+（（2+7）/8）=325≈3
4计算可变长度数据大小：如果索引键中有可变长度的列（包括步骤二2中必要的聚集索引键列），确定其需要的存储空间：Vrbl_Lf_Sz=2+（Nu_Vrbl_Lf_Cls×2）+Mx_Vr_Lf_Sz此時我们假定页的填满度为00%。如果页的填满度低，可以按照比例调整Mx_Vr_Lf_Sz的值，从而对整个表大小得出一个更准确的估计。如果没有可变长度的列，则将Vrbl_Lf_Sz设置为0。本示例中Vrbl_Lf_Sz=2+（×2）+20=24byt。
5计算索引行大小：Lf_R_Sz=Fxd_Lf_Sz+Vrbl_Lf_Sz+Lf_Null_
Btp+（对应于索引行的行标题开销）+6（对应于子页ID指针）=6+24+3+=34byt
6计算每页的索引行数（每页可存储8096个字节）：Lf_Rs_Pr_P=8096/（Lf_R_Sz+2）由于索引不能跨页断行，因此每页的索引行数向下取整。公式中的数值2是计算行数时引入的行大小余量。本示例中Lf_Rs_Pr_P=8096/（34+2）≈224r
7假定页的填充因子并计算每页保留的空行数：Fr_Rs_Pr_P=8096×（（00-Fll_Ftr）/00）/（Lf_R_Sz+2）注意：填充因子为整数，不是百分比。由于索引不能跨页断行，因此每页的行数向下取整。公式中的数值2是计算行数时引入的行大小余量。本示例中假定Fll_Ftr为60，因此Fr_Rs_Pr_P=8096×（（00-60）/00）/（34+2）≈89r。即每页需保留89个空行。
8计算存储所有行所需的页数：Nu_Lf_Ps=Nu_Rs/（Lf_Rs_Pr_P-Fr_Rs_Pr_
P）结果需向上取整。本示例中Nu_Lf_Ps=，000，000/（224-89）=7408p
9计算叶级节点的空间大小（每页可储存892字节）：Lf_Sp_Usd=892×Nu_Lf_Ps=
892×7408≈58MB
三、对从前面两个步骤中得到的值求和
Nlustrddxsz（byts）=Lf_Sp_Usd+Idx_Sp_usd=2+58=60MB
通过计算可知，对于一个具有，000，000条记录的studt表来说，我们创建一个非聚集索引IND__STUDENT（不唯一）时，SQLSrvr为存储该索引所需要的存储空间大约是60MB。相对于存储表的空间，60MB的空间开销是很小的，但它可以大大提升我们对“姓名”字段的查找速度。因此，为数据表中经常需要查询的字段或字段组合设计好非聚集索引，可以大大提高查询效率。
一个表的空间大小是可估的，（）若表无主键，则表大小=堆大小+非聚集索引大小；（2）有主键，则表大小=聚集索引大小+非聚集索引大小。表空间大小可估，数据库的空间大小就可估了。因此给数据库一个合理的初始大小是可行的也是必要的。
参考文献：
[]岳莉在SQLSrvr中估算堆大小[J]教育教学论坛，203，5（22）：46-47
[2]LyuThKyFtrsfMthtlFrulAfftthSzfClustrdIdxAdvdMtrlsRsrhVl962-965（204）：2877-2880
文章来源于：教育教学论坛