《Microsoft Sql server 2008 Internals》讀書筆記--第六章Indexes:Internals and Management(1)

標籤：style   blog   http   color   使用   os   io   strong   

《Microsoft Sql server 2008 Internals》索引目錄：

《Microsoft Sql server 2008 Internals》讀書筆記--目錄索引

 

在第五章主要學習了table的內部儲存結構，第七章《Special storage》中將繼續深入學習儲存機制，那將是本書最難理解的一章。第六章主要介紹index的基礎知識，第八章是《query Optimizer》,下面我們先來看看第六章：Indexes:Internals and Management。這一章分為三部分：

1、介紹Index的使用、概念和內部構造,你也將瞭解索引如何被儲存和它們是如何被檢索的。

2、深入瞭解資料被修改時內部儲存發生了什麼，是如何發生的，以及SQL Server如何確保一致性(consistence)。你還將瞭解到修改資料的索引（對效能的）的潛在影響，比如整理。

3、索引的管理和維護。

前言：索引的好處是不言而喻的。一個良好的索引可能將你的查詢請求從數百萬的I/O下降到few甚至更少。同樣，一個過度的索引設計(over-indexing)比起不用索引可能後果更為嚴重。因此，掌握必要的索引實體儲存體及儲存引擎、策略、最佳化知識對於一個SQL設計人員是至關重要的。

首先，我們來一起學習第一部分：

索引分為叢集索引 (clustered index)和非叢集索引(nonclustered index)兩種，在叢集索引的表中，表資料是按照聚集鍵排序被邏輯存放的。當你找到你要的資料時，搜尋同時完成。而非叢集索引的表中。索引結構是完全和資料自身分離的。當你開始尋找索引的時候，你必須按照某些引用指標(Reference Pointer)的排序得到實際的資料。

關於如何建立索引(index)，請查閱MSDN:
http://msdn.microsoft.com/zh-cn/library/ms188783.aspx

◆SQL Server Index B-Tree

在SQL Server中，索引被按照B-Tree結構組織，B-Tree即(balanced-tree),SQL Server使用一種特殊的B+tree結構。不像通常的樹，B-Tree總是倒的(inverted)，它的根root(單個page)在頂部,葉(Leaf)在底部。中間層級的level取決於多種因素。B-Tree是一個在不同場合被重載(overload)的詞,在本書中。它意味著整個的索引結構，如所示：

重要的是，我們需要理解SQL　Server中B-Tree是如何被構建的(constructed)，以及每一個Level中包含什麼。我們通過一些簡單的概念入手。

首先，索引有兩個很基本的組件：一個葉級(leaf level)，一個或多個非葉級(non-leaf levels)。後者主要用於葉級的導航。此外，第一個中間級(first intermediate level)也被用於整理分析和在大序列索引查詢的驅動預讀(read-ahead)。

非頁級(non-leaf Level)的存在主要是為了在葉級協助迅速導航到一行的架構，而不是直接到資料本身。每個非頁級儲存了自下而上在每一頁(page),直到Root級被建立。越高的級（即距離leaf越遠的）存放更少的資訊, 因為每個處於該級的行只包含位於下一級的最小鍵值，加一個指標。實際上，這些key（最多900位元組或16個列）在SQL　Server中有助於保持索引樹相對的小。

下面我們使用一個包含1,000,000（即1百萬）“行”的索引的葉級為例。首先我們得明確，無論是leaf leave還是non-leaf level都是被儲存在SQL Server pages(8KB pages)中。在這個例子中，non-leaf“‘ 行”將有4000位元組。也就是說，每頁只能儲存兩行。對於一個百萬“行”的表而言，我們的索引的葉級將有500,000頁。相對而言，這是一個非常寬的行結構，然而，我們並沒有浪費很大的空間。假如我們葉級頁有兩個3,000位元組的行，我們仍然每頁兩“行”，於是我們將浪費2,000位元組的空間。

注意，這裡為什麼用"行"而不用資料行(Data Rows)，這是因為：這個頁級可能是叢集索引（這自然就等於資料行），也可能這些葉級行是一些非叢集索引的包含性非鍵值列被加到索引的葉級中的行。當包含性列被使用時，葉級頁可以包含更寬的行（超過900位元組或16列限制）。在本例中，索引建立時頁級將是4GB大小，(500,000個8kb大小的page)。如果使用最大限制，那麼最後長到Root的樹將會更小，並且最多有8個級，如下：
■ Root page of non-leaf level(Level 7)=2 rows=1 page(8 rows per page)

■ Intermediate page of non-leaf level(Level 6)=16 rows=2 page(8 rows per page)

■ Intermediate  non-leaf level(Level 5)=123 rows=16 page(8 rows per page)

■ Intermediate  non-leaf level(Level 4)=977 rows=123 page(8 rows per page)

■ Intermediate  non-leaf level(Level 3)=7,813 rows=977 page(8 rows per page)

■ Intermediate non-leaf level(Level 2)=6,2500 rows=7,813 page(8 rows per page)

■ Intermediate  non-leaf level(Level 1)=50,000 rows=6,2500 page(8 rows per page)

■ Leaf  level(Level 0)=1,000,000 rows=500,000 page(8 rows per page)

更小的鍵大小將會有更快的層級，以同樣資料為例，如果有更小的索引鍵將在非葉級帶來更小的行大小，因此可以儲存更多的行。如果只有20位元組，將可以每而儲存404行資料：

■ Root page of non-leaf level(Level 3)=4 rows=1 page(404 rows per page)

■ Intermediate non-leaf level(Level 2)=1,238 rows=4 page(404 rows per page)

■ Intermediate  non-leaf level(Level 1)=50,000 rows=1,238 page(404 rows per page)

■ Leaf  level(Level 0)=1,000,000 rows=500,000 page(2 rows per page)

請記住：更窄而不是更寬的鍵(key)將給索引帶來更好的效率。最重要的是：索引的大小（即級的數量）取決於三點：1、索引定義。2、基表(table)是否有一個叢集索引。3、索引葉級的page數量。其中，葉級頁的數量直接表中行大小和行數量。這並不是說在索引中一定要使用窄索引。有時還要適當使用寬索引。此外，像"包含性列"和filtered indexes也會影響索引的大小和用途。當然，最重要的是，使用正確的索引。不是嗎？
分析索引的工具(Tools for Analyzing Indexes)

一、使用sys.dm_db_index_physical_stats

[python] view plaincopyprint?

1. select * from sys.dm_db_index_physical_stats(DB_ID(‘testdb‘),null,null,null,null);  

關於sys.dm_db_index_physical_stats的更多使用，請參看MSDN：
http://msdn.microsoft.com/zh-cn/library/ms188917.aspx

二、使用DBCC IND命令,這是一個MSDN未公開的命令。

[python] view plaincopyprint?

1. exec (‘DBCC IND(testdb,[dbo.Fixed],-1)‘)  

下一節將繼續學習物理索引結構(physical Index Structure)。

邀月註：本文著作權由邀月和CSDN共同所有，轉載請註明出處。

助人等於自助!   [email protected]

轉自：http://blog.csdn.net/downmoon/article/details/5280152