Запросы, которые тормозят

N+1 и индексы на измеренных числах

Что это

Две причины, по которым бэкенд начинает тормозить, и обе ИИ воспроизводит охотно: лишние запросы в цикле и отсутствие индексов.

Коварство в том, что на маленьких данных обе незаметны. В разработке с десятью пользователями всё летает; на двухстах начинает подтормаживать; на тысячах встаёт. Код при этом не менялся.

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

N+1 запрос

Классика. Берём пользователей, потом для каждого — его подписки:

users = conn.execute("SELECT id, name FROM users").fetchall()
for u in users:
    conn.execute("SELECT city FROM subs WHERE user_id = ?", (u["id"],))

Выглядит невинно. На деле это один запрос плюс по одному на каждого пользователя — отсюда и название.

То же самое одним запросом:

conn.execute("""
    SELECT u.name, s.city
    FROM users u
    LEFT JOIN subs s ON s.user_id = u.id
""")

Замер на 200 пользователях и 2000 подписках:

N+1:    201 запросов, 20.7 мс
JOIN:     1 запрос,    2.7 мс, строк 2000

В восемь раз быстрее, и это на локальной базе в памяти. Если база на другом сервере, каждый запрос — ещё и поход по сети: двести походов вместо одного. Там разница будет не в восемь раз, а в сотни.

Индексы

Индекс — служебная структура, которая позволяет базе находить строки, не перебирая все.

CREATE INDEX idx_subs_user ON subs(user_id);

Тот же набор запросов до и после:

без индекса: 15.4 мс
с индексом:   1.4 мс     — быстрее в 10,6 раза

Одна строка в схеме, десятикратное ускорение. При этом индекс — не бесплатный: он занимает место и чуть замедляет запись. Поэтому их ставят не на всё подряд, а туда, где ищут.

Правило: индекс нужен на поля, по которым идёт поиск или связывание — то, что стоит в WHERE и в ON. В нашем случае subs.user_id участвует в обоих.

Как увидеть проблему

В SQL есть встроенный способ спросить у базы, как она собирается выполнять запрос:

EXPLAIN QUERY PLAN SELECT city FROM subs WHERE user_id = 7;

Если в ответе SCAN subs — база перебирает всю таблицу. Если SEARCH subs USING INDEX — пользуется индексом. В PostgreSQL то же самое называется EXPLAIN ANALYZE и печатает ещё и реальное время.

Как сказать ИИ

Здесь запрос к базе внутри цикла. Перепиши одним запросом
с JOIN. Результат должен быть тем же: список пользователей,
у каждого — его города, включая пользователей без подписок.

Последняя фраза важна: без неё INNER JOIN потеряет тех, у кого подписок нет (статья 7.5).

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

Этот обработчик делает сколько запросов к базе при одном
HTTP-запросе? Посчитай.

Последний — хороший способ проверки: ответ «один» или «два» нормален, «зависит от числа пользователей» означает N+1.

На что смотреть

Запрос внутри цикла. Главный признак. Ищи for и execute рядом.

Запрос внутри функции, которая вызывается в цикле. То же самое, но на уровень глубже и потому незаметнее. Признак: функция вида get_cities(user) вызывается в обходе списка.

ORM, которая делает запросы лениво. В коде обращение к полю, в базе — запрос. for u in users: print(u.subs) выглядит как работа с памятью, а является N+1.

Индексов нет вообще. Кроме PRIMARY KEY, который создаётся сам. Если в схеме ни одного CREATE INDEX, а поиск по полям идёт — они нужны.

Индекс на всё подряд. Обратная крайность: каждый индекс замедляет запись и занимает место.

«Работает же». На твоих тестовых двадцати записях — работает. Проверяй на объёме, похожем на реальный.

Шпаргалка