Что такое "проблема выбора N + 1" в ORM (объектно-реляционное отображение)?

"Проблема выбора N + 1" обычно указывается как проблема в обсуждениях объектно-реляционного отображения (ORM), и я понимаю, что это связано с необходимостью выполнять множество запросов к базе данных для чего-то, что кажется простым в объекте Мир.

У кого-нибудь есть более подробное объяснение проблемы?

Ответы

Ответ 1

Допустим, у вас есть коллекция объектов Car (строки базы данных), и у каждого Car есть коллекция объектов Wheel (также строк). Другими словами, Car → Wheel является отношением 1-ко-многим.

Теперь, допустим, вам нужно пройтись по всем машинам, и для каждой из них распечатать список колес. Наивная реализация O/R сделала бы следующее:

SELECT * FROM Cars;

А затем для каждого Car:

SELECT * FROM Wheel WHERE CarId = ?

Другими словами, у вас есть один выбор для автомобилей, а затем N дополнительных выборов, где N - общее количество автомобилей.

Кроме того, можно получить все колеса и выполнить поиск в памяти:

SELECT * FROM Wheel

Это уменьшает количество обращений к базе данных с N + 1 до 2. Большинство инструментов ORM дают вам несколько способов предотвратить выбор N + 1.

Ссылка: Сохранение Java с помощью Hibernate, глава 13.

Ответ 2

SELECT 
table1.*
, table2.*
INNER JOIN table2 ON table2.SomeFkId = table1.SomeId

Это дает вам набор результатов, в котором дочерние строки в таблице2 вызывают дублирование, возвращая результаты таблицы1 для каждой дочерней строки в таблице2. Маршрутизаторы O/R должны различать экземпляры таблицы1 на основе уникального ключевого поля, а затем использовать все столбцы таблицы2 для заполнения дочерних экземпляров.

SELECT table1.*

SELECT table2.* WHERE SomeFkId = #

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

Рассмотрим:

class House
{
    int Id { get; set; }
    string Address { get; set; }
    Person[] Inhabitants { get; set; }
}

class Person
{
    string Name { get; set; }
    int HouseId { get; set; }
}

и таблицы с аналогичной структурой. Один запрос для адреса "22 Valley St" может вернуться:

Id Address      Name HouseId
1  22 Valley St Dave 1
1  22 Valley St John 1
1  22 Valley St Mike 1

O/RM должен заполнить экземпляр Home с ID = 1, Address = "22 Valley St", а затем заполнить массив "Жильцы" экземплярами People для Dave, John и Mike с помощью всего одного запроса.

Запрос N + 1 для того же адреса, который использовался выше, приведет к:

Id Address
1  22 Valley St

с отдельным запросом типа

SELECT * FROM Person WHERE HouseId = 1

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

Name    HouseId
Dave    1
John    1
Mike    1

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

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

Ответ 3

Поставщик с отношением "один ко многим" с продуктом. Один поставщик имеет (поставляет) много продуктов.

***** Table: Supplier *****
+-----+-------------------+
| ID  |       NAME        |
+-----+-------------------+
|  1  |  Supplier Name 1  |
|  2  |  Supplier Name 2  |
|  3  |  Supplier Name 3  |
|  4  |  Supplier Name 4  |
+-----+-------------------+

***** Table: Product *****
+-----+-----------+--------------------+-------+------------+
| ID  |   NAME    |     DESCRIPTION    | PRICE | SUPPLIERID |
+-----+-----------+--------------------+-------+------------+
|1    | Product 1 | Name for Product 1 |  2.0  |     1      |
|2    | Product 2 | Name for Product 2 | 22.0  |     1      |
|3    | Product 3 | Name for Product 3 | 30.0  |     2      |
|4    | Product 4 | Name for Product 4 |  7.0  |     3      |
+-----+-----------+--------------------+-------+------------+

Факторы:

  • Lazy mode для поставщика, установленного как "true" (по умолчанию)

  • Режим выборки, используемый для запросов на Продукт, выбирается

  • Режим выборки (по умолчанию): Доступ к информации о поставщике

  • Кэширование не играет роли в первый раз

  • Доступ к поставщику

Режим Fetch - выбор Fetch (по умолчанию)

// It takes Select fetch mode as a default
Query query = session.createQuery( "from Product p");
List list = query.list();
// Supplier is being accessed
displayProductsListWithSupplierName(results);

select ... various field names ... from PRODUCT
select ... various field names ... from SUPPLIER where SUPPLIER.id=?
select ... various field names ... from SUPPLIER where SUPPLIER.id=?
select ... various field names ... from SUPPLIER where SUPPLIER.id=?

Результат:

  • 1 инструкция select для продукта
  • N выбор операторов для Поставщика.

Это проблема с выбором N + 1!

Ответ 4

Я не могу прямо комментировать другие ответы, потому что у меня недостаточно репутации. Но стоит отметить, что проблема по существу возникает только потому, что, исторически, многие dbms были довольно плохими, когда речь заходила о обработке объединений (в качестве примера, заслуживающего внимания MySQL). Таким образом, n + 1, как правило, заметно быстрее, чем соединение. И тогда есть способы улучшить n + 1, но все же без необходимости соединения, с чем связана исходная проблема.

