解决Spring Data JPA Hibernate的N+1查询问题的性能优化最佳方法

　　最佳方法：定制@NamedEntityGraph、定制查询和定制VO，可以做到按照需要最佳查询，需要注意的地方：定制VO的字段一定要等于或小于实际查询的字段，才不会复制的时候触发N+1查询。

1 问题复现

1.1 项目结构

 1.2 entity

package com.xkzhangsan.jpa.entity;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Entity
@Getter
@Setter
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @OneToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
    @JoinColumn(name = "user_detail_id")
    private UserDetail userDetail;
}

package com.xkzhangsan.jpa.entity;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Entity
@Getter
@Setter
@Table(name = "user_detail")
public class UserDetail {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String address;
}

1.3 repository

package com.xkzhangsan.jpa.repository;

import com.xkzhangsan.jpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Integer> {
}

1.4 service

package com.xkzhangsan.jpa.service;

import com.xkzhangsan.jpa.entity.User;
import com.xkzhangsan.jpa.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;

    public List<User> findAll(){
        return userRepository.findAll();
    }

}

1.5 controller

package com.xkzhangsan.jpa.controller;

import com.xkzhangsan.jpa.entity.User;
import com.xkzhangsan.jpa.service.UserService;
import com.xkzhangsan.jpa.vo.UserDetailVO;
import com.xkzhangsan.jpa.vo.UserVO;
import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.stream.Collectors;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping(value = "/")
    public List<UserVO> getPersons() {
        ModelMapper modelMapper = new ModelMapper();
        List<User> userList = userService.findAll();
        if (!CollectionUtils.isEmpty(userList)) {
            return userList.stream().map(user -> {
                UserVO userVO = modelMapper.map(user, UserVO.class);
                UserDetailVO userDetailVO = modelMapper.map(user.getUserDetail(), UserDetailVO.class);
                userVO.setUserDetailVO(userDetailVO);
                return userVO;
            }).collect(Collectors.toList());
        }
        return null;
    }
}

1.6 测试

 

 

查询1次，实际查询了4次，这里的N指的是集合的数量3，所以 N+1，就是3+1=4

1.7 问题原因

User关联对象懒加载导致

 2 最佳解决方法

　　定制@NamedEntityGraph、定制查询和定制VO，可以做到按照需要最佳查询。

2.1 定制@NamedEntityGraph

package com.xkzhangsan.jpa.entity;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Entity
@Getter
@Setter
@Table(name = "user")
@NamedEntityGraph(name = "user.userDetail", attributeNodes = {
        @NamedAttributeNode(value = "userDetail")
})
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @OneToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
    @JoinColumn(name = "user_detail_id")
    private UserDetail userDetail;
}

从代码种可以看出增加注解，使用user.userDetail会联表查询UserDetail

@NamedEntityGraph(name = "user.userDetail", attributeNodes = {
        @NamedAttributeNode(value = "userDetail")
})

2.1.2 定制查询

package com.xkzhangsan.jpa.repository;

import com.xkzhangsan.jpa.entity.User;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface UserRepository extends JpaRepository<User, Integer> {

    @Override
    @EntityGraph(value = "user.userDetail", type = EntityGraph.EntityGraphType.FETCH)
    List<User> findAll();
}

从代码种可以看出，重写了findAll，使用了user.userDetail

2.3 定制VO（可选）

根据需要返回的字段定义一个VO，如果没有需要则不需要定制，比如当前实例种就不需要。

测试结果如下，只查询了一次，但联表查询了，left outer join user_detail

select user0_.id as id1_0_0_, userdetail1_.id as id1_1_1_, user0_.name as name2_0_0_, user0_.user_detail_id as user_det3_0_0_, userdetail1_.address as address2_1_1_ from user user0_ left outer join user_detail userdetail1_ on user0_.user_detail_id=userdetail1_.id

2.3.1 需要定制VO的实例

比如，只想返回用户id和name，可以定制一个UserSimpleVO，如下：

package com.xkzhangsan.jpa.vo;

import lombok.Getter;
import lombok.Setter;


@Getter
@Setter
public class UserSimpleVO {
    private Long id;

    private String name;
}

因为不包括UserDetailVO userDetailVO，所以复制属性的时候不会触发更多的查询。

需要注意的地方：定制VO的字段一定要等于或小于实际查询的字段，才不会复制的时候触发N+1查询。

还有一种方式不用定制VO，通过设置ModelMapper跳过不需要的字段，但这样有2个问题

（1）需要设置ModelMapper，比较麻烦

（2）既然不需要一些字段，定制VO是最有效的方法，这样符合迪米特法则，比如不能展示的敏感字段，如果查询或处理过程中没有处理好，可能导致误返回，最好定制VO，在VO删除这个字段。

 

3 使用EAGER加载方式

既然是因为LAZY导致的，改成EAGER是否可以解决问题？经过验证是不可行的，仍然会查询多次，比如：

package com.xkzhangsan.jpa.entity;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Entity
@Getter
@Setter
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @OneToOne(cascade = CascadeType.DETACH, fetch = FetchType.EAGER)
    @JoinColumn(name = "user_detail_id")
    private UserDetail userDetail;
}

 发现仍然会执行多个sql

Hibernate: select user0_.id as id1_0_, user0_.name as name2_0_, user0_.user_detail_id as user_det3_0_ from user user0_
Hibernate: select userdetail0_.id as id1_1_0_, userdetail0_.address as address2_1_0_ from user_detail userdetail0_ where userdetail0_.id=?
Hibernate: select userdetail0_.id as id1_1_0_, userdetail0_.address as address2_1_0_ from user_detail userdetail0_ where userdetail0_.id=?
Hibernate: select userdetail0_.id as id1_1_0_, userdetail0_.address as address2_1_0_ from user_detail userdetail0_ where userdetail0_.id=?