Покрывающий индекс в SQL

Покрывающий индекс — самый быстрый способ выбрать данные из таблицы.

Разберемся на примере запроса, который выбирает сотрудников с указанной зарплатой:

select id, name from employees
where salary = 90;

Без индекса и с ним

Если нет индекса, движок базы обходит всю таблицу (так называемый «фулскан»):

QUERY PLAN
`--SCAN employees

Создадим индекс по зарплате:

create index employees_idx
on employees (salary);

Теперь движок найдет записи по зарплате в индексе (это быстрее, чем обходить всю таблицу). И для каждой найденной записи обратится к таблице, чтобы получить значения id и name:

QUERY PLAN
`--SEARCH employees USING INDEX employees_idx (salary=?)

Покрывающий индекс

Создадим покрывающий индекс (охватывает все выбираемые столбцы):

create index employees_idx
on employees (salary, id, name);

Теперь движок базы отрабатает только по индексу, вообще без обращения к таблице. Это еще быстрее:

QUERY PLAN
`--SEARCH employees USING COVERING INDEX employees_idx (salary=?)

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

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

create index employees_idx
on employees (id, name, salary);

Теперь движок не сможет быстро выбрать записи по условию salary = 90. Он все еще может использовать индекс, но будет вынужден полностью обойти его (фулскан по индексу) вместо того, чтобы моментально найти нужные записи.

QUERY PLAN
`--SCAN employees USING COVERING INDEX employees_idx

(обратите внимание на SCAN вместо SEARCH)

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