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

0

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

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

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

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

?               Страничная архитектура SQL Server, модель физической схемы и логические операторы плана выполнения запроса.

?               Распределение данных, статистика индексов, выбор индексов оптимизатором запросов и обеспечение обслуживания индексов.

?               Кластеризованная структура индексов, коэффициент заполнения, слияние страниц и обслуживание индексов.

?               Запросы, индексы, планы выполнения запросов и оптимизатор запросов.

?               Повторное использование плана запроса и применение параметров.

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

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

Индексация

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

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

Основы индексации

Существуют два основных типа индексов. Предметный указатель, который физически сортирует текстовые страницы, подобно телефонной книге, называется кластеризованным, так как сами данные выстроены в специфическом порядке. Этот тип предметного указателя физически находится в тексте книги. Естественно, текст может быть отсортирован в данном случае только в одном порядке. Аналогично, таблицы SQL Server могут иметь только один физический порядок сортировки, и именно он называется кластеризованным индексом таблицы.

Некластеризованный индекс подобен предметному указателю в конце книги — он отправляет читателя на определенные страницы. Любой вопрос может быть без труда найден в тексте, если вначале найти его в предметном указателе, а затем отправиться по ссылке на конкретную страницу. SQL Server также располагает некластеризованными индексами, которые выполняют сортировку, используя столбцы, отличные от кластеризованного индекса.

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

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

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

Дополнительная Индексы таблиц нельзя путать с индексированными представлениями. Редак- информация ция Enterprise Edition содержит функцию денормализации, которая строит кластеризованный индекс, распространяющийся на множество таблиц. Неправильное использование индексированных представлений может существенно понизить производительность операционных баз данных. В главе 53 мы более подробно поговорим об индексированных представлениях.

Кластеризованные индексы

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

Рис. 50.1. Кластеризованный индекс связывает листовые узлы страниц индексов со страницами данных, обеспечивая тот же порядок данных, что и в индексе

Кластеризованный индекс может повлиять на производительность одним из описанных ниже способов.

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

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

/      Любая таблица имеет некоторый физический порядок. Если таблица не имеет

На заметку кластеризованного индекса, то она находится в виде кучи, т.е. в неупорядоченном виде. В этом случае строки, при невозможности идентификации по ключевому столбцу кластеризованного индекса, идентифицируются с помощью внутреннего идентификатора строки (RowiD) кучи. Этот идентификатор не используется в запросах.

Некластеризованные индексы

Некластеризованным называется индекс в виде сбалансированного дерева, начинающегося с корневого узла и растущего через промежуточные узлы к листовым узлам. Листовые узлы указывают непосредственно на строки страниц данных (см. рис. 50.1). Таблица SQL Server 2005 может иметь до 249 ^кластеризованных индексов, но мне на практике не встречались таблицы, требующие больше десяти хорошо продуманных индексов.

Некластеризованный индекс может быть создан по вычисляемому столбцу. Для включения возможности создания индекса или индексированного представления по вычисляемому столбцу следует установить для параметра quoted_identif ier значение on.

Создание индексов

В окне Object Explorer утилиты Management Studio существующие индексы каждой таблицы перечислены под узлом Databases^Tables^Indexes. Свойства каждого нового или существующего индекса можно корректировать в диалоговом окне Index Properties (рис. 50.2). Это окно открывается для существующего индекса щелчком правой кнопки мыши на его имени и выбором в контекстном меню пункта Properties. Новые индексы создаются с помощью контекстного меню узла Indexes конкретной таблицы.

В утилите Management Studio индексы отображаются как узлы на панели Object Explorer. С помощью выбора в контекстном меню узла Indexes пункта New Index можно создать новый индекс. В открывающейся при этом форме содержатся четыре вкладки.

?               General. Содержит имя индекса, его тип, свойство уникальности, а также ключевые столбцы.

?               Options. Управляет режимом работы индекса. Здесь же любой индекс может быть отключен и снова включен.

?               Included Columns. Содержит неключевые столбцы, служащие оболочкой индекса.

?               Storage. Позволяет поместить индекс в выбранную файловую группу.

Puc. 50.2. Параметры любого индекса можно установить в диалоговом окне Index Properties утилиты Management Studio

При открытии окна параметров существующего индекса оно содержит также и две дополнительные вкладки.

?               Fragmentation. Отображает детальную информацию о состоянии индекса.

?               Extended Properties. Содержит дополнительные параметры, определяемые пользователем.

