Strona główna / Artykuły / Eliminacja zapytań N+1 oraz powiązanych podzapytań w API Postgres w Node.js

Node.jsPostgreSQLSQLPerformanceExpress

Eliminacja zapytań N+1 oraz powiązanych podzapytań w API Postgres w Node.js

Analiza dwóch wolnych punktów końcowych Express w Postgresie: jak brak indeksów, nawarstwione pętle N+1 oraz zapytania podrzędne na poziomie wiersza wpływają negatywnie na wydajność, oraz jak skrócić czas ich działania do poniżej 200 ms.

2347 słów

Słabe punkty końcowe często zrzuca się na infrastrukturę, jednak wiele z nich działa wolno z powodów widocznych wprost w kodzie obsługującym żądania. W tym analizie przyjrzymy się dwóm punktom końcowym Express obsługiwanym przez Postgres: jednemu, który zwraca zamówienia użytkownika w 2,24 sekundy, oraz punktowi końcowemu do generowania raportów, który potrzebuje około 29 sekund. Żadna z poprawek nie wymaga użycia Redis, dodatkowych serwerów ani nowej architektury; prawie cały opóźnienie spowodowane jest pięcioma powszechnymi błędami, a ich korekta sprawia, że obie odpowiedzi trwają mniej niż 200 ms. Pod koniec powinieneś być w stanie rozpoznać te same wzorce we własnym kodzie i wiedzieć, jakie formy zapytań należy wybrać zamiast nich.

Jeśli szukasz ogólnej metody na zlokalizowanie wąskiego gardła przed zmianą jakiegokolwiek kodu, nasz przewodnik po znajdowaniu prawdziwego wąskiego gardła w wolnym punkcie końcowym Node.js omawia aspekty profilowania. Tutaj nacisk kładziony jest na konkretne wzorce zapytań.

Dwa punkty końcowe pod mikroskopem

Pierwszy obsługiwacz, GET /orders/:userId, ładuje zamówienia użytkownika, następnie przechodzi przez każde zamówienie, aby pobrać jego pozycje, a potem przechodzi przez każdą pozycję, aby pobrać odpowiadający produkt. Dla każdej pozycji oblicza również hash SHA-256 i dołącza go jako pole 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);
});

Drugi obsługa zdarzeń tworzy podsumowanie statusu zamówień. Wybiera każde zamówienie wraz z wskaźnikiem anulowań na użytkownika obliczonym w zapytaniu podrzędnym, a następnie przegląda uzyskane wiersze w JavaScriptu, aby zliczyć liczby statusów i przechować wskaźniki na użytkownika. Chociaż fragment kodu zawiera SQL, sama obsługa zdarzeń jest napisana w JavaScriptu.

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,
  });
});

Na pierwszy rzut oka oba rozwiązania wydają się rozsądne. Problemy stają się widoczne dopiero przy rzeczywistych objętościach danych. Baza testowa zawiera:

  • użytkowników: 5,000
  • produktów: 2,000
  • zamówień: 500,000
  • elementów zamówień: 1,500,000

Pри takiej skali endpoint obsługujący zamówienia potrzebuje 2,24 sekundy, co jest zbyt długo dla prostego ekranu „Moje zamówienia”, natomiast endpoint generujący podsumowanie wymaga 29,26 sekundy.

Dlaczego GET /orders/:userId zajmuje 2,24 sekundy

Użytkownik, który kliknie „Moje zamówienia”, nie powinien czekać dwa sekundy przed widokiem treści. Opóźnienie to wynik trzech oddzielnych problemów, które nakładają się na siebie.

Pierwotne wyszukiwanie przeszukuje i sortuje całą tabelę

Już pierwsze zapytanie zawiera kilka problemów w zaledwie kilku liniach kodu.

const ordersResult = await pool.query(
  `SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
);
  • Nie ma indeksu dla orders.user_id, więc Postgres przeprowadza sekwencyjne przeszukiwanie wszystkich 500 000 wierszy, aby znaleźć około 102 należące do danego użytkownika.
  • Również nie ma żadnego wsparcia dla instrukcji ORDER BY created_at DESC, więc po przeszukiwaniu wiersze są sortowane bez żadnego indeksu, który mógłby pomóc.
  • Wartość userId jest bezpośrednio wstawiana do ciągu SQL. Stanowi to lukę podatną na ataki typu SQL injection, ponieważ wartość pochodzi bezpośrednio z URL, a także sprawia, że każde żądanie staje się unikalnym pod względem tekstu zapytaniem.

Dwukrotnie zagnieżdżony wzorzec N+1

To właśnie pętle są głównym źródłem straty czasu.

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}`);
  • To klasyczny wzorzec N+1, zagnieżdżony dwukrotnie. Około 102 zamówienia generuje 102 zapytania do pozycji, a około 500 pozycji powoduje kolejne ~500 zapytań do produktów. Oznacza to ponad 600 ruchów do Postgresa, wykonywanych jeden po drugim, ponieważ każde await czeka na poprzednie i każdy z nich ponosi pełny koszt opóźnienia sieciowego. Te same dane można pobrać za pomocą jednego do trzech zapytań przy użyciu JOIN lub WHERE id = ANY(...).
  • order_items.order_id nie ma indeksu, więc każde z 102 zapytań do pozycji musi samodzielnie przeczytać 1,5 miliona wierszy.

Hashowanie, które bez powodu blokuje pętlę zdarzeń

Ostatnim problemem jest praca procesora, a nie operacje wejścia/wyjścia.

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

Hash jest obliczany synchronicznie dla każdego elementu, około 500 razy na żądanie. Ponieważ Node.js uruchamia JavaScript w pojedynczym wątku, każde z tych obliczeń blokuje pętlę zdarzeń i opóźnia wszystkie inne żądania obsługiwane przez proces. Co gorsza, wartość ta nigdy nie jest wykorzystywana do cache’owania ani do żądań warunkowych, więc cała praca jest bezużyteczna. Nasz artykuł na temat tego, jak process.nextTick może blokować pętlę zdarzeń wyjaśnia, dlaczego blokowanie tego wątku jest tak kosztowne.

Łącznie opóźnienie wynika z sekwencyjnej sumy ponad 600 ruchów tam i z powrotem, kilku pełnych przeszukiwań tabeli oraz setek obliczeń hashów.

Dlaczego raport podsumowawczy zajmuje około 29 sekund

Użytkownik, który otworzy podsumowanie swojego zamówienia i poczeka prawie pół minuty, uzna racjonalnie, że strona jest uszkodzona. Przyczyna leży w jednym wyrażeniu 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. Podzapytanie korelacyjne jest wykonywane raz na każdy wiersz zewnętrzny. Zapytanie zewnętrzne zwraca wszystkie 500 000 zamówień, a dla każdego z nich Postgres ponownie wykonywa podzapytanie na danych z orders o2, filtrowanych według user_id danego wiersza. Przy około 100 zamówieniach na użytkownika ta sama stopa anulowań jest przeliczana mniej więcej 100 razy dla każdego użytkownika, co daje łącznie około 500 000 wykonań podzapytania.
  2. Brak indeksu utrudnia powtarzane wyszukiwania. Bez indeksu na orders.user_id każde z tych wykonań wymaga pełnego przeszukiwania danych. Indeks sprawiłby, że każde wykonywanie byłoby tańsze, ale nie usunąłby problemu marnotrawstwa zasobów wynikającego z obliczania tej samej odpowiedzi setki razy.
  • Zbyt duży wynik jest agregowany po raz drugi w JavaScript. Wszystkie 500 000 wierszy, z których każdy zawiera obliczoną stawkę, są przesyłane przez sieć do Node, który następnie przechodzi przez nie, aby utworzyć totalsByUser i cancellationRateByUser. To właśnie jest agregacja, którą Postgres mógłby wykonać raz za pomocą GROUP BY.
  • Koszt rośnie wraz z niewłaściwą liczbą. Praca, która powinna być proporcjonalna do 5 000 użytkowników, jest proporcjonalna do 500 000 zamówień, co daje mnożnik około 100 razy większy. Dlatego endpoint potrzebuje od 26 do 29 sekund zamiast około jednej dziesiątej sekundy.

    • Pierwszy krok: dodanie indeksów odpowiadających wzorcowi dostępu

    Indeksy są zazwyczaj najtańszym i najskuteczniejszym pierwszym rozwiązaniem. Potrzebne są tutaj dwa: indeks złożony na orders (user_id, created_at DESC), dzięki któremu zarówno filtrowanie, jak i sortowanie odbywa się za pomocą indeksu, oraz indeks na order_items (order_id) do wyszukiwania pozycji. Poniższy polecenie tworzy oba indeksy wewnątrz kontenera Postgres; flaga IF NOT EXISTS zapewnia bezpieczeństwo przy ponownym uruchomieniu.

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

    Kolejność kolumn w indeksie złożonym ma znaczenie. Umieszczenie user_id na początku pozwala Postgresowi przechodzić bezpośrednio do wierszy danego użytkownika, a ponieważ te wiersze są już zapisane w kolejności created_at DESC wewnątrz indeksu, krok sortowania całkowicie znika. W przypadku intensywnie używanej tabeli produkcyjnej rozważ użycie polecenia CREATE INDEX CONCURRENTLY, aby proces tworzenia indeksu nie blokował zapisów.

    Gdy indeksy są już ustawione, oba mechanizmy obsługi mogą zostać przepisane tak, aby pobierały dane masowo i pozwalaly bazie danych na ich agregację.

    Przepisywanie GET /orders/:userId przy użyciu zapytań zbiorczych

    Nowa wersja wysyła dokładnie trzy zapytania. Pobiera zamówienia użytkownika za pomocą zapytania parametryzowanego, zbiera ich identyfikatory oraz ładuje wszystkie powiązane elementy przy użyciu order_id = ANY($1), następnie usuwa duplikaty identyfikatorów produktów za pomocą Set i ładuje te produkty w kolejnym zapytaniu. Wyniki są łączone w pamięci za pomocą dwóch wyszukiwań w Map, a wcześniejszy powrót obsługuje przypadki, gdy użytkownik nie ma żadnych zamówień.

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

    Zmiany, po jednej:

    1. Kompozytny indeks na orders (user_id, created_at DESC) zamienia operację WHERE wraz z ORDER BY na skanowanie indeksu zamiast skanowania sekwencyjnego i sortowania 500 000 wierszy.
  • Indeks na order_items (order_id) oznacza, że wyszukiwanie pozycji nie musi już przeszukiwać 1,5 miliona wierszy.
  • Schemat N+1 zostaje zastąpiony trzema zapytaniami: jednym dla zamówień, jednym dla wszystkich ich pozycji oraz jednym dla wszystkich unikalnych produktów. Ponad 600 zserializowanych ruchów w obie strony staje się zaledwie trzema.
  • Zastępniki takie jak $1 zamienniczo realizują interpolację ciągów znakowych, co eliminuje lukę podatną na ataki SQL injection. Należy pamiętać, że w node-postgres nadal planowana jest jedna nieoznaczona zapytanie parametryzowane na każdą eksploatację; jeśli chcesz, aby Postgres ponownie wykorzystał ten plan, użyj oznaczonego zapytania przygotowanego.
  • Nie używany hasz SHA-256 dla każdej pozycji został usunięty, co eliminuje bezsensowne prace blokujące w pętli zdarzeń.

    • Jedna praktyczna uwaga: usunięcie pola etag zmienia strukturę odpowiedzi. Upewnij się, że żaden klient od niego nie zależy, przed wdrożeniem tej zmiany.

    Ponowne napisanie raportu za pomocą GROUP BY

    Optymalizowany raport wykonywa dwa skoncentrowane zapytania agregacyjne: jedno grupuje według user_id i status, aby uzyskać liczbę rekordów, a drugie grupuje według user_id, aby obliczyć wskaźnik anulowań. JavaScript jedynie przekształca już zagrupowane wiersze do formatu odpowiedzi. Jak poprzednio, obsługę realizuje JavaScript z wplecionym 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,
  });
});

    Co się zmieniło i dlaczego to ma znaczenie:

    1. Korelowana podzapytanie staje się GROUP BY user_id. Postgres oblicza ten wskaźnik raz na użytkownika w ramach jednej operacji agregacyjnej: 5 000 wykonań zamiast około 500 000.
    2. Zbiór wyników zmniejsza się z 500 000 wierszy do około 5 000–20 000. Postgres zwraca jeden wiersz na każdą parę (user_id, status) dla liczb rekordów oraz jeden wiersz na użytkownika dla wskaźnika, bez powtarzających się kolumn obliczonych.
  • Dwie niezależne zapytania zastępują jedno zapytanie z mnożeniem wierszy. Każde z nich wymaga tylko jednego przeszukania tablicy orders, zamiast jednego zapytania wplecionego w drugie dla każdego wiersza.
  • Węzeł nie agreguje już ponownie. W JavaScript nie uruchamia się żadnego pętla liczenia; obsługa po prostu mapuje zgrupowane wiersze na klucze.

    • Czas wykonywania skrócił się z około 26 do 29 sekund na mniej więcej 0,1 do 0,19 sekundy, co oznacza prędkość około 150 do 260 razy większą. Sprawdzono również, że nowy endpoint zwraca takie same wartości totalsByUser i cancellationRateByUser jak oryginał dla wszystkich 5000 użytkowników. Ta weryfikacja równoważności jest godna naśladowania: zawsze, gdy przepisujesz zapytanie w celu poprawy szybkości, porównaj wyniki z wersją wolniejszą przed jej zastąpieniem.

    Oba sposoby agregacji nadal odczytują całą tabelę orders przy każdej prośbie. W tym skali to nie stanowi problemu, ale jeśli tabela będzie dalej rosła, pojedyncze zapytanie wykorzystujące klauzule FILTER, widok materializowany lub tabelę podsumowawczą odświeżaną okresowo może znacznie poprawić wydajność raportu.

    Główne wnioski

    • Stwórz indeksy dla kolumn, którymi filtrowasz i sortujesz, oraz dopasuj kolejność indeksów złożonych do zapytania: najpierw kolumny równości, a potem kolumna sortowania.
    • Każde wywołanie await db.query() wewnątrz pętli należy traktować jako sygnał ostrzegawczy; zastąp je zapytaniami typu JOIN lub ANY($1) służącymi do przetwarzania zbiorów danych.
    • Zawsze używaj zapytań parametryzowanych dla wartości pochodzących z żądania.
    • Uważaj na podzapytania korelowane, które ponownie obliczają dane dla każdego wiersza, podczas gdy można je zgrupować raz na całą instancję.
    • Pozwól bazie danych na agregację i zwróć tylko te wiersze, których faktycznie potrzebuje odpowiedź.
  • Usuń synchroniczne zadania CPU, które nie mają odbiorcy, i sprawdź, czy zoptymalizowane punkty końcowe zwracają identyczne wyniki przed przeprowadzeniem zmiany.

    • Literatura pokrewna

    Wszystkie artykułyWyślij brief