SQLServer中在視圖上使用索引（轉載）

標籤：

在SQL Server中，視圖是一個儲存的T-SQL查詢。視圖定義由SQL Server儲存，以便它能夠用作一個虛擬表來簡化查詢，並給基表增加另一層安全。但是，它並不佔用資料庫的任何空間。實際上，在你查詢它之前，視圖並不做任何事情。

索引檢視表

在SQL Server 2000和2005中，你能夠給視圖增加索引。但是，如果視圖只是一個儲存在資料庫中的查詢定義，在運行前沒有自己的資料，你如何給那個定義建立一個索引呢？嗯，這比較麻煩。

索引檢視表是一個已被物化或儲存在資料庫中的視圖。當基本表更新時，給視圖建立的索引由資料庫引擎儲存和更新。當你的結果集返回許多行並且需要求總數時，索引檢視表十分有用。這是因為資料庫引擎必須在基表資料更新時維持視圖索引，這可能會降低事務的效能。

要給一個視圖建立一個索引，視圖定義必須遵守某組條件和會話設定，同時要求把基表和視圖定義聯絡起來。如果你確定你的視圖滿足這些標準，你給視圖建立的第一個索引必須是一個唯一的叢集索引，之後你可以給視圖建立多個非叢集索引。建立的第一個視圖必須在一組欄上，並且被聚集，以便索引被物化。

列表A說明如何建立一個索引檢視表。其中的指令碼將建立SalesHistory表，並給它載入資料。

下面的代碼將建立一個視圖來概括表中的資料：

CREATE VIEW vw_salesbyproductASSELECTProduct,COUNT_BIG(*) as ProductCount,SuM(SalePrice) as TotalSalesFROMdbo.SalesHistoryGROUP BY Product

 

由於它只是一個T-SQL查詢定義，建立這個視圖不用多少時間。建立好視圖後，你就可以像對一個表一樣對它進行查詢。

SELECT Product, TotalSales, ProductCountFROM vw_SalesByProductWHERE product = ‘Computer‘

 

如果你在SQL Server Management Studio或Query  Analyzer中設定選項來查看查詢的“執行計畫”（Execution  Plan），你會注意到上面的查詢使用一個表掃描來找出Computer產品的總計結果。表掃描一般用於資料沒有索引的情況下，它對整個結果集進行掃描以 找出需要的結果。

 

進行一些簡單的改變，你就能夠修改視圖，以便你可以給它增加一個索引，從而改善搜尋效能。首先，你必須設定下面的會話設定(注意必須要嚴格按照下面set語句的設定後，才能在視圖上成功開啟索引功能)：

SET ANSI_NULLS ONGOSET ANSI_PADDING ONGOSET ANSI_WARNINGS ONGOSET CONCAT_NULL_YIELDS_NULL ONGOSET QUOTED_IDENTIFIER ONGOSET NUMERIC_ROUNDABORT OFFGO--現在你可以建立自己的視圖。為使事情更加簡單，我建立一個嶄新的視圖。注意使用索引的視圖在建立的時候必須要使用with schemabinding語句CREATE VIEW dbo.vw_SalesByProduct_IndexedWITH SCHEMABINDINGASSELECTProduct,COUNT_BIG(*) AS ProductCount,SUM(ISNULL(SalePrice,0)) AS TotalSalesFROM dbo.SalesHistoryGROUP BY ProductGO--下面的指令碼給我們的視圖建立索引：CREATE UNIQUE CLUSTERED INDEXidx_SalesView ON vw_SalesByProduct_Indexed(Product)--為表明已經給視圖建立一個索引，並且它確實佔用資料庫的空間，運行下面的指令碼查明叢集索引有多少行以及視圖佔用多少空間。EXECUTE sp_spaceused ‘vw_SalesByProduct_Indexed‘--下面的SELECT語句和以前的語句相同，只是這次它執行一個叢集索引搜尋，這個過程完成得非常快。SELECTProduct, TotalSales, ProductCountFROM vw_SalesByProduct_IndexedWHERE Product = ‘Computer‘

加了視圖索引後，沒有命中索引？

當你完成上面的步驟後如果在視圖上執行查詢語句，有可能發現語句的執行計畫還是用的Table Scan來掃描底層SalesHistory表，而不是用的Index Scan或Index Seek來使用我們上面在視圖上加的索引idx_SalesView，這有可能是Sqlserver版本的問題，按照老外一篇文章的說法如果你的Sqlserver不是企業版就算建立了視圖索引，但是在查詢中也不會去使用視圖索引，同時也有可能是Sqlserver查詢分析器認為直接掃描底層表比用視圖上的索引效率更高，所以導致你的查詢語句沒使用視圖索引。這時我們可以使用WITH ( NOEXPAND )語句來告訴Sqlserver在查詢中使用視圖索引，語句如下：

SELECTProduct, TotalSales, ProductCountFROM vw_SalesByProduct_Indexed WITH (NOEXPAND)WHERE Product = ‘Computer‘

加了WITH ( NOEXPAND )語句後，上面的查詢應該就會用到我們在視圖vw_SalesByProduct_Indexed上建立的叢集索引idx_SalesView了。

這裡有一篇老外的文章詳細介紹了Sqlserver的視圖索引，WITH ( NOEXPAND )語句也是這篇文章提到的，有空可以看看。

 

不要忘記效能測試

索引檢視表如果使用得當，它會十分有用，因為它們能夠顯著地提高查詢的效能。但是，由於叢集索引增加的效能，資料庫引擎必須在視圖基表的所有事務過程 中維持那個索引。因為這個交換，建立一個索引檢視表可能對系統有益，也可能給系統造成傷害。確定這樣做是有益還是有害的最佳方法就是進行全面的效能測試。

 

SQLServer中在視圖上使用索引（轉載）