Kolumnlagringsindex i datalager

Artikel
2025-04-09

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)SQL Database i Microsoft Fabric

Kolumnlagringsindex, tillsammans med partitionering, är viktiga för att skapa ett SQL Server-informationslager. Den här artikeln fokuserar på viktiga användningsfall och exempel för datalagerdesign med SQL Database Engine.

Viktiga funktioner för datalagerhantering

SQL Server 2016 (13.x) introducerade dessa funktioner för prestandaförbättringar i columnstore:

Always On-tillgänglighetsgrupper tillåter frågeställningar på ett columnstore-index på en läsbar sekundär replik.
Flera aktiva resultatuppsättningar (MARS) stöder kolumnlagringsindex.
En ny dynamisk hanteringsvy sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) ger information om prestandafelsökning på radgruppsnivå.
Alla frågor i columnstore-index kan köras i batchläge. Tidigare kunde endast parallella frågor köras i batchläge.
Operatorerna Sort, Distinct Sort och Distinct körs i batchläge.
Aggregat för fönsterfunktioner körs nu i batchläge för databaskompatibilitetsnivå 130 och senare.
Aggregattryck för effektiv bearbetning av aggregat. Detta stöds på alla databaskompatibilitetsnivåer.
Optimering av predikatberäkning för effektiv bearbetning av strängpredikat. Detta stöds på alla databaskompatibilitetsnivåer.
Isolering av ögonblicksbilder för databaskompatibilitetsnivå 130 och senare.
Ordnade grupperade columnstore-index introducerades med SQL Server 2022 (16.x). Mer information finns i CREATE COLUMNSTORE INDEX och Performance tuning with ordered columnstore indexes. Information om tillgänglighet för ordnat columnstore-index finns i Ordnad kolumnindextillgänglighet.

Mer information om nya funktioner i versioner och plattformar för SQL Server och Azure SQL finns i Nyheter i kolumnlagringsindex.

Förbättra prestanda genom att kombinera icke-grupperade index och kolumnlagringsindex

Från och med SQL Server 2016 (13.x) kan du skapa icke-grupperade index för radarkiv i ett grupperat kolumnlagringsindex.

Exempel: Förbättra effektiviteten för tabellsökningar med ett icke-grupperat index

För att förbättra effektiviteten för tabellsökningar i ett informationslager kan du skapa ett icke-grupperat index som är utformat för att köra frågor som presterar bäst med tabellsökningar. Till exempel fungerar frågor som letar efter matchande värden eller returnerar ett litet värdeintervall bättre mot ett B-trädindex i stället för ett kolumnlagringsindex. De kräver inte en fullständig genomsökning av kolumnlagringsindexet och returnerar rätt resultat snabbare genom att göra en binär sökning via ett B-trädindex.

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.

--Create the table
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int
);

--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;

--Add a nonclustered index.
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

Exempel: Använd ett icke-grupperat index för att framtvinga en primärnyckelbegränsning för en kolumnlagringstabell

Eftersom en tabell kan ha högst ett klustrat index kan en tabell med ett grupperat columnstore-index inte ha en klustrad primärnyckelbegränsning. Om du vill skapa en primär nyckelbegränsning för en kolumntabell måste du deklarera den som icke-klustrad.

I följande exempel skapas en tabell med en icke-klustrad primärnyckelbegränsning och skapar sedan ett grupperat kolumnlagringsindex i tabellen. Eftersom alla infogningar eller uppdateringar i kolumnlagertabellen också ändrar det icke-klustrade indexet, misslyckas hela åtgärden om någon operation bryter mot den primära nyckelbegränsningen.

--Create a primary key constraint on a columnstore table.

--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int,
    CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);

--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;

Förbättra prestanda genom att aktivera låsning på radnivå och radgruppsnivå

SQL Server 2016 (13.x) erbjuder detaljerad låsfunktion för SELECT, UPDATE och DELETE åtgärder för att komplettera det icke-klustrade indexet på en kolumnlagringsindexfunktion. Frågor kan köras med låsning på radnivå för indexsökningar mot ett icke-grupperat index och låsning på radgruppsnivå vid fullständiga tabellgenomsökningar mot kolumnlagringsindexet. Använd detta för att uppnå högre samtidighet mellan läsning och skrivning med hjälp av låsning på radnivå och radgruppsnivå på lämpligt sätt.

--Granular locking example
--Store table t_account as a columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account

--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN
    -- The query plan chooses a seek operation on the nonclustered index
    -- and takes the row lock
    SELECT * 
    FROM t_account 
    WHERE AccountKey = 100;
COMMIT TRAN;

Isolering av ögonblicksbilder och lästillägnad ögonblicksbildisolering

Använd ögonblicksbildisolering (SI) för att garantera transaktionskonsekvens och skrivskyddad ögonblicksbildisolering (RCSI) för att garantera konsekvens på instruktionsnivå för frågor om kolumnlagringsindex. Detta gör att frågorna kan köras utan att blockera dataskrivare. Det här icke-blockerande beteendet minskar också sannolikheten för dödlägen för komplexa transaktioner avsevärt. Mer information finns i Radversionsbaserade isoleringsnivåer i databasmotorn.

Dela via