Главная » Microsoft SQL Server, Базы данных » Настройка индексов

0

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

Отсутствие индексов

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

I               SQL Server 2005 может отобразить статистику использования индексов с помо- S VS щью Динамических представлений управления. В частности, sysdm_db_index_ * I * operational_stats и sysdm_index_usage_stats раскрывают информацию             ? о том, как используются индексы. Дополнительно существуют четыре динамических представления управления, которые вскрывают информацию о том, какие индексы искал оптимизатор запросов, но не смог найти. Это sysdm_ missing_index_groups, sysdm_missing_index_group_stats, sysdm_ missing_index_columns и sysdm_missing_index_details.

В первом приводимом примере используется простой запрос в базе данных OBXKites. Следующий простой код извлекает данные и упорядочивает их по StartDate:

SET STATISTICS TIME ON;

USE Adventureworks;

SELECT WorkOrderlD

FROM Production.WorkOrder ORDER BY StartDate;

Будет получен следующий результат (время из статистики):

SQL Server Execution Times:

CPU time = 126 ms, elapsed time = 679 ms.

Этот запрос использует сканирование кластеризованного индекса, за чем следует операция сортировки для упорядочения данных по StartDate. В данном примере операция сортировки является признаком того, что отсутствует индекс.

Создадим индекс по StartDate, чтобы скорректировать план выполнения:

CREATE INDEX WOStartDate ON Production.WorkOrder (StartDate);

Повторный запуск запроса показывает значительное уменьшение времени работы процессора — со 126 до 63 миллисекунд. При этом новый план выполнения запроса покажет только сканирование некластеризованного индекса по WOStartDate.

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

USE OBXKites;

SELECT LastName, FirstName, ProductName

FROM dbo.Contact JOIN dbo.[Order]

ON Contact.ContactID = [Order].ContactID JOIN dbo.OrderDetail

ON [Order].OrderlD = OrderDetail.OrderlD JOIN dbo.Product

ON OrderDetail.ProductID = Product.ProductID JOIN dbo.ProductCategory

ON Product.ProductCategorylD = ProductCategory.ProductCategorylD

WHERE ProductCategoryName = ’Kite’

ORDER BY LastName, FirstName;

Будут получены следующие результаты (время из статистики):

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 146 ms.

Общее время выполнения мало ввиду небольших размеров базы данных. Тем не менее план выполнения запроса выявляет некоторые проблемы (рис. 50.4). Одна из этих проблем проявляется в том, что выполняется сканирование таблицы, объединенной с использованием операции сравнения кучи. Сравнение кучи объединяет таблицы OrderDetail и Product. Единственный индекс таблицы OrderDetail в этой копии базы OBXKites является некла- стеризованным первичным ключом, включающим в себя столбец OrderDetail ID.

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

Поиск закладки

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

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

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

Рис. 50.4. Операции сканирования таблицы и сравнения кучи являются четким признаком отсутствия индекса

Puc. 50.5. Операция поиска в RID и вложенный цикл выявляют проблему поиска закладки

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

Для решения проблемы поиска закладки следует добавить поле OrderDetail ID к непастеризованному индексу ProductID, чтобы объединяемые данные непосредственно извлекались из некластеризованного индекса. В качестве альтернативы можно преобразовать первичный ключ в кластеризованный индекс, который заменит RID в некластеризованном индексе ключами кластеризованного индекса. Оба решения позволят некластеризованному индексу ProductID полностью покрыть требования запроса к таблице OrderDetail и избежать поиска закладки. Так как данные OrderDetail часто извлекаются по внешнему ключу OrderlD, он является наилучшим кандидатом на кластеризованный индекс. По этой причине лучшим решением является добавление поля OrderDetail ID в индекс ProductID.

Оптимизируемый аргумент поиска

Оптимизатор запросов SQL Server проверяет условия в предложении WHERE запроса для определения того, какие индексы ему окажутся полезными. Если SQL Server может оптимизировать предложение WHERE с помощью индекса, то данное условие называется аргументом поиска (или SARG). Однако не любое условие является таковым.

?               Множество условий, объединенных оператором AND, являются SARG, в то время как объединенные оператором OR — нет.

?               Условия, содержащие отрицания (о, !>,!<, Not Exists, Not In и Not Like), не являются оптимизируемыми. Очень легко доказать, что определенная строка существует. В то же время, чтобы доказать, что ее не существует, придется проверить все строки.

?               Условия, начинающиеся с символа макроподстановки, не используют индексы. Индекс поможет легко найти фамилию Smith, но для того чтобы найти все фамилии, содержащие ith, придется просканировать все строки.

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

?               Если предложение WHERE включает в себя функцию, например строковую, то для проверки всех строк с применением к данным функции потребуется сканирование таблицы.

Избирательность индексов

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

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

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

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

Use СНА2

DBCC Show_Statistics (Customer, IxCustomerName)

Будут получены следующие результаты (отформатированные и усеченные; полный листинг содержит детали о каждом значении индекса):

Statistics for INDEX ‘IxCustomerName’.

Rows                                                                                                                   Average

Updated Rows Sampled Steps Density key length

May 1,02 42 42                                  33                     0.0                           11.547619

All density Average Length Columns

3 . 03 03 031E-2 6.6904764                               LastName

2 .3809524E-2 11.547619                                  LastName, FirstName

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии