--Indeks to struktura na dysku powiązana z tabelą lub widokiem i przyspieszająca pobieranie wierszy z tabeli lub widoku. Indeks zawiera klucze utworzone na podstawie jednej lub większej liczby kolumn tabeli lub widoku. Klucze te są przechowywane w zrównoważonej strukturze drzewa, która umożliwia szybkie wyszukiwanie wierszy według ich wartości kluczy w SQL Server.

--Indeksy klastrowe sortują i przechowują wiersze danych w tabelach lub widokach na podstawie ich kluczowych wartości. Wartości te są kolumnami zawartymi w definicji indeksu. Na każdą tabelę przypada tylko jeden indeks klastrowy, ponieważ wiersze danych można sortować tylko w jednej kolejności.
--Wiersze danych w tabeli są przechowywane w kolejności sortowania tylko wtedy, gdy tabela zawiera indeks klastrowy. Jeśli tabela ma indeks klastrowy, wówczas nazywa się ją klastrowaną. Jeśli tabela nie ma indeksu klastrowego, wiersze danych są przechowywane w nieuporządkowanej strukturze zwanej stertą.

--Indeks nieklastrowany ma dokładnie taką samą strukturę jak indeks klastrowany, ale z dwiema istotnymi różnicami:
--indeks nieklastrowany nie zmienia fizycznej kolejności wierszy w tabeli, a strony liści w indeksie nieklastrowanym składają się z kluczy indeksu i zakładek.

--Indeksy klastrowane zapewniają szybsze pobieranie danych niż indeksy nieklastrowane. Zwykle okazują się szybsze także podczas aktualizacji, ale nie wtedy, gdy wiele aktualizacji dzieje się w tym samym miejscu w środku relacji.

--Z jakiegoś powodu indeks klastrowy zwykle działa szybciej niż indeks nieklastrowany. Kiedy system skanuje indeks klastrowy, nie ma potrzeby opuszczania struktury drzewa B w celu przeskanowania stron danych, ponieważ takie strony są już obecne na poziomie liścia drzewa.

--Indeks nieklastrowany wymaga również większej liczby operacji we/wy niż odpowiadający mu indeks klastrowany.

--Indeks nieklastrowany musi odczytać strony danych po przeskanowaniu drzewa B lub, jeśli w innej kolumnie(-ach) tabeli znajduje się indeks klastrowy, indeks nieklastrowany musi odczytać strukturę drzewa B indeksu klastrowego .

--Tak więc indeks klastrowy będzie znacznie szybszy niż skanowanie tabeli, nawet jeśli jego selektywność jest dość słaba (zapytanie zwraca wiele wierszy)

UTWÓRZ TABELĘ tsql.dbo.NI
Identyfikator int NIE NULL,
T char(8) NULL
);

UTWÓRZ TABELĘ tsql.dbo.NCI
Identyfikator int NIE NULL,
T char(8) NULL
);

--Utwórz indeks klastrowy

UTWÓRZ INDEKS KLASTROWANY IX_1
ON tsql.dbo.NCI(ID);

--Utwórz indeks nieklastrowy w tabeli

UTWÓRZ INDEKS NIEKLASTROWANY IX_2
ON tsql.dbo.NCI(T);

--Dodaj dane testowe
DEKLARACJA @i INT = 100000;
Zdeklaruj @t CHAR(1) = "T";

PODCZAS @i > 0
ZACZYNAĆ
wstaw do wartości tsql.dbo.NI(@i, @t + CAST(@i AS char(6)));
wstaw do wartości tsql.dbo.NCI(@i, @t + CAST(@i AS char(6)));
USTAW @i -= 1;
KONIEC

--Zapytania dotyczące tabeli z indeksami
WYBIERZ IDENTYFIKATOR, T Z tsql.dbo.NCI
ZAMÓW PRZEZ IDENTYFIKATOR, T

WYBIERZ ID, LICZBĘ(*) JAKO C Z tsql.dbo.NCI
GRUPUJ WEDŁUG ID, T

WYBIERZ IDENTYFIKATOR, T Z tsql.dbo.NCI
GDZIE ID > 4000 ORAZ ID< 55000 AND T LIKE "T%"

--Zapytanie przy użyciu obu indeksów
UŻYJ tsql;
WYBIERZ RASĘ (dbo.NCI.ID JAKO VARCHAR)
Z dbo.NCI
GRUPA WEDŁUG dbo.NCI.ID
UNIA WSZYSTKICH
WYBIERZ dbo.NCI.T
Z dbo.NCI
GRUPA WEDŁUG dbo.NCI.T

--Indeksy
SELECT typ_indeksu_desc, głębokość_indeksu, poziom_indeksu,
liczba_stron, liczba_rekordów
Z sys.dm_db_index_physical_stats
(DB_ID(N"tsql"), OBJECT_ID(N"dbo.NCI"), NULL, NULL , "SZCZEGÓŁOWE");

--Usuwanie indeksów
JEŚLI ISTNIEJE (WYBIERZ nazwę Z sys.indexes
GDZIE nazwa = N"IX_1")
USUŃ INDEKS IX_1 NA tsql.dbo.NCI;

JEŚLI ISTNIEJE (WYBIERZ nazwę Z sys.indexes
GDZIE nazwa = N"IX_2")
USUŃ INDEKS IX_2 NA tsql.dbo.NCI;

W poprzednim artykule przedstawiliśmy sposoby optymalizacji relacyjnych baz danych oraz omówiliśmy działanie indeksów klastrowych i nieklastrowych w kontekście optymalizacji czasu wykonania zapytania do bazy danych. Teraz czas zastosować tę wiedzę w praktyce, ucząc się tworzenia indeksów optymalizacyjnych dla bazy danych MS SQL.

Przypomnę definicję schematu tabeli Staffs, z którą będziemy pracować:

Stół pracowniczy

Załóżmy, że musimy utworzyć indeks nieklastrowy dla tabeli Staffs, co zoptymalizuje następujące zapytanie:

WYBIERZ Identyfikator, Nazwisko, Stanowisko Z Rzeczy GDZIE WYNAGRODZENIE > 1000 I Zdjęcie NIE JEST NULL

Kluczem indeksu będą kolumny WYNAGRODZENIE i Zdjęcie, ponieważ wybór jest filtrowany według tych pól. Kolumny Id, Name i Job będą kolumnami uwzględnionymi w indeksie.

Ogólna składnia poleceń jest następująca:

UŻYWAĆ IŚĆ

UTWÓRZ INDEKS NIEKLASTROWANY NA (ASC — kolumny klucza indeksu)

WŁĄCZAĆ ( - uwzględnione kolumny) GO

W naszym przypadku żądanie będzie wyglądać następująco:

(Wynagrodzenie, zdjęcie) Uwzględnij (Identyfikator, imię i nazwisko, stanowisko) PRZEJDŹ

Stworzyliśmy indeks nieklastrowany. A raczej nieklastrowany indeks obejmujący. Oznacza to, że indeks zawiera wszystkie pola niezbędne do wykonania zapytania i SQL Server nie będzie miał dostępu do tabeli bazowej podczas wykonywania zapytania.

Gdyby nasz kod wyglądał tak:

UTWÓRZ INDEKS NIEKLASTROWANY IDX_StaffsSearch ON Rzeczy

(Wynagrodzenie, zdjęcie) Uwzględnij (Id) GO

W tym przypadku indeks przestaje być indeksem pokrywającym, gdyż nie uwzględnia wszystkich kolumn użytych w zapytaniu. Optymalizator będzie nadal korzystał z tego indeksu podczas wykonywania zapytania, ale jego wydajność zostanie zmniejszona o rząd wielkości, ponieważ będzie wymagał dostępu do tabeli podstawowej.

Indeks klastrowy tworzony jest za pomocą następującego polecenia:

UTWÓRZ INDEKS KLASTROWANY IDX_Stsffsid ON Rzeczy (Id)

Tutaj utworzono unikalny indeks klastrowy na podstawie klucza podstawowego tabeli (kolumna Id).

Prawdziwy przykład

Opracujmy teraz scenariusz, w którym będziemy mogli realistycznie ocenić stopień wzrostu wydajności w przypadku wykorzystania indeksów.

Stwórzmy nową bazę danych:

UTWÓRZ BAZĘ DANYCH TestDB;

Oraz pojedynczą tabelę Klienci, która będzie składać się z czterech kolumn:

UTWÓRZ TABELĘ.(

NIE NULL, NULL, NULL, NULL) PRZEJDŹ

Teraz wypełnijmy naszą tabelę losowymi danymi. Kolumna Id zostanie powiększona w pętli, a pozostałe trzy kolumny tabeli zostaną wypełnione liczbami losowymi za pomocą osobliwej wersji funkcji losowej:

DEKLARACJA @i int = 0;

Podczas gdy ja< 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(suma kontrolna(newid())), abs(suma kontrolna(newid())), abs(suma kontrolna(newid())) SET @i = @i + 1; KONIEC

Skrypt ten dodaje do tabeli pół miliona rekordów, więc bądź cierpliwy, skrypt będzie działał przez co najmniej 3 minuty.

Wszystko jest gotowe do testu. Ocenimy charakterystykę wydajności zapytania. Ponieważ czas wykonania zapytania może zależeć od konkretnej maszyny, przeanalizujemy bardziej niezależny wskaźnik - liczbę odczytów logicznych.

Aby włączyć tryb zbierania statystyk, należy uruchomić następującą komendę:

Teraz po wykonaniu każdego żądania, w zakładce Wiadomości będziemy mieli dostęp do statystyk realizacji tego żądania, jak pokazano poniżej:

Nas interesuje tylko wartość parametru odczytów logicznych.

Zatem w naszej tabeli nie ma jeszcze indeksów. Uruchommy następujące trzy zapytania i zapiszmy liczbę odczytów logicznych dla każdego zapytania w poniższej tabeli wyników:

1) WYBIERZ Id, Num1, Num2 OD Klientów GDZIE Id = 2000

2) WYBIERZ Id, Num1, Num2 OD klientów GDZIE Id >= 0 ORAZ Id< 1000

3) WYBIERZ Id, Num1, Num2 OD Klientów GDZIE Id >= 0 ORAZ Id< 5000

Zapytania te zwrócą odpowiednio 1 wiersz, 1000 wierszy i 5000 wierszy. Bez indeksów wskaźnik wydajności (liczba odczytów logicznych) dla wszystkich zapytań jest taki sam i wynosi 1621. Wprowadźmy dane do tabeli wyników:

Widzimy, że w przypadku drugiego i trzeciego zapytania, gdy zwrócono dość dużą liczbę wierszy, utworzony przez nas indeks nie poprawił wydajności. Jednak w przypadku zapytania zwracającego pojedynczy wiersz przyspieszenie było ogromne. Możemy zatem stwierdzić, że przy optymalizacji zapytań zwracających pojedynczy wynik sensowne jest tworzenie indeksów niezakrywających.

Stwórzmy teraz indeks pokrywający, osiągając w ten sposób maksymalną wydajność.

Najpierw usuńmy poprzedni indeks:

UŻYJ TestDB GO DROP INDEX Klienci.TestIndex1

I utwórzmy nowy indeks:

UTWÓRZ INDEKS NIEKLASTRANY TestIndex2 NA dbo.Customers(Id) INCLUDE (Num1, Num2);

Teraz uruchommy nasze zapytania po raz trzeci i zapiszmy wyniki w tabeli:

Brak indeksów
Indeks niekryjący
Indeks krycia

Łatwo zauważyć, że wzrost wydajności był ogromny. Tym samym dziesięciokrotnie zwiększyliśmy szybkość wykonywania zapytań. Podczas uruchamiania bazy danych przechowującej miliony wierszy ten wzrost wydajności będzie dość zauważalny.

W tym artykule przyjrzeliśmy się przykładowi optymalizacji bazy danych poprzez utworzenie indeksów. Warto zaznaczyć, że tworzenie indeksów jest procesem czysto indywidualnym dla każdego zapytania. Aby zbudować indeks, który naprawdę zoptymalizuje wydajność zapytania, należy dokładnie przeanalizować samo zapytanie i plan jego wykonania.

Efektywne budowanie indeksów to jeden z najlepszych sposobów poprawy wydajności aplikacji bazodanowej. Bez użycia indeksów SQL Server przypomina czytelnika próbującego znaleźć słowo w książce, przeglądając każdą stronę. Jeśli książka posiada indeks tematyczny (indeks), czytelnik może znacznie szybciej wyszukać potrzebne informacje.

W przypadku braku indeksu serwer SQL podczas pobierania danych z tabeli przeskanuje całą tabelę i sprawdzi każdy wiersz, aby sprawdzić, czy spełnione są kryteria zapytania. Takie pełne skanowanie może być katastrofalne dla wydajności całego systemu, szczególnie jeśli w tabelach znajduje się dużo danych.

Jednym z najważniejszych zadań podczas pracy z bazą danych jest zbudowanie optymalnego indeksu poprawiającego wydajność systemu. Większość głównych baz danych udostępnia narzędzia umożliwiające przeglądanie planu wykonania zapytania oraz pomoc w dostrajaniu i optymalizacji indeksów. W tym artykule omówiono kilka dobrych zasad, które mają zastosowanie podczas tworzenia lub modyfikowania indeksów w bazie danych. Najpierw przyjrzyjmy się sytuacjom, w których indeksowanie poprawia wydajność i gdzie indeksowanie może zaszkodzić.

Przydatne indeksy

Indeksowanie tabeli przyda się zatem podczas wyszukiwania określonego rekordu w tabeli za pomocą instrukcji Where. Do takich zapytań należą na przykład zapytania wyszukujące zakres wartości, zapytania dopasowujące dokładną wartość do określonej wartości oraz zapytania łączące dwie tabele.

Na przykład następujące zapytania względem bazy danych Northwind będą działać wydajniej podczas tworzenia indeksu na podstawie kolumny UnitPrice.

Usuń z produktów, gdzie cena jednostkowa=1
Wybierz * z produktów, gdzie cena jednostkowa mieści się w przedziale od 14 DO 16

Ponieważ elementy indeksu są przechowywane w sposób posortowany, indeksowanie jest również przydatne podczas tworzenia zapytania przy użyciu klauzuli Order by. Bez indeksu rekordy są ładowane i sortowane podczas wykonywania zapytania. Indeks oparty na UnitPrice pozwoli Ci po prostu przeskanować indeks i pobrać wiersze przez odniesienie podczas przetwarzania następnego żądania. Jeśli chcesz posortować wiersze w kolejności malejącej, możesz po prostu przeskanować indeks w odwrotnej kolejności.

Wybierz * Z zamówienia produktów według ceny jednostkowej ASC

Grupowanie rekordu za pomocą instrukcji Group by również często wymaga sortowania, więc zbudowanie indeksu na kolumnie UnitPrice przyda się również przy kolejnym zapytaniu, które zliczy liczbę jednostek produktu w każdej określonej cenie

Wybierz liczbę(*), cenę jednostkową z grupy produktów według ceny jednostkowej

Indeksy są przydatne do utrzymywania unikalnej wartości kolumny, ponieważ DBMS może łatwo sprawdzić indeks, aby sprawdzić, czy wartość już istnieje. Z tego powodu klucze podstawowe są zawsze indeksowane.

Wady indeksowania

Indeksy obniżają wydajność systemu podczas zmian rekordów. Za każdym razem, gdy wykonywane jest zapytanie mające na celu zmianę danych w tabeli, indeks również musi się zmienić. Aby wybrać optymalną liczbę indeksów, należy przetestować bazę danych i monitorować jej wydajność. Systemy statyczne, w których bazy danych są używane głównie do wyszukiwania danych, na przykład do raportowania, mogą zawierać więcej indeksów w celu obsługi zapytań tylko do odczytu. Bazy danych zawierające dużą liczbę transakcji zmieniających dane będą wymagały niewielkiej liczby indeksów, aby zapewnić większą przepustowość.

Indeksy zajmują dodatkowe miejsce na dysku i w pamięci RAM. Dokładny rozmiar będzie zależał od liczby rekordów w tabeli, a także od liczby i rozmiaru kolumn w indeksie. W większości przypadków nie jest to poważny problem, ponieważ miejsce na dysku można teraz łatwo poświęcić na rzecz lepszej wydajności.

Budowanie optymalnego indeksu

Prosty indeks

Indeks prosty to indeks wykorzystujący wartości pojedynczego pola w tabeli. Korzystanie z prostego indeksu jest korzystne z dwóch powodów. Po pierwsze, uruchomienie bazy danych powoduje duże obciążenie dysku twardego. Duże klucze indeksowe zmuszą bazę danych do wykonywania większej liczby operacji we/wy, co ogranicza wydajność.

Po drugie, ponieważ w porównaniach często biorą udział elementy indeksu, łatwiej jest porównać mniejsze indeksy. Z tych dwóch powodów pojedyncza kolumna zawierająca liczby całkowite jest lepszym indeksem, ponieważ jest mała i łatwa do porównania. Z drugiej strony ciągi znaków wymagają porównań znak po znaku i zwracania uwagi na obsługę parametrów.

Indeks selektywny

Najbardziej efektywne indeksy to te z niskim odsetkiem zduplikowanych wartości. Na przykład książka telefoniczna miasta, w którym prawie wszyscy noszą nazwisko Smith, nie będzie tak użyteczna, jeśli wpisy w niej zawarte będą posortowane według nazwiska.

Indeks z wysokim procentem unikalnych wartości nazywany jest również indeksem selektywnym. Oczywiście indeks unikalny ma największą selektywność, ponieważ nie zawiera zduplikowanych wartości. Wiele systemów DBMS może śledzić statystyki dotyczące każdego indeksu i rozpoznawać, ile nieduplikowanych wartości zawiera każdy indeks. Ta statystyka jest używana podczas generowania planu wykonania zapytania.

Indeksy kryjące

Indeksy składają się z kolumny danych, na której zbudowany jest sam indeks, oraz wskaźnika do odpowiedniego wiersza. Działa jak indeks książki: zawiera tylko słowa kluczowe i łącze do strony, do której można przejść, aby uzyskać więcej informacji. Zazwyczaj system DBMS będzie podążał za wskaźnikami do wiersza indeksu, aby zebrać wszystkie informacje potrzebne do zapytania. Jeśli jednak indeks zawiera wszystkie kolumny potrzebne w zapytaniu, informacje można pobrać bez konieczności uzyskiwania dostępu do samej tabeli.

Rozważmy indeks w kolumnie UnitPrice, o którym już wspomniano powyżej. System DBMS może używać elementów indeksu jedynie do wykonania następnego zapytania.

Wybierz liczbę(*), cenę jednostkową z grupy produktów według ceny jednostkowej

Ten typ zapytania nazywany jest zapytaniem pokrywającym, ponieważ wszystkie kolumny, których dotyczy zapytanie, można pobrać z jednego indeksu. W przypadku najważniejszych zapytań warto rozważyć utworzenie indeksu obejmującego w celu uzyskania najlepszej możliwej wydajności. Takie indeksy będą prawdopodobnie złożone (wykorzystujące więcej niż jedną kolumnę), co jest przeciwieństwem pierwszej zasady: twórz proste indeksy. Oczywiście wybór optymalnej liczby kolumn w indeksie można ocenić jedynie poprzez testowanie i monitorowanie wydajności bazy danych w różnych sytuacjach.

Indeks klastra

Wiele baz danych ma jeden specjalny indeks w tabeli, w którym zawarte są wszystkie dane z wiersza. W SQL Server taki indeks nazywany jest indeksem klastrowym. Indeks klastrowy można porównać do książki telefonicznej, ponieważ każdy element indeksu zawiera wszystkie potrzebne informacje i nie zawiera odnośników umożliwiających uzyskanie dodatkowych danych.

Obowiązuje ogólna zasada – każda nietrywialna tabela musi posiadać indeks klastrowy. Jeśli możliwe jest utworzenie tylko jednego indeksu w tabeli, należy go klastrować. W SQL Server, gdy tworzony jest klucz podstawowy, automatycznie tworzony jest indeks klastrowy (jeśli jeszcze go nie zawiera), używając kolumny klucza podstawowego jako klucza indeksującego. Indeks klastrowy jest najbardziej wydajnym indeksem (jeśli jest używany, obejmuje całe zapytanie) i w wielu SZBD taki indeks pomaga efektywnie zarządzać przestrzenią wymaganą do przechowywania tabel, gdyż w przeciwnym razie (bez budowania indeksu klastrowego) wiersze tabeli są przechowywane w nieuporządkowana struktura, zwana stertą.

Należy zachować ostrożność podczas wybierania kolumn dla indeksu klastrowego. Jeśli zmienisz rekord i wartość kolumny w indeksie klastrowym, baza danych będzie zmuszona odbudować elementy indeksu (aby zachować je w posortowanej kolejności). Należy pamiętać, że elementy indeksu indeksu klastrowego zawierają wszystkie wartości kolumn, więc zmianę wartości kolumny można porównać do wykonania instrukcji Delete, po której następuje instrukcja Insert, co oczywiście, jeśli będzie wykonywane często, spowoduje problemy z wydajnością. Z tego powodu indeksy klastrowe często składają się z klucza podstawowego i kolumny klucza obcego. Jeśli kluczowe wartości się zmieniają, zmieniają się bardzo rzadko.

Wniosek

Określenie właściwych indeksów do użycia w bazie danych wymaga dokładnej analizy i testowania systemu. Praktyki przedstawione w tym artykule są dobrymi zasadami konstruowania indeksów. Po zastosowaniu tych metod konieczne będzie ponowne przetestowanie konkretnej aplikacji pod kątem konkretnego sprzętu, pamięci i warunków operacyjnych.

Jeden z najważniejszych sposobów na osiągnięcie wysokiej produktywności Serwer SQL jest użycie indeksów. Indeks przyspiesza proces zapytań, zapewniając szybki dostęp do wierszy danych w tabeli, podobnie jak indeks w książce pomaga szybko znaleźć potrzebne informacje. W tym artykule przedstawię krótki przegląd indeksów w Serwer SQL i wyjaśnij, w jaki sposób są one zorganizowane w bazie danych i w jaki sposób pomagają przyspieszyć zapytania do bazy danych.

Indeksy tworzone są w kolumnach tabeli i widoku. Indeksy umożliwiają szybkie przeszukiwanie danych na podstawie wartości znajdujących się w tych kolumnach. Na przykład, jeśli utworzysz indeks na kluczu podstawowym, a następnie wyszukasz wiersz danych przy użyciu wartości klucza podstawowego, wówczas Serwer SQL najpierw znajdzie wartość indeksu, a następnie użyje indeksu, aby szybko znaleźć cały wiersz danych. Bez indeksu zostanie wykonane pełne skanowanie wszystkich wierszy tabeli, co może mieć znaczący wpływ na wydajność.
Indeks można utworzyć dla większości kolumn tabeli lub widoku. Wyjątkiem są głównie kolumny z typami danych do przechowywania dużych obiektów ( LOB), Jak na przykład obraz, tekst Lub varchar (maks.). Można także tworzyć indeksy na kolumnach przeznaczonych do przechowywania danych w formacie XML-a, ale indeksy te mają nieco inną strukturę niż standardowe i ich rozważenie wykracza poza zakres tego artykułu. Artykuł też nie omawia magazyn kolumn indeksy. Zamiast tego skupiam się na indeksach, które są najczęściej używane w bazach danych Serwer SQL.
Indeks składa się z zestawu stron, węzłów indeksu, które są zorganizowane w strukturę drzewiastą - zrównoważone drzewo. Struktura ta ma charakter hierarchiczny i zaczyna się od węzła głównego na górze hierarchii i węzłów-liście, czyli liści, na dole, jak pokazano na rysunku:


Kiedy wysyłasz zapytanie do indeksowanej kolumny, silnik zapytań uruchamia się na górze węzła głównego i kieruje się w dół przez węzły pośrednie, przy czym każda warstwa pośrednia zawiera bardziej szczegółowe informacje o danych. Silnik zapytań kontynuuje poruszanie się po węzłach indeksu, aż do osiągnięcia najniższego poziomu z liśćmi indeksu. Na przykład, jeśli szukasz wartości 123 w kolumnie indeksowanej, silnik zapytań najpierw określi stronę na pierwszym poziomie pośrednim na poziomie głównym. W tym przypadku pierwsza strona wskazuje wartość od 1 do 100, a druga od 101 do 200, zatem silnik zapytań będzie miał dostęp do drugiej strony tego poziomu pośredniego. Następnie zobaczysz, że powinieneś przejść na trzecią stronę następnego poziomu średniozaawansowanego. Stąd podsystem zapytań odczyta wartość samego indeksu na niższym poziomie. Liście indeksu mogą zawierać same dane tabeli lub po prostu wskaźnik do wierszy zawierających dane w tabeli, w zależności od typu indeksu: indeks klastrowany lub indeks nieklastrowany.

Indeks klastrowany

Indeks klastrowy przechowuje rzeczywiste wiersze danych w liściach indeksu. Wracając do poprzedniego przykładu, oznacza to, że wiersz danych powiązany z wartością klucza 123 będzie przechowywany w samym indeksie. Ważną cechą indeksu klastrowego jest to, że wszystkie wartości są sortowane w określonej kolejności, rosnąco lub malejąco. Dlatego tabela lub widok może mieć tylko jeden indeks klastrowy. Dodatkowo należy zaznaczyć, że dane w tabeli przechowywane są w formie posortowanej tylko wtedy, gdy dla tej tabeli utworzono indeks klastrowy.
Tabela, która nie ma indeksu klastrowego, nazywana jest stertą.

Indeks nieklastrowany

W przeciwieństwie do indeksu klastrowego liście indeksu nieklastrowanego zawierają tylko te kolumny ( klucz), według którego wyznaczany jest ten indeks, a także zawiera wskaźnik do wierszy z rzeczywistymi danymi w tabeli. Oznacza to, że system podzapytań wymaga dodatkowej operacji w celu zlokalizowania i pobrania wymaganych danych. Zawartość wskaźnika danych zależy od sposobu przechowywania danych: tabela klastrowana lub sterta. Jeśli wskaźnik wskazuje tabelę klastrową, wskazuje na indeks klastrowy, którego można użyć do znalezienia rzeczywistych danych. Jeśli wskaźnik odwołuje się do sterty, to wskazuje na konkretny identyfikator wiersza danych. Indeksów nieklastrowanych nie można sortować jak indeksów klastrowych, ale można utworzyć więcej niż jeden indeks nieklastrowany w tabeli lub widoku, aż do 999. Nie oznacza to, że należy utworzyć jak najwięcej indeksów. Indeksy mogą poprawić lub obniżyć wydajność systemu. Oprócz możliwości tworzenia wielu indeksów nieklastrowanych, możesz także dołączyć dodatkowe kolumny ( dołączona kolumna) do swojego indeksu: liście indeksu będą przechowywać nie tylko wartość samych indeksowanych kolumn, ale także wartości tych nieindeksowanych dodatkowych kolumn. Takie podejście pozwoli ominąć część ograniczeń nałożonych na indeks. Można na przykład dołączyć kolumnę nieindeksowaną lub ominąć limit długości indeksu (w większości przypadków 900 bajtów).

Rodzaje indeksów

Oprócz tego, że jest indeksem klastrowym lub nieklastrowym, można go dodatkowo skonfigurować jako indeks złożony, indeks unikalny lub indeks pokrywający.

Indeks złożony

Taki indeks może zawierać więcej niż jedną kolumnę. Indeks może zawierać maksymalnie 16 kolumn, ale ich łączna długość jest ograniczona do 900 bajtów. Zarówno indeksy klastrowe, jak i nieklastrowane mogą być złożone.

Unikalny indeks

Indeks ten gwarantuje, że każda wartość w indeksowanej kolumnie jest unikatowa. Jeśli indeks jest złożony, niepowtarzalność dotyczy wszystkich kolumn w indeksie, ale nie każdej pojedynczej kolumny. Na przykład, jeśli utworzysz unikalny indeks dla kolumn NAZWA I NAZWISKO, wówczas pełne imię i nazwisko musi być unikalne, ale możliwe są duplikaty w imieniu lub nazwisku.
Unikalny indeks jest tworzony automatycznie po zdefiniowaniu ograniczenia kolumny: ograniczenie klucza podstawowego lub unikalnej wartości:

• Główny klucz
  Kiedy zdefiniujesz ograniczenie klucza podstawowego w jednej lub większej liczbie kolumn, wówczas Serwer SQL automatycznie tworzy unikalny indeks klastrowy, jeśli indeks klastrowy nie został wcześniej utworzony (w tym przypadku na kluczu podstawowym tworzony jest unikalny indeks nieklastrowy)
• Wyjątkowość wartości
  Kiedy zdefiniujesz ograniczenie niepowtarzalności wartości, wtedy Serwer SQL automatycznie tworzy unikalny indeks nieklastrowany. Można określić, że ma zostać utworzony unikalny indeks klastrowy, jeśli w tabeli nie utworzono jeszcze indeksu klastrowego

Indeks krycia

Indeks taki umożliwia konkretnemu zapytaniu natychmiastowe uzyskanie wszystkich niezbędnych danych z liści indeksu bez dodatkowego dostępu do rekordów samej tabeli.

Projektowanie indeksów

Choć indeksy mogą być przydatne, należy je projektować ostrożnie. Ponieważ indeksy mogą zajmować dużo miejsca na dysku, nie należy tworzyć większej liczby indeksów, niż jest to konieczne. Ponadto indeksy są automatycznie aktualizowane podczas aktualizacji samego wiersza danych, co może prowadzić do dodatkowego obciążenia zasobów i pogorszenia wydajności. Projektując indeksy, należy wziąć pod uwagę kilka czynników dotyczących bazy danych i zapytań do niej.

Baza danych

Jak wspomniano wcześniej, indeksy mogą poprawić wydajność systemu, ponieważ zapewniają silnikowi zapytań szybki sposób wyszukiwania danych. Należy jednak wziąć pod uwagę również to, jak często zamierzasz wstawiać, aktualizować lub usuwać dane. Kiedy zmieniasz dane, należy również zmienić indeksy, aby odzwierciedlić odpowiednie działania na danych, co może znacznie zmniejszyć wydajność systemu. Planując strategię indeksowania, weź pod uwagę następujące wskazówki:

• W przypadku tabel, które są często aktualizowane, należy używać jak najmniejszej liczby indeksów.
• Jeśli tabela zawiera dużą ilość danych, ale zmiany są niewielkie, użyj tylu indeksów, ile potrzeba, aby poprawić wydajność zapytań. Jednak zastanów się dobrze przed użyciem indeksów na małych tabelach, ponieważ... Możliwe, że wyszukiwanie indeksowe może zająć więcej czasu niż zwykłe skanowanie wszystkich wierszy.
• W przypadku indeksów klastrowych staraj się, aby pola były jak najkrótsze. Najlepszym podejściem jest użycie indeksu klastrowego dla kolumn, które mają unikalne wartości i nie pozwalają na NULL. Z tego powodu klucz podstawowy jest często używany jako indeks klastrowy.
• Unikalność wartości w kolumnie wpływa na wydajność indeksu. Ogólnie rzecz biorąc, im więcej duplikatów znajduje się w kolumnie, tym gorsza jest wydajność indeksu. Z drugiej strony, im więcej unikalnych wartości, tym lepsza wydajność indeksu. Jeśli to możliwe, używaj unikalnego indeksu.
• W przypadku indeksu złożonego należy wziąć pod uwagę kolejność kolumn w indeksie. Kolumny używane w wyrażeniach GDZIE(Na przykład, GDZIE Imię = „Charlie”) musi znajdować się na pierwszym miejscu w indeksie. Kolejne kolumny należy wyszczególniać w oparciu o niepowtarzalność ich wartości (na pierwszym miejscu są kolumny z największą liczbą unikalnych wartości).
• Można także określić indeks dla kolumn obliczeniowych, jeśli spełniają one określone wymagania. Przykładowo wyrażenia służące do uzyskania wartości kolumny muszą być deterministyczne (zawsze zwracają ten sam wynik dla danego zestawu parametrów wejściowych).

Zapytania do bazy danych

Inną kwestią przy projektowaniu indeksów jest to, jakie zapytania są uruchamiane w bazie danych. Jak wspomniano wcześniej, należy wziąć pod uwagę częstotliwość zmian danych. Dodatkowo należy stosować następujące zasady:

• Spróbuj wstawić lub zmodyfikować jak najwięcej wierszy w jednym zapytaniu, zamiast robić to w kilku pojedynczych zapytaniach.
• Utwórz indeks nieklastrowy dla kolumn, które są często używane jako wyszukiwane hasła w Twoich zapytaniach. GDZIE i połączenia w DOŁĄCZYĆ.
• Rozważ indeksowanie kolumn używanych w zapytaniach wyszukiwania wierszy w celu uzyskania dokładnych dopasowań wartości.

A teraz właściwie:

14 pytań dotyczących indeksów w SQL Server, których wstydziłeś się zadać

Dlaczego tabela nie może mieć dwóch indeksów klastrowych?

Chcesz krótką odpowiedź? Indeks klastrowy to tabela. Kiedy tworzysz indeks klastrowy w tabeli, mechanizm przechowywania sortuje wszystkie wiersze w tabeli w kolejności rosnącej lub malejącej, zgodnie z definicją indeksu. Indeks klastrowy nie jest odrębną jednostką jak inne indeksy, ale mechanizmem sortowania danych w tabeli i ułatwiającym szybki dostęp do wierszy danych.
Wyobraźmy sobie, że masz tabelę zawierającą historię transakcji sprzedaży. Tabela Sprzedaż zawiera takie informacje jak identyfikator zamówienia, pozycja produktu w zamówieniu, numer produktu, ilość produktu, numer i data zamówienia itp. Tworzysz indeks klastrowy na kolumnach ID zamówienia I Identyfikator linii, posortowane w kolejności rosnącej, jak pokazano poniżej T-SQL kod:
UTWÓRZ UNIKALNY INDEKS KLASTROWANY ix_oriderid_lineid NA dbo.Sales(OrderID, LineID);
Po uruchomieniu tego skryptu wszystkie wiersze w tabeli zostaną fizycznie posortowane najpierw według kolumny OrderID, a następnie według LineID, ale same dane pozostaną w jednym logicznym bloku, czyli w tabeli. Z tego powodu nie można utworzyć dwóch indeksów klastrowych. Może istnieć tylko jedna tabela z jednymi danymi i tabela ta może być posortowana tylko raz w określonej kolejności.

Jeśli tabela klastrowa zapewnia wiele korzyści, to po co używać sterty?

