Запросы с большими результирующими наборами:

SELECT * FflOM customers

Для большого набора не создавайте покрывающий индекс, в кото-

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

i.uu I ic 1 с I ьуюших значений.

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

Эффективность индексов

Нелегко конструировать и подходящие индексы БД, особенно если типич-

ные запросы к БД или структура БД постоянно меняются. В SQL Server имеется мастер Index tuning, помогающий выбрать индекс для таблицы или представления. Мастер Index

Tuning анализирует репрезентативный образец операций, выполняемых над БД (ее рабо-

че зки), и рекомендует идеальный индекс для этой БД. При изменении нагрузки БД или ее структуры следует заново запустить мастер Index Tuning для повторного анализа индексов БД. Для формирования входного образца рабочей нагрузки для мастера применяют сценарий SQL (файл с расщирением .SQL), файл SQL Profiler Trace (.TR( или -LOG), трассировочную таблицу или сценарий, выбранный в Query Analyzer.

Статистика индекса

SQL Server поддерживает статистику распределения значений используемых ин-

дексами. От этой статистики зависит точность выбора оптимизатором индекса, го для запроса. Можно отключить обновление статистики, используя конструкцию STA-TISTICS NORECOMPUTE оператора CREATE INDEX. Она отключает автоматический пересчет статистики индекса при ее устаревании. Не рекомендуется отключать статистику, если таблица или представление часто изменяется. Чтобы восстановить автоматическое обновление статистики, следует исполнить для индекса оператор UPDATE STATISTICS без указания конструкции или системную хранимую процедуру

Последняя выводит или изменяет значение параметра STATISTICS.

Более об операторе UPDATE STATISTICS и процедуре

stats рассказано в SQL Server Books Online, а о производительности индексов - в главе 14 и в Books Online.

Упражнение 2. Создание кластерного индекса

В этом упражнении вы создадите для некоторых таблиц БД кластер-

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

340 Индексы Глаяа 11

Р Создание некластерного индекса - . .

Откройте Analyzer и подключитесь к локальному серверу.

2, На панели Editor в окне Query введи re и исполните следующий код:

USE bookshopdb sp helpinuex books

На вкладке Grids панели Results показано, что для ограничения primary key с именем существует кластерный индекс

Наблюдения за работой с данными таблицы Books что нет ни-

каких причин для изменения этог дакса. Работники часто запрашивают значения столбов TiiolD. Title и Sold, чтобы сообщить покупателю о наличии той или иной книги. Значение тбиа Title может быть весьма ным. поэтому решено, что для покрытия запросов к столбцам TitielD, Title и Sold лучше всего создать некластерный

индекс.

На панели Editor в окне Query введите и исполните следующий код:

CREAT STERED INDEX Tit eSoldStatus ON : oo..-( f-itln, sold)

В магазине (с одинаковым значением поля sold) могут оказаться два издания с одинаковым названием. Поэтому требуется индекс слово опущено).

4. Если на панели Results в Query Л laiyzer отсутствует вкладка Execution Plan, щелкните Query, затем - Show Execution Plin.

5- Чтобы проверить, использует ли оптимизатор запросов некластерный индекс, на панели Editor в окне Query введите и исполните код:

SELECT titleid, title, sold books

На вкладке Grids панели Results результирующий набор.

(v Щелкните вкладку Execution Plan и наведите указатель мыши на индекс Books.-TitleSokl . В окне отобразится статистика для индекса TitleSoIdStatus. Обратите внимание на полное имя индекса, которое выводится в нижней части окна.

7. Почему оптимизатор запросов не пспил.зова.! кластерный индекс Booksjk?

Подготовка ицы к созданию нового индекса

На панели Editor в окне Query вводите и исполните следующий код: orders

На вкладке Grids панели Results эано, чт rs pk - кластерный, уникальный индекс ащий ключ индекса Orders pk - имя ограничения primary key.

Предположим, сотрудники обычно делают запросы к столбцам и

таблицы Orders одновременно, поэтому следует создать кластерный индекс, включающий столбцы OrderlD и < usioinelD. Для этото необходимо сделать кластерный индекс Orders pk некластерным. 2. На панели Editor в окне Query вв4:дите и исполните следующий код: CREATE NCLUblLHED INDEX Qrdeis p.< ONi.i ders(orderld) WITH DflOP.EXISTING

На вкладке Grids панели Results выводится сообщение об ошибке, которое предупреждает о невозможности кластерного индекса в некластерный.

Чтобы заменить кластерный индекс некластерным, следует удалить индекс и создать его заново. Поскольку индекс является частью ограничений primary key и foreign key другой сначала необходимо удалить эти ограничения с помощью

ALTER TABLE. Первым следует удалить ограничение foreign key.

3. На панели Editor в окне введите и исполните следующий код:

ALTER TABLE hnokr.rdpr s DROP CONSTRAINT orderid fk

ook Orders удаляется ограничение foreign key. Все ограничения foreign key (мы восстановим их позже) необходимо удалить, прежде чем удалять ограничение

primary key.

Затем мы удалим ограничение primary key.

4. На панели Editor в окне Query введите и исполните следующий код:

ALTER TABLE orders

DROP CONSTRAINT orders pk

Из таблицы Orders удаляется ограничение primary key.

В следующем задании мы восстановим это ограничение и ограничение foreign key после создания кластерного индекса для

► Создание кластерного индекса

J, На панели Editor в окне Query введите и исполните следующий код:

CREATE UNIQUE CLUSTERED INDEX CustOdierOrder

ON ordersCcustomerid, orderid) WITH FILLFACTOR = 70

Этот оператор создает кластерный индекс CustomerOrder с

ключом, состоящим из двух столбцов - и

2. Исполните код.

В таблиие Orders создается кластерный индекс CustomerOrder. Для повыщения эффективности можно сначала создать кластерный индекс, а затем - некластерный. Этот способ эффективнее, поскольку некластерный индекс в этом случае создается лишь

а в качестве закладок ключи кластерного индекса.

Теперь, когда создан кластерный индекс, восстановите ограничения primary key и

foreign key. Начните с primary key.

3. На панели Editor в окне Query введите и исполните следующий код:

ALTER TABLE orders

ADD CONSTRAINT orders pk

PRIMARY KEY NONO! M.RF Mori ri)

В таблице Orders воссоздается ограничение primary key. Это ограничение генерирует

некластерный индекс. Обратите внимание на ключевое слово NONCLUSTERED. Оно

стало поскольку в таблице уже существует индекс. Здесь оно ис-

пользовано лишь как средство описания намерений программиста.

После primary key можно восстановить ограничение foreign key.

4. На панели Editor в окне Query введите и исполните код:

ALTER TABLE bookorders ADD CONSTRAINT orderid fk