Megosztás a következőn keresztül:

Szűrt indexek létrehozása

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példánySQL-adatbázis a Microsoft Fabricben

Ez a cikk azt ismerteti, hogyan hozhat létre szűrt indexet az SQL Server Management Studio (SSMS) vagy a Transact-SQL használatával. A szűrt index egy optimalizált, lemezalapú, nem klaszterezett sortároló index, amely különösen alkalmas a jól definiált adatrészhalmazokból kiválasztott lekérdezések lefedésére. Szűrő predikátumot használ a tábla sorainak egy részének indexeléséhez. A jól megtervezett szűrt indexek javíthatják a lekérdezési teljesítményt, és csökkenthetik az indexkarbantartási és tárolási költségeket a teljes táblázatos indexekhez képest.

A szűrt indexek a következő előnyöket biztosítják a teljes táblázatos indexekhez képest:

  1. Továbbfejlesztett lekérdezési teljesítmény és tervminőség.

    A jól megtervezett szűrt index javítja a lekérdezési teljesítményt és a végrehajtási terv minőségét, mivel kisebb, mint egy teljes táblás nemclustered index, és szűrt statisztikákat tartalmaz. A szűrt statisztikák pontosabbak, mint a teljes táblázatos statisztikák, mivel csak a szűrt indexben lévő sorokat fedik le.

  2. Alacsonyabb indexkarbantartási költségek.

    Az indexek csak akkor maradnak fenn, ha az adatmanipulációs nyelv (DML) utasításai hatással vannak az indexben lévő adatokra. A szűrt indexek csökkentik az indexkarbantartás költségeit a teljes táblás, nemclustered indexekhez képest, mivel kisebbek, és csak akkor maradnak fenn, ha az index adatai módosulnak. Nagy számú szűrt index is lehet, különösen akkor, ha ritkán változó adatokat tartalmaznak. Hasonlóképpen, ha egy szűrt index csak a gyakran módosított adatokat tartalmazza, az index kisebb mérete csökkenti a statisztikák frissítésének költségeit.

  3. Alacsonyabb indextárolási költségek.

    A szűrt index létrehozása csökkentheti a nem fürtözött indexek lemeztárolását, ha nincs szükség teljes táblás indexre. A teljes táblás nemclustered indexeket több szűrt indexre cserélheti anélkül, hogy jelentősen növelte a tárolási követelményeket.

Tervezési szempontok

Ha egy oszlop csak néhány releváns értékkel rendelkezik a lekérdezésekhez, létrehozhat egy szűrt indexet az értékek részhalmazán. Az eredményként kapott index kisebb lesz, és kevesebb költséggel tartható fenn, mint az ugyanazon kulcsoszlopokon definiált teljes táblás nemclustered index.

Fontolja meg például a szűrt indexet a következő adatforgatókönyvekben. A lekérdezés WHERE záradékának minden esetben a szűrt index WHERE záradékának egy részhalmazának kell lennie, hogy kihasználhassa a szűrt index előnyeit.

  • Ha egy oszlop értékei többnyire NULL , és a lekérdezés csak a nem NULL értékek közül választ. Szűrt indexet hozhat létre a nem NULL adatsorokhoz.
  • Amikor egy táblázat sorai meg vannak jelölve feldolgozottnak egy ismétlődő munkafolyamat vagy üzenetsor-folyamat által. Idővel a tábla legtöbb sora feldolgozottként lesz megjelölve. A még nem feldolgozott sorok szűrt indexe előnyös lenne az ismétlődő lekérdezésnek, amely még nem feldolgozott sorokat keres.
  • Ha egy tábla heterogén adatsorokat tartalmaz. Egy vagy több adatkategóriához létrehozhat szűrt indexet. Ez javíthatja a lekérdezések teljesítményét ezeken az adatsorokon azáltal, hogy a lekérdezés fókuszát a tábla egy adott területére szűkíti. Az eredményként kapott index ismét kisebb lesz, és kevesebbe kerül fenntartani, mint egy teljes táblás, nemclustered index.

