摘要：SQL Server是占有市場(chǎng)份額較大的一個(gè)關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)。本文討論在數(shù)據(jù)庫(kù)設(shè)計(jì)階段的一項(xiàng)優(yōu)化技術(shù)，估算非聚集索引的大小。索引的設(shè)計(jì)將最終決定數(shù)據(jù)庫(kù)運(yùn)行的性能。除去必要的聚集索引，非聚集索引也是數(shù)據(jù)庫(kù)設(shè)計(jì)中的重要環(huán)節(jié)。

關(guān)鍵詞：SQL Server；數(shù)據(jù)庫(kù)大小；非聚集索引；估算非聚集索引大小

中圖分類號(hào)：G642.0 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：1674-9324（2018）16-0268-03

SQL Server作為微軟的數(shù)據(jù)庫(kù)管理系統(tǒng)主要解決了中小企業(yè)數(shù)據(jù)管理需求。在設(shè)計(jì)應(yīng)用軟件的數(shù)據(jù)庫(kù)部分時(shí)，我們經(jīng)常遇到一個(gè)問題，那就是：數(shù)據(jù)庫(kù)的初始大小定義多少更合理呢？這就是估算數(shù)據(jù)庫(kù)大小的問題。數(shù)據(jù)庫(kù)的大小由數(shù)據(jù)庫(kù)中所有表的大小決定，即數(shù)據(jù)庫(kù)的大小是全部數(shù)據(jù)表之和。估算數(shù)據(jù)表占用存儲(chǔ)空間的大小時(shí)，有如下兩種情況：（1）沒有設(shè)計(jì)主鍵的表我們稱之為堆；（2）設(shè)計(jì)了主鍵（唯一聚集索引）的表。在這兩種表中我們都可以為經(jīng)常查詢的字段或字段組合創(chuàng)建索引，這種索引屬于非聚集索引（可以唯一也可以不唯一）。

在SQL Server中一個(gè)表有且僅有一個(gè)唯一聚集索引就是主鍵，但是可以有多個(gè)非聚集索引，針對(duì)應(yīng)用程序中經(jīng)常查詢需要用到的字段或字段組合我們可以為其設(shè)計(jì)非聚集索引。本文以student表為例，為經(jīng)常需要查詢的字段“姓名”建立非唯一非聚集索引，并通過3個(gè)步驟對(duì)該非聚集索引的大小進(jìn)行估算。首先說(shuō)明SQL Server中對(duì)索引的存儲(chǔ)。當(dāng)我們?yōu)楸韯?chuàng)建了一個(gè)非聚集索引時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)將建立起一顆B-樹用來(lái)存儲(chǔ)該索引。在B-樹中有兩類節(jié)點(diǎn)：（1）非葉級(jí)節(jié)點(diǎn)；（2）葉級(jí)節(jié)點(diǎn)。非葉級(jí)節(jié)點(diǎn)保存了鍵值之間的排列關(guān)系，而葉級(jí)節(jié)點(diǎn)用來(lái)存儲(chǔ)指針（該指針指向記錄的實(shí)際存儲(chǔ)位置）。所以兩類節(jié)點(diǎn)的估算方法不同。我們先用T-SQL語(yǔ)句聲明student表的表結(jié)構(gòu)并為“姓名”字段設(shè)計(jì)非聚集索引。

CREATE TABLE student

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

Name VARCHAR（20） NOT NULL，--非聚集索引的索引關(guān)鍵字（索引鍵），只有一個(gè)字段

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

） --創(chuàng)建student表

GO

CREATE INDEX IND_name_STUDENT ON student（Name） --為“姓名”字段創(chuàng)建非聚集索引，索引的名字為IND_NAME_STUDENT，考慮到現(xiàn)實(shí)中的重名的情況，該索引不唯一。

下面我們就通過3個(gè)步驟來(lái)計(jì)算student表的IND_name_STUDENT索引所占用的存儲(chǔ)空間。

一、計(jì)算用于存儲(chǔ)非聚集索引的非葉級(jí)節(jié)點(diǎn)的空間

1.預(yù)估表中的行數(shù)： Num_Rows =1，000，000，因該表定義了聚集索引（主鍵），當(dāng)行數(shù)超過10，000，000時(shí)，存儲(chǔ)聚集索引的B樹深度會(huì)增加，使得存儲(chǔ)空間與記錄行之間不再是線性漸變關(guān)系，因此我們將表中的行數(shù)設(shè)定在1，000，000。此問題可參看《Relational Database Architecture Refine Based on the Storage Space Estimate》。

2.指定索引鍵中固定長(zhǎng)度和可變長(zhǎng)度列的數(shù)量，并計(jì)算存儲(chǔ)所需的空間：索引鍵列可以包括固定長(zhǎng)度和可變長(zhǎng)度列。要估計(jì)內(nèi)部級(jí)別索引行的大小，需計(jì)算每組列在索引行中所占據(jù)的空間。列的大小取決于該列的數(shù)據(jù)類型和長(zhǎng)度。

Num_Key_Cols = 總鍵列數(shù)（固定長(zhǎng)度和可變長(zhǎng)度）=1column

Fixed_Key_Size = 所有固定長(zhǎng)度鍵列的總字節(jié)大小=0 byte

Num_Variable_Key_Cols = 可變長(zhǎng)度鍵列的數(shù)量=1column

Max_Var_Key_Size = 所有可變長(zhǎng)度鍵列的最大字節(jié)大小=20byte

3.如果索引不是唯一的，對(duì)數(shù)據(jù)行定位符的計(jì)算方法如下：如果非聚集索引不是唯一的，數(shù)據(jù)行定位符將與非聚集索引鍵組合使用，以便為每一行生成唯一的鍵值。

（1）如果非聚集索引在堆上，則數(shù)據(jù)行定位符是堆RID。其大小是8個(gè)字節(jié)，公式如下。但在stduent表中由于定義了主鍵，因此IND_name_STUDENT索引不是定義在堆上，不適用此種情況，只給出公式不做計(jì)算。

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）如果非聚集索引在聚集索引之上，則數(shù)據(jù)行定位符是聚集鍵。必須與非聚集索引鍵結(jié)合使用的列是聚集鍵中的以下列：不在非聚集索引鍵列集中的列。Student表符合該情況，因此計(jì)算如下：

Num_Key_Cols =Num_Key_Cols+不在非聚集索引鍵列集中的聚集鍵列數(shù)（如果聚集索引不唯一，則加1）=1+1=2

Fixed_Key_Size =Fixed_Key_Size+不在非聚集索引鍵列集中的固定長(zhǎng)度聚集鍵列的總字節(jié)大小=0+6=6byte

Num_Variable_Key_Cols =Num_Variable_Key_Cols+

不在非聚集索引鍵列集中的可變長(zhǎng)度聚集鍵列數(shù)（如果聚集索引不唯一，則加1）=1+0=1.

Max_Var_Key_Size =Max_Var_Key_Size+不在非聚集索引鍵列集中的可變長(zhǎng)度聚集鍵列的最大字節(jié)大小（如果聚集索引不唯一，則加4）=20+0=20byte

說(shuō)明：student表的主鍵PK_number_STUDENT是唯一聚集索引，它只有固定長(zhǎng)度為6byte的1個(gè)列，沒有可變長(zhǎng)度的列，因此：不在非聚集索引鍵列集中的聚集鍵列數(shù)為1，不在非聚集索引鍵列集中的固定長(zhǎng)度聚集鍵列的總字節(jié)大小為6byte，不在非聚集索引鍵列集中的可變長(zhǎng)度聚集鍵列數(shù)=0column，不在非聚集索引鍵列集中的可變長(zhǎng)度聚集鍵列的最大字節(jié)大小=0byte。

4.保留行的一部分（稱為“空位圖”），以管理列的為空性。計(jì)算大?。喝绻饕I中有可為空的列（包括步驟一.3 中所述的所有必要的聚集鍵列），則保留索引行的一部分，以用于空位圖。

Index_Null_Bitmap = 2 +（（可為空值的鍵列數(shù) + 7）/ 8） 對(duì)表達(dá)式取整。 如果沒有可為空的鍵列，將 Index_Null_Bitmap設(shè)置為 0。由于IND_name_STUDENT的索引鍵中沒有可以為空的列。所以將Index_Null_Bitmap=0.

5.計(jì)算可變長(zhǎng)度數(shù)據(jù)大?。喝绻饕I中有可變長(zhǎng)度的列（包括所有必要的聚集索引鍵列），確定存儲(chǔ)索引行中的這些列需使用的空間：Variable_Key_Size = 2 + （Num_Variable_Key_Cols×2） + Max_Var_Key_Size此時(shí)我們假定頁(yè)的填滿度為100%。如果頁(yè)的填滿度低，可以按照比例調(diào)整 Max_Var_Key_Size 值，從而對(duì)整個(gè)表大小得出一個(gè)更準(zhǔn)確的估計(jì)。如果沒有可變長(zhǎng)度列，將 Variable_Key_Size 設(shè)置為 0。在student表中，Variable_Key_Size =2+（1×2）+20=24byte

