V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
powinds
V2EX  ›  Java

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

  •  
  •   powinds · 2022-03-26 12:52:59 +08:00 · 2064 次点击
    这是一个创建于 733 天前的主题,其中的信息可能已经有所发展或是发生改变。

    这是我执行 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
        1
    golangLover  
       2022-03-26 13:05:26 +08:00 via Android
    应该是因为你第一条 sql 查询出来的 entity 有外键。导致相关的 entity 也有额外的查询。尝试把第一个相关的 entity 把 fetch type 转为 lazy
    powinds
        2
    powinds  
    OP
       2022-03-26 13:21:36 +08:00
    谢谢,很有用
    lybcyd
        3
    lybcyd  
       2022-03-26 14:10:32 +08:00
    看起来是 N+1 问题,按照 JPA 规范使用 EntityGraph 解决。
    hay313955795
        4
    hay313955795  
       2022-03-26 14:12:22 +08:00
    我的上个项目也是用的 jpa.然后前一个挖坑的人用了外键,而且对应的数据贼多.导致后面查询数据的时候牵出来一大串数据,让整个页面加载数据的时候贼慢.所以我经常需要改这些代码...我对它印象很深.
    ilumer
        5
    ilumer  
       2022-03-26 14:19:30 +08:00
    jpa n+1 解决方法上面已经说了
    letitbesqzr
        6
    letitbesqzr  
       2022-03-26 16:28:51 +08:00
    1. JPA 规范使用 EntityGraph
    2. criteriabuilder 里使用 fetch
    3. querydsl 里使用 fetchJoin
    awolf
        7
    awolf  
       2022-03-26 20:32:47 +08:00
    这些都是 hibernate 时代带过来的知识点
    nothingistrue
        8
    nothingistrue  
       2022-03-28 09:19:48 +08:00
    对于 JPA 、Hibernate ,或者任何 DDD 模型来说,所有关联对象都是要(立刻或者延迟)全部加载进内存的,所以不能随意设计关联,要在业务上的关联和性能上的伸缩性之间做平衡。

    其他备注:不要太过于在意 N+1 问题,N+1 虽然是 N+1 次查询,但是实际上也就两次 SQL 编译,对性能影响不是严重的级别,能解决掉是好,但是为了开发便利性不解决也可用。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5857 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 34ms · UTC 02:24 · PVG 10:24 · LAX 19:24 · JFK 22:24
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.