Узнайте о запросах N + 1, почему и как их избежать

Ok. Вы все еще инженер-программист, но пишете неэффективные запросы, которые легко исправить. Это шаблон, который пишет почти каждый новый разработчик, включая меня, когда я начал свою первую роль разработчика Ruby on Rails.

Это так злонамеренно, потому что при небольшом масштабе данных обратная сторона минимальна. Но поскольку его стоимость линейно масштабируется по мере роста данных, она неизбежно достигает критических для приложений масштабов, как только ваша платформа начинает привлекать много новых пользователей.

Мы рассмотрим примеры для нескольких различных сред (SQL, Python, Rails), и я покажу вам, как их исправить.

1) Чистый SQL
2) Python
3) Рельсы

Что такое запрос N + 1?

Проще говоря, это когда каждая загруженная запись из запроса выполняет свой собственный запрос.

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

Предпочтительное решение - присоединить автора к статьям на уровне базы данных в одной транзакции.

Поскольку ORM (объектно-реляционные сопоставления) обеспечивают такую ​​абстракцию и любят ленивую загрузку объектов, часто очень легко сделать это случайно.

Пример SQL

Вот как будет выглядеть приведенный выше пример в SQL. Выполняем запрос на получение статей.

SELECT * FROM articles

Затем для каждой статьи мы выполняем еще один запрос, чтобы получить автора этой статьи. Итак, если есть 25 статей, то второй запрос выполняется 25 раз.

SELECT * FROM authors WHERE authors.id = article.author_id

Мы должны сделать это, а не выше.

SELECT 
  * 
FROM articles
LEFT JOIN authors ON authors.id = articles.author_id

И загрузите все сразу.

Конечно, это легко заметить при написании чистого SQL без каких-либо других ограничений или беспорядка, поэтому давайте рассмотрим более сложные примеры.

Пример Python

Давайте перепишем выше, но в реальном коде Python, который вы можете запустить в Jupyter Notebooks или приложении Python.

import psycopg2 as pg
conn = psycopg2.connect(dbname='mydb')
cur = conn.cursor()
# Get all articles
cur.execute("""SELECT id,title from articles""")
articles = cur.fetchall()
# For each article, get the author
for a in articles:
    cur.execute("""
      SELECT name from authors where authors.id = {}""".format(a[0]) 
    )
    author = cur.fetchall()[0]
    print(a[1], author)
cur.close()
conn.close()

Выше мы импортируем pyscopg2, который позволяет нам выполнять SQL на Python, открывать соединение с базой данных и выполнять запрос, чтобы получить idиtitle для каждой статьи. Затем мы перебираем эти статьи и для каждой выполняем другой запрос, чтобы загрузить автора статьи.

Мы должны были написать это вместо этого.

import psycopg2 as pg
conn = psycopg2.connect(dbname='mydb')
cur = conn.cursor()
# Get articles and their authors at the same time
cur.execute("""
    SELECT 
        articles.id,
        articles.title, 
        authors.name
    from articles 
    left join authors on authors.article_id = articles.id
""")
articles = cur.fetchall()
# for each article, print the article title and author name
for a in articles:
    print(a[1],a[2])
cur.close()
conn.close()

Здесь мы одновременно извлекали статьи и их авторов.

Иногда вы действительно хотите загружать такие данные (или партиями), особенно когда загруженные объекты очень большие и могут превышать объем памяти вашего компьютера. Это более распространено в конвейерах данных или при обучении модели машинного обучения.

Пример рельсов

В среде фреймворка еще проще случайно написать N + 1 запросов. Если у вас нет опыта работы с Rails, обратите внимание, что контроллер содержит логику, поддерживающую представление.

# Controller
@articles = Article.all
# View
@articles.each do |a|
  <%= a.title %>
  <%= a.author.name %>

Вышеупомянутые результаты приводят к выполнению следующих запросов.

SELECT "articles".* FROM "articles"

SELECT "authors".* FROM "authors" WHERE "articles"."id" = $1 LIMIT 1 [["id", 1]]
SELECT "authors".* FROM "authors" WHERE "authors"."id" = $1 LIMIT 1 [["id", 2]]
SELECT "authors".* FROM "authors" WHERE "authors"."id" = $1 LIMIT 1 [["id", 3]]
SELECT "authors".* FROM "authors" WHERE "authors"."id" = $1 LIMIT 1 [["id", 4]]
SELECT "authors".* FROM "authors" WHERE "authors"."id" = $1 LIMIT 1 [["id", 5]]
SELECT "authors".* FROM "authors" WHERE "authors"."id" = $1 LIMIT 1 [["id", 6]]
SELECT "authors".* FROM "authors" WHERE "authors"."id" = $1 LIMIT 1 [["id", 7]]
...

Что ужасно! Надо было написать.

# Controller
@articles = Article.includes(:author)
# View
@articles.each do |a|
  <%= a.title %>
  <%= a.author.name %>

Это приводит к выполнению следующего SQL.

SELECT  "articles".* FROM "articles"
SELECT "authors".* FROM "authors" WHERE "authors"."article_id" IN (1, 2, 3,...)

Несмотря на то, что это не отдельный запрос, это значительное улучшение, особенно если первый запрос вернул тысячи записей.

Заключение

N + 1 запросов легко выполнить и (обычно) легко исправить. Но снижение производительности может быть весьма значительным, когда N количество записей велико, поэтому лучше избегать этого шаблона запроса, даже если ваше приложение небольшое, чтобы это не стало проблемой при масштабировании.

Если у вас есть другие способы справиться с N + 1, я буду рад их здесь в комментариях!