Masz rację. Tabele klastrowane są świetne i większość zapytań będzie działać lepiej w przypadku tabel mających indeks klastrowy. Ale w niektórych przypadkach możesz chcieć pozostawić stoły w ich naturalnym, nieskazitelnym stanie, tj. w formie sterty i twórz tylko indeksy nieklastrowe, aby zapewnić działanie zapytań.
Jak pamiętasz, sterta przechowuje dane w losowej kolejności. Zazwyczaj podsystem pamięci masowej dodaje dane do tabeli w kolejności, w jakiej są wstawiane, ale podsystem pamięci masowej lubi również przenosić wiersze, aby zapewnić bardziej wydajne przechowywanie. Dzięki temu nie masz możliwości przewidzenia, w jakiej kolejności dane będą przechowywane.
Jeśli silnik zapytań będzie musiał znaleźć dane bez korzystania z indeksu nieklastrowego, wykona pełne skanowanie tabeli w celu znalezienia potrzebnych wierszy. Na bardzo małych stołach zwykle nie stanowi to problemu, ale wraz ze wzrostem wielkości sterty wydajność szybko spada. Oczywiście indeks nieklastrowany może być pomocny poprzez użycie wskaźnika do pliku, strony i wiersza, w którym przechowywane są wymagane dane - jest to zwykle znacznie lepsza alternatywa dla skanowania tabeli. Mimo to trudno jest porównać zalety indeksu klastrowego, biorąc pod uwagę wydajność zapytań.
Jednak w pewnych sytuacjach sterta może pomóc w poprawie wydajności. Rozważmy tabelę zawierającą wiele wstawień, ale niewiele aktualizacji i usunięć. Na przykład tabela przechowująca dziennik służy przede wszystkim do wstawiania wartości do czasu jego archiwizacji. Na stercie nie będzie widać stronicowania i fragmentacji danych, tak jak w przypadku indeksu klastrowego, ponieważ wiersze są po prostu dodawane na końcu sterty. Zbyt duże dzielenie stron może mieć znaczący wpływ na wydajność i to nie w dobrym tego słowa znaczeniu. Ogólnie rzecz biorąc, sterta umożliwia stosunkowo bezproblemowe wstawianie danych i nie trzeba zajmować się kosztami ogólnymi związanymi z przechowywaniem i konserwacją, jak w przypadku indeksu klastrowego.
Jednak braku aktualizacji i usuwania danych nie należy uważać za jedyny powód. Istotny jest także sposób pobierania próbek danych. Na przykład nie powinieneś używać sterty, jeśli często wykonujesz zapytania dotyczące zakresów danych lub dane, których szukasz, często wymagają sortowania lub grupowania.
Oznacza to tylko, że użycie sterty powinieneś rozważyć tylko wtedy, gdy pracujesz z bardzo małymi tabelami lub cała interakcja z tabelą ogranicza się do wstawiania danych, a zapytania są niezwykle proste (i używasz indeksów nieklastrowanych W każdym razie). W przeciwnym razie trzymaj się dobrze zaprojektowanego indeksu klastrowego, na przykład zdefiniowanego w prostym rosnącym polu klucza, na przykład powszechnie używanej kolumny z TOŻSAMOŚĆ.

Jak zmienić domyślny współczynnik wypełnienia indeksu?

Zmiana domyślnego współczynnika wypełnienia indeksu to jedno. Zrozumienie, jak działa współczynnik domyślny, to inna sprawa. Ale najpierw cofnij się kilka kroków. Współczynnik wypełnienia indeksu określa ilość miejsca na stronie do przechowywania indeksu na dolnym poziomie (poziom kartki) przed rozpoczęciem wypełniania nowej strony. Przykładowo, jeśli współczynnik zostanie ustawiony na 90, to gdy indeks wzrośnie, zajmie 90% strony, a następnie przejdzie do następnej strony.
Domyślnie włączona jest wartość współczynnika wypełnienia indeksu Serwer SQL wynosi 0, co jest równe 100. W rezultacie wszystkie nowe indeksy automatycznie dziedziczą to ustawienie, chyba że określisz w kodzie wartość inną niż standardowa wartość systemowa lub zmienisz zachowanie domyślne. Możesz użyć Studio zarządzania serwerem SQL aby dostosować wartość domyślną lub uruchomić systemową procedurę zapisaną w bazie sp_configure. Na przykład następujący zestaw T-SQL polecenia ustawia wartość współczynnika na 90 (należy najpierw przejść do trybu ustawień zaawansowanych):
EXEC sp_configure "pokaż opcje zaawansowane", 1; PRZEJDŹ DO PONOWNEJ KONFIGURACJI; GO EXEC sp_configure „współczynnik wypełnienia”, 90; PRZEJDŹ DO PONOWNEJ KONFIGURACJI; IŚĆ
Po zmianie wartości współczynnika wypełnienia indeksu należy ponownie uruchomić usługę Serwer SQL. Możesz teraz sprawdzić ustawioną wartość, uruchamiając sp_configure bez określonego drugiego argumentu:
EXEC sp_configure „współczynnik wypełnienia” GO
To polecenie powinno zwrócić wartość 90. W rezultacie wszystkie nowo utworzone indeksy będą używać tej wartości. Możesz to przetestować, tworząc indeks i wysyłając zapytanie o wartość współczynnika wypełnienia:
UŻYJ AdventureWorks2012; -- twoja baza danych UTWÓRZ INDEKS NIEKLASTRAWONY ix_people_lastname ON Person.Person(LastName); PRZEJDŹ WYBIERZ współczynnik wypełnienia Z sys.indexes GDZIE id_obiektu = id_obiektu("Osoba.Osoba") ORAZ nazwa="ix_people_nazwisko";
W tym przykładzie utworzyliśmy indeks nieklastrowy w tabeli Osoba w bazie danych AdventureWorks2012. Po utworzeniu indeksu wartość współczynnika wypełnienia możemy uzyskać z tabel systemowych sys.indexes. Zapytanie powinno zwrócić 90.
Wyobraźmy sobie jednak, że usunęliśmy indeks i utworzyliśmy go na nowo, ale teraz określiliśmy konkretną wartość współczynnika wypełnienia:
UTWÓRZ INDEKS NIEKLASTRAWONY ix_people_lastname NA Person.Person(LastName) Z (fillfactor=80); PRZEJDŹ WYBIERZ współczynnik wypełnienia Z sys.indexes GDZIE id_obiektu = id_obiektu("Osoba.Osoba") ORAZ nazwa="ix_people_nazwisko";
Tym razem dodaliśmy instrukcje Z i opcja współczynnik wypełnienia dla naszej operacji tworzenia indeksu UTWÓRZ INDEKS i podał wartość 80. Operator WYBIERAĆ teraz zwraca odpowiednią wartość.
Do tej pory wszystko było dość proste. W całym tym procesie naprawdę można się poparzyć, tworząc indeks używający domyślnej wartości współczynnika, zakładając, że znasz tę wartość. Przykładowo ktoś majstruje przy ustawieniach serwera i jest na tyle uparty, że ustawił współczynnik wypełnienia indeksu na 20. W międzyczasie Ty kontynuujesz tworzenie indeksów zakładając, że domyślna wartość wynosi 0. Niestety nie masz jak sprawdzić wypełnienia współczynnik, dopóki nie utworzysz indeksu, a następnie sprawdź wartość, tak jak to zrobiliśmy w naszych przykładach. W przeciwnym razie będziesz musiał poczekać na moment, w którym wydajność zapytań spadnie tak bardzo, że zaczniesz coś podejrzewać.
Kolejną kwestią, o której powinieneś wiedzieć, jest odbudowa indeksów. Podobnie jak w przypadku tworzenia indeksu, podczas jego odbudowy można określić wartość współczynnika wypełnienia indeksu. Jednakże, w przeciwieństwie do polecenia tworzenia indeksu, polecenie przebudowa nie korzysta z domyślnych ustawień serwera, pomimo tego, jak może się to wydawać. Co więcej, jeśli nie określisz konkretnie wartości współczynnika wypełnienia indeksu Serwer SQL zastosuje wartość współczynnika, przy jakim wskaźnik ten istniał przed jego restrukturyzacją. Na przykład następująca operacja ZMIEŃ INDEKS odbudowuje właśnie utworzony indeks:
ZMIEŃ INDEKS ix_people_nazwisko ON Osoba.Osoba ODBUDOWANA; PRZEJDŹ WYBIERZ współczynnik wypełnienia Z sys.indexes GDZIE id_obiektu = id_obiektu("Osoba.Osoba") ORAZ nazwa="ix_people_nazwisko";
Sprawdzając wartość współczynnika wypełnienia, otrzymamy wartość 80, ponieważ tak określiliśmy podczas ostatniego tworzenia indeksu. Wartość domyślna jest ignorowana.
Jak widać, zmiana wartości współczynnika wypełnienia indeksu nie jest taka trudna. Znacznie trudniej jest poznać aktualną wartość i zrozumieć, kiedy jest ona stosowana. Jeśli zawsze dokładnie określisz współczynnik podczas tworzenia i odbudowy indeksów, zawsze znasz konkretny wynik. Chyba że musisz się martwić, że ktoś inny ponownie nie zepsuje ustawień serwera, powodując odbudowanie wszystkich indeksów z absurdalnie niskim współczynnikiem wypełnienia indeksu.

