Создание индексов в таблицах

Создание индексов в таблицах

 

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

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

SQL Сервер поддерживает следующие виды индексов:

 

 

В этой главе будут подробно расмотрены перечисленные типы индексов.

 

 

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

 

 

Если эти ограничения являются обременительными для приложения, с которым работает пользователь, то индексы следует создавать оператором creat index. В противном случае нужно использовать уникальный или главный ключ, поскольку это самый простой способ связывания индекса с таблицой. Более детально об уникальных и главных ключах рассказывалось в главе 7 “Создание баз данных и таблиц”.

 

 

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

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

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

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

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

 

 

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

 

 

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

 

 

Индексы связываются со столбцом таблицы с целью ускорения выборки данных. Простешая форма команды create index (создание индекса) имеет следующий вид:

 

create index название_индекса

on название_таблицы (название_столбца)

 

Например, команда для создания индекса в столбце au_id таблицы authors имеет следующий вид:

 

create index au_id_ind

on authors (au_id)

 

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

Индексы нельзя связывать со столбцами типа bit, text и image.

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

 

 

Полный синтаксис команды создания индексов create index имеет следующий вид:

 

create [unique] [clustered | nonclustered]

   index название_индекса

on [[база_данных.]владелец.]название_таблицы (название_столбца

   [,название_столбца]...)

[with {{fillfactor | max_rows_per_page}= x,

    ignore_dup_key, sorted_data,

   [ignore_dup_row | allow_dup_row]}]

[on название_сегмента]

 

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

 

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

 

 

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

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

 

create index nmind

on friends_etc (pname, sname)

 

Порядок следования названий столбцов в этом списке влияет на порядок следования (сортировку) составных значений и этот порядок может отличаться от порядка следования этих столбцов в операторе создания таблицы create table. Например, в предыдущем операторе столбцы pname и sname можно указать в другом порядке.

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

В составной индекс как уже указывалось можно включать названия двух и более столбцов. Эти столбцы вместе со столбцом sensitivity образуют составной индекс. Составной индекс следует использовать в том случае, когда комбинацию значений данных можно рассматривать как одно составное значение, по которому удобно вести поиск. Например, в таблице friends_etc был определен составной индекс, связанный со значениями в столбцах pname, sname и sensitivity (этот столбец добавляется SQL Сервером автоматически). Как уже было указано, оператор определения индекса в этой таблице имеет следующий вид:

 

create index nmind

on friends_etc (pname, sname)

 

Порядок следования названий столбцов в этом списке может отличаться от  порядка следования этих столбцов в операторе создания таблицы create table. Например, в предыдущем операторе столбцы pname и sname можно указать в другом порядке. SQL Сервер всегда автоматически добавляет столбец sensitivity в качестве последнего в каждый составной индекс.

 

 

Уникальный (unique) индекс, связанный с некоторым столбцом, препятствует появлению в этом столбце двух одинаковых значений, включая неопределенное значение NULL. В этом случае система сама проверяет отсутствие дублирующихся значений, во время определения такого индекса, если в таблице уже есть некоторые данные, и такая проверка повторяется после каждого оператора модификации данных insert или update.

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

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

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

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

Ключевое слово unique (уникальный) можно использовать и для составных индексов. Но оно не использовать в нашем примере при определении составного индекса в таблице friends_etc.

 

 

Опция identity in nonunique index (счетчик в неуникальном индексе) автоматически включает столбец счетчика во все индексы таблицы, тем самым делая их все уникальными. Эта опция базы данных делает логически неуникальные ключи фактически уникальными, что позволяет использовать их в обновляющих курсорах (uptable cursors) и при считываниях на нулевом уровне изоляции (isolation level 0 reads).

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

Опцию identity in nonunique index следует использовать в том случае, если пользователь планирует работать с курсорами или выполнять считывание на нулевом уровне в таблице с неуникальными индексами. Уникальный индекс обеспечивает установку курсора на нужной строке, перед тем как выполняется следующая операция fetch (загрузка) с помощью этого курсора.

 

 

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

 

 

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

По умолчанию этот фактор равен 0 и это же значение используется, если пользователь не указал опции заполнения. Системный администратор может изменить значение этого фактора, принимаемое по умолчанию, с помощью системной процедуры sp_configure (конфигурация). Более подробная информация об этой опции приводится Руководстве системного администратора SQL Сервера.

Правильные значения этого фактора, которые может указать пользователь, изменяются в интервале от 1 до 100.

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

 

create index postalcode_ind

on friends_etc(postalcode)

with fillfactor = 100

 

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

 

 

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

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

Правильное значения этого параметра, задаваемое пользователем, заключено в интервале от 1 до 256.

Следующий оператор создания индекса использует опцию max_rows_per_page:

 

create index postalcode_ind

on friends_etc(postalcode)

with max_rows_per_page = 10

 

 

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

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

Логически главный ключ выбирается на этапе проектирования базы данных. Однако пользователь может явно определить главные ключи, внешние ключи и общие ключи (пары ключей часто используемые при соединениях) с помощью системных процедур sp_primarykey, sp_foreignkey и sp_commonkey. Информацию о ключах можно получить с помощью системных процедур sp_helpkey, а информацию о столбцах, по которым целесообразно осуществлять  соединение с помощью системной процедуры sp_helpjoins.

Главный ключ в виде ограничения целостности данных можно также определить в операторах создания таблицы (creat table) или изменения таблицы (alter table), тем самым создав табличный индекс. Информацию об ограничениях целостности можно получить с помощью системной процедуры sp_helpconstraint.

Об определении главных и внешних ключей можно посмотреть в главе 15 “Триггеры: обеспечение целостности данных”. Полная информация о системных процедурах дается в Справочном руководстве SQL Сервера.

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

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

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

Если при определении индекса не указаны ключевые слова clustered (кластеризованный) или nonclustered (некластеризованный), то создается некластеризованный индекс.

Далее приводится пример создания индекса, связанного со столбцом title_id, таблицы titles (если пользователь действительно собирается выполнить эту команду, то необходимо затем удалить этот индекс командой drop index):

 

create clustered index titleidind

on titles (title_id)

 

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

 

create nonclustered index postalcodeind

on friends_etc (postalcode)

 

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

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

 

drop index friends_etc.nmind

 

Затем уже можно определить кластеризованный индекс:

 

create clustered index nmind

on friends_etc (pname, sname)

 

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

 

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

 

 

Индексные опции ignore_dup_key (игнорировать дублирование ключа), ignore_dup_row (игнорировать повторяющиеся строки) и allow_dup_row (разрешить дублирование строк) определяют реакцию системы на появление повторяющихся значений при выполнении операторов вставки и обновления (insert, update). В следующей таблицы приведены условия, когда можно использовать эти опции:

 

Таблица 11-1: Индексные опции

 

Тип индекса	Опция
Кластеризованный	ignore_dup_row или allow_dup_row
Уникальный кластеризованный	ignore_dup_key
Некластеризованный	Нет
Уникальный некластеризованный	ignore_dup_key
Уникальный некластеризованный	ignore_dup_row

 

 

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

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

 

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

 

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

Приведем пример использования опции ignore_dup_key:

 

create unique clustered index phone_ind

on friends_etc(phone)

with ignore_dup_key

 

 

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

Опции ignore_dup_row и allow_dup_row являются взаимоисключающими.

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

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

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

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

 

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

 

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

 

Таблица 11-2: Индексные опции, связанные с дублированием строк

 

Опция	Имеются повторения	Вводятся повторения
Нет опций	Команда создания индекса creat index не выполняется.	Отменяется выполнение текущей команды.
Указана опция allow_dup_row	Команда создания индекса полностью выполняется.	Текущая команда полностью выполняется.
Указана опция ignore_dup_row	Индекс создается, но дубликаты строк удаляются. Выдается сообщение об ошибке.	Все строки вставляются за исключением дубликатов. Выдается сообщение об ошибке. См. предыдущие предупреждения.

 

 

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

Если указана опция sorted_data, а расположение табличных данных не соотвествует значениям индекса, то выдается сообщение об ошибке и команда не выполняется.

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

 

 

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

 

create index titleind

on titles (title)

on seg1

 

 

Команда drop index (удаление индекса) используется для удаления индексов из базы данных. Эта команда имеет следующий синтаксис:

 

drop index название_таблицы.название_индекса

            [,название_таблицы.название_индекса]...

 

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

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

Индекс полезно удалить в том случае, если он не используется в большинстве запросов.

Чтобы удалить индекс phone_ind в таблице friends_etc, нужно выполнить следующую команду:

 

drop index friends_etc.phone_ind

 

 

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

 

sp_helpindex friends_etc

 

index_name      index_description                     index_keys

--------------        --------------------------------         -------------

nmind                clustered located on default       pname, sname

postalcode_ind  nonclustered located on default  postalcode

postalcodeind    nonclustered located on default  postalcode

 

          (3 rows affected, return status = 0)

 

Процедура sp_help также выдает информацию об индексах таблицы.

 

 

Команда update statistics (обновление статистики) помогает SQL Серверу сделать оптимальный выбор индексов при выполнении запросов путем запоминания самого последнего распределения ключевых значений в индексах. Эту команду следует использовать, когда большое количество данных добавляется, изменяется или удаляется из индексного столбца.

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

 

update statistics название_таблицы [название_индекса]

 

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

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

Следующая команда показывает как можно узнать индексы таблицы authors:

 

sp_helpindex authors

 

index_name  index_description     index_keys          

----------     ------------------    ---------------------

auidind        clustered, unique        au_id             

aunmind        nonclustered                au_lname, au_fname

 

Чтобы обновить статистику для всех индексов этой таблицы, нужно выполнить следующую команду:

 

update statistics authors

 

Чтобы обновить статистику, относящуюся только к индексу, связанному со столбцом au_id, нужно выполнить следующую команду:

 

update statistics authors auidind

 

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