索引及行操作解析課件

1、SQL Server 2000的物理存儲與索引結(jié)構(gòu)10/15/20221SQL Server 2000的物理存儲與索引結(jié)構(gòu)10/SQL Server 2000的物理存儲與索引結(jié)構(gòu)數(shù)據(jù)庫與數(shù)據(jù)庫文件表的物理存儲索引及行操作10/15/20222SQL Server 2000的物理存儲與索引結(jié)構(gòu)數(shù)據(jù)庫與SQL Server 2000的物理存儲與索引結(jié)構(gòu)索引及行操作10/15/20223SQL Server 2000的物理存儲與索引結(jié)構(gòu)索引及行認識SQL Server數(shù)據(jù)庫索引SQL Server采用標準的B-樹來存儲索引信息；SQL Server的索引由根級頁、中間級頁和葉級頁組成；索引的級數(shù)與表

2、中行的多少和索引碼的大小有關(guān)；SQL Server的索引分為簇集索引和非簇集索引兩種。10/15/20224認識SQL Server數(shù)據(jù)庫索引SQL Server采用標認識SQL Server數(shù)據(jù)庫索引10/15/20225認識SQL Server數(shù)據(jù)庫索引10/11/20225索引及行操作基本原理SQL Server的索引組織在SQL Server中創(chuàng)建索引SQL Server的索引頁結(jié)構(gòu)索引的空間需求索引的管理SQL Server的特殊索引插入行分裂頁刪除行更新行10/15/20226索引及行操作基本原理插入行10/11/20226基本原理堆文件這是最簡單、最原始、也是最早使用的一種文件結(jié)

3、構(gòu)；文件中的記錄在邏輯上沒有特定的次序，在物理上也不一定鄰接，就好像是堆在一起一樣；這種文件插入很容易，但查找就不方便了，需要順序掃描文件。10/15/20227基本原理堆文件10/11/20227基本原理B-樹與B+-樹索引結(jié)構(gòu)B-樹與B+-樹是樹形數(shù)據(jù)結(jié)構(gòu)的特例；B-樹最早是由R.Bayer和E.Mccreight兩人提出；B-樹的特點是：高效、易變、平衡以及獨立于硬件結(jié)構(gòu)等等；B-樹是目前數(shù)據(jù)庫管理系統(tǒng)中最重要的動態(tài)文件索引結(jié)構(gòu)；10/15/20228基本原理B-樹與B+-樹索引結(jié)構(gòu)10/11/20228B-樹與B+-樹索引結(jié)構(gòu)B+-樹是B-樹的改進：在B-樹中，指向數(shù)據(jù)的指針可以出現(xiàn)在

4、B-樹的任何一級在B+-樹中，數(shù)據(jù)指針只能出現(xiàn)在葉級結(jié)點B+-樹的葉級結(jié)點與中間級結(jié)點不同：對于每個索引碼值，B+-樹的葉級結(jié)點都要有一個對應(yīng)的索引項，也稱為索引記錄或索引行B+-樹葉級結(jié)點的索引項由兩部分組成，一部分是索引碼值，另一部分是索引碼值對應(yīng)的指針基本原理10/15/20229B-樹與B+-樹索引結(jié)構(gòu)基本原理10/11/20229B+-樹索引總體結(jié)構(gòu)B+-樹索引是一個多級索引，但是其結(jié)構(gòu)不同于多級順序索引B+-樹索引采用平衡樹結(jié)構(gòu)，即每個葉結(jié)點到根的路徑長度都相同每個非葉結(jié)點有n/2到n個子女，n對特定的樹是固定的基本原理10/15/202210B+-樹索引基本原理10/11/202

5、210B+-樹索引總體結(jié)構(gòu)B+-樹的所有結(jié)點的數(shù)據(jù)結(jié)構(gòu)都相同，它最多包含n-1個索引碼值K1、K2、Kn-1，以及n個指針P1、P2、Pn，每個結(jié)點中的索引碼值都按次序存放，即，如果ij，那么KiKj，如下圖所示：基本原理10/15/202211B+-樹索引基本原理10/11/202211B+-樹索引葉結(jié)點指針Pi(i=1，2，n-1)指向具有索引碼值為Ki的一個文件記錄或一個指針桶，桶中的每個指針指向具有索引碼值為Ki的一個文件記錄指針桶只在數(shù)據(jù)文件不按索引碼順序物理存儲記錄時才使用，指針Pn具有特殊的作用基本原理10/15/202212B+-樹索引基本原理10/11/202212B+-樹索

6、引葉結(jié)點每個葉結(jié)點最多可有n-1個索引碼值，最少也要有(n-1)/2個索引碼值各個葉結(jié)點中索引碼值的范圍互不相交要使B+-樹索引成為稠密索引，數(shù)據(jù)文件中的各個索引碼值就必須出現(xiàn)在某個葉結(jié)點中，且只能出現(xiàn)一次基本原理10/15/202213B+-樹索引基本原理10/11/202213B+-樹索引葉結(jié)點由于各個葉結(jié)點按照所含的索引碼值有一個線性順序，所以就可以利用各個葉結(jié)點的指針Pn將葉結(jié)點按索引碼順序鏈接在一起這種排序能夠高效地對文件進行順序處理，而B+-樹索引的其他結(jié)構(gòu)能夠高效地對文件進行隨機處理，如下圖所示 基本原理10/15/202214B+-樹索引基本原理10/11/202214B+-樹

