Устранение проблемы N+1 запросов и связанных подзапросов в API Node.js с Postgres

Медленную работу конечных точек часто связывают с инфраструктурой, однако во многих случаях причина замедления кроется прямо в коде обработчика. В этом анализе рассматриваются две конечные точки фреймворка Express, работающие с базой данных Postgres: одна возвращает информацию о заказах пользователя за 2,24 секунды, а другая — отчеты — за примерно 29 секунд. Для устранения проблем не требуется использование Redis, дополнительных серверов или изменения архитектуры; почти весь дополнительный времени вызван пятью распространенными ошибками, и их устранение позволяет сократить время отклика обеих конечных точек до менее чем 200 мс. К концу вы сможете распознавать подобные проблемы в собственном коде и понимать, какие форматы запросов следует использовать вместо других.

Если вам нужен общий метод для выявления узкого места ещё до того, как вы начнёте работу с кодом, наша инструкция по поиску настоящего узкого места в медленном Node.js-эндпоинте охватывает аспекты профилирования. Здесь основное внимание уделяется конкретным шаблонам запросов.

Два эндпоинта под микроскопом

Первый обработчик, GET /orders/:userId, загружает заказы пользователя, затем проходит по каждому заказу, чтобы получить его элементы, а после этого проходит по каждому элементу, чтобы получить соответствующий продукт. Для каждого элемента также вычисляется хеш SHA-256, который добавляется в качестве поля etag.

// GET /orders/:userId
app.get('/orders/:userId', async (req, res) => {
  const { userId } = req.params;

  const ordersResult = await pool.query(
    `SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
  );
  const orders = ordersResult.rows;

  const enrichedOrders = [];

  for (const order of orders) {
    const itemsResult = await pool.query(
      `SELECT * FROM order_items WHERE order_id = ${order.id}`
    );
    const items = itemsResult.rows;

    const enrichedItems = [];
    for (const item of items) {
      const productResult = await pool.query(
        `SELECT * FROM products WHERE id = ${item.product_id}`
      );
      const product = productResult.rows[0];

      const etag = crypto
        .createHash('sha256')
        .update(JSON.stringify({ item, product }))
        .digest('hex');

      enrichedItems.push({ ...item, product, etag });
    }

    enrichedOrders.push({ ...order, items: enrichedItems });
  }

  res.json(enrichedOrders);
});

Второй обработчик формирует краткое описание статусов заказов. Он выбирает все заказы вместе с показателем стопроцентной доли отмен заказов для каждого пользователя, рассчитанным с помощью подзапроса, затем в JavaScript просматривает полученные строки для подсчёта количества заказов по статусам и сохранения показателя на пользователя. Хотя в фрагменте кода присутствует SQL, сам обработчик написан на JavaScript.

app.get('/reports/order-status-summary', async (req, res) => {
  const result = await pool.query(`
    SELECT
      o.id AS order_id,
      o.user_id,
      o.status,
      (
        SELECT round(
          count(*) FILTER (WHERE o2.status = 'cancelled')::numeric
            / NULLIF(count(*), 0),
          4
        )
        FROM orders o2
        WHERE o2.user_id = o.user_id
      ) AS cancellation_rate
    FROM orders o
  `);
  const rows = result.rows;

  const totalsByUser = {};
  const cancellationRateByUser = {};
  for (const row of rows) {
    totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
    totalsByUser[row.user_id][row.status] =
      (totalsByUser[row.user_id][row.status] || 0) + 1;
    cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
  }

  res.json({
    usersProcessed: Object.keys(totalsByUser).length,
    totalsByUser,
    cancellationRateByUser,
  });
});

При быстром просмотре оба решения кажутся разумными. Проблемы проявляются только при работе с реалистичными объёмами данных. В тестовой базе данных содержатся:

• пользователи: 5,000
• товары: 2,000
• заказы: 500,000
• элементы заказов: 1,500,000

При таком объёме запрос к эндпоинту заказов выполняется за 2,24 секунды, что слишком долго для простого экрана «Мои заказы», а запрос к эндпоинту сводки — за 29,26 секунд.

Почему запрос GET /orders/:userId занимает 2,24 секунды

Пользователь, нажимающий на «Мои заказы», не должен ждать две секунды перед отображением контента из-за индикатора загрузки. Эта задержка возникает из-за трех взаимосвязанных факторов, усугубляющих друг друга.

Первоначальный поиск сканирует и сортирует всю таблицу

Уже первый запрос содержит несколько проблем в считанных строках.

const ordersResult = await pool.query(
  `SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
);

• Для поля orders.user_id отсутствует индекс, поэтому Postgres выполняет последовательный сканирование всех 500 000 строк, чтобы найти примерно 102 записи, относящиеся к пользователю.
• Также нет ничего, что могло бы поддержать операцию ORDER BY created_at DESC; поэтому после сканирования соответствующие строки сортируются без использования индекса.
• Значение userId вставляется непосредственно в строку SQL. Это создаёт уязвимость к инъекциям, поскольку данные поступают непосредственно из URL, к тому же каждый запрос превращается в уникальную по тексту запрос-строку.

Двойно вложенная структура N+1

Большая часть времени тратится на циклы.

for (const order of orders) {
  const itemsResult = await pool.query(`SELECT * FROM order_items WHERE order_id = ${order.id}`);
  ...
  for (const item of items) {
    const productResult = await pool.query(`SELECT * FROM products WHERE id = ${item.product_id}`);

• Это классическая схема N+1, вложенная дважды. Примерно 102 заказа приводят к 102 запросам к элементам, а около 500 элементов порождают еще примерно 500 запросов к товарам. Это более 600 обращений к Postgres, выполняемых одно за другим, поскольку каждый await ждет предыдущего, и каждое из них влечет за собой полную задержку сети. Те же данные можно получить за один-три запроса с использованием JOIN или WHERE id = ANY(...).
• order_items.order_id не имеет индекса, поэтому каждый из 102 запросов к элементам вынужден самостоятельно просматривать 1,5 миллиона строк.

Хеширование, бесполезно блокирующее цикл событий

Последняя проблема связана с работой процессора, а не с вводом-выводом.

const etag = crypto.createHash('sha256').update(JSON.stringify({ item, product })).digest('hex');

Хеш вычисляется синхронно для каждого элемента примерно 500 раз на запрос. Поскольку Node.js выполняет JavaScript в однопоточной среде, каждое такое вычисление блокирует цикл событий и задерживает обработку всех остальных запросов. Что ещё хуже, полученное значение никогда не используется для кэширования или условных запросов, поэтому весь этот труд бесполезен. В нашей статье о том, как process.nextTick может заблокировать цикл событий объясняется, почему блокировка этой потока так дорогостояща.

В сумме задержка обусловлена последовательной суммой более 600 итераций, нескольких полных сканирований таблицы и сотен вычислений хешей.

Почему отчет с обобщением генерируется примерно за 29 секунд

Пользователь, который открывает карточку своего заказа и ждет почти полминуты, вполне может предположить, что страница не работает. Причина заключается в одном SQL-запросе.

SELECT
  o.id AS order_id, o.user_id, o.status,
  (
    SELECT round(
      count(*) FILTER (WHERE o2.status = 'cancelled')::numeric / NULLIF(count(*), 0),
      4
    )
    FROM orders o2
    WHERE o2.user_id = o.user_id
  ) AS cancellation_rate
FROM orders o

1. Связанный подзапрос выполняется один раз на каждую внешнюю строку. Внешний запрос возвращает все 500 000 заказов, и для каждого из них Postgres перезапускает подзапрос с использованием таблицы orders o2, отфильтрованной по значению user_id данной строки. При примерно 100 заказах на пользователя один и тот же показатель отмены вычисляется примерно 100 раз для каждого пользователя, в результате чего общее количество выполнений подзапроса достигает около 500 000.
2. Отсутствует индекс для ускорения повторных поисков. Без индекса на поле orders.user_id каждое из этих выполнений требует полного сканирования данных. Индекс позволил бы сократить затраты на каждое выполнение, но не устранил бы излишние расходы на вычисление одного и того же результата сотни раз.

Результат с завышенными значениями суммируется второй раз в JavaScript. Все 500 000 строк, каждая из которых содержит вычисленный показатель, передаются по сети в Node, где затем происходит их обработка для формирования переменных totalsByUser и cancellationRateByUser. Эту операцию Postgres мог бы выполнить один раз с помощью оператора GROUP BY.

Затраты растут пропорционально неверному числу. Работа, которая должна быть пропорциональна 5 000 пользователям, фактически пропорциональна 500 000 заказам, что соответствует умножителю примерно в 100 раз. Именно поэтому обработка запроса занимает от 26 до 29 секунд вместо примерно десятой доли секунды.

Первый шаг: добавление индексов, соответствующих паттерну доступа

Индексы обычно являются самым дешевым и эффективным первым решением. Здесь требуются два индекса: композитный индекс на orders (user_id, created_at DESC), чтобы как фильтрация, так и сортировка выполнялись с использованием этого индекса, и индекс на order_items (order_id) для поиска записей о товарах. Приведённая ниже команда создаёт оба индекса внутри контейнера Postgres; использование IF NOT EXISTS позволяет безопасно запускать её снова и снова.

docker exec slow-api-postgres psql -U postgres -d shop -c "
CREATE INDEX IF NOT EXISTS idx_orders_user_id_created_at ON orders (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items (order_id);
"

Порядок столбцов в композитном индексе имеет значение. Если первым указать user_id, Postgres сможет сразу перейти к строкам конкретного пользователя, и поскольку эти строки уже хранятся в порядке created_at DESC внутри индекса, шаг сортировки полностью исчезает. Для активно используемых таблиц в производстве рекомендуется использовать CREATE INDEX CONCURRENTLY, чтобы процесс создания индекса не блокировал записи.

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

Перепись GET /orders/:userId с использованием пакетных запросов

Новая версия выполняет ровно три запроса. Сначала с помощью параметризованного запроса загружаются заказы пользователя, собираются их идентификаторы, затем с использованием order_id = ANY($1) загружаются все связанные элементы. После этого с помощью структуры Set удаляются дубликаты идентификаторов продуктов, и ещё одним запросом загружаются эти продукты. Результаты объединяются в памяти с помощью двух поисков в структурах Map, а при отсутствии заказов у пользователя происходит немедленный возврат.

app.get('/orders/:userId', async (req, res) => {
  const { userId } = req.params;

  const ordersResult = await pool.query(
    'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC',
    [userId]
  );
  const orders = ordersResult.rows;

  if (orders.length === 0) {
    return res.json([]);
  }

  const orderIds = orders.map((o) => o.id);
  const itemsResult = await pool.query(
    'SELECT * FROM order_items WHERE order_id = ANY($1)',
    [orderIds]
  );
  const items = itemsResult.rows;

  const productIds = [...new Set(items.map((i) => i.product_id))];
  const productsResult = await pool.query(
    'SELECT * FROM products WHERE id = ANY($1)',
    [productIds]
  );
  const productsById = new Map(productsResult.rows.map((p) => [p.id, p]));

  const itemsByOrderId = new Map();
  for (const item of items) {
    const enrichedItem = { ...item, product: productsById.get(item.product_id) };
    if (!itemsByOrderId.has(item.order_id)) {
      itemsByOrderId.set(item.order_id, []);
    }
    itemsByOrderId.get(item.order_id).push(enrichedItem);
  }

  const enrichedOrders = orders.map((order) => ({
    ...order,
    items: itemsByOrderId.get(order.id) || [],
  }));

  res.json(enrichedOrders);
});

Изменения по порядку:

1. Композитный индекс orders (user_id, created_at DESC) превращает операции WHERE и ORDER BY в сканирование по индексу вместо последовательного сканирования и сортировки 500 000 строк.

Индекс на order_items (order_id) позволяет при поиске элементов больше не сканировать 1,5 миллиона строк.

Паттерн N+1 преобразуется в три запроса: один для заказов, один для всех их элементов, один для всех уникальных продуктов. Более 600 сериализованных запросов превращаются в три.

Местоимения вроде $1 заменяют интерполяцию строк, тем самым закрывая уязвимость к инъекциям SQL. Обратите внимание, что с node-postgres по-прежнему планируется один запрос без имени на каждую экзекуцию; если вы хотите, чтобы Postgres повторно использовал этот план, используйте именованный подготовленный запрос.

Неиспользуемый хеш SHA-256 для каждого элемента устранён, что избавляет цикл событий от бесполезной задержки.

Одно практическое предупреждение: удаление поля etag меняет структуру ответа. Перед внедрением этого изменения убедитесь, что ни один клиент от него не зависит.

Переписывание отчёта с использованием GROUP BY

Оптимизированный отчет выполняет два целенаправленных запроса на агрегацию: один группирует данные по user_id и status для подсчета количества записей, другой — по user_id для расчета коэффициента отказов. JavaScript лишь преобразует уже агрегированные строки в формат ответа. Как и раньше, обработчиком выступает JavaScript с встроенным SQL-кодом.

// GET /reports/order-status-summary (OPTIMIZED / "after")
app.get('/reports/order-status-summary-optimized', async (req, res) => {
  const statusResult = await pool.query(`
    SELECT user_id, status, count(*)::int AS count
    FROM orders
    GROUP BY user_id, status
  `);

  const rateResult = await pool.query(`
    SELECT
      user_id,
      round(
        count(*) FILTER (WHERE status = 'cancelled')::numeric
          / NULLIF(count(*), 0),
        4
      ) AS cancellation_rate
    FROM orders
    GROUP BY user_id
  `);

  const totalsByUser = {};
  for (const row of statusResult.rows) {
    totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
    totalsByUser[row.user_id][row.status] = row.count;
  }

  const cancellationRateByUser = {};
  for (const row of rateResult.rows) {
    cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
  }

  res.json({
    usersProcessed: Object.keys(totalsByUser).length,
    totalsByUser,
    cancellationRateByUser,
  });
});

Что изменилось и почему это важно:

1. Связанный подзапрос теперь представляет собой операцию GROUP BY user_id. Postgres вычисляет коэффициент один раз на каждого пользователя в ходе одной процедуры агрегации: 5,000 вычислений вместо примерно 500,000.
2. Количество строк в результате сокращается с 500,000 до примерно 5,000–20,000. Postgres возвращает по одной строке на каждую пару (user_id, status) для подсчета количества записей и по одной строке на каждого пользователя для расчета коэффициента отказов, без дублирования вычисленных столбцов.

Два независимых запроса заменяют один запрос с множественным обработанием строк. Каждый из них требует лишь одного просмотра таблицы orders, вместо того чтобы для каждой строки выполнять один запрос, вложенный в другой.

Узел больше не выполняет повторную агрегацию. В JavaScript не запускается цикл подсчёта; обработчик просто сопоставляет группированные строки с ключами.

Время выполнения сократилось с примерно 26 до 29 секунд до примерно 0,1–0,19 секунды, что в 150–260 раз быстрее. Было проверено, что новый эндпоинт возвращает такие же значения totalsByUser и cancellationRateByUser, как и оригинальный, для всех 5000 пользователей. Эта проверка эквивалентности стоит скопировать: каждый раз, когда вы переписываете запрос для ускорения, сравнивайте результаты с медленной версией перед заменой.

Оба способа агрегации по-прежнему считывают всю таблицу orders при каждом запросе. При текущих масштабах это нормально, но если таблица будет продолжать расти, один запрос с использованием операторов FILTER, материализованного представления или периодически обновляемой сводной таблицы может улучшить производительность отчета.

Основные выводы

• Создавайте индексы для столбцов, по которым производится фильтрация и сортировка, и соответствуйте порядку композитных индексов порядку запроса: сначала столбцы для проверки равенства, затем столбец сортировки.
• Считайте любой вызов await db.query() внутри цикла тревожным сигналом; замените его на пакетные запросы с использованием JOIN или ANY($1).
• Всегда используйте параметризованные запросы для значений, поступающих из запроса.
• Обращайте внимание на коррелированные подзапросы, которые пересчитывают данные для каждой строки там, где их можно было бы сгруппировать один раз на каждую запись.
• Позвольте базе данных выполнять агрегацию и возвращайте только те строки, которые действительно необходимы в ответе.

Удалите синхронные операции CPU, не имеющие потребителей, и убедитесь, что оптимизированные конечные точки возвращают идентичные результаты перед переключением.