JPA 的 N+1 試煉之路

Speaker: Tim Huang
Event: https://jcconf.tw/2022/

Transcript

1. JPA 的 N+1 試煉之路 Tim Chen 2022.10
2. About me Tim Chen Server-Side Engineer @ LINE • GitHub: https://github.com/chen-tf
3. 01 02 03 Contents What is JPA N+1 Problem 3W1H Entity Graph
4. JPA provides an object relational mapping approach that you declaratively define how to map Java objects to relational database tables in a standard, portable way. Just a specification, doesn’t perform any operation. (Hibernate, EclipseLink Java Persistence API What is JPA Object Table
5. N+1 in real world
6. Moon ! 換你報告 我把⽂件忘在家裡，等我20分鐘 下⼀位，James！ 我也是… 20 分鐘過後 兩位不知道今天要開會嗎？ 為什麼不事先準備好⽂件！
7. N+1 Problem 3W1H
8. One query for parents N queries for children What is the N+1 problem in JPA? parent parent query child child child query query
9. Why N+1 problem will decrease performance? Round trips to the database cost more in overhead than the query itself.
10. N+1 Problem 0 20 40 60 80 100 10 1000 10000 50000 SQL Execution Time One Join Query N+1 Query (Seconds) (Total Parents)
11. When will N+1 problem happen? Most often occurs with lazy loading
12. N+1 Problem customer id int[pk] name char order_history id int[pk] customer_id int price int8 created_time datetime 1 *
13. We want to calculate the total price that orders are after a specific event day. (Using Spring Data JPA Repository + Hibernate) SQL generated ? customerRepository.findAll() .stream() .map(Customer::getOrderHistories) .flatMap(Collection::stream) .filter(this::isAfterEventDate) .mapToLong(OrderHistory::getPrice) .sum();
14. customer id name 1 Tim 2 Sandy order_history id customer_id price created_time 1 1 100 2022-09-01 2 1 200 2022-10-05 3 2 300 2022-10-07 > customerRepository.findAll() [SQL] select * from customer Total Query Counts: 1
15. customer id name 1 Tim 2 Sandy order_history id customer_id price created_time 1 1 100 2022-09-01 2 1 200 2022-10-05 3 2 300 2022-10-07 > customer.getOrderHistories() [SQL] select * from order_history where customer_id = 1 Total Query Counts: 2
16. customer id name 1 Tim 2 Sandy order_history id customer_id price created_time 1 1 100 2022-09-01 2 1 200 2022-10-05 3 2 300 2022-10-07 > customer.getOrderHistories() [SQL] select * from order_history where customer_id = 2 Total Query Counts: 3
17. JPA Fetch Type FetchType.LAZY FetchType.EAGER • @OneToMany • @ManyToMany • @OneToOne • @ManyToOne Why order histories relation lazy loading ?
18. How can we solve the N+1 query problem TWO Queries JOIN FETCH ENTITY GRAPH Select * from parent Select * from child where id in (…)
19. Entity Graph Entity Graph is then to improve the runtime performance when loading the entity’s related associations and basic fields. customer order history Switch between lazy and eager ? LAZY EAGER customer order history
20. Entity Graph @NamedEntityGraph(name = {"customer.orderHistories"}, attributeNodes = { @NamedAttributeNode("orderHistories")}) public class Customer select * from customer c left outer join order_history oh on c.id = oh.customer_id public interface CustomerRepository extends JpaRepository<Customer, Long> { @Override @EntityGraph("customer.orderHistories") List<Customer> findAll(); }
21. JPQL JPQL CustomerRepository > Write query every time Using Join Fetch
22. Customer > Define Entity Graph Once CustomerRepository > Reuse graph by name Using Entity Graph 1 2 3
23. Reference • JPA Entity Graph (Spring Data JPA Doc) • Java Persistence API (oracle Java EE 6 Tutorial) • Hibernate ORM (Your relational data. Objectively) • Introduction_to_Java_Persistence(eclipse wiki)
24. THANK YOU