Оптимизация баз данных: как найти медленный запрос и научить базу отвечать быстро

В предыдущих статьях серии мы научились передавать запрос через API, складывать тяжёлую работу в очередь, держать готовые ответы в кэше, размазывать нагрузку по нескольким серверам, смотреть метрики и находить виновника по логам. Но почти любой запрос в итоге упирается в одно место — базу данных. И именно там чаще всего всё и тормозит: страница грузится три секунды, потому что база три секунды перебирает миллион строк.

Хорошая новость: база данных — очень честная система. Она почти никогда не «тормозит сама по себе». Она медленно отвечает, потому что её попросили сделать много лишней работы. Наша задача — эту лишнюю работу увидеть и убрать.

Плохая новость: без навыков вы будете угадывать. Оптимизация «на глаз» — это когда добавляют три индекса, перезагружают сервер, ставят кэш поверх кэша, а через месяц нагрузка возвращается и становится ещё хуже. Поэтому пойдём по порядку: сначала научимся измерять, потом разберём индексы, потом — масштабирование (шардинг и репликацию), задержку репликации, пул соединений и самое частое практическое зло — проблему N+1.

Термины расшифровываем по ходу. Никакой магии, только инструменты, которые есть в любой нормальной базе.

Часть I. Профилирование: сначала измерьте, потом лечите

1. Журнал медленных запросов — ваш первый инструмент

Slow query log (журнал медленных запросов) — это встроенный в MySQL и MariaDB наблюдатель, который записывает каждый запрос дольше заданного времени. Идея простая: вы не знаете, что тормозит, пока не посмотрите. А когда посмотрите — удивитесь, потому что почти всегда виноваты несколько конкретных запросов, а не «база в целом».

Включаем и настраиваем:

-- Включаем журнал медленных запросов на уровне работающего сервера
SET GLOBAL slow_query_log = 'ON';

-- Порог: писать запросы, которые выполняются дольше 0.5 секунды
SET GLOBAL long_query_time = 0.5;

-- Полезно писать сюда же запросы, которые отработали, но не использовали ни одного индекса
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Где лежит файл журнала (можно задать и в my.cnf)
SHOW VARIABLES LIKE 'slow_query_log_file';

Порог 0.5 секунды — разумная отправная точка для веб-приложения. Если страница должна отдаваться за 200 миллисекунд, любой запрос дольше полусекунды уже ломает весь отклик. Ставьте завышенный порог в 5 секунд и вы, конечно, увидите пару «монстров» — но реальные жалобы пользователей рождаются из сотни запросов по 300 миллисекунд, каждый из которых сам по себе «не медленный».

Дальше журнал нужно агрегировать, потому что сырой файл на 2 гигабайта нечитаем. Для этого есть готовые анализаторы (mysqldumpslow, pt-query-digest из набора Percona Toolkit) — они складывают одинаковые по форме запросы в одну группу и показывают сводку: сколько раз выполнялся, суммарное время, среднее время, сколько строк просмотрено.

Типичная сводка выглядит так:

Запрос (упрощённо)Выполнений за суткиСреднее времяВсего времени
SELECT ... FROM posts WHERE status = ? ORDER BY date DESC LIMIT 20480 0000.9 с120 часов
SELECT ... FROM meta WHERE post_id = ?9 600 0000.05 с133 часа
UPDATE counters SET views = views + 1 WHERE id = ?200 0000.2 с11 часов

Вторая строка — это и есть классическое N+1, о котором мы поговорим в Части VII. Девять с половиной миллионов микрозапросов дают больше суммарного времени, чем один медленный. Поэтому и важно смотреть суммарное время, а не только «самый долгий запрос».

2. Профилирование в PostgreSQL

В PostgreSQL тот же смысл, но инструменты называются иначе. Расширение pg_stat_statements накапливает статистику по всем выполненным запросам:

-- В postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
-- затем один раз:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top-10 запросов по суммарному времени
SELECT round(total_exec_time::numeric, 1) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 1)  AS avg_ms,
       rows,
       left(query, 90)                    AS query
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

Обращайте внимание на столбец calls. Запрос, который выполняется 2 миллисекунды, но вызывается миллион раз на страницу, — это катастрофа. Запрос в три секунды, который запускает админ раз в день из отчёта, — неудобство, но не проблема пользователей.

Правило, которое экономит недели работы: не оптимизируйте то, что не доказало свою медленность в суммарном времени. Красивая теория о «правильных индексах» без цифр превращается в десять лишних индексов, которые замедляют запись и никогда не читаются.

Часть II. EXPLAIN: читаем план выполнения запроса

3. Что такое план запроса

EXPLAIN (дословно «разъясни») — команда, которая просит базу не выполнять запрос, а показать, как она собирается его выполнять. База содержит оптимизатор — встроенный «планировщик», который перебирает варианты: брать индекс или перебирать всё, в каком порядке соединять таблицы, использовать ли временный буфер для сортировки. EXPLAIN этот выбор показывает.

-- MySQL / MariaDB: план с фактическими цифрами
EXPLAIN FORMAT = JSON
SELECT p.id, p.title, u.display_name
FROM   posts   AS p
JOIN   users   AS u ON u.id = p.author_id
WHERE  p.status = 'publish'
ORDER  BY p.date DESC
LIMIT  20;

Что смотреть в выводе:

  • type — способ доступа к таблице. По порядку от худшего к лучшему: ALL (полный перебор всех строк) → index (перебор по индексу) → range (диапазон) → ref (поиск по индексу по значению) → eq_ref и const (точечный поиск по уникальному ключу, самый быстрый).
  • rows — сколько строк база собирается просмотреть. Если их больше, чем строк в результате на порядки — работы слишком много.
  • Extra — «красные флаги» здесь: Using filesort (сортировка в памяти/на диске, потому что индекс не помогает порядку) и Using temporary (промежуточная таблица). Оба часто лечатся правильным индексом.
  • key — какой индекс реально применён. Если пусто при живых подходящих индексах — значит база сочла их невыгодными (обычно из-за устаревшей статистики или слишком «режего» фильтра).

Полный перебор таблицы в 20 миллионов строк, чтобы взять 20 записей, — это ровно та ошибка, из-за которой страница живёт три секунды.

Для PostgreSQL форма чуть отличается, и там важно добавлять ANALYZE:

-- PostgreSQL: план + фактические замеры времени по ходу выполнения
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.id, p.title, u.display_name
FROM   posts   AS p
JOIN   users   AS u ON u.id = p.author_id
WHERE  p.status = 'publish'
ORDER  BY p.date DESC
LIMIT  20;

ANALYZE заставляет запрос действительно выполниться и показывает план и факт: ожидаемое число строк (rows) против реального (actual rows). Если они расходятся в сто раз — оптимизатор обманут устаревшей статистикой; обычно помогает ANALYZE tablename (команда пересобрать статистику) или расширение автовакуума. BUFFERS показывает, сколько страниц данных пришлось читать с диска и сколько из кэша — это прямой ответ на вопрос «тормозит запрос или просто холодные данные».

4. План в действии: разбираем конкретный случай

Дано: запрос выбирает 20 последних опубликованных постов. В таблице 800 000 строк.

План «до» показывает type: ALL, rows: 794120, Extra: Using where; Using filesort. Разбор простыми словами: база прочитала все 800 тысяч строк, отбросила неопубликованные, потом отсортировала оставшееся в памяти, потом взяла 20 штук. Три секунды на это — совсем не удивительно.

Ставим индекс, который покрывает и фильтр, и порядок сортировки, — и переходим к Части III.

Часть III. Индексы: B-tree, GIN, GiST — какие для каких задач

5. Индекс — это оглавление, а не библиотека

Индекс — отдельная отсортированная структура, которая хранит значения колонки (или набора колонок) вместе с указателями на строки таблицы. Вместо перебора миллиона строк база делает несколько прыжков по дереву и выходит на нужные строки.

B-tree (расшифровывается как balanced tree — «сбалансированное дерево») — это классическая структура для поиска. Дерево сбалансировано: все листья на одной глубине, поэтому поиск всегда за одно и то же малое число шагов. В таблице на 10 миллионов строк поиск по B-tree индексу — это примерно 3–4 прочитанных блока, а не 10 миллионов.

B-tree хорош там, где значения упорядочены и можно сравнить «меньше / больше / равно»: числа, даты, строки при точном совпадении, идентификаторы.

Создаём индекс:

-- MySQL / MariaDB
-- Составной индекс: сначала фильтр по status, внутри него порядок по date.
-- Такой индекс обслуживает и WHERE, и ORDER BY — filesort исчезает.
CREATE INDEX idx_posts_status_date ON posts (status, date);

-- Уникальный индекс: значения не повторяются + работает как обычный индекс для поиска
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Индекс по префиксу строки: экономит место, если различия в начале текста
CREATE INDEX idx_posts_slug ON posts (slug(32));

-- PostgreSQL: тот же смысл, синтаксис чуть проще
CREATE INDEX IF NOT EXISTS idx_posts_status_date ON posts (status, date DESC);

-- Частичный индекс: только релевантные строки, индекс в десятки раз меньше
CREATE INDEX idx_posts_live ON posts (date DESC) WHERE status = 'publish';

Полезное свойство составного индекса: он работает как телефонный справочник, отсортированный по «фамилия, имя». Поиск по фамилии — отлично. Поиск только по имени — почти бесполезен, индекс не используется. Поэтому порядок колонок в индексе — это не косметика: первым ставят колонку точного фильтра, затем колонку сортировки или диапазона.

Индексы не бесплатны. Каждый индекс — это структура, которую надо обновлять при каждой вставке, изменении и удалении. Пять-шесть индексов на таблице обычно комфортны; двадцать — значит медленная запись и раздутый диск. Перед удалением «лишнего» индекса посмотрите его использование:

-- MySQL: счётчики применения индексов (после нагрузки и работы приложения)
SELECT object_name, index_name, count_read, count_fetch, count_add
FROM   information_schema.index_statistics
WHERE  object_name = 'posts';

-- PostgreSQL: то же самое, но нагляднее
SELECT relname, indexrelname, idx_scan, idx_tup_read, pg_size_pretty(pg_relation_size(indexrelid))
FROM   pg_stat_user_indexes
ORDER  BY idx_scan;

Индекс с idx_scan = 0 за месяц — кандидат на удаление.

6. GIN: индекс для «внутри значения много чего»

GIN (расшифровка: Generalized Inverted Index — «обобщённый перевёрнутый индекс»). Принцип: вместо одной записи на строку строим список «значение → где оно встречается», как указатель в конце книги: слово «масштабирование» — страницы 12, 47, 131.

Это идеально для данных, где в одной ячейке лежит набор: массивы тегов, JSON с несколькими полями, полнотекстовый поиск по тексту статьи.

-- PostgreSQL: полнотекстовый поиск по тексту поста
CREATE INDEX idx_posts_search ON posts USING gin (to_tsvector('russian', body));

SELECT id, ts_headline('russian', body, q) AS snippet
FROM   posts, plainto_tsquery('russian', 'оптимизация индексы') AS q
WHERE  to_tsvector('russian', body) @@ q
ORDER  BY ts_rank(to_tsvector('russian', body), q) DESC
LIMIT  10;

-- Массив тегов: «найти все посты, где есть тег "php"»
CREATE INDEX idx_posts_tags ON posts USING gin (tags);
SELECT id FROM posts WHERE tags @> ARRAY['php'];

Цена GIN: он большой и медленно строится и обновляется. Ставить его на каждую текстовую колонку нельзя; это инструмент для того, что действительно ищут по содержимому.

7. GiST: индекс для геометрии и «похожести»

GiST (Generalized Search Tree — «обобщённое поисковое дерево»). Отличие от B-tree в том, что GiST умеет сравнивать не «меньше-больше», а «пересекается — не пересекается». Поэтому его стихия:

  • геоданные — «найди все объекты в радиусе 5 километров»;
  • диапазоны и интервалы времени — «нет ли пересечения занятых слотов»;
  • поиск по похожести в векторных расширениях — «найти 10 ближайших по смыслу»;
  • небольшие таблицы, где B-tree не окупается, а также LIKE 'подстрока%' с текстовыми операторными классами.
-- PostgreSQL: геоиндекс для поиска «все точки в радиусе 5 км»
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

CREATE INDEX idx_shops_location ON shops USING gist (ll_to_earth(lat, lng));

SELECT name, earth_distance(ll_to_earth(:my_lat, :my_lng), ll_to_earth(lat, lng)) AS km
FROM   shops
WHERE  earth_box(ll_to_earth(:my_lat, :my_lng), 5000) @> ll_to_earth(lat, lng)
ORDER  BY km
LIMIT  20;

Короткая таблица сравнения:

ТипРасшифровкаДля каких данныхТиповой запрос
B-treeBalanced tree, сбалансированное деревочисла, даты, строки, id=, <, >, BETWEEN, сортировка
GINGeneralized Inverted Index, перевёрнутый индексмассивы, JSON, текст«содержит», полнотекстовый поиск
GiSTGeneralized Search Tree, обобщённое деревогеометрия, диапазоны, похожесть«пересекается», «в радиусе»

Ещё два часто встречающихся типа, чтобы не путаться: hash — индекс только на точное равенство (в PostgreSQL исторически считался менее надёжным, сейчас применим); brin (block range index) — крошечный индекс «какой диапазон значений в каком блоке диска», идеален для очень больших таблиц, где данные пишутся по порядку (логи, счётчики).

8. Когда индекс не поможет

Индекс игнорируется, если:

  • условие ломает сортировку: WHERE YEAR(created_at) = 2026 вместо WHERE created_at >= '2026-01-01'. В первом случае функция применена к колонке — индекс бесполезен. Пишите условие так, чтобы колонка стояла голая: created_at BETWEEN '2026-01-01' AND '2026-12-31'.
  • выборка слишком большая: если status = 'draft' описывает 80% таблицы, читать её по индексу дороже, чем перебором. База сама выберет перебор — это нормально.
  • LIKE '%подстрока%' с звёздочкой в начале. Тут нужен GIN с pg_trgm или внешний поисковый движок.
  • индекс есть, но статистика устарела — оптимизатор «не знает», что индекс выгоден.

Часть IV. Шардинг против репликации: горизонтальное и вертикальное масштабирование

9. Два разных вопроса

Тут путаются чаще всего, потому что оба слова начинаются с «несколько серверов». Разделяем:

  • Репликация отвечает на вопрос «как снять нагрузку с одного сервера». Одна и та же база лежит на нескольких машинах: одна пишет (master, primary — основной сервер), несколько читают (replica, standby — реплики). Объём данных на каждой машине — полный. Если таблица не влезала на диск, репликация не поможет: она размножает проблему, а не решает её.
  • Шардинг отвечает на вопрос «как хранить больше, чем влезает в одну базу». Данные режутся на куски — шарды (от англ. shard, «осколок, часть»): шард 1 хранит пользователей с id 1–10 млн, шард 2 — 10–20 млн и так далее. Каждый шард — отдельный полноценный сервер с куском данных.
  • Вертикальное масштабирование — «купить сервер мощнее»: больше CPU, больше RAM, быстрее диски. Проще всего, но упирается в потолок: самый дорогой сервер когда-нибудь заканчивается, а простой на перезагрузку и миграцию никто не отменял.
  • Горизонтальное масштабирование — «взять много обычных серверов». Именно оно реализуется репликацией и шардингом. Дешевле на масштабе, но добавляет распределённость: два сервера не могут атомарно изменить одну строку, как это делает один.

10. Репликация: как это устроено

Основной сервер пишет изменения и отправляет их в журнал репликации (binary log в MySQL, write-ahead log в PostgreSQL). Реплики читают журнал и применяют те же изменения у себя. Разница между записью на основном и применением на реплике и есть replication lag (задержка репликации) — о ней в Части V.

Типовая раскладка для веб-приложения:

                  запись (INSERT / UPDATE / DELETE)
                          │
                          ▼
                  ┌────────────────┐
                  │   PRIMARY      │   одна база, одна точка записи
                  └───────┬────────┘
                          │ журнал репликации (binlog / WAL)
              ┌───────────┼───────────┐
              ▼           ▼           ▼
        ┌──────────┐ ┌──────────┐ ┌──────────┐
        │ REPLICA 1│ │ REPLICA 2│ │ REPLICA 3│   чтение (SELECT)
        └──────────┘ └──────────┘ └──────────┘

Прикладной код решает просто: все SELECT — на реплику, все INSERT/UPDATE/DELETE — на основной сервер. Так основной сервер (у него всегда есть работа по записи и он один) освобождается для редких операций записи, а три реплики держат всё чтение.

11. Шардинг: как режут данные

Выбор ключа шардинга (колонки, по которой делят данные) — это самое важное архитектурное решение и оно необратимо: переделывать шардинг потом очень больно.

Два основных способа:

  1. Диапазонный (range): id 1–10 млн → шард A, 11–20 млн → шард B. Удобен для запросов диапазона («все заказы за ноябрь»), но создаёт «горячий шард»: новые записи всегда идут в последний диапазон, и один сервер пашет, а остальные скучают.
  2. Хеш-шардинг: shard = hash(user_id) % 8. Распределяет равномерно, но диапазонные запросы становятся дорогими — их приходится слать во все шарды и склеивать результат.

Паттерн запроса при шардинге:

-- Каждый шард — отдельный сервер со своей копией структуры таблиц.
-- Приложение (или proxy-шардер) сначала определяет целевой шард,
-- затем отправляет ЗАПРОС ТОЛЬКО В НУЖНЫЙ ШАРД:
--
--   shard_index = crc32(user_id) % 4  ->  shard_2
--
-- Пример запроса, который выполняется внутри одного шарда.
-- ВАЖНО: в условии обязательно присутствует ключ шардинга (user_id),
-- иначе база не сможет отсечь лишние строки и будет читать всё подряд.

SELECT o.id,
       o.user_id,
       o.total_amount,
       o.created_at
FROM   shard_2.orders AS o
WHERE  o.user_id = 4815162342          -- ключ шардинга: режет 99% данных сразу
  AND  o.status  = 'paid'
ORDER  BY o.created_at DESC
LIMIT  50;

-- Диапазонный запрос внутри шарда — тоже эффективен, если диапазон
-- попадает в один шард (ключ шардинга задан явно):
SELECT id, created_at
FROM   shard_2.orders
WHERE  user_id = 4815162342
  AND  created_at >= '2026-01-01'
  AND  created_at <  '2026-02-01';

А вот запрос без ключа шардинга («найди все заказы со статусом paid») приходится рассылать во все шарды и складывать результаты. Это называется scatter-gather (дословно «рассеять и собрать») и это самый дорогой паттерн в шардированной системе. Такие запросы обычно уводят из пользовательских сценариев в отдельные аналитические базы.

Честно про цену шардинга:

  • Нет нормальных JOIN между шардами — приходится собирать данные в приложении.
  • Транзакции через два шарда — это «двухфазная подготовка» (2PC) или сага с компенсацией; сложность резко растёт.
  • Изменить схему таблицы — значит изменить её на всех шардах, и сделать это синхронно трудно.
  • Балансировка данных (переезд «горячего» пользователя на другой шард) — отдельная операция.

Поэтому шардинг — это не «следующий шаг после первого сервера». Сначала: профилирование → индексы → кеширование (см. Кеширование) → чтение с реплик → вертикальный рост → и только потом шардинг. Для большинства WordPress- и PHP-проектов на VPS шардинг вообще никогда не нужен; реплики и кэш закрывают вопрос.

Часть V. Replication lag: задержка репликации и согласованность данных

12. Что происходит, когда копия отстаёт

Replication lag (задержка репликации) — время между моментом, когда основной сервер записал изменение, и моментом, когда реплика его применила. В спокойной системе это доли секунды. В нагруженной — десятки секунд и минуты.

