Dela via

Skapa filtrerade index

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Den här artikeln beskriver hur du skapar ett filtrerat index med SQL Server Management Studio (SSMS) eller Transact-SQL. Ett filtrerat index är ett optimerat diskbaserat icke-klustrat radlagringsindex som passar särskilt bra för frågor som hämtar från en väldefinierad delmängd av data. Den använder ett filterpredikat för att indexera en del rader i tabellen. Ett väldesignat filtrerat index kan förbättra frågeprestanda och minska kostnaderna för indexunderhåll och lagring jämfört med fulltabellindex.

Filtrerade index kan ge följande fördelar jämfört med fullständiga tabellindex:

  1. Förbättrad frågeprestanda och plankvalitet.

    Ett väldesignat filtrerat index förbättrar frågeprestandan och körningsplanens kvalitet eftersom det är mindre än ett icke-grupperat index med fullständiga tabeller och har filtrerad statistik. Den filtrerade statistiken är mer exakt än fulltabellstatistik eftersom de endast täcker raderna i det filtrerade indexet.

  2. Minskade kostnader för indexunderhåll.

    Ett index underhålls endast när DML-instruktioner (Data Manipulation Language) påverkar data i indexet. Ett filtrerat index minskar kostnaderna för indexunderhåll jämfört med ett icke-grupperat index med fullständiga tabeller eftersom det är mindre och underhålls endast när data i indexet ändras. Det är möjligt att ha ett stort antal filtrerade index, särskilt när de innehåller data som ändras sällan. På samma sätt, om ett filtrerat index endast innehåller de ofta ändrade data, minskar indexets mindre storlek kostnaden för att uppdatera statistiken.

  3. Minskade kostnader för indexlagring.

    Om du skapar ett filtrerat index kan du minska disklagringen för icke-klustrade index när ett fullständigt tabellindex inte behövs. Du kan ersätta ett icke-grupperat index med en fullständig tabell med flera filtrerade index utan att avsevärt öka lagringskraven.

Designöverväganden

När en kolumn bara har några relevanta värden för frågor kan du skapa ett filtrerat index för delmängden av värden. Det resulterande indexet blir mindre och kostar mindre att underhålla än ett icke-grupperat index med fullständiga tabeller som definierats på samma nyckelkolumner.

Tänk dig till exempel ett filtrerat index i följande datascenarier. I varje fall bör WHERE-satsen i frågan vara en delmängd av WHERE-satsen i det filtrerade indexet för att dra nytta av det filtrerade indexet.

  • När värdena i en kolumn mestadels är NULL och frågan endast väljer från icke-NULL-värden. Du kan skapa ett filtrerat index för icke-NULL-datarader.
  • När rader i en tabell markeras som bearbetade av ett återkommande arbetsflöde eller en köprocess. Med tiden markeras de flesta rader i tabellen som bearbetade. Ett filtrerat index på rader som ännu inte bearbetats skulle gynna den återkommande frågan som söker efter rader som ännu inte har bearbetats.
  • När en tabell har heterogena datarader. Du kan skapa ett filtrerat index för en eller flera datakategorier. Detta kan förbättra prestandan för frågor på dessa datarader genom att begränsa fokus för en fråga till ett visst område i tabellen. Återigen blir det resulterande indexet mindre och kostar mindre att underhålla än ett icke-grupperat index med fullständiga tabeller.

