Udostępnij za pośrednictwem

Dostrajanie wydajności za pomocą uporządkowanych indeksów kolumnowych

  • Artykuł

Dotyczy: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceSQL Database w usłudze Microsoft Fabric

Umożliwiając wydajną eliminację segmentów, uporządkowane indeksy magazynu kolumn zapewniają szybsze działanie, pomijając duże partie uporządkowanych danych niespełniających kryteriów zapytania. Ładowanie danych do uporządkowanego indeksu kolumnowego i ich porządkowanie za pomocą ponownego kompilowania indeksu może trwać dłużej niż w indeksie nieuporządkowanym z powodu operacji sortowania, jednak zapytania w uporządkowanych indeksach kolumnowych mogą działać szybciej później.

Gdy użytkownicy wysyłają zapytania do tabeli magazynu kolumn, optymalizator sprawdza minimalne i maksymalne wartości przechowywane w poszczególnych segmentach. Segmenty, które znajdują się poza granicami predykatu zapytania, nie są odczytywane z dysku do pamięci. Zapytanie może zakończyć się szybciej, jeśli liczba segmentów do odczytu i ich całkowity rozmiar są mniejsze.

Aby dowiedzieć się więcej o dostępności uporządkowanego indeksu kolumnowego, zobacz Dostępność uporządkowanego indeksu kolumnowego.

Aby uzyskać więcej informacji na temat ostatnio dodanych funkcji indeksów magazynowanych kolumnowo, zobacz Co nowego w indeksach magazynowanych kolumnowo.

Uporządkowany i nieuporządkowany indeks kolumnowy

W indeksie przechowywania kolumnowego dane w każdej kolumnie każdej grupy wierszy są kompresowane do oddzielnego segmentu. Każdy segment zawiera metadane opisujące wartości minimalne i maksymalne, więc segmenty, które znajdują się poza granicami predykatu zapytania, nie są odczytywane z dysku podczas wykonywania zapytania.

Gdy indeks magazynu kolumn nie jest uporządkowany, konstruktor indeksu nie sortuje danych przed skompresowaniem ich do segmentów. Oznacza to, że mogą wystąpić segmenty z nakładającymi się zakresami wartości, co powoduje, że zapytania odczytują więcej segmentów z dysku i kończą się dłużej.

Podczas tworzenia uporządkowanego indeksu magazynu kolumnowego, aparat bazy danych sortuje istniejące dane według kluczy porządku, które określisz, zanim konstruktor indeksu skompresuje je do segmentów. W przypadku posortowanych danych nakładanie się segmentów jest zmniejszane lub wyeliminowane, co pozwala na bardziej wydajne wyeliminowanie segmentów, a tym samym szybszą wydajność, ponieważ istnieje mniej segmentów do odczytu z dysku.

W zależności od dostępnej pamięci, rozmiaru danych, stopnia równoległości, typu indeksu (klastrowany vs. nieklastrowany) oraz rodzaju budowy indeksu (offline vs. online), sortowanie indeksów kolumnowych może być pełne (bez nakładania się segmentów) lub częściowe (gdy niektóre segmenty się nakładają). Na przykład sortowanie częściowe występuje, gdy dostępna pamięć jest niewystarczająca do pełnego sortowania. Zapytania korzystające z uporządkowanego indeksu magazynu kolumn często są wykonywane szybciej niż w przypadku indeksu, który nie jest uporządkowany, nawet jeśli uporządkowany indeks został skompilowany przy użyciu sortowania częściowego.

Pełne sortowanie jest dostępne dla uporządkowanych klastrowanych indeksów magazynu kolumn utworzonych lub przebudowanych przy użyciu opcji ONLINE = ON i MAXDOP = 1. W takim przypadku sortowanie nie jest ograniczone przez dostępną pamięć, ponieważ używa bazy danych tempdb do przechowywania danych, które nie mieszczą się w pamięci. Może to sprawić, że proces kompilacji indeksu będzie wolniejszy ze względu na dodatkowe operacje we/wy tempdb. Jednak w przypadku ponownego kompilowania indeksu online zapytania mogą nadal używać istniejącego indeksu podczas odbudowy nowego uporządkowanego indeksu.

Można również podać pełne sortowanie dla uporządkowanych klastrowanych i nieklastrowanych indeksów magazynu kolumn utworzonych lub przebudowanych przy użyciu zarówno ONLINE = OFF, jak i MAXDOP = 1 opcji, jeśli ilość danych do sortowania jest wystarczająco mała, aby w pełni zmieścić się w dostępnej pamięci.

We wszystkich innych przypadkach sortowanie w uporządkowanych indeksach magazynu kolumn jest częściowe.

Notatka

Obecnie uporządkowane indeksy magazynu kolumn można utworzyć lub ponownie skompilować w trybie online tylko w usłudze Azure SQL Database i w usłudze Azure SQL Managed Instance za pomocą zawszeup-to-date zasad aktualizacji.

