Udostępnij za pośrednictwem

Kompresja danych

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Programy SQL Server, Azure SQL Database i Azure SQL Managed Instance obsługują kompresję wierszy i stron dla tabel i indeksów składów wierszy oraz obsługują kompresję kolumn i archiwalną kompresję kolumn dla tabel i indeksów składów kolumn.

W przypadku tabel i indeksów rowstore użyj funkcji kompresji danych, aby zmniejszyć rozmiar bazy danych. Oprócz oszczędzania miejsca kompresja danych może pomóc zwiększyć wydajność obciążeń intensywnie korzystających z operacji we/wy, ponieważ dane są przechowywane na mniejszej liczbie stron i zapytania muszą odczytywać mniej stron z dysku. Jednak na serwerze bazy danych potrzebne są dodatkowe zasoby procesora CPU do kompresowania i dekompresowania danych podczas wymieniania danych z aplikacją. Kompresję wierszy i stron można skonfigurować na następujących obiektach bazy danych:

  • Cała tabela przechowywana jako sterta.
  • Cała tabela przechowywana jako indeks klastrowany.
  • Cały indeks nieklastrowany.
  • Cały indeksowany widok.
  • W przypadku partycjonowanych tabel i indeksów można skonfigurować opcję kompresji dla każdej partycji, a różne partycje obiektu nie muszą mieć tego samego ustawienia kompresji.

W przypadku tabel i indeksów magazynu kolumn wszystkie tabele i indeksy magazynu kolumn zawsze używają kompresji magazynu kolumn i nie jest to możliwe do skonfigurowania przez użytkownika. Użyj kompresji archiwalnej magazynu kolumn, aby dodatkowo zmniejszyć rozmiar danych w sytuacjach, w których można sobie pozwolić na dodatkowy czas i zasoby procesora CPU do przechowywania i pobierania danych. Kompresję archiwalną magazynu kolumn można skonfigurować na następujących obiektach bazy danych:

  • Cała tabela magazynu kolumn lub cały klastrowany indeks magazynu kolumn. Ponieważ tabela magazynu kolumn jest przechowywana jako indeks klastrowanego magazynu kolumn, oba podejścia mają te same wyniki.
  • Cały nieklastrowany indeks magazynujący kolumny.
  • W przypadku partycjonowanych tabel magazynu kolumn i indeksów magazynu kolumn można skonfigurować opcję kompresji archiwalnej dla każdej partycji, a różne partycje nie muszą mieć tego samego ustawienia kompresji archiwalnej.

Uwaga / Notatka

Dane można również skompresować przy użyciu formatu algorytmu GZIP. Jest to dodatkowy krok i jest najbardziej odpowiedni do kompresowania części danych podczas archiwizowania starych danych na potrzeby przechowywania długoterminowego. Nie można indeksować danych skompresowanych przy użyciu COMPRESS funkcji . Aby uzyskać więcej informacji, zobacz COMPRESS (Transact-SQL).

Zagadnienia dotyczące kompresji wierszy i stron

W przypadku korzystania z kompresji wierszy i stron należy pamiętać o następujących kwestiach:

  • Szczegóły kompresji danych mogą ulec zmianie bez powiadomienia w dodatku Service Pack lub kolejnych wersjach.

  • Kompresja jest dostępna w usłudze Azure SQL Database

  • Kompresja nie jest dostępna w każdej wersji programu SQL Server. Aby uzyskać więcej informacji, zobacz listę wersji i obsługiwanych funkcji na końcu tej sekcji.

  • Kompresja nie jest dostępna dla tabel systemowych.

  • Kompresja może zezwalać na przechowywanie większej liczby wierszy na stronie, ale nie zmienia maksymalnego rozmiaru wiersza tabeli ani indeksu.

  • Nie można włączyć tabeli pod kątem kompresji, gdy maksymalny rozmiar wiersza oraz obciążenie kompresji przekracza maksymalny rozmiar wiersza wynoszący 8060 bajtów. Na przykład tabela zawierająca kolumny c1 CHAR(8000) i c2 CHAR(53) nie można jej skompresować ze względu na dodatkowe obciążenie kompresji. Gdy jest używany format magazynu vardecimal , sprawdzanie rozmiaru wiersza jest wykonywane po włączeniu formatu. W przypadku kompresji wierszy i stron sprawdzanie rozmiaru wiersza jest wykonywane po początkowym skompresowaniu obiektu, a następnie sprawdzane, gdy każdy wiersz jest wstawiany lub modyfikowany. Kompresja wymusza następujące dwie reguły:

    • Aktualizacja typu o stałej długości musi zawsze zakończyć się powodzeniem.
    • Wyłączenie kompresji danych musi zawsze zakończyć się powodzeniem. Nawet jeśli skompresowany wiersz mieści się na stronie, co oznacza, że jest to mniej niż 8060 bajtów; Program SQL Server uniemożliwia aktualizowanie, które nie mieszczą się w wierszu, gdy nie jest on dekompresowany.

  • Dane poza wierszem nie są kompresowane podczas włączania kompresji danych. Na przykład rekord XML, który jest większy niż 8060 bajtów, używa stron poza wierszem, które nie są kompresowane.

  • Kompresja danych nie ma wpływu na kilka typów danych. Aby uzyskać więcej szczegółów, zobacz Jak kompresja wierszy wpływa na przechowywanie.

  • Po określeniu listy partycji można ustawić typ kompresji na ROW, PAGElub NONE na poszczególnych partycjach. Jeśli lista partycji nie jest określona, wszystkie partycje są ustawiane z właściwością kompresji danych, która jest określona w instrukcji . Po utworzeniu tabeli lub indeksu kompresja danych jest ustawiona na NONE, chyba że określono inaczej. Po zmodyfikowaniu tabeli istniejąca kompresja jest zachowywana, chyba że określono inaczej.

  • Jeśli określisz listę partycji lub partycję, która jest poza zakresem, zostanie wygenerowany błąd.

  • Indeksy nieklastrowane nie dziedziczą właściwości kompresji tabeli. Aby skompresować indeksy, należy jawnie ustawić właściwość kompresji indeksów. Domyślnie ustawienie kompresji indeksów jest ustawione na NONE podczas tworzenia indeksu.

  • Po utworzeniu klastrowanego indeksu na stercie indeks klastrowany dziedziczy stan kompresji sterta, chyba że określono alternatywny stan kompresji.

  • Gdy sterta jest skonfigurowana do kompresji na poziomie strony, strony odbierają kompresję na poziomie strony tylko w następujący sposób:

    • Dane są importowane zbiorczo z włączonymi optymalizacjami zbiorczymi.
    • Dane są wstawiane przy użyciu INSERT INTO ... WITH (TABLOCK) składni, a tabela nie ma indeksu nieklastrowanego.
    • Tabela zostanie ponownie skompilowana przez wykonanie instrukcji ALTER TABLE ... REBUILD z opcją PAGE kompresji.

  • Nowe strony przydzielone w stercie w ramach operacji DML nie używają PAGE kompresji, dopóki sterta nie zostanie ponownie skompilowana. Ponownie skompiluj stertę, usuwając i ponownie stosując kompresję lub tworząc i usuwając indeks klastrowany.

  • Zmiana ustawienia kompresji sterty wymaga przebudowy wszystkich indeksów nieklastrowanych w tabeli, aby miały wskaźniki do nowych lokalizacji wierszy w stercie.

  • Możesz włączyć lub wyłączyć ROW lub PAGE kompresję w trybie online lub offline. Włączenie kompresji na stercie jest pojedyncze wątkowe dla operacji online.

  • Wymagania dotyczące miejsca na dysku dotyczące włączania lub wyłączania kompresji wierszy lub stron są takie same jak w przypadku tworzenia lub ponownego kompilowania indeksu. W przypadku danych partycjonowanych można zmniejszyć ilość miejsca wymaganego przez włączenie lub wyłączenie kompresji dla jednej partycji naraz.

  • Aby określić stan kompresji partycji w tabeli partycjonowanej, wykonaj zapytanie do kolumny data_compression w widoku wykazu sys.partitions.

  • Podczas kompresowania indeksów, strony na poziomie liścia można kompresować zarówno metodą kompresji wierszy, jak i kompresji strony. Strony, które nie są na poziomie liści, nie otrzymują kompresji danych strony.

  • Ze względu na ich rozmiar, typy danych dużego rozmiaru są czasami przechowywane oddzielnie od normalnych danych wierszy na specjalnych stronach przeznaczonych do tego celu. Kompresja danych nie jest dostępna dla danych przechowywanych oddzielnie.

  • Tabele z zaimplementowanym formatem magazynu vardecimal w programie SQL Server 2005 (9.x) zachowują to ustawienie po uaktualnieniu. Kompresję wierszy można zastosować do tabeli, która ma format przechowywania vardecimal. Jednak ponieważ kompresja danych wierszowych jest nadzbiorem formatu przechowywania vardecimal, nie ma powodu, aby zachować format przechowywania vardecimal. Wartości dziesiętne nie uzyskują dodatkowej kompresji, gdy jest używany format przechowywania vardecimal z kompresją wierszy. Kompresję strony można zastosować do tabeli, która ma format przechowywania vardecimal; jednak kolumny w formacie przechowywania vardecimal prawdopodobnie nie osiągną dodatkowej kompresji.

    Uwaga / Notatka

    Wszystkie obsługiwane wersje programu SQL Server obsługują format przechowywania vardecimal; jednak ponieważ kompresja danych osiąga te same cele, ten format jest przestarzały. Ta funkcja zostanie usunięta w przyszłej wersji programu SQL Server. Unikaj używania tej funkcji w nowych pracach programistycznych i zaplanuj modyfikowanie aplikacji, które obecnie korzystają z tej funkcji.

Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server w systemie Windows, zobacz:

Kompresja magazynu kolumn i archiwum magazynu kolumn

Tabele i indeksy kolumnowe są zawsze przechowywane przy użyciu kompresji kolumnowej. Można jeszcze bardziej zmniejszyć rozmiar danych magazynu kolumn, konfigurując dodatkową kompresję o nazwie kompresja archiwalna. Aby przeprowadzić kompresję archiwalną, program SQL Server uruchamia algorytm kompresji Microsoft XPRESS na danych. Dodaj lub usuń kompresję archiwalną przy użyciu następujących typów kompresji danych:

  • Użyj COLUMNSTORE_ARCHIVE kompresji danych do kompresowania danych magazynu kolumnowego z kompresją archiwalną.
  • Użyj COLUMNSTORE kompresji danych do dekompresji kompresji archiwalnej. Wynikowe dane nadal są kompresowane za pomocą kompresji kolumnowej.

Aby dodać kompresję archiwalną, użyj polecenia ALTER TABLE (Transact-SQL) lub ALTER INDEX (Transact-SQL) z opcją REBUILD i DATA COMPRESSION = COLUMNSTORE_ARCHIVE.

Przykład:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4)
);

Aby usunąć kompresję archiwalną i przywrócić dane do kompresji magazynu kolumn, użyj polecenia ALTER TABLE (Transact-SQL) lub ALTER INDEX (Transact-SQL) z opcją REBUILD i DATA COMPRESSION = COLUMNSTORE.

Przykład:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
     DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)
);

W następnym przykładzie na niektórych partycjach ustawiono kompresję danych na columnstore, a na innych partycjach na columnstore archival.

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

Wydajność

Podczas kompresowania indeksów magazynu kolumn z kompresją archiwalną indeks może działać wolniej niż indeksy magazynu kolumn, które nie mają kompresji archiwalnej. Kompresja archiwalna jest używana tylko wtedy, gdy można sobie pozwolić na wykorzystanie dodatkowego czasu i zasobów procesora w celu skompresowania i pobrania danych.

Zaletą kompresji archiwalnej jest zmniejszenie magazynu, co jest przydatne w przypadku danych, do których często nie uzyskuje się dostępu. Jeśli na przykład masz partycję dla każdego miesiąca danych, a większość aktywności dotyczy ostatnich miesięcy, możesz zarchiwizować starsze miesiące, aby zmniejszyć wymagania dotyczące magazynu.

Metadane

Następujące widoki systemowe zawierają informacje o kompresji danych dla indeksów klastrowanych:

Procedura sp_estimate_data_compression_savings (Transact-SQL) może być również stosowana do indeksów magazynu kolumn.

Wpływ na partycjonowane tabele i indeksy

W przypadku korzystania z kompresji danych z partycjonowanych tabel i indeksów należy pamiętać o następujących kwestiach:

  • Gdy partycje są dzielone przy użyciu instrukcji ALTER PARTITION , obie partycje dziedziczą atrybut kompresji danych oryginalnej partycji.

  • Po scaleniu dwóch partycji wynikowa partycja dziedziczy atrybut kompresji danych partycji docelowej.

  • Aby przełączyć partycję, właściwość kompresji danych partycji musi być zgodna z właściwością kompresji tabeli.

  • Istnieją dwie odmiany składni, których można użyć do modyfikowania kompresji partycjonowanej tabeli lub indeksu:

    • Następująca składnia ponownie kompiluje tylko przywołyną partycję:

      ALTER TABLE <table_name>
REBUILD PARTITION = 1 WITH (
    DATA_COMPRESSION = <option>
);

    • Następująca składnia odbudowuje całą tabelę przy użyciu istniejącego ustawienia kompresji dla wszystkich partycji, które nie są wymienione.

      ALTER TABLE <table_name>
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),
    ...
);

    Indeksy partycjonowane stosują tę samą zasadę przy użyciu ALTER INDEX.

  • Po usunięciu indeksu klastrowanego, partycje stert odpowiadające indeksowi zachowują ustawienie kompresji danych, chyba że schemat podziału na partycje zostanie zmodyfikowany. Jeśli schemat partycjonowania zostanie zmieniony, wszystkie partycje zostaną przebudowane do stanu nieskompresowanego. Aby usunąć indeks klastrowany i zmienić schemat partycjonowania, należy wykonać następujące czynności:

    1. Upuść indeks klastrowany.
    2. Zmodyfikuj ALTER TABLE ... REBUILD tabelę przy użyciu opcji określającej opcję kompresji.

    Aby usunąć indeks OFFLINE klastrowany, jest szybką operacją, ponieważ usuwane są tylko górne poziomy indeksów klastrowanych. Po usunięciu ONLINEklastrowanego indeksu program SQL Server musi ponownie skompilować stertę dwa razy, raz dla kroku 1 i raz dla kroku 2.

Jak kompresja wpływa na replikację

W przypadku korzystania z kompresji danych z replikacją należy pamiętać o następujących kwestiach:

  • Gdy agent migawek generuje początkowy skrypt schematu, nowy schemat używa tych samych ustawień kompresji zarówno dla tabeli, jak i jej indeksów. Kompresja nie może być włączona tylko w tabeli, a nie w indeksie.

  • W przypadku replikacji transakcyjnej opcje schematu artykułu określają, jakie obiekty zależne i właściwości muszą być skryptowane. Aby uzyskać więcej informacji, zobacz sp_addarticle.

    Agent dystrybucji nie sprawdza subskrybentów na poziomie podrzędnym, gdy stosuje skrypty. Jeśli wybrano replikację kompresji, tworzenie tabeli na starszych subskrybentach nie uda się. W przypadku topologii mieszanej nie należy włączać replikacji kompresji.

  • W przypadku replikacji scalania poziom zgodności publikacji zastępuje opcje schematu i określa obiekty schematu, które są generowane w skryptach.

    W przypadku topologii mieszanej, jeśli nie jest wymagana obsługa nowych opcji kompresji, poziom zgodności publikacji powinien być ustawiony na wersję subskrybenta na poziomie podrzędnym. Jeśli jest to wymagane, skompresuj tabele na subskrybencie po ich stworzeniu.

W poniższej tabeli przedstawiono ustawienia replikacji kontrolujące kompresję podczas replikacji.

Intencja użytkownika Replikowanie schematu partycji dla tabeli lub indeksu Replikowanie ustawień kompresji Zachowanie skryptowania
Aby replikować schemat partycji i włączyć kompresję dla subskrybenta na konkretnej partycji. Prawda Prawda Zarządza skryptami zarówno schematem partycji, jak i ustawieniami kompresji.
Aby replikować schemat partycji, ale nie kompresować danych u subskrybenta. Prawda Nieprawda Skryptuje schemat partycji, ale nie ustawienia kompresji partycji.
Nie replikować schematu partycji i nie kompresować danych na subskrybencie. Nieprawda Nieprawda Nie skryptuje ustawień partycji ani kompresji.
Aby skompresować tabelę dla subskrybenta, jeśli wszystkie partycje są skompresowane na wydawcy, ale nie replikować schematu partycji. Nieprawda Prawda Sprawdza, czy wszystkie partycje są włączone do kompresji.

Wyłącza kompresję na poziomie tabeli.

Wpływ na inne składniki programu SQL Server

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Kompresja odbywa się w Mechanizmie bazy danych, a dane są prezentowane większości innych składników programu SQL Server w stanie niezkompresowanym. Ogranicza to wpływ kompresji na inne składniki do następujących czynników:

  • Operacje importowania i eksportowania zbiorczego
    • Gdy dane są eksportowane, nawet w formacie natywnym, dane są wyjściowe w formacie nieskompresowanego wiersza. Może to spowodować, że rozmiar wyeksportowanego pliku danych będzie znacznie większy niż dane źródłowe.
    • Gdy dane są importowane, jeśli tabela docelowa została włączona na potrzeby kompresji, aparat bazy danych konwertuje dane na skompresowany format wiersza. Może to spowodować zwiększenie użycia procesora CPU w porównaniu z danymi importowanymi do nieskompresowanej tabeli.
    • Gdy dane są importowane zbiorczo do sterta z kompresją strony, operacja importowania zbiorczego próbuje skompresować dane z kompresją strony po wstawieniu danych.
  • Kompresja nie ma wpływu na tworzenie kopii zapasowych i przywracanie.
  • Kompresja nie ma wpływu na wysyłkę dziennika.
  • Kompresja danych jest niezgodna z rozrzednymi kolumnami. W związku z tym tabele zawierające kolumny rozrzedzone nie mogą być kompresowane ani nie mogą być dodawane do skompresowanej tabeli.
  • Włączenie kompresji może spowodować zmianę planów zapytań, ponieważ dane są przechowywane przy użyciu innej liczby stron i liczby wierszy na stronę.

Treści powiązane