SQLSERVER聚集索引與非聚集索引的再次研究（上）上篇主要說(shuō)聚集索引 下篇的地址：SQLSERVER聚集索引與非聚集索引的再次研究（下） 由于本人還是SQLSERVER菜鳥(niǎo)一枚，加上一些實(shí)驗(yàn)的邏輯嚴(yán)謹(jǐn)性，
單寫(xiě)《SQLSERVER聚集索引與非聚集索引的再次研究（上）》就用了12個(gè)小時(shí)，兩篇文章加起來(lái)最起碼寫(xiě)了20個(gè)小時(shí)，
本人非常非常用心的努力完成這兩篇文章，希望各位看官給點(diǎn)意見(jiàn)o(∩_∩)o
為了搞清楚索引內(nèi)部工作原理和結(jié)構(gòu)，真是千頭萬(wàn)緒，這篇文章只是作為參考，里面的觀點(diǎn)不一定正確 有一些問(wèn)題，msdn里，網(wǎng)上的文章里，博客園里都有提到，但是這些問(wèn)題的答案是正確的嗎？其實(shí)有時(shí)候我自己都想知道答案 比如，畫(huà)聚集索引的圖，有一些人用表格來(lái)表示，但是他們正確嗎？ 以前知道聚集索引 非聚集索引是B樹(shù) 二叉樹(shù)結(jié)構(gòu)，又知道執(zhí)行計(jì)劃圖標(biāo)很像二叉樹(shù)很傳神，但是還是覺(jué)得很抽象 這篇文章寫(xiě)完以后還是比較抽象但是最起碼比以前清晰一些了
有很多問(wèn)題不知道為什么，但是MSDN就是這樣說(shuō)的，既然說(shuō)得這麼模糊不如自己做一下實(shí)驗(yàn)，驗(yàn)證一下MSDN的內(nèi)容吧o(∩_∩)o --------------------------------------------華麗的分割線--------------------------------------------- 先來(lái)看一下索引的結(jié)構(gòu)，文章里面的一些結(jié)構(gòu)圖都是自己畫(huà)的一些草圖，本人自認(rèn)畫(huà)得非常爛，希望各位看官諒解o(∩_∩)o
----------------------------------------------華麗的分割線--------------------------------------------------------- 先創(chuàng)建一個(gè)表，保存DBCC IND的結(jié)果 1 CREATE TABLE DBCCResult ( 2 PageFID NVARCHAR(200), 3 PagePID NVARCHAR(200), 4 IAMFID NVARCHAR(200), 5 IAMPID NVARCHAR(200), 6 ObjectID NVARCHAR(200), 7 IndexID NVARCHAR(200), 8 PartitionNumber NVARCHAR(200), 9 PartitionID NVARCHAR(200), 10 iam_chain_type NVARCHAR(200), 11 PageType NVARCHAR(200), 12 IndexLevel NVARCHAR(200), 13 NextPageFID NVARCHAR(200), 14 NextPagePID NVARCHAR(200), 15 PrevPageFID NVARCHAR(200), 16 PrevPagePID NVARCHAR(200) 17 ) 創(chuàng)建一個(gè)聚集索引表 1 --只有聚集索引 2 CREATE TABLE Department( 3 DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY, 4 Name NVARCHAR(200) NOT NULL, 5 GroupName NVARCHAR(200) NOT NULL, 6 Company NVARCHAR(300), 7 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 8 ) 插入10W條記錄 1 INSERT INTO Department(name,[Company],groupname) VALUES('銷售部','中國(guó)你好有限公司XX分公司','銷售組') 2 GO 100000 將DBCC IND的結(jié)果放入DBCCRESULT表 1 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department,-1) ') 查詢Department表中的頁(yè)面情況 先說(shuō)明一下： PageType 分頁(yè)類型： 1：數(shù)據(jù)頁(yè)面；2：索引頁(yè)面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM頁(yè)面 IndexID 索引ID： 0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引 ，大于250就是text或image字段 紅色框部分都是需要關(guān)注的
第一個(gè)：IAM頁(yè)不是只有堆表才有也不只是維護(hù)堆表中的數(shù)據(jù)頁(yè)的連續(xù)，有索引的表都有，所以IAM頁(yè)不只維護(hù)數(shù)據(jù)頁(yè)，也維護(hù)索引頁(yè)的連續(xù)，在下篇說(shuō)到非聚集索引的時(shí)候 我會(huì)給出MSDN的解釋和IAM頁(yè)在聚集索引表，非聚集索引表中的情況 第二個(gè)：每個(gè)數(shù)據(jù)頁(yè)的IndexID都是1，不是說(shuō)數(shù)據(jù)頁(yè)變成了索引頁(yè)，而是說(shuō)現(xiàn)在數(shù)據(jù)頁(yè)已經(jīng)屬于聚集索引的一部分，不在堆里了 第三個(gè)：每個(gè)數(shù)據(jù)頁(yè)的IndexLevel都是0，就是說(shuō)數(shù)據(jù)頁(yè)在聚集索引的最下層 第四個(gè):索引頁(yè)和數(shù)據(jù)頁(yè),前一頁(yè)和后一頁(yè)是首尾相連的,但是數(shù)據(jù)頁(yè)和索引頁(yè)不是首尾相連的，也就是說(shuō)沒(méi)有一個(gè)數(shù)據(jù)頁(yè)的[PrevPagePID]指向14464頁(yè)或3528頁(yè) 那么在上面的聚集索引圖片中為什麼會(huì)說(shuō)索引頁(yè)指向數(shù)據(jù)頁(yè)呢？葉子節(jié)點(diǎn)就是數(shù)據(jù)頁(yè)呢？ 數(shù)據(jù)頁(yè)的index level是0，那么就是說(shuō)聚集索引的葉子節(jié)點(diǎn)就是數(shù)據(jù)頁(yè)
上面索引頁(yè)的結(jié)構(gòu)
現(xiàn)在來(lái)看一下索引頁(yè)里都有什么，運(yùn)行下面的SQL語(yǔ)句 1 DBCC TRACEON(3604,-1) 2 GO 3 4 DBCC PAGE([pratice],1,3527,3) 5 GO 6 7 8 DBCC PAGE([pratice],1,3528,3) 9 GO 10 11 DBCC PAGE([pratice],1,14464,3) 12 GO
您們應(yīng)該看到ChildPageId，所以上面我的圖為什麼會(huì)這樣畫(huà)的原因，索引頁(yè)連接著數(shù)據(jù)頁(yè)，而且一個(gè)索引頁(yè)指向多個(gè)數(shù)據(jù)頁(yè)
DepartmentID是主鍵列，從1開(kāi)始自增，那么從下圖可以看出主鍵列數(shù)據(jù)是從最左邊的索引節(jié)點(diǎn)（不是葉子節(jié)點(diǎn)）開(kāi)始排序
這里有個(gè)問(wèn)題：為什麼根節(jié)點(diǎn)只有兩行？？？是不是根節(jié)點(diǎn)只作連接作用，所以只有兩行 ？？ 答：其實(shí)我們?cè)诮⑺饕臅r(shí)候，會(huì)有一個(gè)默認(rèn)選項(xiàng)：PAD_INDEX PAD_INDEX 選項(xiàng)只作用于 非葉級(jí)索引頁(yè) 如果PAD_INDEX未指定，則默認(rèn)會(huì)為非葉級(jí)頁(yè)留出一行空間 在非葉級(jí)頁(yè)上的行數(shù)永遠(yuǎn)不會(huì)小于兩行
聚集索引頁(yè)里主鍵列DepartmentID上一行與下一行相差120條記錄,一個(gè)數(shù)據(jù)頁(yè)剛好容納120條記錄 KeyHashValue根據(jù)主鍵列的第一個(gè)字段而生成的，就算兩個(gè)表完全一樣，這個(gè)hash出來(lái)的KeyHashValue都不會(huì)一樣 我創(chuàng)建了一個(gè)一模一樣的表Department2，看到hash出來(lái)的值都不一樣
------------------------------------------------------------華麗的分割線------------------------------------------------------ 聚集索引怎麼找記錄的？？？ 這里要分兩種情況：（1）聚集索引查找 （2）聚集索引掃描 （1）聚集索引查找
放大一下索引頁(yè)
SQLSERVER聚集索引查找記錄的流程 先用二分查找法找到實(shí)際的數(shù)據(jù)頁(yè)面，然后再到數(shù)據(jù)頁(yè)里把實(shí)際數(shù)據(jù)讀出來(lái) 這里還有一個(gè)keyhashvalue用來(lái)鎖定數(shù)據(jù)行資源， 運(yùn)行下面的SQL語(yǔ)句，看一下SQLSERVER申請(qǐng)的鎖就知道了 下面實(shí)驗(yàn)我在Department2表里做的，表數(shù)據(jù)和表結(jié)構(gòu)和Department1一模一樣 
View Code 下面這個(gè)證明代碼在《SQLSERVER企業(yè)級(jí)平臺(tái)管理實(shí)踐》里找的
View Code
需要記住的是，B樹(shù)索引本身并不能找到具體的一條記錄，能找到的只是該記錄所在的頁(yè)面，數(shù)據(jù)庫(kù)把頁(yè)面載入到內(nèi)存，這里算一個(gè)讀I/O 對(duì)真?zhèn)€頁(yè)面進(jìn)行掃描，由于在內(nèi)存中查找的一個(gè)頁(yè)面里的一條記錄很快，因此通常忽略這部分掃描所用的時(shí)間 二分查找只是個(gè)縮小查找范圍的查找方法，本身作用于的對(duì)象是一個(gè)個(gè)的數(shù)據(jù)頁(yè)面，要找到具體某條記錄還需要讀取整個(gè)頁(yè)面到內(nèi)存進(jìn)行查找
（2）聚集索引掃描 先drop掉Department2表，然后重新創(chuàng)建Department2表
View Code 證明：
View Code
上圖“以下查詢使用了聚集索引查找”，由于本人寫(xiě)SQL代碼的時(shí)候沒(méi)有修改上面注釋，大家可以不用理會(huì) 為什麼會(huì)有一個(gè)鍵鎖，那么多的頁(yè)鎖，在徐海蔚老師的《SQLSERVER企業(yè)級(jí)平臺(tái)管理實(shí)踐》的書(shū)本里第361頁(yè)說(shuō)到
SQLSERVER不會(huì)停止掃描數(shù)據(jù)頁(yè)，所以才看到上圖有那么多的頁(yè)面上加了頁(yè)鎖，SQLSERVER需要逐個(gè)數(shù)據(jù)頁(yè)逐個(gè)數(shù)據(jù)頁(yè)去掃描就像堆表的全表掃描那樣。 那個(gè)鍵鎖是當(dāng)SQLSERVER找到那條記錄之后，需要在 記錄的所在頁(yè)面（即是索引頁(yè)指向那個(gè)記錄的數(shù)據(jù)頁(yè)的那一行）加上一個(gè)鍵鎖，以防止別人刪除索引頁(yè)的那一行記錄 但是聚集索引掃描是不是一定比聚集索引查找要差呢？這個(gè)不一定，要看實(shí)際情況o(∩_∩)o 那么非聚集索引掃描是不是跟聚集索引掃描一樣，所要用的時(shí)間和資源與表掃描沒(méi)有什么差別呢？？？ 大家可以看一下《SQLSERVER聚集索引與非聚集索引的再次研究（下）》本人做的一個(gè)小實(shí)驗(yàn) 實(shí)驗(yàn)證明了《SQLSERVER企業(yè)級(jí)平臺(tái)管理實(shí)踐》里第363頁(yè)說(shuō)到的內(nèi)容
------------------------------------------------華麗的分割線-------------------------------------------------------------------- 這里有一個(gè)問(wèn)題：沒(méi)有主鍵但是有聚集索引，索引頁(yè)的列數(shù)不一樣，會(huì)多了一列，而這個(gè)列（uniquifier）的作用在下面會(huì)講到 這里創(chuàng)建Department3表
View Code
可以看到只有聚集索引沒(méi)有主鍵的表會(huì)比主鍵表多了一列uniquifier列，這個(gè)列的作用會(huì)在創(chuàng)建Department5表的時(shí)候講到 -----------------------------------------------華麗的分割線------------------------------------------------------- 下面說(shuō)一下，復(fù)合主鍵或者聚集索引建立在多個(gè)字段上，KeyHashValue只會(huì)根據(jù)第一個(gè)字段生成hash key 當(dāng)你查詢的時(shí)候where 后面的字段不包含創(chuàng)建聚集索引時(shí)的第一個(gè)字段或者復(fù)合主鍵的第一個(gè)字段就會(huì)聚集索引掃描而不是聚集索引查找 創(chuàng)建Department4表
View Code
1 SELECT * FROM [dbo].[Department4] WHERE name='銷售部6' --聚集索引掃描 因?yàn)閚ame不是復(fù)合主鍵中的第一個(gè)字段 2 SELECT * FROM [dbo].[Department4] WHERE name='銷售部241' AND [DepartmentID]=241 --聚集索引查找 3 SELECT * FROM [dbo].[Department4] WHERE [DepartmentID]=241 --聚集索引查找 在建立聚集索引的時(shí)候在多個(gè)字段上建立聚集索引是沒(méi)有任何意義的 因?yàn)榫奂饕檎沂歉鶕?jù)建立索引的第一個(gè)字段來(lái)查找，索引掃描的時(shí)候會(huì)到數(shù)據(jù)頁(yè)里掃描 ，而聚集索引的每一行只是一個(gè)數(shù)據(jù)頁(yè)的范圍值從而不能直接定位到要找的那條記錄 所以只需要在數(shù)據(jù)表的一個(gè)字段上建立聚集索引就可以了，而究竟要在哪一個(gè)字段上建立聚集索引大家一定好好斟酌，本人建議那一個(gè)字段在order by中經(jīng)常要排序的 因?yàn)閿?shù)據(jù)頁(yè)都已經(jīng)按照聚集索引的第一個(gè)字段排好序的了 而不像非聚集索引的索引頁(yè)跟數(shù)據(jù)表的記錄一一對(duì)應(yīng)，掃描的時(shí)候掃描索引頁(yè)的每一行 大家可以對(duì)比一下聚集索引和非聚集索引頁(yè)的結(jié)構(gòu) 聚集索引頁(yè)的結(jié)構(gòu)
非聚集索引頁(yè)的結(jié)構(gòu)
非聚集索引頁(yè)面的結(jié)構(gòu)會(huì)在SQLSERVER聚集索引與非聚集索引的再次研究（下）里講到 ---------------------------------------------------------華麗的分割線----------------------------------------------------- 由于主鍵不允許重復(fù)值，那么就在表上創(chuàng)建一個(gè)不唯一的聚集索引，有人說(shuō)在重復(fù)值很多的列上建立聚集索引沒(méi)有意義 創(chuàng)建Department5表 在Company字段上建立聚集索引，Company字段的值全部都是"中國(guó)你好有限公司XX分公司"
View Code
View Code 在Department3表的時(shí)候講到列（uniquifier），為什麼有主鍵的表沒(méi)有這個(gè)列，而聚集索引的表有這個(gè)列，原因在于 主鍵列不能有重復(fù)值，必須是唯一的，而聚集索引允許有重復(fù)值，所以聚集索引需要增加列（uniquifier）來(lái)區(qū)分重復(fù)值 而且可以看到這里uniquifier列是沒(méi)有規(guī)律的，不像Department表每隔120行記錄在索引頁(yè)里標(biāo)記一行 看一下執(zhí)行計(jì)劃和執(zhí)行結(jié)果 1 SET STATISTICS TIME ON 2 SELECT * FROM [dbo].[Department5] WHERE [Company]='中國(guó)你好有限公司XX分公司' AND [DepartmentID]=241 3 4 SQL Server 分析和編譯時(shí)間: 5 CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。 6 7 (1 行受影響) 8 9 SQL Server 執(zhí)行時(shí)間: 10 CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。
1 SET STATISTICS TIME ON 2 SELECT * FROM [dbo].[Department5] WHERE name='銷售部106' AND [DepartmentID]=106 --聚集索引掃描 3 SQL Server 執(zhí)行時(shí)間: 4 CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。
至于應(yīng)不應(yīng)該在重復(fù)值很多的列上建立聚集索引我這里也不敢妄下判斷，因?yàn)閷?shí)際環(huán)境和這里的測(cè)試環(huán)境不一樣 在MSDN中的解釋：http://msdn.microsoft.com/zh-cn/library/ms177484(v=SQL.105).aspx
還有一個(gè)，看一下葉子節(jié)點(diǎn)中的數(shù)據(jù)頁(yè)，在每個(gè)數(shù)據(jù)頁(yè)的每行記錄中都有 Slot 101 Column 0 Offset 0x1d Length 4 UNIQUIFIER = 206 因?yàn)樾枰獦?biāo)記索引列中的唯一，所以需要在每行記錄中增加一列UNIQUIFIER ，但是這一列在select * 表中數(shù)據(jù)的時(shí)候是select不出來(lái)的 還有人說(shuō)UNIQUIFIER 是一個(gè)可變長(zhǎng)度的字段，但是Length 4已經(jīng)說(shuō)明了是一個(gè)占用4字節(jié)的字段
View Code 還有增加了UNIQUIFIER 列之后，無(wú)論索引頁(yè)和數(shù)據(jù)頁(yè)都會(huì)有所增加，性能有所損耗 下面截圖右邊的是數(shù)據(jù)頁(yè)pageid：14517中的數(shù)據(jù)，左邊的是聚集索引頁(yè)面
至于性能損耗多少，可以看一下宋大俠這篇文章： ----------------------------------------------------華麗的分割線------------------------------------------------------- 堆表中的數(shù)據(jù)頁(yè)之間[PrevPagePID],[NextPagePID]是否會(huì)首尾相連 堆表
聚集索引表
-----------------------------------------------------華麗的分割線------------------------------------------------- 聚集索引有一個(gè)特點(diǎn)，就是當(dāng)表記錄太少的時(shí)候，可能一個(gè)數(shù)據(jù)頁(yè)面就能容納下表的所有記錄，那么這時(shí)候由于可能只有一個(gè)頁(yè)面不足以構(gòu)成一棵B樹(shù) 創(chuàng)建Department6表,然后插入9條記錄 1 --只有聚集索引 2 USE [pratice] 3 GO 4 CREATE TABLE Department6 5 ( 6 DepartmentID INT IDENTITY(1, 1) NOT NULL , 7 Name NVARCHAR(200) NOT NULL , 8 GroupName NVARCHAR(200) NOT NULL , 9 Company NVARCHAR(300) , 10 ModifiedDate DATETIME NOT NULL DEFAULT ( GETDATE() ) , 11 CONSTRAINT [PK_Department6_1] PRIMARY KEY CLUSTERED 12 ( Name ASC,DepartmentID ASC ) 13 WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 14 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] 15 ) ON [PRIMARY] 16 17 18 DECLARE @i INT 19 SET @i=1 20 WHILE @i < 10 21 BEGIN 22 INSERT INTO Department6 ( name, [Company], groupname ) 23 VALUES ( '銷售部'+CAST(@i AS VARCHAR(200)), '中國(guó)你好有限公司XX分公司', '銷售組' ) 24 SET @i = @i + 1 25 END 只有一個(gè)數(shù)據(jù)頁(yè)和一個(gè)IAM頁(yè)
插入更多記錄 1 DECLARE @i INT 2 SET @i=1 3 WHILE @i < 100000 4 BEGIN 5 INSERT INTO Department6 ( name, [Company], groupname ) 6 VALUES ( '銷售部'+CAST(@i AS VARCHAR(200)), '中國(guó)你好有限公司XX分公司', '銷售組' ) 7 SET @i = @i + 1 8 END 因?yàn)閿?shù)據(jù)頁(yè)開(kāi)始多起來(lái)了，這時(shí)候聚集索引會(huì)組織會(huì)一棵有層次結(jié)構(gòu)的B樹(shù)
IndexLevel為2 表示這棵B樹(shù)有3層 0：葉子節(jié)點(diǎn)層 1：非葉子節(jié)點(diǎn)層 2：root層 PageType 分頁(yè)類型： 1：數(shù)據(jù)頁(yè)面；2：索引頁(yè)面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM頁(yè)面 IndexID 索引ID： 0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引 ，大于250就是text或image字段
大家再看一下Department2表的那部分，究竟數(shù)據(jù)頁(yè)的排序順序跟主鍵DepartmentID的排序順序有沒(méi)有關(guān)系呢？ 先創(chuàng)建Department7表，插入1000條記錄
View Code 1 --TRUNCATE TABLE [dbo].[DBCCResult] 2 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department7,-1) ') 3 4 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
看一下索引頁(yè)13791
View Code
再畫(huà)一下草圖
對(duì)比一下數(shù)據(jù)頁(yè)的首尾連接順序那張圖，不知道大家看出規(guī)律沒(méi)有 所以我把聚集索引結(jié)構(gòu)圖畫(huà)成下面這個(gè)樣子
為什麼聚集索引只能按照第一個(gè)字段生成key？為什麼數(shù)據(jù)頁(yè)只能按照第一個(gè)字段來(lái)排序？ 其實(shí)這個(gè)跟數(shù)據(jù)頁(yè)排序有關(guān)的，大家再仔細(xì)看下面兩張圖
聚集索引頁(yè)里根據(jù)第一個(gè)字段排列好這些數(shù)據(jù)頁(yè)的第一個(gè)字段的范圍值，數(shù)據(jù)頁(yè)根據(jù)這個(gè)范圍值首尾相連一一排序好 如果聚集索引按多個(gè)字段來(lái)排序，那么數(shù)據(jù)頁(yè)根本排不了，多個(gè)字段又升序，又降序？？那怎么排序??？只能按照一個(gè)字段來(lái)排序 聚集索引查找的時(shí)候，使用order by為什麼這么快，因?yàn)閿?shù)據(jù)已經(jīng)根據(jù)索引第一個(gè)字段排好序了，例子中的字段就是DepartmentID 而只有非聚集索引的表order by的時(shí)候就需要排一下序了，因?yàn)楸碇袥](méi)有聚集索引，數(shù)據(jù)頁(yè)沒(méi)有預(yù)先按照一定順序來(lái)排序 詳細(xì)可以看一下非聚集索引的結(jié)構(gòu)：SQLSERVER聚集索引與非聚集索引的再次研究（下） ---------------------------------------------華麗的分割線-------------------------------------------------------- 問(wèn)題：為什么一個(gè)表只能建立一個(gè)聚集索引 其實(shí)大家看一下我上面畫(huà)的聚集索引結(jié)構(gòu)圖和非聚集索引結(jié)構(gòu)圖就知道了
因?yàn)槿绻粋€(gè)表有聚集索引，那么他的數(shù)據(jù)頁(yè)跟索引頁(yè)有非常強(qiáng)的聯(lián)系，數(shù)據(jù)頁(yè)跟主鍵第一個(gè)字段排好序了，例子中就是“DepartmentID” 如果你再建一個(gè)聚集索引，你叫SQLSERVER應(yīng)該按哪個(gè)字段來(lái)排序？排序方式是按照你原來(lái)的那個(gè)聚集索引的DepartmentID列來(lái)排序還是 按照你新建的那個(gè)聚集索引的第一個(gè)字段來(lái)排序？？ 多個(gè)聚集索引，數(shù)據(jù)頁(yè)都按不同的字段順序排序，來(lái)建立雙向鏈表，那數(shù)據(jù)表不就亂套了？？？ 但是如果一個(gè)表中只有非聚集索引，非聚集索引里的索引頁(yè)的每一行會(huì)有一個(gè)指針值指向數(shù)據(jù)頁(yè)，數(shù)據(jù)頁(yè)依然是堆，沒(méi)有任何順序可言 所以你可以在一個(gè)表上建立多個(gè)非聚集索引也沒(méi)問(wèn)題 至于表里面只有非聚集索引表結(jié)構(gòu)是怎樣的，大家可以看一下本系列的《SQLSERVER聚集索引與非聚集索引的再次研究（下）》 到時(shí)大家就會(huì)更加清楚了o(∩_∩)o ----------------------------------------------華麗的分割線----------------------------------------------------------
為什麼根節(jié)點(diǎn)只有兩行？？？是不是根節(jié)點(diǎn)只作連接作用，所以只有兩行 ？
聚集索引就說(shuō)到這里了,有些地方有可能不對(duì),希望大家強(qiáng)烈拍磚o(∩_∩)o 也希望給個(gè)推薦o(∩_∩)o --------------------------------------------------------------------------- 2013-7-21補(bǔ)充 為什麼根節(jié)點(diǎn)只有兩行？？其實(shí)根節(jié)點(diǎn)不只有兩行的 由于出現(xiàn)二層索引節(jié)點(diǎn)需要插入大量數(shù)據(jù)，如果數(shù)據(jù)很少的話索引節(jié)點(diǎn)只有一層，并且不能用主鍵，只能創(chuàng)建聚集索引 根據(jù)宋大俠說(shuō)的，當(dāng)頁(yè)拆分的時(shí)候，根節(jié)點(diǎn)就會(huì)增加記錄，我這里提供一下腳本
1 USE [pratice] 2 GO 3 -------------------------------------------------------------------- 4 --只有聚集索引 5 --DROP TABLE [dbo].[Department] 6 CREATE TABLE Department( 7 DepartmentID int NOT NULL , 8 Name NVARCHAR(200) NOT NULL, 9 GroupName NVARCHAR(200) NOT NULL, 10 Company NVARCHAR(300), 11 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 12 ) 13 14 CREATE CLUSTERED INDEX CL_DepartmentID ON Department(DepartmentID ASC) 15 16 DECLARE @i INT 17 SET @i=1 18 WHILE @i < 300000 19 BEGIN 20 INSERT INTO Department([DepartmentID], name, [Company], groupname ) 21 VALUES ( @i,'銷售部'+CAST(@i AS VARCHAR(200)), '中國(guó)你好有限公司XX分公司', '銷售組'+CAST(@i AS VARCHAR(200)) ) 22 SET @i = @i + 3 23 END 24 25 --DROP TABLE Department 26 SELECT * FROM Department 27 28 29 30 --TRUNCATE TABLE [dbo].[DBCCResult] 31 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department,-1) ') 32 33 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 大家可以測(cè)試一下 插入數(shù)據(jù)的時(shí)候最好是根節(jié)點(diǎn)前的記錄
附上宋大俠的文章： T-SQL查詢高級(jí)—SQL Server索引中的碎片和填充因子 根節(jié)點(diǎn)的記錄行數(shù)在下面兩種情況下會(huì)有所變化 （1）向表中插入數(shù)據(jù)或更新表中數(shù)據(jù)并產(chǎn)生碎片的時(shí)候 （2）碎片很多然后alter index REORGANIZE 重組索引的時(shí)候 1 ALTER INDEX [dbo].[TableForTest].[CIX] ON [dbo].[Department] REORGANIZE ------------------------------------------------------------------------------ 2013-8-24 補(bǔ)充： 關(guān)于我在文中說(shuō)的 至于應(yīng)不應(yīng)該在重復(fù)值很多的列上建立聚集索引我這里也不敢妄下判斷，因?yàn)閷?shí)際環(huán)境和這里的測(cè)試環(huán)境不一樣
今天看了一下《SQLSERVER企業(yè)級(jí)平臺(tái)管理實(shí)踐》，書(shū)里面第437頁(yè)是這樣說(shuō)的
所以，為什麼建立聚集索引的時(shí)候，只能在一個(gè)字段上建立索引，并且這個(gè)字段最好不要重復(fù)，從數(shù)據(jù)直方圖上也能解釋這個(gè)原因 ------------------------------------------------------------------------------------------ 2013-9-15 補(bǔ)充： 如何查看聚集索引頁(yè)面的內(nèi)容，使用DBCC PAGE的時(shí)候使用1這個(gè)格式就可以了 聚集索引頁(yè)中有三條記錄，而且三條記錄的Record Type = INDEX_RECORD 1 DBCC TRACEON(3604,-1) 2 GO 3 DBCC PAGE([pratice],1,37397,1) 4 GO
1 DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。 2 3 PAGE: (1:37397) 4 5 6 BUFFER: 7 8 9 BUF @0x03E5DC3C 10 11 bpage = 0x1A8D8000 bhash = 0x00000000 bpageno = (1:37397) 12 bdbid = 5 breferences = 0 bUse1 = 6487 13 bstat = 0x3c0000b blog = 0x212159bb bnext = 0x00000000 14 15 PAGE HEADER: 16 17 18 Page @0x1A8D8000 19 20 m_pageId = (1:37397) m_headerVersion = 1 m_type = 2 21 m_typeFlagBits = 0x0 m_level = 1 m_flagBits = 0x0 22 m_objId (AllocUnitId.idObj) = 549 m_indexId (AllocUnitId.idInd) = 256 23 Metadata: AllocUnitId = 72057594073907200 24 Metadata: PartitionId = 72057594061717504 Metadata: IndexId = 1 25 Metadata: ObjectId = 1543676547 m_prevPage = (0:0) m_nextPage = (0:0) 26 pminlen = 11 m_slotCnt = 3 m_freeCnt = 8057 27 m_freeData = 129 m_reservedCnt = 0 m_lsn = (3046:261:41) 28 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 29 m_tornBits = 0 30 31 Allocation Status 32 33 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 34 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 35 ML (1:7) = NOT MIN_LOGGED 36 37 DATA: 38 39 40 Slot 0, Offset 0x60, Length 11, DumpStyle BYTE 41 42 Record Type = INDEX_RECORD Record Attributes = 43 Memory Dump @0x0A3BC060 44 45 00000000: 06010000 00283d00 000100?????????????.....(=.... 46 47 Slot 1, Offset 0x6b, Length 11, DumpStyle BYTE 48 49 Record Type = INDEX_RECORD Record Attributes = 50 Memory Dump @0x0A3BC06B 51 52 00000000: 06950100 00929100 000100?????????????........... 53 54 Slot 2, Offset 0x76, Length 11, DumpStyle BYTE 55 56 Record Type = INDEX_RECORD Record Attributes = 57 Memory Dump @0x0A3BC076 58 59 00000000: 06290300 002f0000 000100?????????????.).../..... 60 61 OFFSET TABLE: 62 63 Row - Offset 64 2 (0x2) - 118 (0x76) 65 1 (0x1) - 107 (0x6b) 66 0 (0x0) - 96 (0x60) 67 68 69 DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。
|
|
|
來(lái)自： 小魚(yú)兒363 > 《sql server》
