优化Spring-Data JPA查询 [英] Optimize Spring-Data JPA queries
问题描述
我正在寻找框架生成的查询的可能优化。
据我了解，流程如下：
I am looking for possible optimizations for framework-generated queries. As far as I understand, the process is the following:
-
您可以将域对象声明为POJO和添加几个注释，如
@Entity，@Table，@ManyToOne等。
你宣布你的存储库，例如每个接口
you declare your repositories e.g. per interfaces
使用（2）您有几个选项来描述您的查询：例如每个方法名或 @Query
With (2) you have several options to describe your query: e.g. per Methodnames or @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);
自动生成SQL查询，其中订单的每一列都被解析，并且顺序地用于订单定位和依赖obejcts /表。
好像我写的：
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:
select * from order
所以如果我需要来自几个加入对象的某些信息，查询可能会非常昂贵：更有趣的是非常无效。我偶然发现了一个缓慢的查询，MySQL-explain告诉我，在生成的查询中，优化器无法使用索引，这很糟糕。
So in case, that I need some Information from several joined 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 generated query the optimizer could not make use of indices, which is bad.
当然（我知道）我必须处理权衡，生成的 SQL并不像手动那样最优编写并具有编写较少样板代码的优点。
Of course (I am aware) I have to deal with a tradeoff, that generated SQL isn't as optimal as manually written and have the advantage of writing less boilerplate code.
我的问题是：什么是改善查询，查询执行的好策略？
My question is: what are good strategies to improve queries, queryexecution?
我想过一些选择我自己：
I have thought for some options by myself:
1）是否可以为不同的目的定义几个实体，例如订单用于访问到完整特征的订单和类似 FilteredOrder 的列数更少，分辨率 Join-columns ？两者都会引用相同的表，但是一个会使用所有列而另一个只使用一些。
1) Is it possible to define several "Entities" for different purposes, like Order for access to the full characteristics of an order and something like FilteredOrder with 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.
2）使用 @Query（.. 。native =true）包含我想要使用的所有列的选择。这样做的好处是，我不会将我的域对象加倍，并且使用数百个 Filtered -Objects来丢弃我的代码库。
分页怎么样？使用 pageable 与 @Query（... native =true）组合仍然可能（恐怕不是）。
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 pageable in combination with @Query( ...native="true") still possible (I am afraid not).
3）最后但在我看来最差/样板解决方案：使用 JDBCTemplates 并做东西在较低的水平。
3) Last but in my eyes "worst"/boilerplate solution: Use JDBCTemplates and do stuff at a lower level.
还有其他选择，我还没想过？
感谢您对该主题的任何启发：]
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）在可能的情况下，我使用选择新的
因为我有看到，这适用于每个对象（无论是实体还是 POJO ）
1) Where possible, I work with select new As I have seen, this works for every Object (be it an Entity or POJO)
2）结合数据库视图，可以充分利用 SQL 和 ORM 。对于某些用例，可能有兴趣拥有一个聚合结果集。将此结果集定义为视图使得从db-perspective可以轻松地使用简单的 select -statement来查看结果。
对于ORM方面，这意味着，您可以轻松定义与此视图匹配的实体，并在顶部获得整个 ORM-goodness ：分页包括
2) In combination with database views it is possible to take the best of SQL and 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-goodness on top: Paging incl.
推荐答案
一种解决方案是使用DTO：
One solution is to use DTO's:
@Query("select new FilteredOrder(o.name, o.size, o.cost) from Order o where o.id = ?1")
Page<FilteredOrder> findFilteredOrderById(Pageable pageable, String id);
如果您想要生成某些报告的实体，您应该考虑使用nosql数据存储吗？
If you want to have entities for some reports generation maybe you should think about using nosql datastore?
这篇关于优化Spring-Data JPA查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
