JPA + 弹簧启动 - 当<field>数据库很大时查找非常慢(20M行表)

Question

我需要您对JPA spring数据的关联。我有数据库使用MySQL与20GB的数据(20米行)。当我执行findByID(字符串id) -(不是唯一标识符)。需要10多分钟……

性能问题可能是什么??

我的对象实体:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long rootId;
private String address;

private String check_in_time;
private String check_out_time;
@OneToMany(mappedBy = "data" , fetch = FetchType.LAZY)
private Set<DescriptionStruct> description_struct;
private String id;
@ElementCollection(fetch = FetchType.LAZY)
private Set<String> images;
private String kind;
private double latitude;
private double longitude;
private String name;
private String phone;
@Embedded
private Star_certificate star_certificate;
private String postal_code;

@AttributeOverrides({ @AttributeOverride(name = "id", column = @Column(name = "R_ID")),
@AttributeOverride(name = "name", column = @Column(name = "R_NAME")) })
@Embedded
private Region region;
private int star_rating;
private String email;
private int semantic_version;
@ElementCollection(fetch = FetchType.LAZY)
private Set<String> serp_filters;
private boolean is_closed;
@Embedded
private MetapolicyStruct metapolicy_struct;
private String metapolicy_extra_info;
@Embedded
private Facts facts;
@ElementCollection(fetch = FetchType.LAZY)
private Set<String> payment_methods;
private String hotel_chain;
@OneToMany(mappedBy = "data", fetch = FetchType.LAZY)
private Set<AmenityGroup> amenity_groups;
@OneToMany(mappedBy = "data", fetch = FetchType.LAZY)
private Set<RoomGroup> room_groups;
@OneToMany(mappedBy = "data", fetch = FetchType.LAZY)
private Set<PolicyStruct> policy_struct;

我的功能:

@ autowired

public regionSearchData returnFullData(regionSearchResponseRH response) {

regionSearchData data = new regionSearchData();

response.getData().getHotels().forEach(H -> {
Data hotelD = dataRepository.findById(H.getId());
if (hotelD != null) {
RatesSearch R = H.getRates().get(0);
R.setAddress(hotelD.getAddress());
R.setImages(hotelD.getImages());
R.setStar_certificate(hotelD.getStar_certificate());
R.setStar_rating(hotelD.getStar_rating());
R.setName(hotelD.getName());

}

});

data.setTotal_hotels(response.getData().getTotal_hotels());
data.setHotels(response.getData().getHotels()); 
return data;



}

Hibernate统计信息:

16893585 nanoseconds spent preparing 42 JDBC statements;
347535215661 nanoseconds spent executing 42 JDBC statements;
656641754926 nanoseconds spent executing 82 JDBC statements;

如果我删除findBy，它需要10秒…

谢谢,艾丹•

Answer 1

你可以尝试的事情肯定不止一件。您的代码花费超过十分钟的时间可能是因为它通过网络对DB进行了多次往返。请记住，不仅数据库搜索需要花费更多的时间，而且从应用程序到数据库的网络往返也需要花费更多的时间，我怀疑这里就是这种情况。

您可以做的第一件事取决于您的用例是最小化到DB的往返，并且在for循环中调用findById()是最糟糕的事情。以下是我的建议

1. 为您正在搜索的字段添加索引
2. 避免在for循环中调用findById()，您可以使用其他存储库方法或您可以编写findByIdsIn(id列表)，这取决于此for循环的大小-您可以一次传递所有id或使用批处理方法，例如一次获取20-30个实体并执行业务逻辑。
3. 你有很多表连接到你的实体使用@oneToMany或@ElementCollection，你需要检查hibernate是否正在执行N+1查询，如果是，先解决这个问题。