MySQL多表JOIN聚合磁盘溢出？分批聚合实战：某教育平台50万行数据从崩溃到稳定

作者的话：

一、事故背景

某教育集团的数据中台需要从DW层聚合生成6张DM层指标表。其中最大的4张表：

聚合SQL长这样：

INSERTINTOdm_campus_subject_fail_rate(...)SELECTs.subject_type,s.campus_id,s.campus_name,t.exam_id,t.exam_name,COUNT(DISTINCTs.student_id)AStotal_students,COUNT(DISTINCTCASEWHENs.score_statusNOTIN('pass','exempt')THENs.student_idELSENULLEND)ASfail_students,ROUND(...)ASfail_rate,NOW(),NOW(),NOW()FROMdw_student sLEFTJOINdw_enrollment rONs.student_id=r.student_idLEFTJOINdw_exam_score tONs.student_id=t.student_idWHEREs.is_deleted=0ANDr.is_deleted=0ANDt.is_deleted=0GROUPBYs.subject_type,s.campus_id,s.campus_name,t.exam_id,t.exam_name;

三张DW层表JOIN后GROUP BY，数据量约50万行。

结果？MySQL直接把RDS磁盘打满（小规格实例，磁盘仅50GB），实例卡死，同步任务崩溃。

二、为什么临时表会炸？

2.1 MySQL临时表机制

MySQL在执行GROUP BY时，如果内存放不下（超过tmp_table_size），会把中间结果写到磁盘上的临时文件。三表JOIN + GROUP BY的执行过程：

Step 1: dw_student JOIN dw_enrollment → 中间结果A（约30万行） Step 2: 中间结果A JOIN dw_exam_score → 中间结果B（约50万行） Step 3: 中间结果B GROUP BY → 临时表C（磁盘上） Step 4: 临时表C INSERT INTO dm表

中间结果B有50万行，在小规格RDS上，临时表可能占满剩余磁盘空间。

2.2 RDS磁盘监控

磁盘使用率：96.2% ← 接近满载 IOPS：接近上限 CPU：100% MySQL状态：Waiting for disk space

三、优化尝试（失败篇）

3.1 尝试1：调大tmp_table_size

SETSESSIONtmp_table_size=1073741824;-- 1GBSETSESSIONmax_heap_table_size=1073741824;-- 1GB

结果：小规格RDS内存有限，1GB的内存临时表放不下50万行中间结果，还是溢出到磁盘。

3.2 尝试2：优化SQL写法

用子查询预先过滤：

INSERTINTOdm_campus_subject_fail_rate(...)SELECT...FROM(SELECTstudent_id,subject_type,campus_id,campus_name,score_statusFROMdw_studentWHEREis_deleted=0)sINNERJOIN(SELECTstudent_idFROMdw_enrollmentWHEREis_deleted=0)rONs.student_id=r.student_idINNERJOIN(SELECTstudent_id,exam_id,exam_nameFROMdw_exam_scoreWHEREis_deleted=0)tONs.student_id=t.student_idGROUPBY...;

结果：MySQL优化器并不总是按子查询顺序执行，执行计划可能还是全表扫描。临时表依然巨大。

四、最终方案：分批聚合

4.1 核心思路

不要一次性聚合所有数据，而是按维度分批聚合。

原始SQL是一次性聚合所有校区+学科组合，改为：每次只聚合一个校区（或一个分类组合）的数据，循环执行直到覆盖所有维度。

4.2 实现代码

defexecute_dm_batch_aggregation(conn,config):cursor=conn.cursor()cursor.execute(""" SELECT DISTINCT subject_type, campus_id, campus_name FROM dw_student WHERE is_deleted = 0 """)campuses=cursor.fetchall()logger.info(f"总校区数:{len(campuses)}")execute_sql(conn,"TRUNCATE TABLE dm_campus_subject_fail_rate")total_rows=0batch_size=10fori,campusinenumerate(campuses):conn=reconnect_if_needed(conn,config)sql=""" INSERT INTO dm_campus_subject_fail_rate (...) SELECT %s, %s, %s, t.exam_id, t.exam_name, COUNT(DISTINCT s.student_id), COUNT(DISTINCT CASE WHEN s.score_status NOT IN ('pass', 'exempt') THEN s.student_id ELSE NULL END), ROUND(...), NOW(), NOW(), NOW() FROM dw_student s LEFT JOIN dw_enrollment r ON s.student_id = r.student_id LEFT JOIN dw_exam_score t ON s.student_id = t.student_id WHERE s.is_deleted = 0 AND r.is_deleted = 0 AND t.is_deleted = 0 AND s.subject_type = %s AND s.campus_id = %s GROUP BY t.exam_id, t.exam_name """params=(campus['subject_type'],campus['campus_id'],campus['campus_name'],campus['subject_type'],campus['campus_id'])rows=execute_sql(conn,sql,params,config=config)total_rows+=rowsif(i+1)%batch_size==0:release_temp_tables(conn)logger.info(f"进度:{i+1}/{len(campuses)}, 已插入{total_rows}行")gc.collect()cursor.close()deleted=execute_sql(conn,"DELETE FROM dm_campus_subject_fail_rate WHERE total_students < 50")release_temp_tables(conn)

4.3 为什么分批聚合有效？

五、配套优化措施

5.1 Session参数优化

defoptimize_session(conn):withconn.cursor()ascursor:cursor.execute("SET SESSION tmp_table_size = 1073741824")# 1GBcursor.execute("SET SESSION max_heap_table_size = 1073741824")# 1GBcursor.execute("SET SESSION sort_buffer_size = 268435456")# 256MBcursor.execute("SET SESSION join_buffer_size = 268435456")# 256MBconn.commit()

5.2 主动释放临时表

defrelease_temp_tables(conn):try:withconn.cursor()ascursor:cursor.execute("FLUSH TABLES")conn.commit()except:pass

FLUSH TABLES会强制MySQL关闭所有不再使用的临时表，立即释放磁盘空间。

5.3 Python垃圾回收

importgcif(i+1)%batch_size==0:gc.collect()

5.4 连接保活与重连

defreconnect_if_needed(conn,config):try:withconn.cursor()ascursor:cursor.execute("SELECT 1")returnconnexcept:returnpymysql.connect(**config)

六、效果对比

七、总结

多表聚合磁盘溢出的根因是一次性处理的数据量超过了MySQL临时表的承载能力。分批聚合的本质是把一个大问题拆成N个小问题，每个小问题都在MySQL可控范围内。

分批聚合的三个关键点：

1. 选择合适的分批维度：按分类维度（如校区、科室、学科）分批，确保每批数据量均匀
2. 每批后释放资源：FLUSH TABLES+gc.collect()，防止资源累积
3. 连接保活：长时间执行必须处理连接断开的情况

💡一句话总结：当MySQL告诉你"磁盘满了"，不是让你加磁盘，而是让你把一个大SQL拆成N个小SQL。分批聚合，大道至简。

这篇文章如果帮到了你，点赞收藏是对作者最大的支持！有多表聚合经验的同学，评论区分享你的方案~