SQL Server基礎之索引

 索引用於快速找出在某個列中有某一特定值的行，不使用索引，資料庫必須從第一條記錄開始讀完整個表，直到找出相關的行。表越大，查詢資料所花費的時間越多，如果表中查詢的列有一個索引，資料庫能快速到達一個位置去搜尋資料，本篇主要介紹與索引相關的內容，包括索引的含義和特點，索引的分類，索引的設計原則以及如何建立和刪除索引。

一：索引的含義和特點

 索引是一個單獨的，儲存在磁碟上的資料結構，它們包含則對資料表裡所有記錄的引用指標，使用索引用於快速找出在某個或多個列中有某一特定值的行，對相關列使用索引是降低查詢操作時間的最佳途徑。索引包含由表或檢視中的一列或多列生成的鍵。

 例如：資料庫中有20000條記錄，現在要執行這樣一個查詢：SELECT * FROM tableName WHERE num=10000。如果沒有索引，必須遍歷整個表，直到num=10000的這一行為止；如果在num列上建立索引，SQL Server不需要任何掃描，直接在索引裡面找10000，就可以得知這一行的位置。可見，索引的建立可以加快資料的查詢速度。

索引的優點主要有以下幾條：

 (1).通過建立唯一索引，可以保證資料庫表的每一行資料的唯一性。

 (2).可以大大加快資料的查詢速度，這也是建立索引的最主要的原因。

 (3).實現資料的參照完整性，可以速表和表之間的連線。

 (4).在使用分組和排序子句進行查詢時，也可以顯著減少查詢中分組和排序的時間。

同時，增加索引也有不利的一面：

 (1).建立索引和維護索引要耗費時間，並且隨著資料量的增加所耗費的時間也會增加。

 (2).索引需要佔磁碟空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間，如果有大量的索引，索引檔案可能比資料檔案更快達到做大檔案尺寸。

 (3).當對錶中的資料進行增加，刪除和修改的時候，索引也要動態地維護，這樣就就降低了資料的維護速度。

二：索引的分類

 不同資料庫中提供了不同的索引型別，SQL Server中的索引有兩種：聚集索引和非聚集索引。它們的區別是在物理資料的儲存方式上。

1.聚集索引 

  聚集索引基於資料行的鍵值，在表內排序和儲存這些資料行。每個表只能有一個聚集索引，因為資料行本身只能按一個順序儲存。

 (1).每個表只能有一個聚集索引；

 (2). 表中的物理順序和索引中行的物理順序是相同的，建立任何非聚集索引之前要首先建立聚集索引，這是因為非聚集索引改變了表中行的物理順序；

 (3).關鍵值的唯一性使用UNIQUE關鍵字或者由內部的唯一識別符號明確維護。

 (4).在索引的建立過程中，SQL Server臨時使用當前資料庫的磁碟空間，所以要保證有足夠的空間建立索引。

2.非聚集索引

 非聚集索引具有完全獨立於資料行的結構，使用非聚集索引不用將物理資料頁中的資料按列排序，非聚集索引包含索引鍵值和指向表資料儲存位置的行定位器。

 可以對錶或索引檢視建立多個非聚集索引。通常，設計非聚集索引是為了改善經常使用的、沒有建立聚集索引的查詢的效能。

 查詢優化器在搜尋資料值時，先搜尋非聚集索引以找到資料值在表中的位置，然後直接從該位置檢索資料。這使得非聚集索引成為完全匹配查詢的最佳選擇，因為索引中包含搜尋的資料值在表中的精確位置的項。

 具有以下特點的查詢可以考慮使用非聚集索引：

  (1).使用JOIN或者GROUP BY子句，應為連線和分組操作中所涉及的列建立多個非聚集索引，為任何外來鍵建立一個聚集索引.

  (2).包含大量唯一值的欄位。

  (3).不返回大型結果集的查詢。建立篩選索引以覆蓋從大型表中返回定義完善的的行子集的查詢。

  (4).經常包含在查詢的搜尋條件(如返回完全匹配的WHERE子句)中的列。

3.其它索引

 除了聚集索引和非聚集索引之外，SQL Server中還提供了其它的索引型別。 

唯一索引:確保索引鍵不包含重複的值，因此，表或檢視中的每一行在某種程度上是唯一的。聚集索引和非聚集索引都可以是唯一索引。這種唯一性和主鍵約束是關聯的，在某種程度上，主鍵約束等於唯一性的聚集索引。

包含列索引：一種非聚集索引，它擴充套件後不僅包含鍵列，還包含非堅列。

索引檢視：在檢視上新增索引後能提高檢視的查詢效率。檢視的索引將具體化檢視，並將結果集永久儲存在唯一的聚集索引中，而且儲存方式相同，建立聚集索引後，可以為檢視新增非聚集索引。

全文索引：一種特殊型別的基於標記的功能索引，由SQL Server全文引擎生成和維護，用於幫助在字串資料中搜尋複雜的詞，這種索引的結構與資料庫引擎使用的聚集索引或非聚集索引的B樹結構是不同的。

空間索引：一種針對geometry資料型別的列上建立的索引，這樣可以更高效的列中的空間物件執行某些操作，空間索引可以減少需要應用開銷相對較大的空間操作的物件數。

篩選索引:一種經過優化的的非聚集索引，尤其適用於涵蓋從定義完善的資料子集中選擇資料的查詢。篩選索引使用篩選謂詞對錶中的的部分進行索引，與全表索引相比，設計良好的篩選索引可以提高查詢效能，減少索引維護開銷並可降低索引儲存開銷

XML索引：是與XML資料關聯的索引形式，是XML二進位制大物件(BLOB)的已拆分持久表示形式，XML索引可以分為主索引和輔助索引。

三：索引的設計原則

 索引設計不合理或者缺少索引都會對資料庫和應用程式的效能造成障礙，高效的索引對於獲得良好的效能非常重要。設計索引時，應該考慮以下原則：

 (1).索引並非越多越好，一個表中如果有大量的索引，不僅佔用大量的磁碟空間，而且會影響INSERT、DELETE、UPDATE等語句的效能。因為當表中資料更改的同時，索引也會進行調整和更新。

 (2).避免對經常更新的表進行過多的索引，並且索引中的列儘可能少。而對經常用於查詢的欄位應該建立索引，但要避免新增不必要的欄位。

 (3).資料量小的表最好不要使用索引，由於資料較少，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果。

 (4).在條件表示式中經常用到的、不同值較多的列上建立索引，在不同值較少的列上不要建立索引、比如在學生表的【性別】

欄位上只有【男】或【女】兩個不同值，因此就無需建立索引，如果建立索引，不但不會提高查詢效率，反而會嚴重降低更新速度。 

  (5).當唯一性是某種資料本身的特徵時，指定唯一索引，使用唯一索引能夠確保定義的列的資料完整性，提高查詢速度。

 (6).在頻繁進行排序或分組（即進行GROUP BY 或ORDER BY操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引。

四：建立索引

 在瞭解了不同索引型別後，下面開始介紹如何使用T_SQL建立索引:

  CREATE INDEX命令既可以建立一個改變表的物理順序的聚集索引，也可以建立提高查詢效能的非聚集索引。語法如下：

  CREATE [UNIQUE] [CLUSTERED   |    NONCLUSTERRED]

  INDEX index_name ON {table | view }(column [ASC | DESC ]  [,...n])

  [ INCLUDE ( column_name [ ,...n])]

  [with

   (

    PAD_INDEX={ON | OFF}

    | FILLFACTOR =fillfactor

    | SORT_IN_TEMPDB={ ON | OFF}

    |  IGNORE_DUP_KEY ={ ON  | OFF}

    |  STATISRICS_NORECOMPUTE ={ ON | OFF}

    |   DROP_EXISTING = { ON | OFF}

    |  ONLINE ={ ON | OFF}

    |  ALLOW_ROW_LOCKS ={ ON | OFF}

    |  ALLOW_PAGE_LOCKS={ ON   | OFF}

    |  MAXDOP =max_degree_of_parallelism     

   ) [,...n] ]

UNIQUE:表示在表或檢視上建立唯一索引。唯一索引不允許兩行具有相同的索引鍵值。檢視的聚集索引必須唯一。

CLUSTERED:表示建立聚集索引，在建立任何非聚集索引之前建立聚集索引，建立聚集索引時會重新生成表中現有的非聚集索引。如果沒有指定CLUSTERED，則建立非聚集索引。

NONCLUSTERED：表示建立一個非聚集索引，非聚集索引資料行的物理順序獨立於與索引排序，每個表最多可包含999個非聚集索引，NONCLUSTERED是CREATE INDEX的預設值。

index_name:指定索引的名稱，索引名稱在表或檢視中必須唯一，但在資料庫中可不必唯一。

ON{table | view }:指定索引所屬的表或檢視。

Column：指定索引基於一列或者多列。指定兩個或者多個列名，可為指定的列的組合值建立組合索引，{table | view}後的括號中，按排序優先順序列出組合索引要包括的列。一個組合索引鍵中最多可組合16列。組合索引鍵中的所有列必須在同一個表或檢視中。

[ASC | DESC]:指定索引列的升序或降序排序方向，預設為ASC。

INCLUDE（column[,...n]）：指定要新增到非聚集索引的葉級別的非鍵列。

PAD_INDEX:表示指定索引填充，預設值為OFF。ON值表示fillfactor指定的可用空間百分比應用於索引的中間級頁。

