为什么这会导致非延迟获取和N + 1个select语句?

问题描述

我希望仅获得一个sql查询，但遇到N + 1 select陷阱.我真的不明白为什么.这是详细的问题:

I expect to get only one sql query, but I run into the N+1 select trap. I don't really understand why. Here is is the problem in detail:

我有一个实体"PlayerRef":

I have an entity "PlayerRef":

@Entity
@Table(name = "player_ref")
public class PlayerRef {

  //constructor etc...

  @OptimisticLock(excluded = true)
  @OneToMany(fetch = FetchType.LAZY, mappedBy = "playerRef")
  public Set<Player> getPlayers() {
    return players;
  }

}

还有一个职业玩家:

@Entity
@Table(name = "player")
public class Player {

  //constructor etc...

  @OptimisticLock(excluded = true)
  @ManyToOne(optional = true, fetch = FetchType.LAZY)
    @JoinTable(name="cr_player_ref_player", 
      joinColumns = {
        @JoinColumn(name="player_id", unique = true)           
      }
      ,inverseJoinColumns = {
        @JoinColumn(name="player_ref_id")
      }     
    )
  public PlayerRef getPlayerRef() {
    return this.playerRef;
  }
}

现在，在我的程序中，我使用以下HQL查询来获取所有playerRef实体:

Now, in my program I use the following HQL query to get all playerRef entities:

Query playerRefQ = session.createQuery("select playerRef from PlayerRef playerRef ")
  .setReadOnly(true); 
playerRefQ.setParameter("sport", sport);
@SuppressWarnings("unchecked")
List<PlayerRef> allPlayerRefs = playerRefQ.list();

这将导致N + 1个Select语句:

This results in the N+1 Select statements:

1)

select
    playerref0_.id as id1_21_,
    playerref0_.... 
from
    player_ref playerref0_

N次)

select
    players0_.player_ref_id as player_r1_21_0_,
    players0_.player_id as player_i2_34_0_,
    player1_.id as id1_19_1_,
    player1_....,
    player1_1_.player_ref_id as player_r1_34_1_,
    ... 
from
    cr_player_ref_player players0_ 
inner join
    player player1_ 
        on players0_.player_id=player1_.id 
left outer join
    cr_player_ref_player player1_1_ 
        on player1_.id=player1_1_.player_id 
where
    players0_.player_ref_id=?

这是非常出乎意料的，因为我认为该集合是延迟加载的，并且每个playerRef的播放器集都应该是hibernate-proxy.

This is very unexpected, since I thought the collection is lazy loaded and the set of players of each playerRef should be a hibernate-proxy.

任何人都知道我如何才能真正只加载playerRef实体而又不加载关联的玩家?对于我的用例，我需要所有playerRef，但不需要关联的播放器.

Anyone knows how I can really only load the playerRef entities without also loading the associated players? For my use case I need all playerRefs but not the associated players.

推荐答案

这里的问题来自于事实，即您的基础数据库结构实际上是many-to-many.配对表cr_player_ref_player表示

The problem here comes from the fact, that your underlying DB structure is in fact many-to-many. There is a pairing table cr_player_ref_player, and that means, that

• 一个Player可以有多个PlayerRef
• 一个PlayerRef可以有多个Player

• one Player can have many PlayerRef
• one PlayerRef can have many Player

请参见休眠-多对多示例

因为不是这样:

public PlayerRef getPlayerRef() {
    return this.playerRef;
}

应该是:

public Set<PlayerRef> getPlayerRefs() {
    return playerRef;
}

Hibernate的困惑来自于此:@OneToMany(fetch = FetchType.LAZY, mappedBy = "playerRef")，主要是映射将在"playerRef"下找到...在这里，而不是预期的many-to-one要复杂得多，并且部分表达为many-to-many.这就是为什么我们可以看到这些奇怪的N + 1 ...

Confusion for Hibernate comes from this: @OneToMany(fetch = FetchType.LAZY, mappedBy = "playerRef"), mainly that the mapping will be found under the "playerRef"... where instead of expected many-to-one is much more complex and partially expressed many-to-many. And that's why we can see these wierd N+1...

这篇关于为什么这会导致非延迟获取和N + 1个select语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！