SQL Server 索引

標籤：

SQL Server 中資料存放區的基本單位是頁(Page)。資料庫中的資料檔案(.mdf 或 .ndf)分配的磁碟空間可以從邏輯上劃分成頁(從 0 到 n 連續編號)。磁碟 I/O 操作在頁級執行。也就是說，SQL Server 每次讀取或寫入資料的最少資料單位是資料頁。

索引的用途

　　我們對資料查詢及處理速度已成為衡量應用系統成敗的標準，而採用索引來加快資料處理速度通常是最普遍採用的最佳化方法。

索引是什麼

　　資料庫中的索引類似於一本書的目錄，在一本書中使用目錄可以快速找到你想要的資訊，而不需要讀完全書。在資料庫中，資料庫程式使用索引可以定位到表中的資料，而不必掃描整個表。書中的目錄是一個字詞以及各字詞所在的頁碼列表，資料庫中的索引是表中的值以及各值儲存位置的列表。

索引的利弊

　　查詢執行的大部分開銷是I/O，使用索引提高效能的一個主要目標是避免全表掃描，因為全表掃描需要從磁碟上讀取表的每一個資料頁，如果有索引指向資料值，則查詢只需要讀少數次的磁碟就行了。所以合理的使用索引能加速資料的查詢。但是索引並不總是提高系統的效能，帶索引的表需要在資料庫中佔用更多的儲存空間，同樣用來增刪資料的命令已耗用時間以及維護索引所需的處理時間會更長。所以我們要合理使用索引，及時更新去除次優索引。

索引的分類

　　  SQL SERVER中有多種索引類型。

　　按儲存結構區分：“叢集索引（又稱聚類索引，簇集索引）”，“非叢集索引（非聚類索引，非簇集索引）”

　　按資料唯一性區分：“唯一索引”，“非唯一索引”

　　按鍵列個數區分：“單列索引”，“多列索引”。

叢集索引

　　叢集索引是一種對磁碟上實際資料重新組織以按指定的一列或多列值排序。像我們用到的漢語字典，就是一個叢集索引，比如要查“張”，我們自然而然就翻到字典的後面百十頁。然後根據字母順序跟尋找出來。這裡用到微軟的平衡二叉樹演算法，即首先把書翻到大概二分之一的位置，如果要找的頁碼比該頁的頁碼小，就把書向前翻到四分之一處，否則，就把書向後翻到四分之三的地方，依此類推，把書頁續分成更小的部分，直至正確的頁碼。

　　由於叢集索引是給資料排序，不可能有多種排法，所以一個表只能建立一個叢集索引。科學統計建立這樣的索引需要至少相當與該表120%的附加空間，用來存放該表的副本和索引中間頁，但是他的效能幾乎總是比其它索引要快。

　　由於在叢集索引下，資料在物理上是按序排列在資料頁上的，重複值也排在一起，因而包含範圍檢查（bentween,<,><=,>=）或使用group by 或order by的查詢時，一旦找到第一個索引值的行，後面都將是連在一起，不必在進一步的搜尋，避免啦大範圍的掃描，可以大大提高查詢速度。

非叢集索引

　　SQL Server預設情況下建立的索引是非叢集索引，他不重新組織表中的資料，而是對每一行儲存索引列值並用一個指標指向資料所在的頁面。他像漢語字典中的根據‘偏旁部首’尋找要找的字，即便對資料不排序，然而他擁有的目錄更像是目錄，對查取資料的效率也是具有的提升空間，而不需要全表掃描。

　　一個表可以擁有多個非叢集索引，每個非叢集索引根據索引列的不同提供不同的排序次序。

填滿因數

　　索引的一個特性，定義該索引每頁上的可用空間量。FILLFACTOR（填滿因數）適應以後表資料的擴充並減小了頁面分割的可能性。填滿因數是從0到100的百分比數值，設為100時表示將資料頁填滿。只有當不會對資料變更時(例如 唯讀表中)才用此設定。值越小則資料頁上的空閑空間越大，這樣可以減少在索引增長過程中進行頁分裂的需要，但這一操作需要佔用更多的硬碟空間。填滿因數指定不當，會降低資料庫的讀取效能，其降低量與填滿因數設定值成反比。

