Создание и управление индексами

Рассмотрим более побробно формат оператора Transact-SQL (диалекта SQL, используемого Microsoft SQL Server) CREATE INDEX, позволяющего создать реляционный индекс для таблицы или представления:

CREATE [UNIQUE] [CLUSTERED I NONCLUSTERED] INDEX.

index_name.

ON (column [ASC | DESC] [,…n]).

[INCLUDE (column_name [,…n])].

[WHERE ].

[WITH ([,…n])].

[ON { partition_scheme_name (column_name).

I filegroup_name.

I default.

} ].

[FILESTREAM_ON (filestream_filegroup_name |.

partition_scheme_name | «NULL»)][; ].

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

Ключевое слово CLUSTERED указывает на создание кластеризованного индекса; NONCLUSTERED — некластеризованного, он также будет создан, если явно ничего нс указано. Рекомендуется сначала создавать для таблицы кластеризованный индекс, а после него — некластеризованные.

Имя индекса при описании формата обозначено как index narae. Оно должно быть уникальным в пределах объекта (таблицы или представления, в описании формата — object), для которого создается индекс.

Как уже отмечалось, SQL Server позволяет создавать индексы не только для таблиц, но и для представлений. При этом создание уникального кластеризованного индекса физически «материализует» представление: оно будет представлено в базе собственными данными, а не только через определение на языке SQL. Такие представления называются индексированными. Уникальный кластеризованный индекс для представления должен быть создан до того, как для него будут определены какие-либо другие индексы.

После указания имени объекта в скобках идет перечисление столбцов, включаемых в индекс. Опционально может указываться порядок сортировки ASC — по возрастанию (используется по умолчанию), DESC — по убыванию. Если столбцов в ключе индекса несколько, они перечисляются через запятую, — такой индекс называется составным. В SQL Server в ключ составного индекса могут входить до 16 столбцов общим объемом не более 900 байт. Столбцы, входящие в обычный индекс, не могут определяться на «больших» типах данных, таких как TEXT, XML, IMAGE и т. д. Однако кроме реляционных индексов могут создаваться индексы для столбцов с XML-данными и пространственные индексы (для столбцов с типами данных GEOMETRY и GEOGRAPHY).

Секция INCLUDE (coiumn_name [,…п]) позволяет указать неключевые столбцы, добавляемые на конечный уровень некластеризованного индекса. Описанное выше ограничение в 16 столбцов и 900 байт на такие столбцы не распространяется.

Конструкция WHERE позволяет создать фильтрованный индекс, в состав которого будут включаться только строки, соответствующие условию фильтрации. В условии отбора нельзя использовать сравнения со значением NULL. Вместо этого используются операторы IS NULL и IS NOT NULL. Пример создания подобного индекса с условием отбора по голу издания книги:

CREATE NONCLUSTERED INDEX ix4_bookl.

on Bookl (Title).

INCLUDE (Author, Publisher, [Year]).

WHERE [Year]>2000.

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

При создании индекса в разделе WITH можно указать дополнительные параметры индекса (перечислены ниже). Если параметров несколько, они перечисляются через запятую.

FILLFACTOR =.

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

PAD_INDEX = [ON | OFF).

Параметр определяет разреженность индекса. Значение по умолчанию — OFF, в этом случае страницы промежуточного уровня индекса заполняются почти полностью. Если указано ON, то «разреженные» страницы создаются и на промежуточном уровне. Процент определяется параметром FILLFACTOR, который должен обязательно присутствовать. Количество строк на странице индекса промежуточного уровня никогда не бывает менее двух.

SORT_IN_TEMPDB = [ON | OFF).

Значение по умолчанию — OFF, что означает «промежуточные результаты сортировки, производимой в процессе индексирования, хранятся в той же БД, где и индекс». Если установить значение ON, промежуточные результаты будут храниться в базе для временных значений — tempdb. В этом случае, выигрыш в быстродействии может быть получен, если база tempdb и пользовательская БД находятся на разных физических дисках.

IGNORE DUP KEY = [ON | OFF).

Опция определяет реакцию на попытку вставки в уникальный индекс повторяющегося значения ключа. Значение по умолчанию — OFF: в этом случае выводится сообщение об ошибке и выполняется откат всей операции INSERT. Если установить значение ON, будет выводиться предупреждающее сообщение, и с ошибкой завершится только добавление строк, нарушающих ограничение уникальности. IGNORE_DUP_KEY нельзя установить в значение ON для индексируемых представлений, неуникальных индексов, XML-индексов, пространственных и фильтруемых индексов.

STATISTICS_NORECOMPUTE = {ON | OFF}.

Значение по умолчанию — OFF: автоматическое обновление статистических данных включено. Значение ON указывает, что устаревшие статистические данные не будут пересчитываться автоматически. Устаревшая статистика может привести к выбору оптимизатором неоптимальных планов выполнения запросов.

DROP_EXI STING = {ON I OFF}.

Опция определяет действия СУБД в случае, если для данного объекта уже существует индекс с таким же именем. Значение по умолчанию — OFF, в этом случае выдается ошибка. Если указано ON, то существующий индекс удаляется (индекс перестраивается).

ONLINE = {ON | OFF}.

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

ALLOW_ROW_LOCKS = {ON I OFF}.

Значение по умолчанию — ON: разрешает блокировки строк при обращении к индексу. Необходимость в блокировке строк определяет СУБД. Значение OFF указывает на то, что блокировки строк не используются.

ALLOW_PAGE_LOCKS = {ON | OFF).

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

MAXDOP =.

На время выполнения операции с индексами переопределяет настройку, регулирующую максимальное число используемых в ходе операции процессоров (будет ли процедура построения индекса параллельно выполняться на нескольких процессорах). Параметр может принимать значение от 0 до 64 и применяется для версий SQL Server Enterprise и Developer. По умолчанию значение 0, что указывает на то, что в зависимости от загрузки системы используются все имеющиеся процессоры или меньшее их число. При значении 1 используется только один процессор, параллельное выполнение не применяется. Значение > 1 ограничивает используемое в операциях с индексом количество процессоров указанным.

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

DATA_COMPRES SI ON = {NONE | ROW | PAGE}.

[ON PARTITIONS ({ | } [,…n])] Опция задает режим сжатия данных индекса. Значение NONE указывает на то, что сжатие не используется. ROW — сжатие на уровне строк. PAGE — сжатие на уровне страниц. Раздел ON PARTITIONS может задаваться только для секционированного индекса (см. далее) и указывает, что данный тип сжатия применяется для конкретных секций. Диапазон номеров секций указывается с использованием ключевого слова ТО, например: ON PARTITIONS (1 ТО 3).

Индекс, как и таблица, может быть секционированным. Наличие в операторе CREATE INDEX необязательной секции ON partition_ scheme_name (column_name) задает схему секционирования (определяет файловые группы, соответствующие секциям индекса). Схема секционирования предварительно должна быть создана с помощью команды CREATE PARTITION SCHEME. Параметр column_name указывает столбец, по которому будет секционирован индекс.

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

[FILESTREAM_ON {filestream_filegroup_name I.

partition_scheme_name I «NULL» } ].

Раздел FILESTREAMON в операторе CREATE INDEX может указываться только при создании кластеризованного индекса. Указывает размещение данных FILESTREAM для таблицы при создании кластеризованного индекса.

Необходимо пояснить, что хранилище FILESTREAM предоставляет SQL Server возможность размещать данные больших двоичных объектов (BLOB) и varbinarv (max) в виде файлов файловой системы NTFS. С помощью инструкций Transact-SQL можно вставлять, обновлять, запрашивать и создавать резервные копии данных FILESTREAM. Программные интерфейсы файловой системы предоставляют потоковый доступ к этим данным.

После того как индекс создан, может понадобиться изменить какие-то его опции (например, уровень заполнения страниц), выполнить дефрагментацию индекса, временно отключить и снова включить. Эти действия можно выполнить с помощью оператора ALTER INDEX:

ALTER INDEX {index_name I ALL }.

ON.

{ REBUILD [ [PARTITION = ALL].

[WITH ((,…n))].

I [ PARTITION =.

[WITH ([,…n])] ]].

| DISABLE.

| REORGANIZE [ PARTITION = ].

[ WITH (LOB_COMPACTION = {ON I OFF})].

I SET ([ ,…n ])}[; ].

В ходе работы с таблицей индекс может фрагментироваться. Например, при удалении записей из таблицы соответствующие им данные удаляются из индекса, и это место остается незанятым. Дефрагментация индекса может быть выполнена командой ALTER INDEX с опциями REBUILD или REORGANIZE. В первом случае перестаиваются все уровни индекса, во втором — только конечный уровень. Операция REORGANIZE не вызывает долгосрочной блокировки таблицы, тогда как при использовании REBUILD таблица может блокироваться до завершения перестроения индекса. Ниже приведена команда, перестраивающая конечный уровень индекса ix4_bookl таблицы Book!:

ALTER INDEX ix4_bookl ON Bookl REORGANIZE Индекс можно отключить, использовав в операторе ALTER INDEX ключевое слово DISABLE. При этом определение индекса остается в системном каталоге, но индекс не обновляется и не используется [21]. При отключении кластеризованного индекса становится недоступной вся таблица. Ниже приведен пример использования данного оператора:

ALTER INDEX ix4_bookl ON Bookl DISABLE Если понадобится снова включить индекс, необходимо будет его полностью перестроить:

ALTER INDEX ix4_bookl ON Bookl REBUILD Если понадобится удалить индекс, нужно использовать оператор DROP INDEX:

DROP INDEX ON.

[WITH ([ ,…n ])].

Среди опций можно отметить опцию ONLINE = {ON | OFF}, которая определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF: таблицы блокируются; если установить ON, долгосрочные блокировки таблицы не устанавливаются.

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

DROP INDEX ix4_bookl ON Bookl.

В документации по SQL Server отмечается [23], что инструкция DROP INDEX неприменима к индексам, созданным при определении в таблице ограничений PRIMARY KEY или UNIQUE. В подобных случаях для удаления ограничения и соответствующего индекса используется оператор ALTER TABLE с ключевыми словами DROP CONSTRAINT.