Стратегии индексирования для производительности SQL Server

Автор: Paul Randal, SQL101: Indexing Strategies for SQL Server Performance

Один из самых простых способов повысить производительность запросов в SQL Server — обеспечить быстрый доступ к запрашиваемым данным, причём максимально эффективно. В SQL Server использование одного или нескольких индексов может стать именно тем решением, которое вам нужно. Фактически, индексы настолько важны, что SQL Server может даже предупредить вас, когда обнаружит, что отсутствует индекс, который был бы полезен для запроса. Это обзорная статья объяснит, что такое индексы, почему они так важны, а также немного об искусстве и науке разработки различных стратегий индексирования.

Что такое индексы?

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

Таблица без кластерного индекса называется кучей (heap), и строки данных в такой таблице не упорядочены. Если в куче нет индексов, то поиск определённого значения данных в таблице требует чтения всех строк данных в таблице (это называется просмотром таблицы — table scan). Очевидно, что это очень неэффективно, и неэффективность растёт по мере увеличения размера таблицы.

Кластерный индекс на таблице упорядочивает все строки данных в таблице в отсортированном порядке и размещает навигационное «дерево» вместе с организованными данными, чтобы по нему было легко перемещаться. Таблица перестаёт быть кучей; она становится кластеризованной таблицей. Порядок определяется ключом кластерного индекса, который состоит из одного или нескольких столбцов таблицы. Структура кластерного индекса известна как B-дерево, и эта базовая структура данных позволяет найти конкретную строку данных (это называется «поиском» — seek) на основе значения ключа кластерного индекса без необходимости сканировать всю таблицу.

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

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

Продолжая пример с таблицей сотрудников: если кто-то хочет найти данные конкретного сотрудника, зная только его имя, можно было бы создать некластерный индекс с составным ключом из столбцов LastName, FirstName и MiddleInitial. Это позволило бы найти идентификатор сотрудника (Employee ID), а затем извлечь все данные сотрудника из соответствующей строки данных в кластерном индексе.

Почему индексы так важны?

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

• Минимальный объём данных должен быть прочитан с диска. Это предотвращает излишнюю нагрузку на подсистему ввода-вывода со стороны множества запросов, читающих неэффективные или большие объёмы данных, и помогает предотвратить «переполнение» буферного пула (in-memory кэша страниц файлов данных), не вытесняя из памяти данные, которые уже там находятся, чтобы освободить место для данных, читаемых с диска. В некоторых случаях данные вообще не нужно будет читать с диска, если требуемые данные уже находятся в памяти.
• Минимальный объём данных должен занимать место в буферном пуле. Это означает, что большая часть «рабочего набора» рабочей нагрузки может храниться в памяти, что ещё больше снижает потребность в физических чтениях.
• Любое уменьшение количества физических чтений, которые должен выполнить запрос, приведёт к сокращению времени выполнения.
• Любое уменьшение объёма данных, проходящих через план запроса, приведёт к сокращению времени выполнения.

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

• Использование правильных условий соединения.
• Использование поисковых аргументов (search arguments) для дальнейшего сужения объёма требуемых данных.
• Избегание методов кодирования, которые вынуждают использовать просмотр таблицы, например, случайное создание неявных преобразований типов.
• Обеспечение правильного обновления статистики, чтобы оптимизатор запросов мог выбрать наилучшие стратегии обработки и индексы.
• Учёт метода выполнения запроса, когда используется кэшированный план, что приводит к проблемам чувствительности к параметрам (parameter sensitivity).

Но это всё темы для будущих постов!

Искусство и наука индексирования

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

Выбор ключа кластерного индекса — это скорее наука, чем искусство, и это отдельная тема для обсуждения, но мы обычно говорим, что ключ кластерного индекса должен обладать несколькими свойствами (в произвольном порядке и только как сильно урезанная отправная точка в понимании принципов выбора ключа):

