SqlServer——索引

[0]發表於2014-09-25
SQLServer索引

索引是根據表中一列或若干列按照一定順序建立的列值與記錄行之間的對應關係表。在資料庫系統中建立索引主要有以下作用：

l快速存取資料；
l保證資料記錄的唯一性；
l實現表與表之間的參照完整性；
l在使用ORDER BY、GROUP BY子句進行資料檢索時，利用索引可以減少排序和分組的時間。
 
1．聚集索引

 聚集索引將資料行的鍵值在表內排序並儲存對應的資料記錄，使得資料表物理順序與索引順序一致。SQL Server 是按B樹（BTREE）方式組織聚集索引的，B樹方式構建為包含了多個節點的一棵樹。頂部的節點構成了索引的開始點，叫做根。每個節點中含有索引列的幾個值，一個節點中的每個值又都指向另一個節點或者指向表中的一行，一個節點中的值必須是有序排列的。指向一行的一個節點叫做葉子頁。葉子頁本身也是相互連線的，一個葉子頁有一個指標指向下一組。這樣，表中的每一行都會在索引中有一個對應值。查詢的時候就可以根據索引值直接找到所在的行。

•聚集索引中B樹的葉節點存放資料頁資訊。聚集索引在索引的葉級儲存資料。這意味著不論聚集索引裡有表的哪個（或哪些）欄位，這些欄位都會按順序儲存在表中。由於存在這種排序，所以每個表只會有一個聚集索引。
•由於資料記錄按聚集索引鍵的次序儲存，故聚集索引對查詢記錄很有效。 
 
2．非聚集索引

非聚集索引完全獨立於資料行的結構。SQL Server 也是按B樹方式組織非聚集索引的，與聚集索引的不同之處在於：非聚集索引B樹的葉節點不存放資料頁資訊，而是存放非聚集索引的鍵值，並且每個鍵值項都有指標指向包含該鍵值的資料行。

在非聚集索引內，從索引行指向資料行的指標稱為行定位器。行定位器的結構取決於資料頁的儲存方式是堆集還是聚集。對於堆集，行定位器是指向行的指標。對於有聚集索引的表，行定位器是聚集索引鍵。只有在表上建立聚集索引時，表內的行才按特定順序儲存，這些行按聚集索引鍵順序儲存。如果一個表只有非聚集索引，則它的資料行將按無序的堆集方式儲存。

在PXSCJ資料庫中，經常要對XSB、KCB、CJB三個表進行查詢和更新。為了提高查詢和更新速度，可以考慮對三個表建立如下索引：

（1）對於XSB表，按學號建立唯一索引組織方式為聚集索引。

（2）對於KCB表，按課程號建立主鍵索引，組織方式為聚集索引。

（3）對於KCB表，按課程名建立唯一索引（UNIQUE約束），組織方式為非聚集索引。

（4）對於CJB表，按學號+課程號建立唯一索引，組織方式為聚集索引。

在SQL Server Management Studio中，既可利用介面方式建立上述索引，也可以利用T-SQL命令通過查詢分析器建立索引。

 

1．介面方式建立索引

啟動“SQL Server Management Studio”，在物件資源管理器中展開“資料庫”，選擇“表”中的“dbo.XSB”，右擊其中的“索引”項，在彈出的快捷選單上選擇“新建索引(N)…”選單項。

這時，使用者可以在彈出的“新建索引”視窗中輸入索引名稱（索引名在表中必須唯一），如PX_XSB，選擇索引型別為“聚集”，勾選“唯一”核取方塊，單擊新建索引視窗的“新增”按鈕，在彈出的“選擇要新增到索引鍵的表列”視窗中選擇要新增的列，新增完畢後，單擊“確定”按鈕，在主介面中為索引鍵列設定相關的屬性，單擊“確定”按鈕，即完成索引的建立工作。

 

在“表設計器”視窗建立索引的方法如下。

第1步：右擊PXSCJ資料庫中的“dbo.XSB”表，在彈出的快捷選單中選擇“設計”選單項，開啟“表設計器”視窗。

第2步：在“表設計器”視窗中，選擇“學號”屬性列，右擊滑鼠，在彈出的快捷選單中選擇“索引/鍵”選單項。在開啟的“索引/鍵”視窗中單擊“新增”按鈕，並在右邊的“標識”屬性區域的“名稱”一欄中確定新索引的名稱（用系統預設的名或重新取名）。在右邊的常規屬性區域中的“列”一欄後面單擊“ ”按鈕，可以修改要建立索引的列。如果將“是唯一的”一欄設定為“是”則表示索引是唯一索引。在“表設計器”欄下的“建立為聚集的”選項中，可以設定是否建立為聚集索引，由於XSB表中已經存在聚集索引，所以這裡的這個選項不可修改。