Aby sprawdzić zakresy segmentów dla kolumny i określić, czy istnieją jakieś nakładające się segmenty, użyj następującego zapytania, zastępując symbole zastępcze nazwami schematu, tabeli i kolumn:

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;

Na przykład wynik tego zapytania dla w pełni posortowanego indeksu kolumnowego może wyglądać następująco. Należy pamiętać, że kolumny min_data_id i max_data_id dla różnych segmentów nie nakładają się na siebie.

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

Notatka

W uporządkowanym indeksie magazynu kolumn nowe dane pochodzące z tej samej partii operacji DML lub ładowania danych są sortowane tylko w obrębie tej partii. Nie ma sortowania globalnego, które zawiera istniejące dane w tabeli.

Aby posortować dane w indeksie po wstawieniu nowych danych lub zaktualizowaniu istniejących danych, ponownie skompiluj indeks.

W przypadku ponownego kompilowania w trybie offline partycjonowanego indeksu magazynu kolumn ponowne kompilowanie odbywa się pojedynczo w jednej partycji. Dane w partycji, która jest odbudowywana, są niedostępne do momentu ukończenia ponownego kompilowania dla tej partycji.

Dane pozostają dostępne podczas ponownej kompilacji online. Aby uzyskać więcej informacji, zobacz Wykonywanie operacji indeksowania w trybie online.

Wydajność zapytań

Wzrost wydajności z uporządkowanego indeksu magazynu kolumn zależy od wzorców zapytań, rozmiaru danych, sposobu sortowania danych, fizycznej struktury segmentów i zasobów obliczeniowych dostępnych do wykonywania zapytań.

Zapytania z następującymi wzorcami są zwykle uruchamiane szybciej przy użyciu uporządkowanych indeksów kolumnowych.

  • Zapytania, które mają równość, nierówności przewidywań lub predykaty dotyczące zakresu.
  • Zapytania, w których kolumny predykatu i uporządkowane kolumny CCI są identyczne.

W tym przykładzie tabela T1 ma indeks klastrowany magazynu kolumnowego uporządkowany w sekwencji Col_C, Col_Bi Col_A.

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

Wydajność zapytań 1 i 2 skorzysta bardziej z uporządkowanego indeksu magazynu kolumn niż zapytań 3 i 4, ponieważ odwołują się one do wszystkich uporządkowanych kolumn.

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

Wydajność ładowania danych

Wydajność ładowania danych do tabeli ze uporządkowanym indeksem magazynu kolumn jest podobna do tabeli partycjonowanej. Ładowanie danych może trwać dłużej niż w przypadku nieuporządkowanego indeksu magazynu kolumn z powodu operacji sortowania danych, jednak zapytania z kolei mogą działać szybciej.

Redukcja nakładających się segmentów

Liczba nakładających się segmentów zależy od rozmiaru danych do sortowania, dostępnej pamięci i maksymalnego stopnia równoległości (MAXDOP) podczas kompilacji uporządkowanego indeksu magazynu kolumn. Poniższe strategie zmniejszają nakładanie się segmentów, jednak mogą one sprawić, że proces kompilacji indeksu będzie trwać dłużej.

  • Jeśli kompilacja indeksu online jest dostępna, użyj opcji ONLINE = ON i MAXDOP = 1 podczas tworzenia uporządkowanego klastrowanego indeksu magazynu kolumn. Spowoduje to utworzenie w pełni posortowanego indeksu.
  • Jeśli kompilacja indeksu online jest niedostępna, użyj opcji MAXDOP = 1.
  • Przed załadowaniem przesortuj dane według kluczy sortowania.

Jeśli MAXDOP jest większa niż 1, każdy wątek używany do kompilacji indeksu uporządkowanego magazynu kolumn działa na podzestawie danych i sortuje je lokalnie. Nie ma globalnego sortowania danych posortowanych według różnych wątków. Użycie wątków równoległych może skrócić czas tworzenia indeksu, ale generuje więcej nakładających się segmentów niż w przypadku używania pojedynczego wątku. Użycie jednej operacji jednowątkowej zapewnia najwyższą jakość kompresji. Możesz określić MAXDOP za pomocą polecenia CREATE INDEX.

Przykłady

Sprawdź uporządkowane kolumny i kolejność liczb porządkowych

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;

Utwórz uporządkowany indeks kolumnowy

Indeks kolumnowy uporządkowany w klastrze

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

Nieklastrowany uporządkowany indeks magazynu kolumn:

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

Dodaj lub usuń kolumny porządkowe i odbuduj istniejący indeks columnstore.

Sklastrowany uporządkowany indeks magazynu kolumnowego:

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

Nieklastrowany uporządkowany indeks magazynu kolumn:

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

Tworzenie uporządkowanego klastrowanego indeksu magazynu kolumn w trybie online z pełnym sortowaniem w tabeli stert

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

Ponowne kompilowanie uporządkowanego klastrowanego indeksu magazynu kolumn w trybie online z pełnym sortowaniem

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

Powiązana zawartość