Udostępnij za pośrednictwem

Indeksy kolumnowe w magazynowaniu danych

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)SQL database w Microsoft Fabric

Indeksy magazynu kolumn w połączeniu z partycjonowaniem są niezbędne do tworzenia magazynu danych programu SQL Server. Ten artykuł koncentruje się na kluczowych przypadkach użycia i przykładach projektów magazynowania danych za pomocą aparatu bazy danych SQL.

Najważniejsze funkcje magazynowania danych

Program SQL Server 2016 (13.x) wprowadził następujące funkcje na potrzeby ulepszeń wydajności magazynu kolumn:

  • Funkcja Always On obsługuje wykonywanie zapytań do indeksu magazynu danych kolumnowych na czytelnej pomocniczej replice.
  • Wiele aktywnych zestawów wyników (MARS) obsługuje indeksy kolumnowe.
  • Nowy dynamiczny widok zarządzania sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) zapewnia informacje dotyczące rozwiązywania problemów z wydajnością na poziomie grupy wierszy.
  • Zapytania jednowątkowe na indeksach kolumnowych mogą działać w trybie wsadowym. Wcześniej tylko zapytania wielowątkowe mogły być uruchamiane w trybie wsadowym.
  • Operator SORT działa w trybie wsadowym.
  • Wiele operacji DISTINCT jest uruchamianych w trybie wsadowym.
  • Agregacje okien są teraz uruchamiane w trybie wsadowym dla poziomu zgodności bazy danych 130 i wyższego.
  • Zagregowane wypychanie w celu wydajnego przetwarzania agregacji. Jest to obsługiwane na wszystkich poziomach zgodności bazy danych.
  • Wypychanie predykatu ciągów w celu wydajnego przetwarzania predykatów ciągów. Jest to obsługiwane na wszystkich poziomach zgodności bazy danych.
  • Izolacja migawek dla poziomu zgodności bazy danych 130 lub wyższego.
  • Uporządkowane indeksy klastrowanego magazynu kolumn zostały wprowadzone w programie SQL Server 2022 (16.x). Aby uzyskać więcej informacji, zobacz UTWÓRZ INDEKS COLUMNSTORE i Strojenie wydajności z uporządkowanymi indeksami columnstore. Aby dowiedzieć się więcej o dostępności indeksu uporządkowanego magazynu kolumn, zobacz Dostępność indeksu uporządkowanego magazynu kolumn.

Aby uzyskać więcej informacji na temat nowych funkcji w wersjach i platformach, programu SQL Server i usługi Azure SQL, zobacz Co nowego w indeksach kolumnowych.

Zwiększanie wydajności poprzez łączenie indeksów nieklastrowanych i columnstore.

Począwszy od programu SQL Server 2016 (13.x), można zdefiniować indeksy nieklastrowane magazynu wierszy na klastrowanym indeksie magazynu kolumn.

Przykład: Zwiększanie wydajności wyszukiwania tabel za pomocą indeksu nieklastrowanego

Aby zwiększyć wydajność wyszukiwania tabel w magazynie danych, można utworzyć indeks nieklastrowany przeznaczony do uruchamiania zapytań, które najlepiej działają w przypadku wyszukiwania tabel. Na przykład zapytania, które wyszukują pasujące wartości lub zwracają niewielki zakres wartości, działają lepiej względem indeksu drzewa B, a nie indeksu magazynu kolumn. Nie wymagają pełnego skanowania tabeli za pośrednictwem indeksu magazynu kolumn i szybszego zwracania poprawnego wyniku, wykonując wyszukiwanie binarne za pośrednictwem indeksu drzewa B.

--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  
);  
GO  
  
--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;  
GO  
  
--Add a nonclustered index.  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

Przykład: użyj indeksu nieklastrowanego do wymuszania ograniczenia klucza podstawowego w tabeli typu columnstore

