Индексы для оптимизированных для памяти таблицIndexes on Memory-Optimized Tables

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL DatabaseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database

В каждой таблице, оптимизированной для памяти, должен использоваться как минимум один индекс, так как индексы объединяют строки.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. В таблице, оптимизированной для памяти, каждый индекс также будет оптимизирован для памяти.On a memory-optimized table, every index is also memory-optimized. Между оптимизированными для памяти таблицами и традиционными индексами в дисковой таблице есть несколько различий:There are several ways in which an index on a memory-optimized table differs from a traditional index on a disk-base table:

  • Строки данных хранятся не на страницах. Поэтому нельзя сослаться на коллекции страниц, экстенты, разделы или единицы размещения, чтобы получить все страницы таблицы.Data rows are not stored on pages, so there is no collection of pages or extents, no partitions or allocation units that can be referenced to get all the pages for a table. Есть понятие страниц для одного из доступных типов индексов, но они хранятся не так, как индексы таблиц на основе дисков.There is the concept of index pages for one of the available types of indexes, but they are stored differently than indexes for disk-based tables. Они не приводят к традиционной фрагментации на странице, поэтому не имеют коэффициента заполнения.They do not accrue the traditional type of fragmentation within a page, so they have no fillfactor.
  • Изменение индексов в таблицах, оптимизированных для памяти, во время работы с данными никогда не записываются на диск.Changes made to indexes on memory-optimized tables during data manipulation are never written to disk. В журнал транзакций записываются только строки данных и изменения данных.Only the data rows, and changes to the data, are written to the transaction log.
  • Оптимизированные для памяти индексы перестраиваются, когда база данных переходит в оперативный режим.Memory-optimized indexes are rebuilt when the database is brought back online.

При восстановлении базы данных все индексы в таблицах, оптимизированных для памяти, создаются на основе определений индексов.All indexes on memory-optimized tables are created based on the index definitions during database recovery.

Индекс должен быть одним из следующих:The index must be one of the following:

  • хэш-индекс;Hash index
  • некластеризованный индекс, оптимизированный для памяти (т. е. внутренняя структура сбалансированного дерева по умолчанию).Memory-optimized Nonclustered index (meaning the default internal structure of a B-tree)

Хэш-индексы для таблиц, оптимизированных для памяти, более подробно рассматриваются в этом разделе.Hash indexes are discussed in more detail in Hash Indexes for Memory-Optimized Tables.
Некластеризованные индексы для таблиц, оптимизированных для памяти, более подробно рассматриваются в этом разделе.Nonclustered indexes are discussed in more detail in Nonclustered Index for Memory-Optimized Tables.
Индексы columnstore рассматриваются в другой статье.Columnstore indexes are discussed in another article.

Синтаксис индексов, оптимизированных для памятиSyntax for memory-optimized indexes

Каждая операция CREATE TABLE для таблицы, оптимизированной для памяти, должна включать индекс либо явно посредством INDEX, либо неявно посредством ограничения PRIMAY KEY или UNIQUE.Each CREATE TABLE statement for a memory-optimized table must include an index, either explicitly through an INDEX or implicitly through a PRIMAY KEY or UNIQUE constraint.

Чтобы быть объявленной с параметром DURABILITY = SCHEMA_AND_DATA по умолчанию, таблица, оптимизированная для памяти, должна иметь первичный ключ.To be declared with the default DURABILITY = SCHEMA_AND_DATA, the memory-optimized table must have a primary key. Предложение PRIMARY KEY NONCLUSTERED в следующей инструкции CREATE TABLE отвечает двум требованиям:The PRIMARY KEY NONCLUSTERED clause in the following CREATE TABLE statement satisfies two requirements:

  • предоставляет индекс, благодаря чему выполняется требование наличия по крайней мере одного индекса в инструкции CREATE TABLE;Provides an index to meet the minimum requirement of one index in the CREATE TABLE statement.

  • предоставляет первичный ключ, необходимый для предложения SCHEMA_AND_DATA.Provides the primary key that is required for the SCHEMA_AND_DATA clause.

    CREATE TABLE SupportEvent  
    (  
        SupportEventId   int NOT NULL  
            PRIMARY KEY NONCLUSTERED,  
        ...  
    )  
        WITH (  
            MEMORY_OPTIMIZED = ON,  
            DURABILITY = SCHEMA_AND_DATA);  
    

