求助： JPA 使用 findAll 时执行了其他 SQL，该怎么排查

[powinds]

这是我执行 findAll 时打印的 SQL

Hibernate: select vendor0_.id as id1_40_, vendor0_.created_at as created_2_40_, vendor0_.icon as icon3_40_, vendor0_.name as name4_40_, vendor0_.total_type_count as total_ty5_40_, vendor0_.total_version_count as total_ve6_40_, vendor0_.updated_at as updated_7_40_ from nap_device_vendor vendor0_

这是不知道从哪儿执行的 SQL ，一共执行了 18 次。应该是触发了某个函数执行了这些 SQL 吧，各位大佬帮忙看看要怎么排查？

Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at
Hibernate: select types0_.vendor_id as vendor_i7_37_0_, types0_.id as id1_37_0_, types0_.id as id1_37_1_, types0_.created_at as created_2_37_1_, types0_.name as name3_37_1_, types0_.type as type4_37_1_, types0_.updated_at as updated_5_37_1_, types0_.vendor_id as vendor_i7_37_1_, types0_.version_count as version_6_37_1_ from nap_device_type types0_ where types0_.vendor_id=? order by types0_.created_at

[golangLover]

应该是因为你第一条 sql 查询出来的 entity 有外键。导致相关的 entity 也有额外的查询。尝试把第一个相关的 entity 把 fetch type 转为 lazy

[powinds]

谢谢，很有用

[lybcyd]

看起来是 N+1 问题，按照 JPA 规范使用 EntityGraph 解决。

[hay313955795]

我的上个项目也是用的 jpa.然后前一个挖坑的人用了外键,而且对应的数据贼多.导致后面查询数据的时候牵出来一大串数据,让整个页面加载数据的时候贼慢.所以我经常需要改这些代码...我对它印象很深.

[ilumer]

jpa n+1 解决方法上面已经说了

[letitbesqzr]

1. JPA 规范使用 EntityGraph
2. criteriabuilder 里使用 fetch
3. querydsl 里使用 fetchJoin

[awolf]

这些都是 hibernate 时代带过来的知识点

[nothingistrue]

对于 JPA 、Hibernate ，或者任何 DDD 模型来说，所有关联对象都是要（立刻或者延迟）全部加载进内存的，所以不能随意设计关联，要在业务上的关联和性能上的伸缩性之间做平衡。

其他备注：不要太过于在意 N+1 问题，N+1 虽然是 N+1 次查询，但是实际上也就两次 SQL 编译，对性能影响不是严重的级别，能解决掉是好，但是为了开发便利性不解决也可用。