Projektowanie indeksów

• 20 min

Program SQL Server ma kilka typów indeksów do obsługi różnych typów obciążeń. Na wysokim poziomie indeks można traktować jako strukturę dysku skojarzona z tabelą lub widokiem, która umożliwia programowi SQL Server łatwiejsze znajdowanie wiersza lub wierszy skojarzonych z kluczem indeksu (który składa się z co najmniej jednej kolumny w tabeli lub widoku) w porównaniu ze skanowaniem całej tabeli.

Indeksy klastrowane

Typowym pytaniem w wywiadzie dla pracy dba jest zadawanie kandydatowi różnicy między indeksem klastrowanym i nieklastrowanym, ponieważ indeksy są podstawową technologią przechowywania danych w programie SQL Server. Indeks klastrowany to tabela bazowa przechowywana w kolejności posortowanej na podstawie wartości klucza. W danej tabeli może istnieć tylko jeden indeks klastrowany, ponieważ wiersze mogą być przechowywane w jednej kolejności. Tabela bez indeksu klastrowanego jest nazywana stertą, a sterta są zwykle używane tylko jako tabele przejściowe. Ważną zasadą projektowania wydajności jest utrzymanie klucza indeksu klastrowanego tak wąskie, jak to możliwe. Rozważając kolumny kluczy dla indeksu klastrowanego, należy wziąć pod uwagę kolumny, które są unikatowe lub zawierają wiele odrębnych wartości. Inną właściwością dobrego klastrowanego klucza indeksu są rekordy, do których uzyskuje się dostęp sekwencyjnie i są często używane do sortowania danych pobranych z tabeli. Użycie indeksu klastrowanego w kolumnie używanej do sortowania może zapobiec kosztowi sortowania za każdym razem, gdy wykonywane jest zapytanie, ponieważ dane będą już przechowywane w żądanej kolejności.

Indeksy nieklastrowane

Indeksy nieklastrowane są oddzielną strukturą od wierszy danych. Indeks nieklastrowany zawiera wartości klucza zdefiniowane dla indeksu oraz wskaźnik do wiersza danych zawierającego wartość klucza. Możesz dodać kolejną kolumnę nonkey do poziomu liści indeksu nieklastrowanego, aby pokryć więcej kolumn przy użyciu funkcji dołączonych kolumn w programie SQL Server. W tabeli można utworzyć wiele indeksów nieklastrowanych.

Poniżej przedstawiono przykład konieczności dodania indeksu lub dodania kolumn do istniejącego indeksu nieklastrowanego:

Plan zapytania wskazuje, że dla każdego wiersza pobranego przy użyciu wyszukiwania indeksu należy pobrać więcej danych z klastrowanego indeksu (samej tabeli). Istnieje indeks nieklastrowany, ale zawiera tylko kolumnę produktu. Jeśli dodasz inne kolumny w zapytaniu do nieklastrowanego indeksu, jak pokazano poniżej, możesz zobaczyć zmianę planu wykonywania, aby wyeliminować wyszukiwanie klucza.

Utworzony powyżej indeks jest przykładem indeksu obejmującego, gdzie oprócz kolumny klucza dołączasz dodatkowe kolumny do pokrycia zapytania i eliminuje potrzebę uzyskania dostępu do samej tabeli.

Zarówno indeksy nieklastrowane, jak i klastrowane można zdefiniować jako unikatowe, co oznacza, że nie można duplikować wartości kluczy. Indeksy unikatowe są tworzone automatycznie podczas tworzenia klucza podstawowego lub ograniczenia UNIKATOWEGO w tabeli.

Ta sekcja koncentruje się na indeksach b-tree w programie SQL Server — są one również nazywane indeksami magazynu wierszy. Poniżej przedstawiono ogólną strukturę drzewa b:

Każda strona w drzewie indeksu b-tree jest nazywana węzłem indeksu, a górny węzeł b-tree jest nazywany węzłem głównym. Dolne węzły w indeksie są nazywane węzłami liścia, a kolekcja węzłów liścia jest poziomem liścia.

Projektowanie indeksu to mieszanka sztuki i nauki. Wąski indeks z kilkoma kolumnami w kluczu wymaga mniej czasu na aktualizację i ma mniejsze obciążenie konserwacyjne; jednak może nie być przydatne w przypadku tak wielu zapytań, jak szerszy indeks, który zawiera więcej kolumn. Może być konieczne eksperymentowanie z kilkoma metodami indeksowania na podstawie kolumn wybranych przez zapytania aplikacji. Optymalizator zapytań zazwyczaj wybiera to, co uważa za najlepszy istniejący indeks dla zapytania; nie oznacza to jednak, że nie ma lepszego indeksu, który można utworzyć.

Prawidłowe indeksowanie bazy danych jest złożonym zadaniem. Podczas planowania indeksów dla tabeli należy pamiętać o kilku podstawowych zasadach:

• Omówienie obciążeń systemu. Tabela, która jest używana głównie do operacji wstawiania, będzie znacznie mniejsza od dodatkowych indeksów niż tabela używana w przypadku operacji magazynu danych, które są w 90% aktywności odczytu.
• Dowiedz się, jakie zapytania są uruchamiane najczęściej, i optymalizuj indeksy wokół tych zapytań.
• Poznaj typy danych kolumn używanych w zapytaniach. Indeksy są idealne dla typów danych całkowitych lub kolumn unikatowych lub innych niż null.
• Utwórz indeksy nieklastrowane w kolumnach, które są często używane w predykatach i klauzulach sprzężenia, i zachowaj te indeksy tak wąskie, jak to możliwe, aby uniknąć narzutu.
• Zrozumienie rozmiaru/woluminu danych — skanowanie tabeli w małej tabeli będzie stosunkowo tanią operacją, a program SQL Server może zdecydować się na skanowanie tabeli po prostu dlatego, że jest to łatwe (proste) do wykonania. Skanowanie tabeli w dużej tabeli byłoby kosztowne.

Inną opcją programu SQL Server jest utworzenie filtrowanych indeksów. Przefiltrowane indeksy najlepiej nadają się do kolumn w dużych tabelach, w których duża część wierszy ma taką samą wartość w tej kolumnie. Praktycznym przykładem może być tabela pracowników, jak pokazano poniżej, która przechowuje rekordy wszystkich pracowników, w tym tych, którzy opuścili lub wycofali się.