Причины от самых частых к редким:

  1. Однопоточное применение на реплике. Основной сервер пишет параллельно в сто соединений, а реплика исторически применяет журнал в одном-двух потоках. MySQL 8 и PostgreSQL умеют параллельное применение; надо убедиться, что оно включено.
  2. Огромная транзакция. Один UPDATE на 5 миллионов строк на основном сервере блокирует применение у всех реплик на всё время, пока строки не применены. Дробите крупные изменения на пачки по 1–10 тысяч строк.
  3. DDL-операция (ALTER TABLE) без онлайн-инструмента: на время перестройки таблицы реплика стоит. Для больших таблиц используют gh-ost, pt-online-schema-change или ALTER TABLE ... ALGORITHM=INSTANT там, где это возможно.
  4. Реплика слабее основного сервера — тот же объём изменений на диске в три раза медленнее.
  5. Сеть и диск. Реплика читает журнал, но упирается в собственный медленный диск.
  6. Длинные SELECT на самой реплике мешают применению изменений — классика «аналитик запустил отчёт и всё легло».

13. Как это влияет на пользователя

Функция, которую ломает lag, всегда одна и та же: записали — прочитали и не увидели. Пользователь меняет телефон в профиле, страница перезагружается, читает с реплики — показывает старый номер. Покупатель оплачивает заказ, возвращается на страницу заказа — «заказа нет».

Это вопрос консистентности (согласованности) данных: в распределённой системе в один момент времени разные узлы видят разные версии реальности. Полная согласованность возможна, но стоит она дорого.

Практические приёмы, от дешёвых к дорогим:

  • Read-your-writes (читать то, что сам записал). После любой записи на основной сервер следующие N секунд читай для этого пользователя с основного. Реализуется просто: в сессию кладётся last_write_at; при запросе сравниваем — если запись свежее, чем lag, идём на primary. Это решает 95% жалоб.
  • Метки версии. Для критичных сущностей храните номер версии (или offset журнала репликации) и при чтении требуйте от реплики «догони до этой версии»: в PostgreSQL — application_name + pg_wait_for_lsn(), в MySQL — WAIT_FOR_EXECUTED_GTID_SET(). Реплика подождёт и отдаст свежие данные; платите задержкой, но получаете согласованность.
  • Синхронная репликация. Основной сервер не подтверждает запись, пока хотя бы одна реплика не подтвердит приём. Потеря данных исключается, но каждая запись становится медленнее на время сетевого round-trip. Полусинхронный режим (semi-sync) в MySQL — компромисс: ждём подтверждения только о получении, а не применении.
  • Не читать с реплик то, что должно быть точным: остатки на балансе, количество товаров на складе, статус оплаты. Такие данные читаем с primary, остальное — с реплик.
  • Мониторинг задержки как метрики. Лаг (задержку) измеряют в секундах (Seconds_Behind_Master в MySQL, разница pg_wal_lsn_diff / pg_last_wal_receive_lsn в PostgreSQL). Порог алерта обычно 2–5 секунд; если у вас есть аналитика на репликах — следите, чтобы отчёты не шли в момент большого лага. Подробности — в статье про Логирование и трассировку: лаг реплики это именно то, что надо писать в логи и трассы, иначе вы увидите «страница тормозит», но не поймёте почему.

14. Eventual consistency: принять как норму

Eventual consistency (дословно «консистентность в конечном счёте», по-русски — «согласованность со временем») — идея, что копия обязательно догонит оригинал, но не обязана быть одинаковой в каждый момент. Для ленты новостей, счётчика просмотров, списка «похожие товары» это полностью приемлемо: пользователь не заметит, что счётчик отстал на две секунды. Для списания денег — неприемлемо.

Хорошая архитектура не борется с лагом, а разделяет данные на две категории: те, где лаг безразличен (и они идут на реплики и в кэш), и те, где его быть не должно (и они всегда читаются с основной базы). Это решение надо принимать на этапе проектирования API, когда вы выбираете, какие ресурсы вообще стоит отдавать с кэша и реплики — подробно в Проектировании API.

Часть VI. Connection pooling: пул соединений

15. Зачем нужен пул соединений

Connection pooling (пул соединений) — приём, при котором приложение держит не «новое соединение на каждый запрос», а несколько открытых и готовых, и берёт их из пула на время запроса, а потом возвращает обратно.

Почему это важно. Открыть соединение с базой — дорогая операция:

  1. TCP-хендшейк (сетевое рукопожатие) и, если включён TLS, обмен ключами шифрования — это несколько сетевых «туда-обратно»;
  2. аутентификация (проверка пароля, совмещение прав доступа);
  3. выделение памяти под буферы соединения на стороне сервера;
  4. на стороне PostgreSQL — отдельный процесс на каждое соединение, каждый со своей копией памяти. Это дорого: 400 процессов по 10 мегабайт — 4 гигабайта только на соединения.

