问题描述
我发现在检索与其他对象有很多关系的对象的多个实例时会出现性能问题。我在MySQL中使用Spring和Hibernate的JPA实现。问题是，执行JPA查询时，Hibernate不会自动加入其他表。这导致了n * r + 1个SQL查询，其中n是正在检索的对象的数量，r是关系的数量。
例如，一个人住在地址，有很多爱好，并且访问过很多国家：
@Entity
public class Person {
@Id public Integer personId;
公共字符串名称;
@ManyToOne公共地址;
@ManyToMany public Set< Hobby>爱好;
@ManyToMany public Set< Country> countriesVisited;
}
当我执行JPA查询以获取所有名为Bob的人员时， 100数据库中的Bob：
SELECT p FROM Person p WHERE p.name ='Bob'
Hibernate将其转换为301个SQL查询：
SELECT ... FROM Person WHERE name ='Bob'
SELECT ... FROM Address WHERE personId = 1
SELECT ... FROM Address WHERE personId = 2
..
SELECT ... FROM Hobby WHERE personId = 1
SELECT ... FROM Hobby WHERE personId = 2
...
SELECT ... FROM国家/地区WHERE personId = 1
SELECT ... FROM Country WHERE personId = 2
...
根据Hibernate FAQ（和），解决方案是指定LEFT JOIN或。所以现在我的查询看起来像：
SELECT p，a，h，c FROM Person p
LEFT JOIN p。解决一个LEFT OUTER JOIN p.hobbies h LEFT OUTER JOIN p.countriesVisited c
WHERE p.name ='Bob'
这可以起作用，但是如果有多个LEFT OUTER JOIN，那么Hibernate不正确地寻找一个不存在的列会出现一个错误：
无法从结果集中读取列值：personId69_2_;找不到列'personId69_2_'。
错误行为似乎可能由。不幸的是，修复不是任何发布的Hibernate JAR的一部分。我已经运行我的应用程序对快照构建，但错误行为仍然存在。我也从存储库中的最新代码构建了自己的Hibernate Core JAR，并且错误行为仍然存在。所以也许HHH-3636没有解决这个问题。
这个Hibernate性能限制令人非常沮丧。如果我查询1000个对象，则对数据库进行1000 * r + 1的SQL查询。在我的情况下，我有8个关系，所以我得到了8001个SQL查询，这导致了糟糕的性能。正式的Hibernate解决方案是为了加入所有的关系。但由于错误行为，这对于多个多对多关系来说是不可能的。所以我由于多对多的关系而留下了多对一关系和n * r + 1查询的左连接。我打算把LEFT OUTER JOIN作为一个Hibernate bug提交，但同时我的客户需要一个具有合理性能的应用程序。我目前使用批量提取（BatchSize），ehcache和自定义内存缓存的组合，但性能仍然很差（它改进了从30秒到8秒的检索5000个对象）。底线是太多的SQL查询正在触及数据库。
那么，我的问题是否可以在性能敏感的应用程序中使用Hibernate，在这些应用程序中表格有多个关系？我很想听听Hibernate如何使用地址性能。我应该手写SQL吗（这有点挫败了使用Hibernate的目的）？我应该去规范我的数据库模式以减少连接表的数量吗？如果我需要快速查询性能，我应该不使用Hibernate吗？是否有更快的速度？
请参阅我的回答，如果您阅读了链接到的所有常见问题解答：
请参阅。如果您不注意连接，最终会出现问题。
I'm seeing performance problems with retrieving multiple instances of objects that have many relationships with other objects. I'm using Spring and Hibernate's JPA implementation with MySQL. The issue is that when executing a JPA query, Hibernate does not automatically join to other tables. This results in n*r + 1 SQL queries, where n is the number of objects being retrieved and r is the number of relationships.
Example, a Person lives at an Address, has many Hobbies, and has visited many Countries:
@Entity
public class Person {
@Id public Integer personId;
public String name;
@ManyToOne public Address address;
@ManyToMany public Set<Hobby> hobbies;
@ManyToMany public Set<Country> countriesVisited;
}
When I perform a JPA query to get all Persons named Bob, and there are 100 Bobs in the database:
SELECT p FROM Person p WHERE p.name='Bob'
Hibernate translates this to 301 SQL queries:
SELECT ... FROM Person WHERE name='Bob'
SELECT ... FROM Address WHERE personId=1
SELECT ... FROM Address WHERE personId=2
...
SELECT ... FROM Hobby WHERE personId=1
SELECT ... FROM Hobby WHERE personId=2
...
SELECT ... FROM Country WHERE personId=1
SELECT ... FROM Country WHERE personId=2
...
According to the Hibernate FAQ (here and here), the solution is to specify LEFT JOIN or LEFT OUTER JOIN (for many-to-many) in the query. So now my query looks like:
SELECT p, a, h, c FROM Person p
LEFT JOIN p.address a LEFT OUTER JOIN p.hobbies h LEFT OUTER JOIN p.countriesVisited c
WHERE p.name = 'Bob'
This works, but there appears to be a bug if there's more than one LEFT OUTER JOIN in which case Hibernate is incorrectly looking for a non-existent column:
could not read column value from result set: personId69_2_; Column 'personId69_2_' not found.
The bug behavior appears to be possibly addressed by Hibernate Core bug HHH-3636. Unfortunately the fix is not part of any released Hibernate JAR. I've ran my application against the snapshot build but the bug behavior is still present. I've also built my own Hibernate Core JAR from the latest code in the repository and the bug behavior is still present. So maybe HHH-3636 doesn't address this.
This Hibernate performance limitation is very frustrating. If I query for 1000 objects then 1000*r + 1 SQL queries are made to the database. In my case I have 8 relationships so I get 8001 SQL queries, which results in horrible performance. The official Hibernate solution to this is to left join all relationships. But this isn't possible with more than one many-to-many relationships due to the bug behavior. So I'm stuck with left joins for many-to-one relationships and n*r+1 queries due to the many-to-many relationships. I plan to submit the LEFT OUTER JOIN problem as a Hibernate bug, but in the meantime my customer needs an app that has reasonable performance. I currently use a combination of batch fetch (BatchSize), ehcache and custom in-memory caching but the performance is still pretty poor (it improved retrieving 5000 objects from 30 to 8 seconds). The bottom line is that too many SQL queries are hitting the database.
So, my questions, is it possible to use Hibernate in performance sensitive applications where tables have multiple relationships with each other? I would love to hear how successful Hibernate uses address performance. Should I be hand writing SQL (which somewhat defeats the purpose of using Hibernate)? Should I de-normalize my database schema to reduce the number of joined tables? Should I not be using Hibernate if I need fast query performance? Is there something faster?
See my answer to your other question, if you read the whole of the FAQ you linked to:
See the tips on improving performance. If you are not careful with joins, you will end up with Cartesian Product problems.
这篇关于Hibernate可以用于性能敏感的应用程序吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！