Изменения, которые вносят в окне параметров индекса, можно применить немедленно с помощью щелчка на кнопке О К; их применение можно отложить на заданное время, а также воплотить все изменения в сценарий. Для этого можно воспользоваться значками в верхней части окна.

В программном коде индексы создаются с помощью инструкции CREATE INDEX. В следующем примере создается кластеризованный индекс IxOrderld, основанный на внешнем ключе OrderlD таблицы OrderDetail:

CREATE CLUSTERED INDEX IxOrderlD ON dbo.OrderDetail (OrderlD);

I  Для извлечения исчерпывающей информации об индексах с помощью про-

S       VS граммного кода используют следующие функции и представления каталогов:

I                  * sysindexes, sysindex_columns, sysstats, sysstats_columns, sysdm_db_

              *    index_physical_stats, sysdm_index_operational_stats, sysindexkey_

property и sysindex_col.

Кластеризованный индекс создается автоматически при определении первичного ключа. Для удаления индекса используется инструкция DROP INDEX, в которой указывается имя таблицы и имя индекса, например:

DROP INDEX OrderDetail.IxOrderlD

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

Составные индексы

Составным называют кластеризованный или некластеризованный индекс, включающий множество столбцов. На практике большинство индексов являются составными. Если вы используете окно параметров индекса утилиты Management Studio, то составные индексы создаются с помощью добавления множества столбцов во вкладке General. Для создания составного индекса в программном коде он должен быть объявлен с помощью инструкции DDL CREATE INDEX после создания таблицы. В следующем примере создается составной кластеризованный индекс для таблицы GUIDE базы данных СНА2:

CREATE CLUSTERED INDEX IxGuideName ON dbo.Guide (LastName, FirstName);

Порядок столбцов в составном индексе очень важен. Чтобы при поиске получить все преимущества составного индекса, последний должен содержать наиболее часто используемые столбцы в направлении слева направо. Если составной индекс выглядит как lastname, firstname, то поиск только по firstname не будет использовать индекс, а поиск по lastname или совместно по lastname и firstname — будет.

Дополнительная SQL Server 2005 может индексировать слова в столбцах с помощью функции информация полнотекстового поиска, которую мы обсуждали в главе 13.

Первичные ключи

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

Дополнительная О создании первичных ключей см. в главе 17.

информация

Покрывающие индексы

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

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

Покрывающий индекс может иметь необходимость включать некоторые столбцы, которые не требуют реляционное ядро для идентификации строк, отсеиваемых предложением WHERE. Эти дополнительные, неключевые столбцы не испытывают потребность в сортировке в сбалансированном дереве индекса — они включаются исключительно с целью возвращения столбцов запросом SELECT.

Недостатком покрывающих индексов в предыдущих версиях SQL Server было Новинка 4 то, что при обновлениях должны были сортироваться все столбцы — даже те, 2005                          которые не использовались при отборе строк, а были включены исключительно

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

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

CREATE NONCLUSTERED INDEX IxOrderNumber ON dbo.[Order] (OrderNumber)

INCLUDE (OrderDate);

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

Включаемые столбцы не учтены в ограничениях некластеризованного индекса— 16 ключевых столбцов и 900 байтов. На самом деле в покрывающий индекс можно включить до 1023 неключевых столбцов. В качестве включаемых могут быть также и столбцы с особо крупными типами данных — XML, varchar (max), nvarchar (max) и varbinary (max), — даже несмотря на то, что они не могут выступать в роли ключевых столбцов.

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

Местонахождение файловой группы

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

ON имя_файловой_группьг.

CREATE NONCLUSTERED INDEX имя_индекса ON Table (столбцы)

ON имя_файловой_группы;

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

Дополнительная Физическое размещение таблиц и индексов может быть сконфигурировано и информация более глубоко, с использованием файловых групп и разделов. Более подробно _        эти темы мы обсудим в главе 53.

Параметры индексов

Индексы SQL Server 2005 имеют несколько параметров, в том числе уникальность, выделение пространства, а также параметры производительности.

Уникальные индексы

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

В Management Studio уникальный индекс создается с помощью установки флажка Unique во вкладке General диалогового окна параметров индекса.

В программном коде уникальность индекса указывается с помощью добавления в определение ключевого слова UNIQUE:

CREATE UNIQUE INDEX OrderNumber ON [Order] (OrderNumber);

Коэффициент заполнения индекса

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