Limitations

  • A nézetben nem hozható létre szűrt index. A lekérdezésoptimalizáló azonban kihasználhatja a nézetben hivatkozott táblában definiált szűrt indexet. A lekérdezésoptimalizáló egy olyan lekérdezés szűrt indexét veszi figyelembe, amely egy nézetből választ, ha a lekérdezés eredményei helyesek lesznek.

  • Nem hozható létre szűrt index egy táblán, ha a szűrőkifejezésben elért oszlop CLR-adattípusú.

  • A szűrt indexek az alábbi előnyökkel rendelkeznek az indexelt nézetekkel szemben:

    • Alacsonyabb indexkarbantartási költségek. A lekérdezésfeldolgozó például kevesebb CPU-erőforrást használ a szűrt indexek frissítéséhez, mint egy indexelt nézet.

    • Továbbfejlesztett tervminőség. A lekérdezés-fordítás során például a lekérdezésoptimalizáló a szűrt indexet több helyzetben is figyelembe veszi, mint az egyenértékű indexelt nézetet.

    • Az online index újraépítése. A szűrt indexeket újraépítheti, amíg elérhetők a lekérdezésekhez. Az online indexek újraépítése nem támogatott az indexelt nézetek esetén. A további információért tekintse meg a REBUILD lehetőséget a ALTER INDEX (Transact-SQL)esetén.

    • Nem egyedi indexek. A szűrt indexek lehetnek nem egyediek, míg az indexelt nézeteknek egyedinek kell lenniük.

  • A szűrt indexek egy táblában vannak definiálva, és csak az egyszerű összehasonlító operátorokat támogatják. Ha olyan szűrőkifejezésre van szüksége, amely több táblára hivatkozik, vagy összetett logikával rendelkezik, akkor létre kell hoznia egy nézetet. A szűrt indexek nem támogatják LIKE operátorokat.

  • A szűrt indexkifejezés egyik oszlopának nem kell kulcsnak vagy oszlopnak lennie a szűrt indexdefinícióban, ha a szűrt indexkifejezés egyenértékű a lekérdezési predikátummal, és a lekérdezés nem adja vissza a szűrt indexkifejezés oszlopát a lekérdezés eredményeivel.

  • A szűrt indexkifejezés egyik oszlopának kulcsnak vagy oszlopnak kell lennie a szűrt index definíciójában, ha a lekérdezési predikátum az oszlopot olyan összehasonlításban használja, amely nem egyezik meg a szűrt indexkifejezéssel.

  • A szűrt indexkifejezés egyik oszlopának kulcsnak vagy oszlopnak kell lennie a szűrt index definíciójában, ha az oszlop szerepel a lekérdezés eredményhalmazában.

  • A tábla fürtözött indexkulcsának nem kell kulcsnak vagy oszlopnak lennie a szűrt indexdefinícióban. A klaszterezett indexkulcs automatikusan bekerül az összes nem-klaszterezett indexbe, beleértve a szűrt indexeket is. További információ az indexarchitektúrára és tervezéséhez készült útmutatóban.

  • Ha a szűrt index szűrt indexkifejezésében megadott összehasonlító operátor implicit vagy explicit adatkonvertálást eredményez, hiba történik, ha az átalakítás az összehasonlító operátor bal oldalán történik. A megoldás a szűrt indexkifejezés írása az összehasonlító operátor jobb oldalán lévő adatkonvertálási operátorral (CAST vagy CONVERT).

  • Tekintse át a szűrt indexek létrehozásához szükséges SET beállításokat CREATE INDEX (Transact-SQL) szintaxisban

  • A szűrők nem alkalmazhatók elsődleges kulcsra vagy egyedi korlátozásokra, de a UNIQUE tulajdonsággal rendelkező indexekre is alkalmazhatók.

  • Szűrt indexet hozhat létre, és hivatkozhat egy számított oszlopra a kulcsban vagy benne, de a szűrődefinícióban nem hivatkozhat számított oszlopra.

Permissions

ALTER engedély szükséges a táblához vagy a nézethez. A felhasználónak tagja kell lennie a sysadmin rögzített kiszolgálói szerepkörnek, illetve a rögzített adatbázis-szerepkörök db_ddladmin és db_owner. A szűrt indexkifejezés módosításához vegye igénybe a CREATE INDEX WITH DROP_EXISTING.

Szűrt index létrehozása SSMS-sel

  1. Az Object Explorerben válassza a pluszjelet annak az adatbázisnak a kibontásához, amely azt a táblát tartalmazza, amelyen szűrt indexet szeretne létrehozni.

  2. A pluszjel kiválasztásával bontsa ki a Táblák mappát.

  3. Válassza ki a pluszjelet annak a táblának a kibontásához, amelyen szűrt indexet szeretne létrehozni.

  4. Kattintson a jobb gombbal az Indexek mappára, mutasson Új Indexelemre, és válassza Nem Klaszterezett Index...lehetőséget.

  5. Az Új index párbeszédpanelen, az Általános lapon adja meg az index nevét az Index neve mezőben.

  6. A(z) Indexkulcs oszlopokalatt válassza a Hozzáadás....

  7. Az Oszlopok kijelölésetable_name párbeszédpanelen jelölje be az indexhez hozzáadni kívánt táblázatoszlop vagy oszlopok jelölőnégyzetét vagy jelölőnégyzeteit.

  8. Kattintson az OK gombra.

  9. A Szűrő lap Szűrőkifejezésterületén adja meg a szűrt index létrehozásához használni kívánt SQL-kifejezést.

  10. Kattintson az OK gombra.

Szűrt index létrehozása Transact-SQL

A cikkben szereplő kódminták a AdventureWorks2025 vagy AdventureWorksDW2025 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

  1. Az Object Explorer-ben csatlakozzon az adatbázismotor egy példányához.

  2. A Standard sávon válassza Új lekérdezéslehetőséget.

  3. Másolja és illessze be a következő példát a lekérdezési ablakba, és válassza a Végrehajtáslehetőséget.

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

A szűrt index FIBillOfMaterialsWithEndDate a következő lekérdezésre érvényes. Tényleges végrehajtási terv megjelenítése annak megállapításához, hogy a lekérdezésoptimalizáló használta-e a szűrt 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

Kapcsolódó tartalom

  • Last updated on