表和索引體系結(jié)構(gòu)

學(xué)習(xí)散記一表和索引體系結(jié)構(gòu)PS:MSDN上摘入表組織表一＞一個或多個分區(qū)--＞一個堆或一個聚集索引結(jié)構(gòu)包含數(shù)據(jù)行一＞堆頁或聚集索引頁在一個或多個分配單元中進行管理（具體的分配單元數(shù)取決于數(shù)據(jù)行中的列類型）表中的分區(qū)：效果：使用分區(qū)可以快速而有效地管理和訪問數(shù)據(jù)子集，從而使大型表或索引更易于管理概念：已分區(qū)表和已分區(qū)索引的數(shù)據(jù)劃分為分布于一個數(shù)據(jù)庫中多個文件組的單元。數(shù)據(jù)是按水平方式分區(qū)的，因此多組行映射到單個的分區(qū)。對數(shù)據(jù)進行查詢或更新時，表或索引將被視為單個邏輯實體。單個索引或表的所有分區(qū)都必須位于同一個數(shù)據(jù)庫中。決定分區(qū)的因素：要取決于表當(dāng)前的大小或?qū)淼拇笮?、如何使用表以及對表?zhí)行用戶查詢和維護操作的完善程度通常，如果某個大型表同時滿足下列兩個條件，則可能適于進行分區(qū)：1該表包含（或?qū)┮远喾N不同方式使用的大量數(shù)據(jù)。2不能按預(yù)期對表執(zhí)行查詢或更新，或維護開銷超過了預(yù)定義的維護期。 例 子例如，如果對當(dāng)前月份的數(shù)據(jù)主要執(zhí)行INSERT、UPDATE、DELETE和MERGE操作，而對以前月份的數(shù)據(jù)主要執(zhí)行SELECT查詢，則按月份對表進行分區(qū)可能會使表的管理工作更容易一些。如果對表的常規(guī)維護操作只針對一個數(shù)據(jù)子集，那么此優(yōu)點尤為明顯。如果該表沒有分區(qū)，那么就需要對整個數(shù)據(jù)集執(zhí)行這些操作，這樣就會消耗大量資源。表的分類：SQLServer表使用下列兩種方法之一來組織其分區(qū)中的數(shù)據(jù)頁：聚集表是有聚集索引的表：據(jù)行基于聚集索引鍵按順序存儲。聚集索引按B樹索引結(jié)構(gòu)實現(xiàn)，B樹索引結(jié)構(gòu)支持基于聚集索引鍵值對行進行快速檢索堆是沒有聚集索引的表：數(shù)據(jù)行不按任何特殊的順序存儲，數(shù)據(jù)頁也沒有任何特殊的順序。數(shù)據(jù)頁不在鏈接列表內(nèi)鏈接.表的分類詳解：堆結(jié)構(gòu)：堆和分區(qū)：對于堆使用的每個分區(qū)，都有index_id=0。默認(rèn)情況下，一個堆有一個分區(qū)。當(dāng)堆有多個分區(qū)時，每個分區(qū)有一個堆結(jié)構(gòu)，其中包含該特定分區(qū)的數(shù)據(jù)。堆和分配單元：個堆中的每個分區(qū)至少有一個IN_ROW_DATA分配單元。如果堆包含大型對象（LOB）列，則該堆的每個分區(qū)還將有一個LOB_DATA分配單元。如果堆包含超過8,060字節(jié)行大小限制的可變長度列，則該堆的每個分區(qū)還將有一個ROW_OVERFLOW_DATA分配單元。堆和IAM頁:可以通過掃描IAM頁對堆進行表掃描或串行讀操作來找到容納該堆的頁的擴展盤區(qū)。因為IAM按擴展盤區(qū)在數(shù)據(jù)文件內(nèi)存在的順序表示它們，所以這意味著串行堆掃描連續(xù)沿每個文件進行。使用IAM頁設(shè)置掃描順序還意味著堆中的行一般不按照插入的順序返回。聚集索引結(jié)構(gòu)：概念：索引是按B樹結(jié)構(gòu)進行組織的。索引B樹中的每一頁稱為一個索引節(jié)點。B樹的頂端節(jié)點稱為根節(jié)點。索引中的底層節(jié)點稱為葉節(jié)點。根節(jié)點與葉節(jié)點之間的任何索引級別統(tǒng)稱為中間級。在聚集索引中，葉節(jié)點包含基礎(chǔ)表的數(shù)據(jù)頁。根節(jié)點和中間級節(jié)點包含存有索引行的索引頁。每個索引行包含一個鍵值和一個指針，該指針指向B樹上的某一中間級頁或葉級索引中的某個數(shù)據(jù)行。聚集索引和分區(qū)：索引使用的每個分區(qū)的index_id=1。默認(rèn)情況下，聚集索引有單個分區(qū)。當(dāng)聚集索引有多個分區(qū)時，每個分區(qū)都有一個包含該特定分區(qū)相關(guān)數(shù)據(jù)的B樹結(jié)構(gòu)。聚集索引和分配單元：個聚集索引的每個分區(qū)中至少有一個IN_ROW_DATA分配單元。如果聚集索引包含大型對象（LOB）列，則它的每個分區(qū)中還會有一個LOB_DATA分配單元。如果聚集索引包含的變量長度列超過8,060字節(jié)的行大小限制，則它的每個分區(qū)中還會有一個ROW_OVERFLOW_DATA分配單元。聚集索引查找數(shù)據(jù)：根據(jù)鍵值找到鍵值所在的索引頁-》在索引頁里面找到索引行-》然后根據(jù)指針找到數(shù)據(jù)頁中的數(shù)據(jù)行；非聚集索引結(jié)構(gòu)：于聚集索引的差別：1） ：基礎(chǔ)表的數(shù)據(jù)行不按非聚集鍵的順序排序和存儲2） ：非聚集索引的葉層是由索引頁而不是由數(shù)據(jù)頁組成。索引行的組成：包含非聚集鍵值和行定位符。此定位符指向聚集索引或堆中包含該鍵值的數(shù)據(jù)行。關(guān)于定位符：如果表是堆（意味著該表沒有聚集索引），則行定位器是指向行的指針。該指針由文件標(biāo)識符（ID）、頁碼和頁上的行數(shù)生成。整個指針稱為行ID（RID）。果表有聚集索引或索引視圖上有聚集索引，則行定位器是行的聚集索引鍵。非聚集索引和分區(qū)：非聚集索引在index_id>0的sys.partitions中都有對應(yīng)的一行。默認(rèn)情況下，一個非聚集索引有單個分區(qū)。如果一個非聚集索引有多個分區(qū)，則每個分區(qū)都有一個包含該特定分區(qū)的索引行的B樹結(jié)構(gòu)。非聚集索引和分配單元：每個非聚集索引至少有一個針對每個分區(qū)的IN_ROW_DATA分配單元（存儲索引B樹頁）。如果非聚集索引包含大型對象（LOB）歹U，則還有一個針對每個分區(qū)的LOB_DATA分配單元。此外，如果非聚集索引包含的可變長度列超過8,060字節(jié)行大小限制，則還有一個針對每個分區(qū)的ROW_OVERFLOW_DATA分配單元。非聚集索引查找數(shù)據(jù)：根據(jù)非聚集鍵值在索引頁中找到行業(yè)索引行-》根據(jù)索引行中的行定位器找到數(shù)據(jù)頁中的數(shù)據(jù)行（這里分堆和聚集表）具有包含列的索引：將非鍵列添加到非聚集索引的葉級，擴展非聚集索引的功能。這樣的非鍵列具有下列優(yōu)點：它們可以是不允許作為索引鍵列的數(shù)據(jù)類型。在計算索引鍵列數(shù)或索引鍵大小時，數(shù)據(jù)庫引擎不考慮它們。當(dāng)查詢中的所有列都作為鍵列或非鍵列包含在索引中時，帶有包含性非鍵列的索引可以顯著提高查詢性能。這樣可以實現(xiàn)性能提升，因為查詢優(yōu)化器可以在索引中找到所有列值；不訪問表或聚集索引數(shù)據(jù)，從而減少磁盤I/O操作。例子：表中的以下列：Titlenvarchar(50)Revisionnchar(5)FileNamenvarchar(400)建立索引這三列的索引將超出900字節(jié)的大小限制10個字節(jié)(455*2)，這個時候用包含列的索引就可以做到了：CREATEINDEXIX_Document_TitleONProduction.Document(Title,Revision)INCLUDE(FileName);帶有包含列的索引準(zhǔn)則：在CREATEINDEX語句的INCLUDE子句中定義非鍵列。只能對表或索引視圖的非聚集索引定義非鍵列。除text、ntext和image之外，允許所有數(shù)據(jù)類型。精確或不精確的確定性計算列都可以是包含列不能同時在INCLUDE列表和鍵列列表中指定列名6.INCLUDE列表中的列名不能重復(fù)修改帶有包含列的表的限制：除非先刪除索引，否則無法從表中刪除非鍵列除進行下列更改外，不能對非鍵列進行其他更改：將列的為空性從NOTNULL改為NULL。增加varchar、nvarchar或varbinary列的長度。包含列用例：SELECTAddressLine1,AddressLine2,City,StateProvinceID,PostalCodeFROMPerson.AddressWHEREPostalCodeBETWEENN'98000'andN,99999';—對應(yīng)的包含列中的非鍵列應(yīng)該是select后面的列：AddressLine1,AddressLine2