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

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

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

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

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

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

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

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

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

Типы данных

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

Тип данных	Использование в индексе	Дополнительные сведения
Определяемый пользователем тип данных 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 КБ

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

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

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

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

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

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

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

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

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

Создание ограничения PRIMARY KEY или UNIQUE при создании таблицы

CREATE TABLE

Создание ограничения PRIMARY KEY или UNIQUE в существующей таблице

ALTER TABLE

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

CREATE INDEX

См. также

Основные понятия

Создание кластеризованных индексов
Создание индексов с включенными столбцами
Создание некластеризованных индексов
Создание уникальных индексов
Реализация секционированных таблиц и индексов
Ограничения PRIMARY KEY
Ограничения UNIQUE
Индексы для столбцов типа данных xml

Другие ресурсы

ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
Приступая к работе с компонентом Full-Text Search

Справка и поддержка

Получение помощи по SQL Server 2005