SQL索引及表的頁的邏輯順序與物理順序

標籤：位置   serve   理論   最新   指定   jna   sys   需要   重建   

2、但修改表時，無論是叢集索引還是堆的資料頁都是按自然順序向後插入資料，頁面上的位移量可以證明。因為資料庫的最小讀取單元是頁，所以頁內的物理順序無關緊要，只需要維護好頁內資料的邏輯順序。

      聚集表中插入資料時會根據索引找到相應資料頁進行自然順序插入（內部填滿因數，使得資料頁保留一定的空閑空間），

　  如果資料頁滿，將分頁（資料按一定比例挪到新資料頁，插入行在挪動完畢後自然順序插入。新頁的物理順序與邏輯順序可能不一致）。

3、然後叢集索引的資料頁和索引頁的邏輯順序會調整，可以通過dbcc page 的row offset array（slot array）證明。

4、基於以上理論，片段的產生就合理了。因為是邏輯上的調整，所以當在表中插入資料時，可能或產生物理順序與邏輯順序不一致的頁面。

5、基於第一點，當表的片段大時，可以選擇重建索引。

6、索引有重建和重組之分。片段有外部片段（資料在插入，更新等操作時，索引的邏輯順序與物理順序不一致）和內部片段（由於頁面拆分時產生，由填滿因數控制）之分。

 ----------------------------------------------------

實驗涉及到的命令：

