Руководство по архитектуре и разработке индексов SQL Server

Применимо к: даSQL Server (все поддерживаемые версии) ДаБаза данных SQL Azure ДаУправляемый экземпляр SQL Azure даAzure Synapse Analytics даПараллельное хранилище данных

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

Предполагается, что читатель обладает общими знаниями типов индексов, которые есть в SQL Server. Общее описание типов индексов приведено в разделе Типы индексов.

В этом руководстве рассматриваются следующие типы индексов:

  • Кластеризованный
  • Некластеризованный
  • Уникальная идентификация
  • Filtered
  • columnstore
  • Хэш
  • Некластеризованный индекс, оптимизированный для памяти

Сведения об XML-индексах см. в разделах Обзор XML-индексов и Выборочные XML-индексы (SXI).

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

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

Основы проектирования индексов

Представьте себе обычную книгу: в конце книги есть указатель, который помогает быстро находить информацию в книге. Указатель представляет собой отсортированный список ключевых слов, а рядом с ключевым словом — номера страниц, где можно найти каждое ключевое слово. Индекс SQL Server устроен так же. Это упорядоченный список значений, и для каждого значения есть указатели на страницы данных, где находятся эти значения. Сам индекс хранится на страницах индексов в SQL Server. В обычной книге, если указатель занимает несколько страниц и необходимо найти указатели на все страницы, содержащие слово "SQL", например, вам придется листать до тех пор, пока вы не найдете страницу указателя с ключевым словом "SQL". После этого можно следовать указателям на все страницы книги. Этот процесс можно оптимизировать, если в самом начале индекса создать одну страницу, содержащую алфавитный список расположения каждой буквы. Пример: буквы от А до Г — стр. 121, буквы от Д до Ж — стр. 122 и т. д. Благодаря этой дополнительной странице не придется перелистывать указатель, чтобы найти нужное место. Такая страница не существует в обычных книгах, но существует в индексе SQL Server. Эта единственная страница называется корневой страницей индекса. Корневая страница — это начальная страница древовидной структуры, используемой индексом SQL Server. Следуя аналогии дерева, конечные страницы, содержащие указатели на реальные данные, называются "листьями" дерева.

Индекс SQL Server является структурой на диске или в памяти, которая связана с таблицей или представлением и ускоряет получение строк из таблицы или представления. Индекс содержит ключи, построенные из одного или нескольких столбцов в таблице или представлении. Для индексов на диске эти ключи хранятся в виде структуры сбалансированного дерева, которая поддерживает быстрый поиск строк по значениям ключей в SQL Server.

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

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

Оптимизатор запросов в SQL Server с большой вероятностью выбирает наилучший индекс в подавляющем большинстве случаев. Общая стратегия разработки индексов должна давать оптимизатору запросов по возможности разнообразные варианты, чтобы ему было из чего выбирать. Следует довериться его решению. Это уменьшит время анализа и обеспечит высокую производительность в различных ситуациях. Чтобы выяснить, какие индексы оптимизатор запросов использует для отдельных запросов, в меню Запрос среды SQL Server Management Studio выберите Включить действительный план выполнения.

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

1 Rowstore — это традиционный способ хранения реляционных данных таблиц. В SQL Server rowstore — это таблица с базовым форматом хранения данных "куча", "сбалансированное дерево" (кластеризованный индекс) или "таблица, оптимизированная для памяти".

Задачи проектирования индексов

Рекомендуемая стратегия проектирования индексов включает в себя следующие задачи:

  1. Прежде всего следует понять характеристики самой базы данных.

  2. Определите наиболее часто используемые запросы. Например, если известно, что часто используется запрос на соединение двух и более таблиц, это поможет определить наилучший тип индексов.

  3. Выясните характеристики столбцов, используемых в запросах. Например, идеальным будет индекс для столбцов с типом данных integer, которые к тому же имеют уникальные или отличные от NULL значения. Для столбцов с хорошо определенными подмножествами данных в SQL Server 2008 и более поздних версиях можно использовать отфильтрованный индекс. Дополнительные сведения см. в разделе Рекомендации по проектированию отфильтрованных индексов этого руководства.

  4. Определите, какие параметры индексов могут повысить производительность при создании индекса или при его поддержке. Например, при создании кластеризованного индекса для существующей большой таблицы выгодно будет использовать параметр ONLINE. Параметр ONLINE позволяет продолжать параллельную обработку базовых данных во время создания или повторного построения индекса. Дополнительные сведения см. в разделе Установка параметров индекса.

  5. Определите оптимальное расположение для хранения индекса. Некластеризованный индекс может храниться в той же файловой группе, что и базовая таблица, или в другой группе. Правильный выбор расположения для хранения индексов может повысить производительность запросов за счет повышения скорости дискового ввода-вывода. Например, если некластеризованный индекс хранится в файловой группе не на том диске, на котором расположены файловые группы таблицы, то производительность может повыситься, поскольку это позволяет одновременно обращаться к нескольким дискам.
    Кластеризованные и некластеризованные индексы могут использовать схему секционирования, которая охватывает несколько файловых групп. Секционирование делает большие таблицы и индексы более управляемыми, позволяет быстро и эффективно получать доступ к наборам данных и управлять ими, при этом сохраняя целостность всей коллекции. Дополнительные сведения см. в разделе Partitioned Tables and Indexes. При выборе секционирования определите, требуется ли выравнивание индекса, то есть должен ли индекс быть секционирован точно так же, как и таблицы, или он может быть секционирован иным образом.

Общие рекомендации по проектированию индексов

Опытный администратор базы данных может спроектировать хороший набор индексов, но эта задача очень сложна, требует много времени и сопряжена с ошибками даже для рабочих нагрузок и баз данных средней сложности. В разработке оптимальных индексов может помочь понимание характеристик базы данных, запросов и столбцов данных.

Соображения, связанные с базами данных

При проектировании индекса следует учитывать следующие рекомендации:

  • Большое количество индексов в таблице снижает производительность инструкций INSERT, UPDATE, DELETE и MERGE, потому что при изменении данных в таблице все индексы должны быть скорректированы соответствующим образом. Например, если столбец используется в нескольких индексах и выполняется инструкция UPDATE, которая изменяет данные из этого столбца, каждый индекс, содержащий этот столбец, должен быть обновлен, как и столбец в базовой таблице (куча или кластеризованный индекс).

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

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

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

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

  • Для анализа базы данных и получения рекомендаций по созданию индексов следует использовать помощник по настройке ядра СУБД. Дополнительные сведения см. в разделе Database Engine Tuning Advisor.

Вопросы работы с запросами

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

  • Следует создавать некластеризованные индексы для столбцов, которые часто используются в предикатах и условиях соединения в запросах. В нашем примере это столбцы SARGable1. Однако следует избегать добавления столбцов без необходимости. Добавление слишком большого числа индексных столбцов может отрицательно повлиять на количество свободного места на диске и на производительность поддержания индекса.

  • Покрывающие индексы могут повысить производительность запросов, так как данные, необходимые для удовлетворения требований запроса, присутствуют в самом индексе. Таким образом, для получения запрашиваемых данных требуются только страницы индекса, а не страницы данных таблицы или кластеризованного индекса. Следовательно, уменьшается общий объем операций дискового ввода-вывода. Например, запрос по столбцам a и b таблицы, у которой есть составной индекс, созданный на основе столбцов a, b и c , может найти нужные данные, пользуясь только этим индексом.

    Важно!

    Покрывающими индексами называются некластеризованные индексы, которые разрешают один или несколько схожих результатов запроса напрямую, без доступа к базовой таблице и без уточняющих запросов. Такие индексы имеют на конечном уровне все необходимые столбцы, отличные от SARGable. Это означает, что индекс включает столбцы, возвращаемые предложением SELECT и указываемые в любых аргументах WHERE и JOIN. Это позволяет существенно снизить объем операций ввода-вывода для выполнения запроса, если индекс будет достаточно узким по сравнению с количеством строк и столбцов в самой таблице, то есть будет правильным подмножеством ее столбцов. Используйте покрывающие индексы, при обращении к небольшому фрагменту большой таблицы, который определяется фиксированным предикатом, например разреженными столбцами с малым числом непустых значений.

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

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

1 Термин SARGable в реляционных базах данных обозначает сочетание предикатов S earchable (поддерживающий поиск) и ARG ument -able (поддерживающий аргументы), которые позволяют использовать индекс для ускорения запроса.

Вопросы работы со столбцами

При проектировании индекса, следует принимать во внимание следующие рекомендации, относящиеся к столбцам.

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

  • Столбцы с типами данных ntext, text, image, varchar(max) , nvarchar(max) и varbinary(max) нельзя указать в качестве ключевых столбцов индекса. Однако типы данных varchar(max) , nvarchar(max) , varbinary(max) и xml могут участвовать в некластеризованных индексах в качестве их неключевых столбцов индекса. Дополнительные сведения см. в разделе Индекс с включенными столбцамиэтого руководства.

  • Столбцы типа xml могут быть ключевым столбцом только в XML-индексе. Дополнительные сведения см в разделе XML-индексы (SQL Server). С пакетом обновления 1 (SP1) в SQL Server 2012 появился новый тип XML-индекса — выборочный XML-индекс. Этот новый индекс повышает производительность запросов для данных, хранимых в виде XML на SQL Server, и тем самым значительно ускоряет индексирование рабочих нагрузок XML-данных большого объема и повышает масштабируемость за счет уменьшения места хранения самого индекса. Дополнительные сведения см. в разделе Выборочный XML-индекс (SXI).

  • Проверьте уникальность столбцов. Замена неуникального индекса уникальным для той же комбинации столбцов обеспечивает оптимизатору запросов дополнительные сведения, что делает индекс более полезным. Дополнительные сведения см. в разделе Правила по созданию уникальных индексов этого руководства.

  • Проверьте распределение данных в столбце. Часто длительное выполнение запроса обусловлено индексированием столбца, в котором мало уникальных значений, или присоединением такого столбца. Это фундаментальная проблема, связанная с данными и запросом, и обычно она не может быть решена без определения ситуации. Например: физический телефонный справочник, отсортированный в алфавитном порядке по фамилии, не сможет быстро найти человека, если всех жителей города зовут Смит или Джонс. Дополнительные сведения о распределении данных см. в разделе Statistics.

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

  • Следует учитывать порядок столбцов, если индекс будет включать их несколько. Столбец, использованный в предложении WHERE в условии поиска равных (=), больших (>), меньших (<) или находящихся в интервале (BETWEEN) значений или участвующий в соединении, должен стоять первым. Дополнительные столбцы должны быть упорядочены по уровню различимости, то есть от наиболее четкого к наименее четкому.

    Например, если индекс определен как LastName, FirstName , индекс будет полезным, если критерий поиска — WHERE LastName = 'Smith' или WHERE LastName = Smith AND FirstName LIKE 'J%'. Однако оптимизатор запросов не станет использовать этот индекс для запроса только по критерию FirstName (WHERE FirstName = 'Jane').

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

Характеристики индекса

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

  • кластеризованный или некластеризованный;
  • уникальный или неуникальный;
  • с одним или несколькими столбцами;
  • порядок по возрастанию или по убыванию в столбцах индекса;
  • полнотабличные или фильтруемые некластеризованные индексы.
  • columnstore или rowstore;
  • хэш-индекс или некластеризованный индекс для таблиц, оптимизированных для памяти.

Также можно задать начальные характеристики хранилища индекса, чтобы оптимизировать его производительность или поддержание, задав такие параметры, как FILLFACTOR. Чтобы оптимизировать производительность, можно также определить место хранения индекса с помощью файловых групп или схем секционирования.

Размещение индекса в файловых группах или схемах секций

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

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

  • Создайте некластеризованные индексы в файловой группе, отличной от файловой группы базовой таблицы или кластеризованного индекса.
  • Секционировать кластеризованные и некластеризованные индексы, чтобы они размещались в нескольких файловых группах.
  • Переместить таблицу из одной файловой группы в другую, удалив кластеризованный индекс и указав новую файловую группу или схему секционирования в предложении MOVE TO инструкции DROP INDEX или выполнив инструкцию CREATE INDEX с предложением DROP_EXISTING.

Создав некластеризованный индекс в другой файловой группе, можно достичь прироста производительности, если файловые группы находятся на разных физических дисках с собственными контроллерами. Сведения о данных и индексе могут считываться параллельно несколькими головками. Например, если таблица Table_A в файловой группе f1 и индекс Index_A в файловой группе f2 используются в одном и том же запросе, производительность увеличится, так как обе файловые группы используются полностью, не состязаясь между собой. Однако если таблица Table_A сканируется запросом, а на индекс Index_A ссылки нет, то используется только файловая группа f1 . В этом случае нет никакого выигрыша в производительности.

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

Секции во многих файловых группах

Можно рассмотреть возможность секционирования кластеризованных и некластеризованных индексов по нескольким файловым группам. Секционированные индексы разбиваются горизонтально или построчно, в зависимости от функции секционирования. Функция секционирования определяет, как каждая строка сопоставляется с набором секций на основе значений определенных столбцов — столбцов секционирования. Схема секционирования определяет сопоставление секций набору файловых групп.

Секционирование индекса может предоставить следующие преимущества.

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

  • Запросы выполняются быстрее и эффективнее. Когда запросы выполняются в нескольких секциях индекса, оптимизатор запросов может обрабатывать определенные секции в одно и то же время и исключать секции, к которым запрос не относится.

Дополнительные сведения см. в разделе Partitioned Tables and Indexes.

Рекомендации по созданию порядка сортировки индексов

При определении индексов следует иметь в виду, что данные ключевых столбцов индекса сохраняются в порядке возрастания или убывания. По умолчанию сортировка производится по возрастанию, как и в предыдущих версиях SQL Server. Синтаксис инструкций CREATE INDEX, CREATE TABLE и ALTER TABLE поддерживает ключевые слова ASC (по возрастанию) и DESC (по убыванию) для конкретных столбцов в индексах и ограничениях.

Указание порядка, в котором значения ключей хранятся в индексе, полезно тогда, когда запрос ссылается на таблицу с предложением ORDER BY, в котором указано другое направление для ключевого столбца индекса или индексированного столбца. В этом случае индекс может исключить необходимость в операторе SORT в плане запроса, то есть запрос будет выполняться значительно эффективнее. Например, покупателю в отделе заказов Компания Adventure Works Cycles необходимо определить качество товаров от разных поставщиков. Больше всего его интересуют товары тех поставщиков, которые имеют набольшую частоту отказов. Как показано в следующем запросе, получение данных по соответствию этому критерию требует, чтобы столбец RejectedQty в таблице Purchasing.PurchaseOrderDetail был отсортирован в порядке убывания (от большего значения к меньшему), а столбец ProductID — в порядке возрастания (от меньшего к большему).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,  
    ProductID, DueDate  
FROM Purchasing.PurchaseOrderDetail  
ORDER BY RejectedQty DESC, ProductID ASC;  

Следующий план выполнения для этого запроса показывает, что оптимизатор запросов применяет оператор SORT для результирующего набора в порядке, указываемом предложением ORDER BY.

Схема плана выполнения для этого запроса, показывающая, что оптимизатор запросов применяет оператор SORT для результирующего набора в порядке, указываемом предложением ORDER BY.

Если создан индекс для ключевых столбцов, который соответствует индексу, указанному в предложении ORDER BY, оператор SORT может быть исключен из плана запроса, что значительно повысит его эффективность.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty  
ON Purchasing.PurchaseOrderDetail  
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);  

После повторного выполнения запроса план выполнения показывает, что оператор SORT исключен и используется вновь созданный некластеризованный индекс.

Схема плана выполнения, показывающая, что оператор SORT исключен и используется вновь созданный некластеризованный индекс.

Компонент Database Engine одинаково эффективно работает в обоих направлениях. Индекс, определенный как (RejectedQty DESC, ProductID ASC) , может также быть использован в запросах с порядком сортировки, обратным порядку, определенному в предложении ORDER BY. Например, запрос со следующим предложением ORDER BY-: ORDER BY RejectedQty ASC, ProductID DESC может воспользоваться этим индексом.

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

Метаданные

Используйте приведенные ниже представления метаданных, чтобы увидеть атрибуты индексов. В некоторых из этих представлений содержатся дополнительные сведения об архитектуре.

Примечание

Все столбцы в индексах columnstore хранятся в метаданных как включенные столбцы. Индекс columnstore не имеет ключевых столбцов.

Правила проектирования кластеризованного индекса

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

  • Может применяться для часто используемых запросов.

  • Обеспечивает высокую степень уникальности.

    Примечание

    При создании ограничения PRIMARY KEY будет автоматически создан уникальный индекс по столбцу или столбцам. По умолчанию этот индекс кластеризован; однако при создании ограничения можно указать некластеризованный индекс.

  • Может использоваться в диапазонных запросах.

Если кластеризованный индекс создан не со свойством UNIQUE, компонент Компонент Database Engine автоматически добавляет 4-байтовый столбец uniqueifier к таблице. При необходимости компонент Компонент Database Engine автоматически добавляет значение uniqueifier к строке, чтобы сделать каждый ключ уникальным. Данный столбец и его значения используются внутри и недоступны пользователям для просмотра или использования.

Архитектура кластеризованного индекса

В SQL Server индексы организованы в виде сбалансированных деревьев. Каждая страница в сбалансированном дереве индекса называется узлом индекса. Верхний узел сбалансированного дерева называется корневым. Узлы нижнего уровня индекса называются конечными. Все уровни индекса между корневыми и конечными узлами называются промежуточными. В кластеризованном индексе конечные узлы содержат страницы данных базовой таблицы. На страницах индекса корневого и промежуточного узлов находятся строки индекса. Каждая строка индекса содержит ключевое значение и указатель либо на страницу промежуточного уровня сбалансированного дерева, либо на строку данных на конечном уровне индекса. Страницы на каждом уровне связаны в двунаправленный список.

Для каждого кластеризованного индекса таблица sys.partitionsсодержит одну строку со значением index_id , равным 1 для каждой секции, используемой индексом. По умолчанию, кластеризованный индекс занимает одну секцию. Если кластеризованный индекс занимает несколько секций, каждая секция содержит сбалансированное дерево, содержащее данные этой секции. Например, если кластеризованный индекс занимает четыре секции, существует четыре сбалансированных дерева: по одному в каждой секции.

В зависимости от типов данных, каждая структура кластеризованного индекса состоит из одной или более единиц распределения, которые применяются для хранения и управления данными секции. Для каждой секции кластеризованный индекс содержит, как минимум, одну единицу распределения IN_ROW_DATA. Для хранения столбцов больших объектов (LOB) кластеризованному индексу требуется одна единица размещения LOB_DATA для каждой секции. Кроме того, для хранения столбцов переменной длины, превышающих ограничение на размер строки, равное 8060 байтам, для каждой секции требуется одна единица размещения ROW_OVERFLOW_DATA.

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

На следующем рисунке изображена структура кластеризованного индекса для одной секции.

Схема, изображающая структуру кластеризованного индекса для одной секции.

Вопросы работы с запросами

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

  • Возвращают диапазон значений с помощью операторов, таких как BETWEEN, >, >=, < и <=.

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

  • Возвращают большие результирующие наборы.

  • Используют предложения JOIN; обычно в них участвуют внешние ключевые столбцы.

  • Используют предложения ORDER BY или GROUP BY.

    Индекс по столбцам, указанным в предложении ORDER BY или GROUP BY, может исключить потребность в сортировке данных для компонента Компонент Database Engine , потому что строки будут уже отсортированы. Это улучшает производительность запроса.

Вопросы работы со столбцами

В общем случае надо так определить ключ кластеризованного индекса, чтобы в него вошло как можно меньше столбцов. Рассмотрите столбцы, которым присущ один или несколько следующих атрибутов:

  • Являются уникальными или содержат много различных значений.

    Например, идентификатор сотрудника уникально идентифицирует служащих. Кластеризованный индекс или ограничение PRIMARY KEY на столбец EmployeeID улучшило бы производительность запросов, которые производят поиск сведений о сотруднике, основываясь на номере идентификатора сотрудника. В качестве альтернативы кластеризованный индекс мог бы быть создан по столбцам LastName, FirstNameи MiddleName , потому что записи сотрудников часто группируются и запрашиваются именно таким образом, так что сочетание этих столбцов обеспечивало бы высокую степень различия.

    Совет

    Если не указано иное, при создании ограничения PRIMARY KEYSQL Server создает кластеризованный индекс для поддержки этого ограничения. Хотя для обеспечения уникальности в качестве PRIMARY KEY можно использовать uniqueidentifier , он не является эффективным ключом кластеризации. При применении uniqueidentifier в качестве PRIMARY KEY рекомендуется создавать его как некластеризованный индекс и использовать другой столбец, например IDENTITY, для создания кластеризованного индекса.

  • Обращение к ним происходит последовательно.

    Например, код продукта уникально идентифицирует продукты в таблице Production.Product в базе данных AdventureWorks2012 . Запросы, в которых указан последовательный поиск, например WHERE ProductID BETWEEN 980 and 999, извлекут заметную выгоду из кластеризованного индекса по ProductID. Это происходит потому, что строки будут храниться в отсортированном порядке по этому ключевому столбцу.

  • Определен как IDENTITY.

  • Часто используются для сортировки данных, полученных из таблицы.

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

Кластеризованные индексы — не лучший выбор для следующих атрибутов:

  • столбцов, которые подвергаются частым изменениям;

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

  • широких ключей.

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

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

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

Подобно тому, как читатель использует индекс в книге, оптимизатор запросов выискивает значение типа данных, просматривая некластеризованный индекс. Там он находит место расположения интересующего его значения в таблице и затем получает данные непосредственно из этого места. Благодаря этому некластеризованные индексы считаются оптимальным выбором для запросов с точным соответствием, поскольку такие индексы содержат записи, описывающие точное расположение в таблице значений типов данных, которые задаются в подобных запросах. К примеру, чтобы выбрать в таблице HumanResources. Employee всех сотрудников, подчиняющихся тому или иному менеджеру, оптимизатор запросов может воспользоваться некластеризованным индексом IX_Employee_ManagerID; ключевым столбцом в нем является ManagerID . Оптимизатор запросов может быстро обнаружить в индексе все записи, соответствующие указанному значению ManagerID. Каждый элемент указателя ссылается на конкретную страницу и строку в таблице или на кластеризованный индекс, в котором можно найти соответствующие данные. После того как оптимизатор запросов обнаружит все записи в индексе, он может переходить непосредственно к нужной странице и строке, откуда он будет получать требуемые данные.

