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

Индексы используются СУБД для быстрого поиска строки в таблице. По своей организации индексы представляют, как правило, структуры данных в виде двоичных деревьев. Индексы позволяют избегать полных просмотров таблиц, снижающих производительность системы. Индекс хранит последовательность упорядочивания данных в поле (полях) таблицы по возрастанию или убыванию в виде физических номеров записей. Эти служебные системные номера генерируются СУБД по мере формирования каждой новой строки таблицы. Используя индекс, СУБД быстрее находит нужные данные, при этом сокращается физическое число перемещений физических устройств считывания данных между блоками памяти. Проектируя индексы, необходимо помнить, что повальное использование индексов по принципу «чем больше – тем лучше» может затормозить выполнение запросов, оптимизатор запросов СУБД будет использовать первый индекс и, возможно, он не будет лучшим. Необходим здравый подход к созданию индексов.

Индексы необходимы, если:

— часто производится поиск в БД по определенному полю (полям) таблицы;

— часто выполняется операция объединения таблиц по значению поля (полям);

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

Не рекомендуется строить индексы по полям или группам полей, если:

— поля редко используются для выполнения операций поиска, объединения, сортировки;

— значения полей часто меняют свои значения;

— значения полей содержат небольшое число вариантных значений;

— значения полей имеют большие размеры;

— поля содержат большое количество пустых значений.

Для объявленных при создании таблиц первичного и уникальных ключей современные СУБД индекс создают автоматически. Некоторые СУБД, в частности InterBase, создают автоматически индексы и для объявленных внешних ключей. При выполнении запроса к БД, в условие поиска которого входит столбец таблицы, для которого создан индекс, поиск значений производится в первую очередь в индексе.

Различают следующие типы индексов:

— простые, строятся на основе только одного поля таблицы;

— составные – строятся по двум и более полям таблицы, при этом последовательность полей в составном индексе может быть не связана с последовательностью полей в таблице. Последовательность влияет на скорость поиска данных. Для создания оптимального составного индекса рекомендуется первым помещать поле, содержащее меньшее количество повторов, то есть содержащее наиболее ограничивающее значение, либо первым помещать поле, содержащее данные, которые наиболее часто задаются в условиях поиска;

— уникальные – используются дополнительно с целью поддержки целостности данных. Так, например, для создаваемой в среде СУБД FoxPro базы данных необходимо такие индексы строить для первичных и уникальных ключей, поскольку эта СУБД не поддерживает явно определение таких ключей.

При создании индексов следует поддерживать следующие рекомендации:

— нельзя создавать для таблицы несколько индексов, содержащих одинаковые, но расположенные в другом порядке поля, в случае необходимости индексы следует сокращать. Исключение составляют таблицы, разбивающие связь "многие_ко_многим";

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

Если проектировщиком приняты неудачные решения по индексированию, то в дальнейшем может значительно снизиться производительность системы вследствие излишней траты времени процессором на операции чтения, записи, ненужную оптимизацию; неэффективного использования дискового пространства.

Создание индекса может быть реализовано командой Create Index.

При многократном внесении и изменении данных в таблицах индексы, связанные с этой таблицей, могут быть разбалансированы, содержать неадекватный порядок следования записей. Поэтому периодически необходимо осуществлять улучшение производительности индекса, используя команды перестройки (деактивизации) индексов, например команду Alter Index.

Для оптимального использования индексов в конкретной СУБД необходимо пользоваться соответствующей технической документацией.