第3步：最後關閉該視窗，單擊皮膚上的“儲存”按鈕，在彈出的對話方塊中單擊“是”按鈕，索引建立即完成。

索引建立完後，只需返回SSMS主視窗，在物件資源管理器中展開“dbo.XSB”表中的“索引”項，就可以檢視已建立的索引。其他索引的建立方法與之類似。

 

2．利用SQL命令建立索引

使用CREATE INDEX語句可以為表建立索引。

語法格式：

CREATE [ UNIQUE ]   /*指定索引是否唯一*/

  [ CLUSTERED | NONCLUSTERED ]   /*索引的組織方式*/

  INDEX index_name   /*索引名稱*/

    ON {[ database_name. [ schema_name ] . | schema_name. ] table_or_view_name}

   ( column [ ASC | DESC ] [ ,...n ] )   /*索引定義的依據*/

    eg:

1 /*為KCB表的“課程名”列建立索引*/
2 create index kc_name_ind
3     on KC(Cname)
4 go
View Code

Result:picture2

eg:

1 /*根據KCB表的“課程號”列建立唯一聚集索引，因為指定了CLUSTERED，所以該索引將對磁碟上的資料進行物理排序。*/
2 create unique clustered index xs_id_ind
3     on XS(Sname)
View Code

Result:

picture3

eg:

1 /*建立複合索引。*/
2 create index cj_ind
3     on XS(Sage,Sno1)
View Code

Result:

p4

eg:

1 /*根據XSB表中的“總學分”列建立索引*/
2 CREATE NONCLUSTERED INDEX score_ind 
3     ON XS(Sno1)
View Code

eg:

1 /*根據XSB表中“學號”列建立唯一聚集索引。如果輸入了重複的鍵，將忽略該INSERT或UPDATE語句。*/
2 CREATE UNIQUE CLUSTERED INDEX xs_ind 
3     ON XS(Sno)
View Code

建立索引有如下幾點要說明：

（1）在計算列上建立索引。對於UNIQUE或PRIMARY KEY索引，只要滿足索引條件，就可以包含計算列，但計算列必須具有確定性，必須精確。若計算列中帶有函式，則使用該函式時有相同的引數輸入，輸出的結果也一定相同時，該計算列是確定的。而有些函式，如getdate()，每次呼叫時都輸出不同的結果，這時就不能在計算列上定義索引。

計算列為text、ntext或image列時也不能在該列上建立索引。

（2）在檢視上建立索引。可以在檢視上定義索引。索引檢視是一種在資料庫中儲存檢視結果集的方法，可減少動態生成結果集的開銷。索引檢視還能自動反映出建立索引後對基表資料所做的修改。

 eg:

 1 /*定義檢視，在以下例子中使用了WITH  SCHEMABINDING子句，因此定義檢視時，SELECT子句中表名必須為“架構名.表名”的形式*/
 2 CREATE VIEW View_stu WITH SCHEMABINDING 
 3     AS 
 4     SELECT Sno,Sname 
 5         FROM   dbo.XS
 6 GO
 7 
 8 /*在檢視上建立索引*/
 9 CREATE UNIQUE CLUSTERED INDEX Inx1 
10     ON View_stu(Sno)
11 GO
View Code

Result:

p5.1,p5.2

1．通過圖形介面方式刪除索引

通過圖形介面方式刪除索引的主要步驟如下：

啟動SQL Server Management Studio，在物件資源管理器中展開資料庫“PXSCJ→表→dbo.XSB→索引”，選擇其中要刪除的索引，單擊滑鼠右鍵，在彈出的快捷選單上選擇“刪除”選單項。在開啟的“刪除物件”視窗中單擊“確定”按鈕即可。

2．通過SQL命令刪除索引

從當前資料庫中刪除一個或多個索引。

語法格式：

DROP INDEX

{    index_name ON  table_or_view_name [ ,...n ]

  | table_or_view_name.index_name [ ,...n ]

}

 　　

DROP INDEX語句可以一次刪除一個或多個索引。這個語句不適合刪除通過定義PRIMARY KEY或UNIQUE約束建立的索引。若要刪除PRIMARY KEY或UNIQUE約束建立的索引，必須通過刪除約束實現。

另外，在系統表的索引上不能進行DROP INDEX操作。

eg:

1 /*刪除PXSCJ資料庫中表KCB的一個索引名為kc_name_ind的索引。*/
2 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'kc_name_ind')
3     DROP INDEX KC.kc_name_ind
View Code

 

 

 

 

相關文章