Indexek memória-optimalizált táblákon

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Minden memóriaoptimalizált táblának rendelkeznie kell legalább egy indexel, mert az indexek kötik össze a sorokat. A memóriaoptimalizált táblákban minden index memóriaoptimalizált is. A memóriaoptimalizált táblák indexei többféleképpen különböznek a lemezalaptáblák hagyományos indexétől:

  • Az adatsorokat nem tárolja a rendszer a lapokon, így nincs lap- vagy terjedelemgyűjtemény, nincs olyan partíció vagy foglalási egység, amely hivatkozható egy táblázat összes lapjának lekéréséhez. Létezik az indexlapok fogalma az egyik elérhető indextípushoz, de a rendszer másként tárolja őket, mint a lemezalapú táblák indexei. Nem gyűjtik össze a hagyományos töredezettség típusát egy lapon belül, így nincs fillfactoruk.
  • A memóriaoptimalizált táblák indexeinek adatmanipuláció során végzett módosításai soha nem lesznek lemezre írva. Csak az adatsorok és az adatok változásai kerülnek a tranzakciónaplóba.
  • A memóriaoptimalizált indexek újraépülnek az adatbázis online állapotba helyezésekor.

A memóriaoptimalizált táblák összes indexe az adatbázis-helyreállítás során az indexdefiníciók alapján jön létre.

Az indexnek a következők egyikének kell lennie:

  • Hash index
  • Memóriaoptimalizált nem klaszteres index (ami egy B-fa alapértelmezett belső struktúráját jelenti)

A hash indexeket részletesebben a Memóriaoptimalizált táblák hash indexei című rész tárgyalja.
A nem klaszterezett indexek részletesebben a memóriaoptimalizált táblák nem klaszterezett indexéről szóló részben vannak tárgyalva.
Az oszlopcentrikus indexeket egy másik cikkben tárgyaljuk.

Memóriaoptimalizált indexek szintaxisa

A memóriaoptimalizált táblákhoz tartozó CREATE TABLE utasításnak tartalmaznia kell egy indexet, akár explicit módon, akár implicit módon, elsődleges kulcson vagy EGYEDI kényszeren keresztül.

Az alapértelmezett beállításként a TARTÓSSÁG = SCHEMA_AND_DATA deklarálásához a memóriaoptimalizált táblának rendelkeznie kell egy elsődleges kulccsal. A következő CREATE TABLE utasítás ELSŐDLEGES KULCS NEM KLASSZTEREZETT záradéka két követelménynek is megfelel:

  • Egy indexet biztosít, amely megfelel egy index minimális követelményének a CREATE TABLE utasításban.

  • Megadja a SCHEMA_AND_DATA záradékhoz szükséges elsődleges kulcsot.

    CREATE TABLE SupportEvent  
(  
    SupportEventId   int NOT NULL  
        PRIMARY KEY NONCLUSTERED,  
    ...  
)  
    WITH (  
        MEMORY_OPTIMIZED = ON,  
        DURABILITY = SCHEMA_AND_DATA);

Megjegyzés:

Az SQL Server 2014 (12.x) és az SQL Server 2016 (13.x) memóriaoptimalizált táblázat- vagy táblatípusonként 8 indexet tartalmaz. Az SQL Server 2017 -től (14.x) és az Azure SQL Database-ben már nincs korlátozva a memóriaoptimalizált táblákra és táblázattípusokra vonatkozó indexek száma.

Kódminta szintaxishoz

Ez az alszakasz egy Transact-SQL kódblokkot tartalmaz, amely bemutatja a szintaxist, amely különböző indexeket hoz létre egy memóriaoptimalizált táblában. A kód a következőket mutatja be:

  1. Memóriaoptimalizált táblázat létrehozása.

  2. Az ALTER TABLE utasításokkal két indexet adhat hozzá.

  3. SZÚRJON be néhány adatsort.

    DROP TABLE IF EXISTS SupportEvent;  
go  

