Kolumnlagringsindex – Designvägledning

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ågor bör uppdateras och tas bort mindre än 10% av raderna.
• Har jag fakta- och dimensionstabeller för ett informationslager?
• Behöver jag utföra analys av en transaktionstung 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 efter data, söker efter ett visst värde eller efter frågor om ett litet värdeintervall. 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.

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

Det klustrade kolumnlagringsindexet är inte bara ett index, utan också den primära tabelllagringen. 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 får du inte 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 som 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 grupperat kolumnlagringsindex 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 grupperat columnstore-index i följande scenarier:

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

Ett ordnat grupperat columnstore-index 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 mycket enkelt att prova eftersom du kan aktivera ett icke-grupperat columnstore-index utan ändringar i OLTP-programmet.

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. Genom att göra detta kommer framtida säkerhetskopior att hoppa ö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. Data blir långsammare för frågeprestanda, vilket är 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 deltaradgrupper.

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 tvinga deltaradgrupperna in i kolumnlagret. Men om det här är de enda raderna i kolumnlagringsindexet blir 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 kommer MAXDOP-1 sannolikt att 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 fler radgrupper hoppas över.

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.

Relaterade uppgifter

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

Relaterat innehåll

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

• SQL Server eller SQL Database, hänvisa till CREATE TABLE (Transact-SQL).
• Azure Synapse Analytics, se CREATE TABLE (Azure Synapse Analytics).

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).

• Vad som är nytt i kolumnlagringsindex
• Kolumnbutiksindex i datalager
• Kolumnbutiksindex – Sökningsprestanda