Проблема N+1 и EXPLAIN QUERY PLAN

Этот вопрос любят на собеседованиях бэкенд-разработчиков, потому что он про реальную боль: страница грузится секунду, хотя каждый отдельный запрос выполняется за миллисекунду. В прошлом уроке мы ускоряли один медленный запрос. Здесь виноват не запрос, а их количество.

Как спрашивают

Что такое проблема N+1? Как её обнаружить и как исправить?

Часто следом: «Как посмотреть, что база делает с запросом?»

Короткий ответ

N+1 это когда код делает один запрос за списком из N записей, а потом ещё по одному запросу на каждую запись за связанными данными. Итого N+1 запрос вместо одного или двух. Каждый быстрый, но сетевые задержки и накладные расходы складываются. Обнаруживают по логу запросов: видно сотню одинаковых SELECT, отличающихся только параметром. Лечат одним запросом с JOIN или вторым запросом с IN (...) по всем идентификаторам сразу. Чтобы понять, как база выполняет конкретный запрос, смотрят план: EXPLAIN в PostgreSQL и EXPLAIN QUERY PLAN в SQLite.

Разбор: поход в магазин

Тебе нужно купить продукты по списку из десяти пунктов. Можно сходить в магазин один раз со всем списком. А можно сходить за списком, вернуться, потом сходить за молоком, вернуться, сходить за хлебом. Каждый поход короткий, но дорога туда и обратно съедает всё время.

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

Как это выглядит в коде

Задача: показать клиентов из учебной базы и их заказы. Псевдокод с ORM:

const customers = await Customer.findAll();        // 1 запрос
for (const customer of customers) {
  const orders = await customer.getOrders();        // ещё N запросов
  print(customer.name, orders);
}

ORM с ленивой загрузкой отправит в базу:

SELECT id, name FROM customers;
SELECT * FROM orders WHERE customer_id = 1;
SELECT * FROM orders WHERE customer_id = 2;
SELECT * FROM orders WHERE customer_id = 3;
SELECT * FROM orders WHERE customer_id = 4;

Четыре клиента дали пять запросов. Тысяча клиентов даст 1001. Код выглядит невинно: цикл и обращение к полю, ни одного явного SQL.

Как обнаружить

• Лог запросов. Включи логирование SQL в ORM или в базе и открой страницу. Если видишь длинную серию одинаковых запросов с разными параметрами, это N+1.
• Счётчик запросов на запрос пользователя. Многие фреймворки умеют показывать, сколько запросов к базе сделал один HTTP-запрос. Число, растущее вместе с размером списка, выдаёт проблему.
• Тест. В тесте можно проверить, что страница делает не больше заданного числа запросов.

Лечение первое: один запрос с JOIN

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

name  | order_id | amount
------+----------+-------
Ольга |        1 |   5000
Ольга |        2 |   3000
Ольга |        4 |   3000
Павел |        3 |   7000
Роман |        5 |   1200
Света |          |

LEFT JOIN, а не обычный, чтобы Света без заказов не пропала, это разбиралось в уроке про виды JOIN. Минус: данные клиента повторяются в каждой строке его заказов, и приложение должно собрать их обратно в группы.

Лечение второе: два запроса с IN

SELECT id, name FROM customers;
SELECT customer_id, id, amount FROM orders WHERE customer_id IN (1, 2, 3, 4);

Список идентификаторов приложение берёт из первого ответа, а заказы раскладывает по клиентам у себя в памяти. Ровно два запроса при любом числе клиентов. Так работает жадная загрузка в большинстве ORM: include в Prisma, prefetch_related в Django, JOIN FETCH в Hibernate.