Udostępnij za pośrednictwem

Indeksy kolumnowe w magazynowaniu danych

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:

  • Zawsze włączone grupy dostępności obsługują wykonywanie zapytań dotyczących indeksu magazynu kolumn w repliki pomocniczej z możliwością odczytu.
  • 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.
  • Wszystkie zapytania dotyczące indeksów Columnstore mogą być uruchamiane w trybie wsadowym. Wcześniej tylko zapytania równoległe mogły być uruchamiane w trybie wsadowym.
  • Operatory Sort, Distinct Sort i Distinct działają w trybie wsadowym.
  • Agregacje okien są teraz uruchamiane w trybie wsadowym dla poziomu zgodności baz danych 130 i wyższych.
  • Zagregowane wypychanie do wydajnego przetwarzania agregacji. Jest to obsługiwane na wszystkich poziomach zgodności bazy danych.
  • Wypychanie ciągów predykatów dla wydajnego przetwarzania predykató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 SQL Server 2016 (13.x), można utworzyć nieklastrowane indeksy magazynu wierszy na klastrowanym indeksie magazynu kolumnowego.

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 indeksu magazynu kolumn i zwracają poprawny wynik szybciej, wykonując wyszukiwanie binarne w indeksie 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
);

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

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

Ponieważ tabela może zawierać co najwyżej jeden indeks klastrowany, tabela z klastrowanym indeksem magazynu kolumn nie może mieć ograniczenia klastrowanego klucza podstawowego. Aby utworzyć ograniczenie klucza podstawowego w tabeli magazynującej kolumny, należy zadeklarować je jako nieklastrowane.

Poniższy przykład tworzy tabelę z nieklastrowanym ograniczeniem klucza podstawowego, a następnie tworzy indeks klastrowanego magazynu kolumn w tabeli. Ponieważ wszystkie operacje wstawiania lub aktualizacji tabeli magazynu kolumn modyfikują również indeks nieklastrowany, wszystkie operacje naruszające ograniczenie klucza podstawowego powodują niepowodzenie całej operacji.

--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;

Zwiększanie wydajności dzięki włączeniu blokowania na poziomie wiersza i na poziomie grupy wierszy

Aby uzupełnić indeks nieklastrowany w funkcji magazynu kolumn, SQL Server 2016 (13.x) oferuje dokładne możliwości blokowania dla operacji SELECT, UPDATE i DELETE. 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

--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;

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 Poziomy izolacji opartej na wersjonowaniu wierszy w Silniku bazy danych.

Powiązana zawartość