Indexen voor Memory-Optimized tabellen

Artikel
01/15/2025

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

Alle tabellen die zijn geoptimaliseerd voor geheugen, moeten ten minste één index hebben, omdat dit de indexen zijn die de rijen met elkaar verbinden. In een tabel die is geoptimaliseerd voor geheugen, is elke index ook geoptimaliseerd voor geheugen. Er zijn verschillende manieren waarop een index voor een tabel die is geoptimaliseerd voor geheugen verschilt van een traditionele index op een schijfbasistabel:

Gegevensrijen worden niet opgeslagen op pagina's, dus er is geen verzameling pagina's of gebieden, geen partities of toewijzingseenheden waarnaar kan worden verwezen om alle pagina's voor een tabel op te halen. Er is het concept van indexpagina's voor een van de beschikbare typen indexen, maar ze worden anders opgeslagen dan indexen voor schijftabellen. Ze hebben geen traditionele paginafragmentatie, waardoor ze geen fillfactor hebben.
Wijzigingen in indexen in tabellen die zijn geoptimaliseerd voor geheugen tijdens het bewerken van gegevens, worden nooit naar de schijf geschreven. Alleen de gegevensrijen en wijzigingen in de gegevens worden naar het transactielogboek geschreven.
Indexen die zijn geoptimaliseerd voor geheugen, worden opnieuw opgebouwd wanneer de database weer online wordt gebracht.

Alle indexen voor tabellen die zijn geoptimaliseerd voor geheugen worden gemaakt op basis van de indexdefinities tijdens het herstellen van de database.

De index moet een van de volgende zijn:

Hashindex
Voor geheugen geoptimaliseerde niet-geclusterde index (wat wil zeggen de standaard interne structuur van een B-boom)

Hash--indexen worden in meer detail besproken in Hash-indexen voor Memory-Optimized-tabellen.
niet-geclusterde-indexen worden uitgebreider besproken in Niet-geclusterde index voor Memory-Optimized tabellen.
Columnstore indexen worden besproken in een ander artikel.

Syntaxis voor indexen die zijn geoptimaliseerd voor geheugen

Elke CREATE TABLE-statement voor een geheugen-geoptimaliseerde tabel moet een index bevatten, expliciet via een INDEX of impliciet via een primary key of unieke beperking.

Om te worden verklaard met de standaard DUURZAAMHEID = SCHEMA_AND_DATA moet de geheugen-geoptimaliseerde tabel een primaire sleutel hebben. De clausule PRIMARY KEY NONCLUSTERED in de volgende CREATE TABLE-instructie voldoet aan twee vereisten:

Biedt een index om te voldoen aan de minimale vereiste van één index in de instructie CREATE TABLE.

Het biedt de primaire sleutel die vereist is voor de SCHEMA_AND_DATA-clausule.

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

Notitie

SQL Server 2014 (12.x) en SQL Server 2016 (13.x) hebben een limiet van 8 indexen per tabel of tabeltype dat is geoptimaliseerd voor geheugen. Vanaf SQL Server 2017 (14.x) en in Azure SQL Database is er geen limiet meer voor het aantal indexen dat specifiek is voor tabellen en tabeltypen die zijn geoptimaliseerd voor geheugen.

Codevoorbeeld voor syntaxis

Deze subsectie bevat een Transact-SQL codeblok dat de syntaxis laat zien voor het maken van verschillende indexen in een tabel die is geoptimaliseerd voor geheugen. De code demonstreert het volgende:

Maak een tabel die is geoptimaliseerd voor geheugen.
Gebruik ALTER TABLE-instructies om twee indexen toe te voegen.

Voeg een paar rijen met gegevens in.

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

Dubbele indexsleutelwaarden

Dubbele waarden voor een indexsleutel kunnen de prestaties van tabellen die zijn geoptimaliseerd voor geheugen verminderen. Duplicaten stellen het systeem in staat om invoerketens te doorlopen voor de meeste lees- en schrijfbewerkingen op de index. Wanneer een keten van dubbele vermeldingen groter is dan 100 vermeldingen, kan de prestatievermindering meetbaar worden.

Dubbele hashwaarden

Dit probleem is beter zichtbaar in het geval van hash-indexen. Hash-indexen lijden meer vanwege de volgende overwegingen:

De lagere kosten per bewerking voor hash-indexen.
De interferentie van grote duplicaatketens met de hash-botsingsketen.

Probeer de volgende aanpassingen om duplicatie in een index te verminderen:

Gebruik een niet-geclusterde index.
Voeg extra kolommen toe aan het einde van de indexsleutel om het aantal duplicaten te verminderen.
- U kunt bijvoorbeeld kolommen toevoegen die zich ook in de primaire sleutel bevinden.

Voor meer informatie over hashconflicten, zie Hash-indexen voor Memory-Optimized tabellen.

Voorbeeldverbetering

Hier volgt een voorbeeld van hoe u prestatie-inefficiëntie in uw index kunt voorkomen.

Overweeg een Customers tabel met een primaire sleutel op CustomerIden een index heeft voor kolom CustomerCategoryID. Meestal zijn er veel klanten in een bepaalde categorie. Er zijn dus veel dubbele waarden voor CustomerCategoryID binnen een bepaalde sleutel van de index.

