SQL SERVER 2008索引維護實作篇

2012-01-14

查看索引資訊

我們可以透過指令與親和力的圖形使用者介面進行查看。

(1) 透過 sp_helpindex 儲存程序

(2) 透過 SQL SERVER Management Studio 進行檢索索引資訊

透過物件總管工具，展開目標資料庫->資料表，將會看到索引鍵資料夾

建立索引

透過 Create Index 語法來建立索引

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON <object> ( column [ ASC | DESC ] [ ,...n ] )

A. 建立簡單的非叢集索引

        1: USE AdventureWorks;

       2:  

       3: GO

       4:  

       5: IF EXISTS (SELECT name FROM sys.indexes

       6:  

       7: WHERE name = N'IX_ProductVendor_VendorID')

       8:  

       9: DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;

      10:  

      11: GO

      12:  

      13: CREATE INDEX IX_ProductVendor_VendorID 

      14:  

      15: ON Purchasing.ProductVendor (VendorID); 

      16:  

      17: GO

B. 建立簡單的非叢集複合索引

       1: USE AdventureWorks

       2:  

       3: GO

       4:  

       5: IF EXISTS (SELECT name FROM sys.indexes

       6:  

       7: WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')

       8:  

       9: DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;

      10:  

      11: GO

      12:  

      13: CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD

      14:  

      15: ON Sales.SalesPerson (SalesQuota, SalesYTD);

      16:  

      17: GO

C. 建立唯一的非叢集索引

       1: USE AdventureWorks;

       2:  

       3: GO

       4:  

       5: IF EXISTS (SELECT name from sys.indexes

       6:  

       7: WHERE name = N'AK_UnitMeasure_Name')

       8:  

       9: DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;

      10:  

      11: GO

      12:  

      13: CREATE UNIQUE INDEX AK_UnitMeasure_Name 

      14:  

      15: ON Production.UnitMeasure(Name);

      16:  

      17: GO

D.建立唯一的叢集所引

       1: USE AdventureWorks;

       2:  

       3: GO

       4:  

       5: IF EXISTS (SELECT name from sys.indexes

       6:  

       7: WHERE name = N'PK_UnitMeasure_ID')

       8:  

       9: DROP INDEX PK_UnitMeasure_ID ON Production.UnitMeasure;

      10:  

      11: GO

      12:  

      13: CREATE UNIQUE CLUSTERED INDEX PK_UnitMeasure_ID 

      14:  

      15: ON Production.UnitMeasure(ID);

      16:  

      17: GO

修改索引

語法：

ALTER INDEX { index_name | ALL } ON <object>

{ REBUILD | DISABLE | REORGANIZE}

A. 重建索引

在 Employee 資料表上，重建單一索引。

       1: USE AdventureWorks;

       2:  

       3: GO

       4:  

       5: ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee

       6:  

       7: REBUILD;

B. 在資料表上重新組織索引

       1: USE AdventureWorks;

       2:  

       3: GO

       4:  

       5: ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto

       6:  

       7: REORGANIZE ;

C. 停用索引

       1: USE AdventureWorks;

       2:  

       3: GO

       4:  

       5: ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee

       6:  

       7: DISABLE ;

       8:  

       9: GO

刪除索引

語法：

DROP INDEX ON 資料表名.索引名 | 檢視表名.索引名

CODE:

       1: DROP INDEX [CustomerID] ON [dbo].[Orders] WITH ( ONLINE = OFF )

使用系統內建的系統資料表查看索引資訊

詳細欄位意義請查詢MSDN官方定義
http://technet.microsoft.com/zh-tw/library/ms190283(SQL.90).aspx

維護索引的統計資訊

索引的統計資訊，可以協助SQL SERVER 的查詢引擎利用這些資訊來確定最佳的查詢計畫，進而提高查詢的效率。

(1) 透過SQL SERVER Management Studio工具進行查看索引統計資訊

依序選擇目標資料庫->資料表->統計資料，如圖示

在要進行查看的索引統計資訊，雙擊滑鼠兩下，將可以看到更詳細的資訊

(2) 透過命令語法

使用系統檢視表sys.stats進行查看

欄位定義請查詢MSDN完整的定義
http://technet.microsoft.com/zh-tw/library/ms177623.aspx

使用DBCC SHOW_STATISTICS 命令

DBCC SHOW_STATISTICS 會針對資料表或索引檢視表顯示目前的查詢最佳化統計資料。

語法

1: DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )

2:

3: [ WITH [ NO_INFOMSGS ] < option > [ , n ] ]

4:

5: < option > :: =

6:

7: STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

更完整的欄位資訊，請參閱線上MSDN

http://technet.microsoft.com/zh-tw/library/ms174384.aspx

使用系統預存程序 sp_autostats 來查看資料庫中指定的資料表或索引檢視之所有索引和統計資料的自動 UPDATE STATISTICS 設定

(TIP) 在SQL SERVER 2008，資料庫選項的AUTO_CREATE_STATISTICS是最作用於控制是否自動建立索引統計資訊，預設值為ON，因此如果執行了SELECT 、INSERT、UPDATE和DELETE語法中使用到索引指定的欄位，則資料庫會自動為該欄位建立對應的統計資訊，以加速執行查詢計畫。

手動建立索引統計資訊

透過Create Statistics 語法建立

1: CREATE STATISTICS statistics_name

2: ON { table | view } ( column [ ,...n ] )

3: [ WHERE <filter_predicate> ]

4: [ WITH

5: [ [ FULLSCAN

6: | SAMPLE number { PERCENT | ROWS }

7: | STATS_STREAM = stats_stream ] [ , ] ]

8: [ NORECOMPUTE ]

9: ] ;

例如，以下為Contact資料表的ContactID, EmailAddress兩個欄位建立統計資訊

1: CREATE STATISTICS ContactMail1

2:

3: ON Person.Contact (ContactID, EmailAddress)

使用sp_createstats 預存程序

改程序主要是針對目前資料庫中的所有使用者資料表和內部資料表，建立所有適用資料列的單一資料行統計資料。

語法

sp_createstats [ [ @indexonly = ] 'indexonly' ]

[ , [ @fullscan = ] 'fullscan' ]

[ , [ @norecompute = ] 'norecompute' ]

引數

[ @indexonly = ] 'indexonly'

指定只應考慮建立參與索引之資料行的統計資料。indexonly 是 char(9)。預設值是 NO。

[ @fullscan = ] 'fullscan'

指定搭配 CREATE STATISTICS 來使用 FULLSCAN 選項。如果省略 fullscan，SQL Server Database Engine 會執行預設範例掃描。fullscan 是 char(9)。預設值是 NO。

[ @norecompute = ] 'norecompute'

指定針對新建的統計資料來停用統計資料的自動重新計算。norecompute 是 char(12)。預設值是 NO。

範例是針對目前資料庫中的所有使用者資料表，建立所有適用資料列的統計資料。

1: EXEC sp_createstats;

修改統計資訊

語法

1: UPDATE STATISTICS table | view

2: [

3: {

4: { index | statistics_name }

5: | ( { index |statistics_name } [ ,...n ] )

6: }

7: ]

8: [ WITH

9: [

10: [ FULLSCAN ]

11: | SAMPLE number { PERCENT | ROWS } ]

12: | RESAMPLE

13: ]

此語法結構跟前面講的Create Statistics語法相似，在此直接與取之MSDN範例說明。

A. 更新單一資料表的所有統計資料

1: UPDATE STATISTICS Sales.SalesOrderDetail;

B. 只更新單一索引的統計資料

1: UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;

C. 利用 50% 取樣來更新特定統計資料群組 (集合) 的統計資料

1: CREATE STATISTICS Products

2:

3: ON Production.Product ([Name], ProductNumber)

4:

5: WITH SAMPLE 50 PERCENT

6:

7: -- Time passes. The UPDATE STATISTICS statement is then executed.

8:

9: UPDATE STATISTICS Production.Product(Products)

10:

11: WITH SAMPLE 50 PERCENT;

D. 利用 FULLSCAN 和 NORECOMPUTE 來更新特定統計資料群組 (集合) 的統計資料

1: UPDATE STATISTICS Production.Product(Products)

2:

3: WITH FULLSCAN, NORECOMPUTE;

刪除統計資訊

有了前面的基礎，新增、修改統計資訊的觀念後，最後當然也有刪除的語法，刪除很簡單，只要透過簡單的語法 DROP STATISTICS 資料表.索引名，就可以進行刪除。

後續有時間會繼續寫索引如何優化資料庫的觀念………By Allen

回首頁