Zgodnie z założeniem tabela kolumnowa nie zezwala na ograniczenie klastrowanego klucza podstawowego. Teraz możesz użyć indeksu nieklastrowanego w tabeli z magazynem kolumn, aby wymusić ograniczenie klucza podstawowego. Klucz podstawowy jest odpowiednikiem ograniczenia UNIQUE w kolumnie innej niż NULL, a program SQL Server implementuje ograniczenie UNIQUE jako indeks nieklastrowany. Łącząc te fakty, poniższy przykład definiuje ograniczenie UNIQUE dla klucza konta kolumny innej niż NULL. Wynikiem jest indeks nieklastrowany, który wymusza ograniczenie klucza podstawowego jako ograniczenie UNIKATOWE w kolumnie innej niż NULL.

Następnie tabela jest konwertowana na indeks klastrowanego magazynu kolumn. Podczas konwersji indeks nieklastrowany jest utrwalany. Wynikiem jest klastrowany indeks magazynu kolumn z indeksem nieklastrowanym, który wymusza ograniczenie klucza podstawowego. Ponieważ każda aktualizacja lub wstawianie w tabeli magazynu kolumn ma również wpływ na indeks nieklastrowany, wszystkie operacje naruszające unikatowe ograniczenie i wartość inną niż NULL powodują niepowodzenie całej operacji.

Wynikiem jest indeks magazynujący kolumny z nieklastrowanym indeksem, który wymusza zachowanie ograniczenia klucza podstawowego na obu indeksach.

--EXAMPLE: Enforce a primary key constraint on a columnstore table.   
  
--Create a rowstore table with a unique constraint.  
--The unique constraint is implemented as a nonclustered index.  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int,  
  
    CONSTRAINT uniq_account UNIQUE (AccountKey)  
);  
  
--Store the table as a columnstore.   
--The unique constraint is preserved as a nonclustered index on the columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account  
  
--By using the previous two steps, every row in the table meets the UNIQUE constraint  
--on a non-NULL column.  
--This has the same end-result as having a primary key constraint  
--All updates and inserts must meet the unique constraint on the nonclustered index or they will fail.  
  
--If desired, add a foreign key constraint on AccountKey.  
  
ALTER TABLE [dbo].[t_account]  
WITH CHECK ADD FOREIGN KEY([AccountKey]) REFERENCES my_dimension(Accountkey);

Zwiększ wydajność, włączając blokowanie na poziomie wiersza i na poziomie grupy wierszy

Aby uzupełnić indeks nieklastrowany w funkcji indeksu magazynu kolumn, program SQL Server 2016 (13.x) oferuje szczegółowe funkcje blokowania dla operacji wybierania, aktualizowania i usuwania. Zapytania mogą być uruchamiane przy użyciu blokady na poziomie wiersza przy wyszukiwaniu w indeksie nieklastrowanym i blokady na poziomie grupy wierszy podczas pełnego skanowania tabeli względem indeksu magazynu kolumn. Umożliwia to osiągnięcie wyższej współbieżności odczytu/zapisu przy użyciu odpowiedniego blokowania na poziomie wiersza i na poziomie grupy wierszy.

--Granular locking example  
--Store table t_account as a columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account  
GO  
  
--Add a nonclustered index for use with this example  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);  
GO  
  
--Look at locking with access through the nonclustered index  
SET TRANSACTION ISOLATION LEVEL repeatable read;  
GO  
  
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

Izolacja migawki i izolacja migawek zatwierdzonych do odczytu

Użyj izolacji migawki (SI), aby zagwarantować spójność transakcyjną i izolację migawek zatwierdzonych do odczytu (RCSI), aby zagwarantować spójność na poziomie instrukcji dla zapytań dotyczących indeksów magazynu kolumn. Dzięki temu zapytania mogą działać bez blokowania piszących dane. Takie nieblokujące zachowanie znacznie zmniejsza prawdopodobieństwo wystąpienia zakleszczeń w złożonych transakcjach. Aby uzyskać więcej informacji, zobacz Izolacja migawek w programie SQL Server.

Powiązana zawartość