Dela via

Columnstore-index – Frågeprestanda

  • Artikel

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

Den här artikeln innehåller rekommendationer för att uppnå hög frågeprestanda med columnstore-index.

Kolumnlagringsindex kan ge upp till 100 gånger bättre prestanda för analys- och datalagerarbetsbelastningar och upp till 10 gånger bättre datakomprimering än traditionella radlagringsindex. De här rekommendationerna hjälper dina frågor att uppnå den snabba frågeprestanda som columnstore-index är utformade för att tillhandahålla.

Rekommendationer för att förbättra frågeprestanda

Här följer några rekommendationer för att uppnå den prestanda som de högpresterande kolumnlagringsindexen är utformade för att erbjuda.

1. Ordna data för att eliminera fler radgrupper från en fullständig tabellgenomsökning

  • Välj noga infogningsordning. I vanliga fall i det traditionella informationslagret infogas data verkligen i tidsordning och analys görs i tidsdimension. Till exempel genom att analysera försäljningen per kvartal. För den här typen av arbetsbelastning sker radgruppseliminering automatiskt. I SQL Server 2016 (13.x) kan du ta reda på antalet radgrupper som utlämnas som en del av frågebearbetningen.

  • Använd ett grupperat rowstore-index. Om det vanliga frågepredikatet finns i en kolumn (till exempel C1) som inte är relaterat till infogningsordningen skapar du ett radlagerklustrerat index i kolumnen C1. Släpp sedan det grupperade rowstore-indexet och skapa ett grupperat columnstore-index. Om du skapar det klustrade kolumnlagringsindexet explicit med MAXDOP = 1sorteras det resulterande klustrade kolumnlagringsindexet perfekt efter kolumn C1. Om du anger MAXDOP = 8visas överlappning av värden över åtta radgrupper. För ett icke-grupperat kolumnlagringsindex (NCCI) sorteras raderna redan efter den klustrade indexnyckeln om tabellen har ett grupperat rowstore-index. I det här fallet ordnas det icke-klustrerade kolumnbutiksindexet också automatiskt. Ett kolumnlagringsindex bevarar inte radordningen. När nya rader infogas eller äldre rader uppdateras kan du behöva upprepa processen eftersom analysfrågans prestanda kan försämras.

  • Implementera tabellpartitionering. Du kan partitionera columnstore-indexet och sedan använda partitionseliminering för att minska antalet radgrupper som ska genomsökas. En faktatabell lagrar till exempel köp som görs av kunder. Ett vanligt frågemönster är att hitta kvartalsköp med customer. I det här fallet kombinerar du insert order-kolumnen med partitionering på kolumnen customer. Varje partition innehåller rader för varje customer, ordnade efter infogning. Överväg också att använda tabellpartitionering om det finns ett behov av att ta bort äldre data från kolumnarkivet. Att växla ut och trunkera partitioner som inte behövs är en effektiv strategi för att ta bort data utan att generera fragmentering.

  • Undvik att ta bort stora mängder data. Att ta bort komprimerade rader från en radgrupp är inte en synkron åtgärd. Det skulle vara dyrt att dekomprimera en radgrupp, ta bort raden och sedan komprimera den igen. När du tar bort data från komprimerade radgrupper genomsöks därför dessa radgrupper fortfarande, även om de returnerar färre rader. Om antalet borttagna rader för flera radgrupper är tillräckligt stort för att sammanfogas till färre radgrupper ökar omorganiseringen av kolumnarkivet indexets kvalitet och frågeprestanda förbättras. Om databorttagningsprocessen vanligtvis tömmer hela radgrupper bör du överväga att använda tabellpartitionering. Växla ut partitioner som inte behövs längre och trunkera dem i stället för att ta bort rader.

    Not

    Från och med SQL Server 2019 (15.x) får tuppelflyttaren hjälp av en bakgrundssammanslagningsuppgift. Den här uppgiften komprimerar automatiskt mindre OPEN delta-radgrupper som har funnits under en tid, enligt ett internt tröskelvärde, eller sammanfogar KOMPRIMERADE radgrupper där ett stort antal rader har tagits bort. Detta förbättrar kolumnlagringsindexets kvalitet över tid. Om du behöver ta bort stora mängder data från kolumnlagringsindexet bör du överväga att dela upp åtgärden i mindre borttagningsbatch över tid. Med batchbearbetning kan bakgrundssammanslagningsaktiviteten hantera uppgiften att slå samman mindre radgrupper och förbättra indexkvaliteten. Sedan behöver du inte schemalägga underhållsperioder för indexomorganisering efter borttagning av data. För mer information om columnstore-terminologi och koncept, se Columnstore-index: Översikt.

2. Planera för tillräckligt med minne för att skapa kolumnlagringsindex parallellt

Att skapa ett columnstore-index är som standard en parallell åtgärd om inte minnet är begränsat. Att skapa indexet parallellt kräver mer minne än att skapa indexet seriellt. När det finns gott om minne, tar skapandet av ett columnstore-index ungefär 1,5 gånger så lång tid som att bygga ett B-träd på samma kolumner.

Det minne som krävs för att skapa ett kolumnlagringsindex beror på antalet kolumner, antalet strängkolumner, graden av parallellitet (DOP) och dataegenskaperna. Om tabellen till exempel har färre än en miljon rader använder SQL Server bara en tråd för att skapa kolumnlagringsindexet.

Om tabellen har fler än en miljon rader, men SQL Server inte kan få tillräckligt med minne för att skapa indexet med MAXDOP, minskar SQL Server automatiskt MAXDOP efter behov. I vissa fall måste DOP minskas till ett för att kunna skapa indexet under begränsat minne i tillgänglig minnestilldelning.

Sedan SQL Server 2016 (13.x) fungerar frågan alltid i batchläge. I tidigare versioner används batchkörning endast när DOP är större än en.

Förklaring av prestanda för columnstore

Kolumnlagringsindex ger höga frågeprestanda genom att kombinera bearbetning i minnesinternt batchläge med tekniker som avsevärt minskar I/O-kraven. Eftersom analysfrågor genomsöker ett stort antal rader är de vanligtvis I/O-bundna, och därför är det viktigt att minska I/O under frågekörningen för utformningen av kolumnlagringsindex. När data har lästs in i minnet är det viktigt att minska antalet minnesinterna åtgärder.

Kolumnlagringsindex minskar I/O och optimerar minnesinterna åtgärder genom hög datakomprimering, kolumnlagringseliminering, radgruppseliminering och batchbearbetning.

Datakomprimering

Kolumnlagringsindex uppnår upp till 10 gånger större datakomprimering än radlagringsindex. Detta minskar avsevärt det I/O som krävs för att köra analysfrågor och förbättrar därför frågeprestandan.

  • Kolumnlagringsindex läser komprimerade data från disken, vilket innebär att färre byte data måste läsas in i minnet.

  • Kolumnlagringsindex lagrar data i komprimerad form i minnet, vilket minskar I/O genom att undvika att läsa samma data i minnet. Med 10 gånger komprimering kan kolumnlagringsindex till exempel behålla 10 gånger mer data i minnet, jämfört med att lagra data i okomprimerad form. Med mer data i minnet är det mer troligt att kolumnlagringsindexet hittar de data som behövs i minnet utan onödiga läsningar från disken.

  • Kolumnlagringsindex komprimerar data efter kolumner i stället för efter rader, uppnår höga komprimeringshastigheter och minskar storleken på data som lagras på disken. Varje kolumn komprimeras och lagras separat. Data i en kolumn har alltid samma datatyp och tenderar att ha liknande värden. Datakomprimeringstekniker för columnstore är bra på att uppnå högre komprimeringshastigheter när värdena är liknande.

En faktatabell lagrar till exempel kundadresser och har en kolumn för country-region. Det totala antalet möjliga värden är färre än 200. Vissa av dessa värden upprepas många gånger. Om faktatabellen har 100 miljoner rader komprimeras kolumnen country-region enkelt och kräver lite lagringsutrymme. Komprimering rad för rad kan inte dra nytta av likheten mellan kolumnvärden på det här sättet och måste använda fler byte för att komprimera värdena i kolumnen country-region.

Kolumneliminering

Kolumnlagringsindex hoppar över läsning i kolumner som inte krävs för frågeresultatet. Kolumneliminering minskar ytterligare in- och utdata vid frågekörning och förbättrar därför frågeprestanda.

  • Kolumneliminering är möjligt eftersom data ordnas och komprimeras kolumn för kolumn. När data däremot lagras rad för rad lagras kolumnvärdena i varje rad fysiskt tillsammans och kan inte enkelt separeras. Frågeprocessorn måste läsa på en hel rad för att hämta specifika kolumnvärden, vilket ökar I/O eftersom extra data läss in i minnet i onödan.