Однако MySQL теперь намного лучше, чем раньше, когда дело доходит до присоединения. Когда я впервые изучил MySQL, я много использовал. Затем я обнаружил, насколько они медленны, и вместо этого переключился на n + 1. Но в последнее время я возвращаюсь к объединению, потому что MySQL теперь намного лучше справляется с ними, чем когда я начал использовать его.

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

Это обсуждается здесь одной из разработчиков MySQL:

http://jorgenloland.blogspot.co.uk/2013/02/dbt-3-q3-6-x-performance-in-mysql-5610.html

Итак, резюме: если вы избегали объединений в прошлом из-за безубыточной производительности MySQL с ними, повторите попытку в последних версиях. Вы, вероятно, будете приятно удивлены.

Ответ 5

Мы отошли от ORM в Django из-за этой проблемы. В принципе, если вы попытаетесь сделать

for p in person:
    print p.car.colour

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

Простой и очень эффективный подход к этому - это то, что я называю " фанфайлов", что позволяет избежать бессмысленной идеи о том, что результаты запроса из реляционной базы данных должны отображаться обратно к исходным таблицам, из которых выполняется запрос состоит.

Шаг 1: Широкий выбор

  select * from people_car_colour; # this is a view or sql function

Это вернет что-то вроде

  p.id | p.name | p.telno | car.id | car.type | car.colour
  -----+--------+---------+--------+----------+-----------
  2    | jones  | 2145    | 77     | ford     | red
  2    | jones  | 2145    | 1012   | toyota   | blue
  16   | ashby  | 124     | 99     | bmw      | yellow

Шаг 2: Objectify

Соедините результаты с создателем общего объекта с аргументом для разделения после третьего элемента. Это означает, что объект "jones" не будет выполняться более одного раза.

Шаг 3: рендеринг

for p in people:
    print p.car.colour # no more car queries

См. эту веб-страницу для реализации фанфинга для python.

Ответ 6

Предположим, у вас есть КОМПАНИЯ и РАБОТНИК. КОМПАНИЯ имеет много СОТРУДНИКОВ (т.е. EMPLOYEE имеет поле COMPANY_ID).

В некоторых конфигурациях O/R, когда у вас есть сопоставленный объект компании и перейдите к его объектам Employee, инструмент O/R сделает один выбор для каждого сотрудника, но если вы просто делаете что-то в прямом SQL, вы может select * from employees where company_id = XX. Таким образом, N (# сотрудников) плюс 1 (компания)

Вот как работали начальные версии EJB Entity Beans. Я считаю, что такие вещи, как Hibernate, покончили с этим, но я не уверен. Большинство инструментов обычно включают информацию о своей стратегии для сопоставления.

Ответ 7

Вот хорошее описание проблемы

Теперь, когда вы понимаете проблему, ее обычно можно избежать, выполнив выборку соединения в вашем запросе. Это в основном вызывает выборку загруженного объекта с отложенным доступом, поэтому данные извлекаются в одном запросе вместо n + 1 запросов. Надеюсь это поможет.

Ответ 8

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

List<PostComment> comments = entityManager.createQuery(
    "select pc " +
    "from PostComment pc " +
    "where pc.review = :review", PostComment.class)
.setParameter("review", review)
.getResultList();

LOGGER.info("Loaded {} comments", comments.size());

for(PostComment comment : comments) {
    LOGGER.info("The post title is '{}'", comment.getPost().getTitle());
}

Который генерирует следующие операторы SQL:

SELECT pc.id AS id1_1_, pc.post_id AS post_id3_1_, pc.review AS review2_1_
FROM   post_comment pc
WHERE  pc.review = 'Excellent!'

INFO - Loaded 3 comments

SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_
FROM   post pc
WHERE  pc.id = 1

INFO - The post title is 'Post nr. 1'

SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_
FROM   post pc
WHERE  pc.id = 2

INFO - The post title is 'Post nr. 2'

SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_
FROM   post pc
WHERE  pc.id = 3

INFO - The post title is 'Post nr. 3'

Сначала Hibernate выполняет запрос JPQL, и выбирается список сущностей PostComment.

Затем для каждого PostComment соответствующее свойство post используется для создания сообщения журнала, содержащего заголовок Post.

Поскольку post ассоциация не инициализирована, Hibernate должен извлечь сущность Post со вторичным запросом, а для N сущностей PostComment будет выполнено еще N запросов (следовательно, проблема с N + 1 запросом).

Во-первых, вам нужна правильная регистрация и мониторинг SQL, чтобы вы могли обнаружить эту проблему.

Во-вторых, этот тип проблемы лучше поймать интеграционными тестами. Вы можете использовать автоматическое утверждение JUnit для проверки ожидаемого количества сгенерированных операторов SQL. Проект db-unit уже предоставляет эту функциональность и имеет открытый исходный код.

Когда вы определили проблему с запросом N + 1, вам нужно использовать JOIN FETCH, чтобы дочерние ассоциации выбирались в одном запросе вместо N. Если вам нужно получить несколько дочерних ассоциаций, лучше выбрать одну коллекцию в начальном запросе, а вторую - вторичный SQL-запрос.

Ответ 9

Прочтите сообщение Айенде на тему: Борьба с проблемой N + 1 в NHibernate.

В основном, при использовании ORM, например NHibernate или EntityFramework, если у вас есть отношение "один ко многим" (master-detail), и вы хотите перечислить все детали для каждой основной записи, вы должны сделать N + 1 запросов на запрос к база данных, где "N" - это число основных записей: 1 запрос, чтобы получить все основные записи, и N запросов, по одному на каждую основную запись, чтобы получить все данные для основной записи.

Больше вызовов запросов к базе данных → большее время ожидания → снижение производительности приложения/базы данных.

Однако у ORM есть варианты, чтобы избежать этой проблемы, в основном используя JOIN.

Ответ 10

По-моему, статья, написанная в Hibernate Pitfall: почему отношения должны быть ленивыми, прямо противоположна реальной проблеме N + 1.

Если вам нужно правильное объяснение, обратитесь к Hibernate - Глава 19: Повышение эффективности - Стратегии выбора

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

Ответ 11

Поставляемая ссылка имеет очень простой пример проблемы n + 1. Если вы примените его к Hibernate, это в основном говорит о том же. Когда вы запрашиваете объект, объект загружается, но любые ассоциации (если они не настроены иначе) будут загружены лениво. Следовательно, один запрос для корневых объектов и другой запрос для загрузки ассоциаций для каждого из них. 100 возвращенных объектов означают один начальный запрос, а затем 100 дополнительных запросов, чтобы получить ассоциацию для каждого, n + 1.

http://pramatr.com/2009/02/05/sql-n-1-selects-explained/

Ответ 12

Гораздо быстрее выдать 1 запрос, который возвращает 100 результатов, чем выдавать 100 запросов, каждый из которых возвращает результат 1.

Ответ 13

Один миллионер имеет N автомобилей. Вы хотите получить все (4) колеса.

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

Расходы:

Предположим, что индексы помещаются в ram.

1 + N разбор запросов и строгание + поиск индекса И 1 + N + (N * 4) доступ к пластине для загрузки полезной нагрузки.

Предположим, что индексы не вписываются в ram.

Дополнительные затраты в наихудшем случае 1 + N для доступа к индексу загрузки.

Резюме

Шея бутылки - доступ к пластине (около 70 раз в секунду произвольный доступ на hdd) Желающий выбор соединения также будет обращаться к пластине 1 + N + (N * 4) раз для полезной нагрузки. Поэтому, если индексы вписываются в ram - не проблема, это достаточно быстро, потому что задействованы только операторы ram.

Ответ 14

Проблема выбора N + 1 - это боль, и имеет смысл обнаруживать такие случаи в модульных тестах. Я разработал небольшую библиотеку для проверки количества запросов, выполняемых данным тестовым методом или просто произвольным блоком кода. JDBC Sniffer

Просто добавьте специальное правило JUnit к вашему тестовому классу и разместите аннотацию с ожидаемым количеством запросов в ваших методах тестирования:

@Rule
public final QueryCounter queryCounter = new QueryCounter();

@Expectation(atMost = 3)
@Test
public void testInvokingDatabase() {
    // your JDBC or JPA code
}

Ответ 15

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

Я удивлен, что это не упоминается, но это то, как я обошел эту проблему... Я делаю полу-временную таблицу идентификаторов. Я также делаю это, когда у вас есть ограничение IN ().

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

Сначала вы вставляете идентификаторы родительских объектов в виде пакета в таблицу идентификаторов. Это batch_id - это то, что мы создаем в нашем приложении и поддерживаем.

INSERT INTO temp_ids 
    (product_id, batch_id)
    (SELECT p.product_id, ? 
    FROM product p ORDER BY p.product_id
    LIMIT ? OFFSET ?);

Теперь для каждого столбца OneToMany вы просто делаете SELECT в таблице идентификаторов INNER JOIN с дочерней таблицей с WHERE batch_id= (или наоборот). Вы просто хотите удостовериться, что вы заказываете по столбцу идентификаторов, поскольку это упростит объединение столбцов результатов (в противном случае вам понадобится HashMap/Table для всего набора результатов, который может быть не так уж и плох).

Затем вы просто периодически очищаете таблицу идентификаторов.

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

Теперь количество запросов, которые вы делаете, - это количество столбцов OneToMany.

Ответ 16

Примите пример Matt Solnit, представьте, что вы определяете связь между автомобилем и колесами как LAZY, и вам нужны некоторые поля Wheels. Это означает, что после первого выбора спящий режим будет делать "Выберите * из колес, где car_id =: id" ДЛЯ КАЖДОГО автомобиля.

Это делает первый выбор и больше 1 выбирается каждым N-автомобилем, поэтому он назвал проблему n + 1.

Чтобы избежать этого, сделайте выбор ассоциации более интересным, так что hibernate загружает данные с помощью соединения.

Но обратите внимание, если много раз у вас нет доступа к связанным колесам, лучше сохранить LAZY или изменить тип выборки с помощью критериев.