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

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

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

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

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

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

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

Каждая инструкция CREATE TABLE для таблицы, оптимизированной для памяти, должна включать индекс — либо явно с помощью INDEX, либо неявно с помощью ограничения PRIMARY 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 определяет только второй столбец ключа индекса.

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

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

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

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

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

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

Связанные материалы

  • Последнее обновление: