hibernate - FetchMode Join与SubSelect

Question

我有两个表Employee和Department以下是它们的实体类

Department.java
@Entity
@Table(name = "DEPARTMENT")
public class Department {
    @Id
    @Column(name = "DEPARTMENT_ID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer departmentId;
    @Column(name = "DEPARTMENT_NAME")
    private String departmentName;
    @Column(name = "LOCATION")
    private String location;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "department", orphanRemoval = true)
    @Fetch(FetchMode.SUBSELECT)
    //@Fetch(FetchMode.JOIN)
    private List<Employee> employees = new ArrayList<>();
}


Employee.java
@Entity
@Table(name = "EMPLOYEE")
public class Employee {
    @Id
    @SequenceGenerator(name = "emp_seq", sequenceName = "seq_employee")
    @GeneratedValue(generator = "emp_seq")
    @Column(name = "EMPLOYEE_ID")
    private Integer employeeId;
    @Column(name = "EMPLOYEE_NAME")
    private String employeeName;

    @ManyToOne
    @JoinColumn(name = "DEPARTMENT_ID")
    private Department department;
}

以下是我执行 em.find(Department.class, 1);时触发的查询

-获取模式= fetchmode.join

    SELECT department0_.DEPARTMENT_ID AS DEPARTMENT_ID1_0_0_,
      department0_.DEPARTMENT_NAME    AS DEPARTMENT_NAME2_0_0_,
      department0_.LOCATION           AS LOCATION3_0_0_,
      employees1_.DEPARTMENT_ID       AS DEPARTMENT_ID3_1_1_,
      employees1_.EMPLOYEE_ID         AS EMPLOYEE_ID1_1_1_,
      employees1_.EMPLOYEE_ID         AS EMPLOYEE_ID1_1_2_,
      employees1_.DEPARTMENT_ID       AS DEPARTMENT_ID3_1_2_,
      employees1_.EMPLOYEE_NAME       AS EMPLOYEE_NAME2_1_2_
    FROM DEPARTMENT department0_
    LEFT OUTER JOIN EMPLOYEE employees1_
    ON department0_.DEPARTMENT_ID   =employees1_.DEPARTMENT_ID
    WHERE department0_.DEPARTMENT_ID=?

-获取模式= fetchmode.subselect

    SELECT department0_.DEPARTMENT_ID AS DEPARTMENT_ID1_0_0_,
      department0_.DEPARTMENT_NAME    AS DEPARTMENT_NAME2_0_0_,
      department0_.LOCATION           AS LOCATION3_0_0_
    FROM DEPARTMENT department0_
    WHERE department0_.DEPARTMENT_ID=?

    SELECT employees0_.DEPARTMENT_ID AS DEPARTMENT_ID3_1_0_,
      employees0_.EMPLOYEE_ID        AS EMPLOYEE_ID1_1_0_,
      employees0_.EMPLOYEE_ID        AS EMPLOYEE_ID1_1_1_,
      employees0_.DEPARTMENT_ID      AS DEPARTMENT_ID3_1_1_,
      employees0_.EMPLOYEE_NAME      AS EMPLOYEE_NAME2_1_1_
    FROM EMPLOYEE employees0_
    WHERE employees0_.DEPARTMENT_ID=?

我只是想知道我们应该选择 FetchMode.JOIN还是 FetchMode.SUBSELECT？在哪种情况下我们应该选择哪一个？

Answer 1

Marmite引用的SUBQUERY策略与FetchMode.SELECT有关，而不与SUBSELECT有关。

您发布的有关 fetchmode.subselect 的控制台输出很奇怪，因为这不是应该的工作方式。

FetchMode.SUBSELECT

use a subselect query to load the additional collections

休眠 docs:

If one lazy collection or single-valued proxy has to be fetched, Hibernate will load all of them, re-running the original query in a subselect. This works in the same way as batch-fetching but without the piecemeal loading.

FetchMode.SUBSELECT应该看起来像这样:

SELECT <employees columns>
FROM EMPLOYEE employees0_
WHERE employees0_.DEPARTMENT_ID IN
(SELECT department0_.DEPARTMENT_ID FROM DEPARTMENT department0_)

您可以看到第二个查询会将所有属于某个部门的员工(即employee.department_id不为null)存储到 内存中，这是否不是您在第一个查询中检索到的部门并不重要。
因此，如果雇员表很大，则可能是一个主要问题，因为它可能是accidentially loading a whole database into memory。

但是，FetchMode.SUBSELECT显着减少了查询数量，因为与FecthMode.SELECT的N + 1个查询相比，只进行了两个查询。

您可能会认为FetchMode.JOIN进行的查询更少，只有1条，那么为什么要完全使用SUBSELECT？好吧，这是事实，但要以重复数据和增加响应为代价。

如果必须使用JOIN提取单值代理，则查询可以检索:

+---------------+---------+-----------+
| DEPARTMENT_ID | BOSS_ID | BOSS_NAME |
+---------------+---------+-----------+
|             1 |       1 | GABRIEL   |
|             2 |       1 | GABRIEL   |
|             3 |       2 | ALEJANDRO |
+---------------+---------+-----------+

如果老板领导多个部门，并且其成本具有可比性，则该老板的员工数据将被复制。

如果必须使用JOIN提取惰性集合，则查询可以检索:

+---------------+---------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_ID | EMPLOYEE_ID |
+---------------+---------------+-------------+
|             1 | Sales         | GABRIEL     |
|             1 | Sales         | ALEJANDRO   |
|             2 | RRHH          | DANILO      |
+---------------+---------------+-------------+

如果部门数据包含多个员工(自然情况)，则该部门数据是重复的。
我们不仅要付出带宽成本，而且还会得到重复的duplicated Department objects，并且必须使用SET或DISTINCT_ROOT_ENTITY进行重复数据删除。

但是，在许多情况下，诸如延迟Markus Winand says这样的情况下，重复数据具有较低的延迟是一个很好的权衡。

An SQL join is still more efficient than the nested selects approach—even though it performs the same index lookups—because it avoids a lot of network communication. It is even faster if the total amount of transferred data is bigger because of the duplication of employee attributes for each sale. That is because of the two dimensions of performance: response time and throughput; in computer networks we call them latency and bandwidth. Bandwidth has only a minor impact on the response time but latencies have a huge impact. That means that the number of database round trips is more important for the response time than the amount of data transferred.

因此，有关使用SUBSELECT的主要问题是hard to control，可能正在将整个实体图加载到内存中。
使用批量获取，您可以在单独的查询中以SUBSELECT的方式获取关联的实体(这样您就不会出现重复项)，最重要的是，您只逐步查询相关的实体(这样就不会承受潜在加载巨大图的麻烦)，因为IN子查询由外部查询检索的ID过滤)。

Hibernate: 
    select ...
    from mkyong.stock stock0_

Hibernate: 
    select ...
    from mkyong.stock_daily_record stockdaily0_ 
    where
        stockdaily0_.STOCK_ID in (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )

(这可能是一个有趣的测试，是否具有很高的批处理大小的批处理将像SUBSELECT一样工作，但不会加载整个表)

一些帖子显示了不同的获取策略和SQL日志(非常重要):

Hibernate – fetching strategies examples

Hibernate FetchMode explained by example

Investigating Hibernate fetch strategies – A tutorial

概要:

JOIN:避免了主要的N + 1查询问题，但它可以检索重复的数据。

SUBSELECT:也避免N + 1并且不重复数据，但是它将关联类型的所有实体加载到内存中。

这些表是使用ascii-tables构建的。