в базе данных в связи с применением индексов тех или других типов. Чтобы полупить полное представление о том, при каких обстоятельствах индекс определенного типа становится наиболее подходящим (или неподходящим), необходимо полностью разобраться в том, какие операции фактически выполняются в процессе обработки данных с помощью индексов.

Выбор правильного расположения столбцов в индексе

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

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

Указанный подход является вполне оправданным. В качестве весьма нагладного примера рассмотрим телефонный справочник. В телефонном справочнике данные об абонентах отсортированы в первую очередь по фамрыиям, а затем по именам, и это вполне оправдано. Разве можно найти в таком справочнике абонента, если известно только имя, скажем, Фрэд? С другой стороны, если известно лишь то, что абонент имеет фамилию Блейк, то такой справочник поможет по крайней мере сузить область поиска.

Одна из наиболее распространенных ошибок в создании индексов, которая обнаруживается на практике, обусловлена тем, что, по мнению многих разработчиков, достаточно включить все столбцы в один индекс и использовать его во всех ситуациях. Но в действительности применение такого подхода приводит лишь к дублированию хранимых данных. Если в конструкции JOIN, ORDER BY или WHERE запроса не упоминается первый столбец индекса, то индекс полностью игнорируется.

Удаление индексов

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

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

DROP INDEX <table or view name>.<index name>

После выполнения этого оператора индекс становится недоступным.

Использование программы Database Engine Tuning Advisor

Программисты, которые достаточно полно изучили все сведения, касающиеся использования индексов, фактически не ощущают потребности в применении программы Database Engine Tuning Advisor, но эта программа все равно может оказаться весьма полезной. Функционирование программы Database Engine Tuning Advisor основано на том, что берется файл регистрации рабочей нагрузки, созданный с использованием программы SQL Server Profiler (которая будет описана в главе 24), и осуществляется поиск информации, на основании которой может быть принято решение о том, какие индексы являются наиболее приемлемыми в конкретной системе.

Программа Database Engine Tuning Advisor входит в состав инстр)ПУ1ентальных средств, доступ к которым предоставляется с помощью меню Tools программы SQL Server Management Studio. Кроме того, программа Database Engine Tuning Advisor может быть вызвана на вьшолнение с помощью отдельного элемента меню программ в меню Start операционной системы Windows. Как и в отношекир! большинства других инструментальных средств настройки, автор не рекомендует использовать это инструментальное средство в качестве единственного способа принятия решения о том, какие индексы должны быть созданы, но эта программа может оказаться весьма удобной с той точки зрения, что некоторые предлагаемые ею рекомендации моппг оказаться не вполне очевидными.

Сопровождение индексов

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

Сказанное выше вовсе не означает, что автор предлагает pa3pa6oT4HKv занять какую-то должность в отделе технического сопровождения заказчика. Речь идет о чем-то гораздо более важном - о планировании сопровождения.

При сопровождении индексов приходится решать две проблемы:

устранять последствия разбиения страниц;

устранять фрагментацию.

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

Фрагментация

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

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

Фрагментация базы данных возникает в результате того, что объем базы данных возрастает, происходит разбиение страниц, а затем в конечном итоге часть данных удаляется. Безусловно, механизм сопровождения В-дерева неплохо справляется с задачей обеспечения сбалансированности индекса при выполнении операций вставки данных, но практически не позволяет достичь той же цели, когда удаляются данные. В результате может сложиться такая ситуация, что на отдельных страницах будут находиться всего лишь одна-две строки. Иными словами, многие страницы будут содержать небольшое количество данньгх, составляюш,ее лишь незначительную долю от того количества данных, которые они могли бы содержать.

Фрагментация становится причиной возникновения целого ряда проблем. Первая проблема является вполне очевидной - непроизводительное расходование внешней памяти. Напомним, что в СУБД SQL Server распределение пространства осуществляется путем вьщеления cpeiay целого экстента. Даже если требуется записать в память только одну страницу с единственной строкой, распределяется целый экстент. Безусловно, если происходит ввод новых данных, то в СУБД SQL Server освободившиеся страницы в экстенте снова заполняются, но если, например, объем данных в таблице или индексе уменьшается, то свободные страницы в экстенте остаются неиспользуемыми.

Вторая проблема в большей степени касается снижения производительности операций доступа к данным. Дело в том, что если база данных фрагментирована, то строки данных в ней не сосредоточены в одном месте, а распределены по всему пространству памяти, поэтому в процессе выборки данных возникают дополнительные издержки. Скажем, вместо загрузки одной страницы и получения всех искомых десяти строк в СУБД SQL Server для получения той же информации приходится загружать десять отдельных страниц. Дело в том, что невозможно прочитать лишь требуемую строку, ведь доступ к строкам предоставляется только после считывания страницы, на которой они находятся. А чем больше страниц приходится считывать для получения одних и тех же данных, тем больший объем работы приходится при этом выполнять.

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

Таким образом, увеличение степени фрагментации приводит к уменьшению производительности операций выборки данных, но вместе с тем способствует значительному повышению производительности операций вставки данных. Из этого следует, что для систем OLAP фрагментация является неблагоприятным фактором, а в сис-темг1х OLTP играет двоякую роль.

Получение сведений о фрагментации и оценка вероятности разбиения страниц

в программном обеспечении SQL Server всегда была предусмотрена возможность определить, какова степень заполнения страниц и экстентов в базе данных. А в версии SQL Server 2005 корпорации Microsoft удалось намного расширить состав применяемых