MySQL Spring JPA复杂查询排序保留及查询效率对比问题

你原来的写法排序丢失是符合SQL执行逻辑的：SQL规范中未搭配LIMIT的子查询ORDER BY会被数据库优化器直接忽略，同时DISTINCT操作、外层JOIN操作都不会保留子查询的行顺序，所以不能依赖子查询的排序传递到外层。

你可以把排序维度暴露到外层，在外层统一执行排序，不需要拆分查询也能保留正确顺序，优化后的写法如下（适配MySQL8+/PostgreSQL等支持窗口函数的数据库，完全不需要额外写DISTINCT）：

WITH site_sort AS (
    SELECT 
        s.id AS site_id,
        et.severity_level,
        COUNT(et.`type`) AS event_cnt,
        -- 每个站点取排序优先级最高的一条规则，自动去重
        ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY et.severity_level ASC, COUNT(et.`type`) DESC) AS rn
    FROM sites s
    JOIN users_sites us ON s.id = us.site_id
    JOIN users u ON us.user_id = u.user_id
    JOIN areas a ON s.id = a.site_id
    JOIN panels p ON a.id = p.area_id
    JOIN events e ON p.id = e.panel_id
    JOIN event_types et ON e.event_type_id = et.id
    WHERE u.user_id = "98765432-123a-1a23-123b-11a1111b2cd3"
    GROUP BY s.id, et.severity_level, et.`type`
)
SELECT s.* 
FROM sites s
JOIN site_sort ss ON s.id = ss.site_id AND ss.rn = 1
-- 外层统一排序，顺序完全和你原来的逻辑一致
ORDER BY ss.severity_level ASC, ss.event_cnt DESC
-- 分页直接加LIMIT/OFFSET即可，适配Spring JPA的Pageable分页参数

如果使用的是不支持窗口函数的低版本数据库（如MySQL5.7），可以用分组聚合的方式实现：

SELECT s.*
FROM sites s
JOIN (
    SELECT 
        s.id AS site_id,
        et.severity_level,
        COUNT(et.`type`) AS event_cnt
    FROM sites s
    JOIN users_sites us ON s.id = us.site_id
    JOIN users u ON us.user_id = u.user_id
    JOIN areas a ON s.id = a.site_id
    JOIN panels p ON a.id = p.area_id
    JOIN events e ON p.id = e.panel_id
    JOIN event_types et ON e.event_type_id = et.id
    WHERE u.user_id = "98765432-123a-1a23-123b-11a1111b2cd3"
    GROUP BY s.id, et.severity_level
) AS ss ON s.id = ss.site_id
GROUP BY s.id
ORDER BY MIN(ss.severity_level) ASC, MAX(ss.event_cnt) DESC

在你提到的每10秒执行一次、性能敏感的场景下，优化后的单条查询性能明显优于拆分查询方案，原因如下：

• 单条查询仅需要一次数据库往返，没有两次查询的网络IO开销，也不需要业务层额外做内存排序
• 数据库层的排序、关联都可以通过索引优化，只要给关联字段加对应索引（users_sites(user_id,site_id)、areas(site_id)、panels(area_id)、events(panel_id,event_type_id)），执行效率会非常高
• 拆分查询方案仅在单页数据量小于20条的场景下和单条查询性能接近，一旦数据量超过100，IN语句的解析开销、内存排序的O(nlogn)开销会快速上升，且如果需要全量拉取数据，拆分方案的性能会差几个数量级

1. 优先使用优化后的单条原生SQL实现，Spring JPA中直接用@Query(nativeQuery = true)注解引入SQL，传入Pageable参数即可自动适配分页逻辑
2. 如果因为特殊原因必须使用拆分方案，一定要在第一次获取ID的查询里就加上LIMIT/OFFSET做分页，不要全量拉取ID再分页，否则会有严重的性能问题

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