SQL Server: использование индексов при настройке производительности

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

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

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

Полезные индексы

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

Например, запросы к базе данных Northwind, приведенные ниже, будут выполняться более эффективно при построении индекса по столбцу UnitPrice.

Поскольку элементы индекса хранятся отсортированными, индексирование также оказывается полезным при построении запроса с использованием инструкции Order by. Без индекса записи загружаются и сортируются во время выполнения запроса. Индекс по UnitPrice позволит при обработке следующего запроса просто просканировать индекс и извлечь строки по ссылке. Если требуется упорядочить строки по убыванию, достаточно будет просто просканировать индекс в обратном порядке.

Группировка записи с использованием инструкции Group by также зачастую требует сортировки, таким образом, построение индекса по колонке UnitPrice будет полезным и при следующем запросе, подсчитывающим количество единиц продукта по каждой определенной цене

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

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

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

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

Построение оптимального индекса

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

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

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

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

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

Индексы состоят из столбца данных, по которому собственно построен индекс и указателя на соответствующую строку. Это похоже на предметный указатель (индекс) книги: он содержит только ключевые слова и ссылку на страницу, на которую вы можете обратиться за дополнительной информацией. Обычно СУБД будет следовать указателям к строке из индекса, чтобы собрать всю информацию необходимую для запроса. Тем не менее, если индекс содержит все столбцы необходимые в запросе, информация может быть извлечена без обращения к самой таблице.

Рассмотрим индекс по столбцу UnitPrice, который уже упоминался выше. СУБД может использовать только элементы индекса для выполнения следующего запроса.

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

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

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

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

Заключение

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