MySQL中count() over()与两次查询的性能对比及HQL实现

1. count() over() 对比两次查询是否更优？ #

大部分业务场景下，用count() over()的单查询方式比分开执行数据查询+计数查询更优，核心原因有两点：

• 减少请求与连接开销：两次查询需要发起两次数据库请求，哪怕复用连接，也会产生额外的请求解析、网络传输消耗；合并成一次查询就能省去这部分冗余开销。
• 避免重复执行核心逻辑：如果查询带有where过滤、order by排序等操作，两次查询会让数据库重复解析执行计划、扫描符合条件的数据、执行排序；而count() over()会在同一次数据扫描过程中，同时完成总数统计和分页数据提取，核心逻辑仅执行一次，大幅节省计算资源。

仅在极端特例下可能例外：比如计数查询能用到极致高效的覆盖索引（比如仅用主键索引统计总数），而主查询需要扫描大量数据做复杂过滤/关联，这种场景下分开查询可能有微小优势，但实际业务中极少遇到。

2. HQL中如何编写该查询？ #

从Hibernate 5.2版本开始，HQL支持窗口函数（前提是底层数据库支持，比如MySQL 8.0+），结合@OneToMany关联的场景，写法如下：

基础写法（无关联集合加载） #

假设实体类为Table1，对应数据库表table1，基础分页+总数查询的HQL：

select count(1) over() as recordCount, t1 from Table1 t1 order by t1.id limit 10

如果需要添加过滤条件，直接追加where子句即可：

select count(1) over() as recordCount, t1 from Table1 t1 where t1.status = :status order by t1.id limit 10

查询结果会返回Object[]数组，第一个元素是Long类型的总记录数，第二个是Table1实体对象。

结合DTO封装结果 #

如果想更优雅地接收结果，可以定义一个DTO类：

public class PageResult<T> {
    private Long recordCount;
    private T entity;

    public PageResult(Long recordCount, T entity) {
        this.recordCount = recordCount;
        this.entity = entity;
    }
    // getter、setter 省略
}

然后用HQL的构造函数表达式查询：

select new com.yourpackage.PageResult(count(1) over(), t1) from Table1 t1 order by t1.id limit 10

处理@OneToMany关联的fetch join场景 #

如果需要通过fetch join立即加载@OneToMany关联的集合，要注意笛卡尔积会导致count(1)重复统计，此时需要用distinct确保总数正确：

select count(distinct t1.id) over() as recordCount, t1 from Table1 t1 left join fetch t1.relatedItems order by t1.id limit 10

这里relatedItems是Table1中@OneToMany注解的集合属性，count(distinct t1.id)可以避免因关联数据产生的重复行导致总数统计错误。

内容的提问来源于stack exchange，提问作者Yash Londhe