Czy można utworzyć indeks klastrowy w kolumnie zawierającej duplikaty?

Tak i nie. Tak, możesz utworzyć indeks klastrowy w kolumnie klucza zawierającej zduplikowane wartości. Nie, wartość kolumny klucza nie może pozostać w stanie nieunikalnym. Pozwól mi wyjaśnić. Jeśli utworzysz nieunikalny indeks klastrowy w kolumnie, silnik pamięci masowej doda unikatowy identyfikator do zduplikowanej wartości, aby zapewnić niepowtarzalność i w ten sposób umożliwić identyfikację każdego wiersza w tabeli klastrowanej.
Można na przykład zdecydować o utworzeniu indeksu klastrowego w kolumnie zawierającej dane klientów Nazwisko zachowanie nazwiska. Kolumna zawiera wartości Franklin, Hancock, Washington i Smith. Następnie ponownie wstawiasz wartości Adams, Hancock, Smith i Smith. Jednak wartość kolumny klucza musi być unikalna, aby silnik magazynu zmienił wartość duplikatów, tak aby wyglądały mniej więcej tak: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 i Smith5678.
Na pierwszy rzut oka takie podejście wydaje się w porządku, jednak wartość całkowita zwiększa rozmiar klucza, co może stać się problemem w przypadku dużej liczby duplikatów, a wartości te staną się podstawą indeksu nieklastrowanego lub obcego kluczowe odniesienie. Z tych powodów zawsze, gdy jest to możliwe, należy próbować tworzyć unikalne indeksy klastrowe. Jeśli nie jest to możliwe, spróbuj przynajmniej użyć kolumn o bardzo dużej wartości unikalnej.

W jaki sposób przechowywana jest tabela, jeśli nie utworzono indeksu klastrowego?

Serwer SQL obsługuje dwa typy tabel: tabele klastrowe posiadające indeks klastrowy oraz tabele stertowe lub po prostu sterty. W przeciwieństwie do tabel klastrowych, dane na stercie nie są w żaden sposób sortowane. W istocie jest to stos (sterta) danych. Jeśli dodasz wiersz do takiej tabeli, silnik pamięci po prostu dołączy go na końcu strony. Gdy strona zapełni się danymi, zostaną one dodane do nowej strony. W większości przypadków będziesz chciał utworzyć indeks klastrowy w tabeli, aby skorzystać z możliwości sortowania i szybkości wykonywania zapytań (spróbuj wyobrazić sobie wyszukiwanie numeru telefonu w nieposortowanej książce adresowej). Jeśli jednak zdecydujesz się nie tworzyć indeksu klastrowego, nadal możesz utworzyć indeks nieklastrowany na stercie. W tym przypadku każdy wiersz indeksu będzie miał wskaźnik do wiersza sterty. Indeks zawiera identyfikator pliku, numer strony i numer linii danych.

Jaki jest związek między ograniczeniami unikalności wartości a kluczem podstawowym z indeksami tabel?

Klucz podstawowy i ograniczenie unikalności zapewniają, że wartości w kolumnie są unikalne. Można utworzyć tylko jeden klucz podstawowy dla tabeli i nie może on zawierać wartości ZERO. Można utworzyć kilka ograniczeń unikalności wartości dla tabeli, a każde z nich może zawierać pojedynczy rekord ZERO.
Podczas tworzenia klucza podstawowego silnik pamięci masowej tworzy również unikalny indeks klastrowy, jeśli indeks klastrowy nie został jeszcze utworzony. Można jednak zastąpić zachowanie domyślne i zostanie utworzony indeks nieklastrowany. Jeśli w momencie tworzenia klucza podstawowego istnieje indeks klastrowy, zostanie utworzony unikalny indeks nieklastrowany.
Po utworzeniu unikalnego ograniczenia silnik magazynu tworzy unikalny, nieklastrowany indeks. Można jednak określić utworzenie unikalnego indeksu klastrowego, jeśli nie został on wcześniej utworzony.
Ogólnie rzecz biorąc, ograniczenie wartości unikalnej i indeks unikalny to to samo.

Dlaczego indeksy klastrowe i nieklastrowane nazywane są drzewem B w SQL Server?

Podstawowe indeksy w SQL Server, klastrowane lub nieklastrowane, są rozmieszczone w zestawach stron zwanych węzłami indeksu. Strony te są zorganizowane w określoną hierarchię o strukturze drzewiastej zwanej drzewem zrównoważonym. Na najwyższym poziomie znajduje się węzeł główny, na dole węzły liściowe, z węzłami pośrednimi pomiędzy górnym i dolnym poziomem, jak pokazano na rysunku:


Węzeł główny stanowi główny punkt wejścia dla zapytań próbujących pobrać dane za pośrednictwem indeksu. Zaczynając od tego węzła, silnik zapytań inicjuje nawigację w dół struktury hierarchicznej do odpowiedniego węzła liścia zawierającego dane.
Załóżmy na przykład, że otrzymano żądanie wybrania wierszy zawierających wartość klucza 82. Podsystem zapytań rozpoczyna pracę od węzła głównego, który odnosi się do odpowiedniego węzła pośredniego, w naszym przypadku 1-100. Z węzła pośredniego 1-100 następuje przejście do węzła 51-100, a stamtąd do węzła końcowego 76-100. Jeśli jest to indeks klastrowany, to liść węzła zawiera dane wiersza powiązanego z kluczem równym 82. Jeśli jest to indeks nieklastrowany, to liść indeksu zawiera wskaźnik do tabeli klastrowanej lub konkretnego wiersza w sterta.

W jaki sposób indeks może nawet poprawić wydajność zapytań, jeśli trzeba przejść przez wszystkie te węzły indeksu?

Po pierwsze, indeksy nie zawsze poprawiają wydajność. Zbyt wiele niepoprawnie utworzonych indeksów zamienia system w bagno i pogarsza wydajność zapytań. Bardziej trafne jest stwierdzenie, że jeśli indeksy zostaną zastosowane ostrożnie, mogą zapewnić znaczny wzrost wydajności.
Pomyśl o ogromnej książce poświęconej tuningowi wydajności Serwer SQL(wersja papierowa, nie elektroniczna). Wyobraź sobie, że chcesz znaleźć informacje na temat konfigurowania Resource Governor. Możesz przeciągać palcem strona po stronie przez całą książkę lub otworzyć spis treści i znaleźć dokładny numer strony z szukanymi informacjami (pod warunkiem, że książka jest poprawnie zaindeksowana i treść ma prawidłowe indeksy). Z pewnością zaoszczędzi to sporo czasu, mimo że musisz najpierw uzyskać dostęp do zupełnie innej struktury (indeksu), aby uzyskać potrzebne informacje ze struktury podstawowej (książki).
Podobnie jak indeks książek, indeks w Serwer SQL pozwala na wykonywanie precyzyjnych zapytań o potrzebne dane, zamiast całkowitego skanowania wszystkich danych zawartych w tabeli. W przypadku małych tabel pełne skanowanie zwykle nie stanowi problemu, ale duże tabele zajmują wiele stron danych, co może skutkować znacznym czasem wykonania zapytania, chyba że istnieje indeks umożliwiający silnikowi zapytań natychmiastowe uzyskanie prawidłowej lokalizacji danych. Wyobraź sobie, że gubisz się na wielopoziomowym skrzyżowaniu dróg przed dużą metropolią bez mapy, a zrozumiesz, o co chodzi.

Jeśli indeksy są tak świetne, dlaczego po prostu nie utworzyć jednego w każdej kolumnie?

Żaden dobry uczynek nie powinien pozostać bez kary. Przynajmniej tak jest w przypadku indeksów. Oczywiście indeksy działają świetnie, jeśli uruchamiasz zapytania pobierające przez operatora WYBIERAĆ, ale gdy tylko zaczną się częste połączenia z operatorami WSTAWIĆ, AKTUALIZACJA I USUWAĆ, więc krajobraz zmienia się bardzo szybko.
Kiedy inicjujesz żądanie danych przez operatora WYBIERAĆ, silnik zapytań znajduje indeks, porusza się po jego strukturze drzewiastej i odkrywa szukane dane. Co może być prostszego? Ale sytuacja się zmienia, jeśli zainicjujesz instrukcję zmiany, taką jak AKTUALIZACJA. Tak, w przypadku pierwszej części instrukcji silnik zapytań może ponownie użyć indeksu do zlokalizowania modyfikowanego wiersza - to dobra wiadomość. A jeśli nastąpi prosta zmiana danych w wierszu, która nie wpłynie na zmiany w kluczowych kolumnach, to proces zmiany będzie całkowicie bezbolesny. Co jednak, jeśli zmiana spowoduje podział stron zawierających dane lub zmieni się wartość kolumny klucza, powodując jej przeniesienie do innego węzła indeksu - spowoduje to, że indeks prawdopodobnie będzie wymagał reorganizacji mającej wpływ na wszystkie powiązane indeksy i operacje , co skutkuje powszechnym spadkiem produktywności.
Podobne procesy zachodzą podczas wywoływania operatora USUWAĆ. Indeks może pomóc zlokalizować usuwane dane, ale samo usunięcie danych może spowodować przetasowanie stron. Odnośnie operatora WSTAWIĆ, główny wróg wszystkich indeksów: zaczynasz dodawać dużą ilość danych, co prowadzi do zmian w indeksach i ich reorganizacji, na czym wszyscy cierpią.
Dlatego też, zastanawiając się, jakiego typu indeksy i ile utworzyć, należy wziąć pod uwagę typy zapytań do bazy danych. Więcej nie znaczy lepiej. Przed dodaniem nowego indeksu do tabeli należy wziąć pod uwagę nie tylko koszt zapytań bazowych, ale także ilość zajętego miejsca na dysku, koszt utrzymania funkcjonalności i indeksów, co może wywołać efekt domina na innych operacjach. Strategia projektowania indeksu jest jednym z najważniejszych aspektów implementacji i powinna uwzględniać wiele czynników, od rozmiaru indeksu, liczby unikalnych wartości, po typ zapytań, które indeks będzie obsługiwał.

Czy konieczne jest utworzenie indeksu klastrowego w kolumnie z kluczem podstawowym?

Indeks klastrowy można utworzyć dla dowolnej kolumny spełniającej wymagane warunki. Prawdą jest, że indeks klastrowy i ograniczenie klucza podstawowego są dla siebie stworzone i pasują do siebie w niebie, więc zrozum fakt, że kiedy utworzysz klucz podstawowy, indeks klastrowy zostanie utworzony automatycznie, jeśli nie został jeszcze stworzony wcześniej. Możesz jednak zdecydować, że indeks klastrowy będzie działał lepiej gdzie indziej i często Twoja decyzja będzie uzasadniona.
Głównym celem indeksu klastrowego jest sortowanie wszystkich wierszy w tabeli na podstawie kolumny klucza określonej podczas definiowania indeksu. Zapewnia to szybkie wyszukiwanie i łatwy dostęp do danych w tabeli.
Klucz podstawowy tabeli może być dobrym wyborem, ponieważ jednoznacznie identyfikuje każdy wiersz w tabeli bez konieczności dodawania dodatkowych danych. W niektórych przypadkach najlepszym wyborem będzie zastępczy klucz podstawowy, który jest nie tylko unikalny, ale także niewielkich rozmiarów i którego wartości rosną sekwencyjnie, dzięki czemu indeksy nieklastrowane oparte na tej wartości są bardziej wydajne. Optymalizator zapytań również lubi tę kombinację indeksu klastrowego i klucza podstawowego, ponieważ łączenie tabel jest szybsze niż łączenie w inny sposób, który nie wykorzystuje klucza podstawowego i powiązanego z nim indeksu klastrowego. Jak mówiłem, to mecz stworzony w niebie.
Na koniec jednak warto zaznaczyć, że tworząc indeks klastrowy należy wziąć pod uwagę kilka aspektów: ile indeksów nieklastrowych będzie na nim opartych, jak często będzie zmieniać się wartość kolumny indeksu klucza i jak duża. Gdy wartości w kolumnach indeksu klastrowego ulegną zmianie lub indeks nie działa zgodnie z oczekiwaniami, może to mieć wpływ na wszystkie inne indeksy w tabeli. Indeks klastrowy powinien opierać się na najbardziej trwałej kolumnie, której wartości rosną w określonej kolejności, ale nie zmieniają się w sposób losowy. Indeks musi obsługiwać zapytania dotyczące najczęściej używanych danych tabeli, tak aby zapytania w pełni wykorzystywały fakt, że dane są posortowane i dostępne w węzłach głównych, czyli na liściach indeksu. Jeśli klucz podstawowy pasuje do tego scenariusza, użyj go. Jeśli nie, wybierz inny zestaw kolumn.

Co się stanie, jeśli zindeksujesz widok, czy nadal będzie to widok?

Widok to wirtualna tabela, która generuje dane z jednej lub większej liczby tabel. Zasadniczo jest to nazwane zapytanie, które pobiera dane z tabel źródłowych podczas wykonywania zapytania w tym widoku. Można poprawić wydajność zapytań, tworząc w tym widoku indeks klastrowy i indeksy nieklastrowe, podobnie jak w przypadku tworzenia indeksów w tabeli, ale głównym zastrzeżeniem jest to, że najpierw tworzy się indeks klastrowy, a następnie można utworzyć indeks nieklastrowany.
Kiedy tworzony jest widok indeksowany (widok zmaterializowany), wówczas sama definicja widoku pozostaje odrębną jednostką. W końcu jest to po prostu zakodowany na stałe operator WYBIERAĆ, przechowywane w bazie danych. Ale indeks to zupełnie inna historia. Kiedy tworzysz indeks klastrowany lub nieklastrowany u dostawcy, dane są fizycznie zapisywane na dysku, podobnie jak zwykły indeks. Ponadto, gdy dane w tabelach źródłowych ulegają zmianie, indeks widoku zmienia się automatycznie (oznacza to, że warto unikać indeksowania widoków w tabelach, które często się zmieniają). W każdym razie widok pozostaje widokiem - widokiem tabel, ale wykonywanym w danej chwili, z odpowiadającymi mu indeksami.
Zanim będzie można utworzyć indeks w widoku, musi on spełniać kilka ograniczeń. Na przykład widok może odwoływać się tylko do tabel podstawowych, ale nie do innych widoków, a tabele te muszą znajdować się w tej samej bazie danych. W rzeczywistości istnieje wiele innych ograniczeń, więc koniecznie sprawdź dokumentację Serwer SQL za wszystkie brudne szczegóły.

Po co używać indeksu obejmującego zamiast indeksu złożonego?

Najpierw upewnijmy się, że rozumiemy różnicę między nimi. Indeks złożony to po prostu zwykły indeks zawierający więcej niż jedną kolumnę. Można użyć wielu kolumn kluczy, aby zapewnić unikatowość każdego wiersza w tabeli, można mieć wiele kolumn, aby zapewnić unikatowość klucza podstawowego, lub można próbować zoptymalizować wykonywanie często wywoływanych zapytań w wielu kolumnach. Ogólnie jednak im więcej kluczowych kolumn zawiera indeks, tym mniej efektywny będzie on, co oznacza, że ​​indeksów złożonych należy używać rozsądnie.
Jak już wspomniano, zapytanie może przynieść ogromne korzyści, jeśli wszystkie wymagane dane zostaną natychmiast umieszczone na liściach indeksu, podobnie jak sam indeks. Nie stanowi to problemu w przypadku indeksu klastrowego, ponieważ wszystkie dane już tam są (dlatego tak ważne jest dokładne przemyślenie tworzenia indeksu klastrowego). Ale indeks nieklastrowany na liściach zawiera tylko kluczowe kolumny. Aby uzyskać dostęp do wszystkich pozostałych danych, optymalizator zapytań wymaga dodatkowych kroków, co może znacznie zwiększyć obciążenie związane z wykonywaniem zapytań.
Tutaj na ratunek przychodzi indeks pokrywający. Definiując indeks nieklastrowany, można określić dodatkowe kolumny do kolumn kluczowych. Załóżmy na przykład, że aplikacja często wysyła zapytania do danych kolumn ID zamówienia I Data zamówienia na stole Obroty:
SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
Można utworzyć złożony indeks nieklastrowany w obu kolumnach, ale kolumna OrderDate doda jedynie narzut związany z utrzymaniem indeksu, nie służąc jako szczególnie użyteczna kolumna kluczowa. Najlepszym rozwiązaniem byłoby utworzenie indeksu pokrywającego w kolumnie klucza ID zamówienia i dodatkowo dołączona kolumna Data zamówienia:
UTWÓRZ INDEKS NIEKLASTROWANY ix_orderid NA dbo.Sales(OrderID) INCLUDE (OrderDate);
Pozwala to uniknąć wad indeksowania zbędnych kolumn, zachowując jednocześnie zalety przechowywania danych w liściach podczas wykonywania zapytań. Dołączona kolumna nie jest częścią klucza, ale dane są przechowywane w węźle liścia, liściu indeksu. Może to poprawić wydajność zapytań bez dodatkowego obciążenia. Ponadto kolumny zawarte w indeksie obejmującym podlegają mniejszym ograniczeniom niż kluczowe kolumny indeksu.

Czy liczba duplikatów w kolumnie klucza ma znaczenie?

