Бази даних під навантаженням: запити, індекси, рушії та компроміси масштабування

Історії про продуктивність зазвичай нудні — доки перестають бути такими. У дашборді все зелене й дев’яносто мілісекунд, а тоді запускається кампанія, звіт збирає шість джойнів і раптом оплата та скидання пароля стоять в одній черзі за одним сховищем. Рідко рятує один регулятор: частіше це менше round-trip’ів, індекси під реальні предикати, чесна ємність і іноді визнання, що одна логічна база не безмежна.

Пов’язані матеріали: Високонавантажений потік подій · Порівняння черг і брокерів · Sail: бази та Docker-сервіси

Зміст

Спочатку вимірюйте, потім «оптимізуйте»

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

Практичні сигнали:

  • Журнал повільних запитів із порогом, який переглядають із ростом даних — не «усе підряд», інакше звикнете до шуму.
  • EXPLAIN у Postgres / EXPLAIN у MySQL 8+ на тих самих формах, що в проді (з реальними параметрами).
  • Кількість з’єднань і заповнення пулу; частина інцидентів «база гальмує» — це черга за конектом, а не диск.

Тримайте в голові: база — спільний стан. Усе, що навантажує CPU, блокування чи I/O на первинному вузлі, змагається з рештою критичного шляху.

Оптимізація запитів із прикладами

Забирайте лише потрібне

Широкий SELECT * по «товстих» рядках змушує СУБД тягти байти, які застосунок відкине. Явний список колонок особливо важливий для великого тексту чи JSON.

-- Гірше: усі колонки, включно з тим, що не віддаєте в API
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;

Форма джойна та потужність зв’язків

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

Пагінація без повного проходу

OFFSET на великих зміщеннях часто змушує рушій обходити пропущені рядки.

-- Уповільнюється, коли :offset великий
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

Keyset-пагінація використовує останній ключ сортування:

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

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'
);

Переписувати все сліпо не варто — перевіряйте EXPLAIN.

Агрегації

Важкий GROUP BY по сирих OLTP-таблицях б’є по хвосту затримок. Попередній розрахунок у зведені таблиці, materialized views (Postgres) або OLAP.

Схема, яка не мститься за рік

  • Типи під гроші — мінорні одиниці цілим або DECIMAL з явною точністю, не «float», якщо важлива звірка.
  • NULL — коли «невідомо» — частина домену; як лінивий дефолт ускладнює індекси й статистику.
  • Зовнішні ключі — трохи коштують на записі й дають цілісність. Вимкнення «ради швидкості» часто дає сиріт і недетермінізм у застосунку.
  • Нормалізація проти читання — чиста теорія не знає частоти ваших вибірок. Свідома денормалізація з документацією може перемогти джойни — ціною складності запису та інваріантів.

Типи індексів і коли вони допомагають

Ідея Типове застосування Примітки
B-tree (типово) Рівність і діапазон Більшість індексів; у складеному порядок колонок критичний — провідні мають збігатися з типовим WHERE / ORDER BY.
Hash Точна рівність (де є) У Postgres у старих версій були нюанси з реплікацією — дивіться версію. У MySQL hash ближче до MEMORY і внутрішньостей InnoDB.
Повнотекст Пошук по токенах InnoDB FTS у MySQL проти tsvector + GIN у Postgres.
GIN / GiST (Postgres) JSONB, масиви, повнотекст, частина гео Потужно; стежте за роздуттям і вартістю побудови.
Просторові Геозапити Часто еталон — PostGIS на Postgres; у MySQL свої правила типів.

Складений індекс: порядок колонок

Якщо майже завжди фільтр по tenant_id, він зазвичай перший:

-- Добре для WHERE tenant_id = ? AND status = 'open'
CREATE INDEX orders_tenant_status_idx ON orders (tenant_id, status);

Запит лише по status може не використати індекс ефективно — інколи потрібен другий індекс або свідомий компроміс.

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

Якщо індекс містить усі колонки з SELECT, план може обійтися лише ним (у Postgres — Index Only Scan; у InnoDB — за відповідного кластерного ключа).

CREATE INDEX sessions_lookup_idx ON sessions (user_id) INCLUDE (last_seen_at);
-- INCLUDE — Postgres 11+; у MySQL часто складений (user_id, last_seen_at).

Частковий індекс (Postgres)

CREATE INDEX invoices_open_due_idx
ON invoices (due_at)
WHERE status = 'open' AND due_at IS NOT NULL;

У MySQL 8+ є індекси за виразами; часткові простіше в Postgres — наближення через згенеровану колонку + індекс.

Чому важка логіка в базі б’є по команді

Тригери, збережені процедури й складні уявлення — це вибір деплою й власності, а не «зло само по собі».

Що часто ламається:

  • Версіонування — код застосунку їде через Git і rolling deploy; рутини живуть окремо, дрейф між гілками болючий.
  • Тестування — правила в PHP/Java зручніше покривати звичними тестами; ланцюги тригерів важче ізолювати.
  • Переносимість — логіка в застосунку переживає зміну СУБД; PL/pgSQL або MySQL-процедури прив’язують і ускладнюють blue/green.
  • Спостережуваність — APM зосереджений на застосунку; тригери дають непрозору затримку без додаткової інструментації.

Коли логіка в СУБД виправдана: CHECK, зовнішні ключі, унікальність за бізнес-ключем, тонкий аудит із документацією та тестами міграцій.

Корисний девіз: не «ніколи в базі», а «важкі бізнес-процеси — у коді з тим самим пайплайном релізу, що й решта системи».

MySQL і PostgreSQL на практиці

Обидві СУБД придатні для проду. Відмінності кусаються, якщо вважати їх взаємозамінними.

Тема MySQL (зазвичай InnoDB) PostgreSQL
Зберігання рядків Кластеризація по PK; вторинні індекси вказують на PK Купа; індекси окремо; CLUSTER — обслуговувальна операція
MVCC / прибирання Undo; відставання purge при довгих транзакціях Мертві кортежі; VACUUM (autovacuum) — рутина експлуатації
Можливості SQL Міцне ядро SQL Багатші CTE, віконні функції, LATERAL, JSONB, діапазонні типи
Розширення Вужче в ядрі PostGIS, pgvector, citext тощо
Реплікація Зрілий binlog Фізична та логічна; публікації/підписки для вибіркових потоків
Сюрпризи ізоляції Дефолти інколи дивують приходьків з Postgres Знайома багатьом картина MVCC; залишаються аномалії серіалізації
JSON JSON; у 8.x — функціональні індекси JSONB, GIN, оператори вмісту

JSONB у Postgres:

SELECT id FROM events
WHERE payload @> '{"kind":"purchase"}'::jsonb;

MySQL 8.x (часто з generated STORED + індекс під навантаженням):

SELECT id FROM events
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.kind')) = 'purchase';

Евристики вибору

  • Postgres — багатий SQL, JSONB, гео, розширення, логічна реплікація між шарами.
  • MySQL/MariaDB — якщо хостинг, досвід команди чи екосистема вже зосереджені там.

Обидві винагороджують індексовані запити та дисципліну обслуговування (vacuum/purge).

Шляхи масштабування та проблеми, які вони привозять

Вертикальне масштабування

Просте, доки не вперлися в однопотокове застосування реплікації, пропускну здатність диска та NUMA. Плюс одна зона відмови.

Репліки для читання

Плюси: зняти читання, бекапи, копії під звіти. Мінуси: лаг реплікації — читання застарілі; маршрутизація має бути явною (у Laravel можна розвести read/write у конфігурації).

Якщо код очікує прочитати свою запис з репліки — користувачі бачать «зберіг — оновив — зникло».

Пул з’єднань

Відкривати TCP+auth на кожен HTTP-запит не масштабується. PgBouncer (Postgres) або пулери в керованому MySQL стоять між застосунком і БД. Увага до prepared statements і transaction pooling — ORM інколи припускає сесійну прив’язку.

Кеш (Redis тощо)

Кеш маскує гарячі ключі, не лікує підсилення запису на primary. Потрібні TTL, інвалідація і захист від cache stampede.

Декомпозиція без казок

Окрема база на сервіс — це менше випадкових джойнів і зрозуміліший радіус ураження. Ціна — розподілені транзакції або саги, коли одна дія користувача реально перетинає кілька сховищ.

Патерни: таблиця outbox у власника події та релей у брокер (зв’язок із гайдом про черги); read models, перебудовані з подій, з чесним eventual consistency у UX.

Антипатерн: два сервіси пишуть одну таблицю — це розподілений моноліт із зайвими мережевими викликами.

Шардінг: ключ, кросс-шардовий біль і ребаланс

Шардінг ділить рядки між кількома первинними вузлами за ключем (часто user_id, tenant_id, регіон).

Що покращує: стелю запису на машину; blast radius може звузитися при ізоляції збоїв — якщо застосунок переносить часткову деградацію.

Що погіршує: кросс-шардові джойни та глобальна унікальність — потрібні координація, 2PC (дорого) або правила в застосунку; ребаланс при гарячому орендарі — міграційний проект; операційна складність — N копій бекапу, патчів і міграцій схеми.

-- Однакова схема на кожному шарді; tenant_id = 42 на шарді від hash(tenant_id).
-- Звіт «по всіх орендарях» = fan-out по всіх шардах зі злиттям — дорого й крихко.

Більшості команд варто вичерпати індекси, запити, кеш, репліки та архів/партиціонування до клієнтського шардованого OLTP.

Чекліст перед шардами та залізом

  1. Чи показує EXPLAIN послідовне сканування, яке чесний індекс прибере?
  2. Чи немає N+1 на рівні ORM незалежно від швидкості диска?
  3. Вузьке місце — з’єднання, CPU на БД чи блокування через довгі транзакції?
  4. Чи виміряний лаг реплікації, якщо читання пішло на репліки?
  5. Чи можна обмежити ріст утриманням і архівом партицій дешевше, ніж нова топологія?

Бази винагороджують нудну коректність і вимірювання. Індекси й вибір рушія купують час; архітектура — черги, окремі read model і інколи шарди — купує стелю. Обирайте найменшу зміну, що прибирає виміряне вузьке місце, і знову заміряйте: завтрашній bottleneck рідко збігається з учорашнім.