Що таке покривний індекс у MySQL і чому вторинний індекс InnoDB «містить» первинний ключ?

Question

Accepted Answer

Покривний індекс містить усі колонки, які потрібні запиту, - і в умові, і в SELECT, і в сортуванні. Тоді MySQL читає лише індекс і не йде в саму таблицю. У EXPLAIN це видно як Using index в колонці Extra.
Чому це так вигідно в InnoDB. Звичайний пошук за вторинним індексом - це два проходи по деревах:

у вторинному індексі знаходимо значення первинного ключа;
у кластерному індексі за первинним ключем знаходимо рядок.

Якщо запит повертає тисячу рядків, другий крок - це тисяча окремих пошуків у різних місцях таблиці. Покривний індекс прибирає його повністю.
Вторинний індекс неявно містить первинний ключ - саме ним він посилається на рядок:
CREATE INDEX orders_user_idx ON orders (user_id);   -- фактично (user_id, id)

SELECT id FROM orders WHERE user_id = 7;            -- Using index: id уже в індексі
SELECT id, total FROM orders WHERE user_id = 7;     -- потрібен рядок: total немає в індексі

Як зробити запит покривним:
CREATE INDEX orders_user_status_total_idx ON orders (user_id, status, total);

SELECT status, SUM(total) FROM orders WHERE user_id = 7 GROUP BY status;
-- усе в індексі: умова, групування, агрегат

Типові застосування:

лічильники й агрегати за умовою (COUNT(*) WHERE ...);
пагінація з відкладеним з'єднанням (deferred join): спершу знайти id через покривний індекс, потім підтягнути рядки:

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20 OFFSET 10000) AS page
  ON page.id = o.id;

Глибоке зміщення проходиться по компактному індексу, а повні рядки читаються лише для 20 записів.

перевірки існування (EXISTS) і whereIn за зовнішнім ключем.

Ціна: кожна колонка в індексі збільшує його розмір і сповільнює запис. Додавати «хвіст» колонок у кожен індекс заради покриття - погана ідея. Робити покривним варто запит, який виконується дуже часто й читає багато рядків.
Пастка Eloquent: ->get() за замовчуванням вибирає *, що майже ніколи не покривається індексом. Для гарячих запитів варто явно обмежувати колонки: ->select(['id', 'status']) чи ->pluck('id').
Відмінність від PostgreSQL: там вторинний індекс не містить первинного ключа, а index-only scan залежить ще й від карти видимості (visibility map).
Докладніше в документації: Формат виводу EXPLAIN: Using index

Що таке покривний індекс у MySQL і чому вторинний індекс InnoDB «містить» первинний ключ?

Схожі питання