Sql Server之旅——第八站 複合索引和include索引到底有多大區別?

一線碼農發表於2015-02-01

  週末終於搬進出租房了,裝了寬頻。。。。才發現沒網的日子。。。那是一個怎樣的與世隔絕呀。。。再也受不了那樣的日子了。。。。好了,既然網

安上去了,還得繼續我的這個系列。

  索引和鎖,這兩個主題對我們開發工程師來說,非常的重要。。。只有理解了這兩個主題,我們才能寫出高質量的sql語句,在之前的部落格中,我所說的

索引都是單列索引。。。當然資料庫不可能只認單列索引,還有我這篇的複合索引,說到複合索引,可能熟悉的人又會說到include索引,那這兩個索引到底

有什麼區別呢,當然我也是菜鳥一枚。。。所以下面的也是我的個人見解。。。

 

一:從資料頁角度看問題

1. 做兩個表,插入兩條資料,在test1上做複合索引,在test2上做include索引,如下圖:

 1 -- 在test1表中插入2條記錄
 2 CREATE TABLE test1(ID int,Name CHAR(5),Email CHAR(10))
 3 INSERT INTO test1 VALUES(1,'aaaaa','111@qq.com')
 4 INSERT INTO test1 VALUES(2,'bbbbb','222@qq.com')
 5 CREATE INDEX idx_test1 ON dbo.test1(Name,Email)
 6 
 7 -- 在test2表中插入2條記錄
 8 CREATE TABLE test2(ID int,Name CHAR(5),Email CHAR(10))
 9 INSERT INTO test2 VALUES(1,'aaaaa','111@qq.com')
10 INSERT INTO test2 VALUES(2,'bbbbb','222@qq.com')
11 CREATE INDEX idx_test2 ON dbo.test2(Name) INCLUDE(Email)

 

2. 然後通過DBCC 命令檢視資料頁記錄

<1> 先來看看test1表中各個槽位的資訊

1 DBCC TRACEON(2588,3604)
2 DBCC IND(Ctrip,test1,-1)
3 DBCC PAGE(Ctrip,1,194,1) 
 1 Slot 0, Offset 0x60, Length 27, DumpStyle BYTE
 2 
 3 Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 27
 4 
 5 Memory Dump @0x000000000FB0A060
 6 
 7 0000000000000000:   16616161 61613131 31407171 2e636f6d †.aaaaa111@qq.com 
 8 0000000000000010:   c0000000 01000000 030000†††††††††††††...........      
 9 
10 Slot 1, Offset 0x7b, Length 27, DumpStyle BYTE
11 
12 Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 27
13 
14 Memory Dump @0x000000000FB0A07B
15 
16 0000000000000000:   16626262 62623232 32407171 2e636f6d †.bbbbb222@qq.com 
17 0000000000000010:   c0000000 01000100 030000†††††††††††††...........      
18 
19 OFFSET TABLE:
20 
21 Row - Offset                         
22 1 (0x1) - 123 (0x7b)                 
23 0 (0x0) - 96 (0x60)            

 

<2> 再來看看test2表中各個槽位資訊

1 DBCC TRACEON(2588,3604)
2 DBCC IND(Ctrip,test2,-1)
3 DBCC PAGE(Ctrip,1,207,1)
 1 Slot 0, Offset 0x60, Length 27, DumpStyle BYTE
 2 
 3 Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 27
 4 
 5 Memory Dump @0x000000000DFCA060
 6 
 7 0000000000000000:   16616161 6161c400 00000100 00003131 †.aaaaa........11 
 8 0000000000000010:   31407171 2e636f6d 030000†††††††††††††1@qq.com...      
 9 
10 Slot 1, Offset 0x7b, Length 27, DumpStyle BYTE
11 
12 Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 27
13 
14 Memory Dump @0x000000000DFCA07B
15 
16 0000000000000000:   16626262 6262c400 00000100 01003232 †.bbbbb........22 
17 0000000000000010:   32407171 2e636f6d 030000†††††††††††††2@qq.com...      
18 
19 OFFSET TABLE:
20 
21 Row - Offset                         
22 1 (0x1) - 123 (0x7b)                 
23 0 (0x0) - 96 (0x60) 

 

<3> 從test1和test2的資料頁來看,都是有兩個slot槽位,然後我們把test1和test2的slot0槽位拿出來對比下,是不是就知道兩者大概有什麼區別了。

test1のslot0 

1 0000000000000000:   16616161 61613131 31407171 2e636f6d †.aaaaa111@qq.com 
2 0000000000000010:   c0000000 01000000 030000†††††††††††††...........    

test2のslot0 

1 0000000000000000:   16616161 6161c400 00000100 00003131 †.aaaaa........11 
2 0000000000000010:   31407171 2e636f6d 030000†††††††††††††1@qq.com...     

下面我仔細解剖下兩表中的slot內容:

 16   6161616161   3131314071712e636f6d  c0000000 0100 0000  0300    00

16:                              這個是索引記錄的系統頭資料。

6161616161:               轉換成十進位制就是9797979797,也就是字元的aaaaa。

3131314071712e636f6d:  這個我想你也懂,也就是111@qq.com。

c000000010000000:        因為我們是堆表,所以這個就是表的RowID,轉化為十進位制就是: 192:1:0。

0300:                            這個表示表中的記錄數,也就是3條記錄。

 

如果你對上面的講解明白了,那我們繼續看看test2のslot0,如果你仔細的話,你會看到在test2中,111qq.com是在記錄的最後。。。那這說明什

問題呢???如果你對記錄比較熟悉的話,你就知道,其實記錄中的變長欄位值一般都是放在記錄的尾部。。。好處就是可以做到“行溢位”。也就是

可以超過索引的900長度限制。。。而複合索引卻無法做到。。。如果你不信我可以做個例子,將name和email的長度設為定長500。

 

而include索引卻可以順利通過。。。。。

 

這幾天上海特別冷,打字都打著手哆嗦。。。不準備繼續說了。。。下一篇繼續扯下複合索引到底都能帶來哪些好處。

 

相關文章