Begränsningar och restriktioner

  • Du kan inte skapa ett filtrerat index i en vy. Frågeoptimeraren kan dock dra nytta av ett filtrerat index som definierats i en tabell som refereras till i en vy. Frågeoptimeraren överväger ett filtrerat index för en fråga som väljer från en vy om frågeresultatet blir korrekt.

  • Du kan inte skapa ett filtrerat index i en tabell när kolumnen som används i filteruttrycket är av en CLR-datatyp.

  • Filtrerade index har följande fördelar jämfört med indexerade vyer:

    • Minskade kostnader för indexunderhåll. Frågeprocessorn använder till exempel färre CPU-resurser för att uppdatera ett filtrerat index än en indexerad vy.

    • Förbättrad plankvalitet. Under frågekompilering överväger till exempel frågeoptimeraren att använda ett filtrerat index i fler situationer än motsvarande indexerade vy.

    • Onlineindex byggs om. Du kan återskapa filtrerade index när de är tillgängliga för frågor. Online ombyggnad av index stöds inte för indexerade vyer. Mer information finns i alternativet REBUILD för ALTER INDEX (Transact-SQL).

    • Icke-unika index. Filtrerade index kan vara icke-unika, medan indexerade vyer måste vara unika.

  • Filtrerade index definieras i en tabell och stöder bara enkla jämförelseoperatorer. Om du behöver ett filteruttryck som refererar till flera tabeller eller har komplex logik bör du skapa en vy. Filtrerade index stöder inte LIKE operatorer.

  • En kolumn i det filtrerade indexuttrycket behöver inte vara en nyckel eller inkluderad kolumn i den filtrerade indexdefinitionen om det filtrerade indexuttrycket motsvarar frågepredikatet och frågan inte returnerar kolumnen i det filtrerade indexuttrycket med frågeresultatet.

  • En kolumn i det filtrerade indexuttrycket ska vara en nyckel eller inkluderad kolumn i den filtrerade indexdefinitionen om frågepredikatet använder kolumnen i en jämförelse som inte motsvarar det filtrerade indexuttrycket.

  • En kolumn i det filtrerade indexuttrycket ska vara en nyckel eller inkluderad kolumn i den filtrerade indexdefinitionen om kolumnen finns i frågeresultatuppsättningen.

  • Den klustrade indexnyckeln i tabellen behöver inte vara en nyckel eller inkluderad kolumn i den filtrerade indexdefinitionen. Den klustrade indexnyckeln inkluderas automatiskt i alla icke-grupperade index, inklusive filtrerade index. Läs mer i -indexarkitekturen och designguiden.

  • Om jämförelseoperatorn som anges i det filtrerade indexuttrycket för det filtrerade indexet resulterar i en implicit eller explicit datakonvertering uppstår ett fel om konverteringen sker till vänster om en jämförelseoperator. En lösning är att skriva det filtrerade indexuttrycket med datakonverteringsoperatorn (CAST eller CONVERT) till höger om jämförelseoperatorn.

  • Granska de SET alternativ som krävs för att skapa filtrerade index i CREATE INDEX (Transact-SQL) syntax

  • Filter kan inte tillämpas på primärnyckel eller unika begränsningar, men kan tillämpas på index med egenskapen UNIQUE.

  • Du kan inte skapa ett filtrerat index i en beräknad kolumn.

Behörigheter

Kräver behörighet för ALTER på tabellen eller vyn. Användaren måste vara medlem i sysadmin fast serverroll eller db_ddladmin och db_owner fasta databasroller. Om du vill ändra det filtrerade indexuttrycket använder du CREATE INDEX WITH DROP_EXISTING.

Skapa ett filtrerat index med SSMS

  1. I Object Explorer väljer du plustecknet för att expandera databasen som innehåller tabellen där du vill skapa ett filtrerat index.

  2. Välj plustecknet för att expandera mappen Tables.

  3. Välj plustecknet för att expandera tabellen där du vill skapa ett filtrerat index.

  4. Högerklicka på mappen Index, peka på Nytt indexoch välj icke-klustrat index....

  5. I dialogrutan Nytt index på sidan Allmänt anger du namnet på det nya indexet i rutan Indexnamn.

  6. Under indexnyckelkolumnerväljer du Lägg till....

  7. I dialogrutan Välj kolumner fråntable_name markerar du kryssrutan eller kryssrutorna i tabellkolumnen eller kolumnerna som ska läggas till i indexet.

  8. Välj OK.

  9. På sidan Filter, under Filteruttryck, anger du SQL-uttryck som du ska använda för att skapa det filtrerade indexet.

  10. Välj OK.

Skapa ett filtrerat index med Transact-SQL

Kodexemplen i den här artikeln använder AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.

  1. I Object Exploreransluter du till en instans av databasmotorn.

  2. I standardfältet väljer du Ny fråga.

  3. Kopiera och klistra in följande exempel i frågefönstret och välj Kör.

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

Det filtrerade indexet FIBillOfMaterialsWithEndDate är giltigt för följande fråga. Du kan visa frågekörningsplanen för att avgöra om frågeoptimeraren använde det filtrerade indexet.

USE AdventureWorks2022;
GO

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

Nästa steg

Mer information om hur du skapar index och relaterade begrepp finns i följande artiklar: