Optimaliser med indekser
Indekser er datastrukturer som akseler datahenting ved å lage optimaliserte oppslagsstier til tabellrader. Uten indekser må databasemotoren skanne hver rad i en tabell for å finne matchende poster, en full tabellskanning som blir uoverkommelig treg etter hvert som tabellene vokser.
En indeks fungerer som en bokindeks: i stedet for å lese hver side for å finne en artikkel, konsulterer du indeksen for å hoppe direkte til relevante sider. Databasen bruker indekser på lignende måte, og konverterer potensielt millioner av rad-sammenligninger til noen få effektive oppslag.
Indekser bruker imidlertid lagringsplass og bremser INSERT, UPDATE, og DELETE operasjoner fordi databasen må opprettholde indeksstrukturen sammen med dataene. Denne avveiningen gjør indeksvalg til en kritisk designbeslutning som direkte påvirker både spørringsytelse og skrivekapasitet.
Ulike indekstyper har ulike formål.
Bruk radlagringsindekser
Å designe effektive indekser er nøkkelen til god ytelse i databaser og applikasjoner. Mangel på indekser, overindeksering eller dårlig utformede indekser er de viktigste årsakene til databaseytelsesproblemer.
Rowstore-indekser organiserer data i radformat, og lagrer alle kolonnene i en rad samlet på samme side, noe som gjør dem optimale for transaksjonelle arbeidsbelastninger som henter komplette poster eller utfører hyppige oppdateringer.
En klynget indeks sorterer og lagrer dataradene i tabellen basert på deres nøkkelverdier. Disse nøkkelverdiene er kolonnene som er inkludert i indeksdefinisjonen. Det kan bare være én klynget indeks per tabell, fordi dataradene selv kan lagres i bare én rekkefølge.
En ikke-klynget indeks har en struktur adskilt fra dataradene. En ikke-klynget indeks inneholder de ikke-klyngede indeksnøkkelverdiene, og hver nøkkelverdioppføring har en peker til dataraden som inneholder nøkkelverdien. Du kan lage flere ikke-klyngede indekser på en tabell eller indeksert 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);
Klyngede indekser er best når du trenger effektive intervallspørringer, stabile og smale nøkler, eller en naturlig sorteringsrekkefølge som identitetskolonner eller datofelt, fordi de definerer den fysiske radrekkefølgen og optimaliserer skanninger over ordnede data.
Ikke-klyngede indekser er ideelle når du trenger raske oppslag for spesifikke predikater, joins eller sorteringsmønstre som ikke samsvarer med den klyngede nøkkelen, eller når du vil dekke en spørring ved å inkludere ekstra kolonner for å unngå nøkkeloppslag.
Valget mellom dem avhenger av hvordan du får tilgang til dataene: bruk en klynget indeks for den primære tilgangsstien og ikke-klyngede indekser for å støtte alternative, svært selektive eller ofte forespurte mønstre, samtidig som du balanserer kostnadene de medfører ved skriveoperasjoner.
Forstå columnstore-indekser
Tradisjonelle radlagringsindekser lagrer data rad for rad, noe som er perfekt for transaksjonssystemer som henter individuelle poster. Men analytiske spørringer som skanner millioner av rader for å beregne aggregater (SUM, AVG, ) COUNTkaster bort tid på å lese kolonner de ikke trenger. Columnstore-indekser har som mål å løse dette ved å lagre data kolonne for kolonne, og kun lese de kolonnene som kreves for spørringen din.
Forstå columnstore-arkitekturen
En kolonnelagringsindeks organiserer data i radgrupper, hver med opptil 1 048 576 rader. Innenfor hver radgruppe lagrer motoren hver kolonne separat som et kolonnesegment og komprimerer den uavhengig. Denne arkitekturen gjør det mulig for spørringsoptimalisatoren å lese kun kolonnene som trengs for en spørring, og hoppe over irrelevante data helt.
Når du setter inn data, går små partier først til en deltastore—en midlertidig radlagringsstruktur som bruker en B+ treindeks. Når en delta-radgruppe har akkumulert nok rader (minst 102 400), komprimerer en bakgrunnsprosess kalt tuple-mover den inn i columnstore. Rader som kommer gjennom bulklaster på 102 400 eller flere rader omgår deltastore og komprimeres direkte inn i kolonnelagringen.
Følgende tabell beskriver anbefalingen for kolonnelagringsindekser:
| Scenario | Anbefaling | Reason |
|---|---|---|
| Datavarehus-faktatabeller | Bruk columnstore | Tabeller med millioner+ rader brukt til analyse drar nytte av kolonnelagring og komprimering |
| Rapporteringsdatabaser | Bruk columnstore | Lesetunge arbeidsbelastninger med aggregerte spørringer yter raskere med kolonneorientert tilgang |
| Historiske data | Bruk columnstore | Arkiverte data som du sjelden oppdaterer, men ofte analyserer, oppnår høye komprimeringsforhold |
| Små tabeller (<1 million rader) | Unngå columnstore | Overhead veier tyngre enn fordelene; Rekkgrupper trenger tilstrekkelige rader for effektiv komprimering |
| Høyfrekvent oppdateringer/slettinger | Unngå columnstore | Modifikasjoner markerer rader som slettet i stedet for å oppdateres på stedet, noe som fører til fragmentering |
| Enkeltradsoppslag | Unngå columnstore | Rowstore-indekser er raskere for å hente individuelle poster |
Bruk Clustered Columnstore Index (CCI)
En Clustered Columnstore Index (CCI) er en type columnstore-indeks som blir den primære lagringsstrukturen for hele tabellen, og erstatter enhver eksisterende klynget radlagringsindeks. I motsetning til en ikke-klynget kolonnelagringsindeks (NCCI), som lager en sekundær kolonnekopi ved siden av radlagringstabellen, lagrer en CCI all tabelldata utelukkende i kolonneformat.
Dette betyr at tabellen ikke har tradisjonell radbasert lagring – motoren komprimerer og lagrer hver kolonne separat. Både CCI og NCCI bruker samme optimalisering for kolonnekomprimering og batchprosessering, men bruker CCI når analyse er hovedarbeidsbelastningen og du ikke trenger transaksjonsmønstre på radnivå. I kontrast tillater en NCCI deg å vedlikeholde radlagerindekser for transaksjonsspørringer, samtidig som den gir en kolonnestruktur for analytiske spørringer på samme tabell.
Du kan lage en klynget kolonnelagringsindeks ved å bruke setningen 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;
Bruk Nonclustered Columnstore Index (NCCI)
En Nonclustered Columnstore Index (NCCI) lager en separat kolonnekopi av utvalgte kolonner ved siden av den eksisterende radlagringstabellen, noe som gjør at samme tabell effektivt kan håndtere både transaksjonelle og analytiske arbeidsbelastninger. Tabellen beholder sin opprinnelige klyngede radlagringsindeks for raske enkeltradoppslag og oppdateringer, mens NCCI gir optimalisert kolonnebasert tilgang for analytiske spørringer. Spørringsoptimalisatoren velger automatisk mellom radstore- og kolonnestore-strukturene basert på spørringsmønsteret.
Du kan lage en ikke-klynget columnstore-indeks ved å bruke setningen 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åk kolonnestore-indekser
Du kan overvåke helsen og ytelsen til columnstore-indeksene dine ved å søke i den dynamiske administrasjonsvisningen sys.dm_db_column_store_row_group_physical_stats .
Følgende spørring viser radgruppestatistikk inkludert tilstand, radantall, slettede rader og lagringsstørrelse. Åpne radgrupper aksepterer fortsatt innsettinger i deltastore, lukkede radgrupper venter på at tuple-mover skal komprimere dem, og komprimerte radgrupper lagrer data i kolonneformat. Høye rader eller mange små radgrupper 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 spørringsytelse og skrivekapasitet. Designindekser nøye under den innledende utviklingen for å unngå kostbare ombygginger og ytelsesproblemer i produksjonen.