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

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

Аннотация: Чем больше становится ваша база данных, тем, вероятнее всего, возрастает количество и сложность запросов. Для повышения эффективности производительности запросов путем снижения количества операций ввода-вывода используются индексы. Некоторые аспекты из теории программирования необходимо знать для лучшего усвоения материала. Рассматриваются простые и составные индексы, их отличие и применение. Проводится обзор мастеров: Create Index Wizard и Full-Text Indexing Wizard. И, конечно же, использованию T-SQL уделено немало разделов.

Индексы – одно из самых мощных средств, доступных разработчику базы данных. Индекс – это вспомогательная структура, позволяющая вам повышать производительность запросов за счет снижения количества операций ввода-вывода, необходимых для поиска запрошенных данных; т.е. индекс позволяет системе Microsoft SQL Server 2000 находить данные, используя меньшее число операций ввода-вывода, чем при поиске данных путем доступа только к таблице базы данных. Если для поиска строки данных вы используете индекс таблицы базы данных, SQL Server может быстро определить, где хранятся эти данные и сразу считать эти данные. Таким образом, индексы таблиц базы данных во многом похожи на индексы (алфавитные указатели) в книгах: в обоих случаях обеспечивается быстрый доступ к большим объемам информации.

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

Что такое индекс?

Как уже говорилось, индекс – это вспомогательная структура данных, используемая системой SQL Server для доступа к данным. В зависимости от типа индекса он хранится вместе с данными или отдельно от данных. Независимо от типа все индексы действуют одинаковым в своей основе способом, о котором вы узнаете в этом разделе.

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

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

Если использовать в качестве аналога книгу, то индекс действует следующим образом: предположим, что началом индекса (алфавитного указателя) является страница, где указаны номера страниц для статей индекса на букву "a", "b", "c" и т.д. Затем предположим, что эти страницы содержат номера страниц для статей в диапазонах aa-ab, Ас-ad, ae-af и т.д., а соответствующие страницы – номера страниц для записей в диапазонах aaa-aab, aАс-aad, aae-aaf и т.д. При подобной организации вы можете быстро найти то, что вам нужно, с использованием относительно небольшого количества операций поиска. Такая структура аналогична индексу таблицы базы данных, когда первой страницей является корневой узел.

Корневой узел и узлы-ветви

Рис. 17.1. Корневой узел и узлы-ветви

Как и корневой узел, каждый узел-ветвь содержит ряд индексных строк в структуре индексной страницы. Каждая индексная строка указывает на другой узел-ветвь или на узел-лист (конечный узел) (рис. 17.2). Узел-лист является последним уровнем индекса. В отличие от корневого узла каждый узел-ветвь содержит также связанный список узлов-ветвей того же уровня. Иными словами, узел "знает" о смежных узлах и об узлах более низкого уровня.

 Дерево поиска с узлами-ветвями и узлами-листьями

Рис. 17.2. Дерево поиска с узлами-ветвями и узлами-листьями

Как следует из названия "B-дерево", узлы-ветви разветвляются от корневого узла в древовидной форме. Каждая группа узлов-ветвей одного уровня в древовидной структуре называется уровнем индекса (рис. 17.3). Количество операций ввода-вывода, которое требуется для достижения узлов-листьев (узлов самого нижнего уровня дерева), зависит от количества уровней индекса. Если таблица базы данных содержит лишь небольшое количество данных, то корневой узел может указывать непосредственно узлы-листья, и тогда для индекса вообще не требуется никаких узлов-ветвей (маловероятная ситуация).

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

Рис. 17.3. Уровни индекса

В некластеризованном индексе узел-лист содержит значение ключа, а также идентификатор строки (Row ID), указывающий нужную строку в таблице, или ключ кластеризованного индекса, если имеется также кластеризованный индекс по этой таблице. А в кластеризованном индексе в узле-листе находятся сами данные. (О кластеризованных и некластеризованных индексах см. раздел "Типы индексов" далее.) Количество строк в узле-листе зависит от размера индексных записей, а в случае кластеризованного индекса – от размера данных.

Примечание. Row ID – это указатель, который автоматически формируется системой SQL Server из идентификатора файла (File ID), номера страницы и номера строки данных. Используя Row ID, вы можете считывать данные с помощью всего лишь одной дополнительной операции ввода-вывода. Поскольку вы знаете, какую страницу нужно считывать, а SQL Server "знает", где эта страница находится, то она считывается в память с помощью единственного запроса ввода-вывода. Именно простота этого процесса определяет эффективность использования индексов для считывания данных и обеспечивает столь значительное повышение производительности.

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

Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Игорь Соловьев
Игорь Соловьев
Россия, Братск