SQL Server 非聚集索引（如何預先估算其大小空間）

假如有一張大表，現在需要增加一個非聚集索引，對於DBA來說，要有預估其大小以及執行時間的估算能力，尤其對一些企業使用SSD硬碟，其硬碟空間很是寶貴，增加索引如果錯誤預估其大小，很有可能導致硬碟資源超出預期使用量，造成沒必要的麻煩，這裡只針對其預估硬碟占用空間展開討論，行為標準8060 in_row_ ...

　　假如有一張大表，現在需要增加一個非聚集索引，對於DBA來說，要有預估其大小以及執行時間的估算能力，尤其對一些企業使用SSD硬碟，其硬碟空間很是寶貴，增加索引如果錯誤預估其大小，很有可能導致硬碟資源超出預期使用量，造成沒必要的麻煩，這裡只針對其預估硬碟占用空間展開討論，行為標準8060 in_row_data,不涉及行溢出，大對象等情況。

　　舉個例子給大家

create table Index_test (id int,a char(10))

go

insert into Index_test select 100,'aaaaa'

go 4000

create nonclustered index ix_id_a on Index_test (id,a)

go

如果認為索引行的單行大小為4 + 10 = 14位元組，那麼最後的計算結果應該是

索引占用了（4000/（8096/14））*8192/1024 = 48KB。實際呢？

Paddy

索引占用了（14+1）*8192/1024 = 120KB

與預期的值相差了2倍以上，設想一下，如果你的某張大表的索引錯誤的預測為50G，實際則會占用100G以上。

網路上很多資料給出了非聚集索引的存儲格式，這裡我簡單明瞭的說明下非聚集索引的內部結構和預測其大小的方法。：

（非聚集索引分為根葉，中間級頁面，葉級頁面，實際在物理存儲上，我們可以將根葉和中間級頁面合併看成中間級頁面，因為兩者的存儲格式是相同的。我後面的討論也都是分為葉級頁面和中間級頁面）

非聚集索引葉級頁面單行：存儲格式

狀態位（1位元組），標識此行是否有變長，空值，以及此行是否為索引行等等
非聚集索引（定長列）鍵值大小
如果是堆表，則是Rowid（8位元組），如果是聚集索引，則是聚集索引鍵值（定長部分）大小。
包行列（定長列）長度
索引列數（2位元組）
Null位（1位元組）用來標識哪列值為null
變長列數量（2位元組）
變長列1偏移長度（2位元組）+變長列2偏移長度（2位元組）.. 變長列n偏移長度（2位元組）
包行列（變長列）長度 2位元組　　
非聚集索引（變長列）鍵值大小 + 聚集索引（變長列）鍵值大小 +包行列（變長列）鍵值大小
行偏移量（2位元組）

非聚集索引中間級頁面單行：存儲格式

狀態位（1位元組），標識此行是否有變長，空值，以及此行是否為索引行等等
非聚集索引（定長列）鍵值大小
如果是堆表，則是Rowid（8位元組），如果是聚集索引，則是聚集索引鍵值（定長部分）大小。
非聚集索引鍵值所在的pageid（4位元組） + 非聚集索引鍵值所在的頁面的文件id(2位元組)
索引列數（2位元組）
Null位（1位元組）用來標識哪列值為null
變長列數量（2位元組）
變長列1偏移長度（2位元組）+變長列2偏移長度（2位元組）.. 變長列n偏移長度（2位元組）
包行列（變長列）長度
非聚集索引（變長列）鍵值大小 + 聚集索引（變長列）鍵值大小 +包行列（變長列）鍵值大小
行偏移量（2位元組）

下麵總結了幾條規律，方便理解上面的結構：

如果索引涉及的列有一個允許null，則索引行會包含索引列數（2位元組）和Null位（1位元組）
如果索引包含聚集索引，則需將rowid替換為聚集索引鍵值
如果索引包含唯一約束，則中間層頁面不會包含rowid或者聚集索引鍵值
如果聚集索引不是唯一索引，而且存在重覆值，則重覆的聚集鍵值為（指定列 + 內部4位元組整數列）來標識唯一性，要點：內部4位元組整數列也屬於聚集索引鍵值，並且是變長列類型
如果沒有變長列，則在葉子頁面和中間層頁面不會包含：變長列數（2位元組） +變長列長度（2位元組）*變長列數 + 變長列鍵值。

