SQL: індекси й транзакції

Індекс - окрема відсортована структура (найчастіше B-дерево), яка зберігає значення колонки й посилання на рядки таблиці. Як предметний покажчик у книжці: щоб знайти сторінку, не треба гортати всю книгу.

Без індексу запит WHERE email = 'a@b.com' змушує базу переглянути всю таблицю (Seq Scan / full table scan). З індексом пошук займає кілька кроків по дереву навіть на мільйонах рядків.

CREATE INDEX users_email_idx ON users (email);

Ціна індексу:

• Запис сповільнюється: кожен INSERT, UPDATE індексованої колонки й DELETE змушує базу оновити ще й індекс. Десять індексів на таблиці - десять додаткових записів на кожну вставку.
• Місце на диску і в пам'яті.

Що індексувати: колонки з WHERE, JOIN і ORDER BY у частих запитах, зовнішні ключі. Не варто - колонки, за якими не шукають, і таблиці на кілька сотень рядків.

Важливо: індекс корисний, коли умова відсіює більшість рядків. Колонка is_active, де 95% значень true, мало що дасть - базі все одно читати майже всю таблицю.

Докладніше в документації: Індекси

Складений індекс (a, b, c) впорядковує рядки спершу за a, всередині однакових a - за b, далі за c. Як телефонний довідник: за прізвищем, потім за ім'ям.

CREATE INDEX orders_user_status_idx ON orders (user_id, status, created_at);

Правило лівого префікса: індекс допомагає, коли умова використовує колонки з початку:

• WHERE user_id = 5 - так.
• WHERE user_id = 5 AND status = 'paid' - так.
• WHERE user_id = 5 AND status = 'paid' ORDER BY created_at - так, і сортування береться з індексу.
• WHERE status = 'paid' - ні (або лише дуже повільним повним обходом індексу): шукати за ім'ям у довіднику, впорядкованому за прізвищем, не вийде.

Як обирати порядок:

1. Спершу колонки з рівністю (=), потім ті, що в діапазоні (>, BETWEEN) чи в ORDER BY. Після колонки з діапазоном наступні колонки для пошуку вже майже не працюють.
2. Серед колонок з рівністю - ті, що використовуються в більшості запитів.

Наслідок: індекс (user_id, status) робить окремий індекс на user_id зайвим - лівий префікс уже покриває такі запити. Зайвий індекс лише сповільнює запис.

Докладніше в документації: Багатоколонкові індекси

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

WHERE lower(email) = 'ivan@x.com'        -- індекс на email не використається
WHERE DATE(created_at) = '2026-10-04'    -- те саме
WHERE name LIKE '%олег%'                 -- невідомий початок рядка

Як виправити:

• Переписати умову, щоб колонка лишалася «голою»:

WHERE created_at >= '2026-10-04' AND created_at < '2026-10-05'

• Індекс за виразом (PostgreSQL; функціональний індекс у MySQL 8.0.13+):

CREATE INDEX users_email_lower_idx ON users (lower(email));

• LIKE 'олег%' (відомий початок) працює з B-деревом. У PostgreSQL з не-C локаллю для цього потрібен індекс з text_pattern_ops.
• LIKE '%олег%' - потрібен інший тип індексу: триграмний (pg_trgm + GIN у PostgreSQL) або повнотекстовий пошук (tsvector, FULLTEXT у MySQL), або окремий пошуковий рушій - Meilisearch, Elasticsearch.

Ще одна пастка - неявне приведення типів: порівняння рядкової колонки з числом (WHERE phone = 380501234567) змушує MySQL перетворювати кожне значення колонки, і індекс не працює.

Докладніше в документації: Індекси за виразами

Звичайно база знаходить у індексі посилання на рядок, а потім іде в саму таблицю по решту колонок. Якщо всі колонки, потрібні запиту, вже є в індексі, звертатися до таблиці не треба - це index-only scan, а індекс називають покривним.

-- Запит
SELECT status, total FROM orders WHERE user_id = 5;

-- Покривний індекс: user_id для пошуку, status і total - щоб не йти в таблицю
CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (status, total);

INCLUDE (PostgreSQL 11+) додає колонки лише в листки індексу: за ними не шукають, зате їх можна прочитати. У MySQL INCLUDE немає - колонки просто додають у кінець складеного індексу.

Нюанси:

• PostgreSQL через MVCC мусить перевірити, чи рядок видимий транзакції. Index-only scan справді не читає таблицю лише для сторінок, позначених у visibility map як «усі рядки видимі». Її оновлює VACUUM, тож на таблиці з частими змінами без вакууму виграш менший. У EXPLAIN ANALYZE це видно як Heap Fetches.
• InnoDB усі вторинні індекси вже містять первинний ключ, тож SELECT id ... WHERE email = ? за індексом на email - покривний автоматично.
• Широкий покривний індекс - це копія частини таблиці: більше місця, повільніший запис. Його роблять під конкретний частий запит, а не «про всяк випадок».

Докладніше в документації: Index-only scan і покривні індекси

Частковий індекс (PostgreSQL) індексує лише рядки, що відповідають умові WHERE. Він менший, швидше оновлюється і краще тримається в пам'яті.

1. Запити завжди дивляться на малу частину таблиці:

-- 99% замовлень завершені, але черга обробки дивиться лише на pending
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

Індекс займає частку від повного, і запит WHERE status = 'pending' ORDER BY created_at бере з нього кілька рядків.

2. Унікальність лише серед частини рядків - найкорисніше застосування:

-- Email унікальний лише серед не видалених (soft delete)
CREATE UNIQUE INDEX users_email_active_uniq ON users (email) WHERE deleted_at IS NULL;

-- Лише одна активна підписка на користувача
CREATE UNIQUE INDEX subscriptions_one_active ON subscriptions (user_id) WHERE active;

Без часткового індексу видалений користувач «займав» би email назавжди, і зареєструватися знову було б неможливо.

Обмеження:

• Планувальник використає індекс, лише якщо з умови запиту можна вивести умову індексу. WHERE status = 'pending' - так; WHERE status = $1 з параметром, невідомим на етапі планування, - не завжди.
• MySQL часткових індексів не має. Альтернативи - згенерована колонка, яка NULL для «неактивних» рядків (унікальний індекс пропускає NULL), або функціональний індекс.

Докладніше в документації: Часткові індекси