优化 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

如果我写一个查询:

@Query("select t from Order t LEFT join fetch t.orderPositions where t.id = ?1")
Page<Order> findById(Pageable pageable, String id);

一个 SQL 查询是自动生成的，其中订单的每一列都被解析，随后用于订单位置和依赖对象/表.就像我写的:

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) 是否可以为不同的目的定义多个实体"，例如 Order 用于访问订单的 full 特征以及诸如 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 :]

更新:我们目前的策略如下

1) 在可能的情况下，我使用 select new正如我看到，这有效对于每个对象(无论是 Entity 还是 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 的角度使用简单的 select 语句观察结果.对于 ORM 端，这意味着，您可以轻松地定义一个与此视图匹配的实体，并在顶部获得整个 ORM-goodness:Paging incl.

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屋！