CREATE TABLE [HumanResources].[Employee](
     [BusinessEntityID] [int] NOT NULL,
     [NationalIDNumber] [nvarchar](15) NOT NULL,
     [LoginID] [nvarchar](256) NOT NULL,
     [OrganizationNode] [hierarchyid] NULL,
     [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
     [JobTitle] [nvarchar](50) NOT NULL,
     [BirthDate] [date] NOT NULL,
     [MaritalStatus] [nchar](1) NOT NULL,
     [Gender] [nchar](1) NOT NULL,
     [HireDate] [date] NOT NULL,
     [SalariedFlag] [bit] NOT NULL,
     [VacationHours] [smallint] NOT NULL,
     [SickLeaveHours] [smallint] NOT NULL,
     [CurrentFlag] [bit] NOT NULL,
     [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
     [ModifiedDate] [datetime] NOT NULL)

W tej tabeli znajduje się kolumna o nazwie CurrentFlag, która wskazuje, czy pracownik jest obecnie zatrudniony. W tym przykładzie użyto typu danych bitowych wskazujących tylko dwie wartości, jedną dla aktualnie zatrudnionych i zero dla aktualnie niezatrudnionych. Filtrowany indeks z kolumną WHERE CurrentFlag = 1, w kolumnie CurrentFlag umożliwia wydajne zapytania bieżących pracowników.

Można również tworzyć indeksy w widokach, co może zapewnić znaczne wzrosty wydajności, gdy widoki zawierają elementy zapytania, takie jak agregacje i/lub sprzężenia tabeli.

Indeksy magazynu kolumn

Magazyn kolumn oferuje lepszą wydajność zapytań, które uruchamiają duże obciążenia agregacji. Ten typ indeksu był pierwotnie przeznaczony dla magazynów danych, ale w czasie indeksy magazynu kolumn zostały użyte w wielu innych obciążeniach, aby ułatwić rozwiązywanie problemów z wydajnością zapytań w dużych tabelach. Od programu SQL Server 2014 istnieją zarówno nieklastrowane, jak i klastrowane indeksy magazynu kolumn. Podobnie jak indeksy b-tree, indeks klastrowanego magazynu kolumn jest tabelą przechowywaną w specjalny sposób, a indeksy magazynu kolumn nieklastrowanych są przechowywane niezależnie od tabeli. Klastrowane indeksy magazynu kolumn z założenia zawierają wszystkie kolumny w danej tabeli. Jednak w przeciwieństwie do indeksów klastrowanych magazynu wierszy indeksy klastrowanego magazynu kolumn nie są sortowane.

Indeksy nieklastrowanego magazynu kolumn są zwykle używane w dwóch scenariuszach, a pierwszy to, gdy kolumna w tabeli ma typ danych, który nie jest obsługiwany w indeksie magazynu kolumn. Większość typów danych jest obsługiwana, ale pliki XML, CLR, sql_variant, ntext, tekst i obraz nie są obsługiwane w indeksie magazynu kolumn. Ponieważ klastrowany magazyn kolumn zawsze zawiera wszystkie kolumny tabeli (ponieważ jest tabelą), jedyną opcją jest nieklastrowana. Drugi scenariusz jest indeksem filtrowanym — ten scenariusz jest używany w architekturze nazywanej hybrydowym przetwarzaniem analitycznym transakcyjnym (HTAP), gdzie dane są ładowane do tabeli bazowej, a jednocześnie raporty są uruchamiane w tabeli. Filtrując indeks (zazwyczaj w polu daty), ten projekt umożliwia zarówno dobre wstawianie, jak i raportowanie wydajności.

Indeksy magazynu kolumn są unikatowe w mechanizmie przechowywania, dlatego każda kolumna w indeksie jest przechowywana niezależnie. Oferuje ona 2-krotną korzyść. Zapytanie korzystające z indeksu magazynu kolumn musi skanować tylko kolumny potrzebne do spełnienia zapytania, zmniejszając łączną liczbę wykonanych operacji we/wy i umożliwiając większą kompresję, ponieważ dane w tej samej kolumnie prawdopodobnie będą podobne.

Indeksy magazynu kolumn działają najlepiej w przypadku zapytań analitycznych, które skanują duże ilości danych, takie jak tabele faktów w magazynie danych. Począwszy od programu SQL Server 2016, można rozszerzyć indeks magazynu kolumn za pomocą innego indeksu nieklastrowanego drzewa b, co może być przydatne, jeśli niektóre zapytania wykonują wyszukiwania względem pojedynczych wartości.

Indeksy magazynu kolumn korzystają również z trybu wykonywania wsadowego, który odwołuje się do przetwarzania zestawu wierszy (zazwyczaj około 900) jednocześnie w porównaniu z aparatem bazy danych przetwarzającym te wiersze pojedynczo. Zamiast niezależnie ładować każdy rekord i przetwarzać je, aparat zapytań oblicza obliczenia w tej grupie 900 rekordów. Ten model przetwarzania znacznie zmniejsza liczbę instrukcji procesora CPU.

SELECT SUM(Sales) FROM SalesAmount;

Tryb wsadowy może zapewnić znaczny wzrost wydajności w przypadku tradycyjnego przetwarzania wierszy. Program SQL Server 2019 zawiera również tryb wsadowy dla danych magazynu wierszy. Chociaż tryb wsadowy dla magazynu wierszy nie ma takiego samego poziomu wydajności odczytu jak indeks magazynu kolumn, zapytania analityczne mogą zobaczyć maksymalnie 5-krotną poprawę wydajności.

Inne korzyści indeksy magazynu kolumn oferują obciążenia magazynu danych to zoptymalizowana ścieżka ładowania dla operacji wstawiania zbiorczego 102 400 wierszy lub więcej. Chociaż 102 400 jest minimalną wartością do załadowania bezpośrednio do magazynu kolumn, każda kolekcja wierszy nazywana grupą wierszy może wynosić do około 1024 000 wierszy. Po mniejszej liczbie, ale pełniej, grupy wierszy sprawiają, że zapytania SELECT są wydajniejsze, ponieważ aby pobrać żądane rekordy, należy przeskanować mniej grup wierszy. Te obciążenia odbywają się w pamięci i są ładowane bezpośrednio do indeksu. W przypadku mniejszych woluminów dane są zapisywane w strukturze drzewa b o nazwie magazyn różnicowy i asynchronicznie ładowane do indeksu.

W tym przykładzie te same dane są ładowane do dwóch tabel, FactResellerSales_CCI_Demo i FactResellerSales_Page_Demo. FactResellerSales_CCI_Demo ma klastrowany indeks magazynu kolumn, a FactResellerSales_Page_Demo ma klastrowany indeks b-tree z dwiema kolumnami i jest skompresowany na stronie. Jak widać, każda tabela ładuje 1024 000 wierszy z tabeli FactResellerSalesXL_CCI . Gdy SET STATISTICS TIME parametr to ON, program SQL Server śledzi czas, który upłynął podczas wykonywania zapytania. Załadowanie danych do tabeli magazynu kolumn trwało około 8 sekund, gdzie ładowanie do skompresowanej tabeli strony trwało prawie 20 sekund. W tym przykładzie wszystkie wiersze przechodzące do indeksu magazynu kolumn są ładowane do pojedynczej grupy wierszy.

Jeśli załadujesz mniej niż 102 400 wierszy danych do indeksu magazynu kolumn w ramach pojedynczej operacji, zostanie załadowany w strukturze drzewa b znanej jako magazyn różnicowy. Aparat bazy danych przenosi te dane do indeksu magazynu kolumn przy użyciu asynchronicznego procesu nazywanego krotką mover. Otwarcie magazynów różnicowych może mieć wpływ na wydajność zapytań, ponieważ odczytywanie tych rekordów jest mniej wydajne niż odczytywanie z magazynu kolumn. Indeks można również zreorganizować za pomocą COMPRESS_ALL_ROW_GROUPS opcji , aby wymusić dodanie i skompresowanie magazynów różnicowych do indeksów magazynu kolumn.