SQL Server i Azure SQL: przewodnik po architekturze i projektowaniu indeksów.

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

Źle zaprojektowane indeksy i brak indeksów są podstawowymi źródłami wąskich gardeł aplikacji bazy danych. Projektowanie wydajnych indeksów ma kluczowe znaczenie dla osiągnięcia dobrej wydajności bazy danych i aplikacji. Ten przewodnik projektowania indeksu zawiera informacje dotyczące architektury indeksu oraz najlepsze rozwiązania ułatwiające projektowanie efektywnych indeksów spełniających potrzeby aplikacji.

W tym przewodniku założono, że czytelnik ma ogólną wiedzę na temat dostępnych typów indeksów. Aby uzyskać ogólny opis typów indeksów, zobacz Indeksy.

W tym przewodniku omówiono następujące typy indeksów:

Podstawowy format przechowywania	Typ indeksu
Magazyn wierszy na dysku
	Sklastrowany
	Nieklastrowane
	Niepowtarzalny
	Filtrowane
Przechowywanie kolumnowe
	Klastrowany magazyn kolumn
	Nieklastrowany magazyn kolumn
Zoptymalizowane pod kątem pamięci
	Hasz
	Memory-Optimized nieklastrowane

Aby uzyskać informacje o indeksach XML, zobacz Indeksy XML (SQL Server) i Selektywne indeksy XML (SXI).

Aby uzyskać informacje o indeksach przestrzennych, zobacz Omówienie indeksów przestrzennych.

Aby uzyskać informacje o indeksach pełnotekstowych, zobacz Wypełnianie indeksów Full-Text.

Podstawy projektowania indeksu

Pomyśl o regularnej książce: na końcu książki znajduje się indeks, który pomaga szybko zlokalizować informacje w książce. Indeks jest posortowaną listą słów kluczowych, a obok każdego słowa kluczowego jest zestaw numerów stron wskazujących strony, na których można znaleźć każde słowo kluczowe.

Indeks magazynu wierszy nie różni się: jest to uporządkowana lista wartości i dla każdej wartości znajdują się wskaźniki do stron danych, na których znajdują się te wartości. Sam indeks jest przechowywany na stronach nazywanych stronami indeksu. W zwykłej książce, jeśli indeks obejmuje wiele stron, a ty musisz znaleźć wskaźniki do wszystkich stron, które zawierają słowo SQL, musisz na przykład przerzucać strony aż do zlokalizowania strony indeksu zawierającej słowo kluczowe SQL. Stamtąd podążasz za wskazówkami do wszystkich stron książki. Można to dodatkowo zoptymalizować, jeśli na samym początku indeksu utworzysz jedną stronę zawierającą alfabetyczną listę miejsc, w których można znaleźć każdą literę. Na przykład: "Od A do D - strona 121", "Od E do G - strona 122" itd. Ta dodatkowa strona wyeliminowałaby etap przeglądania indeksu w celu znalezienia miejsca rozpoczęcia. Taka strona nie istnieje w zwykłych książkach, ale istnieje w indeksie rowstore. Ta pojedyncza strona jest nazywana stroną główną indeksu. Strona główna to strona początkowa struktury drzewa używanej przez indeks. Podążając za analogią drzewa, strony końcowe zawierające wskaźniki do rzeczywistych danych są określane jako "strony liściowe" drzewa.

Indeks jest strukturą na dysku lub w pamięci skojarzona z tabelą lub widokiem, która przyspiesza pobieranie wierszy z tabeli lub widoku. Indeks wierszowy zawiera klucze utworzone na podstawie co najmniej jednej kolumny w tabeli lub widoku. W przypadku indeksów magazynu wierszy te klucze są przechowywane w strukturze drzewa (drzewo B+), która umożliwia silnikowi bazy danych szybkie i wydajne odnalezienie wiersza lub wierszy powiązanych z wartościami kluczy.

Indeks wierszowy przechowuje dane logicznie zorganizowane jako tabela z wierszami i kolumnami, a fizycznie przechowywane w formacie danych wierszowym o nazwie rowstore¹ lub w formacie danych kolumnowym o nazwie columnstore.

Wybór odpowiednich indeksów dla bazy danych i jej obciążenia to złożone zadanie znalezienia równowagi pomiędzy szybkością zapytań i kosztem aktualizacji. Wąskie indeksy magazynu wierszy oparte na dysku lub indeksy z kilkoma kolumnami w kluczu indeksu wymagają mniej miejsca na dysku i mniejszych nakładów związanych z konserwacją. Szerokie indeksy natomiast obejmują więcej zapytań. Przed znalezieniem najbardziej wydajnego indeksu może być konieczne eksperymentowanie z kilkoma różnymi projektami. Indeksy można dodawać, modyfikować i odrzucać bez wpływu na schemat bazy danych lub projekt aplikacji. W związku z tym nie należy wahać się eksperymentować z różnymi indeksami.

Optymalizator zapytań w Aparacie Bazy Danych niezawodnie wybiera najbardziej efektywny indeks w większości przypadków. Ogólna strategia projektowania indeksów powinna zapewniać różne indeksy do wyboru przez optymalizator zapytań i ufać mu, że podejmie właściwą decyzję. Skraca to czas analizy i zapewnia dobrą wydajność w różnych sytuacjach. Aby sprawdzić, które indeksy optymalizator zapytań używa dla określonego zapytania, w programie SQL Server Management Studio w menu Zapytanie wybierz pozycję Uwzględnij rzeczywisty plan wykonania.

Nie zawsze utożsamiaj użycie indeksu z dobrą wydajnością i dobrą wydajnością przy wydajnym użyciu indeksu. Jeśli użycie indeksu zawsze pomogło uzyskać najlepszą wydajność, zadanie optymalizatora zapytań byłoby proste. W rzeczywistości nieprawidłowy wybór indeksu może spowodować mniej niż optymalną wydajność. W związku z tym zadaniem optymalizatora zapytań jest wybranie indeksu lub kombinacji indeksów, tylko wtedy, gdy poprawia wydajność i aby uniknąć indeksowanego pobierania, gdy utrudnia wydajność.

¹ Przechowywanie wierszowe to tradycyjny sposób przechowywania danych relacyjnej tabeli. Magazyn wierszy oznacza tabelę, w której format przechowywania danych to sterta, drzewo B+ (indeks klastrowany) lub tabela zoptymalizowana pod kątem pamięci. Rejestr wierszy bazujący na dysku wyklucza tabele zoptymalizowane pod kątem pamięci.

Zadania projektowania indeksu

Następujące zadania składają się na naszą zalecaną strategię projektowania indeksów:

1. Poznaj cechy samej bazy danych.

   • Czy na przykład jest to baza danych przetwarzania transakcji online (OLTP) z częstymi modyfikacjami danych, które muszą utrzymać wysoką przepływność? Tabele i indeksy zoptymalizowane pod kątem pamięci są szczególnie odpowiednie dla tego scenariusza, zapewniając projekt bez blokad. Aby uzyskać więcej informacji, zobacz Indeksy dotyczące tabel Memory-Optimized lub wytyczne dotyczące projektowania indeksów nieklastrowanychMemory-Optimized i wytyczne dotyczące projektowania indeksów mieszających w tym przewodniku.
   • Czy jest to przykład bazy danych systemu obsługi decyzji (DSS) lub bazy danych magazynowania danych (OLAP), która musi szybko przetwarzać bardzo duże zestawy danych? Indeksy kolumnowe są szczególnie odpowiednie dla typowych zestawów danych hurtowni danych. Indeksy magazynu kolumn mogą przekształcać środowisko magazynowania danych dla użytkowników, umożliwiając szybszą wydajność typowych zapytań magazynowania danych, takich jak filtrowanie, agregowanie, grupowanie i zapytania sprzężenia gwiazdy. Aby uzyskać więcej informacji, zobacz Indeksy magazynu kolumn: omówienie lub Wytyczne dotyczące projektowania indeksu magazynu kolumn w tym przewodniku.

2. Poznaj cechy najczęściej używanych zapytań. Na przykład wiedząc, że często używane zapytanie łączy co najmniej dwie tabele, pomaga określić najlepszy typ indeksów do użycia.

3. Poznaj charakterystykę kolumn używanych w zapytaniach. Na przykład indeks jest idealny dla kolumn, które mają typ danych całkowitych, a także są kolumnami unikatowymi lub niepustymi. W przypadku kolumn, które mają dobrze zdefiniowane podzestawy danych, można użyć indeksu filtrowanego w programie SQL Server 2008 (10.0.x) i nowszych wersjach. Aby uzyskać więcej informacji, zobacz Wytyczne dotyczące projektowania filtrowanego indeksu w tym przewodniku.

4. Określ, które opcje indeksu mogą zwiększyć wydajność podczas tworzenia lub konserwacji indeksu. Na przykład utworzenie indeksu klastrowanego w istniejącej dużej tabeli skorzystałoby z opcji indeksu ONLINE . Opcja ONLINE umożliwia równoczesne działanie danych bazowych w czasie tworzenia lub odbudowy indeksu. Aby uzyskać więcej informacji, zobacz Ustawianie opcji indeksu.

5. Określ optymalną lokalizację przechowywania indeksu.

   Indeks nieklastrowany może być przechowywany w tej samej grupie plików co tabela bazowa lub w innej grupie plików. Lokalizacja magazynu indeksów może zwiększyć wydajność zapytań, zwiększając wydajność operacji we/wy dysku. Na przykład przechowywanie indeksu nieklastrowanego w grupie plików, która znajduje się na innym dysku niż grupa plików tabeli, może zwiększyć wydajność, ponieważ wiele dysków można odczytywać w tym samym czasie. Alternatywnie indeksy klastrowane i nieklastrowane mogą używać schematu partycji w wielu grupach plików. Podczas rozważania partycjonowania określ, czy indeks powinien być wyrównany, czyli partycjonowany w zasadniczo taki sam sposób, jak tabela, lub partycjonowany niezależnie. Dowiedz się więcej w sekcji umieszczania indeksu w grupach plików lub schematach partycji w tym artykule.

6. W przypadku identyfikowania brakujących indeksów za pomocą dynamicznych widoków zarządzania (DMV), takich jak sys.dm_db_missing_index_details i sys.dm_db_missing_index_columns, mogą być oferowane podobne odmiany indeksów w tej samej tabeli i kolumnach. Sprawdź istniejące indeksy w tabeli oraz sugestie dotyczące brakujących indeksów, aby uniknąć tworzenia zduplikowanych indeksów. Dowiedz się więcej na temat dostrajania indeksów nieklastrowanych z sugestiami dotyczącymi brakujących indeksów.

Ogólne wytyczne dotyczące projektowania indeksów

Doświadczeni administratorzy baz danych mogą zaprojektować dobry zestaw indeksów, ale to zadanie jest złożone, czasochłonne i podatne na błędy nawet w przypadku średnio złożonych baz danych i obciążeń. Zrozumienie cech bazy danych, zapytań i kolumn danych może pomóc w projektowaniu optymalnych indeksów.

Zagadnienia dotyczące bazy danych

Podczas projektowania indeksu należy wziąć pod uwagę następujące wytyczne dotyczące bazy danych:

• Duża liczba indeksów w tabeli wpływa na wydajność instrukcji INSERT, UPDATE, DELETE i MERGE, ponieważ wszystkie indeksy muszą być odpowiednio dostosowane w miarę zmian danych w tabeli. Na przykład, jeśli kolumna jest używana w kilku indeksach i wykonujesz instrukcję UPDATE modyfikującą dane tej kolumny, każdy indeks zawierający tę kolumnę musi zostać zaktualizowany, podobnie jak kolumna w podstawowej tabeli bazowej, czy to w postaci stosu czy indeksu klastrowanego.

  • Unikaj nadmiernego indeksowania mocno zaktualizowanych tabel i zachowaj wąskie indeksy, czyli z jak najmniejszą liczbą kolumn.

  • Użyj wielu indeksów, aby zwiększyć wydajność zapytań w tabelach z niskimi wymaganiami dotyczącymi aktualizacji, ale dużą ilością danych. Duża liczba indeksów może pomóc w wydajności zapytań, które nie modyfikują danych, takich jak wyrażenia SELECT, ponieważ optymalizator zapytań ma więcej indeksów do wyboru, aby określić najszybszą metodę dostępu.

• Indeksowanie małych tabel może nie być optymalne, ponieważ analiza indeksu przez optymalizator zapytań może trwać dłużej niż wykonanie podstawowego skanowania tabeli. W związku z tym indeksy w małych tabelach nigdy nie mogą być używane, ale nadal muszą być przechowywane w miarę zmian danych w tabeli.

• Indeksy w widokach mogą zapewnić znaczne wzrosty wydajności, gdy widok zawiera agregacje, sprzężenia tabeli lub kombinację agregacji i sprzężeń. Widok nie musi być jawnie przywołany w zapytaniu, aby został użyty przez optymalizator zapytań.

• Bazy danych na replikach podstawowych w usłudze Azure SQL Database automatycznie generują zalecenia dotyczące wydajności doradcy bazy danych dla indeksów. Opcjonalnie możesz włączyć automatyczne dostrajanie indeksów.

• Magazyn zapytań ułatwia identyfikowanie zapytań o nieoptymalną wydajność i zapewnia historię planów wykonywania zapytań , które dokumentują indeksy wybrane przez optymalizatora.