Архитектура некластеризованного индекса

Некластеризованные индексы имеют ту же структуру сбалансированного дерева, что и кластеризованные индексы; существуют только следующие различия:

  • строки данных в базовой таблице не сортируются и хранятся в порядке, который основан на их некластеризованных ключах;

  • конечный уровень некластеризованного индекса состоит из страниц индекса вместо страниц данных.

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

  • Если таблица является кучей, что означает, что она не содержит кластеризованный индекс, то обнаружитель строки является указателем на строку. Указатель строится на основе идентификатора файла (ID), номера страницы и номера строки на странице. Весь указатель целиком называется идентификатором строки (RID).

  • Если для таблицы имеется кластеризованный индекс или индекс построен на индексированном представлении, то указатель строки — это ключ кластеризованного индекса для строки.

Для некластеризованных индексов таблица sys.partitions содержит одну строку со значением столбца index_id >1 для каждой секции, используемой индексом. По умолчанию некластеризованный индекс включает одну секцию. Если некластеризованный индекс состоит из нескольких секций, то каждая секция имеет структуру сбалансированного дерева, в которой содержатся индексные строки для данной конкретной секции. Например, если некластеризованный индекс состоит из четырех секций, то существуют четыре структуры сбалансированного дерева, по одной на каждую секцию.

В зависимости от типов данных в некластеризованном индексе каждая структура некластеризованного индекса будет содержать одну или более единиц распределения, в которых хранятся данные для определенной секции. Каждый некластеризованный индекс будет содержать по меньшей мере одну единицу размещения IN_ROW_DATA на секцию, в которой хранятся страницы сбалансированного дерева индекса. Некластеризованный индекс будет также содержать одну единицу размещения LOB_DATA на секцию, если в индексе содержатся столбцы типа большого объекта (LOB). Кроме того, некластеризованный индекс будет содержать одну единицу размещения ROW_OVERFLOW_DATA на секцию, если в индексе содержатся столбцы переменной длины, в которых превышается максимальный размер строки, равный 8060 байт.

На следующей иллюстрации показана структура некластеризованного индекса, состоящего из одной секции.

Схема, изображающая структуру некластеризованного индекса, состоящего из одной секции.

Соображения, связанные с базами данных

При проектировании некластеризованных индексов следует принимать во внимание характеристики соответствующей базы данных.

  • В базах данных или таблицах, характеризующихся нечастыми обновлениями и большими объемами хранимых данных, запросы могут выполняться быстрее при использовании множества некластеризованных индексов. Рекомендуется создать отфильтрованные индексы для четко определенных подмножеств данных, что позволит повысить производительность запросов, а также снизить затраты на обслуживание и хранение индексов по сравнению с полнотабличными некластеризованными индексами.

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

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

    Большое количество индексов в таблице снижает производительность инструкций INSERT, UPDATE, DELETE и MERGE, потому что при изменении данных в таблице все индексы должны быть соответствующим образом изменены.

Вопросы работы с запросами

Перед тем как приступать к созданию некластеризованных индексов, необходимо разобраться с тем, каким образом будет организован доступ к данным. Рассмотрите возможность использования некластеризованных индексов для запросов, обладающих следующими атрибутами:

  • Используют предложения JOIN или GROUP BY.

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

  • Запросы, не возвращающие больших результирующих наборов.

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

    Совет

    Как правило, предложение WHERE в инструкции CREATE INDEX совпадает с предложением WHERE в запросе, для которого создается индекс.

  • Содержат столбцы, часто встречающиеся в условиях поиска запроса, таких как предложение WHERE, возвращающих точные соответствия.

    Совет

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

Вопросы работы со столбцами

Рассмотрите столбцы, обладающие одним или несколькими указанными ниже атрибутами:

  • Покрытие запроса.

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

    Если таблица имеет кластеризованный индекс, то столбец или столбцы, определенные в этом кластеризованном индексе, автоматически добавляются к концу каждого некластеризованного индекса таблицы. Это может привести к возникновению покрытого запроса без указания столбцов кластеризованного индекса в определении некластеризованного индекса. Так, если для таблицы имеется кластеризованный индекс в столбце C, то некластеризованный индекс в столбцах B и A будет иметь в качестве ключевых значений столбцы B, Aи C.

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

    Если имеется очень мало индивидуальных значений, скажем, только 1 и 0, то при выполнении большинства запросов этот индекс не будет использоваться, так как эффективность просмотра таблиц, как правило, выше. Для этого типа данных рекомендуется создать отфильтрованный индекс для определенного значения, которое содержится в небольшом количестве строк. Например, если большая часть значений равна 0, то оптимизатор запросов может использовать отфильтрованный индекс для строк данных, в которых содержится 1.

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

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

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

  • Они не учитываются компонентом Компонент Database Engine при расчете числа ключевых столбцов индекса и размера ключа индекса.

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

Примечание

Если индекс содержит все столбцы, ссылаемые в запросе, это называется покрытием запроса.

В то время как ключевые столбцы сохраняются на всех уровнях индекса, неключевые столбцы сохраняются только на конечном уровне.

Использование включенных столбцов для обхода ограничений по размеру

Можно включать неключевые столбцы в некластеризованный индекс, чтобы избежать превышения текущих ограничений на размер индекса (16 ключевых столбцов) и размер ключа индекса (900 байт). Компонент Компонент Database Engine не учитывает неключевые столбцы при расчете количества ключевых столбцов индекса и размера ключа индекса.
Например, нужно индексировать следующие столбцы в таблице Document :

  • Title nvarchar(50)
  • Revision nchar(5)
  • FileName nvarchar(400)

Поскольку для типов данных nchar и nvarchar необходимо 2 байта для каждого символа, индекс, содержащий эти три столбца, на 10 байт превысит ограничение на размер в 900 байт (455 * 2). Использование предложения INCLUDE в инструкции CREATE INDEX позволит определить ключ индекса как (Title, Revision), а FileName определить как неключевой столбец. Таким образом, размер ключа индекса составит 110 байт (55 * 2), при этом индекс будет по-прежнему содержать все нужные столбцы. Следующая инструкция создает такой индекс:

CREATE INDEX IX_Document_Title   
ON Production.Document (Title, Revision)   
INCLUDE (FileName);   
Правила для индекса с включенными столбцами

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

  • Неключевые столбцы определяются предложением INCLUDE инструкции CREATE INDEX.

  • Неключевые столбцы можно определять только для некластеризованных индексов по таблицам или индексированным представлениям.

  • Допускаются данные всех типов, за исключением text, ntext и image.

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

  • Как и ключевые столбцы, вычисляемые столбцы, полученные на основе типов данных image, ntext и text , могут быть неключевыми (включенными) столбцами, если тип данных этого вычисляемого столбца допустим в качестве неключевого столбца индекса.

  • Имена столбцов нельзя указывать ни в списке INCLUDE, ни в списке ключевых столбцов.

  • Имена столбцов в списке INCLUDE нельзя повторять.

Требования к размеру столбцов
  • Должен быть определен как минимум один ключевой столбец. Максимальное количество неключевых столбцов равно 1023. Это на 1 меньше, чем максимальное количество столбцов таблицы.

  • Ключевые столбцы индекса, в отличие от неключевых, должны удовлетворять текущим ограничениям на максимальное количество столбцов (16) и общий размер ключа индекса (900 байт).

  • Общий размер всех неключевых столбцов ограничен только размером столбцов, указанных в предложении INCLUDE, например, столбцы varchar(max) могут иметь размер до 2 ГБ.

Правила изменения столбца

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

  • Неключевые столбцы нельзя удалять из таблицы до удаления соответствующего индекса.

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

    • изменение поведения столбца в отношении значения NULL с NOT NULL на NULL;

    • увеличение длины столбцов типов varchar, nvarchar и varbinary .

      Примечание

      Эти ограничения на изменение столбца также применяются к ключевым столбцам индекса.

Рекомендации по проектированию

Переопределите некластеризованные индексы с большим размером ключа индекса, чтобы только столбцы, используемые для поиска и уточняющего запроса, были ключевыми. Все остальные столбцы, покрывающие запрос, сделайте включенными неключевыми столбцами. Таким образом, в наличии будут все столбцы, покрывающие запрос, но сам ключ индекса будет небольшим и эффективным.

Например, нужно спроектировать индекс, покрывающий следующий запрос:

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode  
FROM Person.Address  
WHERE PostalCode BETWEEN N'98000' and N'99999';  

Для покрытия запроса необходимо включить в индекс все его столбцы. Хотя можно определить все столбцы как ключевые, размер ключа составит 334 байт. Поскольку в качестве критерия поиска реально используется только столбец PostalCode , имеющий длину 30 байт, более эффективный индекс определит PostalCode в качестве ключевого столбца, а все остальные столбцы включит как неключевые.

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

CREATE INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
Вопросы производительности

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

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

  • Увеличение места на диске, требуемого для хранения индекса. В частности, добавление типов данных varchar(max) , nvarchar(max) , varbinary(max) или xml в качестве неключевых столбцов индекса может значительно повысить требования к месту на диске. Это обусловлено тем, что значения столбцов копируются на конечный уровень индекса. Поэтому они находятся и в индексе, и в базовой таблице.

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

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

Правила по созданию уникальных индексов

Уникальный индекс гарантирует, что ключ индекса не будет содержать одинаковых значений, а значит, каждая строка в таблице будет уникальна. Создание уникального индекса имеет смысл, только если данные сами по себе могут быть уникальными. Например, если требуется обеспечить уникальность значений в столбце NationalIDNumber из таблицы HumanResources.Employee , когда первичным ключом является EmployeeID, создайте ограничение UNIQUE для столбца NationalIDNumber . Если пользователь попытается ввести в этот столбец одно и тоже значение для нескольких сотрудников, появится сообщение об ошибке, а дублирующие данные не будут сохранены.

В случае уникальных индексов по нескольким столбцам индекс гарантирует, что каждая комбинация значений в ключе индекса уникальна. Например, если уникальный индекс создан для комбинации столбцов LastName, FirstNameи MiddleName , то никакие две строки в таблице не могут образовывать одну и ту же комбинацию этих значений.

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

Уникальные индексы имеют следующие преимущества:

  • Гарантируется целостность данных в определенных столбцах.

  • Предоставляются дополнительные сведения, полезные оптимизатору запросов.

Создание ограничений PRIMARY KEY или UNIQUE автоматически создает уникальный индекс для заданных столбцов. Между созданием ограничения UNIQUE и созданием уникального индекса независимо от ограничения больших различий нет. Проверка данных происходит подобным же образом, и оптимизатор запросов не делает различия между уникальным индексом, который создан ограничением, и индексом, созданным вручную. Однако на столбцы, для которых важна целостность данных, следует накладывать ограничение UNIQUE или PRIMARY KEY. Тогда цель создания индекса будет ясна.

Рекомендации

  • Уникальный индекс и ограничения UNIQUE и PRIMARY KEY не могут быть созданы, если дублирующиеся значения уже существуют.

  • Если данные уникальны и если нужно и далее требовать этой уникальности, создание уникального индекса вместо неуникального для тех же сочетаний столбцов предоставит дополнительные сведения оптимизатору запросов, который может создать более эффективные планы выполнения. В этом случае рекомендуется создание уникального индекса (предпочтительнее через ограничение UNIQUE).

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

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

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

Применимо к: с SQL Server 2008 до SQL Server 2019 (15.x).

Отфильтрованные индексы могут предоставить следующие преимущества по сравнению с индексами, построенными на всей таблице.

  • Улучшение производительности запроса и качества плана

    Хорошо спроектированный отфильтрованный индекс повышает производительность запроса и качество плана выполнения, поскольку он меньше, чем полнотабличный некластеризованный индекс, и содержит отфильтрованную статистику. Отфильтрованная статистика точнее полнотабличной статистики, так как содержит только строки отфильтрованного индекса.

  • Снижение расходов на обслуживание индекса

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

  • Снижение затрат на хранение индекса

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

Отфильтрованные индексы полезны, когда столбцы содержат хорошо определенные подмножества данных, указанных в инструкциях SELECT. Примеры:

  • Разреженные столбцы, содержащие небольшое количество значений, отличных от NULL.

  • Разнородные столбцы, содержащие категории данных.

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

  • Секции таблицы, определенные логикой простого сравнения для значений столбцов.

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

Отфильтрованные индексы определены в одной таблице и поддерживают только простые операторы сравнения. Если необходим критерий фильтра, который ссылается на множество таблиц или имеет сложную логику, нужно создать представление.

Вопросы проектирования

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

Совет

Определение некластеризованного индекса columnstore поддерживает использование отфильтрованных условий. Чтобы свести к минимуму негативное влияние на производительность, вызванное добавлением некластеризованного индекса columnstore для таблицы OLTP, можно использовать отфильтрованное условие для создания некластеризованного индекса columnstore только для холодных данных операционной рабочей нагрузки.

Отфильтрованные индексы для подмножеств данных

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

Например, база данных AdventureWorks2012 содержит таблицу Production.BillOfMaterials с 2679 строками. Столбец EndDate содержит только 199 строк, содержащих значения, отличные от NULL, и 2 480 строк, содержащих значение NULL. Следующий отфильтрованный индекс может перекрывать запросы, которые возвращают столбцы, указанные в индексе, и выбирают только строки, содержащие значения EndDate, отличные от NULL.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
    ON Production.BillOfMaterials (ComponentID, StartDate)  
    WHERE EndDate IS NOT NULL ;  
GO  

Отфильтрованный индекс FIBillOfMaterialsWithEndDate допустим для следующего запроса. Можно отобразить план выполнения запроса для проверки того, использует ли оптимизатор запросов отфильтрованный индекс.

SELECT ProductAssemblyID, ComponentID, StartDate   
FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL   
    AND ComponentID = 5   
    AND StartDate > '20080101' ;  

Дополнительные сведения о создании отфильтрованного индекса и об определении выражения предиката отфильтрованного индекса см. в разделе Create Filtered Indexes.

Отфильтрованные индексы для разнородных данных

Если таблица содержит строки с разнородными данными, можно создать отфильтрованный индекс для одной или более категорий данных.

Например, продукты, содержащиеся в таблице Production.Product , связаны с идентификатором ProductSubcategoryID, который в свою очередь связан с категориями продуктов, такими как велосипеды, запчасти, одежда или аксессуары. Эти категории являются разнородными, так как значения их столбцов в таблице Production.Product не являются близко коррелирующими. Например, столбцы Color, ReorderPoint, ListPrice, Weight, Classи Style имеют уникальные характеристики для каждой категории продукта. Предположим, что существуют частые запросы на аксессуары, имеющие подкатегории с 27 по 36 включительно. Можно повысить результативность запросов на аксессуары, создав отфильтрованный индекс по подкатегориям аксессуаров, как показано в следующем примере.

CREATE NONCLUSTERED INDEX FIProductAccessories  
    ON Production.Product (ProductSubcategoryID, ListPrice)   
        Include (Name)  
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;  

Отфильтрованный индекс FIProductAccessories включает следующий запрос, поскольку результаты

запроса содержатся в индексе, а план запроса не включает в себя поиск в базовых таблицах. Например, выражение предиката запроса ProductSubcategoryID = 33 — это подмножество предиката отфильтрованного индекса ProductSubcategoryID >= 27 и ProductSubcategoryID <= 36, а столбцы ProductSubcategoryID и ListPrice в предикате запроса являются ключевыми столбцами в индексе. Имя сохраняется на конечном уровне индекса в качестве включенного столбца.

SELECT Name, ProductSubcategoryID, ListPrice  
FROM Production.Product  
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;  

Ключевые столбцы

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

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

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

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;   

Столбец в выражении отфильтрованного индекса должен быть ключевым или включенным столбцом в определении отфильтрованного индекса, если предикат запроса использует в сравнении столбец, который не эквивалентен выражению отфильтрованного индекса. Например, отфильтрованный индекс FIBillOfMaterialsWithEndDate допустим для следующего запроса, поскольку этот запрос выбирает подмножество строк из отфильтрованного индекса. Однако он не перекрывает следующий запрос, поскольку столбец EndDate используется в сравнении EndDate > '20040101', не эквивалентном выражению отфильтрованного индекса. Обработчик запросов не может выполнить запрос без поиска значений EndDate. Поэтому в определении отфильтрованного индекса EndDate должен быть ключевым или включенным столбцом.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate > '20040101';   

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

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;  

Ключ кластеризованного индекса таблицы необязательно должен быть ключевым или включенным столбцом в определении отфильтрованного индекса. Ключ кластеризованного индекса автоматически включается во все некластеризованные индексы, в том числе отфильтрованные индексы.

Операторы преобразования данных в предикате фильтра

Если оператор сравнения определен в выражении отфильтрованного индекса результатов отфильтрованного индекса в неявном или явном преобразовании данных, произойдет ошибка, если преобразование выполняется в левой части оператора сравнения. Решением является применение выражения отфильтрованного индекса с оператором преобразования данных (CAST или CONVERT) в правой части оператора сравнения.

В следующем примере создается таблица с различными типами данных.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.TestTable (a int, b varbinary(4));  

В следующем определении отфильтрованного индекса столбец b неявно преобразуется в тип данных integer для сравнения с константой 1. Это вызывает сообщение об ошибке 10611, поскольку преобразование выполняется в левой части оператора в отфильтрованном предикате.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = 1;  

Решением является преобразование константы в правой части к типу столбца b, как показано в следующем примере.

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = CONVERT(Varbinary(4), 1);  

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

Рекомендации по проектированию индексов columnstore

columnstore index — это технология хранения, получения данных и управления ими с помощью формата хранения данных в один столбец, называемого columnstore. Дополнительные сведения см. в статье Общие сведения об индексах columnstore.

Сведения о версиях см. в разделе Новые возможности индексов columnstore.

Архитектура индексов columnstore

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

Данные хранятся в форматах columnstore и rowstore

При обсуждении индексов columnstore для обозначения формата хранения данных используются термины rowstore и columnstore. Индексы columnstore используют оба типа хранилища.

Кластеризованный индекс columnstore

  • columnstore — это данные, логически организованные в виде таблицы, состоящей из строк и столбцов, и физически хранящиеся как столбцы

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

  • rowstore — это данные, логически организованные в виде таблицы, состоящей из строк и столбцов, и физически хранящиеся как строки. Это стандартный способ хранения реляционных данных таблиц Это стандартный способ хранения реляционных данных таблиц, например индекса кучи или кластеризованного индекса сбалансированного дерева.

    Индекс columnstore также физически сохраняет некоторые строки в формате rowstore, который называется deltastore. deltastore также называют разностными группами строк. Это место хранения строк, которых слишком мало для сжатия в columnstore. Каждая разностная группа строк реализована в виде кластеризованного индекса сбалансированного дерева.

  • deltastore — это место хранения строк, которых слишком мало для сжатия в columnstore. В deltastore таблица хранится в формате rowstore.

См. сведения в статье Общие сведения об индексах columnstore.

Операции выполняются в сегментах групп строк и столбцов

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

В группах строк индекс columnstore может выполнять следующие операции:

  • сжимает группы строк в columnstore (выполняется в каждом сегменте столбца в группе строк);
  • объединяет группы строк во время операции ALTER INDEX ... REORGANIZE с очисткой удаленных данных.
  • создает новые группы строк во время операции ALTER INDEX ... REBUILD;
  • отправляет отчеты об исправности и фрагментации групп строк в динамических административных представлениях.

deltastore состоит из одной или нескольких групп строк, которые называются разностными группами строк. Каждая разностная группа строк — это кластеризованный индекс сбалансированного дерева, в котором хранятся небольшие массовые загрузки и вставки, пока группа строк не будет содержать 1 048 576 строк. При этом процесс, который называется задачей переноса кортежей, автоматически сжимает закрытую группу строк в columnstore.

См. сведения о состояниях групп строк в статье sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Совет

Слишком большое количество небольших групп строк ухудшает качество индекса columnstore. Операция реорганизации объединяет небольшие группы строк в соответствии с внутренней политикой пороговых значений, которая определяет способ очистки удаленных строк и объединения сжатых групп строк. После объединения качество индекса должно повыситься.

Примечание

Начиная с SQL Server 2019 (15.x), задача переноса кортежей выполняется вместе с задачей фонового объединения. Последняя автоматически сжимает небольшие разностные группы строк с состоянием OPEN, которые существовали некоторое время в соответствии с внутренним пороговым значением, или объединяет группы строк с состоянием COMPRESSED, из которых было удалено большое количество строк.

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

Сегмент столбца

Когда индекс columnstore сжимает группу строк, он отдельно сжимает каждый сегмент столбца. Чтобы распаковать целый столбец, индексу columnstore необходимо распаковать только один сегмент столбца из каждой группы строк.

См. сведения в статье Общие сведения об индексах columnstore.

Небольшие загрузки и вставки переносятся в deltastore

Индекс columnstore улучшает сжатие и производительность columnstore за счет сжатия как минимум 102 400 строк в индекс columnstore за раз. Чтобы выполнить массовое сжатие строк, индекс columnstore накапливает небольшие загрузки и вставки в deltastore. Операции deltastore обрабатываются в фоновом режиме. Для получения правильных результатов запросов кластеризованные индексы columnstore объединяют результаты запроса от columnstore и deltastore.

Переход строк в deltastore происходит в следующих случаях:

  • если они вставляются с помощью инструкции INSERT INTO ... VALUES;
  • если по завершении массовой загрузки они насчитывают меньше 102 400 строк;
  • в случае обновления (каждое обновление реализуется как операция удаления или вставки).

В deltastore также хранится список идентификаторов для удаленных строк, которые были помечены как удаленные, но еще не удалены физически из columnstore.

См. сведения в статье Общие сведения об индексах columnstore.

Когда разностные группы строк заполнены, они сжимаются в columnstore

Прежде чем сжать группу строк в columnstore, кластеризованные индексы собирают 1 048 576 строк в каждой разностной группе строк. Это повышает степень сжатия индекса columnstore. Когда количество строк в разностной группе строк достигает максимального значения, состояние группы меняется с OPEN на CLOSED. Фоновый процесс, который называется задачей переноса кортежей, проверяет наличие закрытых групп строк. При обнаружении закрытой группы строк она сжимается и сохраняется в columnstore.

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

См. сведения о состояниях групп строк в статье sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Чтобы перестроить или реорганизовать индекс, вы можете принудительно сжать разностную группу строк в columnstore с помощью ALTER INDEX. Обратите внимание, что если во время сжатия наблюдается нехватка памяти, индекс columnstore может уменьшить число строк в сжимаемой группе строк.

См. сведения в статье Общие сведения об индексах columnstore.

Каждая секция таблицы содержит собственные группы строк и разностные группы

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

Группы строк всегда определяются в пределах секции таблицы. При секционировании индекса columnstore каждая секция получает свои собственные сжатые группы строк и разностные группы строк.

Совет

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

Каждая секция может содержать несколько разностных групп строк

Каждая секция может содержать несколько разностных групп строк. Когда индексу columnstore требуется добавить данные в разностную группу строк, которая заблокирована, он попытается получить блокировку в другой разностной группе строк. Если доступные разностные группы строк отсутствуют, индекс columnstore создаст новую группу. Например, у таблицы с 10 секциями может быть 20 и более разностных групп строк.

Вы можете объединить индексы columnstore и rowstore в одной таблице

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

Начиная с версии SQL Server 2016 (13.x);, можно создавать обновляемый некластеризованный индекс columnstore в таблице rowstore. Индекс columnstore сохраняет копию данных, так что вам обязательно потребуется дополнительное хранилище. Тем не менее данные в индексе columnstore будут сжаты до меньшего размера, чем это требуется для таблицы rowstore. Таким образом, вы сможете выполнять аналитику на основе индекса columnstore и транзакции на основе индекса rowstore одновременно. Хранилище столбцов обновляется при каждом изменении данных в таблице rowstore, поэтому оба индекса работают с одними и теми же данными.

Начиная с версии SQL Server 2016 (13.x);, индекс columnstore может включать один или несколько некластеризованных индексов rowstore. Это обеспечивает эффективность поиска по таблицам на основе базового индекса columnstore. Кроме того, появляется доступ к другим возможностям. Например, можно принудительно задать ограничение PRIMARY KEY, применив к таблице rowstore ограничение UNIQUE. Поскольку неуникальное значение в таблицу rowstore не вставляется, SQL Server не может вставить значение в columnstore.

Вопросы производительности

  • Определение некластеризованного индекса columnstore поддерживает использование отфильтрованных условий. Чтобы свести к минимуму негативное влияние на производительность, вызванное добавлением некластеризованного индекса columnstore для таблицы OLTP, можно использовать отфильтрованное условие для создания некластеризованного индекса columnstore только для холодных данных операционной рабочей нагрузки.

  • Таблица в памяти может включать один индекс columnstore. Его можно создать при создании таблицы или добавить позже с помощью процедуры ALTER TABLE (Transact-SQL). До версии SQL Server 2016 (13.x); создание индекса columnstore допускалось только в таблицах на дисках.

Дополнительные сведения см. в статье Производительность запросов по индексам columnstore.

Руководство по проектированию

  • Таблица rowstore может включать один обновляемый некластеризованный индекс columnstore. До версии SQL Server 2014 (12.x) некластеризованный индекс columnstore был доступен только для чтения.

Дополнительные сведения см. в статье Руководство по проектированию индексов columnstore.

Рекомендации по проектированию хэш-индексов

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

Применимо к: с SQL Server 2014 (12.x) до SQL Server 2019 (15.x).

Архитектура хэш-индекса

Хэш-индекс состоит из массива указателей. Каждый элемент массива называется хэш-контейнером.

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

При определении индекса необходимо указать число контейнеров.

  • Чем меньше отношение количества контейнеров к количеству строк или уникальных значений, тем длиннее будет средний список ссылок на контейнеры.
  • Короткие списки обладают большим быстродействием по сравнению с длинными.
  • Максимальное число контейнеров в хэш-индексах составляет 1 073 741 824.

Совет

Чтобы определить подходящий BUCKET_COUNT для ваших данных, см. раздел Настройка числа контейнеров хэш-индекса.

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

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

  • SQL Server имеет одну хэш-функцию, используемую для всех хэш-индексов.
  • Хэш-функция является детерминированной. Одно значение входного ключа всегда связано с одним контейнером в хэш-индексе.
  • Несколько ключей индекса могут быть сопоставлены с одним и тем же хэш-контейнером индекса.
  • Хэш-функция сбалансирована, а это означает, что распределение значений ключей индекса, связанных с хэш-контейнерами, соответствует распределению Пуассона или нормальному распределению, а не плоскому линейному распределению.
  • Распределение Пуассона не является равномерным. Значения ключа индекса не распределяются в хэш-контейнерах равномерно.
  • Если два ключа индекса сопоставляются с одним хэш-контейнером, происходит хэш-конфликт. Большое число конфликтов хэша может оказывать негативное влияние на операции чтения. Реалистичная цель — сделать так, чтобы 30 % контейнеров содержали два различных значения ключа.

Взаимозависимость хэш-индекса и контейнеров иллюстрируется на следующем рисунке.

hekaton_tables_23d

Настройка числа контейнеров хэш-индекса

Число контейнеров хэш-индекса указывается в момент создания индекса и может быть изменено с помощью синтаксиса ALTER TABLE...ALTER INDEX REBUILD.

В большинстве случаев идеальное число контейнеров должно находиться в диапазоне, в 1–2 раза превышающем число уникальных значений в ключе индекса.
Не всегда можно спрогнозировать количество значений, которые содержит конкретный ключ индекса. Обычно производительность остается хорошей, если значение BUCKET_COUNT входит в диапазон значений, в 10 раз превышающих фактическое число ключевых значений, и, как правило, его лучше переоценить, чем недооценить.

Слишком малое количество контейнеров имеет следующие недостатки.

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

Слишком большое количество контейнеров имеет следующие недостатки.

  • Слишком большое число контейнеров может привести к увеличению количества пустых контейнеров.
  • Пустые контейнеры влияют на скорость полного сканирования индексов. Если сканирования выполняются регулярно, можно выбрать число контейнеров, близкое к числу уникальных значений ключа индекса.
  • Пустые контейнеры задействуют память, хотя каждый контейнер использует всего 8 байт.

Примечание

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

Вопросы производительности

Производительность хэш-индекса обладает следующими характеристиками.

  • Отличная, если предикат в предложении WHERE задает точное значение каждого столбца в ключе хэш-индекса. Хэш-индекс вернется к сканированию данного предиката неравенства.
  • Низкая, если в предикате в предложении WHERE указан диапазон значений ключа индекса.
  • Низкая, если в предикате в предложении WHERE указано одно конкретное значение для первого столбца ключа хэш-индекса, состоящего из двух столбцов, но не указано значение для других столбцов ключа.

Совет

Предикат должен содержать все столбцы в ключе хэш-индекса. Для хэш-индекса требуется ключ (для хэша) для поиска в индексе. Если ключ индекса состоит из двух столбцов, а в предложении WHERE предоставлен только первый столбец, у SQL Server не будет полного ключа для хэширования. Это приведет к получению плана запроса сканирования индекса.

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

Вопросы, связанные с обновлением

Хэш-индекс может существовать только для таблицы, оптимизированной для памяти. Он не может существовать для таблицы на диске.

Хэш-индекс можно объявить как:

  • УНИКАЛЬНЫЙ или неуникальный по умолчанию.
  • НЕКЛАСТЕРИЗОВАННЫЙ, который используется по умолчанию.

Ниже приведен пример синтаксиса для создания хэш-индекса за пределами инструкции CREATE TABLE.

ALTER TABLE MyTable_memop  
ADD INDEX ix_hash_Column2 UNIQUE  
HASH (Column2) WITH (BUCKET_COUNT = 64);

Версии строк и сборка мусора

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

Кроме того, хэш-индекс может иметь различные версии записей для отражения обновления.

Позже, когда старые версии больше не требуются, поток сборки мусора перебирает список контейнеров и их списки ссылок, удаляя старые записи. Поток сборки мусора работает быстрее, если списки ссылок короткие. Дополнительные сведения см. в статье Сборка мусора модулем In-Memory OLTP.

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

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

Применимо к: с SQL Server 2014 (12.x) до SQL Server 2019 (15.x).

Архитектура некластеризованного индекса в памяти

Некластеризованные индексы в памяти реализуются с помощью структуры данных, которая называется BW-деревом. Она была изобретена и описана подразделением Microsoft Research в 2011 году. BW-дерево является разновидностью сбалансированного дерева без блокировок и кратковременных блокировок. См. дополнительные сведения о сбалансированном дереве для новых аппаратных платформ.

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

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

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

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

  • Для изменения страницы блокировка или кратковременная блокировка не требуется.
  • Страницы индекса не имеют фиксированного размера.

Значение ключа на каждой странице не конечного уровня — это максимальное значение, которое содержится на ее дочерней странице. Каждая строка также содержит идентификатор этой логической страницы. Страница конечного уровня, помимо значения ключа, содержит физический адрес строки данных.

Поиск точек выполняется так же, как в сбалансированных деревьях. Единственным отличием является то, что, поскольку страницы связаны только в одном направлении, Компонент SQL Server Database Engine следует по правым указателям на страницы, где каждая конечная страница имеет максимальное значение своей дочерней страницы, а не минимальное, как в случае со сбалансированным деревом.

Если необходимо изменить страницу конечного уровня, Компонент SQL Server Database Engine не изменяет саму страницу. Вместо этого Компонент SQL Server Database Engine создает разностную запись с описанием изменения и добавляет ее к прежней странице. Затем оно также меняет адрес этой прежней страницы в таблице сопоставления страниц на адрес разностной записи, который теперь становится физическим адресом страницы.

Для управления структурой BW-дерева могут требоваться три разных операции: консолидация, разделение и слияние.

Консолидация разностных записей

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

hekaton_tables_23e

Разделение страницы

Страница индекса в BW-дереве растет по мере необходимости, начиная от одной строки и заканчивая максимальным размером, равным 8 КБ. Если размер страницы индекса достиг 8 КБ, вставка еще одной строки приведет к разделению страницы. Для внутренней страницы это означает, что больше нет места для добавления еще одного значения ключа и указателя, а для конечной страницы — что после включения всех разностных записей строка не будет помещаться на странице. С помощью статистических данных в заголовке конечной страницы отслеживается необходимое место для консолидации разностных записей, и эти данные корректируются после добавления каждой новой разностной записи.

Операция разделения производится в два отдельных этапа. На приведенном ниже рисунке предполагается, что конечная страница инициирует разделение, так как вставляется ключ со значением 5 и существует неконечная страница, указывающая на конец текущей страницы конечного уровня (значение ключа 4).

hekaton_tables_23f

Шаг 1. Выделяются две новые страницы (P1 и P2), и строки со старой страницы P1, включая вставляемую строку, разделяются между этими новыми страницами. Для хранения физического адреса страницы P2 используется новый слот в таблице сопоставления страниц. Страницы P1 и P2 пока не доступны для параллельных операций. Кроме того, задается логический указатель со страницы P1 на P2. Затем в рамках одного этапа указатель со старой страницы P1 в таблице сопоставления страниц меняется на указатель с новой страницы P1.

Шаг 2. Неконечная страница указывает на P1, но прямого указателя с неконечной страницы на P2 нет. Страница P2 доступна только через P1. Чтобы создать указатель с неконечной страницы на P2, выделите новую неконечную страницу (внутреннюю страницу индекса), скопируйте все строки со старой неконечной страницы и добавьте новую строку, указывающую на P2. После этого в рамках одного этапа указатель со старой неконечной страницы в таблице сопоставления страниц меняется на указатель с новой неконечной страницы.

Слияние страниц

Если в результате операции DELETE размер страницы составляет меньше 10 % максимального размера (в настоящее время 8 КБ) или на странице остается одна строка, эта страница объединяется со смежной страницей.

При удалении строки со страницы добавляется разностная запись для операции удаления. Кроме того, проводится проверка того, подлежит ли страница индекса (неконечная) слиянию. При этом проверяется, составляет ли оставшееся место после удаления строки менее 10 % от максимального размера страницы. Если это условие выполняется, слияние производится в три отдельных этапа.

На приведенном ниже рисунке предполагается, что операция DELETE удаляет значение ключа 10.

hekaton_tables_23g

Шаг 1. Создается разностная страница, представляющая значение ключа 10 (синий треугольник), а указатель этого значения на неконечной странице Pp1 устанавливается на новую разностную страницу. Кроме того, создается специальная разностная страница слияния (зеленый треугольник), которая связывается с разностной страницей. На этом этапе обе страницы (разностная страница и разностная страница слияния) недоступны для параллельных транзакций. В рамках этого же этапа указатель на страницу конечного уровня P1 в таблице сопоставления страниц обновляется так, чтобы теперь он указывал на разностную страницу слияния. После этого этапа запись значения ключа 10 на странице Pp1 будет указывать на разностную страницу слияния.

Шаг 2. Необходимо удалить строку, представляющую значение ключа 10 на неконечной странице Pp1, и обновить запись для значения ключа 10 так, чтобы она указывала на страницу P1. Для этого выделяется новая неконечная страница Pp2 и копируются все строки со страницы Pp1, кроме строки, представляющей значение ключа 7. Затем строка значения ключа 10 обновляется так, чтобы теперь она указывала на страницу P1. После этого в рамках того же этапа запись, указывающая на Pp1, в таблице сопоставления страниц обновляется так, чтобы теперь она указывала на Pp2. Страница Pp1 больше не доступна.

Шаг 3. Страницы конечного уровня P2 и P1 сливаются, а разностные страницы удаляются. Для этого выделяется новая страница P3, сливаются строки со страниц P2 и P1, а изменения разностной страницы включаются в новую страницу P3. После этого в рамках того же этапа запись, указывающая на P1, в таблице сопоставления страниц обновляется так, чтобы теперь она указывала на страницу P3.

Вопросы производительности

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

Примечание

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

Совет

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

Дополнительные материалы

CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL)
Реорганизация и перестроение индексов
Повышение производительности с помощью индексированных представлений SQL Server 2008
Partitioned Tables and Indexes
Создание первичного ключа
Индексы для оптимизированных для памяти таблиц
Общие сведения об индексах columnstore
Устранение неполадок хэш-индексов для оптимизированных для памяти таблиц
Оптимизированные для памяти динамические административные представления таблиц (Transact-SQL)
Динамические административные представления и функции, связанные с индексами (Transact-SQL)
Индексы вычисляемых столбцов
Индексы и инструкция ALTER TABLE
Адаптивная дефрагментация индексов