В нормальном веб-приложении один HTTP-запрос делает к базе десять-тридцать обращений. Если на каждое открывать новое соединение, вы платите 30 × 5 миллисекунд = 150 миллисекунд на соединениях, не начав ничего полезного. При росте числа пользователей это превращается в исчерпание max_connections (лимит соединений в базе) и ошибки «too many connections» (слишком много соединений) для всех.

16. Как настраивать

Уровень 1: персистентные соединения в PHP. MySQLi и PDO могут переиспользовать уже открытое соединение между запросами (persistent=yes). Это маленькая победа, но она не ограничивает количество: при всплеске трафика вы получите столько соединений, сколько одновременных PHP-процессов.

Уровень 2: выделенный пул-прокси. Когда процессов много, ставят прослойку: PgBouncer для PostgreSQL, ProxySQL или MySQL Router для MySQL. Приложение подключается к прокси (он отвечает мгновенно, потому что соединения с базой уже открыты и ждут), а прокси держит маленькое число реальных соединений к базе и подставляет их.

Два режима PgBouncer, которые важно различать:

HTML
; /etc/pgbouncer/pgbouncer.ini — фрагмент конфигурации

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Режим пула. transaction = переиспользуем соединение между транзакциями,
; даёт максимум уплотнения; session = соединение закрепляется за клиентом
; до его закрытия (нужно, если код использует prepared statements на уровне сервера).
pool_mode = transaction

; Реальных соединений к базе — мало и жёстко.
; Ориентир: (число ядер базы * 2) + 10, для 8-ядерной базы ~30-40.
default_pool_size = 32
max_client_conn  = 2000

; Не держать соединение занятым дольше — иначе один кривой запрос
; блокирует пул для всех.
query_timeout = 30
idle_transaction_timeout = 10

; Логирование и статистика — обязательно, иначе слепая настройка
stats_period = 15

Ключевое число — default_pool_size. Оно контринтуитивно: больше соединений к базе не значит быстрее. База обрабатывает столько запросов, сколько может одновременно выполнить её CPU и диск. Лишние соединения просто выстраиваются в очередь и добавляют переключения контекста. Классическая ошибка — поднять max_connections со 100 до 2000 и получить не «в два раза быстрее», а полный коллапс.

Правило подбора: измеряйте среднее время запроса и пропускную способность. Если база стабильно выполняет 300 запросов в секунду при среднем времени 10 миллисекунд, ей нужно около 300 × 0.01 = 3 одновременно работающих запросов — плюс запас на пики и на запросы-долгострои. Пул в 30–50 соединений обычно закрывает потребности сервера на десятки тысяч клиентов.

Отдельно: пул в PHP-FPM не существует сам по себе. Число соединений = число воркеров PHP-FPM. Поэтому pm.max_children (максимум детей) в пуле PHP напрямую связан с max_connections базы. На пять веб-серверов по 50 воркеров — 250 соединений; если база держит 151 — часть запросов упадёт. Считайте это уравнение при любой нагрузке; проверять — в Нагрузочном тестировании.

17. Как понять, что пул — ваше узкое место

Сигнатуры в метриках:

  • в базе много одновременно работающих запросов (Threads_running в MySQL) при маленьком числе соединений (Threads_connected) — база переваривает, а очереди к ней нет;
  • приложение ждёт, а база простаивает → пул слишком мал, много потоков стоят в очереди к прокси;
  • приложение простаивает в ожидании ответа базы, соединений в пуле занято 100% → либо утечка (соединение не вернули в пул), либо длинные запросы держат его;
  • Aborted_clients / aborted connections растут → таймауты или закрытие соединения без возврата.

И отдельно: пул не чинит медленные запросы. Если запрос идёт 800 миллисекунд, пул лишь сделает ожидание в очереди честным. Сначала индексы и N+1, потом пул.

Часть VII. Проблема N+1: классическая ошибка ORM

18. Что такое ORM и как рождается N+1

ORM (Object-Relational Mapping — объектно-реляционное отображение) — библиотека, которая позволяет писать не SQL-строки, а код с объектами: $post->author->name. Она прячет запросы к базе «под капотом» и это очень удобно ровно до момента, пока вы не узнали, сколько запросов она делает на самом деле.

N+1 (читается «эн плюс один») — паттерн, когда ORM выполняет один запрос на список объектов, а затем по одному запросу на каждый связанный объект. 1 + N запросов вместо одного.

Плохо:

PHP
<?php
// ПЛОХО: классическое N+1.
// 1 запрос на список постов + N запросов на авторов (N = 100 -> 101 запросов к базе)

$posts = Post::where('status', 'publish')
             ->orderBy('date', 'desc')
             ->take(100)
             ->get();                      // SQL: SELECT * FROM posts ... LIMIT 100

foreach ($posts as $post) {
    echo $post->title . ' — ' . $post->author->name . PHP_EOL;
    //             ^^^^^^^^^^^^^^^^
    // Каждый цикл это отдельный: SELECT * FROM users WHERE id = ? 
}

Хорошо — eager loading (дословно «загружать заранее», по-русски «выбрать связанные данные сразу»):

