Индексы для оптимизированных для памяти таблиц

  • Статья

Применяется к:SQL ServerAzure SQL DatabaseAzure, управляемому экземпляру SQL Azure

В каждой таблице, оптимизированной для памяти, должен использоваться как минимум один индекс, так как индексы объединяют строки. В таблице, оптимизированной для памяти, каждый индекс также будет оптимизирован для памяти. Между оптимизированными для памяти таблицами и традиционными индексами в дисковой таблице есть несколько различий:

  • Строки данных хранятся не на страницах. Поэтому нельзя сослаться на коллекции страниц, экстенты, разделы или единицы размещения, чтобы получить все страницы таблицы. Есть понятие страниц для одного из доступных типов индексов, но они хранятся не так, как индексы таблиц на основе дисков. Они не приводят к традиционной фрагментации на странице, поэтому не имеют коэффициента заполнения.
  • Изменение индексов в таблицах, оптимизированных для памяти, во время работы с данными никогда не записываются на диск. В журнал транзакций записываются только строки данных и изменения данных.
  • Оптимизированные для памяти индексы перестраиваются, когда база данных переходит в оперативный режим.

При восстановлении базы данных все индексы в таблицах, оптимизированных для памяти, создаются на основе определений индексов.

Индекс должен быть одним из следующих:

  • хэш-индекс;
  • некластеризованный индекс, оптимизированный для памяти (т. е. внутренняя структура сбалансированного дерева по умолчанию).

Хэш-индексы для таблиц, оптимизированных для памяти, более подробно рассматриваются в этом разделе.
Некластеризованные индексы для таблиц, оптимизированных для памяти, более подробно рассматриваются в этом разделе.
Индексыcolumnstore рассматриваются в другой статье.

Синтаксис индексов, оптимизированных для памяти

Каждая операция CREATE TABLE для таблицы, оптимизированной для памяти, должна включать индекс либо явно посредством INDEX, либо неявно посредством ограничения PRIMAY KEY или UNIQUE.

Для объявления с параметром DURABILITY = SCHEMA_AND_DATA по умолчанию оптимизированная для памяти таблица должна иметь первичный ключ. Предложение PRIMARY KEY NONCLUSTERED в следующей инструкции CREATE TABLE отвечает двум требованиям:

  • предоставляет индекс, благодаря чему выполняется требование наличия по крайней мере одного индекса в инструкции CREATE TABLE;

  • предоставляет первичный ключ, необходимый для предложения SCHEMA_AND_DATA.

    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 2016 (13.x) имеют ограничение в 8 индексов на оптимизированную для памяти таблицу или тип таблицы. Начиная с SQL Server 2017 (14.x) и в Базе данных SQL Azure больше нет ограничения на количество индексов, относящихся к оптимизированным для памяти таблицам и типам таблиц.

Пример кода для синтаксиса

В этом подразделе приведен блок кода Transact-SQL, демонстрирующий синтаксис для создания различных индексов в таблице, оптимизированной для памяти. Этот код демонстрирует следующее.

  1. Создание оптимизированной для памяти таблицы.

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

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

    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

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

Повторяющиеся значения ключа индекса способны снизить производительность таблиц, оптимизированных для памяти. Повторяющиеся значения заставляют систему обходить цепочки записей в большинстве операций чтения и записи индекса. Когда цепь повторяющихся записей превышает длину 100 записей, снижение производительности может стать измеримым.

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

Эта проблема лучше проявляется в случае хэш-индексов. Хэш-индексы страдают от нее больше, если принять во внимание следующие аспекты:

  • Более низкие затраты на каждую операцию для хэш-индексов.
  • Пересечение больших цепочек повторяющихся значений с цепочкой конфликтов хэша.

Чтобы сократить дублирование в индексе, выполните следующее:

  • Используйте некластеризованный индекс.
  • Добавьте дополнительные столбцы в конец ключа индекса, чтобы уменьшить число дубликатов.
    • Например, можно добавить столбцы, входящие также в первичный ключ.

Дополнительные сведения о хэш-конфликтах см. в разделе о хэш-индексах для таблиц, оптимизированных для памяти.

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

Вот пример того, как избежать снижения производительности индекса.