Поскольку индекс представляет собой сбалансированное дерево, каждая страница должна содержать как минимум две строки. Коэффициент заполнения и параметр pad index влияют как на промежуточные страницы, так и на листовые узлы, как показано в табл. 50.1.

Таблица 50.1. Коэффициент заполнения и параметр pad index

Коэффициент

заполнения

Промежуточные страницы

Листовой узел

0

Одна свободная запись

100%-ное заполнение

1-99

Одна свободная запись или объем, меньший коэффициента заполнения, если установлен параметр pad index

Объем, меньший коэффициента заполнения

100

Одна свободная запись

100%-ное заполнение

Коэффициент заполнения применяется только к листовым узлам индекса, если к нему не применен параметр pad index. Этот параметр указывает серверу применять слабость коэффициента заполнения также и к промежуточным уровням сбалансированного дерева.

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

Дополнительная Коэффициент заполнения индекса по мере разделения страниц постепенно ут- икформация рачивает свою роль. Для восстановления коэффициента заполнения план обслуживания должен включать в себя периодическую реиндексацию. Информация о порядке поддержки индексов содержится в главе 37.

В Management Studio коэффициент заполнения устанавливается во вкладке Options диалогового окна параметров индекса. В программном коде Т-SQL параметры коэффициента заполнения и index pad указываются после команды CREATE INDEX. В следующем примере создается индекс OrderNumber с 15% свободного пространства как на листовых узлах, так и на промежуточных страницах:

CREATE NONCLUSTERED INDEX IxOrderNumber ON dbo.[Order] (OrderNumber)

WITH FILLFACTOR = 85, PAD_INDEX = ON;

Управление блокировкой индексов, их создание в реальном времени и ограни- Норинка     чение параллелизма индексов являются новыми параметрами, появившимися

2005         в версий SQL Server 2005. Для параметров pad_index, fillfactor, sort_in_db,

ignore_dup_key, statistiсs_norecompute и drop_existing был изменен синтаксис. Новый синтаксис требует обязательного включения выражения =оп. Из соображений обратной совместимости продолжает поддерживаться и старый синтаксис, однако в будущих версиях этого уже не будет.

Ограничение блокировок и параллелизма

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

Порядок сортировки индексов

СУБД SQL Server способна создавать нисходящие индексы. Любой запрос, использующий предложение ORDER BY, приведет к сортировке по возрастанию, если в этом предложении не будет явно указано ключевое слово DESC.

В инструкции DDL CREATE INDEX ключевые слова AS С и DESC следуют непосредственно за именем столбца.

Параметр игнорирования дублирующихся ключей

Параметр I GNORE_DUP_KEY не оказывает влияния на сам индекс, а только на то, как впоследствии индекс будет влиять на изменения данных.

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

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

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

CREATE UNIQUE INDEX OrderNumber ON [Order] (OrderNumber)

WITH IGNORE_DUP_KEY = ON

Параметр удаления существующего индекса

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

Параметр запрета пересчета статистики индекса

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

Сортировка в базе tempdb

Параметр sort_in_tempdb=on изменяет метод создания индекса, форсируя использование базы tempdb, а не памяти. Если индекс постоянно удаляется и воссоздается, то этот параметр способен сократить время создания индекса. В большинстве индексов этот параметр не имеет большого значения, к тому же он не обязательный.

Отключение индекса

Любой индекс может быть временно отключен. Для этого достаточно снять флажок Use Index во вкладке Option диалогового окна параметров индекса. В программном коде T-SQL этот эффект достигается с помощью включения параметра DISABLE в инструкцию DDL ALTER INDEX:

ALTER INDEX [IxContact] ON [dbo].[Contact] DISABLE

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

Отключение кластеризованного индекса фактически приводит к отключению Внимание! всей таблицы.

Чтобы снова включить индекс, используется команда ALTER INDEX. . . REBUILD WITH:

ALTER INDEX [PK____ Contact      0BC6C43E]

ON [dbo].[Contact]

REBUILD WITH ( PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF,

ALLOW ROW LOCKS = ON,

ALLOW_PAGE_LOCKS = ON,

SORT_IN_TEMPDB = OFF,

ONLINE = OFF )

Создание базовых индексов

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

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

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

3.              Создайте одностолбцовый индекс для всех столбцов, которые наиболее вероятно будут появляться в предложениях WHERE, ORDER BY или GROUP BY.

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

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

По теме:

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