結論

　　所以，對於一個非聚集索引來說，如果想確保內部系統開銷最小，索引行最節省空間，除了限制索引引用沒必要的列以外，還要考慮所有引用列均為not null，並且設置為unique唯一約束，同時最好具有聚集索引。

預估非聚集索引行大小

　　我根據以上規律總結了一個預估非聚集索引大小的腳本，因為索引填充率、變長列需要預先用最大值考慮等關係，最終結果會有稍微的誤差，只能當做最小預估空間的參考值。腳本並不能保證很完善，大家可以自行改良。

　文章開頭的例子，如果使用這個腳本，結果為：

和DMV輸出結果一樣

declare @fix_length                int    --定長欄位長度(byte)
declare @columns_count                int    --欄位數量
declare @variable_length            int    --變長欄位長度(byte)
declare @variable_count            int    --變長欄位數量
declare @Pri_Key_Length    int --聚集索引長度(byte)
declare @is_primarykey bit    --是否存在聚集索引，0不存在，1存在
declare @is_unique        bit    --是否指定唯一約束，0沒有指定，1為指定
declare @is_Null                bit    --是否允許為null，0為允許，1為不允許
declare @Num_Rows     int    --記錄數
declare @fillfactor            float    --填充因數，預設為100

set @fillfactor = 100
set @fix_length = 14
set @columns_count = 2
set @variable_length =0
set @variable_count = 0
set @Pri_Key_Length = 0
set @is_primarykey =0
set @is_unique =0
set @is_Null =0
set @Num_Rows  = 4000

declare @yezi                        int
declare @zhongjian            int
declare @ye_r_length            int
declare @ye_r_count            int 
declare @zhong_r_length    int
declare @zhong_r_count    int
--1.    無聚集索引，無唯一約束，允許null 
if @is_primarykey = 0 and @is_unique = 0 and @is_Null = 0
begin
    if @variable_count =0 
    begin
        set @ye_r_length= @fix_length + CEILING(@columns_count/8.0) + (1 + 8 + 2+2) 
        set @ye_r_count = (8192-96)*(@fillfactor/100.00) / @ye_r_length 
        set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
        set @zhong_r_length = @fix_length + CEILING(@columns_count/8.0) + (1 + 8 + 6 + 2 + 2)
        set @zhong_r_count =  (8192-96)/convert(int,@zhong_r_length)
        set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
    else
    begin
        set @ye_r_length= @fix_length + CEILING(@columns_count/8.0) + (1 + 8 + 2 + 2 + 2*@variable_count + @variable_length + 2) 
        set @ye_r_count = (8192-96) *(@fillfactor/100.00)/ @ye_r_length 
        set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
        set @zhong_r_length = @fix_length + CEILING(@columns_count/8.0) + (1 + 8 + 6 + 2+2 + 2*@variable_count + @variable_length + 2)
        set @zhong_r_count =  (8192-96)/convert(int,@zhong_r_length)
        set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
end
--2.    無聚集索引，無唯一約束，不允許null
if @is_primarykey = 0 and @is_unique = 0 and @is_Null = 1
begin
    if @variable_count = 0
    begin
            set @ye_r_length= @fix_length + (1 + 8 +2) 
            set @ye_r_count = (8192-96) *(@fillfactor/100.00) / @ye_r_length 
            set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
            set @zhong_r_length = @fix_length +   (1 + 8 + 6   + 2)
            set @zhong_r_count =  (8192-96)/convert(int,@zhong_r_length)
            set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
    else
    begin
            set @ye_r_length= @fix_length + (1 + 8 + 2 + 2*@variable_count + @variable_length + 2) 
            set @ye_r_count = (8192-96)*(@fillfactor/100.00) / @ye_r_length 
            set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
            set @zhong_r_length = @fix_length +   (1 + 8 + 6 + 2*@variable_count + @variable_length    + 2)
            set @zhong_r_count =  (8192-96) /convert(int,@zhong_r_length)
            set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
