JPA @CollectionTable查询过慢优化及索引合理性咨询

public class MunicipalityEntity {
@Id
@Column(name = "MUNICIPALITY_CODE", nullable = false)
private String code;

@Column(nullable = false)
private String name;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "PROVINCE_CODE")
private ProvinceEntity province;

@ElementCollection
@CollectionTable(name = "GEO_MUNICIPALITY_POSTAL_CODE",
        joinColumns = @JoinColumn(name = "MUNICIPALITY_CODE"),
indexes = {
        @Index(name = "idx_municipalityentity_postal_code", columnList = "MUNICIPALITY_CODE")
})
@Column(name = "POSTAL_CODE")
private List<String> postalCodeList;

}

## ProvinceEntity
```java
@Entity
@Table(name = "GEO_PROVINCE")
...
public class ProvinceEntity {
    @Id
    @Column(nullable = false)
    private String code;
...
}

API需要获取指定省份编码下的所有市镇（包含对应全部邮政编码），因此编写了如下Repository类：

@Repository
public interface MunicipalityJpaRepository extends JpaRepository<MunicipalityEntity, String> {
    List<MunicipalityEntity> findAllByProvinceCode(String province);

    @Query("select m from MunicipalityEntity m left join m.postalCodeList postalCodeList where m.province.code = ?1")
    List<MunicipalityEntity> findByCode(String code);
}

上述两种方法功能一致，但调用getPostalCodeList()时查询极慢，原因是会为每个查询到的市镇单独执行一次查询以获取邮政编码，出现N+1查询问题。尝试JPA原生方法和自定义查询两种方式，结果一致。期望提升查询性能，同时咨询：由于总是通过编码查询市镇，当前设置的索引是否合理？

解决N+1查询问题 #

1. 使用Fetch Join一次性加载关联集合 #

修改自定义查询，通过fetch join强制将市镇与对应的邮政编码列表一次性查询出来，避免懒加载触发的N+1查询：

@Query("select m from MunicipalityEntity m left join fetch m.postalCodeList where m.province.code = ?1")
List<MunicipalityEntity> findByProvinceCodeWithPostalCodes(String provinceCode);

Fetch Join会将主实体和关联集合的数据合并到同一条SQL查询中，JPA会自动处理返回结果中的重复主实体记录，确保每个市镇只返回一次。

2. 用EntityGraph按需指定关联加载 #

通过@EntityGraph注解可以灵活指定查询时需要加载的关联属性，适合不同查询场景的按需加载需求：

@EntityGraph(attributePaths = {"postalCodeList"})
@Query("select m from MunicipalityEntity m where m.province.code = ?1")
List<MunicipalityEntity> findByProvinceCodeWithPostalCodes(String provinceCode);

也可以直接结合Spring Data JPA的派生查询使用：

@EntityGraph(attributePaths = {"postalCodeList"})
List<MunicipalityEntity> findAllByProvinceCode(String provinceCode);

3. 修改集合加载策略（谨慎使用） #

如果业务中几乎每次获取MunicipalityEntity都需要用到postalCodeList，可以将集合的加载策略改为EAGER：

@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "GEO_MUNICIPALITY_POSTAL_CODE",
        joinColumns = @JoinColumn(name = "MUNICIPALITY_CODE"),
indexes = {
        @Index(name = "idx_municipalityentity_postal_code", columnList = "MUNICIPALITY_CODE")
})
@Column(name = "POSTAL_CODE")
private List<String> postalCodeList;

注意：这种方式会导致所有查询MunicipalityEntity的操作都自动加载邮政编码列表，可能在不需要该集合的场景下造成性能浪费，需根据业务场景评估后使用。

索引合理性分析与优化 #

当前索引的合理性 #

1. GEO_MUNICIPALITY表的PROVINCE_CODE索引：合理。因为核心查询是通过省份编码获取市镇，该索引能大幅加速WHERE条件的过滤效率，减少数据库扫描的数据量。
2. GEO_MUNICIPALITY_POSTAL_CODE表的MUNICIPALITY_CODE索引：合理。该表通过MUNICIPALITY_CODE与主表关联，无论是懒加载还是Fetch Join查询，这个索引都能加速关联数据的查找。

额外优化建议 #

• MUNICIPALITY_CODE作为主键，数据库会自动为主键创建唯一索引，无需额外添加。
• 如果后续出现通过邮政编码反查市镇的需求，可以在GEO_MUNICIPALITY_POSTAL_CODE表的POSTAL_CODE字段添加索引。

内容的提问来源于stack exchange，提问作者Davide C