JPA与Hibernate查询性能异常求助：SQL在Workbench秒级执行

问题背景 #

在应用性能测试中，遇到Hibernate执行查询获取约18000条记录耗时约90秒，但相同的SQL语句在MySQL Workbench中执行仅需1秒。相关代码信息如下：

实体类 #

@Entity @Table(name = "merged_bill_items_data")
public class MergedBillItemData{

    @Id
    @AccessType("property")
    @Column(unique = true, nullable = false)
    private String id;

    @Temporal(TemporalType.DATE)
    @Column(name = "start_date", nullable = false)
    @Type(type = "com.iblogix.analytic.type.LocalDateDBType")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd") 
    private LocalDate startDate;

    @Temporal(TemporalType.DATE)
    @Column(name = "end_date", nullable = false)
    @Type(type = "com.iblogix.analytic.type.LocalDateDBType")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd") 
    private LocalDate endDate;

    @Temporal(TemporalType.DATE)
    @Column(name = "statement_date", nullable = false)
    @Type(type = "com.iblogix.analytic.type.LocalDateDBType")
    private LocalDate statementDate;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "analysis_id", nullable = false)
    private Analysis analysis;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "bill_item_def_id", nullable = false)
    private BillItemDefinition billItemDefinition;
    // 省略其他字段
}

Repository类 #

public interface MergedBillItemsDataRepository
extends
GenericJpaRepository<MergedBillItemData, String>,
JpaSpecificationExecutor {
}

原始命名查询 #

@NamedQuery(name = "MergedBillItemData.findByUserAndEnergyTypeAndDisplayMonthRangeByAdjType", 
            query = "Select mbid From BuildingUsers bu, MergedBillItemData mbid " +
                    "where bu.user.id=:userId and bu.building.id=mbid.analysis.building.id " +
                    "and mbid.energyType.id =:energyTypeId and mbid.adjustmentType =:adjustmentType " +
                    "and mbid.displayMonth >= :startDate and mbid.displayMonth <= :endDate " +
                    "order by mbid.displayMonth asc"),

最初怀疑是两个EAGER加载的关联实体导致N+1查询，因此尝试了以下优化方案，但均无效果：

尝试过的优化方案 #

方案1：改用DTO投影避免EAGER加载 #

自定义DTOMergedBillItemDataWrapper，通过构造函数投影仅查询所需字段，避免加载关联实体，但性能无提升：

@NamedQuery(name = "MergedBillItemData.getBillItemsByUserIdAndEnergyTypeAndDisplayMonth", 
            query = "select new com.iblogix.analytic.dto.MergedBillItemDataWrapper(" +
                    "mbid.id, mbid.startDate,mbid.endDate, mbid.statementDate, " +
                    "mbid.analysis.id as analysisId,mbid.analysis.building.id as buildingId, " +
                    "mbid.billItemDefinition.id as billItemDefinitionId, " +
                    "mbid.billItemDefinition.ffBillItemName,mbid.billItemDefinition.utilityBillItemName, " +
                    "mbid.billItemDefinition.ffBillItemCategory,mbid.energyType.id as energyTypeId, " +
                    "mbid.meterReadDatesAligned, mbid.cost,mbid.statementDatesAligned," +
                    "mbid.numberOfStatements,mbid.thirdPartyBilled,mbid.itemUsageValue," +
                    "mbid.unitId,mbid.unitPrice,mbid.readingType,mbid.displayMonth, mbid.adjustmentType) " +
                    "From MergedBillItemData mbid , BuildingUsers bu  " +
                    "where bu.user.id=:userId and bu.building.id=mbid.analysis.building.id " +
                    "and mbid.energyType.id =:energyTypeId and mbid.adjustmentType =:adjustmentType " +
                    "and mbid.displayMonth >= :startDate and mbid.displayMonth <= :endDate " +
                    "order by mbid.displayMonth asc"),

方案2：改用原生查询+结果集映射 #

编写原生SQL并通过@SqlResultSetMapping映射结果，性能仍无提升：

@SqlResultSetMappings({
    @SqlResultSetMapping(name = "MBIDMapping", 
                         // 省略具体映射配置
                        )
})
@NamedNativeQueries({
@NamedNativeQuery(name = "MergedBillItemData.getBillItemsByUserIdAndEnergyTypeAndDisplayMonthAndAdjustmentType", 
                 query = "select mbid.id, mbid.start_date as startDate, mbid.end_date as endDate, " +
                         "mbid.statement_date as statementDate, mbid.analysis_id as analysisId, " +
                         "b.id as buildingId, mbid.bill_item_def_id as billItemDefinitionId," +
                         "bd.ff_util_bill_item_name as ffBillItemName, bd.util_bill_item_name as utilityBillItemName," +
                         "bd.ff_util_bill_item_category as ffBillItemCategory,mbid.energy_type_id as energyTypeId, " +
                         "mbid.are_meter_read_dates_aligned as meterReadDatesAligned, mbid.cost as cost," +
                         "mbid.are_statement_dates_aligned as statementDatesAligned, mbid.number_of_statements as numberOfStatements, " +
                         "mbid.third_party_billed as thirdPartyBilled,mbid.item_usage_value as itemUsageValue, " +
                         "mbid.unit_id as unitId, mbid.unit_price as unitPrice, mbid.reading_type as readingType, " +
                         "mbid.display_month as displayMonth, mbid.adjustment_type as adjustmentType " +
                         "from building_users bu " +
                         "INNER JOIN user u ON bu.user_id=u.id " +
                         "INNER JOIN building b ON bu.building_id=b.id " +
                         "INNER JOIN analysis a ON a.building_id=b.id " +
                         "INNER JOIN merged_bill_items_data mbid ON mbid.analysis_id=a.analysis_id " +
                         "INNER JOIN energy_type et ON mbid.energy_type_id=et.id " +
                         "INNER JOIN bill_item_defs bd ON mbid.bill_item_def_id= bd.id " +
                         "where bu.user_id=:userId " +
                         "and mbid.energy_type_id =:energyTypeId " +
                         "and mbid.display_month >= :startDate " +
                         "and mbid.display_month <= :endDate " +
                         "and mbid.adjustment_type =:adjustmentType " +
                         "order by mbid.display_month asc ", 
                 resultSetMapping = "MBIDMapping"),
})

核心疑问 #

为何相同SQL在Workbench中执行极快，但通过Hibernate执行耗时长达90秒？

排查方向 #

• 网络与连接池：检查应用服务器与数据库之间的网络延迟、带宽，对比Workbench所在机器与应用服务器的网络环境；同时确认数据库连接池配置（如最大连接数、等待超时）是否合理，是否存在连接等待。
• Hibernate结果映射开销：开启Hibernate统计日志（hibernate.generate_statistics=true），查看结果集处理的耗时；检查自定义类型LocalDateDBType的转换逻辑是否存在性能瓶颈，18000条记录的对象映射可能累积大量开销。
• 参数绑定与执行计划：开启Hibernate SQL日志，复制带参数的SQL到Workbench执行，确认是否仍快速；检查参数类型是否与数据库字段匹配（如日期参数是否被转成字符串导致索引失效），对比Hibernate执行时的数据库执行计划与Workbench的差异。
• 事务与隐式加载：确认查询是否在大事务中执行，事务上下文可能带来额外开销；检查是否存在隐式关联加载（即使使用DTO投影，Hibernate是否仍触发了不必要的查询），查看SQL日志是否有额外的N+1查询。
• JVM内存与GC：检查JVM堆内存配置是否足够，查看GC日志是否存在频繁GC或Full GC导致的停顿，18000条对象加载可能需要足够的内存空间，GC停顿会拉长整体耗时。

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