Планирование использования, создание и управление индексами

 

 

Цель работы– изучение назначения, типов и способов определения индексов, синтаксиса и семантики команд языка Transact-SQL для их создания, переименования, перестройки, создания статистики для оптимизации индексов и удаления, а также приобретения навыков создания и управления индексами с помощью команд, системных хранимых процедур, графических средств утилита Enterprise Manager и мастеров Create Index Wizard и Index Tuning Wizard.

 

 

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

В SQL Server 2000 реализованы эффективные алгоритмы поиска нужного значения в упорядоченной последовательности данных. Одних из таких алгоритмов является метод деления пополам, на каждом шаге которого данные делятся пополам и путем сравнения со средним значением определяется, найдено ли требуемое значение, а если не найдено, то решается, в какой половине данных оно находиться, при этом на каждом шаге происходит сокращение интервала поиска.

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

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

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


В MS SQL Server 2000 реализованы следующие типы индексов:

а) кластерные индексы;

б) не кластерные индексы;

в) уникальные индексы.

 

 

Не кластерныеиндексы являются наиболее типичными индексами. В отличие от кластерных, они не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки (указатели – row locator), которые включают в себя:

а) информацию об идентификационном номере файла (ID file), в котором храниться


строка;


 

б) идентификационный номер страницы данных;

в) номер соответствующей строки на странице;

г) содержимое столбца.

При определении кластерногоиндекса физическое расположение данных


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

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

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

При определении индекса надо задавать параметр, который будет устанавливать плотность записи данных на странице, - фактор заполнения (fill factor). Его значение определяет, какой процент доступного пространства индексных страниц будет заполнен данными при создании индекса, а какой процент будет заполняться постепенно по мере загрузки данных в таблицу. Если таблица используется только для чтения, то значение этого параметра надо задавать близким к 100%, если таблица часто обновляется и дополняться, то значение фактора дополнение должно быть задано небольшим.

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

а) с помощью команд языка Transact – SQL, которые предоставляют пользователю максимум возможностей;

б) с помощью Enterprise Manager;

в) средствами мастеров Create Index Wizard и Index Tuning Wizard.

Создание индекса командами языка Transact – SQL производится следующим образом:

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

(если не указан параметр NONCLUSTERED);

б) автоматически при реализации ограничения целостности UNIQUE, когда создается не кластерный индекс;

в) автоматически при создании таблицы, когда для столбца указываются параметры

CLUSTERED или NONCLUSTERED;

г) с помощью специальной команды CREATE INDEX.

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

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


CREATE [UNIQVE] [CLUSTERED\NONCLUSTERED] INDEX

Имя индекса

ON {Имя индекса\Имя представления} (column[ASC\DESC] [,…n])

[WITH [PAD_INDEX]

[[,] FILLFACTOR = Фактор заполнения] [[,] IGNOR_DUP_KEY]

[[,] DROP_EXISTING]

[[,] STATISTICS_NORECOMPUTE] [[,] SORT_IN_TEMP_DB]

]

[ON Имя группы файлов]

 

Если автоматическое создание кластерного индекса не предполагается, то перед созданием не кластерного индекса надо создать кластерный, так как не кластерный индекс всегда ссылается на кластерный. Можно создать 249 не кластерных индексов с использованием до 16 столбцов в каждом индекс, при этом общая длина индекса не должна превышать 900 байтов. Столбцы с типами данных text, ntext или image не допускаются. Порядок столбцов при определении ключа очень важен. Желательно их указывать в порядке возрастания длины данных.

Параметры ASC и DESC определяют метод сортировки ключевых элементов –

соответственно по возрастанию или по убыванию.

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

Параметр IGNORE_DUP_KEY не приводит к отказу транзакции при добавлении дублирующих строк, при этом сами дублирующие строки игнорируются, и сервером выдается сообщение об ошибке.

Остальные параметры команды используются редко.

Созданный тем или иным способом индекс, можно переименовать с помощью системной хранимой процедуры sp_rename, можно его удалить командой DROP INDEX или перестроить для упорядочивания свободного места на индексных страницах, используя команды DROP INDEX и CREATE INDEX или команду DBCC DBREINDEX. Для получения информации об индексах используется системная хранимая процедура

 

sp_helpindex [@objname]‘name’,

 


 

команду


где name – имя рассматриваемой таблицы текущей базы данных.

Для просмотра индивидуальных свойств конкретного индекса следует применять


 

INDEXPROPERTY (table_ID, index, property),

 

в которой table_ID = OBJECT_ID (имя таблицы) – идентификационный номер таблицы, index – имя индекса, а property – рассматриваемое свойство: Index Depth (глубина индекса), Is Clustered (кластерный), Is Unique (уникальный) и др.

Для сбора и анализа статических данных при использовании индексов используются следующие команды и процедуры: CREATE STATISTICS, UPDATE STATISTICS, sp_autostats, sp_statistics и др.

 

Задание 1.Создать уникальный кластерный индекс для столбца au_id таблицы authors

базы данных pubs с расположением его на первичной группе файлов, выполнив команду:

 

 

CREATE UNIQVE CLUSTERED INDEX VEKCL_auidind

ON [dbo].[authors](au_id)

WITH DROP_EXISTING -- с предварительным уничтожением

-- индекса с таким же именем


ON PRIMARY.

 

 

Задание 2.Создать составной не кластерный индекс для столбцов au_fname и au_lname

таблицы authors базы данных pubs, выполнив команду:

 


 

 

индекса;


CREATE UNIQVE NONCLUSTERED INDEX index_authors_name

ON authors (au_fname, au_lname ) WITH FILLFACTOR = 30,

STATISTICS_NORECOMPUTE -- статистика создается один раз при построении

 

SORT_IN_TEMPDB -- использовать временную базу для сортировки; ON PRIMARY.


 

 

Задание 3.Используя утилиту Enterprise Manager, создать индексы, рассмотренные в заданиях 1 и 2, предварительно удалив их командой DROP INDEX.

 

Задание 4.Используя мастер Create Index Wizard, создать индексы, рассмотренные в третьем задании, предварительно их удалив с помощью Enterprise Manager.

 

 

Задание 5.Используя системную хранимую процедуру sp_rename, переименовать индексы, созданные в четвертом задании.

 

 

Задание 6.Используя системные хранимые процедуры sp_helpindex и sp_spstatistic,

получить сведения об индексах и эффективности их использования.