Базы данных под нагрузкой: запросы, индексы, движки и цена масштабирования

Истории про производительность обычно скучны — пока не перестают быть таковыми. В дашборде всё зелёное и девяносто миллисекунд, а потом выкатывается кампания, отчёт собирает шесть джойнов, и внезапно оплата и сброс пароля стоят в одной очереди за одним и тем же диском. Редко спасает один рычаг: чаще это меньше 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;

Форма джойна и мощность связей

Вложенные циклы дёшевы, когда внутренняя сторона по индексу и небольшая; хэш‑соединения хороши на больших наборах — что выберет планировщик, зависит от движка и статистики. Если джойн раздувает строки из‑за многие-ко-многим без ограничений, чините модель, а не только statement_timeout.

Пагинация без полного скана

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

Слепо переписывать каждый IN (подзапрос) не нужно — сверяйтесь с EXPLAIN.

Агрегации и отчёты

Тяжёлый GROUP BY по сырым OLTP‑таблицам — классический способ убить хвост задержек. Предрасчёт в сводные таблицы, материализованные представления (Postgres) или OLAP (см. гайд по потокам событий), когда бизнесу нужна интерактивная аналитика.

Схема, которая не мстит через год

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

Типы индексов и когда они помогают

Не каждый индекс — B-tree, и не каждый B-tree — правильная форма.

Идея Типичное применение Замечания
B-tree (по умолчанию) Равенство и диапазон по сравнимым типам Большинство «обычных» индексов; в составном порядок колонок критичен — ведущие должны совпадать с типичным WHERE/ORDER BY.
Hash Точное равенство (где поддерживается) У Postgres у hash‑индексов в старых версиях были нюансы с репликацией — смотрите версию и документацию. В MySQL hash ближе к MEMORY и внутренностям InnoDB, а не замена размышлению про B-tree.
Полнотекст Поиск по токенам InnoDB FTS в MySQL против tsvector + GIN в Postgres — разные парсеры, ранжирование, обслуживание.
GIN / GiST (Postgres) JSONB, массивы, полнотекст, часть гео Мощно; следите за размером, раздутием и стоимостью построения.
Пространственные Геозапросы Часто стандарт de facto — 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, CI и rolling deploy; процедуры живут отдельно. Расхождение веток и окружений становится болью.
  • Тестирование — бизнес‑правила в PHP/Java проще покрывать привычными тестами; триггеры, которые мутируют строки на INSERT, сложнее изолировать.
  • Переносимость — логика в приложении переживает смену СУБД; PL/pgSQL или MySQL‑процедуры привязывают и усложняют blue/green.
  • Наблюдаемость — APM и стектрейсы центрированы на приложении; цепочки триггеров дают непонятную задержку, если их специально не инструментировать.

Когда логика в СУБД оправдана:

  • Ограничения — CHECK, внешние ключи, уникальность по бизнес‑ключу дешевле, чем надеяться, что каждый сервис помнит инвариант.
  • Идемпотентность — уникальный индекс вместо гонки «SELECT потом INSERT».
  • Тонкие триггеры на аудит — если это документировано и миграции проверяются.

Полезный лозунг не «никогда в базе», а «дорогие бизнес‑процессы держите в коде, который откатывается так же, как любой релиз».

MySQL и PostgreSQL на практике

Обе СУБД годятся для прода. Отличия кусаются, когда их считают взаимозаменяемыми.

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

Примеры, где синтаксис расходится

Postgres — вхождение JSONB:

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

MySQL — извлечение из JSON и индекс (8.x):

SELECT id
FROM events
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.kind')) = 'purchase';
-- Часто добавляют STORED generated column + индекс под нагрузкой.

Эвристики выбора

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

Обе наградят дисциплиной и проиндексированными запросами; ни одна не простит пропущенный VACUUM/purge и плохие индексы.

Пути масштабирования и проблемы, которые они привозят

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

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

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

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

Если код предполагает прочитал свою запись с реплики — пользователи видят «сохранил — обновил — пропало».

Пулы соединений

Открывать TCP+auth на каждый HTTP‑запрос нельзя масштабировать. PgBouncer (Postgres) или пулеры у managed MySQL стоят между приложением и БД. Следите за режимами prepared statements и transaction pooling — ORM иногда предполагает сессионную привязку.

Кэш (Redis и т.д.)

Кэш маскирует горячие ключи, не лечит запись на primary. Нужны TTL, инвалидация и защита от cache stampede.

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

Отдельная схема/БД на сервис — это меньше случайных джойнов и яснее радиус поражения. Цена — распределённые транзакции или саги, когда один пользовательский шаг реально трогает несколько хранилищ.

Рабочие приёмы:

  • Таблица outbox в владельце события и релей в брокер (стыкуется с гайдом по очередям).
  • Read model из событий — 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 редко совпадает со вчерашним.