CREATE TABLE SupportEvent  
(  
    SupportEventId   int               not null   identity(1,1)  
    PRIMARY KEY NONCLUSTERED,  

    StartDateTime        datetime2     not null,  
    CustomerName         nvarchar(16)  not null,  
    SupportEngineerName  nvarchar(16)      null,  
    Priority             int               null,  
    Description          nvarchar(64)      null  
)  
    WITH (  
    MEMORY_OPTIMIZED = ON,  
    DURABILITY = SCHEMA_AND_DATA);  
go  

    --------------------  

ALTER TABLE SupportEvent  
    ADD CONSTRAINT constraintUnique_SDT_CN  
    UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);  
go  

ALTER TABLE SupportEvent  
    ADD INDEX idx_hash_SupportEngineerName  
    HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.  
go  

    --------------------  

INSERT INTO SupportEvent  
    (StartDateTime, CustomerName, SupportEngineerName, Priority, Description)  
    VALUES  
    ('2016-02-23 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'     ),  
    ('2016-02-24 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'    ),  
    ('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'      ),  
    ('2016-02-26 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');  
go

Indexkulcsértékek duplikálása

Az indexkulcs ismétlődő értékei csökkenthetik a memóriaoptimalizált táblák teljesítményét. Duplikációk a rendszer számára a belépési láncok a legtöbb index olvasási és írási művelethez való áthaladásához. Ha az ismétlődő bejegyzések lánca meghaladja a 100 bejegyzést, a teljesítménycsökkenés mérhetővé válhat.

Ismétlődő kivonatértékek

Ez a probléma jobban látható a kivonatindexek esetében. A kivonatindexek a következő szempontok miatt szenvednek jobban:

  • A kivonatindexek műveletenkénti alacsonyabb költsége.
  • A nagy ismétlődő láncok interferálása a hash ütközési láncával.

Az indexek duplikációjának csökkentéséhez próbálkozzon a következő módosításokkal:

  • Használjon nem klaszterezett indexet.
  • Adjon hozzá további oszlopokat az indexkulcs végéhez az ismétlődések számának csökkentése érdekében.
    • Hozzáadhat például olyan oszlopokat, amelyek szintén az elsődleges kulcsban találhatók.

A hash összeütközéseiről további információt a Hash indexek memória-optimalizált táblákhoz című témakörben talál.

Példa a fejlesztésre

Íme egy példa arra, hogyan kerülheti el a teljesítménybeli hatékonyságtalanságot az indexben.

Tekintsünk egy olyan táblázatot Customers, amelyen az elsődleges kulcs meg van határozva CustomerId, és az oszlopnak CustomerCategoryID indexe van. Általában sok ügyfél lesz egy adott kategóriában. Így a CustomerCategoryID számos ismétlődő értéket tartalmaz az index adott kulcsán belül.

Ebben a forgatókönyvben az ajánlott eljárás egy nem klaszterezett index használata a (CustomerCategoryID, CustomerId)-on. Ez az index olyan lekérdezésekhez használható, amelyek predikátumot használnak CustomerCategoryID, de az indexkulcs nem tartalmaz duplikációt. Ezért az indexkarbantartás nem okoz hatékonysági problémákat sem a CustomerCategoryID duplikált értékei, sem az index extra oszlopa miatt.

Az alábbi lekérdezés az index CustomerCategoryID duplikált indexkulcs-értékeinek átlagos számát mutatja a Sales.Customers mintaadatbázisban lévő táblában.

SELECT AVG(row_count) FROM
    (SELECT COUNT(*) AS row_count 
	    FROM Sales.Customers
	    GROUP BY CustomerCategoryID) a

A saját táblához és indexhez tartozó indexkulcs-duplikációk átlagos számának kiértékeléséhez cserélje le Sales.Customers a tábla nevét, és cserélje le CustomerCategoryID az indexkulcs oszlopainak listájára.

Az egyes indextípusok használatának összehasonlítása

Az adott lekérdezések természete határozza meg, hogy melyik indextípus a legjobb választás.

Ha memória-optimalizált táblákat implementál egy meglévő alkalmazásban, az általános javaslat az, hogy nem klaszteres indexekkel kezdjünk, mivel képességeik jobban hasonlítanak a lemezalapú táblák hagyományos fürtözött és nem klaszteres indexeinek képességeihez.

Javaslatok nemfürtözött index használatára

