--- 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, обеспечивая быстрый поиск по вложенным ключам и значениям.