Udostępnij za pośrednictwem


Indeksy w tabelach Memory-Optimized

Dotyczy:SQL ServerAzure SQL DatabaseAzure 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:

  1. Utwórz tabelę zoptymalizowaną pod kątem pamięci.

  2. Użyj instrukcji ALTER TABLE, aby dodać dwa indeksy.

  3. 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 CustomerIdi 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.Customersw 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

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