Dela via

Prestandaoptimering med ordnade kolumnlagrade index

  • Artikel

gäller för: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Genom att aktivera effektiv segmenteliminering ger ordnade kolumnlagringsindex snabbare prestanda genom att hoppa över stora mängder sorterade data som inte matchar frågepredikatet. Det kan ta längre tid än i ett ej ordnat index på grund av datasorteringsoperationen att läsa in data i ett ordnat columnstore-index och hålla det ordnat genom indexåteruppbyggnad, men med ordnade kolumnlagringsindex kan frågor köras snabbare efteråt.

När användare kör frågor mot en columnstore-tabell kontrollerar optimeraren de lägsta och högsta värden som lagras i varje segment. Segment som ligger utanför gränserna för frågepredikatet läses inte från disk till minne. En fråga kan slutföras snabbare om antalet segment som ska läsas och deras totala storlek är mindre.

Information om ordnad tillgänglighet för columnstore-index finns i Ordnad kolumnlagringsindextillgänglighet.

Mer information om nyligen tillagda funktioner för kolumnlagringsindex finns i Nyheter i kolumnlagringsindex.

Ordnat jämfört med icke-ordnat kolumnlagringsindex

I ett columnstore-index komprimeras data i varje kolumn i varje radgrupp till ett separat segment. Varje segment innehåller metadata som beskriver dess lägsta och högsta värden, så segment som ligger utanför gränserna för frågepredikatet läses inte från disken under frågeexekveringen.

När ett kolumnlagringsindex inte är sorterat, sorterar inte indexbyggaren data innan den komprimeras till segment. Det innebär att segment med överlappande värdeintervall kan inträffa, vilket gör att frågor läser fler segment från disken och tar längre tid att slutföra.

När du skapar ett ordnat columnstore-index sorterar databasmotorn befintliga data efter de ordernycklar som du anger innan indexverktyget komprimerar dem till segment. Med sorterade data minskas eller elimineras segmentöverlappning, vilket gör det möjligt för frågor att få en effektivare segmenteliminering och därmed snabbare prestanda eftersom det finns färre segment att läsa från disk.

Beroende på det tillgängliga minnet, datastorleken, graden av parallellitet, indextypen (klustrad jämfört med icke-grupperad) och typen av indexversion (offline jämfört med online) kan sorteringen för sorterade kolumnlagringsindex vara full (ingen segmentöverlappning) eller partiell (viss segmentöverlappning). Till exempel sker partiell sortering när det tillgängliga minnet inte räcker till för en fullständig sortering. Frågor som använder ett ordnat columnstore-index körs ofta snabbare än med ett icke-ordnat index även om det ordnade indexet skapades med hjälp av en partiell sortering.

Fullständig sortering tillhandahålls för ordnade grupperade kolumnlagringsindex som skapats eller återskapats med både ONLINE = ON och MAXDOP = 1 alternativ. I det här fallet begränsas sorteringen inte av det tillgängliga minnet eftersom den använder den tempdb databasen för att spilla data som inte får plats i minnet. Detta kan göra indexgenereringsprocessen långsammare på grund av de ytterligare tempdb I/O. Men när ett onlineindex återskapas kan frågor fortsätta att använda det befintliga indexet medan det nya sorterade indexet återskapas.

Fullständig sortering kan också tillhandahållas för sorterade grupperade och icke-grupperade kolumnlagringsindex som skapats eller återskapats med både ONLINE = OFF och MAXDOP = 1 alternativ om mängden data som ska sorteras är tillräckligt liten för att få plats i tillgängligt minne.

I alla andra fall är sorteringen i ordnade kolumnlagringsindex partiell.

Not

För närvarande kan ordnade kolumnlagringsindex skapas eller återskapas online endast i Azure SQL Database och i Azure SQL Managed Instance med uppdateringsprincipen Always-up-to-date.

Om du vill kontrollera segmentintervallen för en kolumn och avgöra om det finns någon segment överlappning använder du följande fråga, ersätter platshållare med ditt schema, tabell och kolumnnamn:

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Till exempel kan utdata från den här frågan för ett fullständigt sorterat columnstore-index se ut så här. Observera att det inte finns någon överlappning i kolumnerna min_data_id och max_data_id för olika segment.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Not

I ett ordnat kolumnlagringsindex sorteras endast de nya data som härrör från samma batch med DML- eller datainläsningsåtgärder inom den batchen. Det finns ingen global sortering som innehåller befintliga data i tabellen.

Om du vill sortera data i indexet när du har infogat nya data eller uppdaterat befintliga data återskapar du indexet.

Om du vill återskapa ett partitionerat kolumnlagringsindex offline görs återskapande en partition i taget. Data i partitionen som återskapas är inte tillgängliga förrän ombyggnaden har slutförts för partitionen.

Data förblir tillgängliga under en online-återuppbyggnad. Mer information finns i Utföra indexåtgärder online.

Frågeprestanda

Prestandavinsten från ett ordnat kolumnlagringsindex beror på frågemönstren, storleken på data, hur väl data sorteras, segmentens fysiska struktur och de beräkningsresurser som är tillgängliga för frågekörning.

Frågor med följande mönster körs vanligtvis snabbare med ordnade kolumnlagringsindex.

  • Frågor som har likhet, ojämlikhet eller intervallpredikat.
  • Frågor där predikatkolumnerna och de ordnade CCI-kolumnerna är desamma.

I det här exemplet har tabell T1 ett grupperat columnstore-index ordnat i sekvensen för Col_C, Col_Boch Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);

Prestanda för fråga 1 och 2 kan dra nytta av ordnat columnstore-index mer än fråga 3 och 4, eftersom de refererar till alla ordnade kolumner.

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';

-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';

Prestanda för dataöverföring

Prestanda för datainläsning i en tabell med ett ordnat kolumnlagringsindex liknar en partitionerad tabell. Inläsning av data kan ta längre tid än med ett icke-ordnat kolumnlagringsindex på grund av datasorteringsåtgärden, men frågor kan köras snabbare efteråt.

Minska segmentöverlappningen

Antalet överlappande segment beror på datamängdens storlek, det minne som finns tillgängligt och parallellismens maximala grad (MAXDOP) vid byggandet av ett ordnat kolumnlagringsindex. Följande strategier minskar segmentöverlappning, men de kan göra så att det tar längre tid att bygga indexet.

  • Om onlineindexet är tillgängligt använder du både ONLINE = ON och MAXDOP = 1 när du skapar ett ordnat grupperat kolumnlagringsindex. Detta skapar ett fullständigt sorterat index.
  • Om online indexbyggande inte är tillgänglig använder du alternativet MAXDOP = 1.
  • Försortera data efter sorteringsnycklarna innan inläsningen.

När MAXDOP är större än 1 arbetar varje tråd som används för att bygga ett ordnat kolumnlagringsindex på en delmängd data och sorterar den lokalt. Det finns ingen global sortering mellan data sorterade efter olika trådar. Att använda parallella trådar kan minska tiden för att skapa indexet, men det genererar fler överlappande segment än när du använder en enda tråd. Genom att använda en enkelltrådad process resulterar i högsta komprimeringskvalitet. Du kan ange MAXDOP med kommandot CREATE INDEX.

Exempel

Sök efter ordnade kolumner och ordningsföljd

SELECT object_name(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
   AND
   c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;

Skapa ett ordnat kolumnlagringsindex

Klustrat ordnat columnstore-index:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

Icke-grupperat ordnat kolumnlagringsindex:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

Lägga till eller ta bort orderkolumner och återskapa ett befintligt ordnat kolumnlagringsindex

Klustrerat ordnat columnstore-index:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Icke-grupperat ordnat kolumnlagringsindex:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Skapa ett ordnat grupperat columnstore-index online med fullständig sortering i en heaptabell

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Återskapa ett ordnat grupperat columnstore-index online med fullständig sortering

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Relaterat innehåll