"N + 1选择问题"通常被称为对象关系映射(ORM)讨论中的一个问题,我理解它必须为对象中看起来很简单的事情做出大量的数据库查询。 世界。
有没有人对这个问题有更详细的解释?
假设您有一组Car对象(数据库行),每个Car都有一个Wheel对象(也是行)的集合。换句话说,Car - > Wheel是1对多的关系。
现在,假设您需要遍历所有车辆,并为每个车辆打印出车轮列表。天真的O / R实现将执行以下操作:
然后为每个Car:
1
| SELECT * FROM Wheel WHERE CarId = ? |
换句话说,您有一个选择汽车,然后N个额外选择,其中N是汽车总数。
或者,可以获得所有轮子并在内存中执行查找:
这减少了从N + 1到2的数据库往返次数。
大多数ORM工具为您提供了几种防止N + 1选择的方法。
参考:Java Persistence with Hibernate,第13章。
1 2 3 4
| SELECT
table1.*
, table2.*
INNER JOIN table2 ON table2.SomeFkId = table1.SomeId |
这会得到一个结果集,其中table2中的子行通过返回table2中每个子行的table1结果而导致重复。 O / R映射器应根据唯一键字段区分table1实例,然后使用所有table2列填充子实例。
1 2 3
| SELECT table1.*
SELECT table2.* WHERE SomeFkId = # |
N + 1是第一个查询填充主对象的位置,第二个查询填充返回的每个唯一主对象的所有子对象。
考虑:
1 2 3 4 5 6 7 8 9 10 11 12
| 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"的单个查询可能会返回:
1 2 3 4
| Id Address Name HouseId
1 22 Valley St Dave 1
1 22 Valley St John 1
1 22 Valley St Mike 1 |
O / RM应该填充ID = 1,Address ="22 Valley St"的Home实例,然后用Dave,John和Mike的People实例填充Inhabitants数组,只需一个查询。
对上面使用的相同地址的N + 1查询将导致:
1 2
| Id Address
1 22 Valley St |
用一个单独的查询
1
| SELECT * FROM Person WHERE HouseId = 1 |
并产生一个单独的数据集
1 2 3 4
| Name HouseId
Dave 1
John 1
Mike 1 |
并且最终结果与单个查询的上述相同。
单一选择的优点是您可以预先获得所有数据,这可能是您最终想要的。 N + 1的优点是减少了查询复杂性,并且您可以使用延迟加载,其中子结果集仅在第一次请求时加载。
与产品具有一对多关系的供应商。一个供应商拥有(供应)许多产品。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| ***** 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 |
+-----+-----------+--------------------+-------+------------+ |
影响因素:
获取模式为Select Fetch(默认)
1 2 3 4 5 6 7 8 9 10
| // 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=? |
结果:
这是N + 1选择问题!
我不能直接评论其他答案,因为我没有足够的声誉。但值得注意的是,问题基本上只会产生,因为从历史上看,许多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的糟糕表现而过去一直在避免加入,那么再试一次最新版本。你可能会感到惊喜。
由于这个问题,我们离开了Django的ORM。基本上,如果你尝试做
1 2
| for p in person:
print p.car.colour |
ORM将很乐意返回所有人(通常作为Person对象的实例),但随后它将需要查询每个Person的car表。
一种简单而有效的方法是我称之为"粉丝折叠",它避免了一种荒谬的想法,即来自关系数据库的查询结果应该映射回构成查询的原始表。
第1步:广泛选择
1
| select * from people_car_colour; # this is a view or sql function |
这将返回类似的东西
1 2 3 4 5
| 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步:客观化
将结果吸收到通用对象创建器中,并在第三个项目后分割参数。这意味着"jones"对象不会多次出现。
第3步:渲染
1 2
| for p in people:
print p.car.colour # no more car queries |
有关python的fanfolding的实现,请参阅此网页。
假设您有公司和员工。公司有许多员工(即员工有一个字段COMPANY_ID)。
在一些O / R配置中,当你有一个映射的Company对象并且去访问它的Employee对象时,O / R工具会为每个员工做一个选择,如果你只是在直接SQL中做事,你可以。因此N(员工人数)加1(公司)
这就是EJB Entity Beans的初始版本的工作方式。我相信像Hibernate这样的东西已经废除了这个,但我不太大多数工具通常都包含有关其映射策略的信息。
这是对问题的一个很好的描述 - https://web.archive.org/web/20160310145416/http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=why-懒
现在您已经了解了这个问题,通常可以通过在查询中进行连接提取来避免它。这基本上强制获取延迟加载的对象,因此在一个查询中检索数据而不是n + 1个查询。希望这可以帮助。
查看关于主题的Ayende帖子:在NHibernate中解决选择N + 1问题
基本上,当使用像NHibernate或EntityFramework这样的ORM时,如果你有一对多(主 - 细节)关系,并希望列出每个主记录的所有细节,你必须对N + 1查询调用数据库,"N"是主记录的数量:1个查询获取所有主记录,N个查询(每个主记录一个)获取每个主记录的所有详细信息。
更多数据库查询调用 - >更多延迟时间 - >降低应用程序/数据库性能。
但是,ORM可以选择避免这个问题,主要是使用"连接"。
当您忘记获取关联然后需要访问它时,会发生N + 1查询问题:
1 2 3 4 5 6 7 8 9 10 11 12
| 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语句:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| 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查询。
在我看来,用Hibernate陷阱写的文章:为什么关系应该是懒惰的,与真正的N + 1问题正好相反。
如果您需要正确的解释,请参阅Hibernate - 第19章:提高性能 - 获取策略
Select fetching (the default) is
extremely vulnerable to N+1 selects
problems, so we might want to enable
join fetching
提供的链接有一个非常简单的n + 1问题示例。如果你将它应用于Hibernate,它基本上是在谈论同样的事情。查询对象时,将加载实体,但任何关联(除非另外配置)都将延迟加载。因此,一个查询根对象,另一个查询加载每个对象的关联。返回100个对象意味着一个初始查询,然后100个额外的查询以获得每个n + 1的关联。
http://pramatr.com/2009/02/05/sql-n-1-selects-explained/
一位百万富翁有N辆车。你想得到所有(4)轮子。
一(1)个查询加载所有汽车,但是对于每个(N)汽车,提交单独的查询以加载车轮。
成本:
假设索引符合ram。
1 + N查询解析和规划+索引搜索和1 + N +(N * 4)板访问以加载有效载荷。
假设索引不适合ram。
最坏情况下的额外成本1 + N板加载索引。
摘要
瓶颈是板通道(在硬盘上每秒约70次随机访问)
对于有效载荷,急切的连接选择也将访问板1 + N +(N * 4)次。
因此,如果索引适合ram - 没问题,它的速度足够快,因为只涉及ram操作。
发出1个查询返回100个结果比发出100个查询每个返回1个结果要快得多。
N + 1选择问题很痛苦,在单元测试中检测这种情况是有意义的。
我开发了一个小型库,用于验证给定测试方法或任意代码块执行的查询数量 - JDBC Sniffer
只需在测试类中添加一个特殊的JUnit规则,并在测试方法上放置具有预期查询数量的注释:
1 2 3 4 5 6 7 8
| @Rule
public final QueryCounter queryCounter = new QueryCounter();
@Expectation(atMost = 3)
@Test
public void testInvokingDatabase() {
// your JDBC or JPA code
} |
正如其他人所说的更优雅的问题是,您要么拥有OneToMany列的笛卡尔积,要么您正在进行N + 1选择。无论是可能的巨大结果集还是分别与数据库聊天。
我很惊讶这没有被提及,但这是我如何解决这个问题...我做了一个半临时的id表。当你有IN ()子句限制时,我也会这样做。
这并不适用于所有情况(可能甚至不是大多数情况)但如果你有很多子对象使得笛卡尔积会失控(即大量的OneToMany列)结果数量,它的效果特别好将是一个列的乘法)和它更多的批处理作业。
首先,将父对象ID作为批处理插入到ids表中。
这个batch_id是我们在应用程序中生成并保留的内容。
1 2 3 4 5
| INSERT INTO temp_ids
(product_id, batch_id)
(SELECT p.product_id, ?
FROM product p ORDER BY p.product_id
LIMIT ? OFFSET ?); |
现在,对于每个OneToMany列,只需在子表INNER JOIN上使用WHERE batch_id=执行SELECT(反之亦然)。您只是想确保按id列进行排序,因为它会使合并结果列更容易(否则您将需要一个HashMap / Table用于整个结果集,这可能不是那么糟糕)。
然后你只需要定期清理ids表。
如果用户为某种批量处理选择说100个左右的不同项目,这也特别有效。将100个不同的ID放在临时表中。
现在,您正在执行的查询数量取决于OneToMany列的数量。
以Matt Solnit为例,假设您将Car和Wheels之间的关联定义为LAZY并且您需要一些Wheels字段。这意味着在第一次选择之后,休眠将执行"从车轮中选择* car_id =:id"FOR FOR EACH Car。
这使得第一个选择和每个N车选择更多1,这就是为什么它被称为n + 1问题。
为避免这种情况,请将关联提取视为急切,以便hibernate通过连接加载数据。
但是注意,如果很多次你没有访问相关的车轮,最好保持LAZY或用Criteria更改提取类型。