Вопрос: Какова проблема с запросом SELECT N + 1?


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

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


1263


источник


Ответы:


Допустим, у вас есть коллекция 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.


740



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 - сложность запросов, и вы можете использовать ленивую загрузку, когда дочерние результирующие наборы загружаются только при первом запросе.


98



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

***** 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 - выбор 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 выберите оператор для продукта
  • N выбор заявлений для поставщика

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


56



Я не могу прямо комментировать другие ответы, потому что у меня недостаточно репутации. Но стоит отметить, что проблема по существу возникает только потому, что, исторически, много 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 с ними, повторите попытку в последних версиях. Вы, вероятно, будете приятно удивлены.


32



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

for p in person:
    print p.car.colour

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

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

Шаг 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: Render

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

Видеть эта веб-страница для реализации fanfolding для python.


25



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

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

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


16



Here's a good description of the problem - http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=why-lazy

Now that you understand the problem it can typically be avoided by doing a join fetch in your query. This basically forces the fetch of the lazy loaded object so the data is retrieved in one query instead of n+1 queries. Hope this helps.


14



In my opinion the article written in Hibernate Pitfall: Why Relationships Should Be Lazy is exactly opposite of real N+1 issue is.

If you need correct explanation please refer Hibernate - Chapter 19: Improving Performance - Fetching Strategies

Select fetching (the default) is extremely vulnerable to N+1 selects problems, so we might want to enable join fetching


12



Check Ayende post on the topic: Combating the Select N + 1 Problem In NHibernate

Basically, when using an ORM like NHibernate or EntityFramework, if you have a one-to-many (master-detail) relationship, and want to list all the details per each master record, you have to make N + 1 query calls to the database, "N" being the number of master records: 1 query to get all the master records, and N queries, one per master record, to get all the details per master record.

More database query calls --> more latency time --> decreased application/database performance.

However, ORM's have options to avoid this problem, mainly using "joins".


12



The supplied link has a very simply example of the n + 1 problem. If you apply it to Hibernate it's basically talking about the same thing. When you query for an object, the entity is loaded but any associations (unless configured otherwise) will be lazy loaded. Hence one query for the root objects and another query to load the associations for each of these. 100 objects returned means one initial query and then 100 additional queries to get the association for each, n + 1.

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


9



It is much faster to issue 1 query which returns 100 results than to issue 100 queries which each return 1 result.


6



The N+1 query issue happens when you forget to fetch an association and then you need to access it:

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());
}

Which generates the following SQL statements:

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'

First, Hibernate executes the JPQL query, and a list of PostComment entities is fetched.

Then, for each PostComment, the associated post property is used to generate a log message containing the Post title.

Because the post association is not initialized, Hibernate must fetch the Post entity with a secondary query, and for N PostComment entities, N more queries are going to be executed (hence the N+1 query problem).

First, you need proper SQL logging and monitoring so that you can spot this issue.

Second, this kind of issue is better to be caught by integration tests. You can use an automatic JUnit assert to validate the expected count of generated SQL statements. The db-unit project already provides this functionality, and it's open source.

When you identified the N+1 query issue, you need to use a JOIN FETCH so that child associations are fetched in one query, instead of N. If you need to fetch multiple child associations, it's better to fetch one collection in the initial query and the second one with a secondary SQL query.


6