7、索引葉結(jié)點基本原理10/15/202215B+-樹索引基本原理10/11/202215B+-樹索引非葉結(jié)點B+-樹索引的非葉結(jié)點形成葉結(jié)點上的一個多級(稀疏)索引非葉結(jié)點與葉結(jié)點的數(shù)據(jù)結(jié)構(gòu)相同，即都含有能夠存儲n-1個索引碼值和n個指針的存儲單元，只不過非葉結(jié)點中的所有指針都指向樹中的結(jié)點基本原理10/15/202216B+-樹索引基本原理10/11/202216B+-樹索引非葉結(jié)點如果一個非葉結(jié)點有m個指針，則n/2mn。若mn，則非葉結(jié)點中指針Pm之后的所有存儲空間作為預(yù)留空間，與葉結(jié)點的區(qū)別在于結(jié)點的最后一個指針Pm和Pn的位置與指向不同，如下圖所示基本原理10/15/202217B+-

8、樹索引基本原理10/11/202217B+-樹索引非葉結(jié)點在一個含有m個指針的非葉結(jié)點中，指針Pi(i=2，3，m-1)指向一棵子樹，該子樹的所有結(jié)點的索引碼值大于等于Ki-1而小于Ki。指針Pm指向子樹中所含索引碼值大于等于Km-1的那一部分，而指針P1指向子樹中所含索引碼值小于K1的那一部分，如上圖所示基本原理IKk1Ki-1IKkiKm-1IK10/15/202218B+-樹索引基本原理IKk1Ki-1IKkiKm-1B+-樹索引根結(jié)點根結(jié)點的數(shù)據(jù)結(jié)構(gòu)也與葉結(jié)點相同根結(jié)點包含的指針數(shù)可以小于n/2，但是，除非整棵樹只有一個結(jié)點，否則根結(jié)點必須至少包含兩個指針基本原理10/15/20221

9、9B+-樹索引基本原理10/11/202219B+-樹索引舉例：下面是一個B+-樹索引結(jié)構(gòu)的示意圖，其中n=3基本原理10/15/202220B+-樹索引基本原理10/11/202220利用B+-樹索引進行查詢假設(shè)要找出索引碼值為K的所有記錄首先檢查根結(jié)點，找到大于K的最小索引碼值，設(shè)是Ki，則沿著指針Pi走；如果KK1，那么沿著指針P1走；如果以上兩個條件都不滿足且KKm-1，m是該結(jié)點的指針數(shù)，則沿著指針Pm走；對新到達的結(jié)點，重復(fù)以上步驟，最終到達一個葉結(jié)點?；驹?0/15/202221利用B+-樹索引進行查詢基本原理10/11/202221利用B+-樹索引進行查詢舉例：基本原理10

10、/15/202222利用B+-樹索引進行查詢基本原理10/11/202222簇集索引記錄的物理順序與索引碼順序一致簇集索引的葉級頁就是表的真正的數(shù)據(jù)頁，而不僅僅是索引碼，同時也不需要數(shù)據(jù)指針。換句話說，數(shù)據(jù)本身成為簇集索引的一部分具有簇集索引的表的數(shù)據(jù)頁，即葉級頁，要鏈接成一個雙向鏈表，即頁鏈SQL Server的索引組織10/15/202223簇集索引SQL Server的索引組織10/11/20222簇集索引頁鏈中頁的邏輯順序以及數(shù)據(jù)頁上行的邏輯順序與索引碼的順序一致一個表只能有一個簇集索引，WHY？簇集索引都是唯一(UNIQUE)索引，即在索引的葉級不會有重復(fù)的索引碼值SQL Serve

11、r會根據(jù)需要給數(shù)據(jù)行增加一個唯一標識符來保證索引的唯一性SQL Server的索引組織10/15/202224簇集索引SQL Server的索引組織10/11/20222非簇集索引樹的葉級頁里的每一個索引行包含一個標簽(Bookmark)告訴SQL Server到哪兒去找對應(yīng)索引碼的數(shù)據(jù)行，標簽一般有兩種形式：如果表有一個簇集索引，則標簽就是對應(yīng)數(shù)據(jù)行的簇集索引碼如果表是一個堆，則標簽就是行標識符RID，即真正的行定位器-File#:Page#:Slot#SQL Server的索引組織10/15/202225非簇集索引SQL Server的索引組織10/11/2022非簇集索引非簇集索引不影響

12、表中數(shù)據(jù)頁的組織非簇集索引是完全獨立于表的數(shù)據(jù)結(jié)構(gòu)，而簇集索引則是將索引結(jié)構(gòu)和表合二為一，所以一般建議在每個表上建一個簇集索引每個表可以有多個非簇集索引，最多可以有249個SQL Server的索引組織10/15/202226非簇集索引SQL Server的索引組織10/11/2022非簇集索引利用簇集索引進行檢索最后得到的就是真正的數(shù)據(jù)，而利用非簇集索引進行檢索得到的是bookmark：簇集索引碼、RIDSQL Server的索引組織10/15/202227非簇集索引SQL Server的索引組織10/11/2022語法格式在SQL Server中創(chuàng)建索引10/15/202228語法格式在S

13、QL Server中創(chuàng)建索引10/11/2022主要參數(shù)說明UNIQUE在表或視圖上創(chuàng)建一個唯一索引，唯一索引是指所有行的索引碼值都不相同的索引視圖上的簇集索引也必須是UNIQUE的在創(chuàng)建UNIQUE索引時，多個NULL值被認為是重復(fù)值在SQL Server中創(chuàng)建索引10/15/202229主要參數(shù)說明在SQL Server中創(chuàng)建索引10/11/20主要參數(shù)說明CLUSTERED在該參數(shù)約束下創(chuàng)建了一個對象，對象中行的物理順序與行的索引順序一致表或視圖只允許有一個簇集索引，擁有簇集索引的視圖叫做索引視圖，視圖上的唯一簇集索引必須在該視圖上定義任何其他索引之前創(chuàng)建在SQL Server中創(chuàng)建索引

