共用方式為

  • 發行項

設計索引檢視

檢視同時也稱為虛擬資料表，因為由檢視所傳回的結果集與包含資料行與資料列的資料表擁有相同的一般形式，而且可以使用與資料表一樣的方式，在 SQL 陳述式中參考檢視。標準檢視的結果集並不會永久儲存在資料庫中。每次當有查詢參考標準檢視時，除非查詢又修改為僅參考基底資料表，否則 SQL Server 就會在內部將檢視的定義代入查詢中。接著，它就會如往常般執行最後產生的查詢。如需詳細資訊，請參閱＜檢視解析＞。

對於標準檢視而言，為每個參考檢視的查詢動態地建立結果集，對於包含大量資料列複雜處理 (例如彙總大量資料或聯結多個資料列) 的檢視而言，這可能會造成很大的負擔。如果查詢中經常引用此類檢視，為檢視建立唯一的叢集索引即可改善效能。在檢視上建立唯一的叢集索引時，結果集會像含叢集索引的資料表一樣儲存在資料庫中。

對檢視建立索引的另一個好處是，即使查詢的 FROM 子句中未直接使用檢視的名稱，但最佳化工具會開始在查詢中使用檢視索引。由於不必記錄就可以從索引檢視中擷取資料，所提升的效能會使現有的查詢獲得好處。如需詳細資訊，請參閱＜解析檢視上的索引＞。

對基底資料表中的資料做修改時，資料修改會反映在索引檢視中儲存的資料。由於要求檢視的叢集索引必須是唯一的，因而改進了 SQL Server 在索引中尋找被任何資料修改影響之資料列的效率。

當查詢與檢視定義都包含下列相符的元素時，查詢最佳化工具在處理查詢時利用索引檢視的能力，將比舊版有更多的改善：

  • 純量運算式。例如，查詢最佳化工具可使用下列在其述詞中具有純量運算式的查詢：

    SELECT ColA, ColB FROM TableT WHERE ColC * (ColD + 10) > 50

    來與下列檢視上建立的索引進行比對：

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC * (ColD + 10) AS ExpCol
FROM dbo.TableT

    包含使用者自訂函數的純量運算式，也可以用類似的方式比對。

  • 純量彙總函數。例如，下面這個在 SELECT 清單中包含純量彙總函數的查詢：

    SELECT COUNT_BIG (*) FROM dbo.TableT

    可與下列檢視上建立的索引進行比對：

    CREATE VIEW V2 WITH SCHEMABINDING AS
SELECT COUNT_BIG (*) AS Cnt 
FROM dbo.TableT

選取查詢計畫時，查詢最佳化工具也會考慮到下列項目：

  • 查詢述詞中所定義之值的間隔，是否落在索引檢視中所定義之值的間隔內。例如，考量在下列檢視上建立的索引：

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC FROM dbo.TableT
WHERE ColA > 1 and ColA < 10

    現在請看下列查詢：

    SELECT ColB, ColC FROM dbo.TableT
WHERE ColA > 3 and ColA < 7

    查詢最佳化工具會將這個查詢與 V1 檢視比對，這是因為查詢中所定義之 3 和 7 之間的間隔，落在索引檢視中所定義之 1 和 10 之間的間隔內。

  • 查詢中所定義之運算式與索引檢視中所定義之運算式的相似度。SQL Server 在嘗試比對運算式時，會考慮運算式的資料行參考、常值、邏輯運算子 AND、OR、NOT、BETWEEN 和 IN，以及比較運算子 =、<>、>、<、>= 和 <=。不考慮算術運算子 (例如 + 和 %) 與參數。

    例如，查詢最佳化工具會比對下列查詢：

    SELECT ColA, ColB from dbo.TableT
WHERE ColA < ColB

    與在此檢視上建立的索引比對：

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB FROM dbo.TableT
WHERE ColB > ColA

請記住，就跟所有索引一樣，只有當查詢最佳化工具判斷這樣做有幫助時，SQL Server 才會選擇在查詢計畫中使用索引檢視。

可以在任何版本的 SQL Server 2008 建立索引檢視。在 SQL Server 2008 Enterprise 中，查詢最佳化工具會自動考量索引檢視表。若要在所有其他版本中使用索引檢視表，就必須使用 NOEXPAND 資料表提示。

設計索引檢視的指導方針

當基本資料不常更新時，索引檢視的效用最大。索引檢視的維護成本可能比資料表索引的維護成本更高。如果基礎資料時常更新，則使用索引檢視所獲得的效能利益可能不足以支付索引檢視資料的維護成本。如果基礎資料經常以批次方式更新，但在更新之間主要會當作唯讀狀態處理，請考慮在更新前先卸除任何索引檢視，等到更新之後再重建索引。這樣做將可改善更新的效能。

