MySQL 5.6下的“戴着镣铐跳舞”：一次复杂列表查询的性能优化实战

原创于 2025-08-20 17:23:38 发布 · 1.1k 阅读

21 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #性能优化 #jpa

Spring Data JPA 专栏收录该内容

207 篇文章

订阅专栏

MySQL 5.6下的“戴着镣铐跳舞”：一次复杂列表查询的性能优化实战

在后端开发中，我们都梦想着能在最新的技术栈上挥洒创意。但现实往往是，我们需要在充满限制的“旧大陆”上，为日益复杂的业务需求构建高性能的应用。一个典型的挑战就是：如何在不支持窗口函数的老旧MySQL版本（如5.6）上，实现一个既要聚合计算，又要“分组取Top N”的复杂列表接口？

今天，我将带你亲历一次GET /api/app/solutions（小程序方案列表）接口的性能优化之旅。我们将看到，如何在MySQL 5.6这片“没有窗户的房间”里，通过数据库聚合和关联子查询这两把“瑞士军刀”，优雅地“开出一扇性能之窗”。

业务需求：一个信息丰富的“方案卡片” 📝

我们的目标是为小程序端开发一个“我的方案”列表页。每个方案在列表中需要以“卡片”的形式展示，包含丰富的摘要信息：

基础信息：方案ID、名称、状态、更新时间。
统计信息：产品种类数、单套价格、总套数、总金额。
预览信息：方案内前4个产品的图片，用于视觉展示。

数据模型：Solution -> SolutionItem -> SolutionProduct -> Product。
技术限制：线上数据库版本为MySQL 5.6.16，不支持ROW_NUMBER()等现代窗口函数。

挑战分析：两大性能“拦路虎”

聚合计算：如果将所有SolutionItem加载到Java内存中再进行count和sum，会造成巨大的内存和CPU (Central Processing Unit, 中央处理器) 浪费。
分组取Top N：如何在没有窗口函数的情况下，高效地查询出“每个方案的前4张图片”？

解决方案：“两步查询法”——分而治之，逐个击破 🚀

我们的核心策略是分而治之。将一个复杂的查询任务，分解为两个各自专注、且都高效的数据库查询。

第一步：主查询——聚合计算的“主力军”

我们首先通过一个JPQL (Java Persistence Query Language, Java持久化查询语言) 查询，在数据库层面完成分页和大部分聚合计算。

Repository层：

// SolutionRepository.java
@Query(value = "SELECT s.id, s.lastModifiedDate, s.name, s.status, s.setCount, " +
               "  COUNT(si.id), SUM(sp.appGroupBuyPrice * si.quantity) " +
               "FROM Solution s " +
               "LEFT JOIN s.solutionItems si " +
               "LEFT JOIN si.solutionProduct sp " +
               "WHERE s.solutionUser.id = :currentUserId " +
               "GROUP BY s.id, s.lastModifiedDate, s.name, s.status, s.setCount",
       countQuery = "SELECT count(s) FROM Solution s WHERE s.solutionUser.id = :currentUserId")
Page<Object[]> findSolutionsWithSummaryForUser(@Param("currentUserId") Integer currentUserId, Pageable pageable);

技术解读：

数据库聚合：COUNT(si.id)和SUM(...)将繁重的计算任务交给了数据库，应用层只接收最终的计算结果。
LEFT JOIN：保证了即使方案下没有产品，也能正确返回方案本身的信息。
GROUP BY: 支持了聚合函数的正确使用。

对应的SQL (Structured Query Language, 结构化查询语言) 日志：

-- 日志1: 主数据分页查询 + 聚合计算
Hibernate:
select
    s.id as col_0_0_, ..., count(si.id) as col_5_0_, sum(sp.price*si.quantity) as col_6_0_
from solution s
left outer join solution_item si on s.id=si.solution_id
left outer join solution_product sp on si.solution_product_id=sp.id
where s.solution_user_id=?
group by s.id, ...
limit ?

这一步，我们用一次高效的查询，就解决了大部分的数据需求。

第二步：子查询——模拟“分组取Top N”的“奇兵”

现在，我们来解决最棘手的问题：在MySQL 5.6下获取每个方案的前4张图片。我们使用关联子查询来模拟窗口函数。

Repository层：

