在SQL Server中估算非聚集索引的大小

摘要：SQL Server是占有市场份额较大的一个关系数据库管理系统。本文讨论在数据库设计阶段的一项优化技术，估算非聚集索引的大小。索引的设计将最终决定数据库运行的性能。除去必要的聚集索引，非聚集索引也是数据库设计中的重要环节。

关键词：SQL Server；数据库大小；非聚集索引；估算非聚集索引大小

中图分类号：G642.0 文献标志码：A 文章编号：1674-9324（2018）16-0268-03

SQL Server作为微软的数据库管理系统主要解决了中小企业数据管理需求。在设计应用软件的数据库部分时，我们经常遇到一个问题，那就是：数据库的初始大小定义多少更合理呢？这就是估算数据库大小的问题。数据库的大小由数据库中所有表的大小决定，即数据库的大小是全部数据表之和。估算数据表占用存储空间的大小时，有如下两种情况：（1）没有设计主键的表我们称之为堆；（2）设计了主键（唯一聚集索引）的表。在这两种表中我们都可以为经常查询的字段或字段组合创建索引，这种索引属于非聚集索引（可以唯一也可以不唯一）。

在SQL Server中一个表有且仅有一个唯一聚集索引就是主键，但是可以有多个非聚集索引，针对应用程序中经常查询需要用到的字段或字段组合我们可以为其设计非聚集索引。本文以student表为例，为经常需要查询的字段“姓名”建立非唯一非聚集索引，并通过3个步骤对该非聚集索引的大小进行估算。首先说明SQL Server中对索引的存储。当我们为表创建了一个非聚集索引时，数据库管理系统将建立起一颗B-树用来存储该索引。在B-树中有两类节点：（1）非叶级节点；（2）叶级节点。非叶级节点保存了键值之间的排列关系，而叶级节点用来存储指针（该指针指向记录的实际存储位置）。所以两类节点的估算方法不同。我们先用T-SQL语句声明student表的表结构并为“姓名”字段设计非聚集索引。

CREATE TABLE student

（Stu_number CHAR（6） CONSTRAINT PK_number_STUDENT PRIMARY KEY NOT NULL，

Name VARCHAR（20） NOT NULL，--非聚集索引的索引关键字（索引键），只有一个字段

Specialty CHAR（20） NOT NULL，

Gender BIT NOT NULL CONSTRAINT DFT_ Gender _STUDENT DEFAULT 1，--注釋：1 男，0 女

Birthday SMALLDATETIME NOT NULL，

Total_credits TINYINT NULL

） --创建student表

GO

CREATE INDEX IND_name_STUDENT ON student（Name） --为“姓名”字段创建非聚集索引，索引的名字为IND_NAME_STUDENT，考虑到现实中的重名的情况，该索引不唯一。

下面我们就通过3个步骤来计算student表的IND_name_STUDENT索引所占用的存储空间。

一、计算用于存储非聚集索引的非叶级节点的空间

1.预估表中的行数： Num_Rows =1，000，000，因该表定义了聚集索引（主键），当行数超过10，000，000时，存储聚集索引的B树深度会增加，使得存储空间与记录行之间不再是线性渐变关系，因此我们将表中的行数设定在1，000，000。此问题可参看《Relational Database Architecture Refine Based on the Storage Space Estimate》。

2.指定索引键中固定长度和可变长度列的数量，并计算存储所需的空间：索引键列可以包括固定长度和可变长度列。要估计内部级别索引行的大小，需计算每组列在索引行中所占据的空间。列的大小取决于该列的数据类型和长度。

Num_Key_Cols = 总键列数（固定长度和可变长度）=1column

Fixed_Key_Size = 所有固定长度键列的总字节大小=0 byte

Num_Variable_Key_Cols = 可变长度键列的数量=1column

Max_Var_Key_Size = 所有可变长度键列的最大字节大小=20byte

3.如果索引不是唯一的，对数据行定位符的计算方法如下：如果非聚集索引不是唯一的，数据行定位符将与非聚集索引键组合使用，以便为每一行生成唯一的键值。

（1）如果非聚集索引在堆上，则数据行定位符是堆RID。其大小是8个字节，公式如下。但在stduent表中由于定义了主键，因此IND_name_STUDENT索引不是定义在堆上，不适用此种情况，只给出公式不做计算。

Num_Key_Cols = Num_Key_Cols + 1

Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1

Max_Var_Key_Size = Max_Var_Key_Size + 8

（2）如果非聚集索引在聚集索引之上，则数据行定位符是聚集键。必须与非聚集索引键结合使用的列是聚集键中的以下列：不在非聚集索引键列集中的列。Student表符合该情况，因此计算如下：

Num_Key_Cols =Num_Key_Cols+不在非聚集索引键列集中的聚集键列数（如果聚集索引不唯一，则加1）=1+1=2

