5.3. О индексах и производительности

Для ускорения поиска данных можно создавать индексы. Индекс - это системная таблица, построенная по значениям заданного столбца заданной таблицы. В нем размещается перечень уникальных значений указанного столбца таблицы со ссылками на те ее строки, где встречаются эти значения (структура, похожая на предметный указатель книги). Например, индекс, построенный для столбца Основа таблицы Блюда, будет содержать следующие сведения:

Отметим, что такой индекс уже существовал (в несколько иной форме) в базе данных, хотя это обстоятельство никак не повлияло на текст иллюстрационных предложений SELECT, DELETE, INSERT и UPDATE. SQL намеренно не включает в свои конструкции ссылки на индексы. Решение о том, использовать или не использовать какой-либо индекс при обработке некоторого конкретного запроса принимается не пользователем, а оптимизатором СУБД, который учитывает множество факторов - размер таблиц, тип используемых структур хранения данных, статистическое распределение данных в таблицах и индексах и т.д. Однако чтобы оптимизатор смог использовать индексы, их нужно построить (чтобы выиграть в лотерею нужно, по крайней мере, иметь лотерейный билет).

Естественно, что поиск какого-либо значения путем последовательного перебора неупорядоченных данных будет во много раз медленнее, чем поиск с использованием упорядоченного списка (индекса). Ясно также, что таблицу можно упорядочить лишь по данным одного столбца, тогда как поиск часто приходится осуществлять по данным нескольких столбцов. По нескольким столб-цам производится и соединение таблиц. Поэтому, несмотря на то, что индексы увеличивают объем базы данных, их следует использовать как для отдельных столбцов таблицы, так и для комбинации нескольких ее столбцов (например, для комбинации: Фамилия, Имя, Отчество).

Для построения индекса в SQL существует предложение CREATE INDEX (создать индекс), имеющее формат

CREATE [UNIQUE] INDEX имя_индекса ON базовая_таблица
        (столбец [[ASC] | DESC] [, столбец [[ASC] | DESC]] ...);

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

Например, индексы для столбцов БЛ и Основа таблицы Блюда создаются с помощью предложений

CREATE UNIQUE INDEX Блюда_БЛ ON Блюда (БЛ);
CREATE INDEX Блюда_Основа ON Блюда (Основа);

а индекс для первичного ключа (столбцы БЛ и ПР) таблицы Состав - с помощью предложения

CREATE UNIQUE INDEX Состав_БЛ_ПР ON Состав (БЛ, ПР);

В больших (более 1000 строк) таблицах поиск индексированных значений выполняется на порядок быстрее, чем поиск неиндексированных, а в очень больших таблицах - на два-три порядка.

Так может быть, если позволяет память, следует построить индексы для всех столбцов всех таблиц базы данных?

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

DROP INDEX имя_индекса;

Так как индексы могут создаваться или уничтожаться в любое время, то перед выполнением запросов целесообразно строить индексы лишь для тех столбцов, которые используются в WHERE и ORDER BY фразах запроса, а перед модификацией большого числа строк таблиц с индексированными столбцами эти индексы следует уничтожить.