Що таке композитні індекси?

Загальні питання~4 хв читання

Composite index (композитний індекс) - індекс бази даних, побудований на двох або більше колонках, відсортованих зліва направо відповідно до порядку, який ти задаєш.

Теорія

TL;DR

Індекс на (a, b, c) допомагає запитам, які фільтрують за a, за a + b або за a + b + c. Запитам, що фільтрують тільки за b чи c, він не допомагає.
Порядок колонок - найважливіше рішення при проектуванні композитного індексу.
Якщо всі колонки, потрібні запиту, є в індексі, рядок таблиці не читається взагалі. Такий індекс називається покривним (covering index).
Спочатку йдуть колонки з умовами рівності, потім з умовами діапазону.
Один добре спроектований композитний індекс зазвичай замінює два або три окремих індекси без зайвого навантаження на запис.

Швидкий приклад

sql

-- таблиця users: id, last_name, first_name, email, created_at

CREATE INDEX idx_users_name ON users (last_name, first_name);

-- Використовує індекс повністю (обидві колонки збігаються)
SELECT * FROM users WHERE last_name = 'Kim' AND first_name = 'Alex';

-- Використовує індекс (тільки перша колонка)
SELECT * FROM users WHERE last_name = 'Kim';

-- НЕ використовує індекс (пропускає першу колонку)
SELECT * FROM users WHERE first_name = 'Alex';

Індекс зберігає рядки, відсортовані спочатку за last_name, потім за first_name всередині кожної групи прізвищ. Без значення last_name у рушія немає точки входу в структуру. Сканувати з середини відсортованого дерева рівнозначно повному скануванню таблиці.

Принцип ліво-правого читання (leftmost prefix)

Композитний індекс на (col1, col2, col3) - це фактично три індекси в одному: на col1, на (col1, col2) та на (col1, col2, col3). Тільки ці комбінації. Індекс не може обслуговувати запит на (col2) або (col2, col3), бо на верхньому рівні дані відсортовані не так.

MySQL, PostgreSQL та більшість реляційних баз даних реалізують це однаково. Планувальник запитів проходить по умовах WHERE зліва направо, порівнює їх із визначенням індексу і зупиняється, щойно натрапляє на колонку, якої немає в WHERE, або на умову діапазону.

Умови діапазону розривають ланцюжок. Рівність для col1 і діапазон для col2 означає, що індекс допомагає для обох. Але якщо додати AND col3 = 5, індекс більше не може допомогти для col3, бо діапазон у col2 перервав послідовність.

sql

CREATE INDEX idx_orders ON orders (status, created_at, customer_id);

-- Індекс використовується для всіх трьох колонок (усі умови рівності)
SELECT * FROM orders
WHERE status = 'pending' AND created_at = '2024-01-15' AND customer_id = 42;

-- Індекс використовується для status і діапазону created_at, але НЕ для customer_id
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2024-01-01' AND customer_id = 42;

-- Індекс використовується тільки для status
SELECT * FROM orders WHERE status = 'pending';

Порядок колонок

Умови рівності мають стояти перед умовами діапазону. Щойно індекс натрапляє на діапазон, він не може далі робити прямий пошук за наступними колонками.

Для запиту WHERE user_id = 5 AND created_at > '2024-01-01' індекс має бути (user_id, created_at), а не (created_at, user_id). При зворотному порядку рушій може використати індекс тільки для діапазону created_at і повинен фільтрувати user_id вручну по всіх рядках у тому діапазоні.

Кардинальність теж має значення. Колонка з тисячами унікальних значень (user_id) перед колонкою з малою кількістю (status) дозволяє на кожному кроці відсіювати більше рядків. Але це вторинне правило. Правило рівності-перед-діапазоном завжди в пріоритеті.

Покривні індекси

Покривний (covering) індекс містить усі колонки, потрібні запиту: для фільтрації, для сортування і для вибірки. Якщо запит повністю покритий, рушій читає лише сторінки індексу і ніколи не звертається до самого рядка таблиці. На навантаженнях з великою кількістю читань це може скоротити I/O на 80-90%.

sql

-- Без покривного індексу: сканування індексу + зчитування рядка для кожного збігу
CREATE INDEX idx_orders_status ON orders (status);
SELECT order_id, created_at FROM orders WHERE status = 'shipped';

-- З покривним індексом: тільки індекс, без звернення до таблиці
CREATE INDEX idx_orders_cover ON orders (status, order_id, created_at);
SELECT order_id, created_at FROM orders WHERE status = 'shipped';
-- PostgreSQL EXPLAIN покаже: "Index Only Scan" замість "Index Scan"

Компроміс: ширші індекси займають більше місця і сповільнюють INSERT/UPDATE/DELETE, бо кожен запис оновлює і сам індекс.

Типові помилки

Неправильний порядок колонок.

sql

-- Запит: WHERE user_id = 5 AND status = 'active'

-- Погано: низька кардинальність першою
CREATE INDEX idx_bad ON orders (status, user_id);

-- Добре: висока кардинальність і умова рівності першою
CREATE INDEX idx_good ON orders (user_id, status);

Перший індекс спрацює, але прочитає більше рядків перш ніж звузиться до user_id = 5.

Фільтр по не-крайній лівій колонці.

Є індекс на (a, b, c), а запит фільтрує за WHERE b = 1. Результат - повне сканування таблиці. Це найпоширеніша помилка з композитними індексами і в реальному коді, і на технічних співбесідах.

Діапазон в середині ланцюжка.

sql

