Создание физической модели базы данных: проектирование производительности

Исключительно индексные таблицы и другие типы индексов на основе B-Tree

Исключительно индексная таблица (index-organized table) может создаваться на основе значений одной или нескольких колонок. Если требования к данным в запросе удовлетворяются на основе информации из связанного с этими данными индекса, то доступ к базовой таблице не осуществляется.

В некоторых СУБД, в частности в СУБД Oracle, исключительно индексные таблицы поддерживаются. Исключительно индексная таблица является индексом типа B-Tree БД, который одновременно исполняет роль таблицы. Все данные такой таблицы хранятся в индексе. Преимущество создания полностью индексированных таблиц состоит в экономии места хранения на диске и сокращении объема ввода-вывода, поскольку ключевые колонки нет необходимости сохранять еще раз в таблице. Результат выполнения запроса будет получен на основе данных, сохраненных в индексной таблице.

Исключительно индексная таблица в СУБД Oracle создается с помощью команды SQL CREATE TABLE, как показано в примере 20.2.

Пример 20.2.

Предположим, что требуется в отдельной таблице БД сохранять и отслеживать проблемы, возникающие по выполнении всех проектов, а так же запоминать, кто обращался и как часто возникали проблемы. Создадим исключительно индексную таблицу "Проект индексированный" (Proj_Index) для решения этой задачи, как показано ниже:

CREATE TABLE Proj_Index
( projno	char(8) NOT NULL,
 t_person	char(32) NOT NULL,
t_frequency	integer,
t_problem	varchar2(512),
   CONSTRAINT pk_ndx PRIMARY KEY( projno, t_person) )
ORGANIZATION INDEX
TABLESPACE ts_ndx1
PCTTHRESHOLD 20
INCLUDING   t_frequency
OVERFLOW TABLESPACE ts__of_ndx1;

Команда CREATE TABLE не отличается ничем от других команд создания таблиц — до тех пор, пока не встретится предложение ORGANIZATION INDEX, которое указывает СУБД на создание исключительно индексной таблицы. Для размещения индекса на диске указывается табличное пространство. Параметр PCTTHRESHOLD говорит, что оставшуюся часть строки нужно сохранять в заданном табличном пространстве — сегменте переполнения, если данная строка превышает размер физической страницы базы данных на указанное число процентов. Параметр INCLUDING определяет имя колонки, с которой строка индексной таблицы делится на две части: индексную и переполнения. Эта колонка может быть частью первичного ключа таблицы или неключевой колонкой. Все неключевые колонки, которые следуют за указанной колонкой, размещаются в сегменте переполнения, который определяется ключевым словом OVERFLOW.

В СУБД семейства MS SQL Server нет предопределенной возможности создавать исключительно индексные таблицы. Однако в ряде практических случаев в СУБД этого семейства можно создавать структуры, аналогичные исключительно индексным таблицам. Для этого может быть использован некластеризованный индекс с включенными колонками (опция INCLUDE команды CREATE INDEX ).

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

Для примера 20.2 команды создания структуры, подобной исключительно индексной таблице по своему назначению, на диалекте SQL MS SQL Server будут иметь вид, как в примере ниже.

Пример. 20.3.

Сначала создаем таблицу "Проект индексированный" (Proj_Index), как на рис. 20.3.

CREATE TABLE Proj_Index (
   projno	char(8) NOT NULL,
   t_person	char(32) NOT NULL,
   t_frequency	integer,
   t_problem	varchar(512),
   CONSTRAINT pk_ndx PRIMARY KEY( projno, t_person) )
GO

Рис. 20.3. Таблица "Проект индексированный" (Proj_Index)

Затем создаем некластеризованный индекс с ключом "Номер проекта" (projno) и с тремя неключевыми колонками "Фамилия сотрудника" (t_person), "Частота проблемы" (t_frequency) и "Описание проблемы" (t_problem).

CREATE NONCLUSTERED INDEX IX_Proj
    ON Proj_Index (projno)
    INCLUDE (t_person, t_frequency, t_problem);
GO

В семействе СУБД Oracle предусмотрено еще несколько типов индексов, которые позволяют улучшить традиционные для всех СУБД индексы со структурой B-Tree. К таким модификациям, помимо исключительно индексных таблиц, относятся битовые индексы, индексы с обращением ключа, индексы на основе значения функций.

Каждый бит так называемого битового (bitmap) индекса относится к идентификатору строки ROWID (который в Oracle создается и хранится для каждой строки и используется во внутренней организации индексов ) в табличном объекте. Если некоторая строка содержит данное ключевое значение, то в индексе для этого значения сохраняется единица. Такая организация индекса может в некоторых случаях значительно повысить производительность выборки данных, т. к. для извлечения строк с определенными значениями индекса СУБД нужно лишь найти все единицы, отвечающие ключу. Физически такой индекс организован на основе структуры B-Tree, но задача сводится к поиску данной строки за счет одной операции чтения битовой индексной структуры. Этот тип индекса очень эффективен для индексирования колонок с небольшим кардинальным числом — пол, цвет и т.д. Если значений у колонки будет много, то объем ввода-вывода будет возрастать.

Пример 20.4.

Для нашего учебного примера можно построить битовый индекс для таблицы "Служащий" (EMPLOYEE) по колонке "Номер отдела" (DEPNO):

CREATE BITMAP INDEX emp_ndx ON EMPLOYEE (DEPNO);

В СУБД семейства MS SQL Server возможность создания битовых индексов средствами диалекта SQL отсутствует.

В индексе с обращением ключа (reverse-key index) применяется обращение байтов индексируемой колонки числового типа. Этот прием позволяет получать равномерное распределение значений колонок среди блоков-листков индекса со структурой B-Tree, который хорошо подходит для индексирования колонок с последовательной нумерацией или нумерацией с заданным шагом. Заметим, что такие индексы применяются только для возвращения отдельных строк, и с их помощью нельзя выполнить поиск значений в некотором диапазоне. В СУБД Oracle нельзя применить опцию REVERSE к битовым индексам и к исключительно индексным таблицам.

Пример 20.5.

Числовые ключи, содержащие последовательные числа, есть, в частности, в таблице "Служащий" (EMPLOYEE) – "Номер служащего" (EMPNO). Можно создать для этой таблицы дополнительный индекс с обращением ключа для извлечения записи о сотруднике. Заметим, что для этой колонки уже есть индекс первичного ключа.

CREATE INDEX dep_ndx ON EMPLOYEE  (EMPNO) REVERSE;

В процессе эксплуатации администратор базы данных может перестроить этот индекс с помощью команды ALTER INDEX:

ALTER INDEX EMPLOYEE  REBUILD NOREVERSE;

В СУБД семейства MS SQL Server возможность создания индексов с обращением значения ключа средствами диалекта SQL отсутствует.

Если в предложении WHERE применяется функция по индексированной колонке, то обычно СУБД не используют этот индекс при организации доступа к строкам таблицы. Но при создании индекса на основе значения функции (function-based index), которая является той же функцией, что и в предложении WHERE, СУБД как семейства Oracle, так и семейства MS SQL Server использует такой индекс для считывания строк, удовлетворяющих критерию отбора.

В СУБД семейства MS SQL Server вычисляемые колонки могут иметь свойство PERSISTED. Это означает, что компонент Database Engine хранит вычисленные значения в таблице и обновляет их при обновлении любых колонок, от которых зависит вычисляемый столбец. Компонент Database Engine использует эти материализованные значения, когда создает индекс по колонке и когда запрос обращается к индексу.

Для индексации вычисляемой колонки она должна быть детерминированной и точной. Если используется свойство PERSISTED, список типов индексируемых вычисляемых колонок расширяется и включает следующие типы:

1. вычисляемые столбцы, основанные на выражениях языка Transact-SQL, функциях CLR и методах пользовательских типов данных CLR, отмеченных пользователем как детерминированные;
2. вычисляемые столбцы, основанные на выражениях, которые определены компонентом Database Engine как детерминированные, но не являются точными.

Пример 20.6.

В качестве примера использования индексов по вычисляемой колонке приведем пример моделирования хеш-индекса в MS SQL Server.

Предположим, что никакого индекса для колонки "Фамилия" (ENAME) в таблице "Служащий" (EMPLOYEE) создано не было. Тогда, если в предложении WHERE команды SELECT задан предикат поиска по колонке "Фамилия" (ENAME), то СУБД будет сканировать таблицу. Если таблица содержит около 1000000 строк, то операция сканирования будет увеличивать время обработки запроса.

Создадим хеш-индекс для колонки "Фамилия" (ENAME) с помощью функции checksum(), для этого добавим в таблицу вычисляемую колонку, как показано ниже.

alter table EMPLOYEE
add ENameHash as checksum(EName); 
go

Далее создадим индекс по этой колонке, как показано ниже.

create index IDX_ENames on EMPLOYEE (ENameHash);
go

При наличии в БД такого индекса СУБД будет его использовать при обработке запроса описанного в данном примере.

В СУБД семейства MS SQL Server можно создавать и использовать фильтрованные индексы, для создания таких индексов в команде CREATE INDEX предусмотрена возможность использования предложения WHERE.

Применение предложения WHERE <filter_predicate> создает отфильтрованный индекс путем указания строк для включения в индекс. Отфильтрованный индекс должен быть некластеризованным индексом для таблицы. Также создается статистика фильтрации для строк данных отфильтрованного индекса.

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

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

Пример 20.7.

Создадим фильтрованный индекс в таблице "Служащий" (EMPLOYEE) для колонки "Штрафы" (FINE), чтобы быстро получать списки оштрафованных сотрудников.

CREATE NONCLUSTERED INDEX IDX_FINE
    ON EMPLOYEE (EName, FINE)
    WHERE FINE > 0;
GO

В настоящем разделе мы рассмотрели различные типы индексов и примеры их создания. В следующем разделе остановимся подробнее на параметрах проектирования индексов.