Примечание

В SQL Server 2014 (12.x)SQL Server 2014 (12.x) и SQL Server 2016 (13.x);SQL Server 2016 (13.x) действует ограничение в 8 индексов на таблицу, оптимизированную для памяти, или тип таблицы.SQL Server 2014 (12.x)SQL Server 2014 (12.x) and SQL Server 2016 (13.x);SQL Server 2016 (13.x) have a limit of 8 indexes per memory-optimized table or table type. Начиная с версии SQL Server 2017 (14.x);SQL Server 2017 (14.x) и в База данных SQL AzureAzure SQL Database больше не применяются ограничения числа индексов для таблиц, оптимизированных для памяти, и типов таблиц.Starting with SQL Server 2017 (14.x);SQL Server 2017 (14.x) and in База данных SQL AzureAzure SQL Database, there is no longer a limit on the number of indexes specific to memory-optimized tables and table types.

Пример кода для синтаксисаCode sample for syntax

В этом подразделе приведен блок кода Transact-SQL, демонстрирующий синтаксис для создания различных индексов в таблице, оптимизированной для памяти.This subsection contains a Transact-SQL code block that demonstrates the syntax to create various indexes on a memory-optimized table. Этот код демонстрирует следующее.The code demonstrates the following:

  1. Создание оптимизированной для памяти таблицы.Create a memory-optimized table.

  2. Используйте инструкции ALTER TABLE для добавления двух индексов.Use ALTER TABLE statements to add two indexes.

  3. Вставьте (INSERT) несколько строк данных.INSERT a few rows of data.

    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 
    

Повторяющиеся значения ключа индексаDuplicate index key values

Повторяющиеся значения ключа индекса способны снизить производительность таблиц, оптимизированных для памяти.Duplicate values for an index key might reduce the performance of memory-optimized tables. Повторяющиеся значения заставляют систему обходить цепочки записей в большинстве операций чтения и записи индекса.Duplicates for the system to traverse entry chains for most index read and write operations. Когда цепь повторяющихся записей превышает длину 100 записей, снижение производительности может стать измеримым.When a chain of duplicate entries exceeds 100 entries, the performance degradation can become measurable.

Повторяющиеся значения хэшаDuplicate hash values

Эта проблема лучше проявляется в случае хэш-индексов.This problem is more visible in the case of hash indexes. Хэш-индексы страдают от нее больше, если принять во внимание следующие аспекты:Hash indexes suffer more due to the following considerations:

  • Более низкие затраты на каждую операцию для хэш-индексов.The lower cost per operation for hash indexes.
  • Пересечение больших цепочек повторяющихся значений с цепочкой конфликтов хэша.The interference of large duplicate chains with the hash collision chain.

Чтобы сократить дублирование в индексе, выполните следующее:To reduce duplication in an index, try the following adjustments:

  • Используйте некластеризованный индекс.Use a nonclustered index.
  • Добавьте дополнительные столбцы в конец ключа индекса, чтобы уменьшить число дубликатов.Add additional columns to the end of the index key, to reduce the number of duplicates.
    • Например, можно добавить столбцы, входящие также в первичный ключ.For example, you could add columns that are also in the primary key.

Дополнительные сведения о хэш-конфликтах см. в разделе о хэш-индексах для таблиц, оптимизированных для памяти.For more information about hash collisions, see Hash Indexes for Memory-Optimized Tables.

Пример улучшенияExample improvement

Вот пример того, как избежать снижения производительности индекса.Here is an example of how to avoid any performance inefficiency in your index.

