@Query 与分页:count 查询与排序的坑

Page、Slice、List

实测 · 本机执行输出

findByStatus("NEW", PageRequest.of(0, 20))(Page)  ⇒ Page(第 0 页,内容 20 行,总数 125000,总页数 6250),用时 114 ms,发出 2 条 SQL
    select … from ord o1_0 where o1_0.status=? limit ?
    select count(o1_0.id) from ord o1_0 where o1_0.status=?
findSliceByStatus("NEW", PageRequest.of(0, 20))(Slice)  ⇒ Slice(内容 20 行,hasNext=true),用时 5 ms,发出 1 条 SQL
    select … from ord o1_0 where o1_0.status=? limit ?
findListByStatus("NEW", PageRequest.of(0, 20))(List)  ⇒ 20 行,用时 5 ms,发出 1 条 SQL
    select … from ord o1_0 where o1_0.status=? limit ?
@Query("select o from Ord o where o.status = ?1") Page,不写 countQuery  ⇒ Page(第 0 页,内容 20 行,总数 125000,总页数 6250),用时 24 ms,发出 2 条 SQL
    select … from ord o1_0 where o1_0.status=? limit ?
    select count(o1_0.id) from ord o1_0 where o1_0.status=?
@Query(…, countQuery = …) Page  ⇒ Page(第 0 页,内容 20 行,总数 125000,总页数 6250),用时 23 ms,发出 2 条 SQL
    select … from ord o1_0 where o1_0.status=? limit ?
    select count(o1_0.id) from ord o1_0 where o1_0.status=?

Page 两条 SQL(数据加 count),Slice 与 List 一条;@Query 的 count 会自动推出来。

省掉 count

实测 · 本机执行输出

findByIdLessThanEqual(5, 第 0 页,每页 20)(总共只有 5 行)  ⇒ Page(第 0 页,内容 5 行,总数 5,总页数 1),用时 4 ms,发出 1 条 SQL
    select … from ord o1_0 where o1_0.id<=? limit ?
findByIdLessThanEqual(30, 第 0 页,每页 20)(总共 30 行,第一页满了)  ⇒ Page(第 0 页,内容 20 行,总数 30,总页数 2),用时 4 ms,发出 2 条 SQL
    select … from ord o1_0 where o1_0.id<=? limit ?
    select count(o1_0.id) from ord o1_0 where o1_0.id<=?
findByIdLessThanEqual(30, 第 1 页,每页 20)(最后一页只有 10 行)  ⇒ Page(第 1 页,内容 10 行,总数 30,总页数 2),用时 3 ms,发出 1 条 SQL
    select … from ord o1_0 where o1_0.id<=? limit ?,?
findByIdLessThanEqual(30, 第 5 页,每页 20)(超出末页)  ⇒ Page(第 5 页,内容 0 行,总数 30,总页数 2),用时 1 ms,发出 2 条 SQL
    select … from ord o1_0 where o1_0.id<=? limit ?,?
    select count(o1_0.id) from ord o1_0 where o1_0.id<=?

内容不足一页(且非空)时,Page 靠 offset 加内容行数算出总数,不发 count。

排序

实测 · 本机执行输出

Sort.by("amount")  ⇒ Page(第 0 页,内容 3 行,总数 125000,总页数 41667),用时 120 ms,发出 2 条 SQL
    select … from ord o1_0 where o1_0.status=? order by o1_0.amount limit ?
    select count(o1_0.id) from ord o1_0 where o1_0.status=?
