概要
この記事はJava Persistence API 2.1のおさらいメモの補足です。
少し複雑なモデルを使って最適な(N+1問題を回避する)検索処理の実装方法を確認します。
最適化の目標は下記の3点です。
- 関連のFetchTypeは可能な限りLAZYとする。
- open-in-viewは無効(false)にする。
- 発行されるSQLの数を抑える。
環境
- Windows 10 Professional
- Java 9.0.4
- Spring Boot 2.0.0
- Spring Data Jpa 2.0.5
- Hibernate 5.2.14
- MySQL 5.7.19
デモ環境
モデル関連図
(parent) <----------------> (child)
(parent) <---------------> (child)
+--------+ +--------+ +--------------+ +---------+
| |---[OneToMany]--->| |---[OneToOne]--->| +---[ManyToOne]--->| |
| Person | (Cascade) | Phone | (Cascade) | PhoneDetails | | Provider|
| | | | | | | |
| |<---[ManyToOne]---+ |<---[OneToOne]---+ | | |
+--------+ +--------+ +--------------+ +---------+
Entityクラス
- 記載するDDLはHibernateがEntityクラスから生成したものです。
Person
Person
@Entity(name = "Person")
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name", nullable = false)
private String name;
@OneToMany(mappedBy = "person", cascade = CascadeType.ALL, orphanRemoval = true,
fetch = FetchType.LAZY) // default LAZY
//@OnDelete(action = OnDeleteAction.CASCADE)
@JsonManagedReference("Person")
private List<Phone> phones = new ArrayList<>();
public void addPhone(Phone phone) {
phones.add(phone);
phone.setPerson(this);
}
public void removePhone(Phone phone) {
phones.remove(phone);
phone.setPerson(null);
}
}
person
create table person (
id bigint not null auto_increment
, name varchar(255) not null
, primary key (id)
) engine=InnoDB
Phone
Phone
@Entity(name = "Phone")
public class Phone {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "`number`", nullable = false)
private String number;
@ManyToOne(fetch = FetchType.LAZY) // default EAGER
@JoinColumn(name = "person_id", nullable = false)
@JsonBackReference("Person")
private Person person;
@OneToOne(mappedBy = "phone", cascade = CascadeType.ALL, orphanRemoval = true,
fetch = FetchType.LAZY) // default EAGER
@JsonManagedReference("Phone")
private PhoneDetails details;
public void addDetails(PhoneDetails details) {
this.details = details;
details.setPhone(this);
}
public void removeDetails() {
if (this.details != null) {
this.details.setPhone(null);
this.details = null;
}
}
public static Phone of(String number) {
Phone phone = new Phone();
phone.setNumber(number);
return phone;
}
public static Phone of(String number, PhoneDetails details) {
Phone phone = new Phone();
phone.setNumber(number);
phone.setDetails(details);
details.setPhone(phone);
return phone;
}
}
phone
create table phone (
id bigint not null auto_increment
, `number` varchar(255) not null
, person_id bigint not null
, primary key (id)
) engine=InnoDB
PhoneDetails
@Entity(name = "PhoneDetails")
public class PhoneDetails {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@OneToOne(fetch = FetchType.LAZY) //default EAGER
@JoinColumn(name = "phone_id", nullable = false)
//@OnDelete(action= OnDeleteAction.CASCADE)
@JsonBackReference("Phone")
private Phone phone;
@ManyToOne(fetch = FetchType.LAZY) //default EAGER
@JoinColumn(name = "provider_id", nullable = false)
private Provider provider;
private String technology;
public static PhoneDetails of(String technology, Provider provider) {
PhoneDetails details = new PhoneDetails();
details.setTechnology(technology);
details.setProvider(provider);
return details;
}
}
phone_details
create table phone_details (
id bigint not null auto_increment
, technology varchar(255)
, phone_id bigint not null
, provider_id bigint not null
, primary key (id)
) engine=InnoDB
Provider
@Entity(name = "Provider")
public class Provider {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
public static Provider of(String name) {
Provider provider = new Provider();
provider.setName(name);
return provider;
}
}
provider
create table provider (
id bigint not null auto_increment
, name varchar(255)
, primary key (id)
) engine=InnoDB
外部キー制約
alter table phone add constraint FKkk6uij3j6wikpnqlj9dymobs9
foreign key (person_id)
references person (id)
alter table phone_details add constraint FK4vs8ep213cifmt8opragp1gkw
foreign key (phone_id)
references phone (id)
alter table phone_details add constraint FKrco5hsjio6faa9f4s7wc1jd3c
foreign key (provider_id)
references provider (id)
デモデータ
select * from provider;
+----+------------------+
| id | name |
+----+------------------+
| 1 | Verizon Wireless |
| 2 | T-Mobile |
| 3 | AT&T |
+----+------------------+
3 rows in set (0.00 sec)
select * from person;
+----+--------+
| id | name |
+----+--------+
| 1 | robb |
| 2 | bran |
| 3 | jon |
| 4 | rickon |
+----+--------+
4 rows in set (0.00 sec)
select person.id as person_id
, person.name
, phone.id as phone_id
, phone.number
, phone_details.technology
, provider.name as provider_name
from person left outer join phone on person.id = phone.person_id
left outer join phone_details on phone.id = phone_details.phone_id
left outer join provider on provider.id = phone_details.provider_id
order by
person.id
, phone.id;
+-----------+--------+----------+----------------+------------+------------------+
| person_id | name | phone_id | number | technology | provider_name |
+-----------+--------+----------+----------------+------------+------------------+
| 1 | robb | 1 | p1-123-456-789 | GSM | Verizon Wireless |
| 1 | robb | 2 | p1-456-789-123 | NULL | NULL |
| 1 | robb | 3 | p1-789-123-456 | GSM | T-Mobile |
| 2 | bran | 4 | p2-123-456-789 | GSM | AT&T |
| 3 | jon | 5 | p3-123-456-789 | GSM | Verizon Wireless |
| 3 | jon | 6 | p3-456-789-123 | NULL | NULL |
| 4 | rickon | 7 | p4-123-456-789 | GSM | Verizon Wireless |
+-----------+--------+----------+----------------+------------+------------------+
7 rows in set (0.00 sec)
検索処理
検索したエンティティのデータをjsonへ変換してクライアントへ返すというRest APIを想定しています。
実装する検索処理は、personのidを指定して1件取得する処理と、personを全件取得する処理の2つです。
1件検索時のjsonのイメージ
person idを指定した検索結果のjsonです。全件検索はこれが配列になります。
{
"id": 1,
"name": "robb",
"phones": [
{
"id": 1,
"number": "p1-123-456-789",
"details": {
"id": 1,
"provider": {
"id": 1,
"name": "Verizon Wireless"
},
"technology": "GSM"
}
},
{
"id": 2,
"number": "p1-456-789-123",
"details": null
},
{
"id": 3,
"number": "p1-789-123-456",
"details": {
"id": 2,
"provider": {
"id": 2,
"name": "T-Mobile"
},
"technology": "GSM"
}
}
]
}
シンプルな検索
比較対象としてjoin fetchやEntityGraphを使わない方法で検索処理を実装します。
ただし、関連のFetchTypeがLAZYでopen-in-viewがfalseだとjson変換時に下記のエラーが発生してしまうので、この例ではopen-in-viewをtrueにします。
Failed to write HTTP message: org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: failed to lazily initialize a collection of role: com.example.demofeaturelayer.ext.entity.Person.phones, could not initialize proxy - no Session; nested exception is com.fasterxml.jackson.databind.JsonMappingException: failed to lazily initialize a collection of role: com.example.demofeaturelayer.ext.entity.Person.phones, could not initialize proxy - no Session (through reference chain: com.example.demofeaturelayer.ext.entity.Person["phones"])
1件検索する処理
String jpql = "SELECT p FROM Person p WHERE p.id = :id";
Person person = entityManager.createQuery(jpql, Person.class)
.setParameter("id", id)
.getSingleResult();
発行されるSQL
関連するエンティティにアクセスするたびにselect文が発行されてしまう非効率な検索処理です。
select person0_.id as id1_6_, person0_.name as name2_6_
from person person0_
where person0_.id=1
/* 以降はトランザクション外で発行される */
select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
from phone phones0_
where phones0_.person_id=1
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=1
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=2
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=3
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=1
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=2
全件検索する処理
String jpql = "SELECT p FROM Person p";
List<Person> personList = entityManager.createQuery(jpql, Person.class)
.getResultList();
発行されるSQL
select person0_.id as id1_6_, person0_.name as name2_6_
from person person0_
/* 以降はトランザクション外で発行される */
select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
from phone phones0_
where phones0_.person_id=1
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=1
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=2
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=3
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=1
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=2
select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
from phone phones0_
where phones0_.person_id=2
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=4
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=3
select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
from phone phones0_
where phones0_.person_id=3
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=5
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=6
select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
from phone phones0_
where phones0_.person_id=4
select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
from phone_details phonedetai0_
where phonedetai0_.phone_id=7
JOIN FETCHを使用する
1つのSQLで必要なデータをすべて取得するので、関連をすべてLAZYにすることができます。
1件検索する処理
String jpql = "SELECT p FROM Person p LEFT OUTER JOIN FETCH p.phones ph " +
" LEFT OUTER JOIN FETCH ph.details dt " +
" LEFT OUTER JOIN FETCH dt.provider " +
" WHERE p.id = :id";
Person person = entityManager.createQuery(jpql, Person.class)
.setParameter("id", id)
.getSingleResult();
発行されるSQL
select person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, provider3_.id as id1_9_3_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_, provider3_.name as name2_9_3_
from person person0_
left outer join phone phones1_ on person0_.id=phones1_.person_id
left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
left outer join provider provider3_ on phonedetai2_.provider_id=provider3_.id
where person0_.id=1
全件検索する処理
String jpql = "SELECT DISTINCT p FROM Person p LEFT OUTER JOIN FETCH p.phones ph " +
" LEFT OUTER JOIN FETCH ph.details dt " +
" LEFT OUTER JOIN FETCH dt.provider";
List<Person> personList = entityManager.createQuery(jpql, Person.class)
.getResultList();
発行されるSQL
select distinct person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, provider3_.id as id1_9_3_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_, provider3_.name as name2_9_3_
from person person0_
left outer join phone phones1_ on person0_.id=phones1_.person_id
left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
left outer join provider provider3_ on phonedetai2_.provider_id=provider3_.id
JOIN FETCHを使用する (少し改良版)
データ件数の少ないマスター系のエンティティなどはJOIN FETCHせずに、敢えて別個にselectさせて1次キャッシュにのせておいたほうが検索処理のトータルのコストが下がる場合があります。
この例では、providerエンティティをjoin fetchから外して1次キャッシュからヒットさせるようにしています。
open-in-viewは無効にしたいので、providerが即時検索されるようにPhoneDetailsとProviderの関連をEAGERにします。
@Entity(name = "PhoneDetails")
public class PhoneDetails {
@ManyToOne(fetch = FetchType.EAGER) //default EAGER
@JoinColumn(name = "provider_id", nullable = false)
private Provider provider;
}
1件検索する処理
String jpql = "SELECT p FROM Person p LEFT OUTER JOIN FETCH p.phones ph " +
" LEFT OUTER JOIN FETCH ph.details dt " +
" WHERE p.id = :id";
Person person = entityManager.createQuery(jpql, Person.class)
.setParameter("id", id)
.getSingleResult();
発行されるSQL
1件検索のときは、別個に検索するメリットは薄いのでjoin fetchでもいいかもしれません。
select person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_
from person person0_
left outer join phone phones1_ on person0_.id=phones1_.person_id
left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
where person0_.id=1
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=1
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=2
全件検索する処理
String jpql = "SELECT DISTINCT p FROM Person p LEFT OUTER JOIN FETCH p.phones ph " +
" LEFT OUTER JOIN FETCH ph.details dt ";
List<Person> personList = entityManager.createQuery(jpql, Person.class)
.getResultList();
発行されるSQL
providerへのselectが3回発生していますが、1度キャッシュにのったデータは再びselectされないのでpersonのデータ件数が多いほど効果が期待できます。
select distinct person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_
from person person0_
left outer join phone phones1_ on person0_.id=phones1_.person_id
left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=1
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=2
select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
from provider provider0_
where provider0_.id=3
EntityGraphを使用する
EntityGraphをエンティティクラスに定義します。
NamedAttributeNodeアノテーションのvalueにFetchTypeをEAGERにしたいフィールドを指定していきます。
@Entity(name = "Person")
@NamedEntityGraph(name = "Person.graph",
attributeNodes = {
@NamedAttributeNode(value = "phones", subgraph = "Phone.graph")
},
subgraphs = {
@NamedSubgraph(name = "Phone.graph",
attributeNodes = {
@NamedAttributeNode(value = "details", subgraph = "PhoneDetails.graph")
}
),
@NamedSubgraph(name = "PhoneDetails.graph",
attributeNodes = {
@NamedAttributeNode(value = "provider")
}
)
}
)
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name", nullable = false)
private String name;
@OneToMany(mappedBy = "person", cascade = CascadeType.ALL, orphanRemoval = true,
fetch = FetchType.LAZY) // default LAZY
@JsonManagedReference("Person")
private List<Phone> phones = new ArrayList<>();
public void addPhone(Phone phone) {
phones.add(phone);
phone.setPerson(this);
}
public void removePhone(Phone phone) {
phones.remove(phone);
phone.setPerson(null);
}
}
EntityGraphではJPQLを記述する必要はなくリポジトリを使用することができます。
public interface PersonRepository extends JpaRepository<Person, Long> {
// 1件検索処理
@EntityGraph(value = "Person.graph", type = EntityGraph.EntityGraphType.LOAD)
@Override
Optional<Person> findById(Long id);
// 全件検索処理
@EntityGraph(value = "Person.graph", type = EntityGraph.EntityGraphType.LOAD)
@Override
List<Person> findAll();
}
1件検索する処理
Person person = personRepository.findById(id).orElseThrow(RuntimeException::new);
発行されるSQL
select person0_.id as id1_6_0_, person0_.name as name2_6_0_, phones1_.person_id as person_i3_7_1_, phones1_.id as id1_7_1_, phones1_.id as id1_7_2_, phones1_.`number` as number2_7_2_, phones1_.person_id as person_i3_7_2_, phonedetai2_.id as id1_8_3_, phonedetai2_.phone_id as phone_id3_8_3_, phonedetai2_.provider_id as provider4_8_3_, phonedetai2_.technology as technolo2_8_3_, provider3_.id as id1_9_4_, provider3_.name as name2_9_4_
from person person0_
left outer join phone phones1_ on person0_.id=phones1_.person_id
left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
left outer join provider provider3_ on phonedetai2_.provider_id=provider3_.id
where person0_.id=1
全件検索する処理
List<Person> personList = personRepository.findAll();
発行されるSQL
select person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, provider3_.id as id1_9_3_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_, provider3_.name as name2_9_3_
from person person0_
left outer join phone phones1_ on person0_.id=phones1_.person_id
left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
left outer join provider provider3_ on phonedetai2_.provider_id=provider3_.id