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

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

Питання з реальних співбесід з відповідями: Laravel і PHP, бази даних, JavaScript і фронтенд, Git, Docker, API, безпека й архітектура. Тими самими темами, що й тести.

16 питань

Індекс - окрема відсортована структура (найчастіше 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 зайвим - лівий префікс уже покриває такі запити. Зайвий індекс лише сповільнює запис.

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

PostgreSQL має кілька видів індексів, кожен під свій тип запитів:

  • B-tree (за замовчуванням) - рівність, діапазони (<, >, BETWEEN), сортування, LIKE 'початок%'. Покриває більшість випадків.
  • Hash - лише рівність (=). З PostgreSQL 10 надійний (записується в WAL), але виграш над B-tree рідко помітний.
  • GIN (Generalized Inverted Index) - для значень, що містять багато елементів: масиви, jsonb, повнотекстовий пошук (tsvector), триграми (pg_trgm). Шукає «рядки, де є цей елемент».
  • GiST - для геометрії й діапазонів: «перетинається», «містить», «найближчий» (PostGIS, tsrange, exclusion constraints, триграми з пошуком найближчих).
  • SP-GiST - для нерівномірно розподілених даних: IP-адреси, телефонні префікси, квадродерева.
  • BRIN - крихітний індекс для дуже великих таблиць, де значення фізично впорядковані на диску (час вставки в журналі подій).
CREATE INDEX orders_user_idx ON orders (user_id);                       -- B-tree
CREATE INDEX products_tags_idx ON products USING gin (tags);            -- масив
CREATE INDEX docs_search_idx ON docs USING gin (to_tsvector('simple', body));
CREATE INDEX bookings_period_idx ON bookings USING gist (period);       -- tsrange
CREATE INDEX events_created_brin ON events USING brin (created_at);

Як обирати: почати з B-tree. Інші типи - коли запит використовує оператори, які B-tree не підтримує (@>, &&, @@, <->), або коли таблиця настільки велика, що розмір індексу стає проблемою (BRIN).

Нюанс GIN: швидкий на читання, але дорожчий на запис. Тому він накопичує зміни в «відкладеному списку» (fastupdate), який потім вливається в індекс - на таблицях з інтенсивним записом це варто враховувати.

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

Які індекси є:

-- у psql
\d orders

-- або запитом
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

Чи використовуються - статистика pg_stat_user_indexes, яку PostgreSQL збирає з моменту останнього скидання статистики:

SELECT relname AS table,
       indexrelname AS index,
       idx_scan AS scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

idx_scan = 0 означає, що індекс жодного разу не використали для пошуку. Такий індекс лише сповільнює запис і займає місце.

Перш ніж видаляти «невикористаний» індекс:

  • Перевірити термін статистики: якщо її скинули вчора, щомісячний звіт ще не встиг скористатися індексом.
  • Перевірити всі репліки: статистика збирається окремо на кожному сервері. Індекс може не використовуватися на primary, але бути критичним для звітів, що йдуть на репліку.
  • Унікальні індекси й первинні ключі не видаляють, навіть з idx_scan = 0: вони забезпечують цілісність, а не швидкість.
  • Видаляти через DROP INDEX CONCURRENTLY, щоб не блокувати таблицю.

Окремо корисно: seq_scan і seq_tup_read у pg_stat_user_tables показують таблиці, які часто читаються повністю, - кандидати на новий індекс.

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

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

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 і покривні індекси

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

Типові причини:

  • Умова не відсіює більшість рядків. Якщо запит поверне 30% таблиці, послідовне читання сторінок підряд дешевше, ніж тисячі випадкових переходів індекс → таблиця. Так задумано.
  • Маленька таблиця. Кілька сторінок простіше прочитати повністю.
  • Застаріла статистика. Після масового імпорту чи видалення планувальник думає, що в таблиці 100 рядків, а їх мільйон. Рішення - ANALYZE orders (autovacuum робить це сам, але із запізненням).
  • Умова не підходить до індексу: функція над колонкою (lower(email)), неявне приведення типів, LIKE '%текст', умова на другу колонку складеного індексу без першої.
  • Параметризований запит з generic-планом, оптимальним «в середньому», але не для конкретного значення.
  • Налаштування вартості: random_page_cost = 4 за замовчуванням розраховане на HDD. На SSD його зазвичай знижують до 1.1-1.5, і індекси стають «дешевшими» для планувальника.

Як розібратися:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';

Порівняти rows (оцінку) з actual rows. Якщо вони розходяться в рази - проблема в статистиці. Для перевірки гіпотези в сесії можна тимчасово заборонити послідовне читання: SET enable_seqscan = off; - і подивитися, наскільки «індексний» план насправді швидший. На проді так не залишають.

Нерівномірні дані: для колонок з перекосом (99% status = 'done') допомагає збільшити деталізацію статистики: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; і повторний ANALYZE.

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

Обмеження UNIQUE - логічне правило схеми. PostgreSQL реалізує його унікальним індексом, який створює автоматично. Тому для простого випадку різниці в поведінці немає:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
-- майже те саме, що:
CREATE UNIQUE INDEX users_email_unique ON users (email);

Коли потрібен саме індекс:

  • Частковий: унікальність серед частини рядків - CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL. Обмеження з WHERE не буває.
  • За виразом: унікальність без урахування регістру - ON users (lower(email)).
  • CONCURRENTLY: створити без блокування запису на великій таблиці.

Коли потрібне саме обмеження:

  • Зовнішні ключі посилаються на PRIMARY KEY чи UNIQUE-обмеження (або унікальний індекс без умови).
  • Відкладена перевірка DEFERRABLE INITIALLY DEFERRED - перевіряти унікальність у кінці транзакції, а не після кожного рядка (наприклад, при перестановці позицій).
  • ON CONFLICT ON CONSTRAINT за іменем.

NULL і унікальність. За стандартом SQL NULL не дорівнює NULL, тож у колонці з UNIQUE може бути скільки завгодно рядків з NULL. Часто це і потрібно (необов'язковий email). Але інколи - ні: «у користувача може бути лише одна адреса без типу».

NULLS NOT DISTINCT (PostgreSQL 15+) змінює правило - NULL вважаються однаковими:

CREATE UNIQUE INDEX one_default_address ON addresses (user_id, type) NULLS NOT DISTINCT;

Тепер для одного user_id дозволено лише один рядок з type IS NULL.

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

Роздування (bloat) - індекс займає значно більше місця, ніж потрібно для живих даних. Причина - MVCC: UPDATE і DELETE залишають мертві версії рядків, а записи індексу, що на них вказували, звільняються вакуумом не завжди повністю. Сторінки індексу стають напівпорожніми.

Наслідки: більший індекс гірше вміщається в пам'ять, більше читань з диска, повільніші запити й бекапи.

Особливо схильні:

  • індекси на колонках, які часто змінюються;
  • таблиці-черги, де рядки постійно вставляють і видаляють;
  • індекси на монотонно зростаючих значеннях з видаленням старих рядків (ліва частина дерева порожніє).

Як оцінити: розширення pgstattuple (pgstatindex('orders_created_idx') показує щільність листків) або запити-оцінки з PostgreSQL Wiki. Простий сигнал - індекс помітно більший за свіжостворений аналог.

Як виправити без простою:

REINDEX INDEX CONCURRENTLY orders_created_idx;   -- PostgreSQL 12+
REINDEX TABLE CONCURRENTLY orders;

CONCURRENTLY будує новий індекс поруч і підміняє старий, не блокуючи запис. Звичайний REINDEX блокує запис у таблицю на весь час перебудови.

Підводні камені: як і з CREATE INDEX CONCURRENTLY, команду не можна виконати в транзакції, а при збої може лишитися недійсний індекс (_ccnew), який треба видалити вручну.

Профілактика:

  • Налаштований autovacuum для таблиць з частими змінами.
  • fillfactor менше 100 для таблиць з частими оновленнями - щоб нові версії рядків вміщалися на ту саму сторінку (HOT-оновлення не чіпають індекси взагалі).
  • Не індексувати колонки, які часто змінюються, без потреби.

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

B-tree зберігає значення відсортованими. Тому запит «перші N за порядком» може взяти рядки прямо з індексу й зупинитися, не сортуючи всю таблицю:

CREATE INDEX posts_published_idx ON posts (published_at);

SELECT * FROM posts ORDER BY published_at DESC LIMIT 20;
-- Index Scan Backward: 20 рядків з кінця індексу, без сортування

B-tree можна читати в обох напрямках, тож для однієї колонки DESC в індексі не обов'язковий.

Коли напрямок у індексі важливий - змішане сортування кількох колонок:

SELECT * FROM products ORDER BY category_id ASC, price DESC LIMIT 50;

CREATE INDEX products_cat_price_idx ON products (category_id ASC, price DESC);

Індекс (category_id, price) для такого запиту не підійде: обхід у будь-якому напрямку дасть price в тому ж напрямку, що й category_id.

NULLS FIRST / NULLS LAST. За замовчуванням у PostgreSQL NULL більші за всі значення: при ASC вони в кінці, при DESC - на початку. Якщо запит пише ORDER BY published_at DESC NULLS LAST, індекс має збігатися:

CREATE INDEX posts_pub_idx ON posts (published_at DESC NULLS LAST);

Інакше планувальник змушений сортувати.

Разом з фільтром: WHERE user_id = ? ORDER BY created_at DESC LIMIT 10 найкраще обслуговує індекс (user_id, created_at): спершу рівність, потім колонка сортування.

Як перевірити: у EXPLAIN не має бути вузла Sort над великою кількістю рядків - лише Index Scan з Limit. Саме на цьому тримається keyset-пагінація.

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

Частковий індекс (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), або функціональний індекс.

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

Звичайний CREATE INDEX у PostgreSQL бере блокування, яке забороняє запис у таблицю на весь час побудови. На таблиці в сотні мільйонів рядків це хвилини чи години простою для INSERT/UPDATE.

CREATE INDEX CONCURRENTLY будує індекс без блокування запису:

CREATE INDEX CONCURRENTLY orders_created_at_idx ON orders (created_at);

Ціна й підводні камені:

  • Працює довше: два проходи по таблиці й очікування завершення всіх транзакцій, що вже йшли.
  • Не можна в транзакції. У Laravel-міграції для цього потрібно вимкнути транзакцію міграції (public $withinTransaction = false;).
  • Якщо побудова впала (наприклад, порушено унікальність), лишається індекс у стані INVALID. Він не використовується для читання, але сповільнює запис. Його треба знайти (pg_index.indisvalid = false), видалити DROP INDEX CONCURRENTLY і створити знову.
  • Довга транзакція десь у системі змушує побудову чекати - перед операцією варто перевірити pg_stat_activity.

MySQL (InnoDB) з версії 5.6 будує більшість індексів online (ALGORITHM=INPLACE, LOCK=NONE), але на початку й наприкінці все одно потрібне коротке метаданих-блокування. Для великих таблиць часто використовують gh-ost чи pt-online-schema-change.

Загальне правило: зміни схеми великих таблиць ганяють окремо від деплою коду, з lock_timeout, щоб операція краще впала, ніж повісила всю базу в черзі за блокуванням.

Докладніше в документації: CREATE INDEX CONCURRENTLY

BRIN (Block Range INdex) зберігає не кожне значення, а підсумок для діапазону сторінок таблиці: мінімум і максимум значення колонки в кожних, скажімо, 128 сторінках.

Запит WHERE created_at >= '2026-10-01' перевіряє підсумки й читає лише ті діапазони сторінок, де значення можуть бути, - решту пропускає.

CREATE INDEX events_created_brin ON events USING brin (created_at);
CREATE INDEX events_created_brin ON events USING brin (created_at) WITH (pages_per_range = 32);

Головна перевага - розмір. На таблиці в сотні гігабайтів B-tree займає десятки гігабайтів, а BRIN - мегабайти. Він майже не впливає на швидкість вставки.

Коли BRIN працює: значення корелюють з фізичним розташуванням рядків на диску. Ідеальний випадок - журнали, події, метрики, де рядки лише дописуються в кінець, а час вставки зростає. Тоді кожен діапазон сторінок містить вузький проміжок часу.

Коли не працює:

  • Значення розкидані по таблиці випадково (наприклад, user_id у журналі подій) - кожен діапазон містить майже всі значення, і BRIN нічого не відсікає.
  • Часті UPDATE чи видалення з подальшими вставками на звільнене місце руйнують кореляцію.
  • Потрібен точковий пошук одного рядка - BRIN поверне діапазони сторінок, які доведеться перечитати.

Як перевірити придатність: correlation у pg_stats для колонки - близько до 1 чи -1 означає, що BRIN підійде.

Обслуговування: нові сторінки підсумовуються вакуумом або автоматично (autosummarize = on), або вручну brin_summarize_new_values(). Поки діапазон не підсумовано, його доводиться читати завжди.

Типова архітектура: BRIN за часом на великій таблиці подій + партиціонування за місяцями + B-tree лише на тих колонках, за якими справді шукають окремі записи.

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

Через MVCC UPDATE у PostgreSQL створює нову версію рядка. Звичайно нова версія лягає в інше місце таблиці, і кожен індекс таблиці отримує новий запис, що на неї вказує, - навіть індекси на колонках, які не змінювалися.

HOT (Heap-Only Tuple) - оптимізація, коли оновлення не чіпає індекси взагалі. Нова версія кладеться на ту саму сторінку, а стара посилається на неї ланцюжком. Індекси продовжують вказувати на стару позицію й знаходять нову через ланцюжок.

Умови HOT:

  1. Жодна індексована колонка не змінилася.
  2. На тій самій сторінці є вільне місце для нової версії.

Чому зайвий індекс шкодить UPDATE: індекс на колонці updated_at чи views_count, яку змінює майже кожне оновлення, робить HOT неможливим для всіх таких оновлень. Кожен UPDATE ... SET views_count = views_count + 1 тепер пише в усі індекси таблиці й більше роздуває їх.

Як збільшити частку HOT:

  • Не індексувати «гарячі» колонки, які часто змінюються, без явної потреби.
  • fillfactor менше 100 - залишати на сторінках вільне місце для нових версій:
ALTER TABLE counters SET (fillfactor = 80);

(діє для нових сторінок; для наявних - після VACUUM FULL чи перебудови).

  • Винести часто змінювані лічильники в окрему вузьку таблицю.

Як виміряти:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_percent
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

Низький відсоток HOT на таблиці з багатьма оновленнями - сигнал переглянути індекси й fillfactor.

Докладніше в документації: Heap-Only Tuples (HOT)

Кожен індекс уповільнює запис, займає пам'ять і диск, подовжує бекапи й вакуум. З роками в базі накопичуються індекси, створені «про всяк випадок» чи для запитів, яких уже немає.

1. Невикористані:

SELECT s.relname AS table, s.indexrelname AS index, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique           -- унікальні тримають цілісність
ORDER BY pg_relation_size(s.indexrelid) DESC;

2. Дублікати - однакові колонки в тому самому порядку:

SELECT indrelid::regclass AS table, array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey, indexprs::text, indpred::text
HAVING count(*) > 1;

3. Надлишкові за лівим префіксом. Індекс (user_id) зайвий, якщо є (user_id, created_at): другий обслуговує ті самі запити. Виняток - коли перший унікальний чи значно менший і використовується для гарячого запиту.

Перш ніж видаляти:

  • Переконатися, що статистика збиралася достатньо довго (щомісячні звіти, сезонні задачі).
  • Перевірити всі репліки - статистика окрема на кожному сервері.
  • Перевірити, чи індекс не підтримує зовнішній ключ (каскадне видалення без нього стане повним переглядом таблиці).
  • Видаляти через DROP INDEX CONCURRENTLY і мати скрипт для швидкого відновлення.

Інструменти: запити з PostgreSQL Wiki, pgstattuple, звіти в pganalyze, Percona Monitoring. Аналіз варто повторювати регулярно, а не раз на кілька років після проблем зі швидкістю.

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