PHP
<?php
// ХОРОШО: связанные данные достаются одним запросом.
// SQL 1: SELECT * FROM posts WHERE status = 'publish' ORDER BY date DESC LIMIT 100
// SQL 2: SELECT * FROM users WHERE id IN (17, 4, 9, 128, ...) -- ровно нужные авторы

$posts = Post::with('author')                 // with() = присоединить автора заранее
             ->where('status', 'publish')
             ->orderBy('date', 'desc')
             ->take(100)
             ->get();

foreach ($posts as $post) {
    echo $post->title . ' — ' . $post->author->name . PHP_EOL;
    //  $post->author уже в памяти: нового запроса нет
}

Обратите внимание: количество запросов упало со 101 до 2. Если бы в списке выводились ещё и комментарии, «плохая» версия дала бы 1 + 100 + 100×20 = 2101 запрос; «хорошая» — те же 3. Именно этот эффект и даёт те девять с половиной миллионов микрозапросов в сводке из Части I.

19. Как заметить N+1

  • Считать запросы. В Laravel — Debugbar / Telescope; в Symfony — Web Debug Toolbar / profiler; в Django — connection.queries и django-debug-toolbar. В WordPress есть SAVEQUERIES в wp-config.php и плагины отладки. Если страница делает 400 запросов к базе при 30 постах — у вас N+1.
  • Смотреть журнал медленных запросов агрегированно. Один медленный запрос бросается в глаза; десять тысяч одинаковых быстрых — только в суммарном времени.
  • Слушать оптимизатор. log_queries_not_using_indexes ловит запросы вида SELECT ... WHERE id = ? по неиндексированному полю.
  • Проверить IN — если вы видите один WHERE id IN (...) на 100 значений, а рядом цикл с одиночным запросом — второй всегда хуже первого.

20. Способы вылечить

  1. Eager loading (with() / join_with() / select_related()) — основной способ, описан выше.
  2. JOIN в одном запросе — когда нужна сортировка или фильтр по связанной таблице: posts JOIN users вместо двух запросов.
  3. Пакетная загрузка (batch, «загрузить пачкой») — собрать id из первого запроса и сделать один запрос WHERE author_id IN (?), самому склеив результат в памяти. Это то, что делает with(), но в произвольном коде вы повторяете приём вручную.
  4. Денормализация ради списка. Для часто отдаваемого списка «посты + имя автора» удобно хранить имя автора рядом с постом и не ходить в users вовсе. Плата — синхронизация при переименовании автора.
  5. Кэш объектов поверх ORM. Ключ author:{id}, значение — имя. Тогда связанный объект берётся из Redis в 0.3 миллисекунды вместо базы. Про стратегии — в Кешировании.

21. И про безопасность: prepared statements

Раз уж речь о том, как приложение ходит в базу, обязательно о prepared statements (подготовленных выражениях). Это механизм, когда SQL-шаблон отправляется серверу отдельно от значений: сначала текст запроса с подстановочными знаками-плейсхолдерами (? или :name), потом сами значения. Сервер компилирует план один раз и дальше только подставляет данные, никогда не принимая их за код.

Двойная выгода: защита от SQL-инъекций (введения чужого SQL-кода через данные пользователя) и экономия на повторной компиляции одинаковых запросов.

PHP
<?php
// ПРАВИЛЬНО: prepared statements (подготовленные выражения) через PDO.
// SQL-шаблон и данные идут раздельно, поэтому пользовательский ввод
// никогда не может превратиться в исполняемый SQL-код.

$pdo = new PDO(
    'mysql:host=db.internal;dbname=myapp;charset=utf8mb4',
    'app_user',
    getenv('DB_PASSWORD'),                       // пароль — из окружения, не из кода
    [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // ошибки — исключениями
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,   // ВАЖНО: настоящие серверные prepare,
                                                 // а не подстановка строк на стороне PHP
        PDO::ATTR_PERSISTENT         => true,    // переиспользуем соединение между запросами
    ]
);

// Именованные плейсхолдеры: имя параметра видно, опечатка падает сразу, а не тихо
$stmt = $pdo->prepare(
    'SELECT id, title, date
       FROM posts
      WHERE status = :status
        AND date   >= :since
      ORDER BY date DESC
      LIMIT :limit'
);

$stmt->bindValue(':status', $statusFromRequest, PDO::PARAM_STR);
$stmt->bindValue(':since',  $sinceDate,         PDO::PARAM_STR);
$stmt->bindValue(':limit',  $limit,             PDO::PARAM_INT);  // числа — типом INT

$stmt->execute();
$posts = $stmt->fetchAll();

// Транзакция: несколько запросов либо применяются все, либо ни один
$pdo->beginTransaction();
try {
    $upd = $pdo->prepare('UPDATE posts SET views = views + 1 WHERE id = :id');
    $upd->execute([':id' => $postId]);

    $log = $pdo->prepare('INSERT INTO view_log (post_id, created_at) VALUES (:id, NOW())');
    $log->execute([':id' => $postId]);

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();          // ничего не записано в полу-состоянии
    throw $e;                  // ошибку видно в логах — см. статью про логирование
}

