sql server的存儲機制

區段: 是用來為表和索引 分配空間的基本存儲單元. 由 8個連續的頁面構成,大小為64kb.

區段的注意事項:

• 一旦區段已滿,那么下一記錄 將要占據的空間不是記錄的大小,而是整個區段的大小.?
• 通過預先分配空間,sql server節省了為每個記錄分配新空間的時間

頁: 頁是在到達實際數據行 之前所能達到的最后一個存儲級別.盡管每個區段中的頁數是固定的,但是每一頁中的行數 不是固定的--這完全取決于行的大小,而行的大小 可以變化的. 可以把 頁 看作是 表行和索引行的容器.通常不允許行跨頁.就是行的大小 最大為8kb.

下面介紹兩種不同的頁類型:

• 數據頁: 就是表中的實際數據?
• 索引頁:它們既包括 非聚集索引的非葉級頁和頁級頁. 又包括聚集索引的非葉級頁

行:

最大可達8kb,除了 8060字符的限制外,還有最大1024標準列(非稀疏列)的限制.

稀疏列:

使用稀疏列,可以講單個表中允許的列的總數 提高到 30000.

在內部,標記為稀疏的列 的數據 嵌在單個列中--可以打破之前的1024列的限制,而不用做較大的體制結構的更改.

理解索引

索引就是能夠快速訪問數據的方法.

索引中的存儲順序取決于 為數據建立的排列規則信息.可以再數據庫或者列級設置排列規則.

平衡樹:

平衡樹或者 B-Tree 僅是提供一種以一致且相對低成本的方式查找特定信息的方法.

頁拆分簡介:

每次遇到樹中的分支時,因為每一邊都有約有一半的數據,所以B-樹 是平衡的.

通過將數據添加到樹上,節點最終將變滿,并且需要拆分,因為sql? server中,一個節點相當于一個頁--所以這杯稱為頁拆分.

也拆分做的操作:

• 創建新頁
• 將行從現有的頁移動到新頁
• 將新行添加到其中一頁上
• 在父節點中添加另一個記錄項

但是系統開銷不僅僅是這些.因為在進行樹的排列,就可能導致級聯操作.創建新頁時(因為拆分的緣故),需要在父節點中

建立另一個記錄項,在父節點中的這個記錄項在該 級別也可能導致頁拆分,而且整個過程會重新開始.

如果根節點拆分,那么實際最終會創建兩個額外的頁.由于只能有一個根節點,所以之前作為根節點的頁被拆分成兩個頁,而且成為樹的新

中間級別.然后創建全新的根節點.并且將有兩個記錄項.

顯然,拆分頁對系統性能產生非常負面的影響,其表現是 在服務器上 的處理會暫停幾秒.

sql server 中 訪問數據的方式:

廣義上講,sql server檢索數據的方式 只有兩種.

• 使用表掃描
• 使用索引掃描

使用表掃描:

表掃描是個很直觀的過程.sql server 從表的物理起點 開始,瀏覽表中的每一行,當發現和查詢條件匹配的行時,

就在結果集中包含它們.

使用索引:

在查詢優化過程中,優化器查看所有可用的索引結構,并選擇最好的一個(這主要基于在連接和where子句中指定的信息,以及sql server

在索引結構中保存的統計信息). 一旦選擇的索引,sql server 將在樹結構中導航至與條件匹配的數據位置,并且只提取它所需要的記錄.

區別在于,因為數據時排序的,所以查詢引擎知道 它何時到達正在查找的當前范圍的下界,然后他可以結束查詢,或者根據需要移至下一數據范圍.

索引類型和索引導航

• 聚集索引
• 非聚集索引( 有包括兩個: 堆上的非聚集索引,? 聚集表上的非聚集索引)

物理數據的 存儲方式 在 聚集索引和非聚集索引中時不同的. 而? sql server 遍歷平衡樹已到達末端數據的方式 在所有三種

索引類型中,也是不同的.

索引在 聚集表(如果表有聚集索引)或者堆(用于沒有聚集索引的表)上創建.

聚集表: 是 在其上具有聚集索引的任意表.

它們對于表而言意味著 以 指定? 物理順序 存儲數據.通過使用聚集鍵唯一的標示獨立的行---聚集鍵 即 定義聚集索引的列.

堆: 在其上沒有聚集索引的任意表. 在這種情況下,基于 行的區段,頁,以及行偏移量的組合創建唯一的標示符,或者成為RID,

如果沒有可用的聚集鍵,那么RID是唯一必要的內容.

聚集索引:

聚集索引對于 任意給定的表而言都是唯一的.

聚集索引的葉級 是 實際的數據.

數據重新排序,按照和索引排序條件聲明的相同物理順序存儲.這意味著,一旦到達索引的葉級,就到達了 數據.

任何新紀錄都根據其正確的物理順序插入到群集索引中.創建新頁的方式也隨插入記錄的位置而變化.

當發生頁面拆分時,數據自動的四處移動一 保持平衡. 數據的前半部分保留在舊頁上,而數據的剩余部分添加到新頁.-這樣就形成的了對半分,

使得樹 保持平衡.

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

堆上的非聚集索引

這個索引 的頁級不是數據，而是指向數據的一個指針。 該指針 以RID的形式出現，這種RID由 索引指向的特性行的區段，頁以及

偏移量組成。

注意: 堆上的非聚集索引 和聚集索引一樣,通常任何已通過讀取一次的頁 將 仍然 在內存中緩存,而且同樣將非常快速的唄檢索.

聚集表上的非聚集索引

這種索引和堆上的非聚集索引 一樣,索引的非葉級節點的工作與使用 聚集索引時相比幾乎一樣,區別在于 葉級.

在聚集表上的非聚集索引 ,在 葉級 找到的是 聚集鍵,也就說,找到足夠的信息 繼續并利用聚集索引.

何時何地使用何種索引

索引,特別是 非聚集索引,主要在 索引中 有相當高級別的選擇性的情況下是有益的.?

所謂的選擇性就是 列中唯一值的百分比.?? 唯一值百分比越高,選擇性越高.從而索引的用處就越大.

索引的成本:

雖然索引能在查詢時 提高效率,但是在修改數據時,實際上花費很高.每次對數據進行修改時,任何與數據有關的索引也將需要更新,

對于每個創建的索引,意味著 創建了 一組 必須更新的條目.

那么為什么是一組條目呢,因為 平衡樹有多個級別,每次對葉級進行修改時,就可能產生頁拆分,而且也必須修改一個或者多個 非葉級頁.

維護索引

就索引維護而言,需要處理一下兩個問題:

• 頁拆分
• 碎片

這兩個問題 都和? 液密度? 有關,雖然兩者在表現形式上有本質區別,但是故障排除工具是一樣的.

碎片:

當 數據庫增長,頁拆分,然后刪除數據時,都會產生碎片.

雖然從增長的角度看,平衡樹機制 在保持平衡方面做的不錯,但是在刪除數據時,將沒有太多作用.最終可能出現這樣一種情況:

其中在這一頁上有一個記錄,而在那一頁有幾個記錄--意味著頁沒有被填滿,

關于碎片 首先會想到的就是 ----浪費空間.前面 說過,sql server 每次分配一個區段的空間,如果 一個頁 只有一條記錄,仍然會分配一個區段.

散步在各處的記錄會造成 數據檢索時? 的額外開銷.

雖然如此,碎片也有好的一面,—OLTP系統就喜歡碎片,因為 也拆分.沒有許多數據的頁 在插入數據時,幾乎不用擔心也拆分.

所以:大量的碎片 意味著 較差的讀取性能,但是也意味著? 極好的插入性能.

確定碎片和也拆分的可能性:

sql server 提供了一個元數據 函數? sys.dm_db_index_physical_stats ,有助于確定數據庫中的頁和區段有多滿.

那么次函數的參數如下:

參數說明：數據庫id，對象id，索引類別id (1聚集索引? 0堆? 2 非聚集索引)

a表結果：

一個主鍵，自帶一個clustered index

下面看sql :

declare @db_id smallint declare @ob_id int set @db_id=DB_ID('test') set @ob_id=object_id('a') select * from sys.dm_db_index_physical_stats(@db_id,@ob_id,null,null,null) .csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) }

下面看一下 返回的部分數據：

表中明確給出, 索引類型, 平均碎片百分比, 和 索引深度. 下面取消 a表的id主鍵,再次查看:結果如下: .csharpcode, .csharpcode pre { font-size: small; color: rgba(0, 0, 0, 1); font-family: consolas, "Courier New", courier, monospace; background-color: rgba(255, 255, 255, 1) } .csharpcode pre { margin: 0 } .csharpcode .rem { color: rgba(0, 128, 0, 1) } .csharpcode .kwrd { color: rgba(0, 0, 255, 1) } .csharpcode .str { color: rgba(0, 96, 128, 1) } .csharpcode .op { color: rgba(0, 0, 192, 1) } .csharpcode .preproc { color: rgba(204, 102, 51, 1) } .csharpcode .asp { background-color: rgba(255, 255, 0, 1) } .csharpcode .html { color: rgba(128, 0, 0, 1) } .csharpcode .attr { color: rgba(255, 0, 0, 1) } .csharpcode .alt { background-color: rgba(244, 244, 244, 1); width: 100%; margin: 0 } .csharpcode .lnum { color: rgba(96, 96, 96, 1) }

注意 索引類型:? 由 clustered index 變成了 heap? ,說明了 沒有聚集索引的表 稱為 堆表.

查看到相關信息后,那么就像去改變索引,

使用 fillfactor 填充因子 來改變頁面的密度:

alter index PK_a(鍵名) on a(表名) rebuild with (fillfactor = 90) 填充因子越大,空閑空間就越大.

下面提供兩種 查看 表中索引的? 函數:

select * from sys.dm_db_index_operational_stats(db_id('test'),object_id('a'),null,null)

declare @db_id smallint declare @ob_id int set @db_id=DB_ID('test') set @ob_id=object_id('a') select * from sys.dm_db_index_physical_stats(@db_id,@ob_id,null,null,null)

總結

以上是生活随笔為你收集整理的sql server 2008学习8 sql server存储和索引结构的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。