Fixed_Key_Size =Fixed_Key_Size+不在非聚集索引键列集中的固定长度聚集键列的总字节大小=0+6=6byte

Num_Variable_Key_Cols =Num_Variable_Key_Cols+

不在非聚集索引键列集中的可变长度聚集键列数（如果聚集索引不唯一，则加1）=1+0=1.

Max_Var_Key_Size =Max_Var_Key_Size+不在非聚集索引键列集中的可变长度聚集键列的最大字节大小（如果聚集索引不唯一，则加4）=20+0=20byte

说明：student表的主键PK_number_STUDENT是唯一聚集索引，它只有固定长度为6byte的1个列，没有可变长度的列，因此：不在非聚集索引键列集中的聚集键列数为1，不在非聚集索引键列集中的固定长度聚集键列的总字节大小为6byte，不在非聚集索引键列集中的可变长度聚集鍵列数=0column，不在非聚集索引键列集中的可变长度聚集键列的最大字节大小=0byte。

4.保留行的一部分（称为“空位图”），以管理列的为空性。计算大小：如果索引键中有可为空的列（包括步骤一.3 中所述的所有必要的聚集键列），则保留索引行的一部分，以用于空位图。

Index_Null_Bitmap = 2 +（（可为空值的键列数 + 7）/ 8） 对表达式取整。 如果没有可为空的键列，将 Index_Null_Bitmap设置为 0。由于IND_name_STUDENT的索引键中没有可以为空的列。所以将Index_Null_Bitmap=0.

5.计算可变长度数据大小：如果索引键中有可变长度的列（包括所有必要的聚集索引键列），确定存储索引行中的这些列需使用的空间：Variable_Key_Size = 2 + （Num_Variable_Key_Cols×2） + Max_Var_Key_Size此时我们假定页的填满度为100%。如果页的填满度低，可以按照比例调整 Max_Var_Key_Size 值，从而对整个表大小得出一个更准确的估计。如果没有可变长度列，将 Variable_Key_Size 设置为 0。在student表中，Variable_Key_Size =2+（1×2）+20=24byte

6.计算索引行大小：Index_Row_Size= Fixed_Key_Size+Variable_Key_Size+Index_Null_Bitmap

+1（对应于索引行的行标题开销）+ 6（对应于子页 ID 指针）=6+24+0+1+6=37byte

7.计算每页的索引行数（每页可存储 8096字节）： Index_Rows_Per_Page = 8096/（Index_Row_Size + 2） 由于索引不能跨页断行，因此每页的索引行数向下取整。公式中的数值2是计算行数时引入的行大小余量。Index_Rows_Per_Page = 8096 /（37+2）=207 row

8.计算索引中的级别数（即B-树的深度）：Levels = 1+logIndex_Rows_Per_Page（Num_Rows/Index_Rows_ Per_Page）=1+log207（1，000，000/207）=1.59≈2 height

9.计算存储索引所需的页数：Num_Index_Pages = Level （Index_Rows_Per_Page）其中，1<=Level<=Levels，本例中Num_Index_Pages=207+207=208page

10.计算用于存储非聚集索引的非叶级节点的空间大小（每页可存储8192字节）：Index_Space_Used=8192×Num_Index_Pages=8192×208=1，703，936byte=1.625MB≈2MB

二、计算用于存储非聚集索引的叶级节点的空间

1.指定叶级的固定长度列和可变长度列的数量，并计算存储这些列所需的空间：如果非聚集索引的索引键是单一的列，则使用步骤1中的值（以步骤一.3中修改后为准）：

Num_Leaf_Cols = Num_Key_Cols=2 column

Fixed_Leaf_Size = Fixed_Key_Size=6 byte

Num_Variable_Leaf_Cols = Num_Variable_Key_

Cols=1 column

Max_Var_Leaf_Size = Max_Var_Key_Size=20 byte

如果非聚集索引的索引键是多个列的组合，并且这些列中既有可变长度列，也有固定长度列，则对步骤1中的值加上适当的值（以步骤一.3中修改后为准）。列的大小取决于数据类型和长度的规定。

Num_Leaf_Cols = Num_Key_Cols + 包含列数

Fixed_Leaf_Size = Fixed_Key_Size + 固定长度包含列的总字节大小

Num_Variable_Leaf_Cols = Num_Variable_Key_

Cols+可变长度包含列数

Max_Var_Leaf_Size = Max_Var_Key_Size + 可变长度包含列的最大字节大小

本示例的IND_name_STUDENT索引键只有一个可变长度VARCHAR（20）的列NAME，因此无需增加数值。

2.数据行定位符的计算：如果非聚集索引不是唯一的，若已在步骤一.3中考虑了数据行定位符的开销且不需要进行其他的修改，则转到下一步。如果非聚集索引是唯一的，则必须在叶级的所有行中说明数据行定位符。

（1）如果非聚集索引在堆上，则数据行定位符是堆RID（大小为8字节）。

