从数据库中筛选大量数据

问：

我在我的应用程序中使用 JPA、Hibernate 和 MySQL。

我正在尝试优化我的代码以使其执行得更快。我注意到从 UserRepository 下载该方法的数据需要更多时间：

@Cacheable(cacheNames = "subscribers")
   @Query("SELECT DISTINCT u.email FROM User u " +
        "LEFT JOIN u.subscribedAuthors sa " +
        "LEFT JOIN u.subscribedCategories sc " +
        "WHERE (sa IN :authors OR sc IN :categories) " +
        "AND (u.notificationDate IS NULL OR u.notificationDate < :timestamp)")
Page<String> findUserEmailsBySubscribedAuthorsOrSubscribedCategoriesContains(
        @Param("authors") Set<String> authors,
        @Param("categories") Set<String> categories,
        @Param("timestamp") LocalDateTime timestamp,
        Pageable pageable);

@Modifying
@Query("UPDATE User u SET u.notificationDate = CURRENT_TIMESTAMP WHERE u.email IN :emails")
void updateNotificationDateByEmails(@Param("emails") List<String> emails);

我试图减小页面大小，我使用了缓存，但应用程序仍然挂在第二个 SELECT 上：

Hibernate: select distinct u1_0.email from user u1_0 left join user_subscribed_authors s1_0 on u1_0.id=s1_0.user_id left join user_subscribed_category s2_0 on u1_0.id=s2_0.user_id where (s1_0.author in (?,?,?,?,?,?,?,?,?,?,?,?,?,?) or s2_0.category in (?,?,?) limit ?,?
Hibernate: select count(distinct u1_0.email) from user u1_0 left join user_subscribed_authors s1_0 on u1_0.id=s1_0.user_id left join user_subscribed_category s2_0 on u1_0.id=s2_0.user_id where (s1_0.author in (?,?,?,?,?,?,?,?,?,?,?,?,?,?) or s2_0.category in (?,?,?))

打了很久。总的来说，我的数据库中有一百万用户，电子邮件是异步发送的。我可以更改哪些内容以更快地从数据库下载用户？

类，我使用这种方法：

@Component
@RequiredArgsConstructor
public class NewBooksEmailSchedule {
    private static final int PAGE_SIZE = 1000;

    private final BookRepository bookRepository;
    private final UserRepository userRepository;
    private final EmailService emailService;
    
    @Scheduled(cron = "0 0 0 * * MON")
    public void sendNewBooksSummary() throws InterruptedException {
        
        LocalDateTime timestamp = LocalDateTime.now().minusWeeks(1);
        
        if (bookRepository.hasNewBooks(timestamp)) {
            int pageNumber = 0;
            Pageable pageable = PageRequest.of(pageNumber, PAGE_SIZE);
            Page<String> usersPage;
            Page<String> authors;
            Page<String> categories;

            do {
                authors = bookRepository.findAuthorsByCreateTimeAfter(timestamp, pageable);
                categories = bookRepository.findCategoriesByCreateTimeAfter(timestamp, pageable);

                do {

                    usersPage = userRepository
                            .findUserEmailsBySubscribedAuthorsOrSubscribedCategoriesContains
                                    (authors.toSet(), categories.toSet(),timestamp, pageable);

                    userRepository.updateNotificationDateByEmails(usersPage.getContent());

                    emailService.sendNewBooksAlert(usersPage.getContent());

                    pageNumber++;
                    pageable = PageRequest.of(pageNumber, PAGE_SIZE);

                } while (usersPage.hasNext());
                pageNumber++;
                pageable = PageRequest.of(pageNumber, PAGE_SIZE);
            } while (authors.hasNext() || categories.hasNext());
        }
    }
}

实体：

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
@Getter
@Setter
@Builder
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String firstName;
    private String lastName;
    private String email;
    private String password;

    @ElementCollection(targetClass = Role.class, fetch = FetchType.EAGER)
    @CollectionTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id"))
    @Column(name = "role", nullable = false)
    @Enumerated(EnumType.STRING)
    private Set<Role> roles;

    private boolean enabled = false;

    @ElementCollection(fetch = FetchType.LAZY)
    @CollectionTable(name = "user_subscribed_authors", joinColumns = @JoinColumn(name = "user_id"))
    @Column(name = "author")
    private Set<String> subscribedAuthors;

    @ElementCollection(fetch = FetchType.LAZY)
    @CollectionTable(name = "user_subscribed_category", joinColumns = @JoinColumn(name = "user_id"))
    @Column(name = "category")
    private Set<String> subscribedCategories;

    private LocalDateTime notificationDate;

}

@Entity
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
@Getter
@Setter
@Builder
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String author;
    private String category;

    @CreationTimestamp
    @Column(updatable = false)
    private LocalDateTime createTime;
}

配置：

 @SpringBootApplication
    @EnableAsync
    @EnableCaching
    @EnableScheduling
    public class Application {
    
        public static void main(String[] args) {
            SpringApplication.run(Application.class, args);
        }
   
    
        @Bean(name = "threadPoolTaskExecutor")
        public ThreadPoolTaskExecutor taskExecutor() {
            ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
            executor.setCorePoolSize(10);
            executor.setMaxPoolSize(20);
            executor.setQueueCapacity(1000);
            executor.setThreadNamePrefix("AsyncThread-");
            executor.initialize();
            return executor;
        }
        @Bean
        public TaskScheduler taskScheduler() {
            ThreadPoolTaskScheduler taskScheduler = new ThreadPoolTaskScheduler();
            taskScheduler.setPoolSize(5);
            return taskScheduler;
        }
        public AsyncUncaughtExceptionHandler getAsyncUncaughtExceptionHandler() {
            return new CustomAsyncExceptionHandler();
        }