Усуненне запыткаў 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,
  });
});

Якраз працюе швытка, і аба рашэння здаюцца разумнымі. Проблэмы стаюць виднымі толькі праз рэальныя об’ёмы дадзеных. У тестовай базе дадзеных є:

• users: 5,000
• products: 2,000
• orders: 500,000
• order_items: 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. Цэў адкрывае можлівасць для втрымкі 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 мільйона рэядоў.

Хешаванне, якое без прычыны блакуе цикл адбывання запытоў

Пасляднія проблемы стосуюцца роботы CPU, а не I/O.

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

Хэш вычысляецца сінхронна для кожнага элемента, прыблізна 500 разоў за адну запытку. Пакалі Node.js выкананае на аднай нітка, кожны з гэтых вычысленняў блакуе цыкл адбывання запыткаў і затрымляе всі іншы запыткі, якія обрабоўваецца працэсам. Што ўжо горш, гэтае значэнне ніколі не выкарыстоўваецца для кэшавання чы роботы пад умовамі, таму весь гэты труд не прыносіць нічога. Нашая стаття на тэму таго, як 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 з выкарыстоўванням пакетных запытоў

Новая версія выканае роўна тры запыты. Яна запрашвае замовленні корыстувальца за дапамогою параметрызаванага запыту, збірае ўсі іх ID-ы і завантажвае всі супаўзвязаныя элементы за дапамогою order_id = ANY($1); пасля чаго усувае дуплікаты ID-аў продуктав за дапамогою 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 для всіх 5 000 корыстнікаў, як і ў первасны версіі. Такая перакананае прадуктыўнае роўнасці варта копіювання: калі вы перапісваеце запит для паўшчыні, спачатку пораўняйце выходныя даны з медленай версіяй, прычым перад тым, як яе заменіць.

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

Ключовыя выводы

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

Адзьякавайце сінхронную роботу CPU, яка не мае спожывача, і пераканайцеся, што оптымізаваныя канцэнтыры вяртаюць ідэнтычныя рэзультаты, прытаму пераходзіць на іх.