DBCC IND ( { ‘dbname‘ | dbid }, { ‘objname‘ | objid },      { nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )擷取頁號，檔案號，頁數（每一條資料代表一頁）

--   1：顯示所有分頁的資訊，包括IAM分頁,資料分頁,所有存在的LOB分頁和行溢出頁,索引分頁
--  -1： 顯示所有IAM、資料分頁、及指定對象上全部索引的索引分頁.
--  -2： 顯示指定對象的所有IAM分頁
---  nonclustered indid：顯示所有的IAM、資料分頁以及一個索引的索引分頁資訊 

----------------------------------------------------------

屬性說明：

 46 --{‘dbname‘|dbid}表示資料庫名或者資料庫ID 47 -- 48 --{‘objectname‘|objectID}表示對象名或者對象ID 49 -- 50 --{nonclustered indid|1|0|-1|-2}表示顯示行內資料分頁及指定對象的行內IAM分頁資訊 51 -- 52 --  1：顯示所有分頁的資訊，包括IAM分頁,資料分頁,所有存在的LOB分頁和行溢出頁,索引分頁 53 -- 54 -- -1： 顯示所有IAM、資料分頁、及指定對象上全部索引的索引分頁. 55 -- 56 -- -2： 顯示指定對象的所有IAM分頁 57 -- 58 -- nonclustered indid：顯示所有的IAM、資料分頁以及一個索引的索引分頁資訊。 59 -- 60 -- {partition_number}->可選,為了與中的DBCC IND命令向前相容.它指定了一個特定分區號,如果不指定,顯示所有分區的資訊。   --以下是DBCC IND命令輸出結果的欄位描述：欄位名稱                   欄位描述PageFID                    分頁檔的IDPagePID                     頁面編號IAMFID              管理該頁面的IAM頁面所在的檔案IDIAMPID               管理該頁面的IAM頁面編號ObjectID                    表對象IDIndexID                索引ID，0 代表堆, 1 代表叢集索引, 2-250 代表非叢集索引 大於250就是text或image欄位 書本P18PartitionNumber        表或索引所在的分區號碼PartitionID                包含該分頁的分區IDiam_chain_type          該頁所屬配置單位類型；行內資料、資料列溢位資料或Lob資料PageType           分頁類型：1：資料頁面；2：索引頁面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM頁面IndexLevel          索引層級，0 代表分葉層級分頁 ；>0 代表非分葉層級層次； NULL 代表IAM分頁NextPageFID            本層下一個分頁所在的檔案IDNextPageFID               本層下一個分頁ID PrevPageFID          本層上一個分頁所在的檔案ID PrevPageFID                本層上一個分頁ID

--必須啟用此表示才能查看page的詳細情況
dbcc traceon(3604)
go

-------------------------------------------------

DBCC PAGE ([‘database name‘|database id], file number, page number, print option = [0|1|2|3] )擷取頁內行資料的位移量

第一個參數是資料庫名或資料庫ID
第二個參數指定檔案號
第二個參數指定頁號
Print opt參數可選; 可以使用以下值:
0 預設值; 輸出buffer header 和 page header資訊
1 輸出 buffer header, page header, 分別輸出每行資訊, 行位移表
2 輸出 buffer header, page header, 整頁資料,  行位移表
3 輸出 buffer header, page header, 別輸出每行資訊, 行位移表; 分別列出每列的值

----------------------------------------------------------------------

page屬性說明：

PAGE HEADER部分，即該頁面的前96個位元組。141 142 m_pageId = (1:106)              當前頁面號碼143 144 m_headerVersion = 1            版本號碼，始終為1145 146 m_type = 10                當前頁面類型，m_type=1表示資料頁面  10:IAM頁147 148 m_typeFlagBits = 0x0         資料頁和索引頁為4，其他頁為0149 150 m_level = 0              該頁在索引頁（B樹）中的級數，0表示為葉子節點151 152 m_flagBits = 0x0              頁面標誌153 154 m_objId (AllocUnitId.idObj) = 277576027          對象id 表id155 156 m_indexId (AllocUnitId.idInd) = 1      索引ID，0 代表堆, 1 代表叢集索引, 2-250 代表非叢集索引 大於250就是text或image欄位 書本P18157 158 Metadata: AllocUnitId = 299666199216128      儲單元的ID,sys.allocation_units.allocation_unit_id159 160 Metadata: PartitionId = 299666199216128     資料頁所在的分區號，sys.partitions.partition_id161 162 Metadata: IndexId = 1              跟m_indexId一樣 對象的索引號，sys.objects.object_id&sys.indexes.index_id163 164 Metadata: ObjectId = 277576027      跟m_objId 一樣     該頁面所屬的對象的id，sys.objects.object_id165 166 m_prevPage = (0:0)                         該資料頁的前一頁面167 168 m_nextPage = (0:0)                         該資料頁的後一頁面169 170 pminlen = 90          定長資料所佔的位元組數為90個位元組171 172 m_slotCnt = 2    頁面中的資料的行數，每頁2條記錄173 174 m_freeCnt = 6         頁面中剩餘的空間，還剩6位元組的空間176 m_freeData = 8182     頁面空閑空間的位置在8182這個位置 一個頁面8KB約等於8192位元組 頁面空閑空間的位置在8182 177                       說明這個頁面已經放不下資料了179 m_reservedCnt = 0           活動事務釋放的位元組數 181 m_lsn = (6:524:11)          日誌記錄號 184 m_xactReserved = 0       最新加入到m_reservedCnt領域的位元組數187 m_xdesId = (0:0)        添加到m_reservedCnt的最近的事務id190 m_ghostRecCnt = 0            幻影資料的行數193 m_tornBits = 1        頁的校正位或者被由資料庫頁面保護形式決定頁面保護位取代

 

... 行位移數組
8176-8177 slot7
672-8175 空餘空間  
7 (0x7) - 607 (0x25f) 607-671  
6 (0x6) - 542 (0x21e) 542-606  
5 (0x5) - 467 (0x1d3) 467-541  
4 (0x4) - 388 (0x184) 388-466  
3 (0x3) - 309 (0x135) 309-387  
2 (0x2) - 236 (0xec) 236-308
1 (0x1) - 165 (0xa5) 165-235  
0 (0x0) - 96 (0x60) 96-164
0-95 pageheader
DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。

SQL索引及表的頁的邏輯順序與物理順序