// SolutionItemRepository.java
@Query(value = "SELECT si.solution_id, p.image " +
               "FROM solution_item si " +
               "JOIN solution_product sp ON si.solution_product_id = sp.id " +
               "JOIN product p ON sp.product_id = p.id " +
               "WHERE si.solution_id IN :solutionIds " +
               "  AND ( " +
               "    SELECT count(*) FROM solution_item si2 " +
               "    WHERE si2.solution_id = si.solution_id AND si2.id <= si.id " +
               "  ) <= 4",
       nativeQuery = true)
List<Object[]> findTop4ProductImagesBySolutionIds(@Param("solutionIds") List<Integer> solutionIds);

技术解读：

nativeQuery = true: 我们使用了原生SQL，因为它比JPQL在处理这种复杂查询时更强大。
WHERE si.solution_id IN :solutionIds: 这是一个批量查询，只针对当前页的方案ID，避免了全表操作。
AND (SELECT count(*) ... ) <= 4: 这是模拟ROW_NUMBER()的核心。对于外层查询的每一行，它都通过一个子查询计算出“在我自己的分组内，有多少行的ID比我小或等于我”，这实际上是在计算行号。我们只保留行号小于等于4的记录。

对应的SQL日志：

-- 日志2: 批量获取Top N图片 (MySQL 5.6兼容版)
Hibernate:
SELECT si.solution_id, p.image FROM solution_item si ...
WHERE si.solution_id IN (?, ?, ?)
  AND (SELECT count(*) FROM solution_item si2 ...) <= 4

Service层：优雅的“数据织工”

Service层的职责，就是将这两步查询的结果，在内存中优雅地“编织”成最终的VO (View Object, 视图对象)。

// AppSolutionService.java
@Transactional(readOnly = true)
public Page<AppSolutionDetailVO> listSolutionsForApp(...) {
    // 第一步：执行主查询
    Page<Object[]> entityPage = solutionRepository.findSolutionsWithSummaryForUser(...);
    if (entityPage.isEmpty()) { return Page.empty(); }

    // 提取ID，准备第二步查询
    List<Integer> solutionIds = ...;

    // 第二步：执行Top N图片查询
    List<Object[]> imageResults = solutionItemRepository.findTop4ProductImagesBySolutionIds(solutionIds);
    
    // 将图片结果处理成Map，方便查找
    Map<Integer, List<String>> solutionToImagesMap = ...;

    // 第三步：在内存中组装最终的VO列表
    List<AppSolutionDetailVO> voList = entityPage.getContent().stream()
            .map(row -> {
                AppSolutionDetailVO vo = new AppSolutionDetailVO();
                // ... 从row中填充聚合数据 ...
                // ... 从Map中填充图片列表 ...
                return vo;
            })
            .collect(Collectors.toList());

    return new PageImpl<>(voList, ...);
}

结论：限制是创新的催化剂 💡

这次实践告诉我们，即使面对老旧的技术栈限制，我们依然可以通过深刻理解SQL和JPA (Java Persistence API) 的原理，设计出高性能的解决方案。

分而治之：将复杂查询分解为多个职责单一的简单查询，是应对复杂性的不二法门。
数据库优先：尽可能地将聚合、排序、过滤等重计算任务下推到数据库执行。
SQL是你的朋友：当JPQL无法满足需求时，不要害怕使用原生SQL。对于“分组取Top N”这类经典问题，关联子查询是你在旧版MySQL中的可靠伙伴。
内存组装：将多次查询的结果在Java内存中使用Map等高效数据结构进行最后聚合，是“两步查询法”的收官之作。

最终，我们用固定的2次数据库交互，成功地构建了一个功能丰富、性能卓越的列表接口。这正是所谓的“戴着镣铐跳舞”——在限制中寻找最优解，这本身就是软件工程的魅力所在。

附录：图表化总结与深度解析 📊

优化方案总结表

挑战	低效方案 (内存计算) 👎	高效方案 (两步查询法) 👍
聚合计算	加载所有`SolutionItem`到Java内存	数据库聚合 (`COUNT`, `SUM`)
分组取Top N	加载所有图片，在Java中分组取前4	关联子查询 (模拟`ROW_NUMBER`)
DB交互次数	`1 + N` (N为方案数)	2 (固定)
性能	差，随数据量增长而崩溃	高，稳定可预测
一句话总结	“把工厂搬回家自己组装”	“让工厂按需生产，只运回成品”