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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 973 天前的主题，其中的信息可能已经有所发展或是发生改变。

这是我执行 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

8 条回复 • 2022-03-28 09:19:48 +08:00

golangLover

2022-03-26 13:05:26 +08:00 via Android

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

powinds

2022-03-26 13:21:36 +08:00

谢谢，很有用

lybcyd

2022-03-26 14:10:32 +08:00

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

hay313955795

2022-03-26 14:12:22 +08:00

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

ilumer

2022-03-26 14:19:30 +08:00

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

letitbesqzr

2022-03-26 16:28:51 +08:00

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

awolf

2022-03-26 20:32:47 +08:00

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

nothingistrue

2022-03-28 09:19:48 +08:00

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

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