Например, рассмотрим таблицу Customers с первичным ключом в CustomerId и индексом в столбце CustomerCategoryID.Consider a Customers table that has a primary key on CustomerId, and has an index on column CustomerCategoryID. Обычно будет существовать большое количество клиентов в каждой категории.Typically there will be many customers in a given category. Таким образом, будет много повторяющихся значений CustomerCategoryID внутри заданного ключа индекса.Thus there will be many duplicate values for CustomerCategoryID inside a given key of the index.

В такой ситуации рекомендуется использовать некластеризованный индекс в (CustomerCategoryID, CustomerId).In this scenario, the best practice is to use a nonclustered index on (CustomerCategoryID, CustomerId). Этот индекс можно использовать для запросов, использующих предикат с участием CustomerCategoryID, но ключ индекса не содержит повторяющихся значений.This index can be used for queries that use a predicate involving CustomerCategoryID, yet the index key does not contain duplication. Таким образом, устраняется неэффективность обслуживания индекса из-за повтора значений CustomerCategoryID или дополнительного столбца в индексе.Therefore, no inefficiencies in index maintenance are cause by either the duplicate CustomerCategoryID values, or by the extra column in the index.

В следующем запросе показано среднее число повторяющихся значений ключа индекса для индекса CustomerCategoryID в таблице Sales.Customersв образце базы данных WideWorldImporters.The following query shows the average number of duplicate index key values for the index on CustomerCategoryID in table Sales.Customers, in the sample database WideWorldImporters.

SELECT AVG(row_count) FROM
    (SELECT COUNT(*) AS row_count 
        FROM Sales.Customers
        GROUP BY CustomerCategoryID) a

Чтобы вычислить среднее число повторяющихся ключей индекса для таблицы и индекса, замените Sales.Customers именем таблицы, а CustomerCategoryID — списком столбцов ключей индекса.To evaluate the average number of index key duplicates for your own table and index, replace Sales.Customers with your table name, and replace CustomerCategoryID with the list of index key columns.

Сравнение с использованием каждого типа индексаComparing when to use each index type

Наиболее подходящий тип индекса определяется характером конкретных запросов.The nature of your particular queries determines which type of index is the best choice.

При реализации оптимизированных для памяти таблиц в существующем приложении действует общая рекомендация — начинать с некластеризованных индексов, так как их возможности больше похожи на возможности традиционных кластеризованных и некластеризованных индексов в таблицах на диске.When implementing memory-optimized tables in an existing application, the general recommendation is to start with nonclustered indexes, as their capabilities more closely resemble the capabilities of traditional clustered and nonclustered indexes on disk-based tables.

Рекомендации по использованию некластеризованных индексовRecommendations for nonclustered index use

Некластеризованный индекс является предпочтительным по сравнению с хэш-индексом, когда:A nonclustered index is preferable over a hash index when:

  • Запросы содержат предложение ORDER BY для индексированного столбца.Queries have an ORDER BY clause on the indexed column.
  • Используются запросы, в которых проверяются только первые столбцы для индекса, состоящего из нескольких столбцов.Queries where only the leading column(s) of a multi-column index is tested.
  • Запросы проверяют индексированный столбец с помощью предложения WHERE, в котором содержится:Queries test the indexed column by use of a WHERE clause with:
    • неравенство WHERE StatusCode != 'Done';An inequality: WHERE StatusCode != 'Done'
    • проверка диапазонов значений WHERE Quantity >= 100.A value range scan: WHERE Quantity >= 100

Во всех следующих инструкциях SELECT некластеризованный индекс является предпочтительным по сравнению с хэш-индексом:In all the following SELECTs, a nonclustered index is preferable over a hash index:

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';  

Рекомендации по использованию хэш-индексовRecommendations for hash index use

Хэш-индексы в основном используются для уточняющих запросов, а не для проверок диапазона.Hash indexes are primarily used for point lookups and not for range scans.