Tworząc indeks, musisz spróbować zmniejszyć liczbę duplikatów w kluczowych kolumnach. A dokładniej: staraj się utrzymywać częstotliwość powtórzeń na jak najniższym poziomie.
Jeśli pracujesz z indeksem złożonym, powielanie dotyczy wszystkich kolumn kluczowych jako całości. Pojedyncza kolumna może zawierać wiele zduplikowanych wartości, ale liczba powtórzeń między wszystkimi kolumnami indeksu powinna być minimalna. Na przykład tworzysz złożony indeks nieklastrowany na kolumnach Imię I Nazwisko, możesz mieć wiele wartości John Doe i wiele wartości Doe, ale chcesz mieć jak najmniej wartości John Doe, a najlepiej tylko jedną wartość John Doe.
Współczynnik niepowtarzalności wartości kolumny klucza nazywany jest selektywnością indeksu. Im więcej unikalnych wartości, tym wyższa selektywność: unikalny indeks ma największą możliwą selektywność. Silnik zapytań naprawdę lubi kolumny o wysokich wartościach selektywności, szczególnie jeśli te kolumny znajdują się w klauzulach WHERE najczęściej wykonywanych zapytań. Im bardziej selektywny jest indeks, tym szybciej silnik zapytań może zmniejszyć rozmiar wynikowego zbioru danych. Minusem jest oczywiście to, że kolumny zawierające stosunkowo niewiele unikalnych wartości rzadko będą dobrymi kandydatami do indeksowania.

Czy można utworzyć indeks nieklastrowany tylko na określonym podzbiorze danych kolumny klucza?

Domyślnie indeks nieklastrowany zawiera jeden wiersz na każdy wiersz w tabeli. Oczywiście to samo można powiedzieć o indeksie klastrowym, zakładając, że takim indeksem jest tabela. Ale jeśli chodzi o indeks nieklastrowany, relacja jeden do jednego jest ważną koncepcją, ponieważ zaczynając od wersji Serwer SQL 2008, masz możliwość utworzenia indeksu z możliwością filtrowania, który ogranicza liczbę zawartych w nim wierszy. Filtrowany indeks może poprawić wydajność zapytań, ponieważ... jest mniejszy i zawiera filtrowane, dokładniejsze statystyki niż wszystkie tabelaryczne - prowadzi to do tworzenia lepszych planów wykonania. Filtrowany indeks wymaga również mniejszej przestrzeni dyskowej i niższych kosztów utrzymania. Indeks jest aktualizowany tylko wtedy, gdy zmienią się dane pasujące do filtra.
Ponadto utworzenie indeksu z możliwością filtrowania jest łatwe. W operatorze UTWÓRZ INDEKS wystarczy, że się zgłosisz GDZIE stan filtra. Na przykład możesz odfiltrować z indeksu wszystkie wiersze zawierające NULL, jak pokazano w kodzie:
Utwórz indeks nieklastrowany ix_trackingnumber NA Sales.SalesOrderDetail(CarrierTrackingNumber) GDZIE CarrierTrackingNumber NIE JEST NULL;
Tak naprawdę możemy odfiltrować wszelkie dane, które nie są istotne w krytycznych zapytaniach. Ale bądź ostrożny, bo... Serwer SQL nakłada kilka ograniczeń na indeksy filtrowalne, takie jak brak możliwości utworzenia indeksu filtrowalnego w widoku, dlatego należy uważnie przeczytać dokumentację.
Może się również zdarzyć, że podobne wyniki można osiągnąć, tworząc widok indeksowany. Jednak filtrowany indeks ma kilka zalet, takich jak możliwość zmniejszenia kosztów utrzymania i poprawy jakości planów wykonania. Filtrowane indeksy można także odbudować online. Spróbuj tego z widokiem indeksowanym.

I znowu trochę od tłumacza

Celem pojawienia się tego tłumaczenia na łamach Habrahabr było opowiedzenie lub przypomnienie o blogu SimpleTalk z Czerwona Brama.
Publikuje wiele zabawnych i interesujących postów.
Nie jestem powiązany z żadnymi produktami firmy Czerwona Brama ani z ich sprzedażą.

Zgodnie z obietnicą książki dla tych, którzy chcą wiedzieć więcej
Polecam trzy bardzo dobre książki mojego autorstwa (linki prowadzą do rozpalać wersje w sklepie Amazonka):

W zasadzie można otwierać proste indeksy

dla początkujących

indeks

Dodaj tagi

Podstawy T-SQL Microsoft SQL Server 2012 (instrukcja dla programistów) Autor Itzik Ben-Gan Data publikacji: 15 lipca 2012 r Autor, mistrz swojego rzemiosła, przekazuje podstawową wiedzę na temat pracy z bazami danych. Jeżeli o wszystkim zapomniałeś lub nie wiedziałeś, zdecydowanie warto przeczytać.

Indeksy ROWID to obiekty bazy danych, które umożliwiają wyświetlenie wszystkich wartości w kolumnie tabeli, a także identyfikatorów ROWID wszystkich wierszy tabeli zawierających wartości kolumny.

ROWID to pseudokolumna będąca unikalnym identyfikatorem wiersza w tabeli i faktycznie opisująca dokładną fizyczną lokalizację tego konkretnego wiersza. Na podstawie tych informacji Wyrocznia może następnie znaleźć dane powiązane z wierszem tabeli. Za każdym razem, gdy wiersz jest przenoszony, eksportowany, importowany lub jakakolwiek inna operacja zmieniająca jego położenie, plik ROWID linii, ponieważ zajmuje inną pozycję fizyczną. Do przechowywania danych ROWID Wymagane 80 bitów (10 bajtów). Identyfikatory ROWID składa się z czterech elementów: numeru obiektu (32 bity), względnego numeru pliku (10 bitów), numeru bloku (22 bity) i numeru linii (16 bitów). Identyfikatory te są wyświetlane jako sekwencje 18 znaków wskazujące lokalizację danych w bazie danych, przy czym każdy znak jest reprezentowany w formacie base-64 składającym się ze znaków A-Z, a-z, 0-9, + i /. Pierwsze sześć znaków to numer obiektu danych, kolejne trzy to względny numer pliku, kolejnych sześć to numer bloku, a ostatnie trzy to numer linii.

Przykład:

WYBIERZ rodzinę, ROWID OD studenta;

FAM ROWID

——————————————

IWANOW AAAA3kAAGAAAAGsAAA

PETROV AAAA3kAAGAAAAGsAAB

W bazie danych Wyrocznia indeksy służą do różnych celów: zapewnienia unikalności wartości w bazie danych, poprawy wydajności wyszukiwania rekordów w tabeli itp. Wydajność poprawia się poprzez uwzględnienie w kryteriach wyszukiwania odniesienia do indeksowanej kolumny lub kolumn dla danych w tabeli. W Wyrocznia indeksy można tworzyć w dowolnej kolumnie tabeli z wyjątkiem kolumn LONG. Indeksy rozróżniają aplikacje niewrażliwe na prędkość i aplikacje o wysokiej wydajności, szczególnie podczas pracy z dużymi tabelami. Jednak przed podjęciem decyzji o utworzeniu indeksu należy rozważyć zalety i wady dotyczące wydajności systemu. Wydajność nie poprawi się, jeśli po prostu wprowadzisz indeks i zapomnisz o nim.

Chociaż największa poprawa wydajności wynika z utworzenia indeksu w kolumnie, w której wszystkie wartości są unikalne, podobne wyniki można uzyskać w przypadku kolumn zawierających wartości zduplikowane lub NULL. Aby utworzyć indeks, nie jest konieczne, aby wartości kolumn były unikalne. Oto kilka zaleceń, które pomogą Ci osiągnąć pożądany wzrost wydajności podczas korzystania ze standardowego indeksu. Przy tworzeniu indeksu przyjrzymy się także kwestiom związanym z równowagą pomiędzy wydajnością a zużyciem miejsca na dysku.

Używanie indeksów do wyszukiwania informacji w tabelach może zapewnić znaczną poprawę wydajności w porównaniu do przeglądania tabel, których kolumny nie są indeksowane. Jednak wybór odpowiedniego indeksu wcale nie jest łatwy. Oczywiście kolumna, której wszystkie wartości są unikalne, jest preferowana do indeksowania indeksem B-drzewa, ale kolumna, która nie spełnia tych wymagań, jest dobrym kandydatem, o ile około 10% jej wierszy zawiera identyczne wartości i nie więcej. Kolumny „przełącznik” lub „flaga”, na przykład te, które przechowują informacje o płci osoby, nie nadają się do indeksów drzewa B. Kolumny, które służą do przechowywania niewielkiej liczby „wiarygodnych wartości”, a także te, które przechowują pewne wartości również nie są odpowiednie, wówczas znaki, na przykład „niezawodność” lub „niewiarygodność”, „aktywność” lub „brak aktywności”, „tak” lub „nie” itp. itd. Wreszcie indeksy z kluczami odwrotnymi są używany z reguły tam, gdzie jest zainstalowany i gdzie działa Wyrocznia Parallel Server i musisz maksymalnie zwiększyć poziom równoległości w bazie danych.