ГЛАВА 11

Индексы

Занятие 1. Структура индексов 322

Занятие 2. Создание и администрирование индексов 332

Закрепление материала 344

В этой главе /

Индексы - это объекты базы данных, повышающие производительность запросов. В этой главе описана структура и индексов, а также типы индексов и их свой-

ства. Вы научитесь определять, когда индексы, узнаете, как

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

Прежде всего

Для изучения материалов этой главы вам понадобится:

SQL Server 2000 Enterprise Edition, установленный на компьютере под управлением

Windows 2000 Server;

возможность зарегистрироваться на компьютере под управлением Windows 2000 Server и SQL Server в качестве администратора;

результаты упражнений из глав 3, 4, 5 и 7.

Занятие 1. Структура индексов

Структура индексов ориентирована на быстрый возврат наборов. SQL

Server живает два тип докюв; кластерные и некластерные. Индексы могут быть созданы для одного или нескольких столбцов таблицы или представления. Индексированные таблицы поддерживаются №зми редакциями SQL Server 2000, а индексированные представления - только SQL Server Enterprise Edition и SQL Server Developer Edition. Ин-использования ресурсов и производительность при поиске дан-

ных зависит от свойств индекса. запросов использует индекс, если это по-

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

Изучив материал этог гия, вы сможете:

описать назначение и структуру индексов;

показать отличия между и некластерными индексами;

описать важные характеристики индексов;

просматривать свойства индексов с помощью хранимых процедур, Query Analyzer

и Enterprise Manager,

Продолжительность занятия - около 40 минут.

Назначение и структура индексов

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

нирование таблицы). Индекс БД формируется из значений одного или нескольких столбцов таблицы (которые в этом случае зываются ключом индекса) и указателей на соответ-записи таблицы. При запроса с ключом индекса за-

просов использует индекс для поиска записей, запросу.

Диспетчер индексов (SQL Server Index Manager) структурирует индекс в виде сбалансированного дерева, или ерева (Balanced tree, B-tree), которое по виду напоминает передерево: корень ется сверху, а листовой уровень - внизу; между ними расположен ряд промежуточные ..ней. Каждый объект в структуре дерева - это группа отсортированных ключей индекса, которая швается страницей индекса.

Структура В-дерева способствует повышению скорости и сотласованности при испол-запросов, тщательно ширину и глубину дерева по мере увеличения ин-

декса. Упорядочение индекса по значениям его ключей также повышает производительность запросов. SQL Server начинает исполнение всех запросов на поиск данных с корня В-дерева, затем он проходит дальше, пока не достигнет листового уровня. Ширина и глубина дерева зависит от числа табличных записей и размера ключа индекса. Последний называется шириной ключа. Дерево, созданное на таблице со множеством записей и большим ключом индекса, получается оким и глубоким. Чем меньше дерево, тем быстрее возвращается результат поиска.

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

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

Для эффективного поиска записей оптимизатор запросов использует индекс, соответствующий запросу. При исполнении следующего запроса оптимизатор по всей вероятности воспользуется индексом идентификаторов:

SELECT FROM Customers WHER )niencl = 798

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

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

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

Типы индексов

Существует два типа индексов: кластерные и некластерные. Структура обоих типов -В-дерево. На листовом уровне В-дерева кластерный индекс содержит записи таблицы, а некластерный - указатели на записи. Если на таблице построен кластерный индекс, то некластерный можно при поиске данных как В большин-

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

Кластерные индексы

У таблицы или представления должен быть только один кластерный индекс, так как ключ кластерного индекса физически упорядочивает таблицу или представление. Этот тип индексов особенно эффективен при исполнении запросов, поскольку записи (или страницы данных) хранятся на листовом уровне В-дерева. Порядком сортировки и местом хранения кластерный индекс напоминает словарь с его алфавитным порядком сортировки слов и наличием определений после каждого слова.

При создании ограничения primary key в таблице, где еще нет кластерного индекса, SQL Server для создания ключа кластерного индекса столбец с первичным клю-

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

primary key создается некластерный индекс. Столбец с первичным ключом полезен для

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

структуры для поиска работают более эффективно.

Примечание Для столбца с ограничением unique автоматически создается некластерный индекс.

Чтобы принудительно задать тип индекса, создаваемого для столбца или столбцов, следует задать конструкцию CLUSTERED или NONCLUSTERED в операторе CREATE TABLE, ALTER TABLE или CREATE INDEX. Предположим, вы создаете таблицу Persons, состоящую из столбцов PersonID, FirstName, LastName и SocialSecurityNumber. Для столбца PersonID определено ограничение primary key. Чтобы сделать столбец SocialSecurityNumber кластерным индексом, а столбец PersonID - некластерным индексом, следует использовать такое определение таблицы: