Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

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

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

Чому це так вигідно в InnoDB. Звичайний пошук за вторинним індексом - це два проходи по деревах:

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

Якщо запит повертає тисячу рядків, другий крок - це тисяча окремих пошуків у різних місцях таблиці. Покривний індекс прибирає його повністю.

Вторинний індекс неявно містить первинний ключ - саме ним він посилається на рядок:

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

1

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