Почему Spring Data делает лишние запросы?
141
26 июня 2019, 05:20
Есть следующий метод JPA репозитория:
@Query("select new com.example.sweater.domain.dto.MessageDto(" +
" m, " +
" count(ml), " +
" sum(case when ml = :user then 1 else 0 end) > 0" +
") " +
"from Message m left join m.likes ml " +
"group by m")
Page<MessageDto> findAll(Pageable pageable, @Param("user") User user);
Проблема: помимо одного запроса, который указан в аннотации @Query, в базу еще летят запросы на выборку КАЖДОГО сообщения отдельно.
Вопрос: почему так происходит и как с этим бороться?
Весь код доступен на github.
UPD1: Лог запросов:
Hibernate: select message0_.id as col_0_0_, count(user2_.id) as col_1_0_, sum(case when user2_.id=? then 1 else 0 end)>0 as col_2_0_ from message message0_ left outer join message_likes likes1_ on message0_.id=likes1_.message_id left outer join usr user2_ on likes1_.user_id=user2_.id group by message0_.id order by message0_.id desc limit ?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select message0_.id as id1_0_0_, message0_.user_id as user_id5_0_0_, message0_.filename as filename2_0_0_, message0_.tag as tag3_0_0_, message0_.text as text4_0_0_, user1_.id as id1_4_1_, user1_.activation_code as activati2_4_1_, user1_.active as active3_4_1_, user1_.email as email4_4_1_, user1_.password as password5_4_1_, user1_.username as username6_4_1_, roles2_.user_id as user_id1_2_2_, roles2_.roles as roles2_2_2_ from message message0_ left outer join usr user1_ on message0_.user_id=user1_.id left outer join user_role roles2_ on user1_.id=roles2_.user_id where message0_.id=?
Hibernate: select count(message0_.id) as col_0_0_ from message message0_ left outer join message_likes likes1_ on message0_.id=likes1_.message_id left outer join usr user2_ on likes1_.user_id=user2_.id group by message0_.id
UPD2: Еще заметил, что когда я пытаюсь передать в MessageDto(m,...), то вместо m передается не объект сообщения, как мне надо, а id этого сообщения. Может по этому hibernate ходит в базу за каждым сообщением?
Answer 1
Похоже на проблему N+1 selects. Она возникает, когда вы делаете select связанной, родительской, сущности у которой также имеется ссылка на данный объект. Hibernate делает дополнительные селекты для того чтобы вытащить все связи. Поэтому сначала Hibernate вытащит все N записией родителей, а затем, для каждого по отдельности запросит связные сущности.
Для того чтобы решить данную проблему можно либо явно указать join всех связей в @Query или же для всех связей включить кэш второго уровня:
@Entity
@Cacheable
@Table(name = "usr")
public class User implements UserDetails {
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
@OneToMany(mappedBy = "author", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private Set<Message> messages;
}
Проблема в том, что ваши Message и User содержит ссылку друг на друга.
Когда вы указываете FetchType.LAZY, это не означает что он вообще не будет запрашивать все N связей, просто он сделает N select'ов, когда вы обратитесь к этому полю.
Answer 2
Попробуйте использовать аннотацию @NamedEntityGraph
@Entity
@Table(name = "usr")
@NamedEntityGraph(name="allMessages", attributeNodes = {
@NamedAttributeNode("messages")
})
public class User implements UserDetails {
...
@OneToMany(mappedBy = "author", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private Set<Message> messages;
...
}
затем используйте этот граф в репозитории
@EntityGraph(value = "allMessages" , type=EntityGraph.EntityGraphType.FETCH)
Page<MessageDto> findAll(Pageable pageable, @Param("user") User user);
Вы можете использовать несколько NamedEntityGraph с разным набором полей, тем самым получая всегда только те поля которые вам сейчас действительно нужны за один запрос
-
07:09
-
17:51
-
08:01
-
07:36
-
07:00
-
21:22
-
20:00
-
20:14
-
07:41
ТОП-10
- Почему SERVER_ADDR имеет не тот IP 46672 visits
- Как заменить $_SERVER[REMOTE_ADDR] на IP клиента в PHP за двумя Nginx? 30287 visits
- Хочу вывести несколько строк из массива в один div, выводит только много undefined; подскажите, что делать? 22835 visits
- Как через css изменить цвет png изображения? 10161 visits
- Blob video url download 9800 visits
- Php curl запрос через прокси с авторизацией 9059 visits
- Работа с captcha vk api 7977 visits
© "hostciti.net" 25.4.2024 | 20:24