23. Работа с индексами

23.1. Логическое разделение на ключи и индексы

В InterBase разделено понятие ключей и индексов. Это разделение, впрочем, имеет логическую окраску, то есть ценно при создании ТБД. Первичный (PRIMARY KEY) и внешний (FOREIGN KEY) ключи строятся для обеспечения ссылочной целостности реляционно связанных таблиц в БД. Первичный ключ, помимо этого, выполняет функции поддержания уникальности своих значений, что обусловлено его основным назначением - однозначно характеризовать запись в таблице БД. Для таких же целей может использоваться и просто уникальный ключ (UNIQUE).

"Обычные" индексы, создаваемые оператором CREATE INDEX, в отличие от ключей, служат для обеспечения сортировок и оптимизации доступа к данным.

С физической точки зрения, то, что логически при создании таблиц подразделялось на ключи и индексы, преобразуется в индексы. Однако, если "обычным" индексам можно назначить имя, то физические индексы, реализованные на основе определений ключей, строятся и именуются системой автоматически. Например, для таблицы SOTR будут построены два физических индекса - по первичному ключу и по "обычному" индексу DLJ:

CREATE TABLE SOTR (ID_SOTR INTEGER NOT NULL, OTDEL VARCHAR(lO) CHARACTER SET WIN1251

COLLATE PXW_CYRL, DOLJNOST CHAR(10) CHARACTER SET WIN1251

COLLATE PXW_CYRL, FIO VARCHAR(25) CHARACTER SET WIN1251

COLLATE PXW_CYRL, PRIMARY KEY (ID_SOTR)

) ;

CREATE INDEX DLJ ON SOTR (DOLJNOST) ;

но именоваться они будут следующим образом:

DLJ INDEX ON SOTR(DOLJNOST)

RDB$PRIMARY18 UNIQUE INDEX ON SOTR(ID_SOTR)

Заметим, что вывести определения всех индексов БД можно оператором SHOW INDEX;

А проделать то же для конкретной таблицы можно оператором SHOW INDEX <имя таблицы>;

23.2. Необходимость создания индексов

Индексы необходимо создавать в случае, когда по столбцу или группе столбцов:

• часто производится поиск в БД (столбец или группа столбцов часто перечисляются в предложении WHERE оператора SELECT);

• часто строятся объединения таблиц;

• часто производится сортировка в НД, возвращаемых в качестве результатов запросов к БД (то есть столбец или столбцы часто используются в предложении ORDER BY оператора SELECT).

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

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

• часто меняют значение, что приводит к необходимости часто обновлять индекс и способно существенно замедлить скорость работы с БД;

• содержат небольшое число вариантов значения.

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

• при использовании в некоторых запросах не всех столбцов из этого индекса следует использовать только непрерывную последовательность столбцов; например, если индекс построен по столбцам Р1, Р2, РЗ, Р4, то в некотором операторе SELECT допустимо указать SELECT ... ORDER BY P1,P2,P3 но никак не SELECT...ORDER BY P1,P2,P4 или SELECT...ORDER BY Р1,РЗ, Р4, поскольку в последних двух случаях индекс, построенный по столбцам Р1, Р2, РЗ, Р4, не будет использован;

• последовательность указания в предложении ORDER BY столбцов является важной; так, для индекса, построенного по столбцам Р1, Р2, РЗ, Р4, указание SELECT ... ORDER BY P2,P1,P3 не приведет к использованию указанного индекса для сортировки результирующего набора данных;

• при частом использовании в условной части WHERE оператора SELECT нескольких столбцов, связанных между собой операцией "или" (OR): SELECT . . . WHERE Р1 = значение! OR Р2 = значение2 OR РЗ = ...

