2.2. Индексы
На индексы в таблице возлагаются две задачи:
q ускорение поиска по индексированным столбцам;
q гарантия уникальности значений, хранящихся в индексируемых столбцах.
2.2.1.
Общие соображения
Принцип индексирования довольно прост. Пояснить его легче на примере массивов. Пусть имеется массив A[i], где I может меняться от 1 до N. Тогда массив B[i] будет индексировать массив A, если фрагмент
FOR I:=1 TO N DO WRITELN(A[B[I]]);
выведет нам все элементы массива A. Если при этом элементы выводимого массива будут упорядочены, то массив B может быть использован для ускоренного поиска элементов массива A. Этот ускоренный поиск называется еще двоичным или бинарным поиском и может быть представлен листингом 2.1.
Листинг 2.1
K1:=1;
{переменная C содержит искомый элемент
массива}
WHILE K1-K2>1 DO
BEGIN
I:=(K1+
IF
C>A[B[I]] THEN K1:=I ELSE
END
Обратим внимание, что поиск всегда начинается с одного и того же элемента, определяемого начальным значением индекса I. Дальнейшее шаги зависят от элемента C, который мы ищем в массиве. Множество путей поиска (значений индекса I) образует разветвленную структуру, называемую B-деревом, или сбалансированным деревом (balanced tree).
На рис. 2.3 схематически изображено четырехуровневое B-дерево. Уровень 1 дерева соответствует корню дерева. Промежуточные уровни 2 и 3 называют узловыми. Здесь располагаются ссылки на другие уровни дерева. Уровень 4 называется уровнем листьев. На уровне листьев содержатся указатели на данные. Мы видим, что для получения любого данного следует пройти одинаковый путь от корня до соответствующего листа. Это и есть основная отличительная черта сбалансированного дерева.
Рис. 2.3. B-дерево
Наш алгоритм (см. листинг 2.1) не предполагал построение сбалансированного дерева. Для поиска в упорядоченном массиве это совсем не нужно. Дело в том, что за один шаг мы всегда можем получить любой элемент массива по его индексу. Однако структура данных в базе SQL Server отлична от линейной структуры массива (см. разд. 2.5). Данные хранятся на страницах размером 8 Кбайт. Причем между страницами одной таблицы могут быть свободные страницы или страницы другой таблицы. Поэтому, чтобы осуществлять эффективный поиск данных на этих страницах, индексы изначально строятся в виде B-дерева.
Обратимся опять к алгоритму из листинга 2.1. На каждом шаге поиска происходит обращение к индексируемому массиву. Это существенный момент. Если речь пойдет об индексации данных, располагаемых в файле, то обращение на каждом шаге может сильно замедлить поиск. По этой причине в структурах современных индексов хранится и само значение атрибута, по которому осуществляется индексирование. Разумеется, от этого размер индекса увеличивается, но поиск осуществляется быстрее.
Индексы уже давно взяты на вооружение современными СУБД. Посредством индексов доступ к данным, хранящимся в таблицах, ускоряется многократно. Но есть и обратная сторона медали. При изменении содержимого таблицы (вставки строк, удаление строк, обновление столбцов, входящих в атрибут, по которому произведено индексирование) требуется дополнительное время для перестройки содержимого и структуры индексов. Можно вывести простую закономерность: чем больше индексов определено в таблице, тем быстрее выполняются операции поиска в этой таблице и тем медленнее операции изменения данных в таблице. Следовательно, количество используемых в базе данных индексов будет определяться характером информационной системы. Можно выделить две крайних ситуации: большое количество операций выборки данных и статические таблицы или часто меняющееся содержимое таблиц и не большое количество операций выборки. Реальные системы, как правило, находятся где-то посредине, и искусство разработчика заключается в поиске компромиссного решения.
2.2.2. Типы
индексов
Некластерные индексы
Некластерный индекс является самостоятельной структурой (объектом), принимающей форму B-дерева (см. предыдущий раздел). Листья такого индексного дерева (см. рис. 2.3) содержат ссылки на страницы данных таблицы. Все узлы некластерного индекса содержат значения ключа, по которому произведено индексирование, т. е. поиск по индексу осуществляется без обращения к данным таблицы. Некластерные индексы можно создавать не только для таблиц, но и представлений (Views) — виртуальных таблиц (см. разд. 4.4).
В некластерный индекс можно включать неключевые столбцы таблицы. Неключевые столбцы добавляются к листьям индекса. Это ускоряет выполнение запросов, если все столбцы, указанные в запросе, входят в индекс илибо как ключевые, либо как неключевые элементы. Такой результат объясняется тем, что поиск по таблице, по сути, осуществляется без обращения к самой таблице.
Рассмотрим кратко некоторые характеристики индекса, которые доступны при работе с этим окном.
q При помощи свойства Columns можно определить, какие столбцы будут входить в атрибут, по которому будет производиться индексирование. Имейте в виду, что порядок следования столбцов в составном атрибуте также важен. Кроме этого, можно установить также порядок сортировки индекса (Ascending или Descending).
q Свойство Is Unique определяет, будет ли данный индекс гарантировать уникальность атрибуту, по которому он устанавливается.
q Свойства (Name) и Description позволяют задать уникальное имя индекса и записать комментарий для данного индекса.
q Свойство Create As Clustered позволяет создавать кластерные индексы.
q Свойство Filegroup or Partition Scheme Name определяет имя группы файлов или имя схемы секции (см. разд. 2.6).
q Свойство Partition Column List содержит список столбцов, которые используются в секционной функции (см. разд. 2.6).
q Fill Factor — фактор заполнения страницы индекса. Фактор заполнения индекса измеряется в процентах и определяет, какая часть листовых страниц индекса будет заполнена. Если страницы заполнены на 100%, то для добавления новых строк индекса, что потребуется, если содержимое таблицы будет изменено, придется перестраивать весь индекс, добавляя новые листовые страницы. С другой стороны, если листовые страницы заполнены почти полностью, то индекс более компактен, что оптимизирует выборку (поиск) из соответствующей таблицы. Таким образом, варьируя фактором заполнения, можно увеличивать скорость тех или иных операций над таблицей.
q Pad Index — с помощью этого свойства можно предписать серверу резервировать свободные строки на узловых страницах индекса (см. рис. 2.3 и комментарий к нему).
q Ignore Duplicate Keys — игнорировать дублирующие ключи. Если этому свойству присвоить значение Yes (при условии, что индекс уникален), то вместе с отменой операции, приводящей к дублированию строк, произойдет откат всей транзакции. В противном случае операция дублирования будет отменена, но откат транзакции не произойдет.
q Re-compute Statistics — установка этого свойства (значение Yes) приводит к автоматическому перестроению статистики. Статистика необходима оптимизатору запросов сервера. Отмена автоматической перестройки статистики может повысить скорость выполнения операций вставки и изменения данных, но отрицательно скажется на скорости выполнения запросов чтения к таблице.
Мы еще вернемся к перечисленным и другим свойствам индексов, когда в главе 3 будем рассматривать команды CREATE INDEX и ALTER INDEX.
При работе с окном управления индексами (см. рис. 2.4) вы обнаружите, что некоторые свойства недоступны для изменения. Это не должно вас расстраивать: все их можно изменять при помощи команд CREATE INDEX и ALTER INDEX. Но и это еще не все.
Кластерные индексы
В таблице может существовать лишь один кластерный индекс. По умолчанию кластерный индекс создается, когда определяется первичный ключ. Кластерный индекс встроен в саму таблицу, листовая часть индекса является страницами данных самой таблицы.
Рис. 2.6. Структура кластерного индекса
На рис. 2.6 представлена схема кластерного индекса. Прямоугольниками изображены страницы памяти, где хранятся и данные, и индексы. Обратим внимание на следующее.
q Таблица и индекс неразрывно связаны. По сути, таблица стала частью (листовой) индекса.
q Страницы в кластерном индексе связаны не только по вертикали, как в обычном индексе, но и по горизонтали, т. е. образуют двунаправленный список.
q Поскольку таблица обычно снабжается первичным ключом, что вызывает автоматическое создание кластерного индекса, то может создаться впечатление, что кластерный индекс предполагает уникальность атрибута, по которому он создан. В действительности, это совсем не так, и вы можете при желании создавать неуникальные кластерные индексы.
Свойства, которые мы разбирали для обычных индексов, будут справедливы и для кластерных индексов, и мы вернемся к ним к следующей главе, когда будем рассматривать программные способы управления индексами.
Индексы xml
В SQL Server 2005 появился новый тип данных — xml. Если в таблице есть столбцы, имеющие тип xml, то для таблицы могут быть созданы xml-индексы. Эти индексы увеличат скорость запросов к xml-данным, но могут замедлить операции обновления этих данных.
Создание xml-индексов состоит из двух этапов.
q Создание основного (Primary) xml-индекса. Индекс может быть создан только при условии, что в таблице уже существует кластерный индекс, созданный по первичному ключу.
q Создание дополнительных (Secondary) xml-индексов. Могут быть три типа дополнительных индексов:
·
PATH-индекс;
·
PROPERTY-индекс;
· VALUE-индекс.
Полнотекстовые индексы
В SQL Server 2005 заложены возможности ускоренного поиска по текстовым полям таблиц. Поиск базируется на концепции полнотекстовых индексов (full-text indexes). Особенно эффективно данный механизм будет работать, если в ваших таблицах хранятся большие объемы текстовой информации (типы столбцов char, varchar, nvarchar). В полнотекстовых индексах индексируются отдельные слова и фразы, расположенные в текстах, так что вы сможете быстро найти все строки таблицы, где в проиндексированных текстовых полях содержится заданное вами сочетание слов. Полнотекстовый поиск может быть также настроен на поиск в структурированных текстах, например документах MS Word, которые хранятся в столбцах типа varbinary и image.
Для того чтобы создавать полнотекстовые индексы для таблиц выбранной базы данных, следует вначале в окне свойств базы данных на вкладке Files установить флаг Use full-text indexing. Далее имеются два пути.
q Обратиться в раздел Full Text Catalogs в Object Explorer. Потом щелкнув в разделе правой кнопкой мыши, выбрать в контекстном меню New Full-Text Catalog. Затем следует определиться с именем индекса и каталогом, где будут храниться файлы индекса. После этого в разделе Full Text Catalogs появится новая строка с именем индекса. Щелкнув по ней правой кнопкой мыши и выбрав пункт меню Properties, можно приступить к настройке индекса. В частности, в окне можно указать таблицы и столбцы таблицы, которые будут участвовать в индексировании. Принимаются следующие типы столбцов: char, varchar, nchar, nvarchar, varbinary и image. На последних двух типах данных следует остановиться отдельно. Для того чтобы включить их в полнотекстовый индекс, в таблице должен быть еще один текстовый столбец, содержащий тип данных, которые будут храниться в индексируемом столбце.
q Полнотекстовый индекс можно создать и другим способом. Для этого в разделе Tables следует щелкнуть правой кнопкой мыши по пункту меню Full-Text index | Define Full-Text index.... Данный пункт меню будет доступен при условии, что для таблицы полнотекстовый индекс не был ранее создан. В противном случае вы можете выбрать пункт меню Full-Text index | Properties, чтобы изменить параметры уже существующего индекса. При создании нового индекса в вашем распоряжении будет мастер создания полнотекстовых индексов (Full-Text indexing wizard).
Процесс создания и поддержка полнотекстового индекса называется заполнением (population). SQL Server поддерживает следующие типы заполнения индекса.
q Полное заполнение (Full Population). Данный тип заполнения индекса осуществляется при его создании. Предполагается, что в дальнейшем его поддержание осуществляется другими типами заполнения.
q Заполнение на основе отслеживания изменений (Change Tracking Based Population). Для тех индексированных таблиц, где установлен данный тип заполнения, SQL Server поддерживает запись измененных строк. Записанные изменения затем переносятся в полнотекстовый индекс. Замечу, что данный тип заполнения будет работать, если предварительно уже было произведено заполнение индекса. Если вы используете данный тип заполнения, следует указать, каким образом эти изменения будут переноситься в индекс:
· автоматический перенос — сервер сам переносит изменения, после их появления;
· ручной перенос — администратор должен время от времени обновлять индекс;
· перенос по расписанию — можно создать расписание, и SQL Server Agent будет периодически, по расписанию, производить обновление.
q Инкрементное заполнение на основе версии строки (Incremental Timestamp Based Population). Для того чтобы использовать данный тип заполнения, в таблице должен быть столбец с типом timestamp. Для запуска этого типа заполнения используем пункт контекстного меню Full-Text Index | Start Incremental Population, щелкнув предварительно по строке с именем таблицы в разделе Tables в окне Object Explorer.