Om en tabell till exempel har 50 kolumner och frågan bara använder fem av dessa kolumner hämtar kolumnlagringsindexet bara de fem kolumnerna från disken. Den hoppar över läsningen i de övriga 45 kolumnerna, vilket minskar I/O med ytterligare 90%, förutsatt att alla kolumner är av liknande storlek. Om samma data lagras i ett radlager måste frågeprocessorn läsa de återstående 45 kolumnerna.

Radgruppseliminering

För fullständiga tabellgenomsökningar matchar en stor andel av data vanligtvis inte frågepredikatvillkoren. Med hjälp av metadata kan kolumnlagringsindexet hoppa över läsningen i de radgrupper som inte innehåller data som krävs för frågeresultatet, allt utan faktisk I/O. Den här möjligheten, som kallas radgruppseliminering, minskar I/O för fullständiga tabellgenomsökningar och förbättrar därför frågeprestandan.

När behöver ett columnstore-index utföra en fullständig tabellgenomsökning?

Från och med SQL Server 2016 (13.x) kan du skapa ett eller flera vanliga icke-klustrade radlagrings- eller B-trädindex på ett klustrat kolumnlagringsindex. De icke-klustrade B-trädindexen kan påskynda en fråga som har ett likhetspredikat eller ett predikat med ett litet intervall av värden. För mer komplicerade predikat kan frågeoptimeraren välja en fullständig tabellgenomsökning. Utan möjligheten att hoppa över radgrupper kan en fullständig tabellgenomsökning vara tidskrävande, särskilt för stora tabeller.

När drar en analysfråga nytta av radgruppseliminering för en fullständig tabellgenomsökning?

Till exempel modellerar ett detaljhandelsföretag sina försäljningsdata med en faktatabell med grupperat kolumnstore-index. Varje ny försäljning lagrar olika attribut för transaktionen, inklusive det datum då en produkt säljs. Intressant nog, även om kolumnlagringsindex inte garanterar en sorterad ordning, läses rader i den här tabellen in i en datumsorterad ordning. Med tiden växer den här tabellen. Även om detaljhandelsföretaget kan behålla försäljningsdata under de senaste 10 åren kanske en analysfråga bara behöver beräkna ett aggregering för det senaste kvartalet. Kolumnlagringsindex kan eliminera åtkomsten till data för de föregående 39 kvartalen genom att bara titta på metadata för datumkolumnen. Det här är en 97% minskning av mängden data som läss in i minnet och bearbetas.

Vilka radgrupper hoppas över i en fullständig tabellgenomsökning?

För att avgöra vilka rader som ska elimineras använder kolumnlagringsindexet metadata för att lagra lägsta och högsta värden för varje kolumnsegment för varje radgrupp. När inget av kolumnsegmentens intervall uppfyller frågepredikatvillkoren hoppas hela radgruppen över utan att göra någon faktisk I/O. Detta fungerar eftersom data vanligtvis läses in i en sorterad ordning. Även om radsortering inte garanteras finns liknande datavärden ofta inom samma radgrupp eller en närliggande radgrupp.

Mer information om radgrupper finns i Designriktlinjer för Columnstore Index.

Körning av batchläge

Batch-lägeskörning bearbetar rader i grupper, vanligtvis upp till 900 åt gången, för att förbättra effektiviteten. Frågan SELECT SUM(Sales) FROM SalesData beräknar till exempel den totala försäljningen från tabellen SalesData. I batchläge bearbetar frågemotorn data i grupper om 900 rader. Den här metoden minskar kostnaden för metadataåtkomst och andra typer av omkostnader genom att sprida dem över alla rader i en batch, i stället för att medföra kostnader för varje rad. Dessutom fungerar batchläget med komprimerade data när det är möjligt och tar bort några av de exchange-operatorer som används i radläge, vilket avsevärt påskyndar analysfrågorna.

Alla frågekörningsoperatorer kan inte köras i batchläge. Till exempel körs åtgärder för datamanipuleringsspråk (DML), till exempel infoga, ta bort eller uppdatera en rad i taget. Batchlägesoperator som Scan, Join, Aggregate, Sort och andra kan förbättra frågeprestanda. Eftersom kolumnlagringsindexet introducerades i SQL Server 2012 (11.x) finns det ett varaktigt arbete med att utöka de operatorer som kan köras i batchläge. I följande tabell visas de operatorer som körs i batchläge enligt produktversionen.

Operatorer för batchläge När den används SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) och SQL Database1 Kommentarer
DML-åtgärder (infoga, ta bort, uppdatera, sammanfoga) Nej Nej Nej DML är inte en batchlägesåtgärd eftersom den inte är parallell. Även när vi aktiverar batchbearbetning i serieläge ser vi inga större vinster genom att tillåta att DML bearbetas i batchläge.
columnstore-indexgenomsökning SKANNA Inte tillgängligt Ja Ja För columnstore-index kan vi skicka predikatet till SCAN-noden.
columnstore index Scan (icke-klustrerad) SKANNA Ja Ja Ja Ja
indexsökning Inte tillgängligt Inte tillgängligt Nej Vi utför en sökåtgärd via ett icke-grupperat B-trädindex i radläge.
beräkna skalar Uttryck som utvärderas till ett skalärt värde. Ja Ja Ja Precis som alla operatorer i batchläge finns det vissa begränsningar för datatypen.
Sammanfogning UNION och UNION ALL Nej Ja Ja
filter Tillämpa predikat Ja Ja Ja
hash-matchning Hash-baserade aggregeringsfunktioner, yttre hash-anslutning, höger hash-anslutning, vänster hash-anslutning, höger inre anslutning, vänster inre anslutning Ja Ja Ja Begränsningar för sammansättning: ingen min/max för strängar. Tillgängliga sammansättningsfunktioner är sum/count/avg/min/max.
Begränsningar för koppling: inga matchande typkopplingar för icke-heltalstyper.
sammanfoga koppling Nej Nej Nej
frågor med flera trådar Ja Ja Ja
kapslade loopar Nej Nej Nej
entrådade sökfrågor som körs med MAXDOP 1 Nej Nej Ja
entrådade frågor med en seriell frågeplan Nej Nej Ja
sortera ORDER BY-sats på SCAN med ett kolumnbutiksindex. Nej Nej Ja
toppsortering Nej Nej Ja
fönsteraggregeringar Inte tillgängligt Inte tillgängligt Ja Ny operator i SQL Server 2016 (13.x).

1 gäller för SQL Server 2016 (13.x), SQL Database Premium-nivåer, standardnivåer – S3 och högre samt alla nivåer för virtuell kärna och Analysplattformssystem (PDW)

Mer information finns i arkitekturguiden för frågebearbetning.

Aggregerad nedtryckning

En normalt körflöde för aggregerad beräkning, för att hämta de kvalificerande raderna från SCAN-noden och aggregera värdena i Batch-läge. Detta ger bra prestanda, från och med SQL Server 2016 (13.x), men aggregeringsåtgärden kan skickas till SCAN-noden. Aggregerad pushdown förbättrar prestandan för aggregerade beräkningar efter storleksordning utöver körning av Batch-läge, förutsatt att följande villkor uppfylls:

  • Aggregaten är MIN, MAX, SUM, COUNT och COUNT(*).
  • Aggregeringsoperatorn måste vara placerad ovanpå SCAN-noden eller SCAN-noden med GROUP BY.
  • Den här aggregeringen är inte en distinkt aggregering.
  • Samlingskolumnen är inte en strängkolumn.
  • Samlingskolumnen är inte en virtuell kolumn.
  • Indata- och utdatatypen måste vara något av följande och måste rymmas inom 64 bitar:
    • tinyint, int, bigint, smallint, bit
    • smallmoney, money, decimal och numerisk med precision <= 18
    • smalldate, date, datetime, datetime2, time

Till exempel görs aggregerad pushdown i båda följande frågor:

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Pushdown för strängpredikat

När du utformar ett informationslagerschema är den rekommenderade schemamodellering att använda star-schema eller snowflake-schema som består av en eller flera faktatabeller och många dimensionstabeller.

Tips

Den faktatabellen lagrar affärsmätningar eller transaktioner och dimensionstabell lagra de dimensioner som fakta behöver analyseras över. Mer information om dimensionsmodellering finns i Dimensionsmodellering i Microsoft Fabric.

Ett exempel på fakta kan till exempel vara en post som representerar en försäljning av en viss produkt i en viss region, medan dimensionen representerar en samling regioner, produkter och så vidare. Fakta- och dimensionstabellerna är länkade via en primär-/främmande nyckelrelation. De vanligaste analysfrågorna ansluter en eller flera dimensionstabeller till faktatabellen.

Låt oss överväga en dimensionstabell Products. En typisk primärnyckel är ProductCode, som vanligtvis representeras som sträng. För att förbättra prestandan för frågor är bästa praxis att skapa en surrogatnyckel, vanligtvis en heltals kolumn, för att referera till raden i dimensionstabellen från faktatabellen.

Kolumnlagringsindexet kör analysfrågor med kopplingar och predikat som involverar numeriska eller heltalsbaserade nycklar effektivt. SQL Server 2016 (13.x) förbättrade prestandan för analysfrågor med strängbaserade kolumner avsevärt genom att trycka ned predikaten med strängkolumner till SCAN-noden.

Pushdown för strängpredikat utnyttjar den primära/sekundära ordlistan som skapats för kolumner för att förbättra frågeprestandan. Tänk dig till exempel ett strängkolumnsegment i en radgrupp som består av 100 distinkta strängvärden. Varje distinkt strängvärde refereras till 10 000 gånger i genomsnitt, förutsatt att det finns en miljon rader. Med optimering av strängpredikat beräknar frågeutförande predikat mot värden i ordlistan. Om predikatet kvalificerar sig kvalificeras alla rader som refererar till ordlistevärdet automatiskt. Detta förbättrar prestandan på två sätt:

  • Endast den kvalificerade raden returneras, vilket minskar antalet rader som behöver flöda ut från genomsökningsnoden.
  • Antalet strängjämförelser minskas. I det här exemplet krävs endast 100 strängjämförelser jämfört med 1 miljon jämförelser. Det finns vissa begränsningar:
    • Ingen strängpredikatoptimering för delta rowgroups. Det finns ingen ordlista för kolumner i deltaradgrupper.
    • Ingen push-nedtryckning av strängpredikat om ordlistan överskrider 64 KB-poster.
    • Uttryck som utvärderar null stöds inte.

Segmenteliminering

Val av datatyp kan ha en betydande inverkan på frågeprestandabaserade vanliga filterpredikat för frågor i kolumnlagringsindexet.

I kolumnlagringsdata består radgrupper av kolumnsegment. Det finns metadata med varje segment för att möjliggöra snabb eliminering av segment utan att läsa dem. Denna segmenteliminering gäller för numeriska, datum- och tidsdatatyper, och datetimeoffset datatyper med skala mindre än eller lika med två. Från och med SQL Server 2022 (16.x) utökas funktionerna för segmenteliminering till sträng-, binär-, guid-datatyper och datetimeoffset datatyp för skalning som är större än två.

När du har uppgraderat till en version av SQL Server som stöder strängmin/max segmenteliminering (SQL Server 2022 (16.x) och senare) drar kolumnlagringsindexet inte nytta av den här funktionen förrän den återskapas med hjälp av en ALTER INDEX REBUILD eller CREATE INDEX WITH (DROP_EXISTING = ON).

Segmenteliminering gäller inte för LOB-datatyper, till exempel längden på datatypen (max).

För närvarande stöder endast SQL Server 2022 (16.x) och senare eliminering av radgrupper i klustrade kolumnlager för prefixet av LIKE-predikat, till exempel column LIKE 'string%'. Segmenteliminering stöds inte för icke-prefixanvändning av LIKE, till exempel column LIKE '%string'.

Ordnade kolumnlagringsindex kan också dra nytta av segmenteliminering, särskilt för kolumner med strängar. I sorterade kolumnlagringsindex är segmenteliminering på den första kolumnen i indexnyckeln mest effektiv eftersom den är sorterad. Prestandavinster på grund av segmenteliminering på andra kolumner i tabellen är mindre förutsägbara. Mer information om ordnade kolumnlagringsindex finns i Använd ett ordnat kolumnlagringsindex för stora informationslagertabeller. Information om tillgänglighet för ett ordnat Columnstore-index hittar du i Ordnad kolumnindextillgänglighet.

Med hjälp av frågeanslutningsalternativet SET STATISTICS IOkan du visa segmenteliminering i praktiken. Leta efter utdata som följande för att indikera att segmenteliminering har inträffat. Radgrupper består av kolumnsegment, så detta kan tyda på segmenteliminering. Följande SET STATISTICS IO utdataexempel på en fråga, ungefär 83% data hoppades över av frågan:

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Relaterat innehåll