Проектирование индексов для оптимизации запросов в Microsoft SQL Server

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

Ранее в материале «Основы индексов в Microsoft SQL Server» мы с Вами узнали, что же такое индексы и какие они бывают, а сейчас мы с Вами научимся правильно проектировать эти индексы, чтобы достичь максимальной производительности SQL запросов.

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

• Одним из самых эффективных индексов является индекс для целочисленных столбцов, которые имеют уникальные значения, поэтому по возможности создавайте индексы для таких столбцов;
• Если таблица очень интенсивно обновляется, то не рекомендуется создавать большое количество индексов, так как это снижает производительность инструкций INSERT, UPDATE, DELETE и MERGE. Потому что после изменений данных в таблице SQL сервер автоматически вносит соответствующие изменения во все индексы;
• Если таблица с большим объемом данных обновляется редко, при этом она активно используется в инструкциях SELECT, т.е. на выборку данных, то большое количество индексов может улучшить производительность, так как у оптимизатора запросов будет больший выбор индексов при определении наиболее эффективного и быстрого способа доступа к данным;
• Если создавать некластеризованный индекс в файловой группе, которая расположена не на том диске, на котором расположены файловые группы таблицы, то это может повысить производительность для больших таблиц и индексов, так как это позволяет одновременно обращаться к нескольким дискам;
• Для таблиц с небольшим объемом данных создание индексов в частности некластеризованных индексов с целью повышения производительности может оказаться абсолютно бесполезно, да еще и затратами на их поддержание. Так как оптимизатору может потребоваться больше времени на поиск данных в индексе, чем просмотр данных в самой таблице. Поэтому не создавайте индексы для таблиц, в которых очень мало данных;
• Кластеризованный индекс необходимо создавать для столбца, который является уникальным и не принимает значения NULL, также длина ключа должна быть небольшой, другими словами ключ индекса не нужно составлять из нескольких столбцов;
• Если представление содержит агрегаты и объединения таблиц, то индексы для таких представлений могут дать неплохое улучшение производительности.

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

• Некластеризованные индексы необходимо создавать для всех столбцов, которые часто используются в условиях (WHERE) и в объединениях (JOIN);
• По возможности не стоит создавать индексы, в которых очень много ключевых столбцов, так как это влияет на размер индекса и на ресурсы его поддержания;
• Эффективно использовать покрывающие индексы, т.е. индексы которые включают все столбцы, используемые в запросе (это называется «Покрытием запроса»). Благодаря этому оптимизатор запросов может найти все значения столбцов в индексе, при этом не обращаясь к данным таблиц, что приводит к меньшему числу дисковых операций ввода-вывода. Это можно достичь с помощью включения в индекс неключевых столбцов (включенные столбцы), но также следует принять во внимание, что это влечет за собой увеличение размера индекса;
• Если есть возможность, то рекомендовано заменять неуникальный индекс уникальным для той же комбинации столбцов, это обеспечивает оптимизатору запросов дополнительные сведения, что может сделать индекс более эффективным;
• При создании индекса учитывайте порядок ключевых столбцов, это повышает производительность индекса. Например, столбцы, которые используются в предложении WHERE в условиях поиска равно (=), больше (>), меньше (<) или находящихся в интервале (BETWEEN) или участвуют в соединении (JOIN), должны стоять первыми. Если таких несколько, то упорядочивайте их по уровню различности, т.е. от наиболее четкого к наименее четкому;
• Попробуйте применить отфильтрованные индексы для столбцов, имеющих точно определенные подмножества, так как в некоторых случаях такие индексы могут увеличить скорость выполнения запроса по сравнению с обычными (полнотабличными) индексами;
• Также рекомендуется проектировать запросы на изменение данных так, чтобы они вставляли или изменяли как можно больше строк одной инструкцией, т.е. не используйте для тех же операций несколько запросов.

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

На этом у меня все, надеюсь, материал был Вам полезен и интересен, пока!