Статья
04/01/2012

Создание индексов (компонент Database Engine)

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

Задачи, связанные с созданием индекса

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

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

Проектирование индекса — задача первостепенной важности. Проектирование индекса включает определение столбцов, которые следует использовать, выбор типа индекса (например кластеризованный или некластеризованный), выбор подходящих параметров индекса и определение расположения файловой группы или схемы секционирования. Дополнительные сведения см. в разделе Проектирование индексов.
Определение наилучшего метода создания индекса. Индекс можно создать несколькими способами:
- Определив для столбца ограничение PRIMARY KEY или UNIQUE с использованием инструкции CREATE TABLE или ALTER TABLE
  
  Компонент SQL Server Database Engine автоматически создает уникальный индекс, чтобы обеспечить уникальность требований ограничения PRIMARY KEY или UNIQUE. По умолчанию с целью форсирования ограничения PRIMARY KEY создается уникальный кластеризованный индекс, если только кластеризованный индекс уже не создан для таблицы и если не указан уникальный некластеризованный индекс. Чтобы форсировать ограничение UNIQUE, по умолчанию создается уникальный некластеризованный индекс, если явно не указан уникальный кластеризованный индекс и не существует связанного с таблицей кластеризованного индекса.
  
  Кроме того, можно задать параметры индекса, место хранения индекса, файловую группу или схему секционирования.
  
  Индексу, созданному посредством ограничения PRIMARY KEY или UNIQUE, автоматически назначается имя, эквивалентное имени ограничения. Дополнительные сведения см. в разделах Ограничения PRIMARY KEY и Ограничения UNIQUE.
- Создав индекс независимо от ограничения с использованием инструкции CREATE INDEX или диалогового окна Создание индекса обозревателя объектов среды Среда SQL Server Management Studio.
  
  При этом необходимо указать имя индекса, таблицу и столбцы, к которым применяется индекс. Кроме того, можно задать параметры индекса, место хранения индекса, файловую группу или схему секционирования. Если не указаны параметры кластеризации или уникальности, по умолчанию создается некластеризованный неуникальный индекс. Чтобы создать отфильтрованный индекс, укажите необязательное предложение WHERE. Дополнительные сведения см. в разделе Рекомендации по проектированию отфильтрованных индексов.
Создание индекса.

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

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

Замечания по реализации

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

Показатель	Максимальное значение	Дополнительные сведения
Количество кластеризованных индексов в таблице	1
Количество некластеризованных индексов таблицы	999	Включает некластеризованные индексы, созданные ограничениями PRIMARY KEY или UNIQUE, отфильтрованные индексы, но не включает XML-индексы.
Количество XML-индексов таблицы	249	Включает первичные и вторичные XML-индексы столбцов типа xml. Индексы для столбцов типа данных xml
Количество пространственных индексов на таблицу	249	Работа с пространственными индексами (компонент Database Engine)
Количество ключевых столбцов на индекс	16*	Если таблица содержит первичный XML-индекс или любое число пространственных индексов, то кластеризованный индекс ограничен 15 столбцами. Максимальный размер ключей индекса.
Размер записи ключа индекса	900 байт*	Не применяется к XML-индексам и пространственным индексам. Для поддержки таблицей пространственных индексов размер ключа индекса не должен превышать 895 байт. Максимальный размер ключей индекса.

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

Типы данных

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

Тип данных	Использование в индексе	Дополнительные сведения
Определяемый пользователем тип данных CLR	Может быть проиндексирован, если тип поддерживает двоичное упорядочение.	Работа с определяемыми пользователем типами данных CLR
Типы данных больших объектов (LOB): image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml	Не могут использоваться в качестве столбца ключа индекса. Однако столбец типа XML может быть ключевым столбцом в первичном или вторичном XML-индексе таблицы. Все эти типы, кроме image, ntext и text, можно использовать в качестве неключевых (включенных) столбцов некластеризованного индекса. Могут быть использованы в индексе, если входят в выражение вычисляемого столбца.	Индекс с включенными столбцами Индексы для столбцов типа данных xml
Вычисляемые столбцы	Могут быть проиндексированы. В их число входят вычисляемые столбцы, определенные как вызовы методов для определяемого пользователем столбца типа данных CLR, если эти методы отмечены как детерминированные. Вычисляемые столбцы, производные от типов данных LOB, могут быть проиндексированы или как ключевые, или как неключевые столбцы, если тип вычисляемого столбца может быть использован в качестве столбца ключа индекса или неключевого столбца.	Создание индексов вычисляемых столбцов
Столбцы Varchar с внестрочными данными	Ключ кластеризованного индекса не может включать в себя столбцы varchar, для которых существуют данные в единице распределения ROW_OVERFLOW_DATA. Если кластеризованный индекс создается на столбце varchar и существующие данные располагаются в единице распределения IN_ROW_DATA, то последующие операции вставки или обновления для данного столбца, принудительно отправляющие данные за пределы строки, будут завершаться ошибкой.	Организация таблиц и индексов Превышающие размер страницы данные строки, превышающие 8 КБ
geometry	Возможно индексирование с помощью нескольких пространственных индексов.	Типы пространственных данных

Дополнительные сведения

При создании индекса следует учитывать и некоторые дополнительные факторы:

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

Требования к свободному месту на диске

Объем свободного места на диске, необходимый для хранения индекса, зависит от следующих факторов:

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

Вопросы производительности

Время физического создания индекса во многом зависит от подсистемы жестких дисков. Факторы, которые в связи с этим следует принять во внимание, указаны ниже:

Модель восстановления базы данных. Модель восстановления с неполным протоколированием обеспечивает более высокую производительность и требует меньше места для хранения журнала во время создания индекса, чем модель полного восстановления. Однако восстановление с неполным протоколированием уменьшает гибкость восстановления на определенный момент времени. Дополнительные сведения см. в разделе Выбор модели восстановления для операций с индексами.
Уровень массива RAID, используемого для хранения файлов баз данных и журналов транзакций. Как правило, более высокая пропускная способность ввода-вывода достигается при использовании RAID-массивов с чередованием.
Число дисков в RAID-массиве, если эта технология используется. Увеличение числа дисков в массиве сопровождается пропорциональным повышением скорости передачи данных.
Место хранения промежуточных результатов сортировки данных. Использование параметра SORT_IN_TEMPDB может сократить время, требуемое для создания индекса, если база данных tempdb находится на дисках, отличных от тех, на которых находится пользовательская база данных. Дополнительные сведения см. в разделе База данных tempdb и создание индекса.
Создание индекса в режиме в сети или вне сети.

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

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