-- Індекс: (status, created_at, user_id)
-- Рушій використовує status (рівність) і created_at (діапазон)
-- але не може використати індекс для user_id -- фільтрує окремо
SELECT * FROM orders
WHERE status = 'active' AND created_at > '2024-06-01' AND user_id = 99;

-- Виправлення: перемісти user_id перед created_at, бо це умова рівності
CREATE INDEX idx_orders_fixed ON orders (status, user_id, created_at);

Занадто багато колонок про всяк випадок.

Індекси з 5+ колонками рідко дають більше користі ніж індекс з 3 колонками, зате кожна додаткова колонка збільшує розмір і сповільнює записи. Додавай колонки тільки тоді, коли є конкретний виміряний запит, який це виправдовує.

Припущення, що NULL не індексується. У PostgreSQL і MySQL значення NULL зберігаються в індексі. Запити з WHERE col IS NULL можуть використовувати індекс, і для багатьох це несподівана поведінка.

Де зустрічається в реальному коді

E-commerce: (user_id, status) на таблиці orders для отримання всіх очікуваних замовлень конкретного користувача.
Аналітичні дашборди: (event_type, created_at) для фільтрації за подією і сортування за часом в одному скануванні індексу.
Авторизація: (email, is_active) для запитів входу, які перевіряють обидві умови.
Multi-tenant SaaS: (tenant_id, resource_type, created_at) зазвичай перший індекс, який додають до майже кожної таблиці.

Питання на співбесіді

Q: Чи можна використати композитний індекс, якщо WHERE містить тільки другу колонку?
A: Ні. Принцип ліво-правого читання вимагає, щоб рушій починав з першої колонки. Запит по другій колонці призведе до повного сканування таблиці або індексу залежно від оцінки вартості планувальника.

Q: Яка різниця між composite index і covering index?
A: Composite index - будь-який індекс на кількох колонках. Covering index - композитний індекс, що містить усі колонки, потрібні конкретному запиту, тому рядок таблиці не читається взагалі. Кожен covering index є композитним, але не кожен композитний є покривним.

Q: Чи впливає порядок колонок у SELECT на використання індексу?
A: Ні. Важливий порядок у WHERE і у визначенні індексу. Список у SELECT впливає тільки на покривні індекси: всі вибрані колонки мають бути присутні в індексі. Порядок їх перерахування в SELECT ролі не грає.

Q: Як планувальник запитів вирішує, чи використовувати композитний індекс?
A: Він порівнює вартість різних планів: сканування індексу проти послідовного сканування таблиці. Якщо запит повертає велику частку рядків, послідовне сканування часто виявляється дешевшим через швидший послідовний I/O. Переглянути рішення планувальника можна через EXPLAIN ANALYZE у PostgreSQL або EXPLAIN у MySQL.

Q: Є запити на (a, b) і окремо на (a). Чи потрібні два індекси?
A: Ні. Індекс на (a, b) вже обслуговує запити тільки на a за принципом ліво-правого читання. Окремий індекс на (a) - зайві витрати пам'яті і навантаження на запис. Типова помилка перевантаженості індексами в legacy-кодових базах.

Приклади

Фільтрація активних сесій користувача

sql

-- таблиця sessions: id, user_id, token, status, expires_at

CREATE INDEX idx_sessions_lookup ON sessions (user_id, status);

-- Швидко: обидві колонки в індексі, user_id першим
SELECT token, expires_at
FROM sessions
WHERE user_id = 1042 AND status = 'active';

-- Також використовує індекс (leftmost prefix)
SELECT COUNT(*) FROM sessions WHERE user_id = 1042;

-- НЕ використовує індекс
SELECT * FROM sessions WHERE status = 'active';
-- Для цього запиту потрібен окремий індекс на (status)

Цей патерн я бачив у продакшені: команда додала функцію пошуку, що фільтрувала тільки за status, і не помітила, що порядок колонок в існуючому індексі не підходить для такого запиту.

Покривний індекс для запиту дашборду

sql

-- таблиця orders: id, tenant_id, status, total_amount, created_at

-- Без покривного індексу: сканування + зчитування рядка для кожного збігу
CREATE INDEX idx_orders_tenant ON orders (tenant_id, status);

SELECT id, total_amount, created_at
FROM orders
WHERE tenant_id = 5 AND status = 'completed';

-- З покривним індексом: рушій читає тільки індекс, без звернення до таблиці
CREATE INDEX idx_orders_cover
  ON orders (tenant_id, status, id, total_amount, created_at);

-- EXPLAIN ANALYZE покаже "Index Only Scan" замість "Index Scan"
-- Типовий результат на великих таблицях: в 3-5 разів менша затримка

Індекс виріс з 2 до 5 колонок. На таблиці з 10M рядків і запиті, який виконується 1000 разів на хвилину, зменшення читань окупило витрати на запис.

Запит з умовою діапазону

sql

-- таблиця logs: id, service_name, severity, created_at

-- Добре: умови рівності першими, діапазон останнім
CREATE INDEX idx_logs_good ON logs (service_name, severity, created_at);

SELECT * FROM logs
WHERE service_name = 'api-gateway'
  AND severity = 'error'
  AND created_at > NOW() - INTERVAL '1 hour';

-- Погано: діапазон в середині розриває ланцюжок для severity
CREATE INDEX idx_logs_bad ON logs (service_name, created_at, severity);

-- З idx_logs_bad рушій використовує service_name і діапазон created_at,
-- але не може застосувати індекс для severity = 'error' і фільтрує окремо

Коротка відповідь

Для співбесіди

Premium

Коротка відповідь допоможе вам впевнено відповідати на цю тему під час співбесіди.

Дочитали статтю?