Zagadnienia dotyczące zapytań

Podczas projektowania indeksu należy wziąć pod uwagę następujące wytyczne dotyczące zapytań:

• Utwórz indeksy nieklastrowane w kolumnach, które są często używane w predykatach i warunkach sprzężenia w zapytaniach. Są to kolumny SARGable¹. Należy jednak unikać dodawania niepotrzebnych kolumn. Dodanie zbyt wielu kolumn indeksu może niekorzystnie wpływać na miejsce na dysku i wydajność konserwacji indeksu.

• Pokrycie indeksów może zwiększyć wydajność zapytań, ponieważ wszystkie dane potrzebne do spełnienia wymagań zapytania istnieją w samym indeksie. Oznacza to, że tylko strony indeksu, a nie strony danych tabeli lub indeksu klastrowanego, są wymagane do pobrania żądanych danych; w związku z tym zmniejszenie ogólnej liczby operacji we/wy dysku. Na przykład zapytanie o kolumny A i B w tabeli zawierającej indeks złożony utworzony w kolumnach A, Bi C może pobrać określone dane z samego indeksu.

  Indeksy obejmujące są oznaczeniem indeksu nieklastrowanego, który rozwiązuje jeden lub kilka podobnych wyników zapytania bezpośrednio, nie uzyskując dostępu do swojej tabeli bazowej i bez konieczności wykonywania dodatkowych wyszukiwań.

  Takie indeksy mają wszystkie niezbędne kolumny inne niż SARGable na poziomie liścia. Oznacza to, że kolumny zwracane przez klauzulę SELECT oraz wszystkie argumenty WHERE i JOIN są objęte indeksem.

  Jeśli indeks jest wystarczająco wąski w porównaniu z wierszami i kolumnami w samej tabeli, co oznacza, że jest to rzeczywisty podzbiór łącznych kolumn, to potencjalnie wymaga znacznie mniej operacji I/O do wykonania zapytania.

  Rozważ pokrycie indeksów podczas wybierania małej części dużej tabeli i gdy ta mała część jest definiowana przez stały predykat, taki jak rozrzedzone kolumny zawierające tylko kilka wartości innych niż NULL, na przykład.

• Napisz zapytania, które wstawią lub modyfikują jak najwięcej wierszy w jednej instrukcji, zamiast używać wielu zapytań do aktualizowania tych samych wierszy. Używając tylko jednej instrukcji, można zastosować zoptymalizowaną konserwację indeksu.

• Oceń typ zapytania i sposób użycia kolumn w zapytaniu. Na przykład kolumna używana w typie zapytania dokładnego dopasowania byłaby dobrym kandydatem dla indeksu nieklastrowanego lub klastrowanego.

¹ Termin SARGable w relacyjnych bazach danych odnosi się do predykatu Search ARGumentable, który może używać indeksu, aby przyspieszyć wykonywanie zapytania.

Zagadnienia dotyczące kolumn

Podczas projektowania indeksu należy wziąć pod uwagę następujące wytyczne dotyczące kolumn:

• Zachowaj krótki klucz indeksu dla indeksów klastrowanych. Ponadto indeksy klastrowane korzystają z tworzenia unikatowych lub niepustych kolumn.

• Kolumny, które są typu ntext, text, image, varchar(max), nvarchar(max)i varbinary(max) nie można określić jako kolumn kluczy indeksu. Jednak varchar(max), nvarchar(max), varbinary(max)i typy danych XML mogą uczestniczyć w indeksie nieklastrowanym jako kolumny indeksu nieklastrowanego. Aby uzyskać więcej informacji, zobacz sekcję Indeks z dołączonymi kolumnami w tym przewodniku.

• Typ danych XML może być tylko kolumną klucza tylko w indeksie XML. Aby uzyskać więcej informacji, zobacz Indeksy XML (SQL Server). Program SQL Server 2012 SP1 wprowadził nowy typ indeksu XML znanego jako selektywny indeks XML. Ten nowy indeks może poprawić wydajność zapytań względem danych przechowywanych jako XML, umożliwić szybsze indeksowanie dużych obciążeń danych XML i zwiększyć skalowalność, zmniejszając koszty magazynowania samego indeksu. Aby uzyskać więcej informacji, zobacz Selektywne indeksy XML (SXI).

• Sprawdź unikatowość kolumn. Unikatowy indeks zamiast indeksu niepowiązanego w tej samej kombinacji kolumn udostępnia dodatkowe informacje dla optymalizatora zapytań, który sprawia, że indeks jest bardziej przydatny. Aby uzyskać więcej informacji, zobacz Unikatowe wytyczne dotyczące projektowania indeksów w tym przewodniku.

• Sprawdź rozkład danych w kolumnie. Często długotrwałe zapytanie jest spowodowane indeksowaniem kolumny z kilkoma unikatowymi wartościami lub wykonaniem sprzężenia w takiej kolumnie. Jest to podstawowy problem z danymi i zapytaniami, a ogólnie nie można go rozwiązać bez identyfikowania tej sytuacji. Na przykład fizyczny katalog telefoniczny posortowany alfabetycznie na imieniu rodziny nie przyspiesza lokalizowania osoby, jeśli wszystkie osoby w mieście mają nazwę Smith lub Jones. Aby uzyskać więcej informacji na temat dystrybucji danych, zobacz Statystyki.

• Rozważ użycie filtrowanych indeksów w kolumnach, które mają dobrze zdefiniowane podzestawy, na przykład rozrzedzone kolumny, kolumny z głównie NULL wartościami, kolumnami z kategoriami wartości i kolumnami z odrębnymi zakresami wartości. Dobrze zaprojektowany indeks filtrowany może poprawić wydajność zapytań, zmniejszyć koszty konserwacji indeksu i zmniejszyć koszty magazynowania.

• Rozważ kolejność kolumn, jeśli indeks zawiera wiele kolumn. Kolumna używana w klauzuli WHERE równa się (=), większa niż (>), mniejsza niż (<) lub BETWEEN jako warunek wyszukiwania, lub uczestniczy w łączeniu, powinna zostać umieszczona jako pierwsza. Dodatkowe kolumny powinny być uporządkowane na podstawie ich poziomu odrębności, czyli od najbardziej odrębnych do najmniej odrębnych.

  Jeśli na przykład indeks jest zdefiniowany jako LastName, FirstNameindeks jest przydatny, gdy kryterium wyszukiwania to WHERE LastName = 'Smith' lub WHERE LastName = Smith AND FirstName LIKE 'J%'. Jednak optymalizator zapytań nie będzie używać indeksu dla zapytania, które wyszukiwało tylko na FirstName (WHERE FirstName = 'Jane').

• Rozważ indeksowanie obliczonych kolumn. Aby uzyskać więcej informacji, zobacz Indeksy na obliczanych kolumnach.

Cechy indeksu

Po ustaleniu, że indeks jest odpowiedni dla zapytania, możesz wybrać typ indeksu, który najlepiej pasuje do twojej sytuacji. Cechy indeksu obejmują następującą listę:

• Klastrowane a nieklastrowane
• Unikatowa a nieunikatowa
• Pojedyncza kolumna a wielokolumnowa
• Kolejność rosnąca lub malejąca w kolumnach w indeksie
• Pełna tabela kontra filtrowana dla indeksów nieklastrowanych
• Przechowywanie kolumnowe a przechowywanie wierszowe
• Hashowe kontra nieklastrowane indeksy dla tabel zoptymalizowanych pod kątem pamięci

Możesz również dostosować początkowe właściwości magazynu indeksu, aby zoptymalizować jego wydajność lub konserwację, ustawiając opcję, taką jak FILLFACTOR. Ponadto można określić lokalizację przechowywania indeksu przy użyciu grup plików lub schematów partycji w celu optymalizacji wydajności.

Umieszczanie indeksów w grupach plików lub schematach partycji

Podczas opracowywania strategii projektowania indeksu należy rozważyć umieszczenie indeksów w grupach plików skojarzonych z bazą danych. Ostrożny wybór grupy plików lub schematu partycji może zwiększyć wydajność zapytań.

Domyślnie indeksy są przechowywane w tej samej grupie plików co tabela podstawowa, na której tworzony jest indeks. Niepartycjonowany indeks klastrowany i tabela bazowa zawsze znajdują się w tej samej grupie plików. Można jednak wykonać następujące czynności:

• Utwórz indeksy nieklastrowane w grupie plików innej niż grupa plików podstawowej tabeli lub indeksu klastrowanego.
• Partycjonuj klastrowane i nieklastrowane indeksy, aby obejmowały wiele grup plików.
• Przenieś tabelę z jednej grupy plików do innej, upuszczając indeks klastrowany i określając nowy schemat grupy plików lub partycji w MOVE TO klauzuli DROP INDEX instrukcji lub używając CREATE INDEX instrukcji z klauzulą DROP_EXISTING .

Tworząc indeks nieklastrowany w innej grupie plików, można osiągnąć wzrost wydajności, jeśli grupy plików używają różnych dysków fizycznych z własnymi kontrolerami. Następnie informacje o danych i indeksie mogą być odczytywane równolegle przez wiele głowic dysków. Jeśli na przykład Table_A na grupie plików f1 i Index_A na grupie plików f2 są używane przez to samo zapytanie, można osiągnąć wzrost wydajności, ponieważ obie grupy plików są w pełni używane bez konfliktu. Jeżeli jednak zapytanie Table_A jest skanowane, ale Index_A nie jest używane, używana jest tylko plików grupa f1. Nie powoduje to zwiększenia wydajności.

Ponieważ nie można przewidzieć typu dostępu i jego wystąpienia, lepszym rozwiązaniem może być rozmieszczenie tabel i indeksów we wszystkich grupach plików. Gwarantuje to, że dostęp do wszystkich dysków jest uzyskiwany, ponieważ wszystkie dane i indeksy są rozłożone równomiernie na wszystkich dyskach, niezależnie od sposobu uzyskiwania dostępu do danych. Jest to również prostsze podejście dla administratorów systemu.

Partycje w wielu grupach plików

Można również rozważyć partycjonowanie klastrowanych i nieklastrowanych indeksów opartych na dyskach w wielu grupach plików. Partycjonowane indeksy są partycjonowane horyzontalnie, czyli według wierszy, na podstawie funkcji partycji. Funkcja partycji definiuje sposób mapowania każdego wiersza na zestaw partycji na podstawie wartości określonych kolumn, nazywanych kolumnami partycjonowania. Schemat partycji określa mapowanie partycji na zestaw grup plików.

Partycjonowanie indeksu może zapewnić następujące korzyści:

• Zapewnianie skalowalnych systemów, które sprawiają, że duże indeksy są bardziej zarządzane. Systemy OLTP mogą na przykład implementować aplikacje obsługujące partycje, które zajmują się dużymi indeksami.

• Szybsze i wydajniejsze uruchamianie zapytań. Gdy zapytania uzyskują dostęp do kilku partycji indeksu, optymalizator zapytań może przetwarzać poszczególne partycje jednocześnie i wykluczać partycje, które nie mają wpływu na zapytanie.

Aby uzyskać więcej informacji, zobacz Partycjonowane tabele i indeksy.

Wskazówki dotyczące projektowania kolejności sortowania indeksów

Podczas definiowania indeksów należy rozważyć, czy dane kolumny klucza indeksu powinny być przechowywane w kolejności rosnącej lub malejącej. Rosnąco jest wartością domyślną i zapewnia zgodność z wcześniejszymi wersjami silnika bazy danych. Składnia instrukcji CREATE INDEX, CREATE TABLEi ALTER TABLE obsługuje słowa kluczowe ASC (rosnąco) i DESC (malejąco) dla poszczególnych kolumn w indeksach i ograniczeniach.

Określenie kolejności przechowywania wartości kluczy w indeksie jest przydatne, gdy zapytania odwołujące się do tabeli zawierają ORDER BY klauzule określające różne kierunki dla kolumny klucza lub kolumn w tym indeksie. W takich przypadkach indeks może usunąć potrzebę SORT operatora w planie zapytania, co sprawia, że zapytanie jest bardziej wydajne. Na przykład kupujący w dziale zakupów Adventure Works Cycles muszą ocenić jakość produktów, które kupują od dostawców. Kupujący są najbardziej zainteresowani znalezieniem produktów wysyłanych przez tych dostawców, którzy mają wysoki wskaźnik odrzucenia.

Jak pokazano w poniższym zapytaniu względem przykładowej bazy danych AdventureWorks, pobieranie danych w celu spełnienia tych kryteriów wymaga RejectedQty sortowania kolumny w Purchasing.PurchaseOrderDetail tabeli w kolejności malejącej (od dużej do małej) i ProductID sortowania kolumny w kolejności rosnącej (od małej do dużej).

SELECT RejectedQty,
       ((RejectedQty / OrderQty) * 100) AS RejectionRate,
       ProductID,
       DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;
GO

Poniższy plan wykonania dla tego zapytania pokazuje, że optymalizator zapytań użył SORT operatora, aby zwrócić zestaw wyników w kolejności określonej przez klauzulę ORDER BY .

Jeśli indeks wierszowy oparty na dysku jest tworzony z kolumnami, które pasują do tych w ORDER BY klauzuli w zapytaniu, operator SORT może zostać wyeliminowany, a plan zapytania staje się bardziej efektywny.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);
GO