Sort.by("nope")(不存在的属性)  ⇒ 抛 PropertyReferenceException:No property 'nope' found for type 'Ord',用时 3 ms,发出 0 条 SQL
Sort.by("amount; drop table ord")(想注入)  ⇒ 抛 InvalidDataAccessApiUsageException:Sort expression 'amount; drop table ord: ASC' must only contain property references or aliases used in the select clause; If you really want to use something other than that for sorti…,用时 0 ms,发出 0 条 SQL
原生查询 Sort.by("createdAt")(Java 属性名)  ⇒ 抛 InvalidDataAccessResourceUsageException:JDBC exception executing SQL [Unknown column 'createdAt' in 'order clause'] [select * from ord where status = ? order by createdAt asc limit ?]; SQL [select * from ord where stat…,用时 43 ms,发出 1 条 SQL
    select … from ord where status = ? order by createdAt asc limit ?
原生查询 Sort.by("created_at")(列名)  ⇒ Page(第 0 页,内容 3 行,总数 125000,总页数 41667),用时 122 ms,发出 2 条 SQL
    select … from ord where status = ? order by created_at asc limit ?
    select count(1) from ord where status = ?
原生查询 Page 不排序  ⇒ Page(第 0 页,内容 3 行,总数 125000,总页数 41667),用时 17 ms,发出 2 条 SQL
    select … from ord where status = ? limit ?
    select count(1) from ord where status = ?
  分页遍历前 1000 行(每页 20,50 页)按 bucket: ASC:共读到 1000 行,其中不同的行 896 个
  分页遍历前 1000 行(每页 20,50 页)按 bucket: ASC,id: ASC:共读到 1000 行,其中不同的行 1000 个

原生查询的 Sort 要写列名;排序列不唯一会让翻页重复和漏行,要补上唯一列。

深分页

实测 · 本机执行输出

  第 0 页(offset 0):Slice 0.6 ms;Page(多一条 count(*))43.6 ms
  第 1000 页(offset 20000):Slice 4.7 ms;Page(多一条 count(*))47.8 ms
  第 12500 页(offset 250000):Slice 45.1 ms;Page(多一条 count(*))87.6 ms
  第 24999 页(offset 499980):Slice 91.7 ms;Page(多一条 count(*))132.2 ms
  keyset:where id > 0 order by id limit 20:0.9 ms
  keyset:where id > 20000 order by id limit 20:0.7 ms
  keyset:where id > 250000 order by id limit 20:0.6 ms
  keyset:where id > 499980 order by id limit 20:0.7 ms

offset 越大越慢;keyset 的耗时与位置无关,但只能翻下一页。

join fetch 加分页

实测 · 本机执行输出

@Query("select c from Customer c join fetch c.orders") Page,每页 5 个 customer  ⇒ Page(第 0 页,内容 5 行,总数 20000,总页数 4000),用时 127 ms,发出 2 条 SQL
    select … from customer c1_0 join ord o1_0 on c1_0.id=o1_0.customer_id
    select count(c1_0.id) from customer c1_0 join ord o1_0 on c1_0.id=o1_0.customer_id
同上,但 countQuery 指向 select count(c) from Customer c  ⇒ Page(第 0 页,内容 5 行,总数 50,总页数 10),用时 98 ms,发出 2 条 SQL
    select … from customer c1_0 join ord o1_0 on c1_0.id=o1_0.customer_id
    select count(c1_0.id) from customer c1_0
  Hibernate 日志(WARN 及以上)共 5 条:
    LOG o.h.t.s.i.ExceptionHandlerLoggedImpl WARN GenerationTarget encountered exception accepting command : Error executing DDL "alter table ord drop foreign key FKluv11f42b6omo6qhhp4alwf1m" via JDBC [Table 'demo.ord' doesn't exist]
    LOG org.hibernate.orm.jdbc.error WARN HHH000247: ErrorCode: 1054, SQLState: 42S22
    LOG org.hibernate.orm.jdbc.error WARN Unknown column 'createdAt' in 'order clause'
    LOG org.hibernate.orm.query WARN HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory

在内存里分页,count 数的是 join 后的行数,要显式写 countQuery。

分步演示

@Query 与分页

本步的对象

本文用到的工具与边界

本机 macOS 27 arm64 / OpenJDK 21.0.12(Homebrew) / Spring Boot 4.0.8(Spring Framework 7.0.9、Tomcat 11.0.24) / Spring Cloud 2025.1.3;用 javac 编译、java 直接运行,依赖由 Maven 从 Maven Central 解析;数字每次运行会有小幅抖动(Hibernate 7、Spring Data JPA 随 spring-boot-starter-data-jpa;MySQL 是本机自己起的 26.7.0 实例)。ord 表 500000 行(用递归 CTE 生成),status 4 个取值、索引 ix_ord_status,前 20000 行属于 50 个 customer;SQL 来自 Hibernate 的 StatementInspector;第四节每项取 5 次中位数,前几节的单次耗时受 JIT 影响没有引用;JVM -Xmx2g
没有验证:PostgreSQL 等其他数据库的 limit/offset 行为;复合索引能否让 order by 省掉 filesort(没有看 EXPLAIN);Spring Data 4 新增的 ScrollPosition / Window 滚动 API(keyset 的官方写法);Page 的 count 在大表上的优化(近似计数、缓存);原生查询里 countQuery 写法的其他差别;@EntityGraph 与分页的配合(见第 91 篇)

下一篇 · SPRING 90

Specification 与动态查询