14、10/15/202230主要參數(shù)說明在SQL Server中創(chuàng)建索引10/11/20主要參數(shù)說明CLUSTERED簇集索引最好也在建立任何非簇集索引之前創(chuàng)建。在表上創(chuàng)建簇集索引時，該表已有的非簇集索引必須重構(gòu)，因為非簇集索引葉級包含的標簽，以及簇集索引的唯一性可能引起數(shù)據(jù)行的變化如果沒有指定CLUSTERED關(guān)鍵詞，則被創(chuàng)建的索引就是非簇集索引在SQL Server中創(chuàng)建索引10/15/202231主要參數(shù)說明在SQL Server中創(chuàng)建索引10/11/20主要參數(shù)說明NONCLUSTERED在該參數(shù)約束下創(chuàng)建了一個對象，它指定了表中行的一種邏輯順序?qū)σ粋€非簇集索引來說，表中行的物理順序與它們

15、的索引順序無關(guān)在SQL Server中創(chuàng)建索引10/15/202232主要參數(shù)說明在SQL Server中創(chuàng)建索引10/11/20主要參數(shù)說明NONCLUSTERED非簇集索引的葉級包含索引行，每個索引行包含非簇集索引碼值及一個或多個標簽，每個標簽指向包含相應(yīng)非簇集索引碼值的數(shù)據(jù)行對視圖來說只能在擁有簇集索引的視圖上創(chuàng)建非簇集索引，因此視圖上的非簇集索引的標簽總是視圖行的簇集索引碼在SQL Server中創(chuàng)建索引10/15/202233主要參數(shù)說明在SQL Server中創(chuàng)建索引10/11/20約束與索引當(dāng)聲明一個PRIMARY KEY或UNIQUE約束時，SQL Server就在一個或多個列

16、上創(chuàng)建一個唯一(UNIQUE)索引，就好像是使用CREATE INDEX命令創(chuàng)建了索引一樣，而且這個索引是簇集的為支持這些約束而建構(gòu)的索引的名字與相應(yīng)約束的名字相同在SQL Server中創(chuàng)建索引10/15/202234約束與索引在SQL Server中創(chuàng)建索引10/11/202約束與索引就索引的內(nèi)部存儲與維護而言，用CREATE INDEX命令創(chuàng)建的唯一索引與為支持約束而創(chuàng)建的唯一索引沒有任何區(qū)別最大的區(qū)別就是刪除索引的方式不同：DROP INDEX命令只能刪除用CREATE INDEX命令創(chuàng)建的索引在SQL Server中創(chuàng)建索引10/15/202235約束與索引在SQL Server中創(chuàng)

17、建索引10/11/202索引頁與SQL Server的所有其他類型的頁一樣，索引頁的大小也是固定的8KB，即8192字節(jié)和數(shù)據(jù)頁一樣：索引頁有一個96字節(jié)的頁頭；索引頁的尾部也有用來表示頁中行偏移的兩字節(jié)的行偏移數(shù)組。SQL Server的索引頁結(jié)構(gòu)10/15/202236索引頁SQL Server的索引頁結(jié)構(gòu)10/11/20223索引頁每個索引在sysindexes系統(tǒng)表中都有一行，該行的indid值為：1：表示簇集索引2到250：表示非簇集索引255：表示LOB數(shù)據(jù)SQL Server的索引頁結(jié)構(gòu)10/15/202237索引頁SQL Server的索引頁結(jié)構(gòu)10/11/20223索引頁索引

18、頁的頭信息與數(shù)據(jù)頁的頭信息幾乎完全一致唯一的區(qū)別就是type字段的值，type為1表示該頁為數(shù)據(jù)頁，type為2表示該頁為索引頁索引頁頁頭的level字段和indexID字段的值都是非0的，而對數(shù)據(jù)頁來說，它們的值總是0SQL Server的索引頁結(jié)構(gòu)10/15/202238索引頁SQL Server的索引頁結(jié)構(gòu)10/11/20223索引頁在SQL Server中有三種基本類型的索引頁：非簇集索引的葉級頁非簇集索引的結(jié)點(非葉級)頁(包括根)簇集索引的結(jié)點(非葉級)頁(包括根)簇集索引的葉級頁并沒有一個真正單獨的結(jié)構(gòu)，因為它們就是數(shù)據(jù)頁SQL Server的索引頁結(jié)構(gòu)10/15/202239索

19、引頁SQL Server的索引頁結(jié)構(gòu)10/11/20223具有唯一標識符的簇集索引行如果在創(chuàng)建簇集索引時沒有指定選項UNIQUE，SQL Server會在必要的時候在數(shù)據(jù)行里增加一個4字節(jié)的字段使得每一個索引碼都唯一的這個必要的時候就是重復(fù)的索引碼被增加到表里時:Create unique clustered：有和沒有unique的區(qū)別是什么？SQL Server的索引頁結(jié)構(gòu)10/15/202240具有唯一標識符的簇集索引行SQL Server的索引頁結(jié)構(gòu)1具有唯一標識符的簇集索引行舉例：創(chuàng)建一個不唯一的簇集索引建表及索引SQL Server的索引頁結(jié)構(gòu)10/15/202241具有唯一標識符的

20、簇集索引行SQL Server的索引頁結(jié)構(gòu)1具有唯一標識符的簇集索引行舉例：創(chuàng)建一個不唯一的簇集索引插入第一行數(shù)據(jù)SQL Server的索引頁結(jié)構(gòu)10/15/202242具有唯一標識符的簇集索引行SQL Server的索引頁結(jié)構(gòu)1具有唯一標識符的簇集索引行舉例：創(chuàng)建一個不唯一的簇集索引表中數(shù)據(jù)行的內(nèi)容SQL Server的索引頁結(jié)構(gòu)10/15/202243具有唯一標識符的簇集索引行SQL Server的索引頁結(jié)構(gòu)1具有唯一標識符的簇集索引行舉例：創(chuàng)建一個不唯一的簇集索引往表中插入簇集索引碼重復(fù)的數(shù)據(jù)SQL Server的索引頁結(jié)構(gòu)10/15/202244具有唯一標識符的簇集索引行SQL Ser