Po ponownym wykonaniu zapytania poniższy plan wykonywania pokazuje, że SORT operator został wyeliminowany, a nowo utworzony indeks nieklastrowany jest używany.

Aparat bazy danych może poruszać się równie wydajnie w obu kierunkach. Indeks zdefiniowany jako (RejectedQty DESC, ProductID ASC) nadal może być używany dla zapytania, w którym kierunek sortowania kolumn w ORDER BY klauzuli jest odwrócony. Na przykład zapytanie z klauzulą ORDER BYORDER BY RejectedQty ASC, ProductID DESC może używać indeksu.

Kolejność sortowania można określić tylko dla kolumn kluczy w indeksie. Widok katalogu sys.index_columns i funkcja INDEXKEY_PROPERTY pokazują, czy kolumna indeksu jest przechowywana w kolejności rosnącej czy malejącej.

Jeśli korzystasz z przykładów kodu w przykładowej bazie danych AdventureWorks, możesz usunąć kod IX_PurchaseOrderDetail_RejectedQty za pomocą następującego języka Transact-SQL:

DROP INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail;
GO

Metadane

Użyj tych widoków metadanych, aby wyświetlić atrybuty indeksów. W niektórych z tych widoków osadzono więcej informacji o architekturze.

W przypadku indeksów kolumnowych wszystkie kolumny są przechowywane w metadanych jako kolumny dołączone. Indeks columnstore nie zawiera kolumn kluczowych.

• sys.column_store_dictionaries
• sys.column_store_row_groups
• sys.column_store_segments
• sys.dm_column_store_object_pool
• sys.dm_db_column_store_row_group_operational_stats
• sys.dm_db_column_store_row_group_physical_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_xtp_hash_index_stats
• sys.dm_db_xtp_index_stats
• sys.dm_db_xtp_nonclustered_index_stats
• sys.dm_db_xtp_object_stats
• sys.dm_db_xtp_table_memory_stats
• sys.hash_indexes
• sys.index_columns
• sys.indexes
• sys.internal_partitions
• sys.memory_optimized_tables_internal_attributes
• sys.partitions

Wytyczne dotyczące projektowania indeksu klastrowanego

Indeksy klastrowane sortują i przechowują wiersze danych w tabeli na podstawie ich wartości klucza. Może istnieć tylko jeden indeks klastrowany na tabelę, ponieważ wiersze danych można sortować tylko w jednej kolejności. Z kilkoma wyjątkami każda tabela powinna mieć indeks klastrowany zdefiniowany w kolumnie lub kolumnach, który oferuje następujące elementy:

• Może służyć do często używanych zapytań.

• Zapewnia wysoki stopień unikatowości.

  Uwaga / Notatka

  Podczas tworzenia PRIMARY KEY ograniczenia zostanie automatycznie utworzony unikatowy indeks w kolumnie lub kolumnach. Domyślnie ten indeks jest klasterowany; można jednak określić indeks nieklastrowany podczas tworzenia ograniczenia.

• Może być używany w zapytaniach dotyczących zakresu.

Jeśli indeks klastrowany nie jest tworzony z właściwością UNIQUE, aparat bazy danych automatycznie dodaje do tabeli 4-bajtową kolumnę unikalności. Gdy jest to wymagane, aparat bazy danych automatycznie dodaje unikatową wartość do wiersza, aby każdy klucz był unikatowy. Ta kolumna i jej wartości są używane wewnętrznie i nie mogą być widoczne ani dostępne dla użytkowników.

Architektura indeksu klastrowanego

Indeksy Rowstore są zorganizowane jako drzewa B+. Każda strona w drzewie indeksu B+ jest nazywana węzłem indeksu. Górny węzeł drzewa B+ jest nazywany węzłem głównym. Węzły dolne w indeksie są nazywane węzłami liścia. Wszystkie poziomy indeksu między węzłami głównymi i węzłami liścia są wspólnie znane jako poziomy pośrednie. W indeksie klastrowanym węzły liścia zawierają strony danych tabeli bazowej. Węzły na poziomie głównym i pośrednim zawierają strony indeksu zawierające wiersze indeksu. Każdy wiersz indeksu zawiera wartość klucza i wskaźnik do strony poziomu pośredniego w drzewie B+ lub wiersza danych na poziomie liścia indeksu. Strony na każdym poziomie indeksu są połączone na podwójnie połączonej liście.

Indeksy klastrowane mają jeden wiersz w sys.partitions dla każdej partycji index_id = 1 używanej przez indeks. Domyślnie indeks klastrowany ma jedną partycję. Gdy indeks klastrowany ma wiele partycji, każda partycja ma strukturę drzewa B+ zawierającą dane dla tej konkretnej partycji. Jeśli na przykład indeks klastrowany ma cztery partycje, istnieją cztery struktury drzewa B+; jeden na każdej partycji.

W zależności od typów danych w indeksie klastrowanym każda struktura indeksu klastrowanego ma co najmniej jedną jednostki alokacji, w których mają być przechowywane dane dla określonej partycji i zarządzać nimi. Co najmniej każdy indeks klastrowany ma jedną IN_ROW_DATA jednostkę alokacji na partycję. Indeks klastrowany ma również jedną jednostkę alokacji LOB_DATA na partycję, jeśli zawiera kolumny dużych obiektów (LOB). Ma również jedną jednostkę alokacji ROW_OVERFLOW_DATA na partycję, jeśli zawiera kolumny o zmiennej długości, które przekraczają limit rozmiaru wiersza 8060 bajtów.

Strony w łańcuchu danych i wiersze w nich są uporządkowane według wartości klucza indeksu klastrowanego. Wszystkie wstawki są wykonywane w punkcie, w którym wartość klucza w wstawionym wierszu mieści się w kolejności między istniejącymi wierszami.

Na tej ilustracji przedstawiono strukturę indeksu klastrowanego w pojedynczej partycji.

Zagadnienia dotyczące zapytań

Przed utworzeniem indeksów klastrowanych zapoznaj się ze sposobem uzyskiwania dostępu do danych. Rozważ użycie indeksu klastrowanego dla zapytań, które wykonują następujące czynności:

• Zwraca zakres wartości przy użyciu operatorów, takich jak BETWEEN, >, >=, < i <=.

  Po znalezieniu wiersza z pierwszą wartością przy użyciu indeksu klastrowanego wiersze z kolejnymi indeksami mają gwarancję fizycznego sąsiedztwa. Jeśli na przykład zapytanie pobiera rekordy między zakresem numerów zamówień sprzedaży, indeks klastrowany w kolumnie SalesOrderNumber może szybko zlokalizować wiersz zawierający początkowy numer zamówienia sprzedaży, a następnie pobrać wszystkie kolejne wiersze w tabeli do momentu osiągnięcia ostatniego numeru zamówienia sprzedaży.

• Zwracanie dużych zestawów wyników.

• Użyj JOIN klauzul; zazwyczaj są to kolumny kluczy obcych.

• Użyj klauzuli ORDER BY lub klauzuli GROUP BY.

  Indeks kolumn określonych w klauzuli ORDER BY or GROUP BY może usunąć konieczność sortowania danych przez aparat bazy danych, ponieważ wiersze są już sortowane. Poprawia to wydajność zapytań.

Zagadnienia dotyczące kolumn

Ogólnie rzecz biorąc, należy zdefiniować klucz indeksu klastrowanego z jak najmniejszą liczbą kolumn. Rozważ kolumny, które mają co najmniej jeden z następujących atrybutów:

• Są unikatowe lub zawierają wiele odrębnych wartości

  Na przykład identyfikator pracownika jednoznacznie identyfikuje pracowników. Ograniczenie indeksu klastrowanego lub podstawowego klucza w EmployeeID kolumnie poprawi wydajność zapytań, które wyszukują informacje o pracownikach na podstawie numeru identyfikatora pracownika. Alternatywnie można utworzyć indeks klastrowany na LastName, FirstName, MiddleName, ponieważ rekordy pracowników są często grupowane i zapytania są wykonywane w ten sposób, a kombinacja tych kolumn nadal zapewnia wysoki poziom zróżnicowania.

  Wskazówka

  Jeśli nie określono inaczej, podczas tworzenia ograniczenia KLUCZA PODSTAWOWEgo aparat bazy danych tworzy indeks klastrowany w celu obsługi tego ograniczenia.

  Chociaż unikalny identyfikator może służyć do wymuszania unikatowości jako PRIMARY KEY, nie jest to wydajny klucz grupowania.

  Jeśli używasz identyfikatora unikatowego w roli PRIMARY KEY, zaleca się utworzenie go jako indeksu nieklastrowego i użycie innej kolumny, takiej jak IDENTITY, aby utworzyć indeks klastrowy.

• Dostęp jest uzyskiwany sekwencyjnie

  Na przykład identyfikator produktu jednoznacznie identyfikuje produkty w Production.Product tabeli w AdventureWorks2022 bazie danych. Zapytania, w których określono wyszukiwanie sekwencyjne, takie jak WHERE ProductID BETWEEN 980 and 999, skorzystałyby z indeksu klastrowanego na ProductID. Wynika to z faktu, że wiersze będą przechowywane w kolejności posortowanej dla tej kolumny klucza.

• Zdefiniowano jako IDENTITY.

• Często używane do sortowania danych pobranych z tabeli.

  Dobrym pomysłem może być klastrowanie tabeli zgodnie z tą kolumną, aby zaoszczędzić koszt operacji sortowania przy każdym zapytaniu kolumny.

Indeksy klastrowane nie są dobrym wyborem dla następujących atrybutów:

• Kolumny, które przechodzą częste zmiany

  Powoduje to przeniesienie całego wiersza, ponieważ aparat bazy danych musi zachować wartości danych wiersza w kolejności fizycznej. Jest to ważna kwestia w systemach przetwarzania transakcji o dużej ilości, w których dane są zwykle niestabilne.

• Szerokie klawisze

  Klucze szerokie składają się z kilku kolumn lub dużej liczby kolumn o dużych rozmiarach. Wartości kluczy z indeksu klastrowanego są używane przez wszystkie nieklastrowane indeksy jako klucze odnośników. Wszystkie indeksy nieklastrowane zdefiniowane w tej samej tabeli są znacznie większe, ponieważ nieklastrowane wpisy indeksu zawierają klucz klastrowania, a także kolumny kluczy zdefiniowane dla tego indeksu nieklastrowanego.

Nieklastrowane wytyczne dotyczące projektowania indeksów

Indeks nieklastrowany magazynu wierszy oparty na dysku zawiera wartości klucza indeksu i lokalizatory wierszy wskazujące lokalizację przechowywania danych tabeli. Można utworzyć wiele indeksów nieklastrowanych w tabeli lub w widoku indeksowanym. Ogólnie rzecz biorąc, indeksy nieklastrowane powinny być zaprojektowane tak, aby zwiększyć wydajność często używanych zapytań, które nie są objęte indeksem klastrowanym.

Podobnie jak w przypadku użycia indeksu w książce, optymalizator zapytań wyszukuje wartość danych, wyszukując nieklastrowany indeks, aby znaleźć lokalizację wartości danych w tabeli, a następnie pobiera dane bezpośrednio z tej lokalizacji. Dzięki temu indeksy nieklastrowane są optymalnym wyborem dla dokładnie dopasowanych zapytań, ponieważ indeks zawiera wpisy opisujące dokładną lokalizację w tabeli wartości danych wyszukiwanych w zapytaniach. Na przykład, aby przesłać zapytanie do tabeli HumanResources.Employee dla wszystkich pracowników raportujących do określonego menedżera, optymalizator zapytań może używać indeksu nieklastrowanego IX_Employee_ManagerID; ma on ManagerID jako swoją kolumnę klucza. Optymalizator zapytań może szybko znaleźć wszystkie wpisy w indeksie, które pasują do określonego ManagerID. Każdy wpis indeksu wskazuje dokładną stronę i wiersz w tabeli lub indeks klastrowany, w którym można znaleźć odpowiednie dane. Gdy optymalizator zapytań znajdzie wszystkie wpisy w indeksie, może przejść bezpośrednio do dokładnej strony i wiersza, aby pobrać dane.

Architektura indeksu nieklastrowanego

Indeksy nieklastrowane magazynu wierszy opartego na dyskach mają taką samą strukturę drzewa B+ jak indeksy klastrowane, z wyjątkiem następujących istotnych różnic:

• Wiersze danych tabeli bazowej nie są sortowane ani przechowywane według kolejności wynikającej z ich kluczy nieklastrowanych.

• Poziom liści indeksu nieklastrowanego składa się ze stron indeksu zamiast stron danych. Strony indeksu na poziomie liścia indeksu nieklastrowanego zawierają kolumny kluczy i dołączone kolumny.

Lokalizatory wierszy w nieklastrowanych wierszach indeksu są wskaźnikiem do wiersza lub są kluczem indeksu klastrowanego dla wiersza, zgodnie z opisem w następującym artykule:

• Jeśli tabela jest stertą, co oznacza, że nie ma indeksu klastrowanego, lokalizator wierszy jest wskaźnikiem do wiersza. Wskaźnik jest kompilowany na podstawie identyfikatora pliku (ID), numeru strony i numeru wiersza na stronie. Cały wskaźnik jest nazywany identyfikatorem wiersza (RID).

• Jeśli tabela ma indeks klastrowany lub indeks znajduje się w widoku indeksowanym, lokalizator wierszy jest kluczem indeksu klastrowanego dla wiersza.

Lokalizatory wierszy zapewniają również unikatowość dla nieklastrowanych wierszy indeksu. W poniższej tabeli opisano, jak aparat bazy danych dodaje lokalizatory wierszy do indeksów nieklastrowanych:

Typ tabeli	Typ indeksu nieklastrowanego	Lokalizator wierszy
Kupa
	Nieunikatowy	Identyfikator RID dodany do kluczowych kolumn
	Niepowtarzalny	RID dodany do dołączonych kolumn
Unikatowy indeks klastrowany
	Nieunikatowy	Klucze indeksu klastrowanego dodane do kolumn kluczowych
	Niepowtarzalny	Klucze indeksu klastrowanego dodane do dołączonych kolumn
Nieunikatowy indeks klastrowany
	Nieunikatowy	Klucze indeksu klastrowanego i identyfikator unikatowości (jeśli są obecne) zostały dodane do kolumn kluczy
	Niepowtarzalny	Klucze indeksu klastrowanego i dodatkowy identyfikator unikatowy (jeśli jest obecny) dodane do dołączonych kolumn

Aparat bazy danych nigdy nie przechowuje danej kolumny dwa razy w indeksie nieklastrowanym. Kolejność klucza indeksu określona przez użytkownika podczas tworzenia indeksu nieklastrowanego jest zawsze honorowana: wszystkie kolumny lokalizatora wierszy, które należy dodać do klucza indeksu nieklastrowanego, są dodawane na końcu klucza, po kolumnach określonych w definicji indeksu. Klastrowane kolumny lokalizatora wierszy oparte na kluczach w indeksie nieklastrowanym mogą być używane przez optymalizator zapytań, niezależnie od tego, czy zostały jawnie określone w definicji indeksu.

W poniższych przykładach pokazano, jak lokalizatory wierszy są implementowane w indeksach nieklastrowanych:

Indeks klastrowany	Definicja indeksu nieklastrowanego	Definicja indeksu nieklastrowanego z lokalizatorami wierszy	Wyjaśnienie
Unikatowy indeks klastrowany z kolumnami kluczy (A, B, C)	Nieunikalny nieklastrowany indeks z kolumnami klucza (B, A) i kolumnami dołączonymi (E, G)	Kolumny kluczy (B, A, C) i dołączone kolumny (E, G)	Indeks nieklastrowany jest nieunikalny, więc lokalizator wierszy musi znajdować się w kluczach indeksu. Kolumny B i A z lokalizatora wierszy są już obecne, więc dodawana jest tylko kolumna c. Kolumna c jest dodawana na końcu listy kolumn kluczy.
Unikatowy indeks klastrowany z kolumną klucza (A)	Indeks nieklastrowany, nieunikalny z kolumnami kluczy (B, C) i dołączoną kolumną (A)	Kolumny kluczy (B, C, A)	Indeks nieklastrowany jest nieunikalny, więc lokalizator wierszy jest dodawany do klucza. Kolumna A nie jest jeszcze określona jako kolumna klucza, dlatego jest dodawana na końcu listy kolumn kluczy. Kolumna A znajduje się teraz w kluczu, więc nie ma potrzeby przechowywania jej jako dołączonej kolumny.
Unikatowy indeks klastrowany z kolumną klucza (A, B)	Unikatowy indeks nieklastrowany z kolumną klucza (C)	Kolumna klucza (C) i dołączone kolumny (A, B)	Indeks nieklastrowany jest unikatowy, więc lokalizator wierszy jest dodawany do uwzględnionych kolumn.

Indeksy nieklastrowane mają jeden wiersz w pliku sys.partitions z każdą partycją index_id > 1 używaną przez indeks. Domyślnie indeks nieklastrowany ma jedną partycję. Gdy indeks nieklastrowany ma wiele partycji, każda partycja ma strukturę drzewa B+ zawierającą wiersze indeksu dla tej konkretnej partycji. Jeśli na przykład indeks nieklastrowany ma cztery partycje, istnieją cztery struktury drzewa B+ z jedną w każdej partycji.

W zależności od typów danych w indeksie nieklastrowanym każda nieklastrowana struktura indeksu ma co najmniej jedną jednostki alokacji, w których mają być przechowywane dane dla określonej partycji i zarządzać nimi. Co najmniej każdy indeks nieklastrowany ma jedną jednostkę alokacji IN_ROW_DATA na partycję, która przechowuje strony drzewa indeksu B+. Indeks nieklastrowany ma również jedną jednostkę alokacji LOB_DATA na partycję, jeśli zawiera kolumny dużego obiektu (LOB). Ponadto ma jedną jednostkę alokacji ROW_OVERFLOW_DATA na partycję, jeśli zawiera kolumny o zmiennej długości, które przekraczają limit rozmiaru wierszy 8060 bajtów.

Poniższa ilustracja przedstawia strukturę indeksu nieklastrowanego w jednej partycji.

Zagadnienia dotyczące bazy danych

Podczas projektowania indeksów nieklastrowanych należy wziąć pod uwagę cechy bazy danych.

• Bazy danych lub tabele z niskimi wymaganiami dotyczącymi aktualizacji, ale duże ilości danych mogą korzystać z wielu indeksów nieklastrowanych w celu zwiększenia wydajności zapytań. Rozważ utworzenie filtrowanych indeksów dla dobrze zdefiniowanych podzestawów danych, aby zwiększyć wydajność zapytań, zmniejszyć koszty magazynowania indeksów i zmniejszyć koszty konserwacji indeksu w porównaniu z indeksami nieklastrowanym w pełnej tabeli.

  Aplikacje systemu obsługi decyzji i bazy danych zawierające głównie dane tylko do odczytu mogą korzystać z wielu indeksów nieklastrowanych. Optymalizator zapytań ma więcej indeksów do wyboru, co pozwala na określenie najszybszej metody dostępu, a niskie obciążenie aktualizacji w bazie danych oznacza, że konserwacja indeksów nie wpływa negatywnie na wydajność.

• Aplikacje i bazy danych przetwarzania transakcji online (OLTP), które zawierają mocno zaktualizowane tabele, powinny unikać nadmiernego indeksowania. Ponadto indeksy powinny być wąskie, czyli z jak najmniejszą liczbą kolumn.

  Duża liczba indeksów w tabeli wpływa na wydajność instrukcji INSERT, UPDATE, DELETE i MERGE, ponieważ wszystkie indeksy muszą być odpowiednio dostosowane w miarę zmian danych w tabeli.

Zagadnienia dotyczące zapytań

Przed utworzeniem indeksów nieklastrowanych należy zrozumieć, jak uzyskiwany jest dostęp do danych. Rozważ użycie indeksu nieklastrowanego dla zapytań, które mają następujące atrybuty:

• Użyj klauzuli JOIN lub klauzuli GROUP BY.

  Utwórz wiele indeksów nieklastrowanych w kolumnach zaangażowanych w operacje sprzężenia i grupowania oraz indeks klastrowany w dowolnych kolumnach kluczy obcych.

• Zapytania, które nie zwracają dużych zestawów wyników.

  Utwórz indeksy filtrowane w celu pokrycia zapytań, które zwracają dobrze zdefiniowany podzbiór wierszy z dużej tabeli.

  Wskazówka

  Zazwyczaj klauzula WHERE instrukcji CREATE INDEX jest zgodna z klauzulą zapytania, które jest objęte.

• Zawierają kolumny często używane w warunkach wyszukiwania zapytania, takie jak klauzula WHERE , która zwraca dokładne dopasowania.

  Wskazówka

  Podczas dodawania nowych indeksów należy wziąć pod uwagę koszty i korzyści. Lepszym rozwiązaniem może być skonsolidowanie dodatkowych potrzeb związanych z zapytaniami do istniejącego indeksu. Rozważ na przykład dodanie jednej lub dwóch dodatkowych kolumn na poziomie liścia do istniejącego indeksu, jeśli zezwala na pokrycie kilku krytycznych zapytań, zamiast mieć jeden dokładnie obejmujący indeks dla każdego zapytania krytycznego.

Zagadnienia dotyczące kolumn

Rozważ kolumny, które mają co najmniej jeden z następujących atrybutów:

• Omówienie zapytania.

  Wzrost wydajności jest osiągany, gdy indeks zawiera wszystkie kolumny w zapytaniu. Optymalizator zapytań może zlokalizować wszystkie wartości kolumn w indeksie; Dostęp do danych tabeli lub indeksu klastrowanego nie jest uzyskiwany, co powoduje, że liczba operacji we/wy dysku jest mniejsza. Użyj indeksu z dołączonymi kolumnami , aby dodać kolumny obejmujące zamiast tworzyć szeroki klucz indeksu.

  Jeśli tabela ma indeks klastrowany, kolumna lub kolumny zdefiniowane w indeksie klastrowanym są automatycznie dodawane do każdego indeksu nieklastrowanego w tabeli. Może to spowodować utworzenie objętego zapytania bez określania kolumn indeksu klastrowanego w definicji indeksu nieklastrowanego. Jeśli na przykład tabela ma indeks klastrowany w kolumnie C, to indeks nieklastrowany na kolumnach B i A ma za kluczowe wartości kolumny B, A i C. Aby uzyskać więcej informacji, odwiedź stronę architektura indeksu nieklastrowanego.

• Wiele unikatowych wartości, takich jak kombinacja nazwy rodziny i imienia, jeśli indeks klastrowany jest używany dla innych kolumn.

  Jeśli istnieje bardzo niewiele odrębnych wartości, takich jak tylko 1 i 0, większość zapytań nie będzie używać indeksu, ponieważ skanowanie tabeli jest ogólnie bardziej wydajne. W przypadku tego typu danych rozważ utworzenie filtrowanego indeksu dla unikatowej wartości, która występuje tylko w kilku wierszach. Jeśli na przykład większość wartości to 0, optymalizator zapytań może użyć filtrowanego indeksu dla wierszy danych zawierających 1wartość .

Używanie dołączonych kolumn do rozszerzania indeksów nieklastrowanych

Funkcjonalność indeksów nieklastrowanych można rozszerzyć, dodając kolumny nieklastrowane do poziomu liści indeksu nieklastrowanego. Dołączając kolumny niekluczowe, można tworzyć indeksy nieklastrowane, które obejmują więcej zapytań. Dzieje się tak, ponieważ kolumny inne niż kluczowe mają następujące korzyści:

• Mogą to być typy danych niedozwolone jako kolumny klucza indeksu.

• Nie są one brane pod uwagę przez aparat bazy danych podczas obliczania liczby kolumn klucza indeksu lub rozmiaru klucza indeksu.

Indeks z dołączonymi kolumnami niekluczowymi może znacznie poprawić wydajność zapytań, gdy wszystkie kolumny w zapytaniu są uwzględniane w indeksie jako kolumny klucza lub nieklucza. Wzrost wydajności jest osiągany, ponieważ optymalizator zapytań może zlokalizować wszystkie wartości kolumn w indeksie; Dostęp do danych tabeli lub indeksu klastrowanego nie jest uzyskiwany, co powoduje, że liczba operacji we/wy dysku jest mniejsza.

Uwaga / Notatka

Gdy indeks zawiera wszystkie kolumny, do których odwołuje się zapytanie, jest zwykle określane jako obejmujące zapytanie.

Podczas gdy kolumny kluczy są przechowywane na wszystkich poziomach indeksu, kolumny inne niż kluczowe są przechowywane tylko na poziomie liścia.

Użyj dołączonych kolumn, aby uniknąć limitów rozmiaru

Kolumny niekluczowe można uwzględnić w indeksie nieklastrowanym, aby uniknąć przekroczenia bieżących ograniczeń rozmiaru indeksu maksymalnie 16 kolumn klucza i maksymalnego rozmiaru klucza indeksu wynoszącego 900 bajtów. Silnik bazy danych nie uwzględnia kolumn innych niż kluczowe podczas obliczania liczby kolumn klucza indeksu czy rozmiaru klucza indeksu.

Załóżmy na przykład, że chcesz zaindeksować następujące kolumny w Document tabeli:

Title NVARCHAR(50)
Revision NCHAR(5)
FileName NVARCHAR(400)

Ponieważ typy danych nchar i nvarchar wymagają 2 bajtów dla każdego znaku, indeks zawierający te trzy kolumny przekroczy ograniczenie rozmiaru 900 bajtów o 10 bajtów (455 * 2). Korzystając z INCLUDE klauzuli instrukcji, klucz indeksu CREATE INDEX można zdefiniować jako (Title, Revision) i FileName zdefiniować jako kolumnę niekluczową. W ten sposób rozmiar klucza indeksu będzie wynosić 110 bajtów (55 * 2), a indeks nadal będzie zawierać wszystkie wymagane kolumny. Poniższa instrukcja tworzy taki indeks.

CREATE INDEX IX_Document_Title
ON Production.Document(Title, Revision)
    INCLUDE(FileName);
GO

Jeśli używasz poniższych przykładów kodu, możesz usunąć ten indeks przy użyciu tej instrukcji Transact-SQL:

DROP INDEX IX_Document_Title
ON Production.Document;
GO

Indeksowanie z dołączonymi wytycznymi dotyczącymi kolumn

Podczas projektowania indeksów nieklastrowanych z dołączonymi kolumnami należy wziąć pod uwagę następujące wytyczne:

• Kolumny niekluczowe są definiowane w klauzuli INCLUDE instrukcji CREATE INDEX.

