Dela via

Kolumnlagringsindex – Designvägledning

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-databas i Microsoft Fabric

Rekommendationer på hög nivå för att utforma kolumnlagringsindex. Några bra designbeslut hjälper dig att uppnå den höga datakomprimering och frågeprestanda som kolumnlagringsindex är utformade för att ge.

Förutsättningar

Den här artikeln förutsätter att du är bekant med kolumnarkivarkitektur och terminologi. För mer information, se Kolumnbutiksindex: Översikt och Kolumnbutiksindexarkitektur.

Känna till dina datakrav

Innan du utformar ett columnstore-index bör du förstå så mycket som möjligt om dina datakrav. Tänk till exempel igenom svaren på dessa frågor:

  • Hur stor är min tabell?
  • Utför mina frågor främst analyser som genomsöker stora intervall med värden? Kolumnlagringsindex är utformade för att fungera bra för omfattande genomsökningar, snarare än att leta upp specifika värden.
  • Utför min arbetsbelastning många uppdateringar och raderingar? Kolumnlagringsindex fungerar bra när data är stabila. Frågeställningar bör uppdatera och ta bort mindre än 10% av raderna.
  • Har jag fakta- och dimensionstabeller för ett informationslager?
  • Behöver jag utföra analytik på en transaktionell arbetsbelastning? I så fall kan du läsa designvägledningen för columnstore för driftanalys i realtid.

Du kanske inte behöver ett kolumnlagringsindex. Radlagringstabeller (eller B-träd) med heaps eller klustrade index presterar bäst på frågor som söker i datan, efter ett visst värde eller på frågor om ett litet intervall av värden. Använd rowstore-index med transaktionella arbetsbelastningar eftersom de oftast kräver tabellsökningar snarare än stora intervalltabellgenomsökningar.

Välj det bästa kolumnlagringsindexet för dina behov

Ett kolumnlagringsindex är antingen klustrat eller icke-grupperat. Ett grupperat columnstore-index kan ha ett eller flera icke-grupperade B-trädindex. Det är enkelt att prova kolumnlagringsindex. Om du skapar en tabell som ett kolumnlagringsindex kan du enkelt konvertera tabellen tillbaka till en radlagringstabell genom att ta bort kolumnlagringsindexet.

Här är en sammanfattning av alternativen och rekommendationerna.

Alternativet kolumnbutik Rekommendationer för när du ska använda Komprimering
Grupperat kolumnlagringsindex Används för:

1) Traditionell arbetsbelastning för informationslager med ett star- eller snowflake-schema

2) IOT-arbetsbelastningar (Internet of Things) som infogar stora mängder data med minimala uppdateringar och borttagningar.		 Genomsnitt på 10x
Sorterat kolumnlagringsindex Använd när ett grupperat columnstore-index efterfrågas via en enda ordnad predikatkolumn eller kolumnuppsättning. Den här vägledningen liknar valet av nyckelkolumner för ett rowstore-grupperat index, även om de komprimerade underliggande radgrupperna fungerar annorlunda. För mer information, se CREATE COLUMNSTORE INDEX och Prestandaoptimering med ordnade columnstore-index. Genomsnitt på 10x
Icke-grupperade B-trädindex på ett grupperat kolumnlagringsindex Använd för att:

1. Tillämpa primärnyckel- och främmande nyckel-begränsningar på ett grupperat kolumnlagringsindex.

2. Påskynda frågor som söker efter specifika värden eller små intervall med värden.

3. Påskynda uppdateringar och borttagningar av specifika rader.		 10x i genomsnitt plus ytterligare lagring för NCIs.
Icke-klustrat kolumnlagringsindex på en diskbaserad heap eller ett B-trädindex Används för:

1) En OLTP-arbetsbelastning som har vissa analysfrågor. Du kan släppa B-trädindex som skapats för analys och ersätta dem med ett icke-grupperat kolumnlagringsindex.

2) Många traditionella OLTP-arbetsbelastningar som utför ETL-åtgärder (Extract Transform and Load) för att flytta data till ett separat informationslager. Du kan eliminera ETL och ett separat informationslager genom att skapa ett icke-grupperat kolumnlagringsindex på några av OLTP-tabellerna.		 NCCI är ett ytterligare index som kräver 10% mer lagring i genomsnitt.
Columnstore-index i en minnesintern tabell Samma rekommendationer som icke-grupperade kolumnlagringsindex i en diskbaserad tabell, förutom att bastabellen är en minnesintern tabell. Kolumnlagringsindex är ett ytterligare index.

Använda ett grupperat columnstore-index för stora informationslagertabeller

Det klustrade kolumnlagringsindexet är mer än bara ett index; det fungerar som den primära lagringen för tabellen. Det ger hög datakomprimering och en betydande förbättring av frågeprestanda på fakta- och dimensionstabeller för stora datalager. Grupperade kolumnlagringsindex passar bäst för analysfrågor snarare än transaktionsfrågor, eftersom analysfrågor tenderar att utföra åtgärder på stora värden i stället för att leta upp specifika värden.

Överväg att använda ett grupperat columnstore-index när:

  • Varje partition har minst en miljon rader. Kolumnlagringsindex har radgrupper inom varje partition. Om tabellen är för liten för att fylla en radgrupp inom varje partition kanske du inte får fördelarna med kolumnlagringskomprimering och frågeprestanda.
  • Förfrågningar utför huvudsakligen analys av värdeintervall. För att till exempel hitta det genomsnittliga värdet för en kolumn måste frågan genomsöka alla kolumnvärden. Sedan aggregeras värdena genom att summera dem för att fastställa medelvärdet.
  • De flesta infogningar finns på stora mängder data med minimala uppdateringar och borttagningar. Många arbetsflöden, såsom Sakernas Internet (Internet of Things, IoT), infogar stora mängder data med få uppdateringar och borttagningar. Dessa arbetsbelastningar kan dra nytta av komprimerings- och frågeprestandavinster som kommer från att använda ett grupperat kolumnlagringsindex.

Använd inte ett grupperat columnstore-index när:

  • Tabellen kräver varchar(max), nvarchar(max)eller varbinary(max) datatyper. Du kan också utforma kolumnlagringsindexet så att det inte innehåller dessa kolumner (gäller för: SQL Server 2016 (13.x) och tidigare versioner).
  • Tabelldata är inte permanenta. Överväg att använda en heap eller tillfällig tabell när du behöver lagra och ta bort data snabbt.
  • Tabellen har mindre än en miljon rader per partition.
  • Mer än 10% av åtgärderna i tabellen är uppdateringar och borttagningar. Ett stort antal uppdateringar och borttagningar orsakar fragmentering. Fragmenteringen påverkar komprimeringshastigheter och frågeprestanda tills du kör en åtgärd som kallas omorganisering, vilket tvingar alla data till kolumnarkivet och tar bort fragmentering. Mer information finns i Minimera indexfragmentering i columnstore-index.

Mer information finns i Columnstore-index i datalager.

Använda ett ordnat kolumnlagerindex för stora informationslagertabeller

Information om tillgänglighet för ordnat columnstore-index finns i Columnstore-index: Översikt.

Överväg att använda ett ordnat columnstore-index i följande scenarier:

  • När data är relativt statiska (utan ofta skrivningar och borttagningar) och den ordnade kolumnlagringsindexnyckeln är statisk, kan ordnade kolumnlagringsindex ge betydande prestandafördelar jämfört med icke-ordnade kolumnlagringsindex eller radlagringsindex för analytiska arbetsbelastningar.
  • Ju fler distinkta värden det finns i den första kolumnen av kolumnlagringsindexets nyckel, desto större kan prestandaförbättringarna vara. Detta beror på förbättrad segmenteliminering för strängdata. Mer information finns i segmenteliminering.
  • Välj en ordnad kolumnlagringsindexnyckel som ofta efterfrågas och som kan dra nytta av segmenteliminering, särskilt den första kolumnen i nyckeln. Prestandavinster på grund av segmenteliminering på andra kolumner i tabellen är mindre förutsägbara.
  • Användningsfall där endast de senaste analysdata måste efterfrågas, till exempel de senaste 15 sekunderna, kan ordnade kolumnlagringsindex ge segmenteliminering för äldre data. Den första kolumnen i nyckeln för de ordnade kolumnlagringsdata måste vara datum/tid-data, till exempel ett infogat eller skapat datum/tid. Segmenteliminering skulle vara effektivare i ett ordnat kolumnlagringsindex än i ett oordnat kolumnlagringsindex.
  • Överväg att ordna kolumnlagringsindex för tabeller som innehåller nycklar med GUID-data, där datatypen uniqueidentifier nu kan användas för segmenteliminering.

Ett ordnat kolumnlagringsindex kanske inte är lika effektivt i dessa scenarier:

  • På samma sätt som andra kolumnlagringsindex kan en hög infogningsaktivitet skapa överdriven lagrings-I/O.
  • För arbetsflöden där det finns många skrivoperationer kommer kvaliteten på segmenteliminering över tid att minska på grund av radgruppsunderhåll av tuppelflyttaren. Detta kan minimeras genom regelbundet underhåll av kolumnlagringsindexet med ALTER INDEX REORGANIZE.

Lägga till icke-grupperade B-trädindex för effektiva tabellsökningar

Från och med SQL Server 2016 (13.x) kan du skapa icke-grupperade B-träd- eller radlagringsindex som sekundära index i ett grupperat kolumnlagringsindex. Det icke-klustrade B-trädindexet uppdateras när ändringar görs i kolumnlagringsindexet. Det här är en kraftfull funktion som du kan använda till din fördel.

Genom att använda det sekundära B-trädindexet kan du effektivt söka efter specifika rader utan att genomsöka alla rader. Andra alternativ blir också tillgängliga. Du kan till exempel tillämpa en primär- eller sekundärnyckelbegränsning med hjälp av en UNIK begränsning för B-trädindexet. Eftersom ett icke-unikt värde inte kan infogas i B-trädindexet kan SQL Server inte infoga värdet i kolumnarkivet.

Överväg att använda ett B-trädindex på ett kolumnlagringsindex för att:

  • Kör frågor som söker efter specifika värden eller små intervall med värden.
  • Framtvinga en begränsning, till exempel en primärnyckel eller sekundärnyckelbegränsning.
  • Utför effektivt uppdaterings- och borttagningsåtgärder. B-trädindexet kan snabbt hitta de specifika raderna för uppdateringar och borttagningar utan att genomsöka den fullständiga tabellen eller partitionen i en tabell.
  • Du har ytterligare lagringsutrymme för att lagra B-trädindexet.

Använda ett icke-grupperat kolumnlagringsindex för realtidsanalys

Från och med SQL Server 2016 (13.x) kan du ha ett icke-grupperat kolumnlagringsindex i en diskbaserad radlagringstabell eller en minnesintern OLTP-tabell. Detta gör det möjligt att köra analysen i realtid på en transaktionstabell. Medan transaktioner sker i den underliggande tabellen kan du köra analys på kolumnlagringsindexet. Eftersom en tabell hanterar båda indexen är ändringar tillgängliga i realtid för både radarkivet och kolumnlagringsindexen.

Eftersom ett columnstore-index uppnår 10x bättre datakomprimering än ett radlagringsindex behöver det bara en liten mängd extra lagringsutrymme. Om den komprimerade radlagringstabellen till exempel tar 20 GB kan kolumnlagringsindexet kräva ytterligare 2 GB. Det ytterligare utrymme som krävs beror också på antalet kolumner i det icke-klustrade kolumnbutiksindexet.

Överväg att använda ett icke-grupperat kolumnlagringsindex för att:

  • Kör analys i realtid på en transaktionell radlagertabell. Du kan ersätta befintliga B-trädindex som är utformade för analys med ett icke-grupperat kolumnlagringsindex.

  • Eliminera behovet av ett separat informationslager. Traditionellt kör företag transaktioner i en radlagringstabell och läser sedan in data i ett separat informationslager för att köra analys. För många arbetsbelastningar kan du eliminera inläsningsprocessen och det separata informationslagret genom att skapa ett icke-klustrat kolumnstore-index på transaktionstabeller.

SQL Server 2016 (13.x) erbjuder flera strategier för att göra det här scenariot mer presterande. Det är enkelt att prova eftersom du kan aktivera ett icke-klustrat kolumnlagringsindex utan ändringar i din OLTP-applikation.

Om du vill lägga till ytterligare bearbetningsresurser kan du köra analysen på en läsbar sekundär. Om du använder en läsbar sekundär separeras bearbetningen av transaktionsarbetsbelastningen och analysarbetsbelastningen.

Mer information finns i Komma igång med Columnstore för driftanalys i realtid

Mer information om hur du väljer det bästa kolumnlagringsindexet finns i Sunil Agarwals blogg Vilket kolumnlagringsindex är rätt för min arbetsbelastning?.

Använda tabellpartitioner för datahantering och frågeprestanda

Columnstore-index stöder partitionering, vilket är ett bra sätt att hantera och arkivera data. Partitionering förbättrar också frågeprestanda genom att begränsa åtgärder till en eller flera partitioner.

Använda partitioner för att göra data enklare att hantera

För stora tabeller är det enda praktiska sättet att hantera dataintervall genom att använda partitioner. Fördelarna med partitioner för radlagringstabeller gäller även för columnstore-index.

Till exempel använder både tabellerna rowstore och columnstore partitioner för att:

  • Kontrollera storleken på inkrementella säkerhetskopior. Du kan säkerhetskopiera partitioner för att separera filgrupper och sedan markera dem som skrivskyddade. Detta gör att framtida säkerhetskopior hoppar över skrivskyddade filgrupper.
  • Spara lagringskostnader genom att flytta en äldre partition till billigare lagring. Du kan till exempel använda partitionsväxling för att flytta en partition till en billigare lagringsplats.
  • Utför åtgärder effektivt genom att begränsa åtgärderna till en partition. Du kan till exempel endast rikta in dig på fragmenterade partitioner för indexunderhåll.

Med ett columnstore-index använder du dessutom partitionering för att:

  • Spara ytterligare 30% i lagringskostnader. Du kan komprimera äldre partitioner med COLUMNSTORE_ARCHIVE komprimeringsalternativ. Frågeprestanda kan vara långsammare, vilket kan vara acceptabelt om partitionen efterfrågas sällan.

Använda partitioner för att förbättra frågeprestanda

Genom att använda partitioner kan du begränsa dina frågor till att endast genomsöka specifika partitioner, vilket begränsar antalet rader som ska genomsökas. Om indexet till exempel partitioneras per år och frågan analyserar data från förra året behöver det bara söka igenom data i en partition.

Använda färre partitioner för ett columnstore-index

Om du inte har en tillräckligt stor datastorlek presterar ett kolumnlagringsindex bäst med färre partitioner än vad du kan använda för ett radlagringsindex. Om du inte har minst en miljon rader per partition kan de flesta av dina rader hamna i deltastore, där de inte får den prestandafördel som kolumnlagringskomprimering medför. Om du till exempel läser in en miljon rader i en tabell med 10 partitioner och varje partition tar emot 100 000 rader, går alla rader till deltaradgrupperna.

Exempel:

  • Läs in 1 000 000 rader i en partition eller en tabell som inte är partitionerad. Du får en komprimerad radgrupp med 1 000 000 rader. Detta är bra för hög datakomprimering och snabba frågeprestanda.
  • Läs in 1 000 000 rader jämnt i 10 partitioner. Varje partition får 100 000 rader, vilket är mindre än det minsta tröskelvärdet för kolumnlagringskomprimering. Därför kan kolumnlagringsindexet ha 10 deltaradgrupper med 100 000 rader i varje. Det finns sätt att tvångsintegrera deltaradgrupperna i kolumnlagret. Men om det här är de enda raderna i kolumnlagringsindexet är de komprimerade radgrupperna för små för bästa komprimering och frågeprestanda.

Mer information om partitionering finns i Sunil Agarwals blogginlägg Ska jag partitionera mitt kolumnlagringsindex?.

Välj lämplig datakomprimeringsmetod

Kolumnlagringsindexet erbjuder två alternativ för datakomprimering: kolumnlagringskomprimering och arkivkomprimering. Du kan välja komprimeringsalternativet när du skapar indexet eller ändra det senare med ALTER INDEX ... ÅTERSKAPA.

Använd columnstore-komprimering för bästa frågeprestanda

Columnstore-komprimering uppnår vanligtvis 10x bättre komprimeringshastigheter över radlagringsindex. Det är standardkomprimeringsmetoden för kolumnlagringsindex och möjliggör snabb frågeprestanda.

Använd arkivkomprimering för bästa datakomprimering

Arkivkomprimering är utformad för maximal komprimering när frågeprestanda inte är lika viktigt. Det uppnår högre datakomprimeringshastigheter än kolumnlagringskomprimering, men det levereras med ett pris. Det tar längre tid att komprimera och dekomprimera data, så det passar inte för snabba frågeprestanda.

Använda optimeringar när du konverterar en radlagringstabell till ett columnstore-index

Om dina data redan finns i en radlagringstabell kan du använda CREATE COLUMNSTORE INDEX för att konvertera tabellen till ett grupperat kolumnlagringsindex. Det finns ett par optimeringar som förbättrar frågeprestanda när tabellen har konverterats, vilket beskrivs härnäst.

Använd MAXDOP för att förbättra radgruppskvaliteten

Du kan konfigurera det maximala antalet processorer för att konvertera ett heap- eller ett klustrat B-trädindex till ett kolumnlagringsindex. Om du vill konfigurera processorerna använder du maxgraden av parallellitetsalternativet (MAXDOP).

Om du har stora mängder data kan MAXDOP-1 vara för långsamt. Att öka MAXDOP till 4 fungerar bra. Om detta resulterar i några radgrupper som inte har det optimala antalet rader kan du köra ALTER INDEX REORGANIZE för att sammanfoga dem i bakgrunden.

Behåll den sorterade ordningen för ett B-trädindex

Eftersom B-trädindexet redan lagrar rader i en sorterad ordning kan det förbättra frågeprestandan genom att bevara den ordningen när raderna komprimeras till kolumnlagringsindexet.

Kolumnlagringsindexet sorterar inte data, men det använder metadata för att spåra lägsta och högsta värden för varje kolumnsegment i varje radgrupp. När du söker efter ett intervall med värden kan den snabbt beräkna när du ska hoppa över radgruppen. När data är sorterad kan man hoppa över fler radgrupper.

Så här bevarar du den sorterade ordningen under konverteringen:

  • Använd CREATE COLUMNSTORE INDEX med DROP_EXISTING-parametern. Detta bevarar även namnet på indexet. Om du har skript som redan använder namnet på radlagringsindexet behöver du inte uppdatera dem.

    I det här exemplet konverteras ett grupperat radlagringsindex i en tabell med namnet MyFactTable till ett grupperat kolumnlagringsindex. Indexnamnet ClusteredIndex_d473567f7ea04d7aafcac5364c241e09förblir detsamma.

    CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
ON MyFactTable
WITH (DROP_EXISTING = ON);

Förstå segmenteliminering

Varje radgrupp innehåller ett kolumnsegment för varje kolumn i tabellen. Varje kolumnsegment komprimeras tillsammans och lagras på fysiska medier.

Det finns metadata med varje segment för att möjliggöra snabb eliminering av segment utan att läsa dem. Val av datatyp kan ha en betydande inverkan på frågeprestandabaserade vanliga filterpredikat för frågor i kolumnlagringsindexet. Mer information finns i segmenteliminering.

Det här är uppgifter för att skapa och underhålla kolumnlagringsindex.

Uppgift Referensartiklar Anteckningar
Skapa en tabell som en kolumnlagring. CREATE TABLE (Transact-SQL) Från och med SQL Server 2016 (13.x) kan du skapa tabellen som ett grupperat kolumnlagringsindex. Du behöver inte först skapa en radlagringstabell och sedan konvertera den till columnstore.
Skapa en minnestabell med ett columnstore-index. CREATE TABLE (Transact-SQL) Från och med SQL Server 2016 (13.x) kan du skapa en minnesoptimerad tabell med ett kolumnlagringsindex. Kolumnlagringsindexet kan också läggas till när tabellen har skapats med syntaxen ALTER TABLE ADD INDEX.
Konvertera en radlagringstabell till kolumnlagring. CREATE COLUMNSTORE INDEX (Transact-SQL) Konvertera en befintlig heap eller ett B-träd till kolumnlagring. Exempel visar hur du hanterar befintliga index och även namnet på indexet när du utför den här konverteringen.
Konvertera en kolumnorienterad tabell till radorienterad lagring. CREATE CLUSTERED INDEX (Transact-SQL) eller Konvertera en kolumnlagringstabell tillbaka till en radlagringshög Vanligtvis är den här konverteringen inte nödvändig, men det kan finnas tillfällen då du behöver konvertera. Exempel visar hur du konverterar ett kolumnarkiv till ett heap- eller klustrat index.
Skapa ett kolumnlagringsindex i en radlagringstabell. CREATE COLUMNSTORE INDEX (Transact-SQL) En radlagringstabell kan ha ett kolumnlagringsindex. Från och med SQL Server 2016 (13.x) kan kolumnlagringsindexet ha ett filtrerat villkor. Exempel visar den grundläggande syntaxen.
Skapa högpresterande index för driftanalys. Kom igång med Columnstore för driftanalys i realtid Beskriver hur du skapar kompletterande kolumnlagrings- och B-trädindex så att OLTP-frågor använder B-trädindex och analysfrågor använder kolumnlagringsindex.
Skapa högpresterande kolumnlagringsindex för datalagerhantering. Columnstore-indexar i datalagerhantering Beskriver hur du använder B-trädindex i kolumnlagringstabeller för att skapa högpresterande datalagerfrågor.
Använd ett B-trädindex för att framtvinga en primärnyckelbegränsning för ett kolumnlagringsindex. Columnstore-indexar i datalagerhantering Visar hur du kombinerar B-träd- och kolumnlagringsindex för att framtvinga primärnyckelbegränsningar för kolumnlagringsindexet.
Ta bort ett kolumnlagringsindex DROP INDEX (Transact-SQL) Att ta bort ett columnstore-index görs med den standardsyntax för DROP INDEX som används för B-trädindex. Om du tar bort ett grupperat columnstore-index konverteras columnstore-tabellen till en heap.
Ta bort en rad från ett columnstore-index DELETE (Transact-SQL) Använd DELETE (Transact-SQL) för att ta bort en rad.

columnstore rad: SQL Server markerar raden som logiskt borttagen men återtar inte den fysiska lagringen för raden förrän indexet återskapas.

deltastore- rad: SQL Server tar bort raden logiskt och fysiskt.
Uppdatera en rad i columnstore-indexet UPDATE (Transact-SQL) Använd UPDATE (Transact-SQL) för att uppdatera en rad.

columnstore rad: SQL Server markerar raden som logiskt borttagen och infogar sedan den uppdaterade raden i deltastore.

deltastore rad: SQL Server uppdaterar raden i deltastore.
Tvinga alla rader i deltaarkivet att gå in i kolumnarkivet. ALTER INDEX (Transact-SQL) ... Återskapa

Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen		 ALTER INDEX med alternativet REBUILD tvingar alla rader att placeras i kolumnlagret.
Defragmentera ett columnstore-index ALTER INDEX (Transact-SQL) ALTER INDEX ... REORGANIZE defragmenterar kolumnlagringsindex online.
Kombinera tabeller med kolumnlagringsindex. MERGE (Transact-SQL)

Relaterat innehåll

Så här skapar du ett tomt columnstore-index för:

Mer information om hur du konverterar en befintlig radlagringshög eller ett B-trädindex till ett grupperat kolumnlagringsindex, eller om du vill skapa ett icke-grupperat kolumnlagringsindex, finns i CREATE COLUMNSTORE INDEX (Transact-SQL).