Три типичные ошибки, которые стоит проверить в своём коде прямо сейчас:

  • ATTR_EMULATE_PREPARES => true — «подготовка» выполняется на стороне PHP путём склейки строки. Это и медленнее (база не помнит план), и небезопасно при неправильной экранировке. Требуйте false.
  • Конкатенация в prepare(): prepare("... WHERE id = " . $_GET['id']). Это ровно то, от чего prepared statements защищают, — но защищать уже не от чего, потому что данные попали в SQL-шаблон.
  • LIMIT как строка при нативных prepare: bindValue(':limit', $n, PDO::PARAM_STR) с EMULATE_PREPARES=false падает или работает криво. Числа — PDO::PARAM_INT.

Отдельное замечание про ORM: они используют prepared statements по умолчанию, поэтому соблазн «ускорить» цитатой с форума про склейку строк обычно не возникает. Но в ручных DB::select("...") и mysqli_query() — возникает. Там и ищите.

Инструмент под задачу

Попробовать, как тот же запрос ложится на EXPLAIN и индекс, можно прямо на странице: соберёте SQL с учётом префиксов WordPress и проверите план — Попробуйте инструмент прямо здесь, на своих данных:

ИИ SQL-Генератор
Гость
Токены: 0 / 50 000
Гостевой лимит — 50 000 токенов. Зарегистрируйтесь, для пользователей лимит + 200 000 токенов.

Чек-лист оптимизации базы данных

  • Включите журнал медленных запросов и агрегатор. Без сводки по суммарному времени вы лечите наугад. Порог 0.5 секунды, смотреть топ по total_time, а не по максимуму.
  • Прогоните тяжёлые запросы через EXPLAIN. Ищите ALL / Seq Scan, гигантское rows и Using filesort / Using temporary в Extra.
  • Индексы — по порядку колонок. Фильтр (=) первым, затем сортировка или диапазон. Проверяйте, что индекс действительно применяется, и удаляйте индексы с нулевым idx_scan.
  • Для текста, массивов и JSON — GIN, для гео и диапазонов — GiST. Не ждите от B-tree магии на нестандартных данных и не тащите GIN туда, где хватит обычного индекса.
  • Сначала чтение, потом объём. Реплики снимают нагрузку чтения; шардинг — только когда данных физически не влезает или не помещается в RAM/диск одного узла.
  • После записи — читайте с primary хотя бы короткое время (read-your-writes) или ждите нужной версии (offset/LSN) на реплике. Критичные к точности данные не отдавайте с реплик никогда.
  • Лог лага реплики и алерт по нему. Без измеренного лага вы будете писать в тикетах «иногда показывает старые данные» и ничего не докажете.
  • Пул вместо соединения-на-запрос. Прокси (PgBouncer / ProxySQL) с маленьким default_pool_size; считайте число соединений = воркеры × число веб-серверов против max_connections.
  • Считайте запросы на страницу. Больше 50 на обычную страницу — почти наверняка N+1. Чините eager loading’ом, JOIN’ом или пакетной загрузкой.
  • Только prepared statements, всегда. EMULATE_PREPARES=false, числа типом PARAM_INT, никакой конкатенации в SQL-шаблон.
  • Не путайте причину и следствие. Пул не ускорит медленный запрос; кэш не спасёт запрос с полным перебором; «сервер мощнее» не лечит N+1. Порядок: измерить → индекс → убрать лишний запрос → затем уже масштабировать.

Итог. Оптимизация — это дисциплина измерения

Мы прошли путь от «страница тормозит» до конкретной причины: включили журнал медленных запросов, прочитали план выполнения, поставили индекс там, где он нужен именно по типу данных, разделили чтение и запись по репликам, честно учли задержку репликации, уплотнили соединения пулом и схлопнули тысячи микрозапросов в два. Ни одного трюка, ни одной магии — только последовательность, которая работает одинаково в MySQL, MariaDB и PostgreSQL.

Главный вывод этой статьи даже не в инструментах, а в порядке действий. Оптимизация базы данных — это дисциплина: сначала измерить, потом изменить, потом снова измерить. Тот, кто пропускает первое измерение, неизбежно делает два бесполезных изменения и одно вредное.

База больше не тёмный ящик: вы знаете, где она болит, и умеете это лечить. Но все эти меры имеют цену ровно в одном месте — под нагрузкой. Реальные цифры «выдержим ли мы распродажу» и «какой лаг допустим» получают не рассуждениями, а прогоном: k6, профили нагрузки, ступенчатый рост и наблюдение, где система начинает отваливаться.

Материал написан простым языком; все аббревиатуры расшифрованы при первом упоминании (slow query log, EXPLAIN, B-tree, GIN, GiST, shard, replication lag, connection pooling, ORM, N+1, prepared statements, eventual consistency). Примеры используют MySQL/MariaDB и PostgreSQL, так как это самые частые базы в PHP-проектах; синтаксис может отличаться по версиям — сверяйтесь с документацией вашего СУБД (системы управления базами данных). Практические советы учитывают типичный VPS-хостинг и самостоятельное администрирование.

👤 Зарегистрируйтесь, чтобы добавить материал в закладки.