In dit scenario kunt u het beste een niet-geclusterde index op (CustomerCategoryID, CustomerId)gebruiken. Deze index kan worden gebruikt voor query's die gebruikmaken van een predicaat met betrekking tot CustomerCategoryID, maar de indexsleutel bevat geen duplicatie. Daarom worden er geen inefficiënties in het indexonderhoud veroorzaakt door de dubbele CustomerCategoryID-waarden of door de extra kolom in de index.

In de volgende query ziet u het gemiddelde aantal dubbele indexsleutelwaarden voor de index op CustomerCategoryID in tabel Sales.Customers, in de voorbeelddatabase WideWorldImporters.

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

Als u het gemiddelde aantal dubbele indexsleutels voor uw eigen tabel en index wilt evalueren, vervangt u Sales.Customers door de tabelnaam en vervangt u CustomerCategoryID door de lijst met indexsleutelkolommen.

Vergelijken wanneer elk indextype moet worden gebruikt

De aard van uw specifieke query's bepaalt welk type index de beste keuze is.

Bij het implementeren van tabellen die zijn geoptimaliseerd voor geheugen in een bestaande toepassing, is de algemene aanbeveling om te beginnen met niet-geclusterde indexen, omdat hun mogelijkheden meer lijken op de mogelijkheden van traditionele geclusterde en niet-geclusterde indexen op schijftabellen.

Aanbevelingen voor niet-geclusterd indexgebruik

Een niet-geclusterde index heeft de voorkeur boven een hash-index wanneer:

Query's hebben een ORDER BY component voor de geïndexeerde kolom.
Query's waarbij alleen de voorloopkolom(s) van een index met meerdere kolommen worden getest.
Query's testen de geïndexeerde kolom met behulp van een WHERE-component met:
- Een ongelijkheid: WHERE StatusCode != 'Done'
- Een scan naar waardebereik: WHERE Quantity >= 100

In alle volgende SELECT's heeft een niet-geclusterde index de voorkeur boven een hash-index:

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';

Aanbevelingen voor hash-indexgebruik

Hash-indexen worden voornamelijk gebruikt voor puntzoekacties en niet voor bereikscans.

Een hash-index heeft de voorkeur boven een niet-geclusterde index wanneer query's gelijkheidspredicaten gebruiken en de WHERE-component wordt toegewezen aan alle indexsleutelkolommen, zoals in het volgende voorbeeld:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

Index met meerdere kolommen

Een index met meerdere kolommen kan een niet-geclusterde index of een hash-index zijn. Stel dat de indexkolommen kolom1 en kolom2 zijn. Op basis van de volgende SELECT-instructie zou alleen de niet-geclusterde index nuttig zijn voor de queryoptimalisatiefunctie:

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

De hash-index heeft de WHERE-component nodig om een gelijkheidstest voor elk van de kolommen in de sleutel op te geven. Anders is de hash-index niet nuttig voor de queryoptimalisatie.

Geen van beide indextypen is nuttig als de WHERE component alleen de tweede kolom in de indexsleutel opgeeft.

Samenvattingstabel voor het vergelijken van scenario's voor indexgebruik

De volgende tabel bevat alle bewerkingen die worden ondersteund door de verschillende indextypen. Ja betekent dat de index de aanvraag efficiënt kan verwerken en Nee- betekent dat de index niet efficiënt aan de aanvraag kan voldoen.

Operatie	Geoptimaliseerd voor geheugen, hekje	Geoptimaliseerd voor geheugen, niet geclusterd	Op schijf gebaseerd, (niet)geclusterd
Indexscan, alle tabelrijen ophalen.	Ja	Ja	Ja
Indexzoeken op gelijkheidspredicaten (=).	Ja (Volledige sleutel is vereist.)	Ja	Ja
Index zoeken naar ongelijkheidspredicaten en bereikpredicaten (>, <, <=, >=, `BETWEEN`).	Nee (Resultaten in een indexscan.)	Ja ¹	Ja
Rijen ophalen in een sorteervolgorde die overeenkomt met de indexdefinitie.	Nee	Ja	Ja
Rijen ophalen in een sorteervolgorde die overeenkomt met het omgekeerde van de indexdefinitie.	Nee	Nee	Ja

¹ Voor een niet-geclusterde index die is geoptimaliseerd voor geheugen, is de volledige sleutel niet vereist om een indexzoekopdracht uit te voeren.

Automatisch index- en statistiekenbeheer

Maak gebruik van oplossingen zoals Adaptive Index Defragmentatie om automatisch indexdefragmentatie en statistiekenupdates voor een of meer databases te beheren. Deze procedure kiest automatisch of u een index wilt herbouwen of opnieuw ordenen op basis van het fragmentatieniveau, onder andere parameters, en statistieken bijwerken met een lineaire drempelwaarde.

Zie ook

Ontwerphandleiding voor SQL Server-index
Hash-indexen voor Memory-Optimized tabellen
niet-geclusterde indexen voor Memory-Optimized tabellen
Adaptieve Indexdefragmentatie

Delen via