Индексы ​

Что такое индексы ​

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

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

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

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

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

create index employee_dept_id_idx on employees(dept_id);

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

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

Индексы удаляются по своим именам. Удалим индекс 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.

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

• Могут сократить кол-во записей за счет комбинирования колонок
• Если выбираются только колонки из индекса, то это сократит количество операций чтения, т.к. в этом случае данные будут выбраны из индекса без обращения к таблице.

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

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

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

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

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

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

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

• Если колонка часто используется в WHERE
• Если колонка содержит большое количество уникальных значений по отношению к общему количеству строк в таблице.