end
--3.    無聚集索引，有唯一約束，允許null 
if @is_primarykey = 0 and @is_unique = 1 and @is_Null = 0
begin
    if @variable_count  = 0
    begin
        set @ye_r_length= @fix_length + CEILING(@columns_count/8.0) + (1 + 8 + 2+2) 
        set @ye_r_count = (8192-96) *(@fillfactor/100.00)/ @ye_r_length 
        set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
        set @zhong_r_length = @fix_length + CEILING(@columns_count/8.0) + (1 + 6 + 2 + 2)
        set @zhong_r_count =  (8192-96)*0.97/convert(int,@zhong_r_length)
        set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
    else
    begin
        set @ye_r_length= @fix_length + CEILING(@columns_count/8.0) + (1 + 8 + 2 + 2 + 2*@variable_count + @variable_length +2) 
        set @ye_r_count = (8192-96) *(@fillfactor/100.00)/ @ye_r_length 
        set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
        set @zhong_r_length = @fix_length + CEILING(@columns_count/8.0) + (1 + 6 + 2 +2 + 2*@variable_count + @variable_length + 2)
        set @zhong_r_count =  (8192-96)*0.86/convert(int,@zhong_r_length)
        set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
end

--4.    無聚集索引，有唯一約束，不允許null 
if @is_primarykey = 0 and @is_unique = 1 and @is_Null = 1
begin
    if @variable_count = 0
    begin
        set @ye_r_length= @fix_length + (1 + 8 +2) 
        set @ye_r_count = (8192-96*(@fillfactor/100.00)) / @ye_r_length 
        set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
        set @zhong_r_length = @fix_length +   (1 + 6   + 2)
        set @zhong_r_count =  (8192-96)/convert(int,@zhong_r_length)
        set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
    else
    begin
        set @ye_r_length= @fix_length + (1 + 8 + 2 + 2*@variable_count  + @variable_length + 2) 
        set @ye_r_count = (8192-96)*(@fillfactor/100.00) / @ye_r_length 
        set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
        set @zhong_r_length = @fix_length +   (1 + 6  + 2 + 2*@variable_count  + @variable_length  + 2)
        set @zhong_r_count =  (8192-96)/convert(int,@zhong_r_length)
        set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
end

--5.    有聚集索引，無唯一約束，允許null 
if @is_primarykey = 1 and @is_unique = 0 and @is_Null = 0
begin
    if @variable_count = 0
    begin
            set @ye_r_length= @fix_length + @Pri_Key_Length+CEILING(@columns_count/8.0) + (1 + 2+2) 
            set @ye_r_count = (8192-96)*(@fillfactor/100.00) / @ye_r_length 
            set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
            set @zhong_r_length = @fix_length +@Pri_Key_Length+ CEILING(@columns_count/8.0) + (1  + 6 + 2  + 2)
            set @zhong_r_count =  (8192-96)/convert(int,@zhong_r_length)
            set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
    else
    begin
            set @ye_r_length= @fix_length + @Pri_Key_Length+CEILING(@columns_count/8.0) + (1 + 2+ 2 + 2*@variable_count +@variable_length +2) 
            set @ye_r_count = (8192-96)*(@fillfactor/100.00) / @ye_r_length 
            set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
            set @zhong_r_length = @fix_length +@Pri_Key_Length+ CEILING(@columns_count/8.0) + (1  + 6 + 2 + 2 + 2*@variable_count +@fix_length + 2)
            set @zhong_r_count =  (8192-96)/convert(int,@zhong_r_length)
            set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
end


--6.    有聚集索引，無唯一約束，不允許null 
if @is_primarykey = 1 and @is_unique = 0 and @is_Null = 1
begin
    if @variable_count = 0
    begin
        set @ye_r_length= @fix_length +@Pri_Key_Length+ (1 +2) 
        set @ye_r_count = (8192-96)*(@fillfactor/100.00) / @ye_r_length 
        set @yezi =  ceiling(@Num_Rows  / convert(float,convert(int,@ye_r_count)))
        set @zhong_r_length = @fix_length +@Pri_Key_Length+   (1 + 6   + 2)
        set @zhong_r_count =  (8192-96)/convert(int,@zhong_r_length)
        set @zhongjian = ceiling(@yezi/convert(float,(@zhong_r_count)))
    end
    else
    begin
        set @ye_r_length= @fix_length +@Pri_Key_Length+ (1 + 2 + 2*@variable_count  + @variable_length  +2) 
        set @ye_r_count = (8192-96)*<