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 и РЗ в этом индексе спонтанно разбросаны по индексу. Например:
Индекс может быть создан оператором
CREATE [UNIQUE] |ASC[ENDING][ DESC[ENDING]]
INDEX
ИмяИндекса ON ИмяТаблицы (столбец! |,столбец2 ...]);• UNIQUE -
требует создания уникального индекса, не допускающего одинаковых значений индексных полей для разных записей таблицы;• ASC[ ENDING] -
указывает на необходимость сортировки значений индексных полей по возрастанию (режим принят по умолчанию);• DESC[ENDING] -
указывает на необходимость сортировки значений индексных полей по убыванию;•
ИмяИндекса - имя создаваемого индекса;• table
- имя таблицы, для которой создается индекс;• c
толбецN - имена столбцов, по которым создается индекс.Например, для таблицы
PRIHODCREATE 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.Перестройка индекса заключается в пересоздании и балансировке индекса, что наступает после деактивизации индекса оператором
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.Нельзя также удалить используемый индекс, что может иметь Место при выполнении другими пользователями запросов к БД. Кроме этого, для удаления индекса нужно иметь соответствующие привилегии доступа к БД.