21、ver的索引頁結(jié)構(gòu)1具有唯一標識符的簇集索引行舉例：創(chuàng)建一個不唯一的簇集索引SQL Server的索引頁結(jié)構(gòu)10/15/202245具有唯一標識符的簇集索引行SQL Server的索引頁結(jié)構(gòu)1具有唯一標識符的簇集索引行舉例：創(chuàng)建一個不唯一的簇集索引具有重復(fù)簇集索引碼的數(shù)據(jù)行內(nèi)容Slot 0與Slot 1、2不同Slot 1、2有變長列Slot 1、2中變長列的內(nèi)容就是真正的4字節(jié)的唯一標識符SQL Server的索引頁結(jié)構(gòu)10/15/202246具有唯一標識符的簇集索引行SQL Server的索引頁結(jié)構(gòu)1索引行格式索引行與數(shù)據(jù)行的結(jié)構(gòu)非常相似，在索引行里沒有TagB和Fsize字段Fsize原

22、本表示行的定長部分在什么位置結(jié)束，而在索引行里取而代之的是頁頭的pminlen值。pminlen值表示行的定長部分的結(jié)束位置在行中的偏移量。如果索引行沒有變長列和空值列，則pminlen就是行尾的偏移量SQL Server的索引頁結(jié)構(gòu)10/15/202247索引行格式SQL Server的索引頁結(jié)構(gòu)10/11/202索引行格式只有當(dāng)索引行有在定義時允許為空值的列時，Ncol字段和Nullbits字段，即空值位圖，才會同時出現(xiàn)Ncol字段的值表示索引行里有多少列。而在數(shù)據(jù)行里，不管任意一列是否允許為空，數(shù)據(jù)行都一定要有Ncol字段和Nullbits字段SQL Server的索引頁結(jié)構(gòu)10/15/

23、202248索引行格式SQL Server的索引頁結(jié)構(gòu)10/11/202索引行格式SQL Server的索引頁結(jié)構(gòu)10/15/202249索引行格式SQL Server的索引頁結(jié)構(gòu)10/11/202索引行格式根據(jù)索引行在索引中的級別，它所包含的數(shù)據(jù)內(nèi)容會有所不同除了那些葉級的索引行，所有的索引行都包含一個6字節(jié)的下頁指針，索引碼值和標簽除外下頁指針是非葉級索引行的定長數(shù)據(jù)部分的最后一列SQL Server的索引頁結(jié)構(gòu)10/15/202250索引行格式SQL Server的索引頁結(jié)構(gòu)10/11/202索引行格式在非簇集索引中，把非簇集索引碼和標簽當(dāng)作索引行的正常列看待根據(jù)每個碼列是如何定義的，非

24、簇集索引碼和標簽可以處于索引行的固定部分或可變部分如果一個標簽就是RID，無論如何，它總是索引行定長數(shù)據(jù)的一部分SQL Server的索引頁結(jié)構(gòu)10/15/202251索引行格式SQL Server的索引頁結(jié)構(gòu)10/11/202索引行格式如果標簽是一個簇集索引碼值，且簇集索引和非簇集索引共享了某些列，則真正的數(shù)據(jù)在索引行里只存儲一次例如，假設(shè)某表有一個簇集索引，而且簇集索引碼是lastname列，同時該表在firstname和lastname列上還有一個非簇集索引，則非簇集索引的索引行不會存儲lastname的值兩次SQL Server的索引頁結(jié)構(gòu)10/15/202252索引行格式SQL Se

25、rver的索引頁結(jié)構(gòu)10/11/202簇集索引的結(jié)點行簇集索引的結(jié)點行包含一個指向下一級索引頁的指針，即下頁指針，及該指針所指向頁的第一個索引碼值(最小)SQL Server的索引頁結(jié)構(gòu)10/15/202253簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行舉例：創(chuàng)建表及簇集索引SQL Server的索引頁結(jié)構(gòu)10/15/202254簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行舉例：往表里插入大量數(shù)據(jù)SQL Server的索引頁結(jié)構(gòu)10/15/202255簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行舉例

26、：索引的根頁：PAGE158分配給該表的第一個數(shù)據(jù)頁：PAGE160。第一個的含義是首次分配給該表的數(shù)據(jù)頁，也是分配給該表的所有數(shù)據(jù)頁的頁號從小到大排列時的位置而該表的第一條記錄-1250-最終被插入在數(shù)據(jù)頁：PAGE740SQL Server的索引頁結(jié)構(gòu)10/15/202256簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行舉例：SQL Server的索引頁結(jié)構(gòu)下頁指針列10/15/202257簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)下頁指針列1簇集索引的結(jié)點行舉例：下一級索引頁：PAGE729SQL Server的索引頁結(jié)構(gòu)10/15/202258簇集索

27、引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行舉例：頁729(索引的中間級頁)的第一行，即Slot 0的索引碼值是無意義的，但是下頁指針應(yīng)當(dāng)指向簇集索引的葉級頁，而且應(yīng)當(dāng)是簇集索引碼順序下的第一個數(shù)據(jù)頁。表示下頁指針的16進制數(shù)0 x02e3就是10進制數(shù)的739。頁739是一個葉級頁，它具有普通數(shù)據(jù)頁的頁結(jié)構(gòu)SQL Server的索引頁結(jié)構(gòu)10/15/202259簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行舉例：簇集索引碼順序下的第一個數(shù)據(jù)頁，即頁739的第一條記錄的String1列的列值為10000索引列String1的數(shù)據(jù)類型是ch

28、ar(5)，本例插入的第一個數(shù)據(jù)是1250，但將其轉(zhuǎn)換成char(5)后就是1250(表示空格，其ASCII碼值是20)。當(dāng)按字母順序排列時，10000排在1250的前面SQL Server的索引頁結(jié)構(gòu)10/15/202260簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行為什么“除了葉級頁以外，每個索引頁的第一個索引碼項，即索引頁中Slot0的索引碼項常常是空或無意義”？根據(jù)B+樹結(jié)點結(jié)構(gòu)的約定，指針Pi(i=2，3，n-1)指向一棵子樹，該子樹的所有結(jié)點的索引碼值大于等于Ki-1而小于KiSQL Server的索引頁結(jié)構(gòu)10/15/202261簇集索引的結(jié)點行S

