Создание физической модели базы данных

Параметры индексирования

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

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

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

CREATE BITMAP INDEX emp_ndx ON EMPLOYEE (DEPNO);

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

Пример. В нашей учебной базе данных числовые ключи, содержащие последовательные числа, есть, в частности, в таблице PLOYEE - EMPNO. Мы можем определить для этой таблиц дополнительный индекс с обращением ключа для извлечения записи о сотруднике. Заметим, что для этой колонки уже есть индекс первичного ключа.

CREATE INDEX dep_ndx ON EMPLOYEE (EMPNO) REVERSE;

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

ALTER INDEX EMPLOYEE REBUILD NOREVERSE;

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

Пример. Обратимся к нашей учебной базе. Предположим, что при поиске сотрудников по фамилии таковая вводится на верхнем регистре, как в примере ниже:

SELECT * FROM EMPLOYEE WHERE UPPER(:ENAME) ORDER BY UPPER(:ENAME);

Тогда, даже при наличии индекса по колонке ENAME, СУБД будет сканировать таблицу, не обращаясь к этому индексу. Проектировщик базы данных, учитывая, что частота таких транзакций будет очень высокой, может предусмотреть создание индекса на основе значений функции от колонки EMANE, как показано ниже:

CREATE INDEX emp_ndx_e ON EMPLOYEE UPPER(:ENAME);

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

О некоторых параметрах проектирования индексов

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

Кардинальностью колонки (cardinality) таблицы называется число дискретных различных значений колонки, которые встречаются в строках таблицы. Например, если в таблице EMPLOYEE мы заводим колонку для указания пола - SEX, то кардинальность этой колонки есть 2, так как в природе у людей существует только два пола - мужской и женский. Для колонки первичного ключа кардинальность будет равна числу строк в таблице.

Причиной, по которой кардинальность колонки важна для проектирования индексов, состоит в том, что кардинальность индексируемой колонки определяет число уникальных входов, которые должны сохраняться в индексе, т.е. число записей в индексе. Так, для индексируемой колонки SEX будет существовать два уникальных входа, которые будут повторяться много раз в индексе. При предположении равновероятного распределения пола сотрудников на 100000 строк в таблице EMPLOYEE каждый вход индекса будет повторяться 50000 раз. СУБД вряд ли будут принимать решение об использовании такого индекса при построении плана запроса.

Определить кардинальность потенциальной колонки индексирования в существующей базе данных достаточно просто:

SELECT COUNT (DISTINCT колонка) FROM таблица

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

Способ, с помощью которого СУБД оценивает действие кардинальности, состоит в использовании фактора селективности выборки (selectivity factor). Фактора селективности выборки индекса определяется как величина, обратная кардинальности индексной колонки:

Фактор селективности оценивает потенциальный объем операций ввода/вывода. Чем меньше фактор селективности, тем меньше требуется операций ввода/вывода для получения результирующего множества строк таблицы. СУБД оценивает эту величину, чтобы решить, использовать индекс для доступа к строкам таблицы или нет. Какие формулы используются для оценки фактора селективности в СУБД, мы рассмотрим в последней лекции этого курса.

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

Хорошими кандидатами для индексирования обычно являются:

• колонки первичного ключа. По определению, колонки первичного ключа должны иметь уникальный индекс;
• колонки внешнего ключа. Они дают хороший индекс по двум причинам. Во-первых, они часто применяются для выполнения соединений с родительскими таблицами. Во-вторых, они могут быть использованы СУБД при поддержке ссылочной целостности в операциях удаления строк родительской и дочерних таблиц;
• любые колонки, которые содержат уникальные значения;
• колонки, запросы или соединения по которым захватывают от 5 до 10% строк таблицы;
• колонки, которые часто входят как аргументы в функции агрегирования;
• колонки, которые часто используются для проверки правильности ввода данных в программах ввода/редактирования.

Факторы, влияющие на низкую эффективность индексов:

• Таблицы маленького размера. Одним из общих эмпирических правил является правило "не создавать индексы для таблиц размером менее пяти физических страниц". Для таких страниц стоимость поддержки индекса больше, чем стоимость сканирования всей таблицы. Конечно, уникальный индекс требуется для первичного ключа и поддержки ссылочной целостности.
• Интенсивные обновления таблиц в пакетном режиме. Такие таблицы обычно имеют проблемы с переполнением индекса при интенсивной модификации таблицы. Если индекс необходим для такой таблицы, то целесообразнее его удалять перед обновлением и создавать после него.
• Асимметрия значений ключей (Skewness of keys). Если распределение значений ключа имеет значительную асимметрию, то кардинальность индекса может оказаться достаточно высокой и СУБД из-за низкого фактора селективности будет часто использовать этот индекс. Но результат использования индекса будет неудовлетворительным из-за того, что значительная часть строк таблицы имеет одно и то же значение ключа, что приведет к нивелированию стоимости использования индекса по сравнению со сканированием всей таблицы.

Плохими кандидатами для индексирования обычно являются:

• колонки с низкой кардинальностью. Они дают высокий фактор селективности, и СУБД обычно избегает их использования. Стоимость поддержки индекса для колонки с низкой кардинальностью сопоставима со стоимостью сканирования всей таблицы, поскольку при доступе через индекс многие страницы базовой таблицы посещаются много раз;
• колонка имеет много неопределенных значений (NULL-значения). В этом случае неопределенные значения могут дать значительную асимметрию распределения значений колонки, несмотря на то, что кардинальность колонки будет подходящей для использования индекса;
• колонки с часто изменяемыми значениями. Индекс для таких колонок часто обновляется, что приводит к его переполнению, поскольку в большинстве алгоритмов обработки B-Tree индексов физическая страница индекса становится доступной для распределения данных только после того, как из нее будут удалена последняя запись. В частности, это обстоятельство приводит к созданию дополнительных страниц индекса и уровней индекса;
• значительная длина индексных колонок. Составной индекс или индекс для одной колонки с длиной более чем 50 байт будет приводить к росту числа уровней индекса, несмотря на то, что строк в таблице может быть немного. Большое число уровней снижает производительность операций выборки строк через индекс, т.к. каждый уровень требует по крайней мере одной операции ввода/вывода.

Принимая решение об индексировании, проектировщику базы данных следует соблюдать следующие общие правила при создании индексов.

• Должно быть в наличии ограничение PRIMARY KEY.
• Не следует создавать для таблицы несколько составных индексов, содержащих одни и те же колонки, но в другом порядке.
• Ни в коем случае нельзя допускать, чтобы у нескольких индексов для одной таблицы была одинаковая лидирующая часть.

К сожалению, часто проектировщики принимают крайне неудачные решения об индексировании. Это приводит к тому, что в базе данных появляется слишком много индексов. В результате тратится много времени на поддержку этих индексов, дисковое пространство расходуется неэффективно, СУБД "путается" в выборе подходящего индекса или не использует их вовсе. Проектировщик базы данных должен помнить, что есть две главные причины построить индекс:

1. чтобы гарантировать уникальность значений колонки, которая будет индексироваться;
2. чтобы увеличить производительность обработки запросов в базе данных. Это, кстати, единственная разумная причина для создания неуникальных индексов.