數據庫實驗心得

我在sql server 索引基礎知識系列中,第一篇就講了記錄數據的基本格式。那裏主要講解的是，數據庫的最小讀存單元：數據頁。一個數據頁是8k大小。

對於數據庫來説，它不會每次有一個數據頁變化後，就存到硬盤。而是變化達到一定數量級後才會作這個操作。 這時候，數據庫並不是以數據頁來作為操作單元，而是以64k的數據（8個數據頁，一個區）作為操作單元。

區是管理空間的基本單位。一個區是八個物理上連續的頁（即 64 kb）。這意味着 sql server 數據庫中每 mb 有 16 個區。

為了使空間分配更有效，sql server 不會將所有區分配給包含少量數據的表。sql server 有兩種類型的區：

統一區，由單個對象所有。區中的所有 8 頁只能由所屬對象使用。

混合區，最多可由八個對象共享。區中八頁的每頁可由不同的對象所有。

通常從混合區向新表或索引分配頁。當表或索引增長到 8 頁時，將變成使用統一區進行後續分配。如果對現有表創建索引，並且該表包含的行足以在索引中生成 8 頁，則對該索引的所有分配都使用統一區進行。

為何會這樣呢？

其實很簡單：

讀或寫 8kb 的時間與讀或寫 64 kb的時間幾乎相同。

在 8 kb 到 64 kb 範圍之內，單個磁盤 i/o 傳輸操作所花的時間主要是磁盤取數臂和讀/寫磁頭運動的時間。

因此，從數學上來講，當需要傳輸 64 kb 以上的 sql 數據時，

儘可能地執行 64 kb 磁盤傳輸是有益的，即分成數個64k的操作。

因為 64 kb 傳輸基本上與 8 kb 傳輸一樣快，而每次傳輸的 sql server 數據是 8 kb 傳輸的 8 倍。

我們通過一個實例來看 有and 操作符時候的最常見的一種情況。我們有下面一個表，

create table [dbo].[member]( [member_no] [dbo].[numeric_id] identity(1,1) not null, [lastname] [dbo].[shortstring] not null, [firstname] [dbo].[shortstring] not null, [middleinitial] [dbo].[letter] null, [street] [dbo].[shortstring] not null, [city] [dbo].[shortstring] not null, [state_prov] [dbo].[statecode] not null, [country] [dbo].[countrycode] not null, [mail_code] [dbo].[mailcode] not null, [phone_no] [dbo].[phonenumber] null, [photograph] [image] null, [issue_dt] [datetime] not null default (getdate()), [expr_dt] [datetime] not null default (dateadd(year,1,getdate())), [region_no] [dbo].[numeric_id] not null, [corp_no] [dbo].[numeric_id] null, [prev_balance] [money] null default (0), [curr_balance] [money] null default (0), [member_code] [dbo].[status_code] not null default (' '))

這個表具備下面的四個索引：

索引名 細節 索引的列

member_corporation_link nonclustered located on primary corp_no

member_ident clustered, unique, primary key located on primary member_no

member_region_link nonclustered located on primary region_no

memberfirstname nonclustered located on primary firstname

當我們執行下面的sql查詢時候，

select er_no, tname, on_nofrom er as mwhere tname like 'k%' and on_no > 6 and er_no < 5000go

sql server 會根據索引方式，優化成下面方式來執行。

select er_no,tname,on_nofrom(select er_no, tname from er as m where tname like 'k%' and er_no < 5000) a , -- 這個查詢可以直接使用 memberfirstname 非聚集索引，而且這個非聚集索引覆蓋了所有查詢列-- 實際執行時，只需要 邏輯讀取 3 次

(select er_no, on_no from er as mwhere on_no > 6) b

-- 這個查詢可以直接使用 member_region_link 非聚集索引，而且這個非聚集索引覆蓋了所有查詢列-- 實際執行時，只需要 邏輯讀取 10 次

where er_no = er_no

不信，你可以看這兩個sql 的執行計劃，以及邏輯讀信息，都是一樣的。

其實上面的sql，如果優化成下面的方式，實際的邏輯讀消耗也是一樣的。為何sql server 不會優化成下面的方式。是因為 and 操作符優化的另外一個原則。

1/26 的數據和 1/6 的數據找交集的速度要比 1/52 的數據和 1/3 的數據找交集速度要慢。

select er_no,tname,on_nofrom(select er_no, tname from er as mwhere tname like 'k%' -- 1/26 數據) a,

(select er_no, on_no from er as mwhere on_no > 6 and er_no < 5000-- 1/3 * 1/ 2 數據) bwhere er_no = er_no

當然，我們要學習sql 如何優化的話，就會用到查詢語句中的一個功能，指定查詢使用哪個索引來進行。

比如下面的查詢語句

select er_no, tname, on_nofrom er as m with (index (0))where tname like 'k%' and on_no > 6 and er_no < 5000go

select er_no, tname, on_nofrom er as m with (index (1))where tname like 'k%' and on_no > 6 and er_no < 5000goselect er_no, tname, on_nofrom er as m with (index (membercovering3))where tname like 'k%' and on_no > 6 and er_no < 5000goselect er_no, tname, on_nofrom er as m with (index (memberfirstname, member_region_link))where tname like 'k%' and on_no > 6 and er_no < 5000go

這裏 index 計算符可以是 0 ，1， 指定的一個或者多個索引名字。對於 0 ，1 的意義如下：

如果存在聚集索引，則 index(0) 強制執行聚集索引掃描，index(1) 強制執行聚集索引掃描或查找（使用性能最高的一種）。

如果不存在聚集索引，則 index(0) 強制執行表掃描，index(1) 被解釋為錯誤。

總結知識點：

簡單來説，我們可以這麼理解：sql server 對於每一條查詢語句。會根據實際索引情況（sysindexes 系統表中存儲這些信息），分析每種組合可能的成本。然後選擇它認為成本最小的一種。作為它實際執行的計劃。

成本代價計算的一個主要組成部分是邏輯i/o的數量，特別是對於單表的查詢。

and 操作要滿足所有條件，這樣，經常會要求對幾個數據集作交集。數據集越小，數據集的交集計算越節省成本。

的項目中，竟然出現了濫用聚集索引的問題。看來沒有培訓最最基礎的索引的意義，代價，使用場景，是一個非常大的失誤。這篇博客就是從這個角度來羅列索引的基礎知識。

使用索引的意義

索引在數據庫中的作用類似於目錄在書籍中的作用，用來提高查找信息的速度。

使用索引查找數據，無需對整表進行掃描，可以快速找到所需數據。

使用索引的代價

索引需要佔用數據表以外的物理存儲空間。

創建索引和維護索引要花費一定的時間。

當對錶進行更新操作時，索引需要被重建，這樣降低了數據的維護速度。

創建索引的列

主鍵

外鍵或在表聯接操作中經常用到的列

在經常查詢的字段上最好建立索引

不創建索引的列

很少在查詢中被引用

包含較少的惟一值

定義為 text、ntext 或者 image 數據類型的列

heaps是staging data的很好選擇，當它沒有任何index時

excellent for high performance data loading (parallel bulk load and parallel index creation after load)

excellent as a partition to a partitioned view or a partitioned table

聚集索引提高性能的方法，在前面幾篇博客中分別提到過，下面只是一個簡單的大綱，細節請參看前面幾篇博客。

何時創建聚集索引？

clustered index會提高大多數table的性能，尤其是當它滿足以下條件時：

獨特, 狹窄, 靜止： 最重要的條件

持續增長的，最好是隻向上增加。例如：

identity

date, identity

guid (only when using newsequentialid() function)

聚集索引唯一性（獨特型的問題）

由於聚集索引的b+樹結構的葉子節點必須指向具體數據。如果你要建立聚集索引的列不唯一，並且你指定的創建的聚集索引是非唯一的聚集索引，則會有以下情況：

如果未使用 unique 屬性創建聚集索引，數據庫引擎 將向表自動添加一個四字節 uniqueifier 列。必要時，數據庫引擎 將向行自動添加一個 uniqueifier 值，使每個鍵唯一。此列和列值供內部使用，用户不能查看或訪問。