Веб-разработка

N+1 в списке заказов: быстрые запросы и медленный ответ

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

Диагностика по числу обращений

Диагноз начинается с трассировки одного HTTP-запроса, включая сериализацию. Повторяющиеся SELECT покупателей и рост числа обращений вместе с размером страницы поддерживают гипотезу N+1. Один медленный SQL и сотня быстрых SQL требуют разных исправлений. Общая статистика базы без связи с запросом API способна скрыть эту разницу.

Учебная схема и договор результата

Ниже приведена синтетическая модель для PostgreSQL и клиента psql. В ней нет измеренных задержек и истории настоящей аварии. Все блоки относятся к одной сессии учебной базы. Временные таблицы не затрагивают постоянные данные, а завершающий ROLLBACK отменяет подготовку примера.

В модели заказ может не иметь покупателя, а имя покупателя может быть неизвестно. Это намеренно более широкий договор, чем схема с обязательными customer_id и name. Ответ должен сохранять каждый заказ, его customer_id и значение имени, включая NULL. Одинаковые имена не означают одного покупателя.

BEGIN;

CREATE TEMP TABLE customers (
    id integer PRIMARY KEY,
    name text
);

CREATE TEMP TABLE orders (
    id integer PRIMARY KEY,
    customer_id integer REFERENCES customers(id)
);

INSERT INTO customers VALUES
    (1, 'Альфа'), (2, 'Альфа'), (3, NULL);

INSERT INTO orders VALUES
    (101, 1), (102, 1), (103, 2), (104, 3), (105, NULL);

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

Воспроизведение N+1 в psql

Дополнительные SELECT часто скрыты в цикле приложения или ленивой загрузке ORM. В psql команда \gexec воспроизводит эти обращения: первый SELECT формирует пять команд, затем клиент отправляет каждую отдельно. Точка с запятой перед \gexec не нужна.

SELECT format(
    'SELECT %s AS id, %L::integer AS customer_id, '
    || '(SELECT name FROM customers WHERE id = %L::integer) AS name;',
    id, customer_id, customer_id
)
FROM orders
ORDER BY id DESC
LIMIT 5
\gexec

Это один запрос списка команд и пять запросов обогащения, то есть шесть обращений для пяти заказов. Для ста строк тот же алгоритм даст 101 обращение. Числа описывают этот конкретный алгоритм без кеширования и пропуска пустых связей. Кеш ORM или пакетная загрузка способны изменить фактический счетчик, поэтому название N+1 само по себе не заменяет наблюдение.

format с %L здесь безопасно оформляет литералы из учебных строк. В рабочем приложении значения параметров передаются средствами драйвера. Генерация SQL через \gexec нужна только для воспроизведения клиентского цикла; это не рекомендуемый способ построения API. Коррелированный подзапрос внутри каждой команды также не равен отдельному сетевому обращению: граница проходит между командами клиента.

Один запрос с сохранением семантики

Для данного договора один запрос может вернуть всю страницу:

SELECT o.id, o.customer_id, c.name
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id DESC
LIMIT 5;

Первичный ключ customers.id гарантирует не более одного совпадения. LEFT JOIN сохраняет заказ, когда customer_id равен NULL. Внешний ключ исключает ссылку на несуществующего покупателя для непустого customer_id. Поэтому соединение не размножает заказы, а LIMIT по-прежнему ограничивает их количество. Внешний ORDER BY нужен и после соединения: порядок хранения строк не является договором результата.

Если customer_id объявлен NOT NULL, внешний ключ действует, а покупатель доступен запросу без дополнительных ограничений, INNER JOIN тоже сохраняет состав. Но перенос такого исправления в схему с необязательной связью удалит заказ 105. Поэтому выбор соединения определяется ограничениями данных и договором API.

Условие на правую таблицу в WHERE тоже требует внимания. Например, WHERE c.name IS NOT NULL исключит и заказ без покупателя, и заказ покупателя с неизвестным именем. Перенос условия в ON сохранит заказ, но способен скрыть его данные о покупателе. Это разные правила отбора, а не взаимозаменяемые оптимизации. Фильтры доступа и принадлежности организации должны сохранять исходную семантику.

Проверка результата и числа SQL-запросов

Проверка исправления начинается с результата, а не с секундомера. Эталонная последовательность троек [id, customer_id, name] для примера имеет такой вид:

[
  [105, null, null],
  [104, 3, null],
  [103, 2, "Альфа"],
  [102, 1, "Альфа"],
  [101, 1, "Альфа"]
]

Исходный вариант выдает пять отдельных наборов из одной строки; после сборки в порядке получения они должны совпасть с этим массивом. Исправленный вариант должен дать ту же последовательность одним набором. Интеграционная проверка сравнивает массив целиком: пять строк, порядок идентификаторов и все значения. Сравнение множества имен недостаточно. Оно потеряет повторения и не обнаружит перестановку заказов. NULL нельзя незаметно заменять пустой строкой или текстовым обозначением неизвестного значения, если такой замены нет в договоре.

Отдельные случаи для проверки: пустая страница и размеры 1, 3, 5. У исправленного варианта остается один SELECT на загрузку этих данных; у показанного цикла получается 1 + N. Счетчик должен охватывать сериализацию: успешный тест репозитория еще не доказывает отсутствие ленивых запросов при построении JSON.

Завершение учебной сессии:

ROLLBACK;

Измерения и границы применимости

На рабочем стенде сравниваются задержки API, количество SQL, объем данных и нагрузка на базу при одинаковых условиях. EXPLAIN показывает план, но не сетевую цену сотни клиентских вызовов. Без измерений нельзя обещать конкретное ускорение или снижение p95.

У решения есть границы. Для связи один ко многим JOIN с LIMIT может ограничить строки соединения вместо заказов. Тогда сначала определяется страница заказов, затем связанные строки загружаются отдельно или агрегируются с явно заданной семантикой. Альтернатива для текущей модели состоит из двух запросов: страница заказов и покупатели по уникальным непустым идентификаторам. Сборка ответа сохраняет исходный порядок заказов, поскольку IN не задает порядок покупателей.

Несколько SELECT при Read Committed могут видеть разные снимки; один JOIN читает данные в рамках одного оператора. Изменение имени между запросами способно нарушить сравнение без ошибки соединения. Требование согласованности чтения определяется отдельно. Увеличение пула и глобальная eager-загрузка не заменяют этого анализа.

Первичные источники

Учебные материалы

Связь с продуктом: тема N+1 входит в русскоязычный автономный каталог Сбой найден. В нем 1000 синтетических карточек по 40 темам в HTML/PDF/JSON; отдельно представлены 8 исполняемых учебных моделей и 3 упражнения со сломанным кодом, приемочными проверками и эталонами. Цена пакета составляет 1490 руб. Карточки не равнозначны тысяче исполненных лабораторий. На сайте доступны две браузерные задачи и бесплатный архив: SQL-упражнение с десятью проверками и десять полных карточек. Бесплатное упражнение входит в число трех упражнений полного комплекта.

Оставить комментарий