• Kolumny niekluczowe można definiować tylko dla indeksów nieklastrowanych w tabelach lub widokach indeksowanych.

• Wszystkie typy danych są dozwolone z wyjątkiem tekstu, ntexti obrazu.

• Kolumny obliczane, które są deterministyczne i precyzyjne lub nieprecyzyjne, mogą zawierać kolumny. Aby uzyskać więcej informacji, zobacz Indeksy na obliczanych kolumnach.

• Podobnie jak w przypadku kolumn kluczowych, obliczone kolumny pochodzące z obrazów, ntekstów i typów danych tekstowych mogą być kolumnami niekluczowymi (dołączonymi), o ile typ danych kolumny obliczeniowej jest dozwolony jako kolumna indeksu bez klucza.

• Nie można określić nazw kolumn zarówno na INCLUDE liście, jak i na liście kolumn kluczy.

• Nazwy kolumn nie mogą być powtarzane na INCLUDE liście.

Wytyczne dotyczące rozmiaru kolumn

• Należy zdefiniować co najmniej jedną kolumnę klucza. Maksymalna liczba kolumn innych niż kluczowe wynosi 1023 kolumny. Jest to maksymalna liczba kolumn tabeli minus 1.

• Kolumny klucza indeksu, z wyłączeniem kolumn niebędących kluczami, muszą być zgodne z istniejącymi ograniczeniami rozmiaru indeksu, które wynoszą maksymalnie 16 kolumn klucza i łączny rozmiar klucza indeksu wynoszący 900 bajtów.

• Całkowity rozmiar wszystkich kolumn innych niżkey jest ograniczony tylko przez rozmiar kolumn określonych w INCLUDE klauzuli. Na przykład kolumny varchar(max) są ograniczone do 2 GB.

Wytyczne dotyczące modyfikacji kolumn

Podczas modyfikowania kolumny tabeli, która została zdefiniowana jako dołączona kolumna, obowiązują następujące ograniczenia:

• Nie można usunąć kolumn niewchodzących w skład klucza z tabeli, chyba że indeks zostanie usunięty jako pierwszy.

• Nie można zmieniać kolumn nie będących kluczem, z wyjątkiem następujących czynności:

  • Zmień wartość null kolumny z NOT NULL na NULL.

  • Zwiększ długość kolumn varchar, nvarcharlub varbinary.

    Uwaga / Notatka

    Te ograniczenia modyfikacji kolumn dotyczą również kolumn kluczy indeksu.

Zalecenia dotyczące projektowania

Przeprojektuj indeksy nieklastrowane z dużym rozmiarem klucza indeksu, aby tylko kolumny używane do wyszukiwania i odnajdywania były kolumnami kluczowymi. Oznacz wszystkie inne kolumny, które obejmują zapytanie, jako kolumny inne niż kluczowe. W ten sposób masz wszystkie kolumny potrzebne do pokrycia zapytania, ale sam klucz indeksu jest mały i wydajny.

Załóżmy na przykład, że chcesz zaprojektować indeks, aby obejmował następujące zapytanie.

SELECT AddressLine1,
       AddressLine2,
       City,
       StateProvinceID,
       PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';
GO

Aby pokryć zapytanie, każda kolumna musi być zdefiniowana w indeksie. Chociaż można zdefiniować wszystkie kolumny jako kolumny klucza, rozmiar klucza będzie wynosić 334 bajty. Ponieważ jedyną kolumną używaną jako kryteria wyszukiwania jest PostalCode kolumna, która ma długość 30 bajtów, lepszym projektem indeksu będzie zdefiniowana PostalCode jako kolumna klucza i dołączenia wszystkich innych kolumn jako kolumn niekluczowych.

Poniższa instrukcja tworzy indeks z dołączonymi kolumnami w celu pokrycia zapytania.

CREATE INDEX IX_Address_PostalCode
ON Person.Address(PostalCode)
    INCLUDE(AddressLine1, AddressLine2, City, StateProvinceID);

Aby sprawdzić, czy indeks obejmuje zapytanie, utwórz indeks, a następnie wyświetl szacowany plan wykonania.

Jeśli plan wykonywania pokazuje tylko SELECT operator i operator wyszukiwania indeksu dla indeksu IX_Address_PostalCode , zapytanie jest objęte indeksem.

Indeks można usunąć za pomocą następującej instrukcji:

DROP INDEX IX_Address_PostalCode
ON Person.Address;
GO

Zagadnienia dotyczące wydajności

Unikaj dodawania niepotrzebnych kolumn. Dodanie zbyt wielu kolumn indeksu, klucza lub nieklucza może mieć następujące konsekwencje dla wydajności:

• Mniej wierszy indeksu zmieści się na stronie. Może to spowodować zwiększenie wydajności operacji we/wy i zmniejszenie wydajności pamięci podręcznej.

• Do przechowywania indeksu jest wymagana większa ilość miejsca na dysku. W szczególności dodanie typów danych varchar(max), nvarchar(max), varbinary(max) lub xml jako kolumn indeksów niekluczowych może znacznie zwiększyć wymagania dotyczące miejsca na dysku. Dzieje się tak, ponieważ wartości kolumn są kopiowane na poziom liścia indeksu. W związku z tym znajdują się one zarówno w indeksie, jak i w tabeli podstawowej.

• Konserwacja indeksu może zwiększyć czas potrzebny na wykonanie modyfikacji, wstawiania, aktualizacji lub usuwania do bazowej tabeli lub widoku indeksowanego.

Należy określić, czy wzrost wydajności zapytań przewyższa wpływ na wydajność podczas modyfikowania danych i w dodatkowych wymaganiach dotyczących miejsca na dysku.

Unikatowe wytyczne dotyczące projektowania indeksu

Unikatowy indeks gwarantuje, że klucz indeksu nie zawiera zduplikowanych wartości i dlatego każdy wiersz w tabeli jest w jakiś sposób unikatowy. Określanie unikatowego indeksu ma sens tylko wtedy, gdy unikatowość jest cechą samych danych. Jeśli na przykład chcesz upewnić się, że wartości w kolumnie NationalIDNumber tabeli HumanResources.Employee są unikalne, gdy klucz podstawowy to EmployeeID, utwórz ograniczenie UNIQUE w kolumnie NationalIDNumber. Jeśli użytkownik spróbuje wprowadzić tę samą wartość w tej kolumnie dla więcej niż jednego pracownika, zostanie wyświetlony komunikat o błędzie i nie zostanie wprowadzona zduplikowana wartość.

W przypadku indeksów unikatowych wielokolumnowych indeks gwarantuje, że każda kombinacja wartości w kluczu indeksu jest unikatowa. Jeśli na przykład unikatowy indeks jest tworzony w kombinacji LastNamekolumn , FirstNamei MiddleName , żadne dwa wiersze w tabeli nie mogą mieć tej samej kombinacji wartości dla tych kolumn.

Indeksy klastrowane i nieklastrowane mogą być unikatowe. Jeśli dane w kolumnie są unikatowe, możesz utworzyć zarówno unikatowy indeks klastrowany, jak i wiele unikatowych indeksów nieklastrowanych w tej samej tabeli.

Zalety unikatowych indeksów obejmują następujące elementy:

• Zagwarantowana jest integralność danych zdefiniowanych kolumn.
• Dodatkowe informacje pomocne w optymalizatorze zapytań są udostępniane.

Utworzenie ograniczenia PRIMARY KEY lub UNIQUE powoduje automatyczne utworzenie unikatowego indeksu w określonych kolumnach. Nie ma znaczących różnic między tworzeniem UNIQUE ograniczenia a tworzeniem unikatowego indeksu niezależnie od ograniczenia. Walidacja danych odbywa się w taki sam sposób, a optymalizator zapytań nie rozróżnia unikatowego indeksu utworzonego przez ograniczenie ani ręcznie utworzonego. Należy jednak utworzyć ograniczenie UNIQUE lub PRIMARY KEY w kolumnie, gdy celem jest integralność danych. Dzięki temu cel indeksu jest jasny.

Rozważania

• Nie można utworzyć unikatowego indeksu, UNIQUE ograniczenia lub PRIMARY KEY ograniczenia, jeśli w danych istnieją zduplikowane wartości klucza.

• Jeśli dane są unikatowe i chcesz wymusić unikatowość, utworzenie unikatowego indeksu zamiast indeksu innego w tej samej kombinacji kolumn zawiera dodatkowe informacje dla optymalizatora zapytań, które mogą tworzyć bardziej wydajne plany wykonywania. W tym przypadku zaleca się utworzenie unikatowego indeksu (najlepiej przez utworzenie UNIQUE ograniczenia).

• Unikatowy indeks nieklastrowany może zawierać dołączone kolumny inne niż kluczowe. Aby uzyskać więcej informacji, zobacz Indeks z dołączonymi kolumnami.

Wytyczne dotyczące projektowania filtrowanego indeksu

Filtrowany indeks jest zoptymalizowanym indeksem nieklastrowanym, szczególnie dostosowanym do obsługi zapytań wybranych z dobrze zdefiniowanego podzestawu danych. Używa predykatu filtru do indeksowania części wierszy w tabeli. Dobrze zaprojektowany indeks filtrowany może poprawić wydajność zapytań, zmniejszyć koszty konserwacji indeksu i zmniejszyć koszty magazynowania indeksów w porównaniu z indeksami w pełnej tabeli.

Przefiltrowane indeksy mogą zapewnić następujące korzyści w stosunku do indeksów w pełnej tabeli:

• Zwiększona wydajność zapytań i jakość planu

  Dobrze zaprojektowany indeks filtrowany poprawia wydajność zapytań i jakość planu wykonywania, ponieważ jest mniejszy niż indeks nieklastrowany w pełnej tabeli i ma odfiltrowane statystyki. Przefiltrowane statystyki są dokładniejsze niż statystyki pełnej tabeli, ponieważ obejmują tylko wiersze w filtrowanym indeksie.

• Obniżone koszty konserwacji indeksu

  Indeks jest zachowywany tylko wtedy, gdy instrukcje języka manipulowania danymi (DML) wpływają na dane w indeksie. Filtrowany indeks zmniejsza koszty konserwacji indeksu w porównaniu z pełnotabelowym indeksem nieklastrowanym, ponieważ jest mniejszy i jest utrzymywany tylko wtedy, gdy zmienione dane w indeksie tego wymagają. Istnieje możliwość posiadania dużej liczby filtrowanych indeksów, zwłaszcza gdy zawierają one dane, które są rzadko modyfikowane. Podobnie, jeśli filtrowany indeks zawiera tylko często dotknięte dane, mniejszy rozmiar indeksu zmniejsza koszt aktualizowania statystyk.

• Obniżone koszty magazynowania indeksów

  Utworzenie filtrowanego indeksu może zmniejszyć magazyn dysków dla indeksów nieklastrowanych, gdy indeks pełnej tabeli nie jest konieczny. Można zastąpić pełnotabelowy indeks nieklastrowany wieloma indeksami filtrowanymi bez znacznego zwiększenia wymagań dotyczących pamięci masowej.

Indeksy filtrowane są przydatne, gdy kolumny zawierają dobrze zdefiniowane podzestawy danych, do których odwołują się zapytania w instrukcjach SELECT. Przykłady to:

• Rozrzedne kolumny zawierające tylko kilka wartości innych niżNULL .
• Heterogeniczne kolumny zawierające kategorie danych.
• Kolumny zawierające zakresy wartości, takie jak kwoty dolara, godzina i daty.
• Partycje tabel zdefiniowane przez prostą logikę porównania dla wartości kolumn.

Obniżone koszty konserwacji filtrowanych indeksów są najbardziej zauważalne, gdy liczba wierszy w indeksie jest niewielka w porównaniu z indeksem pełnej tabeli. Jeśli filtrowany indeks zawiera większość wierszy w tabeli, może to kosztować więcej niż indeks pełnej tabeli. W takim przypadku należy użyć indeksu pełnej tabeli zamiast filtrowanego indeksu.

Indeksy filtrowane są definiowane w jednej tabeli i obsługują tylko proste operatory porównania. Jeśli potrzebujesz wyrażenia filtru, które odwołuje się do wielu tabel lub ma złożoną logikę, należy utworzyć widok.

Uwagi dotyczące projektowania

Aby zaprojektować efektywne indeksy filtrowane, ważne jest, aby zrozumieć, jakie zapytania są używane przez aplikację i jak odnoszą się do podzestawów danych. Niektóre przykłady danych, które mają dobrze zdefiniowane podzestawy, to kolumny zawierające głównie NULL wartości, kolumny z heterogenicznymi kategoriami wartości i kolumn z odrębnymi zakresami wartości. Poniższe zagadnienia dotyczące projektowania dają różne scenariusze, gdy filtrowany indeks może zapewnić korzyści w stosunku do indeksów w pełnej tabeli.

Wskazówka

Definicja indeksu magazynu kolumn nieklastrowanego obsługuje używanie filtrowanego warunku. Aby zminimalizować wpływ na wydajność, jaki ma dodanie indeksu magazynującego kolumny w tabeli OLTP, użyj warunku filtrującego, aby utworzyć nieklastrowany indeks magazynujący kolumny wyłącznie na danych zimnych obciążenia operacyjnego.

Przefiltrowane indeksy dla podzestawów danych