索引檢視改善了下列查詢類型的效能：

  • 處理多個資料列的聯結與彙總。

  • 由許多查詢所經常執行的聯結與彙總運算。

    例如，在一個記錄存貨的線上交易處理 (OLTP) 資料庫中，可能預期要讓許多查詢來聯結 ProductMaster、ProductVendor，以及 VendorMaster 資料表。然而執行這種聯結的每一個查詢可能並未處理許多資料列，因此上千個此類查詢中，相當比例的查詢可能需要進行非常多次的整體聯結處理。由於這些關聯性可能不會時常更新，因此整個系統的整體效能便可藉由定義索引檢視來儲存聯結的結果而獲得改善。

  • 決策支援工作負載。

    分析系統的特性在於儲存不常更新的摘要、彙總的資料。額外彙總資料與聯結多個資料列即為許多決策支援查詢的特性。此外，決策支援系統有時候會包含內有多個資料行或大型資料行 (或兩者都有) 的寬資料表。對於參考這些資料行窄子集的查詢而言，使用僅包含查詢中某些資料行的索引檢視，或是僅包含這些資料行窄超集的檢視，將可帶來許多效益。建立包含單一資料表的資料行子集的窄索引檢視，這樣的策略稱為「垂直資料分割」，因為它是以垂直方向分割資料表。例如，考量下列資料表與索引檢視：

    CREATE TABLE wide_tbl(a int PRIMARY KEY, b int, ..., z int)
CREATE VIEW v_abc WITH SCHEMABINDING AS
SELECT a, b, c
FROM dbo.wide_tbl
WHERE a BETWEEN 0 AND 1000
CREATE UNIQUE CLUSTERED INDEX i_abc ON v_abc(a)

    只要使用 v_abc 就可以回應下列查詢：

    SELECT b, count_big(*), SUM(c)
FROM wide_tbl 
WHERE a BETWEEN 0 AND 1000
GROUP BY b

    v_abc 檢視比 wide_tbl 資料表佔據更少的頁面。因此，讓最佳化工具選擇它做為解決上述查詢的存取路徑，是較佳的抉擇。

    若要垂直分割整個資料表而非資料表的子集，建議您在使用 INCLUDE 子句的資料表上使用非叢集索引，加入所要的資料行，而非使用叢集檢視。如需詳細資訊，請參閱＜CREATE INDEX (Transact-SQL)＞。

索引檢視通常並未改善下列查詢類型的效能：

  • 經常寫入的 OLTP 系統。

  • 經常更新的資料庫。

  • 未包含彙總或聯結的查詢。

  • 具有高度基數 GROUP BY 索引鍵的資料彙總。高度維數代表著索引鍵包含許多不同的值。由於每個索引鍵都有不同的值，因此唯一索引鍵所具有的維數程度可以達到可能的最大值。索引檢視藉由降低查詢必須存取的資料列數來改善效能。如果檢視的結果集所具有的資料列數幾乎與基底資料表相同，則使用檢視所獲得的效能利益便不高。例如，考量以下執行於包含 1.000 個資料列的資料表中的查詢：

    SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey

    如果資料表索引鍵的基數是 100，則使用這個查詢的結果所建立的索引檢視就只會包含 100 個資料列。使用檢視的查詢必須讀取的數量平均為讀取基底資料表時所需的十分之一。如果索引鍵是唯一索引鍵，則索引鍵的維數是 1000 且檢視的結果集傳回 1000 個資料列。如果檢視與 ExampleTable 基底資料表具有相同大小的資料列，則讓查詢使用這個索引檢視，跟直接讀取基底資料表比較起來，在效能上並沒有差別。

  • 展開聯結。這些檢視的結果集將比基底資料表中的原始資料更大。

以查詢合併索引檢視

雖然在可建立索引檢視類型的限制讓您無法設計可解決完整問題的檢視，您還是可以設計多個較小的索引檢視來加速各部份的過程。

請考量下列範例：

  • 經常執行的查詢彙總某個資料庫中的資料，彙總另一個資料庫中的資料，然後再聯結 (Join) 結果。由於索引檢視無法參考多個資料庫的資料表，因此您無法設計單一的檢視來進行這整個過程。但是，您可以在每個資料庫中個別建立一個索引檢視，用來進行該資料庫的彙總。如果最佳化工具可以和現有查詢的索引檢視相對應，則彙總處理至少可以不需藉由對現有的查詢重新編碼便能加快速度。雖然聯結處理並未加快，但整體的查詢速度將由於使用了儲存於索引檢視中的彙總而更快。

  • 經常執行的查詢彙總了數個資料表的資料，然後再使用 UNION 來合併結果。在索引檢視中並不允許 UNION。您可以再度設計檢視來執行每個個別的彙總運算。然後最佳化工具便可以選取索引檢視以便加快查詢速度，而不需對查詢重新編碼。雖然 UNION 處理並未改善，然而個別的彙總過程卻已獲得改善。

設計可以滿足多重運算的索引檢視。由於最佳化工具即使並未指定於 FROM 子句中也可以使用索引檢視，因此設計良好的索引檢視可以加快許多查詢的處理速度。

例如，請考量在以下的檢視上建立索引：

CREATE VIEW ExampleView WITH SCHEMABINDING
AS
SELECT GroupKey, SUM(Colx) AS SumColx, COUNT_BIG(Colx) AS CountColx
FROM MyTable
GROUP BY GroupKey

這個檢視不僅可以滿足直接參考檢視資料行的查詢，同時也可以用來滿足查詢基底資料表並包含如 SUM(Colx)、COUNT_BIG(Colx)、COUNT(Colx)，以及 AVG(Colx) 等運算式的查詢。所有這類查詢將會更快，因為它們只要擷取檢視中的少數資料列即可，而不需從基底資料表讀取所有的資料列。

同樣地，依日期來彙總資料與群組的索引檢視，也可以用來滿足彙總超過一天的多個不同範圍的查詢，例如 7 天、30 天或 90 天。