Spring Data Pageable 性能优化

Question

好的，这很烦人，我真的不知道该怎么解决，所以事情是这样的。

有一个应用程序返回分页数据如下：

@Query(value="SELECT DD FROM Document DD "
        + " WHERE DD.deletedByUsr IS NULL "
        + " AND DD.deleteTime IS NULL "
        + " AND DD.version = (SELECT MAX(D.version) FROM  Document D WHERE D.code = DD.code AND D.status = :status  AND DD.status =:status)")
public Page<Document> getLatestByStatus(@Param("status") DocumentStatus status, Pageable pageable);

这个工作正常。

为了测试应用程序的性能，我们已向数据库添加了20K数据到此表格中，即使我创建了一个每页限制为5行的Pageable，如下所示：

Pageable pageable = PageRequest.of(0, 5, Sort.by(Sort.Direction.ASC, "id"));

调用仓库如下：

Page<Document> latestByStatus = repository.getLatestByStatus(DocumentStatus.APPROVED, pageable);

检索结果要花费很长时间。

但是，如果我像下面这样调用（使用上面实例化的相同可分页对象）：

Page<Document> latestByStatus = repository.findByStatus(DocumentStatus.APPROVED, pageable);

这个代码库在以下位置：

public Page<Document> findByStatus(DocumentStatus status, Pageable pageable);

结果立即呈现。

我直接在数据库（MSSQLSERVER）上运行了以下SQL语句以检查其所需时间：

 SELECT 
DD .*
FROM DOCUMENT DD
WHERE DD.DELETED_BY_USER_ID IS NULL
AND DD.DELETE_TIME IS NULL
AND DD.STATUS IN (2)
AND DD.VERSION = (SELECT MAX(D.VERSION) FROM  DOCUMENT D WHERE D.CODE = DD.CODE AND D.STATUS IN (2)) AND DD.STATUS IN (2)
ORDER BY DD.ID 
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

同时，结果也会立即返回。

我猜想，对于需要很长时间的第一种情况，Spring首先检索所有数据（没有限制行数），然后返回带有结果数量信息的页面。

不知道是否与@Query注释有关。

顺便说一下，我正在使用2.3.0.RELEASE版本的Spring Boot。

总之，为什么一个需要很长时间来检索数据，而另一个却可以立即检索到数据？

你们有什么解决办法吗？也许在@Query注释中有更好的编写查询的方式？

提前致谢。

- THIAGORC

Answer 1

经过多次调试和记录，我发现Spring Data在hibernate生成的查询中添加了分页(offset 0 rows fetch next 5 rows only)，因此主要问题在于@Query注释中的SQL查询语句。

我已经进行了更改:

@Query(value="SELECT DD FROM Document DD "
            + " WHERE DD.deletedByUsr IS NULL "
            + " AND DD.deleteTime IS NULL "
            + " AND DD.status =:status "
            + " AND DD.version = (SELECT MAX(D.version) FROM  Document D WHERE D.code = DD.code AND D.status = :status  AND D.deleteTime IS NULL AND D.deletedByUsr IS NULL )")

(在内部查询中添加了DD.status = :status和D.deletedByUsr IS NULL和D.deleteTime IS NULL)。

无论如何，谢谢。

- THIAGORC