Partycjonowane tabele i indeksy

2025-06-30

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance

Usługi SQL Server, Azure SQL Database i Azure SQL Managed Instance obsługują tabelę i partycjonowanie indeksów. Dane partycjonowanych tabel i indeksów są podzielone na jednostki, które mogą być rozłożone na więcej niż jedną grupę plików w bazie danych lub przechowywane w jednej grupie plików. Gdy w grupie plików istnieje wiele plików, dane są rozłożone na pliki przy użyciu algorytmu wypełniania proporcjonalnego. Dane są partycjonowane w poziomie, dzięki czemu grupy wierszy są mapowane na poszczególne partycje. Wszystkie partycje pojedynczego indeksu lub tabeli muszą znajdować się w tej samej bazie danych. Tabela lub indeks są traktowane jako pojedyncza jednostka logiczna, gdy zapytania lub aktualizacje są wykonywane na danych.

Przed programem SQL Server 2016 (13.x) SP1 tabele partycjonowane i indeksy nie były dostępne w każdej wersji programu SQL Server. Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server, zobacz Wersje i obsługiwane funkcje programu SQL Server 2022. Partycjonowane tabele i indeksy są dostępne we wszystkich warstwach usług usługi Azure SQL Database i Azure SQL Managed Instance.

Partycjonowanie tabel jest również dostępne w dedykowanych pulach SQL w usłudze Azure Synapse Analytics z pewnymi różnicami składniowymi. Więcej informacji znajdziesz w Partycjonowanie tabel w dedykowanej puli SQL.

Ważne

Aparat bazy danych obsługuje domyślnie maksymalnie 15 000 partycji. W wersjach starszych niż SQL Server 2012 (11.x) liczba partycji była domyślnie ograniczona do 1000.

Zalety partycjonowania

Partycjonowanie dużych tabel lub indeksów może mieć następujące korzyści z zarządzania i wydajności.

Przy zachowaniu integralności kolekcji danych można szybko i wydajnie przesyłać podzestawy danych lub uzyskiwać do ich dostępu. Na przykład operacja, taka jak ładowanie danych z systemu OLTP do systemu OLAP, trwa tylko sekundy, zamiast minut i godzin, przez które operacja trwa, gdy dane nie są partycjonowane.
Operacje konserwacji lub przechowywania danych można wykonywać na co najmniej jednej partycji szybciej. Operacje są bardziej wydajne, ponieważ dotyczą tylko tych podzestawów danych, a nie całej tabeli. Możesz na przykład skompresować dane w co najmniej jednej partycji, ponownie skompilować co najmniej jedną partycję indeksu lub obcinać dane w jednej partycji. Możesz również przenieść poszczególne partycje z jednej tabeli do tabeli archiwum.
Wydajność zapytań można poprawić na podstawie typów często uruchamianych zapytań. Na przykład optymalizator zapytań może przetwarzać zapytania typu equijoin między co najmniej dwiema tabelami partycjonowanymi szybciej, gdy kolumny partycjonowania są takie same jak kolumny, na których tabele są łączone. Aby uzyskać więcej informacji, zobacz sekcję dotyczącą zapytań.

Wydajność można poprawić, włączając eskalację blokady na poziomie partycji zamiast całej tabeli. Może to zmniejszyć rywalizację o blokadę w tabeli. Aby zmniejszyć konflikt o blokadę, umożliwiając eskalację blokady do poziomu partycji, ustaw opcję LOCK_ESCALATION polecenia ALTER TABLE na AUTO.

Składniki i pojęcia

Poniższe terminy mają zastosowanie do partycjonowania tabel i indeksów.

Funkcja partition

Funkcja partycji to obiekt bazy danych, który definiuje sposób mapowania wierszy tabeli lub indeksu na zestaw partycji na podstawie wartości określonej kolumny nazywanej kolumną partycjonowania. Każda wartość w kolumnie partycjonowania jest danymi wejściowymi funkcji partycjonowania, która zwraca wartość partycji.

Funkcja partycji definiuje liczbę partycji i granice partycji, które będzie miała tabela. Na przykład, w przypadku tabeli z danymi zamówień sprzedaży, możesz podzielić tabelę na 12 partycji (miesięcznych) na podstawie kolumny daty i godziny, takiej jak data sprzedaży.

Typ zakresu (LEFT lub RIGHT) określa sposób umieszczenia wartości granic funkcji partycji w wynikowych partycjach:

Zakres LEFT określa, że wartość granicy należy do lewej strony interwału wartości granicy, gdy wartości interwału są sortowane przez aparat bazy danych w kolejności rosnącej od lewej do prawej. Innymi słowy, najwyższa wartość graniczna zostanie uwzględniona w partycji.
Zakres RIGHT określa, że wartość granicy należy do prawej strony przedziału wartości granicznych, gdy wartości przedziałów są sortowane przez silnik bazy danych w kolejności rosnącej od lewej do prawej. Innymi słowy, najniższa wartość graniczna zostanie uwzględniona w każdej partycji.

Jeśli nie określono LEFT lub RIGHT, domyślnie używany jest zakres LEFT.

Na przykład następująca funkcja partycji dzieli tabelę lub indeks na 12 partycji, po jednym dla każdego miesiąca wartości roku w kolumnie datetime . Zakres typu RIGHT jest używany wskazując, że wartości graniczne będą służyły jako dolne wartości ograniczające w każdej partycji. Zakresy RIGHT są często prostsze podczas partycjonowania tabeli na podstawie kolumny typów danych datetime lub datetime2, ponieważ wiersze o wartości północy będą przechowywane w tej samej partycji co wiersze z późniejszymi wartościami w tym samym dniu. Podobnie, jeśli używasz typu danych daty i używasz partycji o długości miesiąca lub dłuższej, zakres RIGHT utrzymuje pierwszy dzień miesiąca w tej samej partycji co kolejne dni w tym miesiącu. Pomaga to w precyzyjnym odfiltrowaniu partycji podczas wykonywania zapytań dotyczących całodniowych danych.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',
               '2022-05-01', '2022-06-01', '2022-07-01', '2022-08-01',
               '2022-09-01', '2022-10-01', '2022-11-01', '2022-12-01');

W poniższej tabeli pokazano, jak tabela lub indeks używający tej funkcji partycji zostanie podzielony w kolumnie partycjonowania datecol. 1 lutego jest pierwszym punktem granicznym zdefiniowanym w funkcji, więc działa jako dolna granica partycji 2.

Partycja	1	2	...\	11	12
Wartości	datecol<`2022-02-01 12:00AM`	datecol>= `2022-02-01 12:00AM` AND datecol<`2022-03-01 12:00AM`		datecol>= `2022-11-01 12:00AM` AND col1<`2022-12-01 12:00AM`	datecol>= `2022-12-01 12:00AM`

Zarówno w przypadku ZAKRESU LEWEGO, jak i ZAKRESU PRAWEGO, najdalej po lewej stronie partycja ma minimalną wartość typu danych jako dolną granicę, a najdalej po prawej stronie partycja ma maksymalną wartość typu danych jako górną granicę.

Więcej przykładów funkcji partycji LEFT i RIGHT można znaleźć w artykule CREATE PARTITION FUNCTION (FUNKCJA CREATE PARTITION).

Schemat partycji

Schemat partycji to obiekt bazy danych, który mapuje partycje funkcji partycji na jedną grupę plików lub wiele grup plików.

Znajdź przykładową składnię, aby utworzyć schematy partycji w programie CREATE PARTITION SCHEME.

Grupy plików

Główną przyczyną umieszczenia partycji w wielu grupach plików jest upewnienie się, że można niezależnie wykonywać operacje tworzenia kopii zapasowych i przywracania na partycjach. Jest to spowodowane tym, że można wykonywać kopie zapasowe w poszczególnych grupach plików. W przypadku korzystania z magazynu warstwowego użycie wielu grup plików umożliwia przypisanie określonych partycji do określonych warstw magazynowania, na przykład w celu umieszczania starszych i rzadziej używanych partycji w wolniejszym i mniej kosztownym magazynie. Wszystkie inne korzyści związane z partycjonowaniem mają zastosowanie niezależnie od liczby używanych grup plików lub umieszczania partycji w określonych grupach plików.

Zarządzanie plikami i grupami plików dla tabel partycjonowanych może z czasem zwiększyć złożoność zadań administracyjnych. Jeśli procedury tworzenia kopii zapasowych i przywracania nie korzystają z wielu grup plików, zaleca się użycie jednej grupy plików dla wszystkich partycji. Te same reguły projektowania plików i grup plików mają zastosowanie do partycjonowanych obiektów, które mają zastosowanie do niepartycjonowanych obiektów.

Uwaga / Notatka

Partycjonowanie nie jest w pełni obsługiwane w usłudze Azure SQL Database. Ponieważ tylko PRIMARY grupa plików jest obsługiwana w usłudze Azure SQL Database, wszystkie partycje muszą zostać umieszczone w PRIMARY grupie plików.

Znajdź przykładowy kod do tworzenia grup plików dla programu SQL Server i usługi Azure SQL Managed Instance w programie ALTER DATABASE (Transact-SQL) Opcje plików i grupy plików.

Kolumna partycjonowania

Kolumna tabeli lub indeksu używana przez funkcję partycji do partycjonowania tabeli lub indeksu. Podczas wybierania kolumny partycjonowania mają zastosowanie następujące kwestie:

Obliczone kolumny, które uczestniczą w funkcji partycji, muszą zostać jawnie utworzone jako utrwalone.
- Ponieważ tylko jedna kolumna może być używana jako kolumna partycji, w niektórych przypadkach łączenie wielu kolumn z obliczoną kolumną może być przydatne.
Kolumny wszystkich typów danych, które są odpowiednie do użycia jako kolumny klucza indeksu, mogą być używane jako kolumna partycjonowania, z wyjątkiem timestamp.
Nie można określić kolumn dużych typów danych obiektów (LOB), takich jak ntext, text, image, xml, varchar(max), nvarchar(max)i varbinary(max).
Kolumny typu zdefiniowanego przez użytkownika i typu aliasu środowiska uruchomieniowego języka wspólnego programu Microsoft .NET Framework (CLR) nie mogą być określone.

Aby podzielić obiekt na partycje, określ schemat partycji i kolumnę partycjonowania w instrukcjach CREATE TABLE, ALTER TABLE i CREATE INDEX .

Jeśli podczas tworzenia indeksu nieklastrowanego nie określono partition_scheme_name lub grupy plików, a tabela jest podzielona na partycje, indeks jest umieszczany w tym samym schemacie partycji, przy użyciu tej samej kolumny partycjonowania, co tabela bazowa. Aby zmienić sposób partycjonowania istniejącego indeksu, użyj klauzuli CREATE INDEX z klauzulą DROP_EXISTING. Umożliwia to partycjonowanie indeksu niepartycyjnego, zmiana indeksu partycyjnego na niepartycyjny lub zmienianie schematu partycji indeksu.

Indeks wyrównany

Indeks oparty na tym samym schemacie partycji co odpowiednia tabela. Gdy tabela i jej indeksy są wyrównane, aparat bazy danych może szybko i wydajnie przełączać partycje w tabeli lub poza nią przy zachowaniu struktury partycji zarówno tabeli, jak i jej indeksów. Indeks nie musi uczestniczyć w tej samej nazwie funkcji partycji , aby był wyrównany do jej tabeli podstawowej. Jednak funkcja partycji indeksu i tabeli bazowej muszą być zasadniczo takie same, w tym:

Argumenty funkcji partycji mają ten sam typ danych.
Definiują one tę samą liczbę partycji.
Definiują te same wartości granic dla partycji.

Partycjonowanie indeksów klastrowanych

Podczas partycjonowania indeksu klastrowanego klucz klastrowania musi zawierać kolumnę partycjonowania. Podczas partycjonowania nieunikalnego indeksu klastrowanego, gdy kolumna partycjonowania nie jest jawnie określona w kluczu klastrującym, aparat bazy danych domyślnie dodaje tę kolumnę do listy kluczy indeksów klastrowanych. Jeśli indeks klastrowany jest unikatowy, należy jawnie określić, że klucz indeksu klastrowanego zawiera kolumnę partycjonowania. Aby uzyskać więcej informacji na temat klastrowanych indeksów i architektury indeksów, zobacz Clustered Index Design Guidelines (Wytyczne dotyczące projektowania indeksu klastrowanego).

Partycjonowanie indeksów nieklastrowanych

Podczas partycjonowania unikatowego indeksu nieklastrowanego klucz indeksu musi zawierać kolumnę partycjonowania. Podczas partycjonowania nieklastrowanego indeksu silnik bazy danych domyślnie dodaje kolumnę partycjonowania jako kolumnę niekluczową (dołączoną) indeksu, aby upewnić się, że indeks jest zgodny z tabelą podstawową. Silnik bazy danych nie dodaje kolumny partycjonującej do indeksu, jeśli jest ona już zawarta w indeksie. Aby uzyskać więcej informacji na temat indeksów nieklastrowanych i architektury indeksów, zobacz Nonclustered Index Design Guidelines (Wytyczne dotyczące projektowania indeksów nieklastrowanych).

Indeks niewyrównany

Indeks niezależny jest partycjonowany inaczej niż jego odpowiadająca tabela. Oznacza to, że indeks ma inny schemat partycji , który umieszcza go w oddzielnej grupie plików lub zestawie grup plików z tabeli podstawowej. Projektowanie nieosiowego indeksu partycjonowanego może być przydatne w następujących przypadkach:

Tabela podstawowa nie została partycjonowana.
Klucz indeksu jest unikatowy i nie zawiera kolumny partycjonowania tabeli.
Chcesz, aby tabela podstawowa brała udział w złączach współlokalizowanych z innymi tabelami, używając różnych kolumn dołączenia.

Eliminacja partycji

Proces, za pomocą którego optymalizator zapytań uzyskuje dostęp tylko do odpowiednich partycji w celu spełnienia kryteriów filtrowania zapytania.

Dowiedz się więcej na temat eliminacji partycji i powiązanych pojęć w temacie Rozszerzenia przetwarzania zapytań dotyczące partycjonowanych tabel i indeksów.

Ograniczenia

Zakres funkcji i schematu partycji jest ograniczony do bazy danych, w której zostały utworzone. W bazie danych funkcje partycji znajdują się w oddzielnej przestrzeni nazw niż inne funkcje.
Jeśli jakiekolwiek wiersze w tabeli partycjonowanej mają wartości NULL w kolumnie partycjonowania, te wiersze są umieszczane w najbardziej lewej partycji. Jeśli jednak wartość NULL jest określona jako pierwsza wartość granicy, a RANGE RIGHT jest określone w definicji funkcji partycji, to najbardziej lewa partycja pozostaje pusta, a wartości NULL zostają umieszczone w drugiej partycji.

Wytyczne dotyczące wydajności

Aparat bazy danych obsługuje maksymalnie 15 000 partycji na tabelę lub indeks. Jednak użycie ponad 1000 partycji ma wpływ na pamięć, partycjonowane operacje indeksu, polecenia DBCC i zapytania. W tej sekcji opisano wpływ na wydajność korzystania z ponad 1000 partycji i przedstawiono obejścia zgodnie z potrzebami.

W przypadku maksymalnie 15 000 partycji dozwolonych na partycjonowaną tabelę lub indeks można przechowywać dane przez długi czas trwania w jednej tabeli. Należy jednak przechowywać dane tylko tak długo, jak są potrzebne, i zachować równowagę między wydajnością a liczbą partycji.

Użycie pamięci i wytyczne

Zalecamy użycie co najmniej 16 GB pamięci RAM, jeśli jest używana duża liczba partycji. Jeśli system nie ma wystarczającej ilości pamięci, instrukcje języka manipulowania danymi (DML), instrukcje Języka definicji danych (DDL) i inne operacje mogą zakończyć się niepowodzeniem z powodu niewystarczającej ilości pamięci. Systemy z 16 GB pamięci RAM, które uruchamiają wiele procesów intensywnie korzystających z pamięci, mogą napotkać brak pamięci podczas operacji na dużej liczbie partycji. W związku z tym tym więcej pamięci masz ponad 16 GB, tym mniej prawdopodobne, że wystąpią problemy z wydajnością i pamięcią.

Ograniczenia pamięci mogą mieć wpływ na wydajność lub zdolność aparatu bazy danych do tworzenia indeksu partycjonowanego. Jest to szczególnie przypadek, gdy indeks nie jest wyrównany do tabeli podstawowej lub nie jest zgodny z indeksem klastrowanym, jeśli tabela ma już indeks klastrowany.

W programie SQL Server i usłudze Azure SQL Managed Instance można zwiększyć index create memory (KB) opcję konfiguracji serwera. Aby uzyskać więcej informacji, zobacz Konfiguracja serwera: indeks tworzenia pamięci. W przypadku usługi Azure SQL Database rozważ tymczasowe lub trwałe zwiększenie celu poziomu usługi dla bazy danych w witrynie Azure Portal w celu przydzielenia większej ilości pamięci.

Operacje indeksu partycjonowanego

Tworzenie i ponowne kompilowanie nieprzywiązanych indeksów w tabeli z ponad 1000 partycjami jest możliwe, ale nie jest obsługiwane. Może to spowodować obniżenie wydajności lub nadmierne zużycie pamięci podczas tych operacji.

Tworzenie i ponowne kompilowanie wyrównanych indeksów może trwać dłużej w miarę wzrostu liczby partycji. Zalecamy, aby w tym samym czasie nie uruchamiać wielu poleceń tworzenia i ponownego kompilowania indeksu, ponieważ mogą wystąpić problemy z wydajnością i pamięcią.

Gdy aparat bazy danych wykonuje sortowanie w celu utworzenia indeksów partycjonowanych, najpierw tworzy jedną tabelę sortowania dla każdej partycji. Następnie kompiluje tabele sortowania w odpowiedniej grupie plików każdej partycji lub w tempdb, jeśli określono opcję indeksu SORT_IN_TEMPDB. Każda tabela sortowania wymaga minimalnej ilości pamięci do skompilowania. Podczas tworzenia indeksu partycjonowanego, który jest zgodny z tabelą podstawową, tabele sortowania są kompilowane pojedynczo przy użyciu mniejszej ilości pamięci. Jednak podczas tworzenia niezrównoważonego indeksu partycjonowanego tabele sortowania są tworzone równocześnie. W związku z tym musi być wystarczająca ilość pamięci do obsługi tych współbieżnych sortowań. Większa liczba partycji, tym większa ilość wymaganej pamięci. Minimalny rozmiar każdej tabeli sortowania dla każdej partycji to 40 stron z 8 kilobajtami na stronę. Na przykład indeks podzielony na partycje ze 100 partycjami wymaga wystarczającej ilości pamięci do szeregowego sortowania 4000 (40 * 100) stron w tym samym czasie. Jeśli ta pamięć jest dostępna, operacja kompilacji powiedzie się, ale wydajność może ucierpieć. Jeśli ta pamięć nie jest dostępna, operacja kompilacji zakończy się niepowodzeniem. Alternatywnie wyrównany indeks partycjonowany z 100 partycjami wymaga tylko wystarczającej ilości pamięci do sortowania 40 stron, ponieważ sortowania nie są wykonywane w tym samym czasie.

W przypadku indeksów wyrównanych i niezrównoważonych wymaganie pamięci może być większe, jeśli aparat bazy danych używa równoległości zapytań do operacji kompilacji na komputerze wieloprocesorowym. Wynika to z tego, że im większy stopień równoległości (DOP), tym większe jest wymaganie dotyczące pamięci. Jeśli na przykład aparat bazy danych ustawia DOP na 4, niezrównany indeks partycjonowany ze 100 partycjami wymaga wystarczającej ilości pamięci dla czterech procesorów do sortowania 4000 stron w tym samym czasie, czyli 16 000 stron. Jeśli indeks partycjonowany jest wyrównany, wymaganie dotyczące pamięci zostanie zmniejszone do czterech procesorów sortujących 40 stron, czyli łącznie 160 (4 * 40) stron. Możesz użyć opcji indeksu MAXDOP , aby ręcznie zmniejszyć stopień równoległości.

Polecenia DBCC

W przypadku większej liczby partycji polecenia DBCC, takie jak DBCC CHECKDB i DBCC CHECKTABLE , mogą trwać dłużej w miarę wzrostu liczby partycji.

Kwerendy

Po partycjonowaniu tabeli lub indeksu zapytania korzystające z eliminacji partycji mogą mieć porównywalną lub lepszą wydajność z większą liczbą partycji. Zapytania, które nie korzystają z eliminacji partycji, mogą trwać dłużej w miarę wzrostu liczby partycji.

Załóżmy na przykład, że tabela zawiera 100 milionów wierszy i kolumn A, Bi C.

W scenariuszu 1 tabela jest podzielona na 1000 partycji w kolumnie A.
W scenariuszu 2 tabela jest podzielona na 10 000 partycji w kolumnie A.

Zapytanie w tabeli, które zawiera klauzulę filtrującą na kolumnie A, wykona eliminację partycji i przeskanuje jedną z nich. To samo zapytanie może działać szybciej w scenariuszu 2, ponieważ w partycji jest mniej wierszy do skanowania. Zapytanie z klauzulą WHERE, która filtruje kolumnę B, przeskanuje wszystkie partycje. Zapytanie może działać szybciej w scenariuszu 1 niż w scenariuszu 2, ponieważ do skanowania jest mniej partycji.

Zapytania korzystające z operatorów, takich jak TOP lub MAX/MIN w kolumnach innych niż kolumna partycjonowania, mogą mieć obniżoną wydajność z partycjonowaniem, ponieważ należy ocenić wszystkie partycje.

Podobnie zapytanie wykonujące wyszukiwanie pojedynczego wiersza lub skanowanie w małym zakresie będzie trwać dłużej w przypadku tabeli partycjonowanej niż w przypadku tabeli niepartycyjnej, jeśli predykat zapytania nie zawiera kolumny partycjonowania, ponieważ będzie musiała wykonać tyle operacji wyszukiwania lub skanowania, jak istnieją partycje. Z tego powodu partycjonowanie rzadko poprawia wydajność w systemach OLTP, w których takie zapytania są powszechne.

Jeśli często uruchamiasz zapytania obejmujące łączenie równoważne między dwiema lub więcej tabelami partycjonowanymi, ich kolumny partycjonowania powinny być takie same jak kolumny, na których są łączone tabele. Ponadto tabele lub ich indeksy powinny być sortowane. Oznacza to, że używają tej samej funkcji partycji o tej samej nazwie lub używają różnych funkcji partycji, które są zasadniczo takie same, w tym, że:

Mają taką samą liczbę parametrów, które są używane do partycjonowania, a odpowiednie parametry są tymi samymi typami danych.
Zdefiniuj tę samą liczbę partycji.
Zdefiniuj te same wartości granic dla partycji.

W ten sposób optymalizator zapytań może przetwarzać łączenia szybciej, ponieważ można łączyć same partycje. Jeśli zapytanie łączy dwie tabele, które nie zostały sprzężone lub nie są partycjonowane w polu sprzężenia, obecność partycji może rzeczywiście spowolnić przetwarzanie zapytań zamiast przyspieszyć.

Przydatne może być użycie $PARTITION w niektórych zapytaniach. Dowiedz się więcej w $PARTITION.

Aby uzyskać więcej informacji na temat obsługi partycji w przetwarzaniu zapytań, w tym równoległej strategii wykonywania zapytań dla partycjonowanych tabel i indeksów oraz dodatkowych najlepszych rozwiązań, zobacz Ulepszenia przetwarzania zapytań dotyczące partycjonowanych tabel i indeksów.

Zmiany zachowania w obliczeniach statystyk podczas operacji indeksu partycjonowanego

W usługach Azure SQL Database, Azure SQL Managed Instance i SQL Server 2012 (11.x) i nowszych statystyki nie są tworzone przez skanowanie wszystkich wierszy w tabeli podczas tworzenia lub odbudowy indeksu partycjonowanego. Zamiast tego optymalizator zapytań używa domyślnego algorytmu próbkowania do generowania statystyk.

Po uaktualnieniu bazy danych z partycjonowanym indeksem z wersji programu SQL Server niższej niż 2012 (11.x) możesz zauważyć różnicę w danych histogramu dla tych indeksów. Ta zmiana zachowania może mieć wpływ na wydajność zapytań. Aby uzyskać statystyki dotyczące partycjonowanych indeksów, przeskanując wszystkie wiersze w tabeli, użyj CREATE STATISTICS lub UPDATE STATISTICS z klauzulą FULLSCAN.