Инструменты пользователя

Инструменты сайта


examination:bd:question38

Вопрос №38. Типы индексов, рекомендации по использованию индексов

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

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

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

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

  1. кластерные индексы;
  2. некластерные индексы;
  3. уникальные индексы.

Некластерный индекс

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

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

В большинстве случаев следует ограничиваться 4-5 индексами.

Кластерный индекс

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

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

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

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

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

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

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

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

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

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

Уникальные индексы следует определять только тогда, когда это действительно необходимо. Для обеспечения целостности данных в столбце можно определить ограничение целостности UNIQUE или PRIMARY KEY, а не прибегать к уникальным индексам. Их использование только для обеспечения целостности данных является неоправданной тратой пространства в базе данных. Кроме того, на их поддержание тратится и процессорное время.

Средства языка SQL предлагают несколько способов определения индекса: автоматическое создание индекса при создании первичного ключа; автоматическое создание индекса при определении ограничения целостности UNIQUE; создание индекса с помощью команды CREATE INDEX.

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

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

Поэтому возникает вопрос, почему бы не связать индекс с каждым столбцом? Дело в том, что создание индекса связано с затратами времени и памяти.

Например, при переопределении кластеризованного индекса автоматически создается некластеризованный индекс.

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

Далее перечисляются несколько рекомендаций по использованию индексов:

· Если необходимо изменить данные в столбце-счетчике (IDENTITY), то целесообразно связать с ним уникальный индекс, чтобы избежать повторения значений в этом столбце;

· С табличным столбцом, по которому проводится сортировка данных и который обычно указывается в предложении order by, необходимо связать индекс, чтобы SQL Сервер мог проводить упорядочение значений по этому индексу;

· Столбцы, по которым часто проводится соединение таблиц, всегда должны индексироваться, поскольку в этом случае данные располагаются в порядке возрастания индекса и соединение происходит значительно быстрее;

· Со столбцом таблицы, который объявлен главным ключом (primary), обычно связывается кластеризованный индекс, особенно тогда, когда он часто используется при соединении с другими таблицами. (Помните, что у таблицы может быть только один кластеризованный индекс);

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

Далее перечисляются несколько случаев, когда использование индексов нецелесообразно:

· Столбцы, которые редко используются в запросах, не стоит индексировать, поскольку выигрыш во времени поиска будет очень маленьким;

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

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

examination/bd/question38.txt · Последние изменения: 2014/01/15 08:12 (внешнее изменение)