骏骏骏的博客

主题

骏骏骏2025-10-16
1299
5.7m
Get...

使用SQL sum函数查询的数据问题 ​

问题产生的背景： ​

线上的sql查询一直报警，查看发现是因为有慢sql，开始对sql语句进行排查时发现查询数量量有问题。

问题SQL语句： ​

Plain
SELECT
        t.id,
        t.tenant_id,
        t.plan_name,
        t.standard_id,
        t.plan_status,
        t.plan_type,
        t.user_id,
        t.classes_num,
        t.student_num,
        t.create_time,
        t.create_by,
        t.update_time,
        t.update_by,
        t.remark,
        t1.id AS tenantId,
        t1.tenant_name AS tenantName,
        t2.standard_name AS standardName,
        t3.user_name AS userName,
        t3.nick_name AS nickName,
        SUM( t4.student_cnt ) AS denominator,
        COUNT( t5.id ) AS numerator 
FROM
        edu_exam_plan t
        LEFT JOIN edu_tenant t1 ON ( t1.id = t.tenant_id AND t1.delete_flag = 0 )
        LEFT JOIN edu_exam_standard t2 ON ( t2.id = t.standard_id AND t2.delete_flag = 0 )
        LEFT JOIN sys_user t3 ON ( t3.user_id = t.user_id )
        LEFT JOIN edu_exam t4 ON ( t4.exam_plan_id = t.id AND t4.delete_flag = 0 )
        LEFT JOIN edu_exam_detail t5 ON ( t5.plan_id = t.id AND t5.delete_flag = 0 ) 
WHERE
        (
                t.plan_status = 1
                AND (
                        t.user_id = 56
                        AND t.tenant_id = 45
                        OR t.tenant_id = 45
                AND t.plan_type = 1)) 
GROUP BY
        t.id 
ORDER BY
        t.create_time DESC
        LIMIT 10

查询结果： ​

img

问题描述： ​

很明显的问题在于就算线上绑定的学生再怎么多也不可能查询出3千万的这种数据。正确的的数字应该查询出来为：35264。经过观察后发现一个问题，这个数字很接近1000倍。刚好这个考试计划下绑定了10个班，而这考试计划中的考试数量是100个。考试的人数为（每场考试的人数总和），考试是每个班级对应每个考试项目产生的。刚好为1000个考试项目所以这的数据会很接近1000倍。

为什么会产生这个问题？： ​

产生问题的原因在于sql语句中的GROUP BY，即使有 GROUP BY t.id，当存在多表 JOIN 时，SUM/COUNT 仍可能错误，因为：

  1. 聚合顺序问题：GROUP BY 是在所有 JOIN 之后 进行的
  2. 数据膨胀阶段：edu_exam (t4) 和 edu_exam_detail (t5) 的 JOIN 会先产生笛卡尔积
  3. 错误聚合时机：SUM(t4.student_cnt) 在笛卡尔积膨胀后的数据集上计算

假设原始数据如下：

表数据量关联关系
edu_exam_plan (t)1 条id=100
edu_exam (t4)10 条exam_plan_id=100，每条 student_cnt=3526
edu_exam_detail (t5)1000 条plan_id=100

执行过程分析： ​

  1. JOIN 阶段：
Java
t JOIN t4 => 产生 10 条记录（t4有10条）
JOIN t5   => 产生 10*1000=10,000 条临时记录
  1. GROUP BY 阶段：
    1. 虽然最终按 t.id 分组，但 SUM(t4.student_cnt) 会在 10,000 条临时记录 上累加
    2. 实际计算：3526 * 1000 = 3,526,000（被错误放大1000倍）

问题的核心： ​

SQL 执行顺序的本质问题 ​

SQL 的执行顺序是固定的，且 聚合操作（GROUP BY）在 JOIN 之后发生。以下是标准 SQL 的执行顺序：

Java
FROM → JOIN → WHERE → GROUP BY → SELECT（包含聚合函数） → ORDER BY → LIMIT

这意味着：

  • 所有 JOIN 操作先完成（包括产生笛卡尔积）
  • 数据膨胀后才进行 GROUP BY 分组
  • 聚合函数（SUM/COUNT）在膨胀后的数据集上计算

数据膨胀阶段的数学本质 ​

假设：

  • 主表 edu_exam_plan（t）有 1 条记录（id=100）
  • edu_exam（t4）有 N 条关联记录（exam_plan_id=100）
  • edu_exam_detail（t5）有 M 条关联记录（plan_id=100）

JOIN 后的临时表数据量：

Java
临时表行数 = N（t4记录数） × M（t5记录数）

例如：

  • t4 有 10 条记录 → N=10
  • t5 有 1000 条记录 → M=1000
  • JOIN 后临时表行数 = 10 × 1000 = 10,000 行
  1. **错误聚合时机的具象化演示

假设每条 t4.student_cnt 的值为 3526：

步骤计算过程结果
JOIN 阶段t4 的 10 条记录 × t5 的 1000 条产生 10,000 行临时数据
SUM 计算阶段3526 × 1000（每条 t4 记录被重复计算 1000 次）35,260,000
GROUP BY 阶段按 t.id 合并所有临时行最终显示 35,260,000

关键问题： GROUP BY 只是将已经错误放大的结果 按 id 合并显示，但 SUM 的基数错误已经无法挽回。

解决方案 ​

方案1： ​

拆分sql，查询出列表后进行循环查询数据，在java中使用stream流进行计算总数。（也是本次问题的解决方案）

C++
List<EduExamPlanQueryResponse> responses = selectJoinList(EduExamPlanQueryResponse.class, wrapper);
// 查询进度百分比
responses.forEach(item -> {
    // 查询考试计划关联的考试信息
    if(item.getPlanStatus()>0){
        // 查询成绩数量
        LambdaQueryWrapper<EduExamDetail> detailWrapper = new LambdaQueryWrapper<>();
        detailWrapper.eq(EduExamDetail::getPlanId, item.getId());
        long detailNum = eduExamDetailService.count(detailWrapper);
        item.setNumerator((int) detailNum);
        // 查询总成绩数量（每个项目的考试人数总和）
        LambdaQueryWrapper<EduExam> sumWrapper = new LambdaQueryWrapper<>();
        sumWrapper.eq(EduExam::getExamPlanId, item.getId());
        sumWrapper.select(EduExam::getStudentCnt);
        List<EduExam> exams = examMapper.selectList(sumWrapper);
        long sumNum = exams.stream().mapToLong(EduExam::getStudentCnt).sum();
        item.setDenominator((int) sumNum);
    }else {
        // 未启动的考试计划都为百分之0
        item.setDenominator(1);
        item.setNumerator(0);
    }
});

方案2： ​

先对关联表单独聚合（子查询），再与主表 JOIN。

SQL
SELECT 
  t.id,...,COALESCE(t4_sum.student_sum, 0) AS denominator, -- 使用子查询聚合结果COALESCE(t5_count.detail_count, 0) AS numerator
FROM edu_exam_plan t
-- 关键修改：先对t4表做聚合LEFT JOIN (SELECT exam_plan_id, SUM(student_cnt) AS student_sum
  FROM edu_exam
  WHERE delete_flag = 0GROUP BY exam_plan_id  -- 先按exam_plan_id聚合) t4_sum ON t4_sum.exam_plan_id = t.id
-- 关键修改：先对t5表做聚合LEFT JOIN (SELECT plan_id, COUNT(id) AS detail_count
  FROM edu_exam_detail
  WHERE delete_flag = 0GROUP BY plan_id  -- 先按plan_id聚合) t5_count ON t5_count.plan_id = t.id
-- 其他JOIN和WHERE条件保持不变WHERE (...)GROUP BY t.id  -- 主表GROUP BY可保留（实际已不需要）ORDER BY t.create_time DESCLIMIT 10;
最近更新