Поделиться через

  • Статья

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

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

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

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

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

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

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

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

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

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

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

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

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

    Важное примечаниеВажно!

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

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

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

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

Соображения, связанные со столбцами

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

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

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

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

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

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

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

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

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

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

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

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

  • кластеризованный или некластеризованный;

  • уникальный или неуникальный;

  • с одним или несколькими столбцами;

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

  • полнотабличные или фильтруемые некластеризованные индексы.

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