29、QL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行為什么“除了葉級頁以外，每個索引頁的第一個索引碼項，即索引頁中Slot0的索引碼項常常是空或無意義”？而在SQL Server的索引頁中，索引記錄主要由兩項組成：索引碼值K和下頁指針P，其中索引碼值K是下頁指針P所指向頁的索引碼值的最小值，即該頁的第一個索引碼值SQL Server的索引頁結(jié)構(gòu)10/15/202262簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/15/202263簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行SQL Serv

30、er的索引頁結(jié)構(gòu)10/15/202264簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/簇集索引的結(jié)點行為什么“索引頁中Slot 0的索引碼項常常是空或無意義”？SQL Server的索引頁結(jié)構(gòu)10/15/202265簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11/非簇集索引的葉級行一般來說，非簇集索引的葉級行包含每一個索引碼值和相應(yīng)的標簽舉例1：建立在堆上的唯一非簇集索引SQL Server的索引頁結(jié)構(gòu)10/15/202266非簇集索引的葉級行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的葉級行舉例1：建立在堆上的唯一非簇集索引葉級索引行的標簽是RID，共8個

31、字節(jié)SQL Server的索引頁結(jié)構(gòu)RID列10/15/202267非簇集索引的葉級行SQL Server的索引頁結(jié)構(gòu)RID列1非簇集索引的葉級行舉例2：在建立非簇集索引之前，先在一個varchar列上建立一個簇集索引，以便非簇集索引的標簽是簇集索引碼 SQL Server的索引頁結(jié)構(gòu)10/15/202268非簇集索引的葉級行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的葉級行舉例2：因為簇集索引碼是可變長的，所以索引行中有關(guān)簇集索引碼的信息位于列數(shù)和NULL位圖之后SQL Server的索引頁結(jié)構(gòu)10/15/202269非簇集索引的葉級行SQL Server的索引頁結(jié)構(gòu)10/11非簇

32、集索引的葉級行舉例3：建立在String1和String2上的復(fù)合非簇集索引以及建立在String2上的(部分重疊的)簇集索引SQL Server的索引頁結(jié)構(gòu)10/15/202270非簇集索引的葉級行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的葉級行舉例3：SQL Server的索引頁結(jié)構(gòu)只有當(dāng)索引行有在定義時允許為空值的列時，Ncol字段和Nullbits字段，即空值位圖，才會同時出現(xiàn)！10/15/202271非簇集索引的葉級行SQL Server的索引頁結(jié)構(gòu)只有當(dāng)索引非簇集索引的葉級行舉例3：列String2的值(在本例中是630)既是非簇集索引碼的一部分，同時又是標簽雖然列St

33、ring2在這里扮演兩個角色，但它的值沒有重復(fù)存儲由于這是葉級行，沒有下頁指針，而且由于該非簇集索引建立在簇集表上，因此標簽是簇集索引碼，而不是RIDSQL Server的索引頁結(jié)構(gòu)10/15/202272非簇集索引的葉級行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的結(jié)點行非簇集索引的非葉級結(jié)點行只是用來幫助SQL Server向下遍歷較低級的索引頁如果非簇集索引是唯一的，其結(jié)點行就只需要包含非簇集索引碼和下頁指針；如果非簇集索引不是唯一的，則索引結(jié)點行除了包含索引碼值和下頁指針以外，還必須包含一個標簽,WHY?SQL Server的索引頁結(jié)構(gòu)10/15/202273非簇集索引的結(jié)點

34、行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的結(jié)點行舉例1SQL Server的索引頁結(jié)構(gòu)10/15/202274非簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的結(jié)點行舉例1SET NOCOUNT ONGODECLARE k intSET k = 1250WHILE k 1GO輸出結(jié)果如下：SQL Server的索引頁結(jié)構(gòu)10/15/202276非簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的結(jié)點行舉例1SQL Server的索引頁結(jié)構(gòu)下頁指針列10/15/202277非簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)下頁指針列非簇集索

35、引的結(jié)點行舉例1：從創(chuàng)建索引行的目的來看，SQL Server并不關(guān)心在非唯一的非簇集索引中索引碼是否真的包含重復(fù)值。如果沒有將非簇集索引定義為唯一的(在CREATE INDEX語句中使用UNIQUE選項)，即使所有的非簇集索引碼值都是唯一的，非葉級的索引行都將包含標簽SQL Server的索引頁結(jié)構(gòu)10/15/202278非簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的結(jié)點行舉例2SQL Server的索引頁結(jié)構(gòu)10/15/202279非簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的結(jié)點行舉例2SET NOCOUNT ONGODECLARE k

36、 intSET k = 1250WHILE k 1GO輸出結(jié)果如下： SQL Server的索引頁結(jié)構(gòu)10/15/202281非簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)10/11非簇集索引的結(jié)點行舉例2SQL Server的索引頁結(jié)構(gòu)在非簇集索引中，把非簇集索引碼和標簽當(dāng)作索引行的正常列看待如果非簇集索引是唯一的，其結(jié)點行就只需要包含非簇集索引碼和下頁指針下頁指針列10/15/202282非簇集索引的結(jié)點行SQL Server的索引頁結(jié)構(gòu)在非簇集B樹的大小索引的大小，通常是指索引樹的大小簇集索引包括真正的數(shù)據(jù)，即使你刪除了簇集索引，真正的數(shù)據(jù)仍然存在對非簇集索引來說，表中的每個數(shù)據(jù)行在

37、索引的葉級都要有一個索引行對簇集索引來說，表的每個數(shù)據(jù)頁在索引的非葉級都要有一個索引行索引的空間需求10/15/202283B樹的大小索引的空間需求10/11/202283B樹的大小對簇集索引來說，可以通過以下計算過程得到簇集索引的葉級之上的索引級需要多少索引頁索引頁填充因子，簡稱 = 每頁可利用的字節(jié)數(shù)/索引行的大小1級索引頁的個數(shù) = 數(shù)據(jù)頁的個數(shù)/2級索引頁的個數(shù) = 1級索引頁的個數(shù)/索引的空間需求10/15/202284B樹的大小索引的空間需求10/11/202284B樹的大小對簇集索引來說，可以通過以下計算過程得到簇集索引的葉級之上的索引級需要多少索引頁3級索引頁的個數(shù) = 2級索

38、引頁的個數(shù)/一旦到了某一級后只需要一個索引頁就停止計算，這一頁就是簇集索引的根頁非簇集索引大小的計算過程與此類似索引的空間需求10/15/202285B樹的大小索引的空間需求10/11/202285B樹的大小對簇集索引來說，10000頁的表大概需要16個索引頁，索引的大小不到表的1%。因此常常以1%作為簇集索引空間需求的大致估計對非簇集索引來說，像簇集索引那樣給出一個好的大致估計很難。一般非簇集索引的大小可能是表大小的30%到40%，甚至有可能比表本身還要大索引的空間需求10/15/202286B樹的大小索引的空間需求10/11/202286索引的實際大小相對于前面剛剛描述的大致估計，表或索引

39、真正使用空間的信息存儲在sysindexes系統(tǒng)表里sysindexes表里的rowcnt列包含了相應(yīng)表中數(shù)據(jù)行的數(shù)目對簇集索引或堆來說，sysindexes表里的dpages列包含了已使用的數(shù)據(jù)頁數(shù)目，而對其他所有索引來說，dpages列包含了已使用的索引頁的數(shù)目索引的空間需求10/15/202287索引的實際大小索引的空間需求10/11/202287索引碎片的類型SQL Server自動維護索引，但隨著時間的推移，索引可能會變得越來越碎，索引碎片有兩類：內(nèi)部碎片：當(dāng)索引頁里還有空間可利用時，也就是說索引空間沒有充分利用的時候，就會出現(xiàn)內(nèi)部碎片外部碎片：當(dāng)頁的邏輯順序和物理順序不匹配的時候，

40、或者是當(dāng)屬于一個表的盤區(qū)不連續(xù)的時候，外部碎片就出現(xiàn)了索引的管理10/15/202288索引碎片的類型索引的管理10/11/202288索引碎片的問題內(nèi)部碎片內(nèi)部碎片影響索引的性能，但可以減少分裂頁的操作外部碎片只有當(dāng)SQL Server順序掃描表或索引的時候，外部碎片才真正有害索引的管理10/15/202289索引碎片的問題索引的管理10/11/202289索引碎片的檢測索引的管理10/15/202290索引碎片的檢測索引的管理10/11/202290刪除索引碎片重建索引以便讓SQL Server分配新的連續(xù)頁使用DROP INDEX和CREATE INDEX命令使用DBCC DBREIND

41、EX命令，它能夠在一次操作里重建一個表上的所有索引使用具有drop_existing子句的CREATE INDEX命令索引的管理10/15/202291刪除索引碎片索引的管理10/11/202291刪除索引碎片重建索引的問題當(dāng)索引支持約束的時候，不能使用DROP INDEX命令重建索引的時候表是不可用的如果正在重建非簇集索引， SQL Server就會給表加共享鎖，其他進程可查詢但不能更新該表如果正在重建簇集索引，SQL Server就會給表加排他鎖，其他進程根本不能訪問該表索引的管理10/15/202292刪除索引碎片索引的管理10/11/202292刪除索引碎片SQL Server不用完全

42、重建索引也能夠整理索引的碎片DBCC INDEXDEFRAG命令可以整理索引的葉級頁，以便它們的物理順序與葉級結(jié)點的邏輯順序相匹配，以提高索引掃描的性能DBCC INDEXDEFRAG命令的語法格式索引的管理10/15/202293刪除索引碎片索引的管理10/11/202293刪除索引碎片DBCC INDEXDEFRAG命令的語法格式索引的管理10/15/202294刪除索引碎片索引的管理10/11/202294SQL Server 2000有兩種特殊索引計算列上的索引和視圖上的索引如果沒有索引，這兩中結(jié)構(gòu)都是純邏輯的，不涉及數(shù)據(jù)的物理存儲，而只保存相關(guān)的計算公式和SELECT語句有了這些特殊

43、索引之后，SQL Server就將那些邏輯數(shù)據(jù)真正實體化成索引的物理葉級。因此，建立在計算列上和視圖上的第一個索引必須是簇集的SQL Server的特殊索引10/15/202295SQL Server 2000有兩種特殊索引SQL Serv往堆里插入新行如果表沒有簇集索引，即表是一個堆，則新行總是插入到表中有可用空間的地方利用IAM頁和PFS頁，SQL Server就可以知道文件的哪些盤區(qū)已經(jīng)屬于該表以及在哪些盤區(qū)中哪些頁有空間可利用如果沒有頁有空間可利用，SQL Server就必須給表分配一個全新的盤區(qū)。利用GAM頁和SGAM頁，SQL Server就可以找到可分配給一個對象的盤區(qū)（前8頁來

44、自混合盤區(qū)）插入行10/15/202296往堆里插入新行插入行10/11/202296往具有簇集索引的表里插入新行什么是新行？新行可以是INSERT語句的直接結(jié)果也可以是UPDATE語句的結(jié)果，當(dāng)然這個UPDATE語句是按照先刪除后插入的策略執(zhí)行的，即將UPDATE語句分解為DELETE后跟INSERT語句新行的簇集索引碼列的值指導(dǎo)新行被插入到特定的頁，即新行的簇集位置插入行10/15/202297往具有簇集索引的表里插入新行插入行10/11/202297往具有簇集索引的表里插入新行新行的簇集位置如果簇集位置的當(dāng)前頁沒有空間，通過頁分裂將新行插入到頁的銜接處頁分裂要求SQL Server必須分

45、配新頁并將新頁鏈接到頁鏈中如果可能，這個新頁是從與其他頁相同的盤區(qū)，即已有的盤區(qū)分配來的如果所有的盤區(qū)都是滿的，新的盤區(qū)就要分配給這個表插入行10/15/202298往具有簇集索引的表里插入新行插入行10/11/202298往具有簇集索引的表里插入新行如果簇集索引沒有被聲明為UNIQUE的，而插入的新行里又有重復(fù)的索引碼值，那么一個唯一標識符就會為具有相同索引碼值的所有后續(xù)行自動生成，因為SQL Server要求所有的簇集索引都是唯一的！插入行10/15/202299往具有簇集索引的表里插入新行插入行10/11/202299頁分裂及其類型SQL Server找到新頁之后，原始頁里的數(shù)據(jù)就必須一

46、分為二：原始頁只留下一半數(shù)據(jù)，而另一半數(shù)據(jù)移動到新頁里，這就是所謂的頁分裂有時，SQL Server發(fā)現(xiàn)即使在分裂頁之后，新行也沒有足夠的存儲空間可利用，這可能是由于新行的變長字段的值所占用的字節(jié)非常多而造成的分裂頁10/15/2022100頁分裂及其類型分裂頁10/11/2022100頁分裂及其類型在SQL Server中，頁分裂的類型依賴于被分裂的頁的類型，被分裂的頁可能是：索引的根頁索引的中間頁數(shù)據(jù)頁，也是簇集索引的葉級頁；而非簇集索引的葉級頁分裂時按照索引的中間頁來對待分裂頁10/15/2022101頁分裂及其類型分裂頁10/11/2022101分裂索引的根頁如果為了插入一個新的索引行

47、而必須分裂索引的根頁，則步驟如下：要有兩個新頁被分配給該索引來自根頁的所有索引行被分割到這兩個新頁里新的索引行則被插入到這兩個新頁之一的適當(dāng)位置原始的根頁仍然是分裂后的根頁，只不過它現(xiàn)在只有兩個新增加的索引行，分別指向剛剛分配過來的新頁（前面的first與root值相同）分裂頁10/15/2022102分裂索引的根頁分裂頁10/11/2022102分裂索引的根頁根頁的分裂造成索引級別的增加，由于索引的級別通常不會很多，所以這種類型的分裂不會經(jīng)常發(fā)生分裂頁10/15/2022103分裂索引的根頁分裂頁10/11/2022103分裂索引的中間頁通過以下步驟就可以簡單地實現(xiàn)索引中間頁的分裂：首先確定

48、被分裂頁上的索引碼的中間點接著分配一個新頁然后將舊索引頁上中間點下游的索引行拷貝到新頁中這種頁分裂比根頁的分裂普遍一些，但也不是經(jīng)常發(fā)生分裂頁10/15/2022104分裂索引的中間頁分裂頁10/11/2022104分裂數(shù)據(jù)頁數(shù)據(jù)頁的分裂是最有趣的、而且也是最普遍的頁分裂只有在插入操作中，而且只有當(dāng)被插入數(shù)據(jù)的表上存在簇集索引時，數(shù)據(jù)頁的分裂才可能發(fā)生如果表上沒有簇集索引，新數(shù)據(jù)行可以插入到任何一頁，只要SQL Server根據(jù)PFS頁判斷該頁有空間就可以分裂頁10/15/2022105分裂數(shù)據(jù)頁分裂頁10/11/2022105分裂數(shù)據(jù)頁雖然數(shù)據(jù)頁的分裂只能由插入操作引起，但該插入操作可以是U

49、PDATE語句的結(jié)果，而不僅僅是INSERT語句的結(jié)果如果數(shù)據(jù)行不能在它所在的位置被更新，或者至少是不能在同一頁被更新，則更新操作就要變成一個刪除操作和一個插入操作，而后面這個插入操作就可能引起數(shù)據(jù)頁的分裂分裂頁10/15/2022106分裂數(shù)據(jù)頁分裂頁10/11/2022106分裂數(shù)據(jù)頁和索引中間頁的分裂一樣，數(shù)據(jù)頁的分裂也要經(jīng)過三個步驟：首先確定要被分裂的數(shù)據(jù)頁上的索引碼的中間點接著分配一個新數(shù)據(jù)頁然后將舊數(shù)據(jù)頁上中間點下游的數(shù)據(jù)行拷貝到新頁中典型的頁分裂的代價不是很昂貴分裂頁10/15/2022107分裂數(shù)據(jù)頁分裂頁10/11/2022107分裂數(shù)據(jù)頁數(shù)據(jù)頁的分裂不僅需要索引管理器確定新

50、數(shù)據(jù)行應(yīng)該位于的頁，而且還要處理那些不適合在舊頁和新頁存儲的大數(shù)據(jù)行當(dāng)數(shù)據(jù)頁被分裂時，如果簇集索引碼值沒有改變，那么非簇集索引不會受到任何影響分裂頁10/15/2022108分裂數(shù)據(jù)頁分裂頁10/11/2022108一個具有簇集索引的表的數(shù)據(jù)插入過程頁分裂舉例10/15/2022109一個具有簇集索引的表的數(shù)據(jù)插入過程頁分裂舉例10/11/20概述當(dāng)從表中刪除行時，必須考慮數(shù)據(jù)頁和索引頁都發(fā)生了什么變化？簇集索引的葉級頁就是真正的數(shù)據(jù)頁，從擁有簇集索引的表中刪除一行數(shù)據(jù)和從非簇集索引的葉級頁刪除一行索引的行為是一樣的從堆中刪除數(shù)據(jù)行的行為與上述情況完全不一樣，它類似于從索引的中間結(jié)點頁里刪除索