вместо индекса по столбцам Р1, Р2, РЗ лучше создать несколько индексов, построенных по каждому из этих полей, поскольку в противном случае будет осуществлен последовательный просмотр всей таблицы. Это неудивительно, поскольку индексно-последовательный доступ для индекса по столбцам Р1, Р2, РЗ может быть осуществлен только для столбца Р1; значения столбцов Р2 и РЗ в этом индексе спонтанно разбросаны по индексу. Например:

23.3. Создание индекса

Индекс может быть создан оператором

CREATE [UNIQUE] |ASC[ENDING][ DESC[ENDING]]

INDEX ИмяИндекса ON ИмяТаблицы (столбец! |,столбец2 ...]);

UNIQUE - требует создания уникального индекса, не допускающего одинаковых значений индексных полей для разных записей таблицы;

ASC[ ENDING] - указывает на необходимость сортировки значений индексных полей по возрастанию (режим принят по умолчанию);

DESC[ENDING] - указывает на необходимость сортировки значений индексных полей по убыванию;

ИмяИндекса - имя создаваемого индекса;

• table - имя таблицы, для которой создается индекс;

• cтолбецN - имена столбцов, по которым создается индекс.

Например, для таблицы PRIHOD

CREATE TABLE PRIHOD(ID_PRIHOD INTEGER NOT NULL PRIMARY KEY,

DATAPRIH DATE NOT NULL,

TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,

KOLVO INTEGER NOT NULL

) ;

создать индекс в порядке убывания значений DATAPRIH и TOVAR:

CREATE DESC INDEX D_P ON PRIHOD (DATAPRIH,TOVAR);

23.4. Улучшение производительности индекса

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

• выполнять перестройку индекса оператором ALTER INDEX;

• пересчитать показатель "выбираемости" индекса оператором SET STATISTICS;

уничтожить индекс оператором DROP INDEX и заново создать его оператором CREATE INDEX.

23.4.1. Перестройка индекса

Перестройка индекса заключается в пересоздании и балансировке индекса, что наступает после деактивизации индекса оператором ALTER INDEX <имя индекса> DEACTIVATE;

и последующей его активизации оператором ALTER INDEX <имя индекса> DEACTIVATE;

Деактивизация индекса полезна также в том случае, когда имеет место вставка в таблицу БД большого числа записей. В обычном режиме добавления записей при активном индексе изменения вносятся в индекс по мере добавления записей в таблицу, что может разбалансировать индекс. Следует помнить, что:

• нельзя перестроить используемый в данный момент индекс, что может иметь место при выполнении другими пользователями запросов к БД;

• нельзя перестроить индекс, созданный в результате определения первичного и внешнего ключей, а также уникальности значений столбца или группы столбцов (PRIMARY KEY, FOREIGN KEY, UNIQUE). Для этой цели следует применять оператор ALTER TABLE;

для выполнения оператора ALTER INDEX нужно иметь соответствующие привилегии доступа к БД.

23.4.2. Повторное вычисление показателя "полезности" индекса

Показатель "полезности" индекса основан на сведениях о числе повторяющихся строк индекса. Он используется InterBase при доступе к таблице для выработки оптимального плана удовлетворения запроса. Пересчет показателя "полезности" производится оператором

SET STATISTICS INDEX <имя индекса:-;

Заметим что SET STATISTICS не перестраивает индекс. Для этой цели необходимо использовать оператор ALTER INDEX. Для выполнения оператора SET STATISTICS нужно иметь соответствующие привилегии

доступа к БД.

23.5. Удаление существующего индекса

Для удаления индекса, ранее созданного оператором CREATE INDEX, используется оператор

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

Нельзя удалить индекс, созданный в результате определения первичного и внешнего ключей, а также уникальности значений столбца или группы столбцов (PRIMARY KEY, FOREIGN KEY, UNIQUE). Для этой цели следует применять оператор ALTER TABLE.

Нельзя также удалить используемый индекс, что может иметь Место при выполнении другими пользователями запросов к БД. Кроме этого, для удаления индекса нужно иметь соответствующие привилегии доступа к БД.