Опубликован: 11.12.2006 | Доступ: свободный | Студентов: 5365 / 282 | Оценка: 4.42 / 3.86 | Длительность: 57:15:00
Лекция 17:

Создание и использование индексов

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

SQL Server поддерживает по каждому индексу статистику, описывающую степень его уникальности (или избирательности) и распределение значений ключей индексов. Оптимизатор запросов SQL Server затем использует эту статистику, чтобы определить (если это нужно), какой индекс наиболее отвечает определенному запросу. Статистика по индексам периодически обновляется по умолчанию. Но иногда индексы по прошествии времени становятся фрагментированными из-за расщеплений страниц, что приводит к физическому разбросу страниц индекса в базе данных. Это приводит к ухудшению производительности. Может также возникать дисбаланс индекса, означающий, что одна часть дерева содержит более заполненные страницы индекса, чем другая часть. Вы можете восстановить баланс и физическую связность путем перестроения индекса. Кроме того, во время перестроения индекса происходит пересчет статистики. Но вам не нужно удалять и снова создавать индекс.

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

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

Один из методов перестроения индекса состоит в ручном удалении индекса и повторном создании индекса. Для небольшой таблицы этот вариант может оказаться приемлемым. Но не используйте этот метод для таблиц среднего или крупного масштаба. Лучше всего использовать описанные в этом разделе возможности для перестроения индекса, не предусматривающего удаления и повторного создания индекса. Вот некоторые причины, являющиеся основанием для этого. Если некластеризованные индексы создаются по кластеризованной таблице, то эти некластеризованные индексы основываются на кластерных ключах. При удалении кластеризованного индекса некластеризованные индексы должны быть созданы снова, поскольку кластеризованный индекс по данной таблице уже не существует. Если кластеризованный индекс создается по этой таблице снова, то некластеризованные индексы должны быть воссозданы во второй раз! Тем самым, если вы удаляете и затем снова создаете кластеризованный индекс, вы должны два раза воссоздавать некластеризованные индексы. Если вы используете другие методы перестроения кластеризованного индекса, то некластеризованные индексы будут воссоздаваться только один раз.

Имеется два метода перестроения индекса без его удаления и повторного создания: оператор CREATE INDEX...DROP_EXISTING и использование DBCC DBREINDEX. Оба этих средства выполняют перестроение индекса за один шаг, и SQL Server "знает", что нужно реорганизовать существующий индекс. Использование этих методов позволяет избежать удаления и повторного создания некластеризованных индексов, когда вы перестраиваете кластеризованный индекс. Эти одношаговые методы также используют отсортированный порядок данных, находящихся в индексе; повторная сортировка этих данных уже не требуется.

CREATE INDEX...DROP_EXISTING используется для единовременного перестроения только одного индекса по таблице. DBCC DBREINDEX используется с именем базы данных и именем таблицы для перестроения всех индексов по этой таблице без необходимости запуска отдельных команд для каждого индекса. Синтаксис и параметры эти двух операторов см. в Books Online.

Обновление статистики по индексам

Если у вас нет времени или ресурсов для повторного создания индексов, то вы можете обновлять статистику по индексам независимо. Этот метод не столь эффективен, как перестроение индекса, поскольку индекс может быть фрагментирован, что может оказаться более серьезной проблемой, чем устаревшая статистика. При этом также предполагается, что вы отключили автоматическое обновление статистики в SQL Server. (Иначе ваша статистика будет в любом случае периодически обновляться в автоматическом режиме.) Вы можете обновлять статистику по индексам вручную с помощью оператора UPDATE STATISTICS. Она имеет следующий синтаксис:

UPDATE STATISTICS имя_таблицы
[ имя_индекса | (имя_статистики[, имя_статистики, ...] ]
[ WITH
[ FULLSCАN | SAMPLE число {PERCENT | ROWS} ]
[ ALL | COLUMNS | INDEX ]
[ NORECOMPUTE]
]

Значения в прямоугольных скобках является необязательными. Единственный обязательный параметр – это имя_таблицы. Необязательные параметры перечислены в табл. 17.2.

Таблица 17.2. Необязательные параметры, которые можно использовать с оператором UPDATE STATISTICS
Параметр Описание
имя_индекса Указывает индекс, по которому нужно пересчитать статистику. По умолчанию происходит пересчет статистики для всех индексов по данной таблице. Если указан параметр имя_индекса, происходит пересчет статистики только для этого индекса
имя_статистики Позволяет вам указывать, какую статистику нужно пересчитать. Если это значение не указано, то происходит пересчет всей статистики
FULLSCАN Указывает, что для сбора статистики будут считываться все строки таблицы. Использование этого параметра является до сих пор наилучшим способом сбора статистики, но это также наиболее "дорогостоящий" метод с точки зрения затрат ресурсов и времени
SAMPLE число PERCENT | ROWS Указывает количество или процент строк, по которым создается статистика. По умолчанию количество строк выборки определяет SQL Server. Этот параметр нельзя использовать в сочетании с параметром FULLSCАN
ALL | COLUMN | INDEX Указывает вид собираемой статистики: вся статистика, статистика по колонкам или только статистика по индексам
NORECOMPUTE Указывает, что статистика не будет в дальнейшем пересчитываться автоматически. Чтобы задать автоматический пересчет статистики, вы должны запустить этот оператор снова без параметра NORECOMPUTE или запустить хранимую процедуру sp_autostats

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

Станислав Ляльков
Станислав Ляльков
Россия, Саратов