6.計(jì)算索引行大小：Index_Row_Size= Fixed_Key_Size+Variable_Key_Size+Index_Null_Bitmap

+1（對(duì)應(yīng)于索引行的行標(biāo)題開銷）+ 6（對(duì)應(yīng)于子頁(yè) ID 指針）=6+24+0+1+6=37byte

7.計(jì)算每頁(yè)的索引行數(shù)（每頁(yè)可存儲(chǔ) 8096字節(jié)）： Index_Rows_Per_Page = 8096/（Index_Row_Size + 2） 由于索引不能跨頁(yè)斷行，因此每頁(yè)的索引行數(shù)向下取整。公式中的數(shù)值2是計(jì)算行數(shù)時(shí)引入的行大小余量。Index_Rows_Per_Page = 8096 /（37+2）=207 row

8.計(jì)算索引中的級(jí)別數(shù)（即B-樹的深度）：Levels = 1+logIndex_Rows_Per_Page（Num_Rows/Index_Rows_ Per_Page）=1+log207（1，000，000/207）=1.59≈2 height

9.計(jì)算存儲(chǔ)索引所需的頁(yè)數(shù)：Num_Index_Pages = Level （Index_Rows_Per_Page）其中，1<=Level<=Levels，本例中Num_Index_Pages=207+207=208page

10.計(jì)算用于存儲(chǔ)非聚集索引的非葉級(jí)節(jié)點(diǎn)的空間大小（每頁(yè)可存儲(chǔ)8192字節(jié)）：Index_Space_Used=8192×Num_Index_Pages=8192×208=1，703，936byte=1.625MB≈2MB

二、計(jì)算用于存儲(chǔ)非聚集索引的葉級(jí)節(jié)點(diǎn)的空間

1.指定葉級(jí)的固定長(zhǎng)度列和可變長(zhǎng)度列的數(shù)量，并計(jì)算存儲(chǔ)這些列所需的空間：如果非聚集索引的索引鍵是單一的列，則使用步驟1中的值（以步驟一.3中修改后為準(zhǔn)）：

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

如果非聚集索引的索引鍵是多個(gè)列的組合，并且這些列中既有可變長(zhǎng)度列，也有固定長(zhǎng)度列，則對(duì)步驟1中的值加上適當(dāng)?shù)闹担ㄒ圆襟E一.3中修改后為準(zhǔn)）。列的大小取決于數(shù)據(jù)類型和長(zhǎng)度的規(guī)定。

Num_Leaf_Cols = Num_Key_Cols + 包含列數(shù)

Fixed_Leaf_Size = Fixed_Key_Size + 固定長(zhǎng)度包含列的總字節(jié)大小

Num_Variable_Leaf_Cols = Num_Variable_Key_

Cols+可變長(zhǎng)度包含列數(shù)

Max_Var_Leaf_Size = Max_Var_Key_Size + 可變長(zhǎng)度包含列的最大字節(jié)大小

本示例的IND_name_STUDENT索引鍵只有一個(gè)可變長(zhǎng)度VARCHAR（20）的列NAME，因此無(wú)需增加數(shù)值。

2.數(shù)據(jù)行定位符的計(jì)算：如果非聚集索引不是唯一的，若已在步驟一.3中考慮了數(shù)據(jù)行定位符的開銷且不需要進(jìn)行其他的修改，則轉(zhuǎn)到下一步。如果非聚集索引是唯一的，則必須在葉級(jí)的所有行中說(shuō)明數(shù)據(jù)行定位符。

（1）如果非聚集索引在堆上，則數(shù)據(jù)行定位符是堆RID（大小為8字節(jié)）。

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）如果非聚集索引在聚集索引之上，則數(shù)據(jù)行定位符是聚集鍵。必須與非聚集索引鍵結(jié)合使用的列是聚集鍵中的以下列：不在非聚集索引鍵列集中的列。

Num_Leaf_Cols = Num_Leaf_Cols + 不在非聚集索引鍵列集中的聚集鍵列數(shù)（如果聚集索引不唯一，則加1）

Fixed_Leaf_Size = Fixed_Leaf_Size + 不在非聚集索引鍵列集中的固定長(zhǎng)度聚集鍵列數(shù)

Num_Variable_Leaf_Cols = Num_Variable_Leaf_

Cols + 不在非聚集索引鍵列集中的可變長(zhǎng)度聚集鍵列數(shù)（如果聚集索引不唯一，則加1）

Max_Var_Leaf_Size = Max_Var_Leaf_Size + 不在非聚集索引鍵列集中的可變長(zhǎng)度聚集鍵列的字節(jié)大?。ㄈ绻奂饕晃ㄒ?，則加4）