建立索引文法

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )[with[PAD_INDEX][[,]FILLFACTOR=fillfactor][[,]IGNORE_DUP_KEY][[,]DROP_EXISTING][[,]STATISTICS_NORECOMPUTE][[,]SORT_IN_TEMPDB]][ ON filegroup ]

CREATE INDEX命令建立索引各參數說明如下：

UNIQUE：用於指定為表或視圖建立唯一索引，即不允許存在索引值相同的兩行。

CLUSTERED：用於指定建立的索引為叢集索引。

NONCLUSTERED：用於指定建立的索引為非叢集索引。

index_name：用於指定所建立的索引的名稱。

table：用於指定建立索引的表的名稱。

view：用於指定建立索引的視圖的名稱。

ASC|DESC：用於指定具體某個索引列的升序或降序排序方向。

Column：用於指定被索引的列。

PAD_INDEX：用於指定索引中間級中每個頁（節點）上保持開放的空間。

FILLFACTOR = fillfactor：用於指定在建立索引時，每個索引頁的資料占索引頁大小的百分比，fillfactor的值為1到100。

IGNORE_DUP_KEY：用於控制當往包含於一個唯一叢集索引中的列中插入重複資料時SQL Server所作的反應。

DROP_EXISTING：用於指定應刪除並重新建立已命名的先前存在的叢集索引或者非叢集索引。

STATISTICS_NORECOMPUTE：用於指定到期的索引統計不會自動重新計算。

SORT_IN_TEMPDB：用於指定建立索引時的中間排序結果將儲存在 tempdb 資料庫中。

ON filegroup：用於指定存放索引的檔案組。

例子

--表bigdata建立一個名為idx_mobiel的非叢集索引，索引欄位為mobielcreate index idx_mobielon bigdata(mobiel) --表bigdata建立一個名為idx_id的唯一叢集索引，索引欄位為id--要求成批插入資料時忽略重複值，不重新計算統計資訊，填滿因數為40create unique clustered index idx_idon bigdata(id) with pad_index,fillfactor=40,ignore_dup_key,statistics_norecompute

管理索引

Exec sp_helpindex BigData   --查看索引定義Exec sp_rename ‘BigData.idx_mobiel‘,‘idx_big_mobiel‘  --將索引名由‘idx_mobiel‘ 改為‘idx_big_mobiel‘drop index BigData.idx_big_mobiel  --刪除bigdata表中的idx_big_mobiel索引dbcc showcontig(bigdata,idx_mobiel) --檢查bigdata表中索引idx_mobiel的片段資訊dbcc indexdefrag(Test,bigdata,idx_mobiel)  --整理test資料庫中bigdata表的索引idx_mobiel上的片段update statistics bigdata  --更新bigdata表中的全部索引的統計資訊

索引的設計原理

　　對於一張表來說索引的有無和建立什麼樣的索引，要取決與where字句和Join運算式中。

　　一般來說建立索引的原則包括以下內容：

• 系統一般會給主鍵欄位自動建立叢集索引。
• 有大量重複值且經常有範圍查詢和排序、分組的列，或者經常頻繁訪問的列，考慮建立叢集索引。
• 在一個經常做插入操作的表中建立索引，應使用fillfactor(填滿因數)來減少頁分裂，同時提高並發度降低死結的發生。如果在表為唯讀表，填滿因數可設為100.
• 在選擇索引鍵時，儘可能採用小資料類型的列作為鍵以使每個索引頁能容納儘可能多的索引鍵和指標，通過這種方式，可使一個查詢必需遍曆的索引頁面降低到最小，此外，儘可能的使用整數做為索引值，因為整數的訪問速度最快。

SQL Server 索引