• Узкий (Narrow). Ключ кластерного индекса — это указатель на строку данных, который включается в каждую строку индекса каждого некластерного индекса. Это означает, что чем он уже, тем меньше места он будет занимать в целом, что поможет с размером данных.
• Фиксированной ширины (Fixed-width). Ключ кластерного индекса должен быть узким, но также использовать тип данных фиксированной ширины. При использовании типа данных переменной ширины строка данных и все строки некластерных индексов будут нести дополнительные накладные расходы.
• Уникальный (Unique). Если ключ кластерного индекса не уникален, то для всех неуникальных строк данных к ключу кластерного индекса добавляется специальный скрытый столбец «уникализатор» (uniquifier), делающий ключ кластерного индекса для этих строк длиннее на четыре байта.
• Статичный (Static). Если значение ключа кластерного индекса изменяется, строка данных должна быть внутренне удалена и вставлена заново, и все записи некластерных индексов, содержащие этот указатель на строку данных, должны быть обновлены.
• Постоянно возрастающий (Ever-increasing). Это свойство помогает предотвратить фрагментацию кластерного индекса.
• Не допускающий NULL (Non-nullable). Ключ кластерного индекса должен быть уникальным по определению (см. пункт 3 выше), что подразумевает, что он не может допускать значения NULL. В некоторых версиях SQL Server и в некоторых структурах столбец, допускающий NULL, влечёт за собой больше накладных расходов, чем не допускающий NULL. В идеале ни один из столбцов, составляющих ключ кластерного индекса, не должен допускать значения NULL.

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

Наука построения наилучшего некластерного индекса для запроса включает:

• Понимание используемых поисковых аргументов и типа запроса (так как существуют различные стратегии индексирования, например, когда поисковые аргументы используют предложения AND или OR, когда задействованы агрегаты и для различных типов соединений). Поисковые аргументы — это, по сути, столбцы таблицы, необходимые для идентификации требуемых строк данных. Они, вероятно, будут частью ключей некластерного индекса.
• Понимание «селективности» (selectivity) данных в каждом из этих ключевых столбцов. Это определит порядок столбцов в ключе индекса, при этом наиболее селективные предикаты будут возглавлять определение ключа.
• Понимание списка SELECT для запроса. Любые из этих столбцов могут быть кандидатами на включение в индекс в качестве неключевых столбцов, чтобы избежать необходимости запроса обращаться к строке данных для их получения (это также называется «покрытием» запроса).

А также существует функциональность SQL Server «отсутствующие индексы» (missing indexes), которая порекомендует наилучший индекс для запроса (она фокусируется только на науке «настройки запросов», но не на искусстве «настройки сервера»).

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

В качестве простого примера предположим, что в таблице есть десять целочисленных столбцов с именами col1 по col10.

Первый запрос для индексирования: SELECT col2, col3 FROM table WHERE col6 = value. Некластерный индекс по col6 позволил бы избежать просмотра таблицы, но потребовал бы от запроса обратиться к строке данных, чтобы получить значения col2 и col3. Более эффективным некластерным индексом был бы индекс с ключом col6, включающий col2 и col3 в качестве неключевых столбцов. Это называется покрывающим индексом (covering index), потому что строка индекса содержит все столбцы, необходимые для запроса, и устраняет необходимость также использовать кластерный индекс для получения дополнительно запрошенных столбцов.

Второй запрос для индексирования: SELECT col4 FROM table WHERE col6 = value. Наука говорит нам, что некластерный индекс по col6, включающий col4, вероятно, будет наилучшим индексом для этого запроса. Но тогда у нас есть два некластерных индекса с ключом по col6, каждый из которых включает разные неключевые столбцы. Здесь вступает в игру искусство, так как наилучшим индексом для общей рабочей нагрузки, скорее всего, будет один некластерный индекс по col6, включающий col2, col3 и col4. Теперь у вас есть один индекс с большим количеством применений и меньше индексов на таблице в целом.

И искусство может продолжаться в несколько итераций.

Допустим, появился третий запрос: SELECT col4, col5 from table where col6 = value AND col2 = value. Наука может сказать, что наилучшим некластерным индексом будет индекс по (col6, col2), если col6 более селективен, чем col2, с включением col4 и col5 в качестве неключевых столбцов. Затем искусство заставляет нас рассмотреть консолидацию и в итоге получить один некластерный индекс по (col6, col2), включающий col3, col4 и col5. Это удовлетворяет все три запроса с помощью одного некластерного индекса вместо трёх, поэтому в целом он занимает меньше места ценой меньшей эффективности для каждого отдельного запроса по сравнению с индивидуальными «идеальными» некластерными индексами. Однако у такой консолидации есть дополнительное преимущество: чем меньше некластерных индексов, тем меньше операций по обслуживанию индексов необходимо выполнять при вставке, удалении или обновлении строки данных.

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

Резюме

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