Jeśli kolumna zawiera tylko kilka odpowiednich wartości dla zapytań, można utworzyć filtrowany indeks w podzestawie wartości. Jeśli na przykład wartości w kolumnie są w większości typu NULL, a zapytanie wybiera tylko spośród wartości innych niż NULL, można utworzyć filtrowany indeks dla wierszy z danymi innymi niż NULL. Wynikowy indeks jest mniejszy i kosztuje mniej konserwacji niż indeks nieklastrowany w pełnej tabeli zdefiniowany w tych samych kolumnach kluczy.

Na przykład przykładowa baza danych AdventureWorks zawiera tabelę Production.BillOfMaterials z 2679 wierszami. Kolumna EndDate zawiera tylko 199 wierszy, które zawierają inną NULL wartość, a pozostałe 2480 wierszy zawierają NULL. Poniższy filtrowany indeks obejmuje zapytania, które zwracają kolumny zdefiniowane w indeksie i wybierają tylko wiersze, w których dla NULL istnieje wartość inna niż EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials(ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

Filtrowany indeks FIBillOfMaterialsWithEndDate jest prawidłowy dla tego zapytania. Wyświetl szacowany plan wykonywania , aby określić, czy optymalizator zapytań użył filtrowanego indeksu.

SELECT ProductAssemblyID,
       ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
      AND ComponentID = 5
      AND StartDate > '20080101';
GO

Aby uzyskać więcej informacji na temat tworzenia filtrowanych indeksów i sposobu definiowania filtrowanego wyrażenia predykatu indeksu, zobacz Tworzenie filtrowanych indeksów.

Przefiltrowane indeksy dla danych heterogenicznych

Gdy tabela zawiera heterogeniczne wiersze danych, można utworzyć filtrowany indeks dla co najmniej jednej kategorii danych.

Na przykład produkty wymienione w Production.Product tabeli są przypisane do elementu ProductSubcategoryID, które są z kolei skojarzone z kategoriami produktów Rowery, Składniki, Odzież lub Akcesoria. Te kategorie są heterogeniczne, ponieważ ich wartości kolumn w Production.Product tabeli nie są ściśle skorelowane. Na przykład kolumny Color, , ReorderPointListPrice, Weight, Classi Style mają unikatowe cechy dla każdej kategorii produktów. Załóżmy, że istnieją częste zapytania dotyczące akcesoriów, które mają podkategorie z zakresu od 27 do 36 włącznie. Wydajność zapytań dotyczących akcesoriów można poprawić, tworząc filtrowany indeks podkategorii akcesoriów, jak pokazano w poniższym przykładzie.

CREATE NONCLUSTERED INDEX FIProductAccessories
ON Production.Product(ProductSubcategoryID, ListPrice)
INCLUDE(Name)
    WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

Filtrowany indeks FIProductAccessories obejmuje następujące zapytanie, ponieważ wyniki zapytania są zawarte w indeksie, a plan zapytania nie zawiera wyszukiwania w tabeli podstawowej. Na przykład wyrażenie predykatu zapytania ProductSubcategoryID = 33 jest podzbiorem predykatu filtrowanego indeksu ProductSubcategoryID >= 27 i ProductSubcategoryID <= 36, kolumny ProductSubcategoryID i ListPrice w predykacie zapytania są zarówno kolumnami kluczowymi w indeksie, a nazwa jest przechowywana na poziomie liścia indeksu jako dołączona kolumna.

SELECT Name,
       ProductSubcategoryID,
       ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33
      AND ListPrice > 25.00;
GO

Kolumny kluczy

Najlepszym rozwiązaniem jest uwzględnienie kilku kluczy lub uwzględnionych kolumn w filtrowanej definicji indeksu oraz uwzględnienie tylko kolumn, które są niezbędne dla optymalizatora zapytań w celu wybrania filtrowanego indeksu dla planu wykonywania zapytania. Optymalizator zapytań może wybrać filtrowany indeks dla zapytania, niezależnie od tego, czy indeks ten obejmuje zapytanie, czy nie. Jednak optymalizator zapytań jest bardziej skłonny do wyboru filtrowanego indeksu, jeśli indeks obejmuje zapytanie.

W niektórych przypadkach filtrowany indeks obejmuje zapytanie bez uwzględniania kolumn w filtrowanym wyrażeniu indeksu jako klucza lub uwzględnionych kolumn w filtrowanej definicji indeksu. Poniższe wskazówki wyjaśniają, kiedy kolumna w filtrowanym wyrażeniu indeksu powinna być kluczem lub dołączona kolumna w definicji filtrowanego indeksu. Przykłady odnoszą się do filtrowanego indeksu, FIBillOfMaterialsWithEndDate który został utworzony wcześniej.

Kolumna w filtrowanym wyrażeniu indeksu nie musi być kluczem ani uwzględniona kolumną w filtrowanej definicji indeksu, jeśli filtrowane wyrażenie indeksu jest równoważne predykatowi zapytania, a zapytanie nie zwraca kolumny w filtrowanym wyrażeniu indeksu z wynikami zapytania. Na przykład obejmuje następujące zapytanie, FIBillOfMaterialsWithEndDate ponieważ predykat zapytania jest odpowiednikiem wyrażenia filtru i EndDate nie jest zwracany z wynikami zapytania. FIBillOfMaterialsWithEndDate nie musi być EndDate kluczem ani dołączoną kolumną w definicji filtrowanego indeksu.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Kolumna w wyrażeniu indeksu filtrowanego powinna być kluczem lub kolumną dołączoną w definicji indeksu filtrowanego, jeśli predykat zapytania używa kolumny w porównaniu, które nie jest równoważne wyrażeniu indeksu filtrowanego. Na przykład FIBillOfMaterialsWithEndDate jest prawidłowe dla następującego zapytania, ponieważ wybiera podzbiór wierszy z filtrowanego indeksu. Nie obejmuje to jednak następującego zapytania, ponieważ EndDate jest używane w porównaniu EndDate > '20040101', które nie jest równoważne filtrowanemu wyrażeniu indeksu. Procesor zapytań nie może wykonać tego zapytania bez wyszukiwania wartości EndDate. EndDate powinna stać się kluczem lub dołączoną kolumną w definicji filtrowanego indeksu.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate > '20040101';

Kolumna w filtrowanym wyrażeniu indeksu powinna być kluczem lub dołączona kolumna w definicji filtrowanego indeksu, jeśli kolumna znajduje się w zestawie wyników zapytania. Na przykład FIBillOfMaterialsWithEndDate nie obejmuje następującego zapytania, ponieważ w wynikach zapytania zwraca kolumnę EndDate. EndDate powinna stać się kluczem lub dołączoną kolumną w definicji filtrowanego indeksu.

SELECT ComponentID,
       StartDate,
       EndDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Klucz w indeksie klastrowanym tabeli nie musi być kluczem ani kolumną dołączoną do definicji indeksu filtrowanego. Klucz indeksu klastrowanego jest automatycznie uwzględniany we wszystkich indeksach nieklastrowanych, w tym indeksach filtrowanych.

Aby usunąć indeksy FIBillOfMaterialsWithEndDate i FIProductAccessories, uruchom następujące instrukcje:

DROP INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials;
GO

DROP INDEX FIProductAccessories
ON Production.Product;
GO

Operatory konwersji danych w predykacie filtru

Jeśli operator porównania określony w filtrowanym wyrażeniu indeksu filtrowanego powoduje niejawną lub jawną konwersję danych, występuje błąd, jeśli konwersja występuje po lewej stronie operatora porównania. Rozwiązaniem jest zapisanie filtrowanego wyrażenia indeksu za pomocą operatora konwersji danych (CAST lub CONVERT) po prawej stronie operatora porównania.

Poniższy przykład tworzy tabelę z różnymi typami danych.

CREATE TABLE dbo.TestTable
(
    a INT,
    b VARBINARY (4)
);
GO

W poniższej przefiltrowanej definicji indeksu kolumna b jest niejawnie konwertowana na typ danych całkowitych w celu porównania jej z stałą 1. Spowoduje to wygenerowanie komunikatu o błędzie 10611, ponieważ konwersja występuje po lewej stronie operatora w przefiltrowanej predykacie.

CREATE NONCLUSTERED INDEX TestTabIndex
ON dbo.TestTable(a, b)
    WHERE b = 1;
GO

Rozwiązaniem jest przekonwertowanie stałej po prawej stronie na taki sam typ jak kolumna b, jak pokazano w poniższym przykładzie:

CREATE INDEX TestTabIndex
ON dbo.TestTable(a, b)
    WHERE b = CONVERT (VARBINARY (4), 1);
GO

Przeniesienie konwersji danych z lewej strony do prawej strony operatora porównania może zmienić znaczenie konwersji. W poprzednim przykładzie gdy operator CONVERT został dodany po prawej stronie, porównanie zmieniło się z porównania liczby całkowitej na porównanie varbinary.

Upuść obiekty utworzone w tym przykładzie, uruchamiając następującą instrukcję:

DROP TABLE TestTable;
GO

Architektura kolumnowego indeksu magazynu

Indeks magazynu kolumn to technologia do przechowywania, pobierania i zarządzania danymi przy użyciu formatu danych kolumnowych nazywanego magazynem kolumn. Aby uzyskać więcej informacji, zobacz Indeksy Columnstore: omówienie.

Aby uzyskać informacje o wersji i dowiedzieć się, co nowego się pojawiło, odwiedź stronę Co nowego w indeksach kolumnowych.

Znajomość tych podstaw ułatwia zrozumienie innych artykułów magazynu kolumn, które wyjaśniają, jak efektywnie ich używać.

Magazyn danych używa kompresji kolumnowej i wierszowej.

Podczas omawiania indeksów magazynu kolumn używamy terminów rowstore i columnstore , aby podkreślić format magazynu danych. Indeksy Columnstore używają obu typów przechowywania.

• Magazyn kolumn to dane uporządkowane logicznie jako tabela z wierszami i kolumnami oraz fizycznie przechowywane w formacie danych z kolumnami.

  Indeks kolumnowy przechowuje fizycznie większość danych w formacie kolumnowym. W formacie magazynu kolumn dane są kompresowane i dekompresowane w postaci kolumn. Nie ma potrzeby dekompresowania innych wartości w każdym wierszu, które nie są żądane przez zapytanie. Dzięki temu można szybko skanować całą kolumnę dużej tabeli.

• Rowstore to dane uporządkowane logicznie jako tabela z wierszami i kolumnami, a następnie fizycznie przechowywane w formacie wierszowym. Jest to tradycyjna metoda przechowywania danych tabeli relacyjnej, takich jak stos lub klastrowany indeks drzewa B+.

  Indeks columnstore przechowuje również kilka wierszy w formacie magazynu wierszy nazywanym deltastore. Magazyn delta, nazywany również grupami wierszy delta, jest miejscem przechowywania wierszy, które są zbyt nieliczne, aby mogły zostać skompresowane do magazynu kolumn. Każda grupa wierszy delta jest implementowana jako klasterowy indeks drzewa B+.

• Magazyn deltastore jest miejscem przechowywania wierszy, których jest zbyt mało, aby zostały skompresowane do magazynu kolumnowego. Magazyn deltastore przechowuje wiersze w formacie rowstore.

Aby uzyskać więcej informacji na temat terminów i pojęć związanych z columnstore, zapoznaj się z Indeksy columnstore: omówienie.

Operacje są wykonywane w grupach wierszy i segmentach kolumn

Indeks magazynu kolumn grupuje wiersze w jednostki możliwe do zarządzania. Każda z tych jednostek jest nazywana grupą wierszy. Aby uzyskać najlepszą wydajność, liczba wierszy w grupie wierszy jest wystarczająco duża, aby poprawić współczynnik kompresji i wystarczająco mały, aby korzystać z operacji w pamięci.

Na przykład indeks magazynu kolumn wykonuje następujące operacje w grupach wierszy:

• Kompresuje grupy wierszy do kolumnowego magazynu danych. Kompresja jest wykonywana w poszczególnych segmentach kolumn w grupie wierszy.
• Łączy grupy wierszy podczas operacji ALTER INDEX ... REORGANIZE, włączając usuwanie usuniętych danych.
• Tworzy nowe grupy wierszy podczas operacji ALTER INDEX ... REBUILD.
• Raporty dotyczące kondycji i fragmentacji grup rzędów w dynamicznych widokach zarządzania (DMV).

Magazyn różnicowy składa się z co najmniej jednej grupy wierszy nazywanej grupami wierszy różnicowych. Każda grupa wierszy delta jest klastrowanym indeksem drzewa B+, który przechowuje małe obciążenia zbiorcze i wstawienia do momentu, gdy grupa wierszy zawiera 1.048.576 wierszy, a wtedy proces nazywany "tuple-mover" automatycznie kompresuje zamkniętą grupę wierszy do składowiska kolumn.

Aby uzyskać więcej informacji na temat statusów grup kolumn, zobacz sys.dm_db_column_store_row_group_physical_stats.

Wskazówka

Zbyt wiele małych grup wierszy zmniejsza jakość indeksu kolumnowego. Operacja reorganizacji scala mniejsze grupy wierszy, postępując zgodnie z wewnętrznymi zasadami progowymi określającymi sposób usuwania usuniętych wierszy i łączenia skompresowanych grup wierszy. Po scaleniu należy poprawić jakość indeksu.

W programie SQL Server 2019 (15.x) i nowszych wersjach przenosiciel tupli wspierany jest przez zadanie scalania w tle, które automatycznie kompresuje mniejsze OPEN grupy wierszy różnicowych, które istnieją już od pewnego czasu według określonego progu wewnętrznego, lub scala COMPRESSED grupy wierszy, z których usunięto dużą liczbę wierszy.

Każda kolumna ma niektóre swoje wartości w grupach wierszy. Te wartości są nazywane segmentami kolumn. Każda grupa wierszy zawiera jeden segment kolumn dla każdej kolumny w tabeli. Każda kolumna ma jeden segment kolumny w każdej grupie wierszy.

Gdy indeks magazynu kolumn kompresuje grupę wierszy, kompresuje oddzielnie każdy segment kolumny. Aby usunąć cała kolumnę, indeks magazynu kolumn musi usunąć tylko jeden segment kolumny z każdej grupy wierszy.

Aby uzyskać więcej informacji na temat terminów i pojęć związanych z columnstore, zapoznaj się z Indeksy columnstore: omówienie.

Małe obciążenia i wkładki przechodzą do deltastore

Indeks kolumnowy poprawia kompresję i wydajność kolumn poprzez jednoczesne kompresowanie co najmniej 102 400 wierszy do indeksu kolumnowego. Aby skompresować wiersze zbiorczo, indeks magazynu kolumn gromadzi małe obciążenia i wstawia do magazynu różnicowego. Operacje deltastore są obsługiwane w tle. Aby zwrócić poprawne wyniki zapytania, klastrowany indeks columnstore łączy wyniki zarówno z columnstore, jak i deltastore.

Wiersze trafiają do deltastore, gdy są:

• Wstawiono instrukcję INSERT INTO ... VALUES .
• Na końcu obciążenia zbiorczego, jeśli ich liczba jest mniejsza niż 102 400.
• Aktualizowano. Każda aktualizacja jest implementowana jako usuwanie i wstawianie.

Magazyn różnicowy przechowuje również listę identyfikatorów wierszy oznaczonych jako usunięte, które nie zostały jeszcze fizycznie usunięte z magazynu kolumn.

Aby uzyskać więcej informacji na temat terminów i pojęć związanych z columnstore, zapoznaj się z Indeksy columnstore: omówienie.

Gdy grupy wierszy różnicowych są pełne, są kompresowane do magazynu kolumn

Klastrowane indeksy magazynu kolumn zbierają do 1048 576 wierszy w każdej grupie wierszy różnicowych przed skompresowaniem grupy wierszy do magazynu kolumn. To poprawia lepszą kompresję indeksu columnstore. Gdy delta rowgroup osiągnie maksymalną liczbę wierszy, przechodzi ze stanu OPEN do stanu CLOSED. Proces w tle o nazwie "tuple-mover" sprawdza zamknięte grupy wierszy. Jeśli proces znajdzie zamkniętą grupę wierszy, kompresuje grupę wierszy i zapisuje ją w magazynie kolumn.

Po skompresowaniu grupy wierszy różnicowych, istniejąca grupa przechodzi w TOMBSTONE stan, który ma zostać usunięty później przez mechanizm przenoszenia krotek, gdy nie będzie do niego żadnych odwołań, a nowa skompresowana grupa wierszy jest oznaczona jako COMPRESSED.

Aby uzyskać więcej informacji na temat statusów grup kolumn, zobacz sys.dm_db_column_store_row_group_physical_stats.

Grupy wierszy różnicowych można wymusić w magazynie kolumn przy użyciu polecenia ALTER INDEX , aby ponownie skompilować lub zreorganizować indeks. Jeśli podczas kompresji występuje wykorzystanie pamięci, indeks magazynu kolumn może zmniejszyć liczbę wierszy w skompresowanej grupie wierszy.

Aby uzyskać więcej informacji na temat terminów i pojęć związanych z columnstore, zapoznaj się z Indeksy columnstore: omówienie.

Każda partycja tabeli ma własne grupy wierszy i grupy wierszy różnicowych

Koncepcja partycjonowania jest taka sama zarówno w indeksie klastrowanym, stercie, jak i w indeksie kolumnowym. Partycjonowanie tabeli dzieli tabelę na mniejsze grupy wierszy zgodnie z zakresem wartości kolumn. Jest ona często używana do zarządzania danymi. Można na przykład utworzyć partycję dla każdego roku danych, a następnie użyć przełączania partycji do archiwizowania danych do tańszego magazynu. Przełączanie partycji działa na indeksach kolumnowych i ułatwia przenoszenie partycji danych do innej lokalizacji.

Grupy wierszy są zawsze definiowane w ramach partycji tabeli. Gdy indeks magazynu kolumn jest partycjonowany, każda partycja ma własne skompresowane pakiety wierszy i pakiety wierszy różnicowych.

Wskazówka

Rozważ użycie partycjonowania tabel, jeśli istnieje potrzeba usunięcia danych z magazynu kolumn. Wyłączanie i obcinanie partycji, które nie są już potrzebne, to efektywna strategia usuwania danych bez generowania fragmentacji wprowadzonej przez mniejsze grupy wierszy.

Każda partycja może mieć wiele grup wierszy różnicowych

Każda partycja może mieć więcej niż jedną grupę wierszy różnicowych. Gdy indeks kolumnowy musi dodać dane do grupy wierszy delta, a grupa wierszy delta jest zablokowana, indeks kolumnowy próbuje uzyskać blokadę w innej grupie wierszy delta. Jeśli nie ma dostępnych grup wierszy różnicowych, indeks magazynowania kolumnowego tworzy nową grupę wierszy różnicowych. Na przykład tabela z 10 partycjami może mieć 20 lub więcej grup wierszy delta.

Łączenie indeksów kolumnowych i wierszowych w tej samej tabeli

Indeks nieklastrowany zawiera kopię części lub wszystkich wierszy i kolumn w tabeli bazowej. Indeks jest definiowany jako co najmniej jedna kolumna tabeli i ma opcjonalny warunek, który filtruje wiersze.

Można utworzyć aktualizowalny indeks kolumnowy nieklastrowany na tabeli przechowującej wiersze. Indeks magazynowy kolumn przechowuje kopię danych, więc potrzebujesz dodatkowej przestrzeni pamięci. Jednak dane w indeksie magazynu kolumnowego są kompresowane do mniejszego rozmiaru niż wymaga tabela magazynu wierszowego. Dzięki temu można uruchomić analizę indeksu magazynu kolumn i transakcji w indeksie magazynu wierszy jednocześnie. Magazyn kolumn jest aktualizowany, gdy dane zmieniają się w tabeli typu rowstore, dlatego oba indeksy operują na tych samych danych.

W indeksie magazynu kolumn może znajdować się co najmniej jeden nieklastrowany indeks magazynu wierszy. Dzięki temu można wykonać wydajne wyszukiwanie tabel w bazowym magazynie kolumn. Inne opcje również staną się dostępne. Można na przykład wymusić ograniczenie klucza podstawowego poprzez zastosowanie ograniczenia UNIQUE na tabeli rowstore. Ponieważ nie można wstawić nieunikalnej wartości do tabeli rowstore, silnik bazy danych nie może wstawić wartości do magazynu kolumnowego.

Zagadnienia dotyczące wydajności

• Definicja indeksu magazynu kolumn nieklastrowanego obsługuje używanie filtrowanego warunku. Aby zminimalizować wpływ na wydajność, jaki ma dodanie indeksu magazynującego kolumny w tabeli OLTP, użyj warunku filtrującego, aby utworzyć nieklastrowany indeks magazynujący kolumny wyłącznie na danych zimnych obciążenia operacyjnego.

• Tabela w pamięci może mieć jeden indeks kolumnowy. Możesz to utworzyć podczas tworzenia tabeli lub dodać później za pomocą ALTER TABLE (Transact-SQL). Przed programem SQL Server 2016 (13.x) tylko tabela oparta na dysku może mieć indeks magazynu kolumn.

Aby uzyskać więcej informacji, zobacz Indeksy kolumnowe — wydajność zapytań.

Wskazówki projektowe

• Tabela typu rowstore może zawierać jeden indeks kolumnowy nieklastrowany, który można aktualizować. Przed programem SQL Server 2014 (12.x) indeks magazynu kolumn nieklastrowany był tylko do odczytu.

Aby uzyskać więcej informacji, zobacz Indeksy magazynu kolumn — wskazówki dotyczące projektowania.

Wskazówki dotyczące projektowania indeksu skrótu

Wszystkie tabele zoptymalizowane pod kątem pamięci muszą mieć co najmniej jeden indeks, ponieważ są to indeksy łączące wiersze ze sobą. W tabeli zoptymalizowanej pod kątem pamięci każdy indeks jest również zoptymalizowany pod kątem pamięci. Indeksy haszowe są jednym z możliwych typów indeksów w tabeli zoptymalizowanej dla pamięci. Aby uzyskać więcej informacji, zobacz Indeksy dotyczące tabel Memory-Optimized.

Dotyczy: SQL Server, Azure SQL Database i Azure SQL Managed Instance.

Architektura indeksu skrótu

Indeks skrótu składa się z tablicy wskaźników, a każdy element tablicy jest nazywany zasobnikiem skrótu.

• Każdy zasobnik to 8 bajtów, które są używane do przechowywania adresu pamięci listy łączy elementów kluczowych.
• Każdy wpis jest wartością klucza indeksu oraz adresem odpowiadającego mu wiersza w podstawowej tabeli zoptymalizowanej pod kątem pamięci.
• Każdy wpis wskazuje na kolejny wpis w łańcuchowej liście wpisów, wszystkie połączone z bieżącym kubełkiem.

Liczba zasobników musi być określona w czasie definicji indeksu:

• Im niższy stosunek zasobników do wierszy tabeli lub do odrębnych wartości, tym dłuższa jest średnia lista połączeń dla zasobników.
• Krótkie listy linków działają szybciej niż długie listy linków.
• Maksymalna liczba zasobników w indeksach skrótów wynosi 1073 741 824.

Wskazówka

Aby określić właściwy BUCKET_COUNT dla Twoich danych, zobacz Konfiguracja liczby zasobników indeksu skrótu.

Funkcja skrótu jest stosowana do kolumn klucza indeksu, a wynik funkcji określa, w jakim zasobniku znajduje się ten klucz. Każdy kubek ma wskaźnik do wierszy, których zahaszowane wartości klucza są przypisane do tego kubka.

Funkcja skrótu używana dla indeksów haszujących ma następujące cechy:

• Silnik bazy danych ma jedną funkcję skrótu używaną we wszystkich indeksach skrótów.
• Funkcja skrótu jest deterministyczna. Ta sama wartość klucza wejściowego jest zawsze mapowana na ten sam zasobnik w indeksie skrótu.
• Wiele kluczy indeksu może zostać zamapowanych na ten sam zasobnik skrótu.
• Funkcja skrótu jest zrównoważona, co oznacza, że rozkład wartości kluczy indeksu na zasobnikach skrótów zwykle następuje według rozkładu Poissona lub krzywej dzwonowej, a nie płaski liniowy rozkład.
• Rozkład Poissona nie jest równomiernym rozkładem. Wartości klucza indeksu nie są równomiernie dystrybuowane w zasobnikach skrótów.
• Jeśli dwa klucze indeksu są mapowane na ten sam zasobnik skrótu, występuje kolizja skrótu. Wysoka liczba kolizji skrótów może wpływać na wydajność operacji odczytu danych. Realistycznym celem jest, aby 30 procent pojemników zawierało dwie różne wartości klucza.

Interakcja indeksu skrótu i zasobników jest podsumowana na poniższym obrazie.

Skonfiguruj liczbę zasobników indeksu skrótu

Liczba zasobników indeksu skrótu jest określana przy tworzeniu indeksu i może zostać zmieniona przy użyciu składni ALTER TABLE...ALTER INDEX REBUILD.

W większości przypadków liczba koszy powinna idealnie wynosić od 1 do 2 razy liczby unikatowych wartości w kluczu indeksu.
Może nie zawsze być w stanie przewidzieć, ile wartości może mieć określony klucz indeksu lub które będą miały. Wydajność jest zwykle dobra, jeśli BUCKET_COUNT wartość znajduje się w granicach 10-krotności względem rzeczywistej liczby wartości klucza, a przeszacowanie jest ogólnie lepsze niż niedoszacowanie.

Zbyt mało zasobników może mieć następujące wady:

• Więcej kolizji skrótów odrębnych wartości klucza.
• Każda odrębna wartość jest wymuszana do współużytkowania tego samego zasobnika o innej odrębnej wartości.
• Średnia długość łańcucha na wiadro rośnie.
• Tym dłużej łańcuch zasobników, tym wolniejsza szybkość wyszukiwania równości w indeksie.

Zbyt wiele zasobników może mieć następujące wady:

• Zbyt wysoka liczba zasobników może spowodować więcej pustych zasobników.
• Puste zasobniki wpływają na wydajność pełnego skanowania indeksu. Jeśli skanowanie jest wykonywane regularnie, rozważ wybranie liczby zasobników zbliżonych do liczby unikatowych wartości klucza indeksu.
• Puste zasobniki używają pamięci, ale każdy zasobnik używa tylko 8 bajtów.

Uwaga / Notatka

Dodanie większej liczby zasobników nie zmniejsza łączenia wpisów, które mają zduplikowaną wartość. Szybkość duplikowania wartości służy do określania, czy skrót jest odpowiednim typem indeksu, a nie do obliczania liczby zasobników.

Zagadnienia dotyczące wydajności

Wydajność indeksu skrótu to:

• Doskonały, gdy predykat w WHERE klauzuli określa dokładną wartość dla każdej kolumny w kluczu indeksu skrótu. Indeks skrótu zmienia się na skanowanie przy użyciu predykatu nierówności.
• Słabe, gdy predykat w klauzuli WHERE szuka zakresu wartości w kluczu indeksu.
• Źle, gdy predykat w WHERE klauzuli określa jedną konkretną wartość dla pierwszej kolumny dwukolumnowego klucza indeksu skrótu, ale nie określa wartości dla innych kolumn klucza.

Wskazówka

Predykat musi zawierać wszystkie kolumny w kluczu indeksu haszującego. Indeks skrótu wymaga klucza (do wyznaczania skrótu) w celu wyszukiwania w indeksie.

Jeśli klucz indeksu składa się z dwóch kolumn, a klauzula WHERE zapewnia tylko pierwszą kolumnę, aparat bazy danych nie ma pełnego klucza do utworzenia skrótu. Spowoduje to utworzenie planu zapytania skanowania indeksu.

Jeśli jest używany indeks skrótu, a liczba unikatowych kluczy indeksu jest 100 razy (lub więcej) większa niż liczba wierszy, rozważ zwiększenie liczby zasobników, aby uniknąć dużych łańcuchów wierszy, lub zamiast tego użyj indeksu nieklastrowanego.

Zagadnienia dotyczące deklaracji

Indeks skrótu może być używany tylko w tabeli zoptymalizowanej dla pamięci. Nie może istnieć w tabeli opartej na dysku.

Indeks skrótu można zadeklarować jako:

• UNIQUElub może domyślnie ustawić wartość jako nieunikalna.
• NONCLUSTERED, który jest wartością domyślną.

Poniższa przykładowa składnia tworzy indeks skrótu poza instrukcją CREATE TABLE :

ALTER TABLE MyTable_memop ADD INDEX ix_hash_Column2
    UNIQUE NONCLUSTERED HASH (Column2) WITH (BUCKET_COUNT = 64);

Wersje rekordów i zarządzanie pamięcią

W tabeli zoptymalizowanej pod kątem pamięci, gdy wiersz jest zmieniany przez UPDATE, tabela tworzy zaktualizowaną wersję wiersza. Podczas transakcji aktualizacji inne sesje mogą być w stanie odczytać starszą wersję wiersza i uniknąć spowolnienia wydajności skojarzonego z blokadą wiersza.

Indeks skrótu może również mieć różne wersje swoich wpisów, aby uwzględnić aktualizację.

Później, gdy starsze wersje nie są już potrzebne, wątek odzyskiwania pamięci (GC) przechodzi przez zasobniki i ich listy linków, aby wyczyścić stare wpisy. Wątek GC działa lepiej, jeśli długość łańcucha listy łączy jest krótka. Aby uzyskać więcej informacji, zobacz In-Memory zarządzanie pamięcią OLTP.

Wytyczne dotyczące projektowania indeksu nieklastrowanego zoptymalizowane pod kątem pamięci

Indeksy nieklastrowane są jednym z możliwych typów indeksów w tabeli zoptymalizowanej pod kątem pamięci. Aby uzyskać więcej informacji, zobacz Indeksy dotyczące tabel Memory-Optimized.

Dotyczy: SQL Server, Azure SQL Database i Azure SQL Managed Instance.

Architektura indeksu nieklastrowanego w pamięci

Indeksy nieklastrowane w pamięci są implementowane przy użyciu struktury danych o nazwie Bw-tree, pierwotnie przewidywanej i opisanej przez Microsoft Research w 2011 roku. Drzewo Bw to wariacja drzewa B wolna od blokad oraz zatrzaśnięć. Aby uzyskać więcej informacji, zobacz Bw-tree: A B-tree for New Hardware Platforms (Drzewo B: drzewo B dla nowych platform sprzętowych).

Na wysokim poziomie drzewo Bw można zrozumieć jako mapę stron zorganizowanych według identyfikatora strony (PidMap), mechanizm przydzielania i ponownego użycia identyfikatorów stron (PidAlloc) oraz powiązany zestaw stron, zarówno w mapie, jak i między sobą. Te trzy podskładniki wysokiego poziomu składają się na podstawową strukturę wewnętrzną drzewa Bw.

Struktura jest podobna do normalnego drzewa B w tym sensie, że każda strona ma zestaw uporządkowanych kluczowych wartości, a w indeksie znajdują się poziomy wskazujące niższy poziom, a poziomy liści wskazują wiersz danych. Istnieje jednak kilka różnic.

Podobnie jak indeksy skrótów, wiele wierszy danych może być połączonych razem (wersje). Wskaźniki stron między poziomami to identyfikatory stron logicznych, które są przesunięte na tabelę mapowania stron, która z kolei ma adres fizyczny dla każdej strony.

Aktualizacje stron indeksu nie są wykonywane bezpośrednio. W tym celu wprowadzono nowe strony delta.

• W przypadku aktualizacji strony nie jest wymagane zatrzaśanie ani blokowanie.
• Strony indeksu nie są stałym rozmiarem.

Wartość klucza na każdej przedstawionej stronie poziomu nadrzędnego jest najwyższą wartością, którą zawiera element podrzędny, na który ta strona wskazuje, a każdy wiersz zawiera również identyfikator strony logicznej. Na stronach poziomu liściowego, oprócz wartości klucza, znajduje się fizyczny adres wiersza danych.

Wyszukiwania punktowe są podobne do drzew B, z tą różnicą, że strony są połączone tylko w jednym kierunku, a silnik bazy danych SQL Server podąża za prawymi wskaźnikami stron, gdzie każda strona niebędąca liściem ma najwyższą wartość swojego elementu podrzędnego, a nie najniższą wartość, jak w drzewie B.

Jeśli strona na poziomie liścia musi ulec zmianie, aparat bazy danych programu SQL Server nie modyfikuje samej strony. Raczej silnik bazy danych SQL Server tworzy rekord różnicowy, który opisuje zmianę i dołącza go do poprzedniej strony. Następnie aktualizuje adres w tabeli mapowania stron dla poprzedniej strony na adres rekordu delta, który teraz staje się fizycznym adresem dla tej strony.

Istnieją trzy różne operacje, które mogą być wymagane do zarządzania strukturą drzewa Bw: konsolidacja, podział i scalanie.

Konsolidacja delty

Długi łańcuch rekordów różnicowych może ostatecznie obniżyć wydajność wyszukiwania, ponieważ może to oznaczać, że przechodzimy przez długie łańcuchy podczas przeszukiwania indeksu. Jeśli nowy rekord różnicowy zostanie dodany do łańcucha, który ma już 16 elementów, zmiany w rekordach różnicowych są konsolidowane na stronie indeksu, do której się odwołuje, a strona jest następnie zrekonstruowana, w tym zmiany wskazywane przez nowy rekord różnicowy wywołujący konsolidację. Nowo utworzona strona ma ten sam identyfikator strony, ale nowy adres pamięci.

Podziel stronę

Strona indeksu w drzewie Bw rośnie zgodnie z potrzebami, począwszy od przechowywania pojedynczego wiersza do przechowywania maksymalnie 8 KB. Gdy strona indeksu wzrośnie do 8 KB, nowe wstawienie pojedynczego wiersza spowoduje podzielenie strony indeksu. W przypadku strony wewnętrznej oznacza to, że nie ma więcej miejsca na dodanie kolejnej wartości klucza i wskaźnika, a dla strony liściowej oznacza to, że wiersz będzie zbyt duży, aby zmieścić się na stronie po włączeniu wszystkich rekordów różnicowych. Informacje statystyczne w nagłówku strony liścia rejestrują wymaganą ilość miejsca na skonsolidowanie rekordów różnicowych. Te informacje są aktualizowane w miarę dodawania każdego nowego rekordu różnicowego.

Operacja podziału jest wykonywana w dwóch atomowych krokach. Na poniższym diagramie przyjęto założenie, że strona liściowa wymusza podział, ponieważ jest wstawiany klucz o wartości 5, a strona nie-liściowa wskazuje na koniec bieżącej strony na poziomie liściowym (wartość klucza 4).

Krok 1: Przydziel dwie nowe strony P1 i P2, i podziel wiersze ze starej P1 strony na te nowe strony, w tym nowo wstawiony wiersz. Nowe miejsce w tabeli mapowania stron służy do przechowywania fizycznego adresu strony P2. Te strony P1 i P2 nie są jeszcze dostępne dla żadnych współbieżnych operacji. Ponadto ustawiono wskaźnik logiczny od P1 do P2 . Następnie, w jednym kroku atomowym, zaktualizuj tabelę mapowania stron, aby zmienić wskaźnik ze starego P1 na nowy P1.

Krok 2. Strona nieliściowa wskazuje do P1 ale nie ma bezpośredniego wskaźnika ze strony nieliściowej do P2. P2 jest osiągalny tylko za pośrednictwem P1. Aby utworzyć wskaźnik ze strony niebędącej liściem do P2, przydziel nową stronę niebędącą liściem (wewnętrzną stronę indeksu), skopiuj wszystkie wiersze ze starej strony niebędącej liściem i dodaj nowy wiersz, aby wskazać na P2. Po wykonaniu tej czynności, zaktualizuj tabelę mapowania stron jednym atomowym krokiem, aby zmienić wskaźnik ze starej nie-liściowej strony na nową nie-liściową stronę.

Łączenie stron

DELETE Gdy operacja powoduje, że strona ma mniej niż 10 procent maksymalnego rozmiaru strony (obecnie 8 KB) lub z pojedynczym wierszem na niej, ta strona jest scalona z ciągłą stroną.

Po usunięciu wiersza ze strony dodawany jest rekord różnicowy usuwania. Ponadto sprawdza się, czy strona indeksu (strona pośrednia) kwalifikuje się do scalania. To sprawdzenie sprawdza, czy pozostałe miejsce po usunięciu wiersza jest mniejsze niż 10 procent maksymalnego rozmiaru strony. Jeśli spełnia kryteria, scalanie odbywa się w trzech niepodzielnych krokach.

Na poniższej ilustracji założono, że DELETE operacja usuwa wartość klucza 10.

pl-PL: Krok 1: Zostanie utworzona strona delta reprezentująca wartość 10 klucza (niebieski trójkąt), a jej wskaźnik na stronie Pp1 nie-liściowej jest ustawiony na nową stronę delta. Ponadto jest tworzona specjalna strona różnicowa scalania (zielony trójkąt) i jest połączona w taki sposób, aby wskazywała stronę różnicową. Na tym etapie zarówno strona różnicowa, jak i strona scalania różnic nie są widoczne dla żadnej równoczesnej transakcji. W jednym atomowym kroku wskaźnik do strony P1 na poziomie liścia w tabeli mapowania stron jest aktualizowany, aby wskazać scalanej delta-strony. Po wykonaniu tego kroku wpis dla wartości klucza 10 w Pp1 wskazuje teraz na stronę delta-scalania.

Krok 2: Wiersz reprezentujący wartość klucza 7 na stronie nieliściastej musi zostać usunięty, a wpis dla wartości klucza Pp1 musi zostać zaktualizowany tak, aby wskazywał 10. W tym celu zostanie przydzielona nowa nielistna strona Pp2 i wszystkie wiersze z Pp1 zostaną skopiowane, z wyjątkiem wiersza reprezentującego wartość klucza 7; następnie wiersz dla wartości klucza 10 zostanie zaktualizowany, aby wskazywać na stronę P1. Po wykonaniu tej czynności, w jednym niepodzielnym kroku, wpis tabeli mapowania strony wskazujący na Pp1 zostanie zaktualizowany, aby wskazywać na Pp2. Pp1 nie jest już osiągalny.

Krok 3: Strony P2 na poziomie liścia i P1 są scalane, a strony różnicowe są usuwane. W tym celu przydzielana jest nowa strona P3, a wiersze z P2 i P1 są scalane, a zmiany strony różnicowej są uwzględniane w nowym P3. Następnie, w jednym atomowym kroku, wpis w tabeli mapowania strony wskazujący na stronę P1 jest aktualizowany, aby wskazywał na stronę P3.

Zagadnienia dotyczące wydajności

Wydajność indeksu nieklastrowanego jest lepsza niż indeksy skrótów nieklastrowanych podczas wykonywania zapytań względem tabeli zoptymalizowanej pod kątem pamięci z predykatami nierówności.

Kolumna w tabeli zoptymalizowanej pod kątem pamięci może być częścią zarówno indeksu skrótu, jak i indeksu nieklastrowanego.

Gdy kluczowa kolumna w indeksie nieklastrowanym ma wiele zduplikowanych wartości, może to pogorszyć wydajność podczas aktualizacji, wstawiania i usuwania. Jednym ze sposobów poprawy wydajności w tej sytuacji jest dodanie kolumny, która ma lepszą selektywność w kluczu indeksu.

Treści powiązane

• STWÓRZ INDEKS (Transact-SQL)
• Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów
• partycjonowane tabele i indeksy
• Indeksy w tabelach Memory-Optimized
• Indeksy kolumnowe: omówienie
• Indeksy w kolumnach obliczanych
• Dostrajanie indeksów nieklastrowanych za pomocą sugestii brakujących indeksów