Например, рассмотрим таблицу Customers с первичным ключом в CustomerId и индексом в столбце CustomerCategoryID. Обычно будет существовать большое количество клиентов в каждой категории. Таким образом, будет много повторяющихся значений CustomerCategoryID внутри заданного ключа индекса.

В такой ситуации рекомендуется использовать некластеризованный индекс в (CustomerCategoryID, CustomerId). Этот индекс можно использовать для запросов, использующих предикат с участием CustomerCategoryID, но ключ индекса не содержит повторяющихся значений. Таким образом, устраняется неэффективность обслуживания индекса из-за повтора значений CustomerCategoryID или дополнительного столбца в индексе.

В следующем запросе показано среднее число повторяющихся значений ключа индекса для индекса CustomerCategoryID в таблице Sales.Customersв образце базы данных WideWorldImporters.

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

Чтобы вычислить среднее число повторяющихся ключей индекса для таблицы и индекса, замените Sales.Customers именем таблицы, а CustomerCategoryID — списком столбцов ключей индекса.

Сравнение с использованием каждого типа индекса

Наиболее подходящий тип индекса определяется характером конкретных запросов.

При реализации оптимизированных для памяти таблиц в существующем приложении действует общая рекомендация — начинать с некластеризованных индексов, так как их возможности больше похожи на возможности традиционных кластеризованных и некластеризованных индексов в таблицах на диске.

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

Некластеризованный индекс является предпочтительным по сравнению с хэш-индексом, когда:

  • Запросы содержат предложение ORDER BY для индексированного столбца.
  • Используются запросы, в которых проверяются только первые столбцы для индекса, состоящего из нескольких столбцов.
  • Запросы проверяют индексируемый WHERE столбец с помощью предложения:
    • неравенство WHERE StatusCode != 'Done';
    • проверка диапазонов значений WHERE Quantity >= 100.

Во всех следующих инструкциях SELECT некластеризованный индекс является предпочтительным по сравнению с хэш-индексом:

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

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

Хэш-индексы в основном используются для уточняющих запросов, а не для проверок диапазона.

Хэш-индекс предпочтительнее некластеризованного, если при запросах используются предикаты равенства, а предложение WHERE сопоставляет все ключевые столбцы индекса, как показано в следующем примере:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

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

Для нескольких столбцов может использоваться как некластеризованный индекс, так и хэш-индекс. Предположим, что индекс включает столбцы col1 и col2. Учитывая следующую инструкцию SELECT, для оптимизатора запросов применим только некластеризованный индекс:

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

Для хэш-индекса в предложении WHERE должен быть задан тест на равенство для каждого столбца в ключе. Без него хэш-индекс не будет полезен для оптимизатора запросов.

Тип индекса также не будет иметь значения, если предложение WHERE определяет только второй столбец ключа индекса.

Сводная таблица, в которой сравниваются сценарии использования индексов

В следующей таблице перечислены все операции, поддерживаемые различными типами индексов. В таблице Да означает, что индекс может эффективно обслуживать запрос, а Нет — что не может.

Операция Оптимизированная для памяти,
hash		 Оптимизированная для памяти,
некластеризованный		 На основе дисков
(не)кластеризованная
Сканирование индекса, получение всех строк таблицы. Да Да Да
Поиск по индексу с использованием предикатов равенства (=). Да
(Требуется полный ключ.)		 Да Да
Индекс ищет предикаты неравенства и диапазона
(>, <, <=, >=, BETWEEN).		 Без
(Результаты в сканировании индекса.)		 Да 1 Да
Получение строк в порядке сортировки, соответствующем определению индекса. No Да Да
Получение строк в порядке сортировки, соответствующем обратному определению индекса. No Нет Да

1 Для некластеризованного индекса, оптимизированного для памяти, полный ключ не требуется.

Автоматическое управление индексами и статистикой

Используйте такие решения, как Адаптивная дефрагментация индексов, чтобы автоматически управлять дефрагментацией индексов и обновлениями статистики для одной базы данных или нескольких. Эта процедура автоматически выбирает, следует ли перестроить или реорганизовать индекс, сверяясь с уровнем фрагментации и другими параметрами, и обновляет статистику на основе линейных пороговых значений.

См. также

Руководство по проектированию индексов SQL Server
Хэш-индексы для оптимизированных для памяти таблиц
Некластеризованные индексы для таблиц, оптимизированных для памяти
Адаптивная дефрагментация индексов