Java 优化 Spring-Data JPA 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20120619/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Optimize Spring-Data JPA queries
提问by Thomas Junk
I am looking for possible optimizations for framework-generated queries. As far as I understand, the process is the following:
我正在为框架生成的查询寻找可能的优化。据我了解，流程如下：
you could declare your domain objects as POJOs and adding several annotations like
@Entity,@Table,@ManyToOneetc.you declare your repositories e.g. per interfaces
你可以声明你的域对象是POJO和增加几个注解像
@Entity，@Table，@ManyToOne等等。您声明您的存储库，例如每个接口
With (2) you have several options to describe your query: e.g. per Methodnames or @Query
使用 (2) 你有几个选项来描述你的查询：例如每个 Methodnames 或 @Query
If I write a query like:
如果我写一个查询，如：
@Query("select t from Order t LEFT join fetch t.orderPositions where t.id = ?1")
Page<Order> findById(Pageable pageable, String id);
a SQL-query is autogenerated, where every column of the order is resolved and subsequentially for orderpositions and depending obejcts/tables. As if I wrote:
SQL 查询是自动生成的，其中订单的每一列都被解析，然后是订单位置和依赖对象/表。好像我写道：
select * from order
So in case, that I need someInformation from severaljoined objects, a query could be quite expensive: and more interesting quite ineffective. I stumbled upon a slow query and MySQL-explain told me, that in the generatedquery the optimizer could not make use of indices, which is bad.
因此，如果我需要来自多个连接对象的一些信息，查询可能会非常昂贵：而且更有趣的是效率很低。我偶然发现了一个缓慢的查询，MySQL-explain 告诉我，在生成的查询中，优化器无法使用索引，这很糟糕。
Of course (I am aware) I have to deal with a tradeoff, that generatedSQL isn't as optimal as manuallywritten and have the advantage of writing less boilerplate code.
当然（我知道）我必须进行权衡，生成的SQL 不如手动编写的最佳，并且具有编写更少样板代码的优势。
My question is: what are good strategies to improve queries, queryexecution?
我的问题是：改进查询、查询执行的好策略是什么？
I have thought for some options by myself:
我自己想了一些选择：
1) Is it possible to define several "Entities" for different purposes, like Orderfor access to the fullcharacteristics of an order and something like FilteredOrderwith fewer columns and no resolution of Join-columns? Both would reference the same tables, but one would use all of the columns and the other only some.
1) 是否可以为不同的目的定义多个“实体”，例如Order访问订单的全部特征，以及FilteredOrder列更少且没有分辨率的东西Join-columns？两者都将引用相同的表，但一个将使用所有列，而另一个仅使用一些列。
2) Use @Query(... native="true")with a selection of all columns, which I want to use. The advantage of that would be, that I would not double my domain-objects and litter my codebase with hundreds of Filtered-Objects.
What about paging? Is using pageablein combination with @Query( ...native="true")still possible (I am afraid not).
2）@Query(... native="true")与我想要使用的所有列的选择一起使用。这样做的好处是，我不会将我的域对象加倍，也不会用数百个Filtered-Object乱扔我的代码库。分页呢？使用pageable结合@Query( ...native="true")仍然有可能（恐怕没有）。
3) Last but in my eyes "worst"/boilerplate solution: Use JDBCTemplatesand do stuff at a lower level.
3）最后但在我看来“最糟糕”/样板解决方案：JDBCTemplates在较低级别使用和做事。
Are there other options, of which I haven't thought? Thank you for any inspiration on that topic :]
还有其他选择吗，我没有想到？感谢您对该主题的任何启发：]
Update:Our current strategy is the following
更新：我们目前的策略如下
1) Where possible, I work with select newAs I have seen, this works for every Object (be it an Entityor POJO)
1）在可能的情况下，我使用select new正如我所见，这适用于每个对象（无论是实体还是POJO）
2) In combination with database viewsit is possible to take the best of SQLand ORM. For some usecases it might be of interest to have an aggregated resultset at hand. Defining this resultset as a view makes it easy from the db-perspective to watch the result with a simple select-statement. For the ORM-side this means, you could easily define an entity matching this view and you get the whole ORM-goodnesson top: Paging incl.
2) 结合数据库视图，可以充分利用SQL和ORM。对于某些用例，手头有一个聚合结果集可能会很有趣。将此结果集定义为视图可以很容易地从数据库的角度使用简单的select语句观察结果。对于 ORM 端，这意味着，您可以轻松定义与此视图匹配的实体，并且您将获得整个ORM 的优点：分页包括。
采纳答案by Jakub Kubrynski
One solution is to use DTO's:
一种解决方案是使用 DTO：
@Query("select new FilteredOrder(o.name, o.size, o.cost) from Order o where o.id = ?1")
Page<FilteredOrder> findFilteredOrderById(Pageable pageable, String id);
If you want to have entities for some reports generation maybe you should think about using nosql datastore?
如果您想为某些报告生成实体，也许您应该考虑使用 nosql 数据存储？
回答by GreyBeardedGeek
Take a look at JPA's lazy fetching strategy. It will allow you to select objects without their relations, but will fetch the relations when you reference them.
看一下 JPA 的惰性抓取策略。它将允许您选择没有关系的对象，但会在您引用它们时获取关系。