FILLFACTOR=fillfactor:制定一個百分比，表示在索引建立或重新生成過程中資料庫引擎應使每個索引頁的葉級別達到的填充程度。fillfactor必須為介於1至100直接的整數值，預設值為0；

SORT_IN_TESTDB:指定是否在tempdb中儲存臨時排序結果，預設值為OFF，ON值表示在tempdb中儲存用於生成索引的中間排序結果，OFF表示中間排序結果與索引儲存在同一資料庫中。

IGNORE_DUP_KEY:指定唯一聚集索引或唯一非聚集索引執行多行插入操作時，出現重複鍵值的錯誤響應。預設值為OFF。ON表示發出一跳警告訊息，但只有違反了唯一索引的行才會失敗，OFF表示發出錯誤訊息，並回滾整個INSERT事物。

STATISRICS_NORECOMPUTE:指定是否重新計算分發統計資訊，預設值為OFF。ON表示不會自動重新計算過時的統計資訊，OFF表示啟用統計資訊自動更新功能。

DROP_EXISTING:指定刪除並重新生成已命名的先前存在的聚集或非聚集索引。預設值為OFF。ON表示刪除並重新生成現有索引。指定的索引名稱必須與當前現有的索引相同，但可以修改索引定義，例如：可以指定不同的列，排序順序，分割槽方案或索引選項。OFF表示如果指定的索引名已存在，則會顯示一條錯誤。

ONLINE={ON | OFF}:指定在索引操作期間，基礎表和關聯的索引是否用於查詢和資料修改操縱，預設值為OFF。

ALLOW_ROW_LOCKS:指定是否允許行鎖，預設值為ON，ON表示在訪問索引時允許行所。資料庫引擎確定何時使用行鎖。OFF表示未使用行鎖。

ALLOW_PAGE_LOCKS:指定是否允許頁鎖，預設值為ON，ON表示在訪問索引時允許頁鎖。資料庫引擎確定何時使用頁鎖。ＯＦＦ表示未使用頁鎖。

MAXDOP:指定在索引操作期間，覆蓋【最大並行度】配置選項，使用MAXDOP可以限制在執行並行計劃的過程中使用的處理器數量，最大數量為64.

【例1】在teacher表中的t_phone列上，建立一個名稱為Idx_phone的唯一聚集索引，降序排列，填充因子為30%，輸入語句如下:

 CREATE UNIQUE  CLOSTERED INDEX Idx_phone

 ON  teacher (t_phone  DESC)

 WITH

 FILLFACTOR=30;

【例2】 --在teacher表中的t_name和t_gender列上，建立一個名稱為Idx_nameAndgender的-唯一非聚集組合索引，升序排列，填充因子為10%。如下：

--首先建立teacher表

   CREATE TABLE teacher(

     t_id int IDENTITY(1,1) PRIMARY KEY ,

     t_name varchar(20),

     t_phone varchar(11),

     t_gender char(2)

   )

--建立索引

CREATE UNIQUE NONCLUSTERED INDEX Idx_nameAndgender

ON teacher(t_name,t_gender)

WITH

FILLFACTOR=10;

注意：如果teacher表中定義了主鍵，則不能執行【例1】中的指令碼，因為每個表只能有一個聚集索引，資料行本身只能按一個順序儲存。並且主鍵本身就是一個聚集索引。

五:管理和維護索引

 索引建立後可以根據需要對資料庫中的索引進行管理，例如在資料表中進行增加，刪除或者更新操作，會使索引頁出現碎塊，為了提高系統的效能，必須對索引進行維護管理，這些管理包括顯示索引資訊、索引的效能分析和維護，以及刪除索引等。

1.顯示索引資訊

 (1).用系統儲存過程檢視索引資訊

   系統儲存過程sp_helpindex可以返回某個表或檢視中的索引資訊，語法如下：

   sp_helpindex [@objectname= ]  'name';

   [@objectname=] 'name':使用者定義的表或檢視的限定或非限定名稱。僅當指定限定的表或檢視名稱時，才需要使用引號。如果提供了完全限定的名稱，包括資料庫名稱，則該資料庫名稱必須是當前資料庫的名稱。

 【例】使用儲存過程檢視是 sample_db資料庫中teacher表中定義的索引資訊，輸入語句如下。

  USE  sample_db;

　 GO

  exec sp_helpindex 'teacher';

 由執行結果可以看到，這裡顯示了authors表中的索引資訊：

 index_name:指定索引名稱.

 index_description:包含索引的描述資訊，例如唯一性索引，聚集索引等。

 index_keys:包含了索引所在表中的列.

(2).檢視索引的統計資訊

 索引資訊還包括統計資訊，這些資訊可以用來分析索引效能，更好的維護索引，索引統計資訊是查詢優化器用來分析和評估查詢、制定最優查詢方式的基礎資料，使用者可以用圖形介面化工具來檢視索引資訊，也可以使用DBCC SHOW_STATISTICS命令來檢視索引的資訊。

【例】使用DBCC SHOW_STATISTICS命令來檢視teacher表中Idx_phone索引的統計資訊，如下：

  DBCC SHOW_STATISTICS ('sample_db.dbo.teacher',Idx_phone);

　　返回的統計資訊包括三個部分：統計標題資訊，統計密度資訊和統計直方資訊。統計標題資訊主要包括表中的行數，統計抽樣行數，索引列的平均長度等。統計密度資訊主要包括索引列字首集選擇性，平均長度等資訊，統計直方圖資訊即為顯示直方圖時的資訊。

(3).重新命名索引

 系統儲存過程sp_rename可以用於更改索引的名稱，其語法如下：

 sp_rename 'object_name','new_name' 'object_type'

object_name:使用者物件或資料型別的當前限定或非限定名稱。此物件可以是表，索引，列，別名資料庫型別或使用者定義型別。

new_name:指定物件的新名稱；

object_type:指定修改的物件型別。下表列出了物件型別的取值：

               sp_rename函式可重新命名的物件

 【例】將teacher表中的索引名稱idx_nameAndgender更改為multi_index,輸入如下:

USE  sample_db;

GO

exec sp_rename  'teacher.idx_nameAndgender' ,'multi_index', index;

(4)刪除索引

 當不再需要某個索引時，可以將其刪除，DROP INDEX命令可以刪除一個或者多個當前資料庫中的索引，語法如下：

DROP INDEX '[table | view ].index' [,...n]

或者

DROP INDEX ' index'  ON '[table | view ].index' 

[table | view ]:用於指定索引列所在的表或檢視；

index:用於指定要刪除的索引名稱。

注意：DROP INDEX 命令不能刪除由 CREATE TABLE或者ALTER TABLE命令建立的主鍵（PRIMARY KEY）或者唯一性（UNIQUE）約束索引，也不能刪除系統表重的索引。

【例】刪除表teacher中的索引 multi_index，如下：

USE  sample_db;

GO

exec sp_helpindex 'teacher'

DROP INDEX teacher.multi_index

exec sp_helpindex 'teacher';

LZ在一次面試中遇到的面試題:

1.索引對資料庫效能如此重要，應該如何使用它？

答:為資料庫選擇正確的索引是一項複雜的任務，如果索引較少，則需要的磁碟空間和維護開銷都較少，如果在一個大表上建立了多種組合索引，索引檔案會膨脹的很快，而另一方面，索引較多則可覆蓋更多的查詢，可能需要試驗若干不同的設計，才能找到最有效的索引，可以新增、修改和刪除索引而不影響資料庫架構或應用程式設計。因此，應該嘗試多個不同的索引，從而建立最優的索引。

2.為什麼要使用短索引？

答：對字元型別的欄位進行索引，如果可能應該指定一個字首長度；例如：如果有一個varchar(255)的列，如果在前10個或者30個字元內，多數值是唯一的，則不需要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和減少I/O操作。

 以上例子的整體程式碼：

--索引
--建立teacher表
create database sample_db;
CREATE TABLE teacher(
    t_id int IDENTITY(1,1),
    t_name varchar(20),
    t_phone varchar(11),
    t_gender char(2)
)
--【例1】在teacher表中的t_phone列上，建立一個名稱為Idx_phone的唯一聚集索引，
--降序排列，填充因子為30%，輸入語句如下:
CREATE UNIQUE  NONCLUSTERED INDEX Idx_phone
ON  teacher (t_phone  DESC)
WITH
FILLFACTOR=30;
--【例2】在teacher表中的t_name和t_gender列上，建立一個名稱為Idx_nameAndgender的
--唯一非聚集組合索引，升序排列，填充因子為10%。如下：
CREATE UNIQUE NONCLUSTERED INDEX Idx_nameAndgender
ON teacher(t_name,t_gender)
WITH
FILLFACTOR=10;
--用系統儲存過程檢視索引資訊
USE  sample_db;
GO
exec sp_helpindex 'teacher';
--檢視索引統計資訊
DBCC SHOW_STATISTICS ('sample_db.dbo.teacher',Idx_phone);
--【例】將teacher表中的索引名稱Idx_nameAndgender更改為multi_index,
--輸入如下:
USE  sample_db;
GO
exec sp_rename  'teacher.Idx_nameAndgender' ,'multi_index', 'index';
--【例】刪除表teacher中的索引 multi_index，如下：
USE  sample_db;
GO
exec sp_helpindex 'teacher'
DROP INDEX teacher.multi_index
exec sp_helpindex 'teacher';