您好,登錄后才能下訂單哦!
本文詳細(xì)分析了SQL Server中表和索引結(jié)構(gòu)存儲(chǔ)的原理以及對(duì)于如何加快搜索速度和提高效率等方面做了詳細(xì)的分析,以下是主要內(nèi)容。
下圖顯示了表的存儲(chǔ)組織,每張表有一個(gè)對(duì)應(yīng)的對(duì)象ID,并且包含一個(gè)或多個(gè)分區(qū),每個(gè)分區(qū)會(huì)有一個(gè)堆或者多個(gè)B樹,堆或者B樹的結(jié)構(gòu)是預(yù)留的。每個(gè)堆或者是B樹都有三個(gè)分配單元用來存放數(shù)據(jù),分別是數(shù)據(jù)、LOB、行溢出,使用最多的分配單元是數(shù)據(jù)。如果有LOB數(shù)據(jù)或者是長(zhǎng)度超過8000字節(jié)的記錄,則可能有另外的LOB分配單元和行溢出分配單元。
小總結(jié): 一個(gè)表可以有多個(gè)分區(qū),但是每個(gè)分區(qū)(堆/B樹)最多有三個(gè)分配單元,每個(gè)分配單元可以有很多頁,對(duì)于每個(gè)分配單元內(nèi)的數(shù)據(jù)頁,根據(jù)表是否有索引,以及索引是聚集還是非聚集,組織方式有以下三種:
1. 堆
所謂堆(heap),就是不含聚集索引的表。堆的 sys.partitions 中具有一行,對(duì)于堆使用的每個(gè)分區(qū),都有 index_id = 0。只有一個(gè)分區(qū),在系統(tǒng)表里,對(duì)于這個(gè)分區(qū)下面的每個(gè)分配單元都有一個(gè)連接指向Index Allocation Map頁(IAM),在IAM頁里,描述了區(qū)的信息。
sys.system_internals_allocation_units 系統(tǒng)視圖中的列 first_iam_page 指向管理特定分區(qū)中堆的分配空間的一系列 IAM 頁的第一頁。SQL Server 使用 IAM 頁在堆中移動(dòng)。堆內(nèi)的數(shù)據(jù)頁和行沒有任何特定的順序,也不鏈接在一起。數(shù)據(jù)頁之間唯一的邏輯連接是記錄在 IAM 頁內(nèi)的信息。
2. 具有非聚集索引的表
如果有一個(gè)表只有非聚集索引而沒有聚集索引,對(duì)應(yīng)的索引號(hào)是2--250。那么針對(duì)每個(gè)非聚集索引,都有一個(gè)對(duì)應(yīng)的分區(qū),在系統(tǒng)表進(jìn)而,對(duì)于這個(gè)分區(qū)下面的每個(gè)分配單元,都有一個(gè)連接指向根頁。數(shù)據(jù)頁之間通過前后指針互相聯(lián)系,是一個(gè)完整的樹形結(jié)構(gòu)。在樹的底層,會(huì)有一個(gè)連接指向真正的數(shù)據(jù),連接的形式是文件號(hào)+頁號(hào)+行號(hào),而真正的數(shù)據(jù)是以堆的形式存放的。如下圖所示:
3. 具有聚集索引的表
表中的聚集索引,對(duì)應(yīng)的索引號(hào)是1。它有一個(gè)對(duì)應(yīng)的分區(qū),該分區(qū)下的每個(gè)分配單元都有一個(gè)連接指向根頁。對(duì)于聚集索引來說,葉子結(jié)點(diǎn)里存放的是真正的數(shù)據(jù),而不是非聚集索引那樣的連接。如下圖所示:
非聚集索引與聚集索引具有相同的 B 樹結(jié)構(gòu),它們之間的顯著差別在于以下兩點(diǎn):
基礎(chǔ)表的數(shù)據(jù)行不按非聚集鍵的順序排序和存儲(chǔ)。
非聚集索引的葉層是由索引頁而不是由數(shù)據(jù)頁組成
案例分析: 我們來查看一個(gè)表的存儲(chǔ)結(jié)構(gòu),我們?cè)诖耸褂玫谋硎且粋€(gè)生產(chǎn)表,共有1億多條記錄,查看表的object_ID,如下圖所示:
此表,我已經(jīng)做了分區(qū),查看其分區(qū)信息,可以使用下圖所示的命令:
從上圖可以看到,此表共有16個(gè)分區(qū),對(duì)應(yīng)不同的索引,基本上每個(gè)分區(qū)都有1千多萬條記錄。從此圖中還可以看到堆或者B樹的ID跟分區(qū)ID是一樣的,如果希望進(jìn)一步查看某一個(gè)索引的具體信息,可以使用下面的命令,如查看72057594067419136的信息。
從這個(gè)圖當(dāng)中,我們可以看到這個(gè)分區(qū)只有一個(gè)分配單元,IN_ROW_DATA表明此分配單元只用來存放具體數(shù)據(jù),共5353頁,已使用5346頁,數(shù)據(jù)占用5320頁。
如果希望查看根頁的位置,可以使用下面的命令:
但需要注意,這里顯示的根頁的位置是0xEC0100001100,由于存儲(chǔ)的關(guān)系,用倒序的方式對(duì)它進(jìn)行解析,也就是0x0011000001EC,最前面的兩個(gè)字節(jié)表明是所在的文件組編號(hào),后面的4個(gè)字節(jié)是頁的編號(hào),即(1,0x01CE) ,換成十進(jìn)制(1,492),然后可以利用我們上一節(jié)所說的DBCC PAGE命令查看頁的信息,如下圖所示:
從中可以看到具體的數(shù)據(jù),此界面的返回結(jié)果會(huì)因表上的聚集索引、非聚集索引而不同。如果查看一個(gè)表使用的總頁數(shù)和區(qū)數(shù),也可以使用命令:DBCC SHOWCONFIG,如下圖所示:
在同樣表結(jié)構(gòu)的情況下,建立聚集索引不會(huì)增加表格的大小,但是建立非聚集索引反而會(huì)增加不少空間,在性能方面,SQL Server產(chǎn)品組做過測(cè)試,在select、update、delete操作下,聚集索引性能較高,在插入記錄時(shí),聚集索引和非聚集索引性能相同,沒有出現(xiàn)聚集索引影響插入速度的現(xiàn)象,但在生產(chǎn)環(huán)境中,還是要謹(jǐn)慎行事。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。