Java17+Spring Boot实现带收藏标识的学生列表高效查询

背景与需求 #

• 系统基于Java 17、Spring Boot、MySQL 8.0.35（RDS），包含Student和Recruiter实体
• 招聘者可收藏学生、查看收藏列表；查询所有学生接口需携带招聘者Token鉴权
• 查询所有学生时，返回的StudentResponse JSON需包含favorited布尔字段，标识该学生是否被当前请求的招聘者收藏

现有性能问题 #

此前通过Java逻辑实现该功能（比如先查所有学生，再逐个校验是否被当前招聘者收藏），导致接口返回JSON耗时长达55秒，性能严重不足。

无效的SQL尝试 #

尝试使用原生SQL查询但未生效，原代码如下：

@Query(value = "SELECT DISTINCT s.id," +
            " s.age," +
            " s.gc_trail," +
            " s.biography," +
            " s.profile_picture," +
            " IF(rs.recruiter_id IS NULL, false, true) AS favorited" +
            " FROM student AS s" +
            " LEFT OUTER JOIN recruiter_student AS rs" +
            " ON s.id = rs.student_id;", nativeQuery = true)
public List<StudentResponseDTO> findAllStudents(@Param("recruiter_id") Long id);

原SQL问题分析：

• LEFT JOIN仅关联student_id，未加入当前招聘者recruiter_id的条件，导致会关联所有招聘者的收藏记录，DISTINCT会额外增加查询开销，且无法正确判断当前招聘者是否收藏该学生
• 未利用索引优化关联查询，进一步拖慢性能

期望返回的实体结构 #

public class StudentResponse {
    private long id;
    private int age;
    private String biography;
    private String profilePicture;
    private boolean favorited;
}

优化解决方案 #

1. 修正原生SQL查询 #

在LEFT JOIN的ON条件中加入当前招聘者ID，确保仅关联当前用户的收藏记录，无需DISTINCT：

@Query(value = "SELECT s.id," +
        " s.age," +
        " s.biography," +
        " s.profile_picture AS profilePicture," +
        " IF(rs.recruiter_id IS NOT NULL, true, false) AS favorited" +
        " FROM student AS s" +
        " LEFT OUTER JOIN recruiter_student AS rs" +
        " ON s.id = rs.student_id AND rs.recruiter_id = :recruiter_id;", 
        nativeQuery = true)
public List<StudentResponse> findAllStudentsWithFavoriteStatus(@Param("recruiter_id") Long recruiterId);

说明：

• 将recruiter_id的条件放在JOIN的ON子句中，而非WHERE子句，这样能保留所有学生记录，同时仅匹配当前招聘者的收藏关系
• 去掉DISTINCT，因为每个学生只会对应0或1条当前招聘者的收藏记录，避免不必要的去重开销
• 给profile_picture添加别名profilePicture，确保与实体属性名映射匹配

2. 添加索引优化查询性能 #

为recruiter_student表创建联合索引，加速关联查询：

CREATE INDEX idx_recruiter_student ON recruiter_student(recruiter_id, student_id);

说明：该索引能让MySQL快速定位到当前招聘者的所有收藏学生记录，大幅减少JOIN操作的时间。

3. 可选：使用JPQL替代原生SQL #

如果偏好JPQL而非原生SQL，可编写如下查询（需确保实体类的关联关系正确定义）：

@Query("SELECT new com.yourpackage.StudentResponse(s.id, s.age, s.biography, s.profilePicture, " +
        "CASE WHEN rs IS NOT NULL THEN true ELSE false END) " +
        "FROM Student s " +
        "LEFT JOIN s.recruiters rs ON rs.id = :recruiterId")
public List<StudentResponse> findAllStudentsWithFavoriteStatusJpql(@Param("recruiterId") Long recruiterId);

说明：需确保Student实体中定义了与Recruiter的多对多关联（对应recruiter_student中间表）。

内容的提问来源于stack exchange，提问作者Eduardo Fettermann