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

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

Индексы, создаваемые оператором CREATE INDEX служат для сортировок и оптимизации доступа к данным. Первичный ключ, внешний ключ и индекс преобразуются в физические индексы – специальный механизм, обеспечивающий быстрый доступ к данным. Индексу можно назначить имя. Физические индексы, реализованные на основе определения ключей, строятся и именуются системой автоматически. Для того чтобы вывести определение всех индексов используется команда:

SHOW INDEX //для всех таблиц БД

SHOW INDEX <имя таблицы> //для определенной таблицы

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

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

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

• часто производится сортировка (столбец или группа столбцов часто используются в предложении ORER BY оператора SELECT).

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

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

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

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

Особенности использования индексов:

При использовании в запросах не всех столбцов из индекса следует использовать только непрерывную их последовательность. Например, если индекс построен по столбцам P1, P2, P3, P4, то можно указать

SELECT … ORDER BY P1, P2, P3

но никак нельзя

SELECT … ORDER BY P1, P3, P4

или

SELECT … ORDER BY P1, P2, P4

Последовательность указания в предложениях ORDER BY столбцов является важной; Например, если индекс построен по столбцам P1, P2, P3, P4, то нельзя указать

SELECT … ORDER BY P1, P3, P2

При частом использовании в условной части WHERE оператора SELECT нескольких столбцов, связанных между собой операцией OR вместо индекса по столбцам лучше создать несколько индексов, построенных по каждому из этих столбцов, т.к. иначе будет осуществлен последовательный просмотр всей таблицы. Например:

SELECT … WHERE

P1=значение 1 OR P2=значение 2 OR P3=значение 3 OR P4=значение 4

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

CREATE [UNIQUE] [ASC[ENDING]/DESC[ENDING]]

INDEX <ИмяИндекса> ON <ИмяТаблицы> (столбец1 [, столбец2 …]);

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

• ASC[ENDING] – сортировка по возрастанию (по умолчанию);

• DESC[ENDING] – сортировка по убыванию;

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

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

После многократного внесения изменений в таблицу базы данных индексы этой таблицы могут быть разбалансированы. Разбалансировка приводит к тому, что “глубина” индекса возрастает сверх критического значения.

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

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

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

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

Перестройка индекса заключается в пересоздании и балансировке индекса. Для перестройки индекса необходимо сначала деактивизировать его

ALTER INDEX <имя> DEACTIVATE,

а затем активизировать

ALTER INDEX <имя> ACTIVATE.

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

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

Нельзя перестроить индекс, созданный оператором PRIMARY KEY, FOREIGN KEY, UNIQUE. Для этой цели следует применять оператор ALTER TABLE.

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

Удаление индекса.

Для удаления индекса используется оператор:

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

Нельзя удалить

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

• индекс, используемый в данный момент в других запросах.

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