建立篩選的索引

適用于:SQL ServerAzure SQL資料庫Azure SQL 受控執行個體Azure SynapseAnalytics AnalyticsPlatform System (PDW)

本文說明如何使用 SQL Server Management Studio (SSMS) 或 Transact-SQL 來建立篩選的索引。 篩選的索引是優化的磁片資料列存放區非叢集索引,特別適合涵蓋從定義完善的資料子集選取的查詢。 篩選索引會使用篩選述詞對資料表中的部分資料列進行索引。 設計良好的篩選索引可以改善查詢效能,並減少與完整資料表索引相較之下的索引維護和儲存體成本。

篩選索引可以提供全資料表索引所不及的下列優勢:

  1. 改善查詢效能和計畫品質。

    設計良好的篩選索引可以提升查詢效能和執行計畫品質,因為它比全資料表的非叢集索引來得小,且具有篩選統計資料。 篩選統計資料比全資料表統計資料更為正確,因為僅涵蓋篩選索引中的資料列。

  2. 降低的索引維護成本。

    只有在資料操作語言 (DML) 陳述式影響到索引中的資料時,才會對索引進行維護。 與完整資料表的非叢集索引相較,篩選索引可以降低維護成本,因為後者較小且僅會在索引中的資料已變更時才會進行維護。 篩選索引的數量可能很多,特別是當其包含不常變更的資料時。 同樣地,如果篩選索引僅包含經常修改的資料,則因為索引的大小較小,更新統計資料的成本就會下降。

  3. 降低索引儲存體成本。

    若不需要完整資料表索引,建立篩選的索引可減少非叢集索引的磁片儲存體。 您可以使用多個篩選索引來取代全資料表的非叢集索引,而不會大幅增加儲存需求。

設計考量

當資料行只有一些相關的查詢值時,您可以在值的子集上建立篩選索引。 所產生的索引比在相同的索引鍵資料行上定義的全資料表非叢集索引還小,維護成本也比較低。

例如,請考慮下列資料案例中的篩選索引。 在每個案例中 WHERE ,篩選索引的 子句應該是查詢子句的 WHERE 子集,以受益于篩選的索引。

  • 當資料行中的值大部分都是 Null,而且查詢只會從非 Null 值中選取。 您可以為非 Null 資料列建立篩選索引。
  • 當資料表中的資料列被週期性工作流程或佇列進程標示為處理時。 經過一段時間後,資料表中的大部分資料列都會標示為已處理。 尚未處理之資料列的篩選索引會讓尋找尚未處理之資料列的週期性查詢受益。
  • 當資料表具有異質資料列時。 您可以為一或多個資料類別建立篩選索引。 這會將查詢焦點縮小為資料表的特定區域,改善這些資料列的查詢效能。 同樣地,所產生的索引比完整資料表非叢集索引還小,維護成本也比較低。

限制事項

  • 您無法在檢視上建立篩選的索引。 不過,如果在檢視中參考的資料表上定義篩選索引,則可為查詢最佳化工具提供多項優點。 如果查詢結果會是正確的,則查詢最佳化工具會針對從檢視進行選取的查詢考慮篩選索引。

  • 當篩選運算式中存取的資料行是 CLR 資料類型時,您無法在資料表上建立篩選索引。

  • 篩選索引具有索引檢視表所不及的下列優勢:

    • 降低的索引維護成本。 例如,相較於索引檢視表而言,查詢處理器會使用較少的 CPU 資源來更新篩選索引。

    • 改善的計畫品質。 例如,在查詢編譯期間,查詢最佳化工具考慮使用篩選索引的情況會比對等的索引檢視表更多。

    • 線上索引重建。 您可以在篩選索引可用於查詢時,重建篩選索引。 索引檢視表不支援線上索引重建。 如需詳細資訊,請參閱 REBUILDALTER INDEX (Transact-SQL) 選項。

    • 非唯一索引。 篩選索引可以是非唯一的,而索引檢視表則必須是唯一的。

  • 篩選索引定義於單一資料表,且僅支援簡單比較運算子。 如果需要參考多個資料表或具有複雜邏輯的篩選運算式,則應該建立檢視。 篩選的索引不支援 LIKE 運算子。

  • 如果篩選的索引運算式相當於查詢述詞,且查詢不會傳回篩選索引運算式中的資料行與查詢結果,則篩選索引運算式中的資料行不需要是篩選索引定義中的索引鍵或內含資料行。

  • 如果查詢述詞在與篩選索引運算式不相等的比較中使用資料行,篩選索引運算式中的資料行應該是篩選索引定義中的索引鍵或內含資料行。

  • 如果篩選索引運算式中的資料行在查詢結果集中,則該資料行應該是篩選索引定義中的索引鍵或內含資料行。

  • 資料表的叢集索引鍵不需要是篩選索引定義中的索引鍵或內含資料行。 叢集索引鍵會自動包含在所有非叢集的索引中 (包含篩選索引在內)。 如需詳細資訊,請參閱 索引架構和設計指南

  • 如果在篩選索引的篩選索引運算式中指定的比較運算子產生隱含或明確的資料轉換,則如果該轉換是發生在比較運算子的左側,就會發生錯誤。 解決方案是在比較運算子右側,使用資料轉換運算子 (CASTCONVERT) 來寫入篩選的索引運算式。

  • 檢閱CREATE INDEX (Transact-SQL) 語法中篩選索引建立的必要 SET 選項

  • 篩選無法套用至主鍵或唯一條件約束,但可以套用至具有 屬性的 UNIQUE 索引。

  • 您無法在計算資料行上建立篩選的索引。

權限

需要資料表或檢視表的 ALTER 權限。 使用者必須是系統管理員固定伺服器角色的成員或db_ddladmin和db_owner固定資料庫角色的成員。 若要修改篩選的索引運算式,請使用 CREATE INDEX WITH DROP_EXISTING

使用 SSMS 建立篩選的索引

  1. 在 [物件總管] 中,選取加號展開包含您要建立篩選索引之資料表的資料庫。

  2. 選取加號展開 [ 資料表] 資料夾。

  3. 選取加號,展開您要在其中建立篩選索引的資料表。

  4. 以滑鼠右鍵按一下 [索引] 資料夾,指向 [新增索引],然後選取 [非叢集索引…]。

  5. [新增索引] 對話方塊,於 [一般] 頁面上的 [索引名稱] 方塊中輸入新索引的名稱。

  6. [索引鍵資料行] 下,選取 [ 新增...]。

  7. 在 [ table_name 選取資料行] 對話方塊中,選取要新增至索引之資料表資料行的核取方塊或核取方塊。

  8. 選取 [確定]。

  9. 在 [篩選] 頁面的 [篩選運算式]底下,輸入要用來建立篩選索引的 SQL 運算式。

  10. 選取 [確定]。

使用 Transact-SQL 建立篩選的索引

此範例使用 AdventureWorks2019 可在 AdventureWorks 範例資料庫下載的資料庫

  1. 在物件總管中,連線到資料庫引擎的執行個體。

  2. 在標準列上,選取 [新增查詢] 。

  3. 複製下列範例並將其貼到查詢視窗中,然後選取 [執行]。

USE AdventureWorks2019;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

篩選索引 FIBillOfMaterialsWithEndDate 對下列查詢有效。 您可以顯示查詢執行計畫 ,以判斷查詢最佳化工具是否使用篩選的索引。

USE AdventureWorks2019;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

下一步

若要深入瞭解如何建立索引和相關概念,請參閱下列文章: