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

Для создания индексов используется команда CREATE INDEX. Общий синтаксис которой показан ниже:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]

INDEX index_name

ON table (column [,...n])

[WITH [PAD_INDEX][[,] FILLFACTOR = fillfactor][[,]

IGNORE_DUP_KEY][[,] DROP_EXISTING][[,]

STATISTICS_NORECOMPUTE]][ON filegroup],

где index_name – имя создаваемого индекса;

table – имя таблицы, в которой создается;

column – имя столбца таблицы;

PAD_INDEX – этот параметр определяет размер пространства, оставляемого открытым на каждой внутренней странице;

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

DROP_EXISTING – этот параметр используется только при создании кластерных индексов и определяет обработку существующих некластерных индексов таблицы;

STATISTICS_NORECOMPUTE – этот параметр блокирует автоматическое обновление статистических сведений по индексам, что потребует выполнения команды UPDATE STATISTICS вручную.

Например:

CREATE CLUSTERED INDEX Student_IND on Student(idSt)

CREATE NONCLUSTERED INDEX NumZach_IND on Student(numZach)

Для просмотра данных об индексах можно воспользоваться хранимыми процедурами sp_helpindex и sp_statistics.

exec sp_helpindex 'Student'

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

1. Прежде всего следует понять характеристики самой базы данных. Например, будет ли это база данных оперативной обработки транзакций (OLTP) с часто изменяющимися данными, или система поддержки решений (DDS), или хранилище данных (OLAP), предназначенное в основном для чтения?

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

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

4. Определите, какие параметры индексов могут повысить производительность при создании индекса или при его поддержке.

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

При проектировании индекса следует учитывать следующие рекомендации:

• Большое количество индексов в таблице снижает производительность инструкций INSERT, UPDATE, DELETE, потому что при изменении данных в таблице все индексы должны быть изменены соответствующим образом.

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

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

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