Индексы

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

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

То есть индексы - это отдельные объекты в БД, которые используются для того, чтобы ускорить поиск данных. В Oracle существует несколько типов индексов, далее здесь будут рассматриваться так называемые "B-tree" индексы. Это "классический" тип индексов, который используется на практике, и часто, когда говорят слово "индекс", то подразумевают именно его.

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

Создадим индекс на колонку dept_id в таблице employees:

create index employee_dept_id_idx on employees(dept_id);

Здесь employee_dept_id_idx - это имя индекса, оно могло быть любым.

На длину имен индексов также действует ограничение в 30 символов.

При создании первичного или уникального ключа в таблице Oracle создает индексы на эти колонки автоматически.

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

Индексы удаляются по своим именам. Удалим индекс employee_dept_id_idx:

drop index employee_dept_id_idx;

Составные индексы

Индексы могут создаваться на несколько колонок. Такие индексы называются составными.

create index employee_name_idx on employees(first_name, last_name);

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

Рассмотрим это на примере. Если у нас есть таблица employees, из которой часто получают данные по фамилии и коду должности, т.е. из таблицы часто запрашивают данные в подобном виде:

select *
from employees e
where e.emp_name = 'Евгений'
and e.job_code = 21

То в таком случае можно попробовать добавить составной индекс, состоящий из колонок emp_name и job_code:

create index emp_name_job_id_idx on employees(emp_name, job_id);

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

  1. По имени будут искать гораздо чаще
  2. Количество уникальных имен в таблице больше, чем количество уникальных должностей.

До версии 9i порядок колонок в иднексе был более важен, т.к. обязательным условием использования индекса было обращение в запросе к колонке, которая является первой в индексе. В нашем случае, если бы мы написали запрос следующим образом:

select *
from employees
where job_id = 10

индекс не использовался бы, т.к. в запросе нет фильтрации по колонке emp_name, которая идет первой в составном индексе.

Index skip scan

Начиная с версии 9i в Oracle появилась возможность использовать составной индекс, даже если его лидирующая колонка не используется в запросе.

Index skip scan может использоваться тогда, когда количество уникальных значений лидирующей колонки индекса относительно невелико. Опять же, решение об использовании или неиспользовании принимает оптимизатор Oracle.

По-прежнему, лучше стараться создавать составные индексы таким образом, чтобы лидирующая колонка использовалась чаще всего, так наличие возможности использования index scip scan вовсе не означает, что он будет использован всегда.

Зачем использовать составные индексы

Уникальные индексы

Уникальные индексы гарантируют, что значения колонок, входящих в него, будут уникальны. В этом смысле они похожи на уникальные ключи и первичные ключи. Отличие в том, что индекс - это объект в базе данных, задача которого - обеспечить быстрый поиск данных. Ключи же являются отражением бизнес-требований к приложению. Мы создаем уникальный ключ не для ускорения поиска, а для того, чтобы обеспечить уникальность данных, потому что этого требует задача.

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

Уникальные индексы создаются следующим образом:
-- Создать уникальный индекс на колонку
-- id таблицы employees
create unique index emp_uk_idx on
employees(id);

Когда нужно создавать индекс

Однозначно ответить на этот вопрос нельзя, т.к. наличие индекса не означает, что он будет использоваться. Решение о том, использовать индекс или нет, принимает оптимизатор Oracle.

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

Комментарии