Udostępnij za pośrednictwem

Indeksy kolumnowe — wskazówki dotyczące ładowania danych

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w Microsoft Fabric

Opcje i zalecenia dotyczące ładowania danych do indeksu magazynu kolumnowego przy użyciu standardowych metod zbiorczego ładowania danych SQL oraz kroplowego wstawiania. Ładowanie danych do indeksu kolumnowego jest istotną częścią dowolnego procesu magazynowania danych, ponieważ wprowadza dane do indeksu w ramach przygotowań do analizy.

Czy dopiero zaczynasz korzystać z indeksów kolumnowych? Zobacz Indeksy magazynu kolumn — omówienie i Architektura indeksu magazynu kolumn.

Co to jest ładowanie zbiorcze?

Ładowanie zbiorcze odnosi się do sposobu dodawania dużej liczby wierszy do magazynu danych. Jest to najbardziej wydajny sposób przenoszenia danych do indeksu magazynowania kolumnowego, ponieważ przetwarza partie wierszy. Ładowanie zbiorcze wypełnia grupy wierszy do maksymalnej pojemności i kompresuje je bezpośrednio do magazynu kolumn. Tylko wiersze na końcu załadunku, które nie osiągają minimalnej liczby 102 400 wierszy na grupę wierszy, przechodzą do magazynu delty.

Aby przeprowadzić ładowanie zbiorcze, możesz użyć bcp, Integration Services lub wybrać wiersze z tabeli tymczasowej.

Zrzut ekranu przedstawiający ładowanie do klastrowanego indeksu magazynującego kolumny.

Jak sugeruje diagram, załadowanie zbiorcze:

  • Nie presortuje danych. Dane są wstawiane do grup wierszy w kolejności odbierania.
  • Jeśli rozmiar partii to >102400, wiersze są ładowane bezpośrednio do skompresowanych grup wierszy. Należy wybrać rozmiar >partii =102400 w celu wydajnego importowania zbiorczego, ponieważ można uniknąć przenoszenia wierszy danych do grup wierszy różnicowych, zanim wiersze zostaną ostatecznie przeniesione do skompresowanych grup wierszy przez wątek w tle, krotkę mover (TM).
  • Jeśli rozmiar < partii 102 400 lub pozostałe wiersze to < 102 400, wiersze są ładowane do grup wierszy różnicowych.

Uwaga

W tabeli typu rowstore z indeksem typu columnstore bezklastrowym program SQL Server zawsze wstawia dane do tabeli bazowej. Dane nigdy nie są wstawiane bezpośrednio do indeksu columnstore.

Ładowanie zbiorcze ma następujące wbudowane optymalizacje wydajności:

  • Obciążenia równoległe: Można mieć wiele równoczesnych ładowań zbiorczych (bcp lub wstawienie zbiorcze), z których każde ładuje oddzielny plik danych. W przeciwieństwie do zbiorczego ładowania danych w magazynie wierszowym do programu SQL Server, nie trzeba określać TABLOCK, ponieważ każdy wątek importu zbiorczego ładuje dane wyłącznie do oddzielnych grup wierszy (skompresowanych lub deltowych) z wyłączną blokadą.

  • Ograniczone rejestrowanie: Dane ładowane bezpośrednio do skompresowanych grup wierszy prowadzą do znacznego zmniejszenia rozmiaru dziennika. Na przykład, jeśli dane zostały skompresowane 10x, odpowiedni log transakcji jest mniej więcej 10x mniejszy, bez konieczności użycia TABLOCK lub modelu odzyskiwania Bulk-logged lub Simple. Wszystkie dane, które przechodzą do delta rowgroup, są w pełni rejestrowane. Obejmuje to wszystkie rozmiary partii, które są mniejsze niż 102 400 wierszy. Najlepszym rozwiązaniem jest użycie rozmiaru wsadowego >= 102400. Ponieważ TABLOCK nie jest wymagane, można załadować dane równolegle.

  • Minimalne rejestrowanie: Dalsze zmniejszenie rejestrowania można uzyskać, jeśli spełnisz wymagania wstępne dotyczące minimalnego rejestrowania. Jednak w przeciwieństwie do ładowania danych do magazynu wierszy, TABLOCK prowadzi do X (wyłącznej) blokady w tabeli, a nie do BU (blokady aktualizacji zbiorczej), i dlatego równoległe ładowanie danych nie może być wykonane. Aby uzyskać więcej informacji na temat blokowania, zobacz Blokowanie i przechowywanie wersji wierszy.

  • Optymalizacja blokowania: Blokada X grupy wierszy jest automatycznie uzyskiwana podczas ładowania danych do skompresowanej grupy wierszy. Jednak podczas zbiorczego ładowania do grupy wierszy różnicowych blokada X jest uzyskiwana dla grupy wierszy, ale silnik bazy danych nadal uzyskuje blokady strony i zakresu, ponieważ blokada X grupy wierszy nie jest częścią hierarchii blokad.

Jeśli indeks B-tree nie jest klasterowany w indeksie magazynu kolumn, nie ma blokady ani optymalizacji rejestrowania dla samego indeksu, ale optymalizacje w klastrowanym indeksie magazynu kolumn zgodnie z wcześniejszym opisem mają zastosowanie.

Planowanie rozmiarów obciążenia zbiorczego w celu zminimalizowania grup wierszy różnicowych

Indeksy kolumnowe działają najlepiej, gdy większość wierszy jest kompresowana w indeksie kolumnowym, a nie w grupach wierszy delta. Najlepiej jest rozmiarować obciążenia tak, aby wiersze były przekazywane bezpośrednio do magazynu kolumnowego i pomijały magazyn delty tak bardzo, jak to możliwe.

Te scenariusze opisują, kiedy załadowane wiersze trafiają bezpośrednio do składowania kolumnowego lub kiedy trafiają do składowania delta. W tym przykładzie każda grupa wierszy może zawierać 102 400–1 048 576 wierszy na grupę wierszy. W praktyce maksymalny rozmiar grupy wierszy może być mniejszy niż 1048 576 wierszy, gdy występuje wykorzystanie pamięci.

Wiersze do ładowania zbiorczego Wiersze dodane do skompresowanej grupy wierszy Wiersze dodane do grupy wierszy różnicowych
102,000 0 102,000
145,000 145,000

Rozmiar grupy wierszy: 145 000		 0
1,048,577 1 048 576

Rozmiar grupy wierszy: 1 048 576.		 1
2,252,152 2,252,152

Rozmiary grup wierszy: 1 048 576, 1 048 576, 155 000.		 0

Poniższy przykład przedstawia wyniki ładowania 1048 577 wierszy do tabeli. Wyniki pokazują, że jedna skompresowana grupa wierszy w magazynie kolumn (jako skompresowane segmenty kolumn) oraz 1 wiersz w magazynie delta.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id,
    state, state_desc, total_rows, deleted_rows, size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats;

Zrzut ekranu przedstawiający grupę wierszy i deltastore na potrzeby ładowania wsadowego.

Użyj tabeli przejściowej, aby zwiększyć wydajność

Jeśli ładujesz dane tylko po to, aby je przygotować przed uruchomieniem dalszych przekształceń, ładowanie danych do tabeli stertowej jest znacznie szybsze niż do tabeli zgrupowanego magazynu kolumnowego. Ponadto ładowanie danych do [tabeli tymczasowej][Tymczasowe] będzie również ładowane znacznie szybciej niż ładowanie tabeli do magazynu trwałego.

Typowym wzorcem ładowania danych jest załadowanie danych do tabeli przejściowej, wykonanie pewnych przekształceń, a następnie załadowanie ich do tabeli docelowej przy użyciu następującego polecenia:

INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

To polecenie wczytuje dane do indeksu magazynu kolumn w podobny sposób jak bcp lub "bulk insert", ale w jednej partii. Jeśli liczba wierszy w tabeli < przejściowej 102400, wiersze są ładowane do grupy wierszy różnicowych. W przeciwnym razie wiersze są ładowane bezpośrednio do skompresowanej grupy wierszy. Jednym z kluczowych ograniczeń było to, że ta INSERT operacja była jednowątkowa. Aby załadować dane równolegle, można utworzyć wiele tabel przejściowych lub problem INSERT/SELECT z nienakładającymi się zakresami wierszy z tabeli przejściowej. To ograniczenie nie dotyczy programu SQL Server 2016 (13.x). Następujące polecenie ładuje dane z tabeli przejściowej równolegle, ale należy określić wartość TABLOCK. Może się wydawać to sprzeczne z wcześniejszymi stwierdzeniami dotyczącymi ładunku zbiorczego, jednak kluczową różnicą jest to, że równoległe ładowanie danych z tabeli przejściowej odbywa się w ramach tej samej transakcji.

INSERT INTO [<columnstore index>] WITH (TABLOCK)
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Podczas ładowania danych do klastrowanego indeksu kolumnowego z tabeli przejściowej dostępne są następujące optymalizacje:

  • Optymalizacja dzienników: Skrócone rejestrowanie podczas ładowania danych do skompresowanej grupy wierszy.
  • Optymalizacja blokowania: Podczas ładowania danych do skompresowanej grupy wierszy uzyskuje się blokadę X w grupie wierszy. Jednak podczas zbiorczego ładowania do grupy wierszy delta, blokada X jest uzyskiwana dla grupy wierszy, ale aparat bazy danych nadal uzyskuje blokady strony i zakresu, ponieważ blokada X grupy wierszy nie jest częścią hierarchii blokad.

Jeśli masz co najmniej jeden indeks nieklastrowany, nie ma optymalizacji blokowania ani logowania dla samego indeksu, ale optymalizacje dla klastrowanego indeksu magazynu kolumn, jak opisano wcześniej, wciąż są dostępne.

Co to jest stopniowe wstawianie?

Wstawienie trickle odnosi się do sposobu, w jaki poszczególne wiersze są przenoszone do indeksu magazynu kolumn. Trickle inserts używa instrukcji INSERT INTO . W przypadku wstawiania kroplowego wszystkie wiersze trafiają do magazynu różnicowego. Jest to przydatne w przypadku niewielkiej liczby wierszy, ale nie jest to praktyczne w przypadku dużych obciążeń.

INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)

Uwaga

Współbieżne wątki korzystające z polecenia INSERT INTO do wstawiania wartości do klastrowanego indeksu magazynu kolumnowego mogą wstawiać wiersze do tej samej grupy wierszy deltastore.

Gdy grupa wierszy zawiera 1048 576 wierszy, grupa wierszy różnicowych oznaczona jako zamknięta, ale nadal jest dostępna dla zapytań i operacji aktualizacji/usuwania, ale nowo wstawione wiersze przechodzą do istniejącej lub nowo utworzonej grupy wierszy magazynu delty. Istnieje wątek w tle nazywany Tuple Mover (TM), który kompresuje zamknięte grupy wierszy delta okresowo co 5 minut. Możesz jawnie wywołać następujące polecenie, aby skompresować zamkniętą grupę delta.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE

Jeśli chcesz wymusić zamknięcie i skompresowanie delta grupy wierszy, możesz wykonać następujące polecenie. Możesz uruchomić to polecenie, jeśli skończysz ładować wiersze i nie oczekujesz żadnych nowych wierszy. Bezpośrednio zamykając i kompresując grupę wierszy różnicowych, można oszczędzić miejsce na magazynowanie i zwiększyć wydajność zapytań analitycznych. Najlepszym rozwiązaniem jest wywołanie tego polecenia, jeśli nie oczekujesz wstawienia nowych wierszy.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)

Jak przebiega ładowanie do tabeli partycjonowanej

W przypadku danych partycjonowanych silnik bazy danych najpierw przypisuje każdy wiersz do partycji, a następnie wykonuje operacje magazynowania kolumn na danych w określonej partycji. Każda partycja ma własne grupy wierszy i co najmniej jedną grupę wierszy różnicowych.

Powiązana zawartość