---
title: 'Бази даних під навантаженням: запити, індекси, MySQL vs Postgres, масштабування | DevSense'
description: 'Як оптимізувати SQL та схеми, вибирати типи індексів, коли бізнес-логіка в БД стає проблемою, чим відрізняються MySQL та PostgreSQL у продакшені та яка реальна ціна реплік, мікросервісної декомпозиції та шардування.'
faq:
- { question: 'Чому постраникова навігація через порівняння (keyset pagination) швидша, ніж використання OFFSET на великих обсягах даних?', answer: 'OFFSET змушує СУБД сканувати та відкидати всі рядки, що передують вибраній сторінці (наприклад, прочитати 100 000 рядків заради виведення 20). Keyset-навігація використовує фільтр порівняння (наприклад, `WHERE (created_at, id) < (?, ?)`) на основі останнього побаченого елемента, дозволяючи СУБД відразу перейти до потрібних записів за B-Tree індексом без сканування попередніх.' }
- { question: 'Коли варто використовувати покриваючий індекс (covering index) замість звичайного?', answer: 'Покриваючий індекс (часто з використанням секції `INCLUDE` або складеного індексу) застосовується, коли високочастотний запит запитує лише кілька конкретних колонок. Це дозволяє СУБД повернути результат прямо зі структури індексу (Index Only Scan) без звернення до фізичних сторінок таблиці на диску.' }
- { question: "З якими основними компромісами пов'язане використання реплік для читання (read replicas)?", answer: 'Репліки знімають навантаження на читання з основного сервера (primary), підвищуючи загальну пропускну здатність. Однак затримка реплікації (replication lag) призводить до того, що репліки можуть віддавати застарілі дані. Розробникам необхідно явно направляти критичні до читання запити (наприклад, відразу після запису) на майстер-сервер.' }
- { question: 'Чому шардування вважається крайнім заходом масштабування баз даних?', answer: 'Шардування розділяє рядки по різних фізичних серверах, що радикально ускладнює експлуатацію. Воно робить неможливими прості JOIN-запити між шардами, ускладнює дотримання цілісності транзакцій (вимагає розподілених транзакцій) та контроль унікальності ключів, а також вимагає складного процесу перерозподілу (rebalancing) даних при зростанні навантаження.' }
published: '2026-05-31'
---
# Бази даних під навантаженням: тюнінг запитів, індекси, рушії та компроміси масштабування
Історія продуктивності будь-якого застосунку зазвичай виглядає нудно до певного часу. На графіках час відповіді тримається в районі 90 мілісекунд, але запускається маркетингова кампанія, складний звіт склеює шість таблиць — і раптом **оформлення замовлення** та **скидання пароля** застряють в одній черзі до диска. Прості рішення на кшталт «підкрутити конфіги» допомагають рідко. Потрібна системна робота: **скорочення мережевих запитів (round-trips)**, **індекси під реальні вибірки**, **чесний розрахунок ємності** та визнання того, що одна база даних не може рости нескінченно.
**Пов’язані матеріали:** [Високонавантажений збір подій](high-load-event-ingestion) · [Порівняння черг повідомлень](message-queues-compared) · [Спостережуваність і моніторинг](observability-monitoring-laravel)
## Зміст
* [Вимірюйте перед оптимізацією](#measure)
* [Оптимізація на рівні запитів з прикладами](#queries)
* [Проектування схем, які добре старіють](#schema)
* [Типи індексів та область їх застосування](#indexes)
* [Чому складна логіка всередині БД шкодить командам](#db-logic)
* [MySQL проти PostgreSQL на практиці](#mysql-vs-postgres)
* [Шляхи масштабування та проблеми, які вони приносять](#scaling)
* [Декомпозиція даних без ілюзій](#decomposition)
* [Шардування: ключі, болі міжшардових JOIN та ребаланс](#sharding)
* [Часті помилки](#common-mistakes)
* [Чеклист перед покупкою заліза або шардуванням](#checklist)
* [Квіз для самоперевірки](#self-test-quiz)
---
## Вимірюйте перед оптимізацією
**Перцентилі затримки (latency percentiles)** важливіші за середні значення. Середній час 40 мс може приховувати хвіст, де **1%** користувачів чекає відповіді більше двох секунд через блокування або холодний кеш.
Практичні індикатори:
* **Лог повільних запитів (slow query log)** з порогом, який переглядається в міру зростання бази (не логуйте все підряд, інакше команда звикне ігнорувати шум).
* Аналіз плану виконання **`EXPLAIN` (Postgres / MySQL 8+)** на реальних продакшн-параметрах, а не тільки на штучних прикладах.
* **Кількість з'єднань** та **утилізація пулу**; часто «повільна база» — це просто час очікування вільного коннекту в черзі застосунку.
> [!NOTE]
> **Спільний стан**
> База даних — це спільний стан. Будь-який запит, який невиправдано вантажить **CPU, блокування або диск**, безпосередньо уповільнює всі інші операції в системі.
---
## Оптимизація на рівні запитів з прикладами
### Вибирайте тільки потрібні поля
Вибірка `SELECT *` змушує СУБД переносити зайві байти, які потім просто відкидаються в коді застосунку. Завжди пишіть явний список колонок, особливо для таблиць з великими текстовими полями або JSON.
```sql
-- database/queries/fetch_users.sql
-- Погано (передає всі колонки, включаючи важкі blob/text)
SELECT * FROM users WHERE country = 'DE' LIMIT 50;
-- Добре
SELECT id, email, display_name, created_at
FROM users
WHERE country = 'DE'
ORDER BY created_at DESC
LIMIT 50;
```
### Структура JOIN та кардинальність
Вкладені цикли (nested loops) ефективні, коли внутрішня таблиця **використовує індекс** і повертає мало рядків. Хеш-джоіни (hash joins) краще працюють на великих обсягах даних. СУБД вибирає стратегію на основі внутрішньої статистики. Якщо JOIN різко збільшує кількість рядків через відношення **багато-до-багатьох без зовнішніх ключів**, виправляйте модель, а не таймаути.
### Пагінація без сканування всієї таблиці
Використання `OFFSET` виглядає простим, але при великих зміщеннях СУБД **все одно сканує** та відкидає всі попередні рядки.
```sql
-- database/queries/offset_pagination.sql
-- Уповільнюється пропорційно зростанню зміщення :offset
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;
```
**Keyset (seek) пагінація** використовує значення колонок останнього прочитаного рядка:
```sql
-- database/queries/seek_pagination.sql
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
```
Для ефективної роботи потрібен складений індекс, що відповідає сортуванню, наприклад `(created_at DESC, id DESC)`.
### Використання `EXISTS` замість `IN`
Для перевірки існування записів напівз'єднання (semi-joins) зазвичай працюють швидше:
```sql
-- database/queries/exists_lookup.sql
SELECT o.id, o.total_cents
FROM orders o
WHERE EXISTS (
SELECT 1 FROM order_items i
WHERE i.order_id = o.id AND i.sku = 'GOLD-001'
);
```
### Агрегації та аналітика
Важкі групування `GROUP BY` на «гарячих» OLTP-таблицях — вірний спосіб **зламати продуктивність**. Рахуйте агрегати заздалегідь у **вітрини даних**, **матеріалізовані подання** (у Postgres) або переносьте їх у спеціалізовані **OLAP-бази** (ClickHouse та ін.), якщо бізнесу потрібна інтерактивна аналітика в реальному часі.
---
## Проектування схем, які добре старіють
* **Типи даних мають відповідати реальності** — зберігайте гроші в **копійках/центах (цілі числа)** або у форматі **decimal** з фіксованою точністю, але не у float/double.
* **Ставлення до NULL** — nullable-колонки ускладнюють індексування та статистику СУБД. Використовуйте їх тільки там, де відсутність значення дійсно має бізнес-сенс.
* **Зовнішні ключі (Foreign keys)** — вони трохи уповільнюють запис, але гарантують **цілісність даних**. Відключення зовнішніх ключів «заради швидкості» майже завжди призводить до **сирітських рядків** та помилок у логіці застосунку.
* **Денормалізація** — чиста теорія нормалізації часто пасує перед реальністю читання даних. Розумна, задокументована **денормалізація полів** може позбавити від важких JOIN-ів, але вимагає **контролю консистентності** на стороні застосунку.
---
## Типи індексів та область їх застосування
Не всі індекси однакові, і B-Tree не завжди є найкращим рішенням.
| Тип індексу | Використання | Примітки |
|------|-------------|--------|
| **B-Tree (дерево)** | Порівняння, діапазони, сортування | Базовий індекс. **Порядок колонок важливий** для складених індексів: першими мають йти найбільш селективні поля. |
| **Hash (хеш)** | Точна відповідність (`=`) | Менше можливостей порівняно з B-Tree. У MySQL доступний в пам'яті або як внутрішній адаптивний індекс. |
| **Full-text (повнотекстовий)** | Пошук слів у тексті | Різні реалізації в InnoDB FTS та Postgres `tsvector` + GIN. |
| **GIN / GiST (Postgres)** | Пошук у JSONB, масивах, гео-даних | Потужний інструмент, але вимагає контролю за **роздуванням індексу (bloat)** та швидкістю запису. |
| **Spatial (просторовий)** | Географічні запити | Залежить від СУБД (розширення PostGIS для Postgres, нативна підтримка в MySQL). |
### Порядок колонок у складеному індексі
Якщо запити майже завжди фільтруються за `tenant_id`, ця колонка має бути **першою** в індексі:
```sql
-- database/migrations/create_composite_index.sql
-- Чудово для запитів вигляду: WHERE tenant_id = ? AND status = 'open'
CREATE INDEX orders_tenant_status_idx ON orders (tenant_id, status);
```
### Покриваючі індекси (Covering indexes)
Якщо індекс **містить всі поля запиту**, СУБД може віддати результат прямо з індексу, не зчитуючи сам рядок з таблиці (у Postgres це називається **Index Only Scan**).
```sql
-- database/migrations/create_covering_index.sql
CREATE INDEX sessions_lookup_idx ON sessions (user_id) INCLUDE (last_seen_at);
```
### Часткові / відфільтровані індекси (Partial indexes)
Якщо фільтр у запиті статичний і відсікає більшу частину таблиці, індексуйте тільки потрібний зріз:
```sql
-- database/migrations/create_partial_index.sql
-- Приклад для PostgreSQL
CREATE INDEX invoices_open_due_idx
ON invoices (due_at)
WHERE status = 'open' AND due_at IS NOT NULL;
```
---
## Чому складна логіка всередині БД шкодить командам
Збережені процедури, тригери та складні в'юхи — це не «погані технології». Це **питання володіння кодом та розгортання**.
Основні проблеми:
* **Версіонування** — код застосунку проходить через Git, CI/CD та поступовий деплой. Процедури СУБД оновлювати складніше, виникає дрифт схем між оточеннями.
* **Тестування** — бізнес-логіку в застосунку легко покрити юніт-тестами. Логіку в тригерах тестувати ізольовано вкрай важко.
* **Портованість** — код застосунку легко перенести. Логіка на PL/pgSQL намертво прив'язує вас до конкретної СУБД.
* **Спостережуваність (observability)** — трейси та APM добре бачать межі застосунку. Ланцюжки тригерів виглядають як **непоясненна затримка бази даних**, поки ви не почнете детальний профайлінг.
Де логіка на стороні СУБД **все ще потрібна**:
* **Обмеження цілісності** — `CHECK`, зовнішні ключі та унікальні індекси гарантують збереження даних надійніше, ніж надія на те, що жоден мікросервіс не забуде валідацію.
* **Ідемпотентність** — унікальний індекс на рівні БД вирішує проблему стану гонки (race conditions) при паралельних запитах.
---
## MySQL проти PostgreSQL на практиці
Обидві бази готові до продакшену, але вони влаштовані по-різному.
| Тема | MySQL (InnoDB) | PostgreSQL |
|-------|-------------------------|------------|
| **Зберігання даних** | Таблиці з кластеризованим **первинним ключем** (дані лежать у листках PK); вторинні індекси посилаються на PK | Дані лежать у купі (**heap**); індекси посилаються на фізичну адресу рядка (TID) |
| **MVCC та сміття** | Історія змін в **Undo-лозі**; очищення відбувається в фоні автоматично | Мертві рядки залишаються в таблиці; потрібен регулярний **VACUUM** (autovacuum) |
| **Можливості SQL** | Хороший стандарт; історично більш м'який до типів даних | Багаті CTE, віконні функції, **LATERAL JOIN**, повноцінний **JSONB**, типи-діапазони |
| **Розширення** | Практично відсутні в ядрі | Величезна екосистема (**PostGIS**, **pgvector**, **citext** та ін.) |
| **Реплікація** | Зріла реплікація на основі **binlog** | Фізична та логічна репликація |
Приклади роботи з JSON, що показують різницю синтаксису:
Postgres (оператор **containment** для JSONB):
```sql
-- database/queries/postgres_jsonb_containment.sql
SELECT id FROM events WHERE payload @> '{"kind":"purchase"}'::jsonb;
```
MySQL (вилучення з JSON у версії 8.x):
```sql
-- database/queries/mysql_json_extract.sql
SELECT id FROM events WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.kind')) = 'purchase';
```
---
## Шляхи масштабування та проблеми, які вони приносять
### Вертикальне масштабування (апгрейд заліза)
Найпростіший шлях, поки ви не упретеся в ліміти заліза, пропускну здатність шини або вартість enterprise-обладнання. Також зростає ціна одиничного збою (Single Point of Failure).
### Репліки для читання (Read replicas)
* **Плюси:** розвантажують майстер-сервер від важких SELECT-ів, використовуються для бекапів.
* **Мінуси:** **затримка реплікації** призводить до читання застарілих даних.
### Пулери з'єднань
Модель «одне TCP-з'єднання на запит» не масштабується під навантаженням. Спеціалізовані пулери на кшталт **PgBouncer** (Postgres) або ProxySQL (MySQL) знижують оверхед.
### Кешування (Redis, Memcached)
Кеш маскує часті читання, но не вирішує проблему **ампліфікації запису** при високому потоці оновлень даних.
---
## Декомпозиція даних без ілюзій
**Виділення баз даних під сервіси (database-per-service)** — це не мода, а спосіб розмежувати контексти та зменшити пов'язаність. При цьому ви втрачаєте можливість робити прості JOIN-запити між таблицями різних сервісів, переходячи на рівень **розподілених транзакцій** (Saga та ін.).
Антипатерн: **кілька сервісів пишуть в одну таблицю** — це класичний розподілений моноліт, де база даних стає прихованою точкою інтеграції.
---
## Шардування: ключі, болі міжшардових JOIN та ребаланс
**Шардування** — це розподіл рядків таблиці по **різних фізичних базах даних** на основі **ключа шардування** (наприклад, `user_id` або `tenant_id`).
Что поліпшується:
* Знімаються обмеження щодо диска та CPU одного сервера на запис.
* Падіння однієї ноди зачіпає тільки частину користувачів (blast radius).
З якими складнощами ви зіткнетеся:
* Неможливо зробити JOIN між шардами без вивантаження даних на рівень застосунку.
* Неможливо гарантувати унікальність по полях, що не містять ключ шардування, без зовнішніх координаторів.
* **Ребалансування** — якщо один із клієнтів (наприклад, велика компанія) перевантажує свій шард, перенесення даних на інший сервер перетворюється на складну операцію.
---
## Часті помилки
1. **Використання OFFSET на мільйонних таблицях**: Використання постраничної навігації вигляду `LIMIT ... OFFSET`, що змушує СУБД сканувати мільйони рядків перед поверненням результату.
2. **Невірний порядок колонок у складеному індексі**: Розміщення діапазонного фільтра (наприклад, `created_at`) перед точними фільтрами рівності в отриманні індексу.
3. **Спільна база даних для мікросервісів**: Прямий доступ різних сервісів до одних і тих самих таблиць БД, що призводить до неявної пов'язаності та неможливості незалежної зміни схем.
4. **Складна бізнес-логика в тригерах**: Винесення валідації та розрахунків у тригери СУБД, що позбавляє команду тестів, контролю версій та наскрізного моніторингу.
---
## Чеклист перед покупкою заліза або шардуванням
1. **Чи показує `EXPLAIN`** послідовне сканування таблиці (Seq Scan), яке можна виправити простим індексом?
2. **Чи не викликані затримки** проблемою N+1 запитів на рівні ORM застосунку?
3. **Що саме є вузьким місцем:** дисковий I/O, CPU чи час очікування блокувань через довгі транзакції?
4. **Чи готовий застосунок** до затримок реплікації при перенесенні читань на репліки?
5. **Чи можна зменшити розмір бази** архівацією старих даних у холодне сховище замість ускладнення топології?
---
## Підсумок
Бази даних люблять передбачуваність, коректне проектування та постійний моніторинг. Індекси та вибір рушія дозволяють виграти час; **архітектура** — черги, розділення моделей читання та запису (CQRS) та шардування — визначає межі масштабування вашої системи.
---
## Квіз для самоперевірки
### Запитання 1: Чому запит вигляду `SELECT * FROM users ORDER BY created_at DESC LIMIT 1` може працювати повільно на таблиці з 10 млн рядків, навіть якщо колонка `created_at` проіндексована?
- A) Індекси B-Tree принципово не підтримують сортування за спаданням.
- B) Якщо колонка `created_at` допускає значення NULL, СУБД може сканувати всю таблицю для пошуку NULL-значень, ігноруючи індекс.
- C) Обов'язково потрібно використовувати покриваючий індекс.
Показати правильну відповідь
**Правильна відповідь: Б**
Якщо колонка дозволяє NULL і в запиті немає фільтра на кшталт `WHERE created_at IS NOT NULL`, планувальник СУБД може вирішити, що вигідніше просканувати всю таблицю (Seq Scan), щоб правильно позиціонувати NULL-значення при сортуванні.
### Запитання 2: Який тип індексу найкраще підходить для пошуку ключів всередині довільних документів JSONB в PostgreSQL?
- A) B-Tree індекс.
- B) GIN індекс (Generalized Inverted Index).
- C) Hash індекс.
Показати правильну відповідь
**Правильна відповідь: Б**
Індекси GIN призначені спеціально для індексації складених значень, таких як масиви та структури JSONB, забезпечуючи швидкий пошук за вкладеними ключами та значеннями.