Повільний SELECT, який раніше відпрацьовував за 20 мілісекунд, раптом починає зависати на 12 секунд. База виросла з 50 тисяч рядків до 5 мільйонів, і кожен запит перетворився на лотерею. Знайомо?

Індекси, штука, про яку всі чули, але мало хто налаштовує усвідомлено. Додав пару штук, наче прискорилося, і забув. А через пів року INSERT гальмує сильніше, ніж SELECT без індексу, тому що кожне вставляння перебудовує п'ять непотрібних B-дерев.

Розберемо, як індекси працюють насправді, які типи бувають і, головне, за якими правилами їх будувати, щоб не наламати дров у production.

💡 Швидкий огляд:

• Розібратися в механіці B-tree, hash і складених індексів, без цього вибрати правильний тип неможливо
• Освоїти 7 ключових практик: від індексування зовнішніх ключів до видалення невикористовуваних індексів
• Навчитися читати EXPLAIN і відрізняти робочий індекс від непотрібного

Що таке індекс бази даних

Індекс бази даних — це окрема структура, яка зберігає відсортовані значення одного або кількох стовпців таблиці разом із вказівниками на рядки. Коли ви виконуєте SELECT ... WHERE category_id = 42, сервер без індексу сканує таблицю рядок за рядком (full table scan). З індексом знаходить потрібні записи за O(log n), як у телефонному довіднику.

Створюється індекс через CREATE INDEX:

1
-- Обычный индекс
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Уникальный индекс
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Але плата за швидкість читання, уповільнення запису. Кожен INSERT, UPDATE і DELETE змушений оновлювати не лише таблицю, а й усі пов'язані індекси. Три індекси на таблиці з мільйоном рядків, і масове вставляння просідає на порядок. Баланс між швидкістю читання і запису, центральне питання проєктування індексів.

Детальний розбір теми від Hussein Nasser: 497 тисяч підписників, інженерний підхід без води. На прикладі PostgreSQL показана внутрішня механіка індексів, чому один CREATE INDEX прискорює запит у 100 разів, а інший не дає нічого.

Типи індексів: який коли застосовувати

Вибір типу індексу визначає, наскільки ефективно база обробить ваш запит. Різні СКБД реалізують їх по-своєму, але принципи єдині.

B-tree (збалансоване дерево)

Стандартний тип у більшості реляційних СКБД, PostgreSQL, MySQL, Oracle і SQL Server використовують B-tree як індекс за замовчуванням. Зберігає ключі у відсортованому порядку, підтримує операції порівняння, діапазони (BETWEEN), префіксний пошук (LIKE 'prefix%') і сортування, найкращий вибір для переважної більшості випадків.

Hash-індекс

Працює тільки для точного порівняння =. Блискавичний на точкових запитах, але непотрібний для діапазонів і сортування. У PostgreSQL hash-індекси стали production-ready з версії 10; у MySQL їх немає в InnoDB (тільки в MEMORY).

Кластерний індекс

Визначає фізичний порядок рядків на диску. У MySQL InnoDB первинний ключ завжди кластерний, рядки зберігаються в порядку PRIMARY KEY. У SQL Server, один кластерний індекс на таблицю. Правильний вибір кластерного ключа (монотонно зростаючий: BIGSERIAL, AUTO_INCREMENT або UNIQUEIDENTIFIER з NEWSEQUENTIALID()) дає виграш на діапазонних запитах і запобігає фрагментації сторінок.

Складений індекс

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

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

Порядок стовпців важливий: першим ставлять стовпець із найвищою селективністю, що використовується в WHERE. Принцип найлівішого префікса: індекс (A, B, C) працює для WHERE A і WHERE A AND B, але не для WHERE B AND C.

Покривальний індекс

Містить усі стовпці, потрібні запиту, і для фільтрації, і для виведення. База бере все з індексу, не заходячи в таблицю. У PostgreSQL це INCLUDE-стовпці, у MySQL InnoDB, неявне покриття через кластерний індекс.

7 Ключових практик індексування

1. Індексуйте стовпці з WHERE

Перше правило: кожен стовпець, що регулярно використовується в WHERE, JOIN ... ON і HAVING, має бути проіндексований. Саме ці операції виграють від індексів найбільше.

Перед створенням індексу перевірте селективність колонки. Якщо в стовпці status всього три значення ('new', 'processing', 'done'), а рядків, 2 мільйони, індекс за status майже непотрібний: планувальник вибере full table scan як дешевший. Індекс має сенс, коли кількість унікальних значень достатньо велика відносно розміру таблиці.

2. Індексуйте стовпці сортування

ORDER BY без індексу — це filesort (MySQL) або explicit sort (PostgreSQL): сервер збирає всі рядки і сортує в пам'яті (або на диску, якщо work_mem/sort_buffer_size малі). Індекс за тими самими стовпцями, що й ORDER BY, безплатний, дані вже відсортовані в B-tree.

1
-- Без индекса по created_at: filesort на миллионах строк
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Индекс решает проблему
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Індексуйте GROUP BY і агрегатні стовпці

Групування без індексу вимагає повного сканування і побудови хеш-таблиці. Індекс за стовпцями GROUP BY перетворює операцію на потокову агрегацію: рядки вже згруповані в порядку ключа.

4. Індексуйте всі зовнішні ключі

Неіндексований зовнішній ключ, бомба сповільненої дії. DELETE FROM users WHERE id = 5 за наявності FOREIGN KEY (user_id) REFERENCES users(id) у таблиці orders без індексу за user_id — це full table scan orders на кожне видалення. Всі популярні СКБД вимагають індексу на зовнішній ключ або створюють його неявно (MySQL InnoDB, автоматично, PostgreSQL, ні).

5. Індексуйте унікальні стовпці та первинні ключі

Первинний ключ індексується автоматично (часто кластерним індексом). Явний UNIQUE INDEX захищає від дублікатів і водночас прискорює пошук. Будь-який стовпець із бізнес-обмеженням унікальності (наприклад email, slug або external_id) повинен мати унікальний індекс, і для цілісності, і для продуктивності.

6. Використовуйте кластерний індекс усвідомлено

Для великих таблиць (десятки мільйонів рядків) правильний кластерний ключ критичний. Хороший вибір, монотонно зростаюче значення: AUTO_INCREMENT, BIGSERIAL або UUID v7. Випадковий UUID як кластерний ключ спричиняє фрагментацію сторінок, кожне вставляння потрапляє у випадкове місце B-tree, розбиваючи заповнені сторінки на дві половинчасті.

7. Видаляйте невикористовувані індекси

Індекс, який не використовується в запитах,, чистий збиток. Він уповільнює запис, займає місце на диску і в буферному пулі, збиває з пантелику планувальник запитів. У PostgreSQL список невикористовуваних індексів дає системна таблиця pg_stat_user_indexes:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

У MySQL аналогічну інформацію надає sys.schema_unused_indexes (починаючи з версії 5.7). Заплануйте щомісячний аудит і видаляйте індекси, які не використовувалися жодного разу за звітний період.

Як перевірити, чи працюють ваші індекси

Створили індекс, перевірте, що він дійсно застосовується. Команда EXPLAIN (або EXPLAIN ANALYZE) показує план виконання запиту і фактичне використання індексів:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

У виводі шукайте рядки Index Scan або Index Only Scan (PostgreSQL) / Using index (MySQL). Якщо бачите Seq Scan (PostgreSQL) або Using where; Using filesort (MySQL), індекс не використовується. Причини: низька селективність, невідповідний тип індексу, незбіг порядку стовпців у складеному індексі, застаріла статистика (ANALYZE table_name;).

Регулярно моніторте метрики: pg_stat_user_indexes.idx_scan у PostgreSQL, sys.schema_index_statistics у MySQL. Індекс із нулем сканувань за місяць, кандидат на видалення.

⁉️🤔 Часті питання

Скільки індексів має бути в таблиці?

Оптимально, від 2 до 6 індексів на активно використовувану таблицю. Менше двох, майже напевно є неоптимальні запити. Більше шести, уважно перевірте, чи всі реально потрібні: кожен зайвий індекс гальмує запис. Для таблиць-довідників (рідкісний запис, часте читання) більше індексів виправдано. Для високонавантажених операційних таблиць (багато INSERT/UPDATE) тримайте мінімум.

Чим складений індекс кращий за кілька одинарних?

Складений індекс (A, B) — це ОДНА структура. Сервер проходить по ній один раз. Три окремі індекси (A), (B) і (C) при WHERE A=1 AND B=2 змушують сервер або вибрати один індекс (і фільтрувати залишок), або робити bitmap index scan (злиття бітових карт). Складений індекс майже завжди ефективніший, якщо порядок стовпців відповідає запитам.

Коли індекс не допомагає, а заважає?

Три типові випадки. Перший: таблиця маленька (до пари тисяч рядків), full table scan швидший, ніж читання індексу плюс добір рядків. Другий: індекс на стовпці з низькою селективністю (is_deleted, status із трьома значеннями). Третій: масові вставляння в ETL/імпорті, індекси перебудовуються на кожному батчі, дропайте їх перед завантаженням і створюйте заново після.

Чи потрібно індексувати стовпці для JOIN?

Обов'язково. Кожен JOIN без індексу на зв'язувальному стовпці зовнішньої таблиці — це nested loop із повним скануванням. Для LEFT JOIN orders ON users.id = orders.user_id індекс на orders.user_id перетворює nested loop на index lookup. Індексуйте стовпці, за якими йдуть з'єднання, завжди.

B-tree чи hash: що вибрати для точного пошуку?

Для = hash швидший: пошук за хешем займає константний час, тоді як B-tree йде по дереву за логарифмічну кількість кроків. Але hash не підтримує діапазони, сортування і UNIQUE. На практиці B-tree покриває переважну більшість сценаріїв; hash, нішевий інструмент для точкових lookup-ів за ключем у високонавантажених системах (сесії, кеш). У PostgreSQL hash-індекси з версії 10 production-ready і займають менше місця, ніж B-tree.

Чи варто індексувати «про всяк випадок»?

Ні. Кожен індекс — це компроміс. Він прискорює читання ціною уповільнення запису і додаткового дискового простору. Індексуйте не «про всяк випадок», а під конкретні запити, які реально виконуються в застосунку. Профілюйте повільні запити (pg_stat_statements, slow_query_log), додавайте індекси точково і перевіряйте EXPLAIN до і після.

Головний підсумок простий: індекси, інструмент, а не самоціль. Один правильний складений індекс може замінити три одинарні і зекономити гігабайти диска. А один невикористовуваний індекс на таблиці з інтенсивним записом, уповільнити весь застосунок.

Хочете копнути глибше, почніть з офіційного посібника з дизайну індексів SQL Server і документації PostgreSQL за типами індексів. А якщо зустріли запит, який не лікується індексами, можливо, проблема в самій моделі даних.