Vytvoření filtrovaných indexů

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Tento článek popisuje, jak vytvořit filtrovaný index pomocí aplikace SQL Server Management Studio (SSMS) nebo Transact-SQL. Filtrovaný index je optimalizovaný neclusterovaný index úložiště řádků založený na disku, a to zejména pro pokrytí dotazů, které vybírají z dobře definované podmnožiny dat. Používá predikát filtru k indexování části řádků v tabulce. Dobře navržený filtrovaný index může zlepšit výkon dotazů a snížit náklady na údržbu indexů a úložiště v porovnání s indexy full-table.

Filtrované indexy můžou poskytovat následující výhody oproti indexům full-table:

1. Lepší výkon dotazů a kvalita plánu

   Dobře navržený filtrovaný index zlepšuje výkon dotazů a kvalitu plánu provádění, protože je menší než neclusterovaný index celé tabulky a má filtrované statistiky. Filtrované statistiky jsou přesnější než statistiky celé tabulky, protože pokrývají pouze řádky ve filtrovaném indexu.

2. Snížení nákladů na údržbu indexů

   Index je zachován pouze v případech, kdy příkazy jazyka DML (Data Manipulat Language) ovlivňují data v indexu. Filtrovaný index snižuje náklady na údržbu indexů v porovnání s neclusterovaným indexem celé tabulky, protože je menší a udržuje se pouze při změně dat v indexu. Je možné mít velký počet filtrovaných indexů, zejména pokud obsahují data, která se mění zřídka. Podobně platí, že pokud filtrovaný index obsahuje pouze často upravená data, menší velikost indexu snižuje náklady na aktualizaci statistiky.

3. Snížení nákladů na úložiště indexů

   Vytvoření filtrovaného indexu může snížit diskové úložiště pro neclusterované indexy, pokud není nutný úplný index tabulky. Neclusterovaný index celé tabulky můžete nahradit několika filtrovanými indexy, aniž byste výrazně zvýšili požadavky na úložiště.

Aspekty návrhu

Pokud sloupec obsahuje jenom několik relevantních hodnot pro dotazy, můžete pro podmnožinu hodnot vytvořit filtrovaný index. Výsledný index bude menší a bude méně nákladný, aby se zachoval než neclusterovaný index celé tabulky definovaný ve stejných klíčových sloupcích.

Představte si například filtrovaný index v následujících datových scénářích. V každém případě by klauzule WHERE dotazu měla být podmnožinou WHERE klauzule filtrovaného indexu, aby bylo možné využít filtrovaný index.

• Pokud jsou hodnoty ve sloupci většinou NULL a dotaz vybere pouze z hodnot, které nejsou null. Můžete vytvořit filtrovaný index pro řádky dat, které nejsou null.
• Pokud jsou řádky v tabulce označené jako zpracovávané opakovaným pracovním postupem nebo procesem fronty. V průběhu času se většina řádků v tabulce označí jako zpracované. Filtrovaný index na řádcích, které ještě nejsou zpracovány, by byl přínosem opakovaného dotazu, který hledá řádky, které ještě nejsou zpracovány.
• Pokud tabulka obsahuje heterogenní řádky dat. Můžete vytvořit filtrovaný index pro jednu nebo více kategorií dat. To může zlepšit výkon dotazů na tyto řádky dat tím, že zpřesní fokus dotazu na konkrétní oblast tabulky. Výsledný index bude opět menší a bude méně nákladný, aby se zachoval než neclusterovaný index celé tabulky.

Omezení

• V zobrazení nelze vytvořit filtrovaný index. Optimalizátor dotazů ale může těžit z filtrovaného indexu definovaného v tabulce, na kterou se odkazuje v zobrazení. Optimalizátor dotazů zváží použití filtrovaného indexu pro dotaz, který vybírá z pohledu, za předpokladu že výsledky dotazu budou správné.

• Filtrovaný index v tabulce nelze vytvořit, pokud je sloupec, ke kterému se přistupuje ve výrazu filtru, datového typu CLR.

• Filtrované indexy mají oproti indexovaným zobrazením následující výhody:

  • Snížení nákladů na údržbu indexů Procesor dotazů například používá k aktualizaci filtrovaného indexu méně prostředků procesoru než indexované zobrazení.

  • Vylepšili jsme kvalitu plánu. Například při kompilaci dotazu zvažuje optimalizátor dotazů použití filtrovaného indexu v více situacích než ekvivalentní indexované zobrazení.

  • Znovu sestavení online indexu. Filtrované indexy můžete znovu sestavit, i když jsou k dispozici pro dotazy. Opětovné sestavení online indexu není u indexovaných zobrazení podporováno. Pro více informací se podívejte na možnost REBUILD pro ALTER INDEX (Transact-SQL).

  • Neunikátní indexy. Filtrované indexy můžou být ne jedinečné, zatímco indexovaná zobrazení musí být jedinečná.

• Filtrované indexy jsou definovány v jedné tabulce a podporují pouze jednoduché relační operátory. Pokud potřebujete výraz filtru, který odkazuje na více tabulek nebo má složitou logiku, měli byste vytvořit zobrazení. Filtrované indexy nepodporují operátory LIKE.

• Sloupec ve výrazu filtrovaného indexu nemusí být klíčem ani zahrnutým sloupcem v definici filtrovaného indexu, pokud výraz filtrovaného indexu odpovídá predikátu dotazu a dotaz nevrací sloupec výrazu filtrovaného indexu ve výsledcích dotazu.

• Sloupec ve filtrovaném výrazu indexu by měl být klíčem nebo zahrnutým sloupcem v definici filtrovaného indexu, pokud predikát dotazu používá sloupec v porovnávání, které není ekvivalentní filtrovanému výrazu indexu.

• Sloupec ve filtrovaném výrazu indexu by měl být klíčovým nebo zahrnutým sloupcem v definici filtrovaného indexu, pokud se sloupec nachází v sadě výsledků dotazu.

• Clusterovaný indexový klíč tabulky nemusí být klíčem ani zahrnutým sloupcem v definici filtrovaného indexu. Clusterovaný indexový klíč se automaticky zahrne do všech neclusterovaných indexů, včetně filtrovaných indexů. Další informace najdete v průvodci návrhem a architektuře indexu .

• Pokud výsledkem relačního operátoru zadaného ve filtrovaném indexovém výrazu filtrovaného indexu je implicitní nebo explicitní převod dat, dojde k chybě, pokud dojde k převodu na levé straně relačního operátoru. Řešením je napsat filtrovaný indexový výraz s operátorem převodu dat (CAST nebo CONVERT) na pravé straně relačního operátoru.

• Projděte si požadované možnosti SET pro vytváření filtrovného indexu v syntaxi CREATE INDEX (Transact-SQL).

• Filtry nelze použít u primárního klíče nebo jedinečných omezení, ale lze je použít u indexů s vlastností UNIQUE.

• Ve počítaném sloupci nemůžete vytvořit filtrovaný index.

Dovolení

Vyžaduje oprávnění ALTER v tabulce nebo zobrazení. Uživatel musí být členem pevné role správce systému nebo pevné role databázových práv db_ddladmin a db_owner. Chcete-li upravit filtrovaný výraz indexu, použijte CREATE INDEX WITH DROP_EXISTING.

Vytvoření filtrovaného indexu pomocí SSMS

1. V Průzkumníku objektů vyberte znaménko plus a rozbalte databázi obsahující tabulku, na které chcete vytvořit filtrovaný index.

2. Vyberte znaménko plus a rozbalte složku Tabulky.

3. Výběrem znaménka plus rozbalte tabulku, na které chcete vytvořit filtrovaný index.

4. Klikněte pravým tlačítkem na složku Indexy, přejděte na Nový indexa vyberte Neskupený index....

5. V dialogovém okně Nový index zadejte na stránce Obecné název nového indexu do pole Název indexu.

6. V části Sloupce klíče indexuvyberte Přidat....

7. V dialogovém okně Vybrat sloupce z zaškrtněte políčko nebo políčka sloupců tabulky, které chcete přidat do indexu.

8. Vyberte OK.

9. Na stránce Filtr v části Výraz filtruzadejte výraz SQL, který použijete k vytvoření filtrovaného indexu.

10. Vyberte OK.

Vytvoření filtrovaného indexu pomocí Transact-SQL

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2022 nebo AdventureWorksDW2022, kterou si můžete stáhnout z domovské stránky ukázky a komunitní projekty Microsoft SQL Serveru.

1. Připojte se k instanci databázového stroje v Průzkumníku objektů.

2. Na panelu Standard vyberte Nový dotaz.

3. Zkopírujte a vložte následující příklad do okna dotazu a vyberte Spustit.

USE AdventureWorks2022;
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

Filtrovaný index FIBillOfMaterialsWithEndDate je platný pro následující dotaz. Zobrazí skutečný plán provádění , který určí, jestli optimalizátor dotazů použil filtrovaný index.

USE AdventureWorks2022;
GO

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

Související obsah

• VYTVOŘIT INDEX (Transact-SQL)
• Průvodce architekturou a návrhem indexů pro SQL Server a Azure SQL
• Zobrazení skutečného plánu provádění
• Indexy