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

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

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

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

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

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

CREATE TABLE student

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

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

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)實中的重名的情況，該索引不唯一。

下面我們就通過3個步驟來計算student表的IND_name_STUDENT索引所占用的存儲空間。

一、計算用于存儲非聚集索引的非葉級節(jié)點的空間

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

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

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

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

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

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

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

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

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

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

Num_Variable_Key_Cols =Num_Variable_Key_Cols+

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

Max_Var_Key_Size =Max_Var_Key_Size+不在非聚集索引鍵列集中的可變長度聚集鍵列的最大字節(jié)大?。ㄈ绻奂饕晃ㄒ唬瑒t加4）=20+0=20byte

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

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

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

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

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

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

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

8.計算索引中的級別數(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.計算存儲索引所需的頁數(shù)：Num_Index_Pages = Level （Index_Rows_Per_Page）其中，1<=Level<=Levels，本例中Num_Index_Pages=207+207=208page

10.計算用于存儲非聚集索引的非葉級節(jié)點的空間大?。宽摽纱鎯?192字節(jié)）：Index_Space_Used=8192×Num_Index_Pages=8192×208=1，703，936byte=1.625MB≈2MB

二、計算用于存儲非聚集索引的葉級節(jié)點的空間

1.指定葉級的固定長度列和可變長度列的數(shù)量，并計算存儲這些列所需的空間：如果非聚集索引的索引鍵是單一的列，則使用步驟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

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

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

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

Num_Variable_Leaf_Cols = Num_Variable_Key_

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

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

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

2.數(shù)據(jù)行定位符的計算：如果非聚集索引不是唯一的，若已在步驟一.3中考慮了數(shù)據(jù)行定位符的開銷且不需要進(jìn)行其他的修改，則轉(zhuǎn)到下一步。如果非聚集索引是唯一的，則必須在葉級的所有行中說明數(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 + 不在非聚集索引鍵列集中的固定長度聚集鍵列數(shù)

Num_Variable_Leaf_Cols = Num_Variable_Leaf_

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

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

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

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

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

5.計算索引行大?。?Leaf_Row_Size = Fixed_Leaf_Size + Variable_Leaf_Size + Leaf_Null_

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

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

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

8.計算存儲所有行所需的頁數(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.計算葉級節(jié)點的空間大小（每頁可儲存 8192字節(jié)）： 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表來說，我們創(chuàng)建一個非聚集索引IND_name_STUDENT（不唯一）時，SQL Server為存儲該索引所需要的存儲空間大約是60MB。相對于存儲表的空間，60MB的空間開銷是很小的，但它可以大大提升我們對“姓名”字段的查找速度。因此，為數(shù)據(jù)表中經(jīng)常需要查詢的字段或字段組合設(shè)計好非聚集索引，可以大大提高查詢效率。

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

參考文獻(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.