Num_Leaf_Cols = Num_Leaf_Cols + 1

Num_Variable_Leaf_Cols = Num_Variable_Leaf_

Cols + 1

Max_Var_Leaf_Size = Max_Var_Leaf_Size + 8

（2）如果非聚集索引在聚集索引之上，则数据行定位符是聚集键。必须与非聚集索引键结合使用的列是聚集键中的以下列：不在非聚集索引键列集中的列。

Num_Leaf_Cols = Num_Leaf_Cols + 不在非聚集索引键列集中的聚集键列数（如果聚集索引不唯一，则加1）

Fixed_Leaf_Size = Fixed_Leaf_Size + 不在非聚集索引键列集中的固定长度聚集键列数

Num_Variable_Leaf_Cols = Num_Variable_Leaf_

Cols + 不在非聚集索引键列集中的可变长度聚集键列数（如果聚集索引不唯一，则加1）

Max_Var_Leaf_Size = Max_Var_Leaf_Size + 不在非聚集索引键列集中的可变长度聚集键列的字节大小（如果聚集索引不唯一，则加4）

说明：由于非聚集索引IND_name_STUDENT的键值不是唯一的（考虑现实中重名的情况），因此跳过二.2步骤，转到二.3步骤。

3.计算空位图大小：Leaf_Null_Bitmap = 2 + （（Num_Leaf_Cols + 7） / 8）对表达式向下取整，因此Leaf_Null_Bitmap = 2+（（2+7）/8）=3.125≈3

4.计算可变长度数据大小：如果索引键中有可变长度的列（包括步骤二.2 中必要的聚集索引键列），确定其需要的存储空间：Variable_Leaf_Size = 2 + （Num_Variable_Leaf_Cols×2） + Max_Var_Leaf_Size此時我们假定页的填满度为100%。如果页的填满度低，可以按照比例调整Max_Var_Leaf_Size的值，从而对整个表大小得出一个更准确的估计。如果没有可变长度的列，则将 Variable_Leaf_Size 设置为 0。 本示例中Variable_Leaf_Size = 2+（1×2）+20=24 byte。

5.计算索引行大小： Leaf_Row_Size = Fixed_Leaf_Size + Variable_Leaf_Size + Leaf_Null_

Bitmap + 1 （对应于索引行的行标题开销）+ 6（对应于子页 ID 指针）=6+24+3+1=34 byte

6.计算每页的索引行数（每页可存储8096个字节）：Leaf_Rows_Per_Page = 8096 / （Leaf_Row_Size + 2）由于索引不能跨页断行，因此每页的索引行数向下取整。公式中的数值2是计算行数时引入的行大小余量。本示例中Leaf_Rows_Per_Page = 8096/（34+2） ≈224 row

7.假定页的填充因子并计算每页保留的空行数：Free_Rows_Per_Page = 8096×（（100 - Fill_Factor） / 100） / （Leaf_Row_Size + 2）注意：填充因子为整数，不是百分比。由于索引不能跨页断行，因此每页的行数向下取整。公式中的数值 2 是计算行数时引入的行大小余量。本示例中假定Fill_Factor为60，因此Free_Rows_Per_Page = 8096×（（100-60）/100）/（34 + 2） ≈89 row。即每页需保留89个空行。

8.计算存储所有行所需的页数：Num_Leaf_Pages = Num_Rows / （Leaf_Rows_Per_Page - Free_Rows_Per_

Page）结果需向上取整。 本示例中Num_Leaf_Pages = 1，000，000/（224-89）=7408 page

9.计算叶级节点的空间大小（每页可储存 8192字节）： Leaf_Space_Used = 8192×Num_Leaf_Pages=

8192×7408≈58MB

三、对从前面两个步骤中得到的值求和

Nonclustered index size （bytes） = Leaf_Space_Used + Index_Space_used=2+58=60MB

通过计算可知，对于一个具有1，000，000条记录的student表来说，我们创建一个非聚集索引IND_name_STUDENT（不唯一）时，SQL Server为存储该索引所需要的存储空间大约是60MB。相对于存储表的空间，60MB的空间开销是很小的，但它可以大大提升我们对“姓名”字段的查找速度。因此，为数据表中经常需要查询的字段或字段组合设计好非聚集索引，可以大大提高查询效率。

一个表的空间大小是可估的，（1）若表无主键，则表大小=堆大小+非聚集索引大小；（2）有主键，则表大小=聚集索引大小+非聚集索引大小。表空间大小可估，数据库的空间大小就可估了。因此给数据库一个合理的初始大小是可行的也是必要的。

参考文献：

[1]岳莉.在SQL Server中估算堆大小[J].教育教学论坛，2013，5（22）：146-147.

[2]Liyue.The Key Factors of Mathematical Formula Affecting the Size of a Clustered Index. Advanced Materials Research Vol.962-965（2014）：2877-2880.