A nem klaszteres index előnyösebb egy hash indexnél, ha:

  • A lekérdezések rendelkeznek egy ORDER BY záradékkal az indexelt oszlopban.
  • Olyan lekérdezések, ahol a rendszer csak a többoszlopos index első oszlopait teszteli.
  • A lekérdezések az indexelt oszlopot egy WHERE záradék használatával tesztelik a következőkkel:
    • Egyenlőtlenség: WHERE StatusCode != 'Done'
    • Értéktartomány-vizsgálat: WHERE Quantity >= 100

Az alábbi SELECT-ekben a nemclustered index előnyösebb egy kivonatindexnél:

SELECT CustomerName, Priority, Description 
FROM SupportEvent  
WHERE StartDateTime > DateAdd(day, -7, GetUtcDate());  

SELECT StartDateTime, CustomerName  
FROM SupportEvent  
ORDER BY StartDateTime DESC; -- ASC would cause a scan.

SELECT CustomerName  
FROM SupportEvent  
WHERE StartDateTime = '2016-02-26';

Javaslatok kivonatindex használatára

A kivonatindexeket elsősorban a pontkeresésekhez használják, a tartományvizsgálatokhoz nem.

Ha a lekérdezések egyenlőségi predikátumokat használnak, a kivonatindex előnyösebb a nem klaszterezett indexekkel szemben, amikor a WHERE záradék minden indexkulcsoszlopra leképezhető, ahogy az alábbi példában is látható:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

Többoszlopos index

A többoszlopos index lehet nem klaszteres index vagy hash index. Tegyük fel, hogy az indexoszlopok col1 és col2. Az alábbi SELECT utasítás alapján csak a nemclustered index hasznos lehet a lekérdezésoptimalizáló számára:

SELECT col1, col3  
FROM MyTable_memop  
WHERE col1 = 'dn';

A kivonatindexnek szüksége van a WHERE záradékra, hogy egyenlőségi tesztet adjon meg a kulcs minden oszlopához. Máskülönben a kivonatindex nem hasznos a lekérdezésoptimalizáló számára.

Egyik indextípus sem hasznos, ha a WHERE záradék csak a második oszlopot adja meg az indexkulcsban.

Összegző táblázat az indexhasználati forgatókönyvek összehasonlításához

Az alábbi táblázat felsorolja az összes olyan műveletet, amelyet a különböző indextípusok támogatnak. Az igen azt jelenti, hogy az index hatékonyan tudja kiszolgálni a kérést, a Nem pedig azt, hogy az index nem tudja hatékonyan teljesíteni a kérést.

Operation Memóriaoptimalizált,
kivonat		 Memóriaoptimalizált,
nem csoportosított		 Lemezalapú,
(nem)klaszterezett
Indexvizsgálat, az összes táblasor lekérése. Igen Igen Igen
Az index egyenlőségi predikátumokat keres (=). Igen
(Teljes kulcs szükséges.)		 Igen Igen
Az index az egyenlőtlenségre és a tartományra vonatkozó predikátumokat keresi
(>, <, <=, >=, BETWEEN).		 Nem
(Egy indexszkennelést eredményez.)		 Igen 1 Igen
Az indexdefiníciónak megfelelő rendezési sorrendben kéri le a sorokat. Nem Igen Igen
Az indexdefiníció fordítottjának megfelelő rendezési sorrendben kéri le a sorokat. Nem Nem Igen

1 Memóriaoptimalizált nemclustered index esetén az indexkeresés végrehajtásához nincs szükség a teljes kulcsra.

Automatikus index- és statisztikakezelés

Egy vagy több adatbázis indextöredezettség-mentesítésének és statisztikáinak automatikus kezeléséhez használjon olyan megoldásokat, mint az adaptív indextöredezettség-mentesítés . Ez az eljárás automatikusan eldönti, hogy újraépít-e vagy átrendez egy indexet a töredezettségi szintnek megfelelően, többek között paraméterekkel, és lineáris küszöbértékkel frissíti a statisztikákat.

Lásd még:

AZ SQL Server index tervezési útmutatója
Hashindexek memóriaoptimalizált táblákhoz
Nem klaszteres indexek memóriaoptimalizált táblákhoz
Adaptív indextöredezettség-mentesítés

  • Last updated on