Хэш-индекс предпочтительнее некластеризованного, если при запросах используются предикаты равенства, а предложение WHERE сопоставляет все ключевые столбцы индекса, как показано в следующем примере:A hash index is preferable over a nonclustered index when queries use equality predicates, and the WHERE clause maps to all index key columns, as in the following example:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

Индекс для нескольких столбцовMulti-column index

Для нескольких столбцов может использоваться как некластеризованный индекс, так и хэш-индекс.A multi-column index could be a nonclustered index or a hash index. Предположим, что индекс включает столбцы col1 и col2.Suppose the index columns are col1 and col2. Учитывая следующую инструкцию SELECT, для оптимизатора запросов применим только некластеризованный индекс:Given the following SELECT statement, only the nonclustered index would be useful to the query optimizer:

SELECT col1, col3  
FROM MyTable_memop  
WHERE col1 = 'dn';  

Для хэш-индекса в предложении WHERE должен быть задан тест на равенство для каждого столбца в ключе.The hash index needs the WHERE clause to specify an equality test for each of the columns in its key. Без него хэш-индекс не будет полезен для оптимизатора запросов.Else the hash index is not useful to the query optimizer.

Тип индекса также не будет иметь значения, если предложение WHERE определяет только второй столбец ключа индекса.Neither index type is useful if the WHERE clause specifies only the second column in the index key.

Сводная таблица, в которой сравниваются сценарии использования индексовSummary table to compare index use scenarios

В следующей таблице перечислены все операции, поддерживаемые различными типами индексов.The following table lists all operations that are supported by the different index types. В таблице Да означает, что индекс может эффективно обслуживать запрос, а Нет — что не может.Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.

ОперацияOperation Оптимизированная для памяти,Memory-optimized,
hashhash
Оптимизированная для памяти,Memory-optimized,
некластеризованныйnonclustered
ДисковаяDisk-based,
(не)кластеризованная(non)clustered
Сканирование индекса, получение всех строк таблицы.Index Scan, retrieve all table rows. ДаYes ДаYes ДаYes
Поиск по индексу с использованием предикатов равенства (=).Index seek on equality predicates (=). ДаYes
(Требуется полный ключ.)(Full key is required.)
ДаYes ДаYes
Поиск по индексу с использованием неравенства и предикатов диапазонаIndex seek on inequality and range predicates
(>, <, <=, >=, BETWEEN).(>, <, <=, >=, BETWEEN).
НетNo
(Результаты в сканировании индекса.)(Results in an index scan.)
Да 1Yes 1 ДаYes
Получение строк в порядке сортировки, соответствующем определению индекса.Retrieve rows in a sort order that matches the index definition. НетNo ДаYes ДаYes
Получение строк в порядке сортировки, соответствующем обратному определению индекса.Retrieve rows in a sort-order that matches the reverse of the index definition. НетNo НетNo ДаYes
       

1 Для некластеризованного индекса, оптимизированного для памяти, полный ключ не требуется.1 For a memory-optimized Nonclustered index, the full key is not required to perform an index seek.

Автоматическое управление индексами и статистикойAutomatic index and statistics management

Используйте такие решения, как Адаптивная дефрагментация индексов, чтобы автоматически управлять дефрагментацией индексов и обновлениями статистики для одной базы данных или нескольких.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Эта процедура автоматически выбирает, следует ли перестроить или реорганизовать индекс, сверяясь с уровнем фрагментации и другими параметрами, и обновляет статистику на основе линейных пороговых значений.This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

См. также:See Also

Руководство по проектированию индексов SQL Server SQL Server Index Design Guide
Хэш-индексы для таблиц, оптимизированных для памяти Hash Indexes for Memory-Optimized Tables
Некластеризованные индексы для таблиц, оптимизированных для памяти Nonclustered Indexes for Memory-Optimized Tables
Адаптивная дефрагментация индексовAdaptive Index Defrag