資料庫實驗心得

我在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 值，使每個鍵唯一。此列和列值供內部使用，使用者不能檢視或訪問。