Optimer med indekser

Indekser er datastrukturer, der acceler datahentning ved at skabe optimerede opslagstier til tabelrækker. Uden indekser skal databasemotoren scanne hver række i en tabel for at finde matchende poster, en fuld tabelscanning, der bliver uoverkommeligt langsom, efterhånden som tabellerne vokser.

Et indeks fungerer som et bogindeks: i stedet for at læse hver side for at finde en artikel, konsulterer du indekset for at springe direkte til relevante sider. Databasen bruger indekser på lignende vis, hvilket potentielt omdanner millioner af rækkesammenligninger til en håndfuld effektive opslag.

Indekser bruger dog lagerplads og sænker INSERT, UPDATE, og DELETE operationer, fordi databasen skal vedligeholde indeksstrukturen sammen med dataene. Denne afvejning gør indeksvalget til en kritisk designbeslutning, der direkte påvirker både forespørgselsydelse og skrivegennemstrømning.

Forskellige indekstyper tjener forskellige formål.

Brug rowstore-indekser

At designe effektive indekser er nøglen til at opnå god database- og applikationsydelse. Mangel på indekser, overindeksering eller dårligt designede indekser er de vigtigste kilder til databasepræstationsproblemer.

Rowstore-indekser organiserer data i rækkeformat og gemmer alle kolonner i en række samlet på samme side, hvilket gør dem optimale til transaktionsarbejdsbyrder, der henter komplette poster eller udfører hyppige opdateringer.

Et klyngeindeks sorterer og gemmer datarækkerne i tabellen baseret på deres nøgleværdier. Disse nøgleværdier er de kolonner, der indgår i indeksdefinitionen. Der kan kun være ét klyngeindeks pr. tabel, fordi datarækkerne selv kun kan gemmes i én rækkefølge.

Et ikke-klynget indeks har en struktur adskilt fra datarækkerne. Et ikke-klynget indeks indeholder de ikke-klyngede indeksnøgleværdier, og hver nøgleværdipost har en peger til den datarække, der indeholder nøgleværdien. Du kan oprette flere ikke-klyngede indekser på en tabel eller indekseret visning.

-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID 
ON Product(ProductID);

-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category 
ON Product(Category) 
INCLUDE (ProductName, Price);

Klyngeindekser er bedst, når du har brug for effektive intervalforespørgsler, stabile og smalle nøgler eller en naturlig sorteringsrækkefølge som identitetskolonner eller datofelter, fordi de definerer den fysiske rækkerækkefølge og optimerer scanninger over ordnede data.

Ikke-klyngede indekser er ideelle, når du har brug for hurtige opslag for specifikke prædikater, joins eller sorteringsmønstre, der ikke stemmer overens med den klyngede nøgle, eller når du vil dække en forespørgsel ved at inkludere ekstra kolonner for at undgå nøgleopslag.

Valget mellem dem afhænger af, hvordan du tilgår dataene: brug et klyngeindeks til den primære adgangssti og ikke-klyngede indekser til at understøtte alternative, meget selektive eller ofte forespurgte mønstre, samtidig med at du balancerer de omkostninger, de medfører ved skriveoperationer.

Forstå columnstore-indekser

Traditionelle rowstore-indekser gemmer data række for række, hvilket er perfekt til transaktionssystemer, der henter individuelle poster. Men analytiske forespørgsler, der scanner millioner af rækker for at beregne aggregater (SUM, AVG, ), COUNTspilder tid på at læse kolonner, de ikke har brug for. Columnstore-indekser har til formål at løse dette ved at gemme data kolonne for kolonne og kun læse de kolonner, der kræves til din forespørgsel.

Forstå columnstore-arkitekturen

Et kolonnelagerindeks organiserer data i rækkegrupper, hver med op til 1.048.576 rækker. Inden for hver rækkegruppe gemmer motoren hver kolonne separat som et kolonnesegment og komprimerer den uafhængigt. Denne arkitektur gør det muligt for forespørgselsoptimereren kun at læse de kolonner, der er nødvendige for en forespørgsel, og springer irrelevante data helt over.

Når du indsætter data, går små batches først til en deltastore—en midlertidig rowstore-struktur, der bruger et B+ træindeks. Når en delta-rækkegruppe har akkumuleret nok rækker (mindst 102.400), komprimerer en baggrundsproces kaldet tuple-mover den ind i columnstore. Rækker, der ankommer gennem bulk-læs på 102.400 eller flere rækker, omgår deltastore og komprimeres direkte ind i kolonnelageret.

Følgende tabel beskriver anbefalingen for columnstore-indekser:

Brug Clustered Columnstore Index (CCI)

Et Clustered Columnstore Index (CCI) er en type kolonnelagerindeks, der bliver den primære lagringsstruktur for hele tabellen og erstatter ethvert eksisterende clustered rowstore-indeks. I modsætning til et ikke-klynget kolonnelagringsindeks (NCCI), som opretter en sekundær kolonnekopi ved siden af rowstore-tabellen, gemmer en CCI alle tabeldata udelukkende i kolonneformat.

Det betyder, at tabellen ikke har traditionel rækkebaseret lagring – motoren komprimerer og gemmer hver kolonne separat. Både CCI og NCCI bruger de samme kolonnekomprimerings- og batchbehandlingsoptimeringer, men bruger en CCI, når analytics er den primære arbejdsbyrde, og du ikke behøver række-niveau transaktionelle adgangsmønstre. Til sammenligning gør en NCCI det muligt at vedligeholde rowstore-indekser for transaktionelle forespørgsler, samtidig med at der tilbydes en kolonnestruktur til analytiske forespørgsler i samme tabel.

Du kan oprette et clustered columnstore-indeks ved at bruge sætningen CREATE CLUSTERED COLUMNSTORE INDEX . Her er et eksempel:

-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;

-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;

Brug Nonclustered Columnstore Index (NCCI)

Et Nonclustered Columnstore Index (NCCI) opretter en separat kolonnekopi af udvalgte kolonner ved siden af den eksisterende rowstore-tabel, hvilket gør det muligt for den samme tabel at levere både transaktionelle og analytiske arbejdsbelastninger effektivt. Tabellen bevarer sit oprindelige klyngede rækkelagerindeks til hurtige enkeltrækkeopslag og opdateringer, mens NCCI tilbyder optimeret kolonnebaseret adgang til analytiske forespørgsler. Forespørgselsoptimeringen vælger automatisk mellem rowstore- og columnstore-strukturerne baseret på forespørgselsmønstret.

Du kan oprette et ikke-clustered columnstore-indeks ved at bruge sætningen CREATE NONCLUSTERED COLUMNSTORE INDEX . Her er et eksempel:

-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);

Overvåg kolonnestore-indekser

Du kan overvåge sundheden og ydeevnen af dine columnstore-indekser ved at forespørge den dynamiske sys.dm_db_column_store_row_group_physical_stats administrationsvisning.

Følgende forespørgsel viser rækkegruppestatistikker, herunder tilstand, rækkeantal, slettede rækker og lagerstørrelse. Åbne rækkegrupper accepterer stadig indsæt i deltastore, lukkede rækkegrupper venter på, at tuple-moveren komprimerer dem, og komprimerede rækkegrupper gemmer data i kolonneformat. Høje slettede rækketal eller mange små rækkegrupper indikerer fragmentering, som du kan løse med ALTER INDEX REORGANIZE.

-- Check columnstore health
SELECT 
    object_name(object_id) AS TableName,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');

Indeksvalg påvirker direkte både forespørgselsydelse og skrivegennemstrømning. Designindekserer omhyggeligt under den indledende udvikling for at undgå dyre genopbygninger og produktionsproblemer.