Покрывающий индекс 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

(обратите внимание на СКАНИРОВАНИЕ вместо ПОИСКА здесь)

Вывод

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