Indeksy w tabelach Memory-Optimized
Dotyczy:SQL Server
Azure SQL Database
Azure SQL Managed Instance
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. Istnieje kilka sposobów, na które indeks w tabeli zoptymalizowanej pod kątem pamięci różni się od tradycyjnego indeksu w tabeli opartej na dysku:
- Wiersze danych nie są przechowywane na stronach, dlatego nie ma kolekcji stron ani zakresów, partycji ani jednostek alokacji, do których można się odwoływać, aby uzyskać wszystkie strony tabeli. Istnieje pojęcie stron indeksu dla jednego z dostępnych typów indeksów, ale są one przechowywane inaczej niż indeksy dla tabel opartych na dyskach. Nie powstają u nich tradycyjne fragmentacje na stronie, więc nie potrzebują fillfactor.
- Zmiany wprowadzone w indeksach w tabelach zoptymalizowanych pod kątem pamięci podczas manipulowania danymi nigdy nie są zapisywane na dysku. Tylko wiersze danych i zmiany w danych są zapisywane w dzienniku transakcji.
- Indeksy zoptymalizowane pod kątem pamięci są odtwarzane po powrocie bazy danych do trybu online.
Wszystkie indeksy w tabelach zoptymalizowanych pod kątem pamięci są tworzone na podstawie definicji indeksu podczas odzyskiwania bazy danych.
Indeks musi być jednym z następujących elementów:
- Indeks skrótu
- Indeks nieklastrowany zoptymalizowany pod kątem pamięci (co oznacza domyślną wewnętrzną strukturę drzewa B)
indeksy haszowe są szczegółowo omówione w indeksach haszowych dla tabel Memory-Optimized.
Indeksy nieklastrowane są szczegółowo omówione w indeksie nieklastrowanym dla tabel Memory-Optimized.
indeksy Columnstore omawiane są w innym artykule.
Składnia indeksów zoptymalizowanych pod kątem pamięci
Każda instrukcja CREATE TABLE dla tabeli zoptymalizowanej pod kątem pamięci musi zawierać indeks jawnie za pośrednictwem indeksu lub niejawnie za pośrednictwem klucza podstawowego lub ograniczenia UNIKATOWEGO.
Aby zadeklarować domyślną trwałość = SCHEMA_AND_DATA, tabela zoptymalizowana pod kątem pamięci musi mieć klucz podstawowy. Klauzula PRIMARY KEY NONCLUSTERED w następującej instrukcji CREATE TABLE spełnia dwa wymagania:
Udostępnia indeks spełniający minimalny wymóg posiadania jednego indeksu w instrukcji CREATE TABLE.
Zawiera klucz podstawowy wymagany dla klauzuli SCHEMA_AND_DATA.
CREATE TABLE SupportEvent ( SupportEventId int NOT NULL PRIMARY KEY NONCLUSTERED, ... ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
Notatka
Programy SQL Server 2014 (12.x) i SQL Server 2016 (13.x) mają limit 8 indeksów na typ tabeli lub tabeli zoptymalizowanej pod kątem pamięci. Począwszy od programu SQL Server 2017 (14.x) i usługi Azure SQL Database, nie ma już limitu liczby indeksów specyficznych dla tabel i typów tabel zoptymalizowanych pod kątem pamięci.
Przykładowy kod składni
Ta podsekcja zawiera blok kodu Transact-SQL, który demonstruje składnię tworzenia różnych indeksów w tabeli zoptymalizowanej pod kątem pamięci. Kod przedstawia następujące elementy:
Utwórz tabelę zoptymalizowaną pod kątem pamięci.
Użyj instrukcji ALTER TABLE, aby dodać dwa indeksy.
WSTAW kilka wierszy danych.
DROP TABLE IF EXISTS SupportEvent; go CREATE TABLE SupportEvent ( SupportEventId int not null identity(1,1) PRIMARY KEY NONCLUSTERED, StartDateTime datetime2 not null, CustomerName nvarchar(16) not null, SupportEngineerName nvarchar(16) null, Priority int null, Description nvarchar(64) null ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); go -------------------- ALTER TABLE SupportEvent ADD CONSTRAINT constraintUnique_SDT_CN UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName); go ALTER TABLE SupportEvent ADD INDEX idx_hash_SupportEngineerName HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64); -- Nonunique. go -------------------- INSERT INTO SupportEvent (StartDateTime, CustomerName, SupportEngineerName, Priority, Description) VALUES ('2016-02-23 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.' ), ('2016-02-24 13:40:41:323', 'Ben' , null , 1, 'Cannot find help.' ), ('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.' ), ('2016-02-26 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.'); go
Zduplikowane wartości klucza indeksu
Zduplikowane wartości klucza indeksu mogą zmniejszyć wydajność tabel zoptymalizowanych pod kątem pamięci. Duplikaty systemu umożliwiające przechodzenie łańcuchów wejścia dla większości operacji odczytu i zapisu indeksu. Gdy łańcuch zduplikowanych wpisów przekracza 100 wpisów, obniżenie wydajności może stać się wymierne.
Zduplikowane wartości skrótu
Ten problem jest bardziej widoczny w przypadku indeksów skrótowych. Indeksy skrótów cierpią bardziej z powodu następujących zagadnień:
- Niższy koszt operacji dla indeksów skrótów.
- Interferencja dużych zduplikowanych łańcuchów z łańcuchem kolizji skrótu.
Aby zmniejszyć duplikację indeksu, spróbuj wykonać następujące korekty:
- Użyj indeksu nieklastrowanego.
- Dodaj dodatkowe kolumny na końcu klucza indeksu, aby zmniejszyć liczbę duplikatów.
- Można na przykład dodać kolumny, które znajdują się również w kluczu podstawowym.
Aby uzyskać więcej informacji na temat kolizji skrótów, zobacz indeksy skrótów dla tabel Memory-Optimized.
Przykładowa poprawa
Oto przykład tego, jak uniknąć utraty wydajności w indeksie.
Rozważ tabelę Customers
zawierającą klucz podstawowy w CustomerId
i zawiera indeks w kolumnie CustomerCategoryID
. Zazwyczaj w danej kategorii będzie wielu klientów. W związku z tym w danym kluczu indeksu będzie istnieć wiele zduplikowanych wartości customerCategoryID.
W tym scenariuszu najlepszym rozwiązaniem jest użycie indeksu nieklastrowanego w (CustomerCategoryID, CustomerId)
. Ten indeks może być używany w przypadku zapytań używających predykatu obejmującego CustomerCategoryID
, ale klucz indeksu nie zawiera duplikatów. W związku z tym żadna nieefektywność konserwacji indeksu nie jest powodowana przez zduplikowane wartości CustomerCategoryID lub przez dodatkową kolumnę w indeksie.
Poniższe zapytanie przedstawia średnią liczbę zduplikowanych wartości klucza indeksu dla indeksu w CustomerCategoryID
tabeli Sales.Customers
w przykładowej bazie danych WideWorldImporters.
SELECT AVG(row_count) FROM
(SELECT COUNT(*) AS row_count
FROM Sales.Customers
GROUP BY CustomerCategoryID) a
Aby ocenić średnią liczbę duplikatów klucza indeksu dla własnej tabeli i indeksu, zastąp Sales.Customers
nazwą tabeli i zastąp CustomerCategoryID
listą kolumn kluczy indeksu.
Porównywanie, kiedy używać każdego typu indeksu
Charakter określonych zapytań określa, który typ indeksu jest najlepszym wyborem.
Podczas implementowania tabel zoptymalizowanych pod kątem pamięci w istniejącej aplikacji ogólne zalecenie polega na rozpoczęciu od indeksów nieklastrowanych, ponieważ ich możliwości bardziej przypominają możliwości tradycyjnych klastrowanych i nieklastrowanych indeksów w tabelach opartych na dyskach.
Zalecenia dotyczące nieklastrowanego użycia indeksu
Preferowany jest indeks nieklastrowany zamiast indeksu skrótu, gdy:
- Zapytania mają klauzulę
ORDER BY
w kolumnie indeksowanej. - Zapytania, w których testowane są tylko kolumny wiodące indeksu wielokolumnowego.
- Zapytania przetestuj kolumnę indeksowaną przy użyciu klauzuli
WHERE
z:- Nierówność:
WHERE StatusCode != 'Done'
- Skanowanie zakresu wartości:
WHERE Quantity >= 100
- Nierówność:
We wszystkich następujących zapytaniach SELECT, indeks nieklastrowany jest bardziej preferowany względem indeksu skrótu.
SELECT CustomerName, Priority, Description
FROM SupportEvent
WHERE StartDateTime > DateAdd(day, -7, GetUtcDate());
SELECT StartDateTime, CustomerName
FROM SupportEvent
ORDER BY StartDateTime DESC; -- ASC would cause a scan.
SELECT CustomerName
FROM SupportEvent
WHERE StartDateTime = '2016-02-26';
Zalecenia dotyczące użycia indeksu skrótów
indeksy skrótów są używane głównie do wyszukiwania punktów, a nie do skanowania zakresu.
Indeks skrótu jest preferowany względem indeksu nieklastrowanego, gdy zapytania używają predykatów równości, a klauzula WHERE
jest mapowana na wszystkie kolumny kluczy indeksu, jak w poniższym przykładzie:
SELECT CustomerName
FROM SupportEvent
WHERE SupportEngineerName = 'Liz';
Indeks wielokolumny
Indeks wielokolumny może być indeksem nieklastrowanym lub indeksem skrótu. Przypuśćmy, że kolumny indeksu to col1 i col2. Biorąc pod uwagę następującą instrukcję SELECT
, tylko indeks nieklastrowany byłby przydatny dla optymalizatora zapytań:
SELECT col1, col3
FROM MyTable_memop
WHERE col1 = 'dn';
Indeks haszujący wymaga klauzuli WHERE
do określenia testu równości dla każdej z kolumn w swoim kluczu. W przeciwnym razie indeks skrótu nie jest przydatny dla optymalizatora zapytań.
Żaden typ indeksu nie jest przydatny, jeśli klauzula WHERE
określa tylko drugą kolumnę w kluczu indeksu.
Tabela podsumowania do porównywania scenariuszy użycia indeksu
W poniższej tabeli wymieniono wszystkie operacje obsługiwane przez różne typy indeksów. Tak oznacza, że indeks może efektywnie obsłużyć żądanie, a Nie oznacza, że indeks nie może skutecznie spełnić żądania.
Operacja | Zoptymalizowane pod kątem pamięci, hasz |
Zoptymalizowane pod kątem pamięci, nieklastrowane |
Oparte na dyskach, (nie)klastrowane |
---|---|---|---|
Skanowanie indeksu, pobieranie wszystkich wierszy tabeli. | Tak | Tak | Tak |
Wyszukiwanie indeksowe na predykatach równości (=). | Tak (Wymagany jest pełny klucz). |
Tak | Tak |
Szukanie indeksu na podstawie predykatów nierówności i zakresu (>, <, <=, >=, BETWEEN ). |
Nie (Wyniki skanowania indeksu). |
Tak 1 | Tak |
Pobieranie wierszy w kolejności sortowania zgodnej z definicją indeksu. | Nie | Tak | Tak |
Pobieranie wierszy w kolejności sortowania zgodnej z odwrotną definicją indeksu. | Nie | Nie | Tak |
1 W przypadku indeksu nieklastrowanego zoptymalizowanego pod kątem pamięci pełny klucz nie jest wymagany do wykonywania wyszukiwania indeksu.
Automatyczne zarządzanie indeksami i statystykami
Korzystaj z rozwiązań, takich jak Adaptive Index Defrag, aby automatycznie zarządzać defragmentacją indeksu i aktualizacjami statystyk dla przynajmniej jednej bazie danych. Ta procedura automatycznie wybiera, czy ponownie skompilować lub zreorganizować indeks zgodnie z poziomem fragmentacji, między innymi parametrami, oraz zaktualizować statystyki z progiem liniowym.
Zobacz też
przewodnik projektowania indeksu programu SQL Server
indeksy hash dla tabel Memory-Optimized
indeksy nieklastrowane dla tabel Memory-Optimized
Defragmentacja Indeksu Adaptacyjnego