51、引行刪除行10/15/2022110概述刪除行10/11/2022110從堆中刪除數(shù)據(jù)行當(dāng)一行數(shù)據(jù)要從堆中被刪除時，SQL Server 2000不會自動壓縮空間如果要刪除的數(shù)據(jù)行位于某頁的slot x處，則SQL Server僅將slot x的行偏移數(shù)組的偏移量改為0(TagA)，而頁里其他slot的數(shù)據(jù)不發(fā)生任何變化，也不移動作為性能優(yōu)化，直到一頁需要額外的連續(xù)空間插入新行時，壓縮才會發(fā)生刪除行10/15/2022111從堆中刪除數(shù)據(jù)行刪除行10/11/2022111從B-樹中刪除葉級行在索引的葉級，當(dāng)行被刪除時，它們就成為幽靈記錄。也就是說被刪除的行還繼續(xù)保留在頁上，但是行頭有一位被修改

52、，以表示該行確實被刪除而成為幽靈了被刪除行所在頁的頁頭也有反映幽靈記錄個數(shù)的信息，幽靈記錄主要用于碼-范圍(key-range)加鎖的并發(fā)優(yōu)化刪除行10/15/2022112從B-樹中刪除葉級行刪除行10/11/2022112從B-樹中刪除葉級行幽靈記錄只能出現(xiàn)在索引的葉級結(jié)點(簇集與非簇集索引)。如果不用幽靈記錄，包圍被刪除碼的整個范圍將不得不加鎖假如在整數(shù)列上有一個唯一索引，并且索引包含值1、30和100。如果刪除碼值為30的行，SQL Server就需要對1到100整個范圍進行加鎖如果使用幽靈記錄，30作為碼-范圍鎖的一個端點仍是可見的，以便在刪除事務(wù)執(zhí)行期間，SQL Server仍允許

53、任何碼值不是30的插入操作繼續(xù)進行刪除行10/15/2022113從B-樹中刪除葉級行刪除行10/11/2022113從B-樹中刪除葉級行SQL Server提供了一個特殊的內(nèi)務(wù)處理線程來周期地檢查B-樹的幽靈記錄，并且異步地將它們從索引的葉級移走。如果你設(shè)置了相應(yīng)的選項，這個線程還執(zhí)行數(shù)據(jù)庫的自動壓縮任務(wù)刪除行10/15/2022114從B-樹中刪除葉級行刪除行10/11/2022114從索引的中間結(jié)點刪除行當(dāng)從表中刪除數(shù)據(jù)行時，所有的非簇集索引都需要維護，因為每一個非簇集索引在葉級都要有一個指針指向剛被刪除的數(shù)據(jù)行當(dāng)索引的中間結(jié)點頁里的行被刪除時，它不能是幽靈的，但是，可以像處理堆頁那樣，

54、先不著急壓縮空間，當(dāng)新的索引行需要空間時再壓縮該頁刪除行10/15/2022115從索引的中間結(jié)點刪除行刪除行10/11/2022115回收頁當(dāng)數(shù)據(jù)頁的最后一行被刪除時，整個頁就被解除分配。如果該頁是整個表僅有的一頁，那么它不會被解除分配。因為每個表至少要包含一頁，即使它是空的也沒有關(guān)系。這與一個表剛被創(chuàng)建時不會立即為其分配任何存儲空間不一樣！刪除行10/15/2022116回收頁刪除行10/11/2022116回收頁從數(shù)據(jù)頁里刪除行也會導(dǎo)致指向該數(shù)據(jù)頁的索引頁里索引行的刪除。如果索引行被刪除，在索引頁里只留下最后一條索引記錄，那么該索引頁也會被解除分配。為什么呢？因為原先留在索引頁里的最后一

55、條索引記錄被移動到與該索引頁相鄰的頁里，這樣就可以把空的索引頁解除分配。刪除行10/15/2022117回收頁刪除行10/11/2022117更新策略延遲更新使用事務(wù)日志作為中間的緩沖區(qū)，將更新操作分兩遍完成，它是更新策略中最慢的一種策略SQL Server 2000不支持延遲更新，所有的更新操作都是直接完成的，即直接更新，它不需要使用事務(wù)日志作為中間的緩沖區(qū)更新行10/15/2022118更新策略更新行10/11/2022118更新操作的分類在執(zhí)行更新操作之前，SQL Server要評估更新操作要影響多少行，如何訪問這些行以及是否有索引碼的改變要發(fā)生等等。更新操作可分為：在位更新：更新操作在

56、通常的位置進行異位更新：更新操作不在通常的位置進行，而變成先刪除后插入兩個操作，這就要引起數(shù)據(jù)行的移動更新行10/15/2022119更新操作的分類更新行10/11/2022119移動數(shù)據(jù)行在SQL Server 2000中，什么情況下會發(fā)生數(shù)據(jù)行的移動呢？一是數(shù)據(jù)行的變長列被更新變大，它不再適合放在原來的位置上二是數(shù)據(jù)行的簇集索引列發(fā)生了改變，該數(shù)據(jù)行需要移動到別的數(shù)據(jù)頁里，因為數(shù)據(jù)行是按照簇集索引碼的順序物理存儲的更新行10/15/2022120移動數(shù)據(jù)行更新行10/11/2022120數(shù)據(jù)行移動與非簇集索引非簇集索引的葉級包含表中每一數(shù)據(jù)行的標簽如果表有一個簇集索引，標簽就是行的簇集索引碼：當(dāng)且僅當(dāng)簇集索引碼發(fā)生了改變，每個非簇集索引的更新才是必要的如果一個數(shù)據(jù)行不再適合存儲在原來的頁上而要移到別處，但是該行的簇集索引碼沒有改變，所有的