說(shuō)明：由于非聚集索引IND_name_STUDENT的鍵值不是唯一的（考慮現(xiàn)實(shí)中重名的情況），因此跳過二.2步驟，轉(zhuǎn)到二.3步驟。

3.計(jì)算空位圖大?。篖eaf_Null_Bitmap = 2 + （（Num_Leaf_Cols + 7） / 8）對(duì)表達(dá)式向下取整，因此Leaf_Null_Bitmap = 2+（（2+7）/8）=3.125≈3

4.計(jì)算可變長(zhǎng)度數(shù)據(jù)大?。喝绻饕I中有可變長(zhǎng)度的列（包括步驟二.2 中必要的聚集索引鍵列），確定其需要的存儲(chǔ)空間：Variable_Leaf_Size = 2 + （Num_Variable_Leaf_Cols×2） + Max_Var_Leaf_Size此時(shí)我們假定頁(yè)的填滿度為100%。如果頁(yè)的填滿度低，可以按照比例調(diào)整Max_Var_Leaf_Size的值，從而對(duì)整個(gè)表大小得出一個(gè)更準(zhǔn)確的估計(jì)。如果沒有可變長(zhǎng)度的列，則將 Variable_Leaf_Size 設(shè)置為 0。 本示例中Variable_Leaf_Size = 2+（1×2）+20=24 byte。

5.計(jì)算索引行大小： Leaf_Row_Size = Fixed_Leaf_Size + Variable_Leaf_Size + Leaf_Null_

Bitmap + 1 （對(duì)應(yīng)于索引行的行標(biāo)題開銷）+ 6（對(duì)應(yīng)于子頁(yè) ID 指針）=6+24+3+1=34 byte

6.計(jì)算每頁(yè)的索引行數(shù)（每頁(yè)可存儲(chǔ)8096個(gè)字節(jié)）：Leaf_Rows_Per_Page = 8096 / （Leaf_Row_Size + 2）由于索引不能跨頁(yè)斷行，因此每頁(yè)的索引行數(shù)向下取整。公式中的數(shù)值2是計(jì)算行數(shù)時(shí)引入的行大小余量。本示例中Leaf_Rows_Per_Page = 8096/（34+2） ≈224 row

7.假定頁(yè)的填充因子并計(jì)算每頁(yè)保留的空行數(shù)：Free_Rows_Per_Page = 8096×（（100 - Fill_Factor） / 100） / （Leaf_Row_Size + 2）注意：填充因子為整數(shù)，不是百分比。由于索引不能跨頁(yè)斷行，因此每頁(yè)的行數(shù)向下取整。公式中的數(shù)值 2 是計(jì)算行數(shù)時(shí)引入的行大小余量。本示例中假定Fill_Factor為60，因此Free_Rows_Per_Page = 8096×（（100-60）/100）/（34 + 2） ≈89 row。即每頁(yè)需保留89個(gè)空行。

8.計(jì)算存儲(chǔ)所有行所需的頁(yè)數(shù)：Num_Leaf_Pages = Num_Rows / （Leaf_Rows_Per_Page - Free_Rows_Per_

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

9.計(jì)算葉級(jí)節(jié)點(diǎn)的空間大小（每頁(yè)可儲(chǔ)存 8192字節(jié)）： Leaf_Space_Used = 8192×Num_Leaf_Pages=

8192×7408≈58MB

三、對(duì)從前面兩個(gè)步驟中得到的值求和

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

通過計(jì)算可知，對(duì)于一個(gè)具有1，000，000條記錄的student表來(lái)說(shuō)，我們創(chuàng)建一個(gè)非聚集索引IND_name_STUDENT（不唯一）時(shí)，SQL Server為存儲(chǔ)該索引所需要的存儲(chǔ)空間大約是60MB。相對(duì)于存儲(chǔ)表的空間，60MB的空間開銷是很小的，但它可以大大提升我們對(duì)“姓名”字段的查找速度。因此，為數(shù)據(jù)表中經(jīng)常需要查詢的字段或字段組合設(shè)計(jì)好非聚集索引，可以大大提高查詢效率。

一個(gè)表的空間大小是可估的，（1）若表無(wú)主鍵，則表大小=堆大小+非聚集索引大?。唬?）有主鍵，則表大小=聚集索引大小+非聚集索引大小。表空間大小可估，數(shù)據(jù)庫(kù)的空間大小就可估了。因此給數(shù)據(jù)庫(kù)一個(gè)合理的初始大小是可行的也是必要的。

參考文獻(xiàn)：

[1]岳莉.在SQL Server中估算堆大小[J].教育教學(xué)論壇，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.