作者的话一、事故背景某教育集团的数据中台需要从DW层聚合生成6张DM层指标表。其中最大的4张表表名说明最终行数聚合方式dm_campus_subject_fail_rate校区学科不及格率约5万行三表JOIN GROUP BYdm_campus_subject_avg_score校区学科平均分约2.6万行三表JOIN GROUP BYdm_national_subject_avg_score全国学科平均分约2.1万行两表JOIN GROUP BYdm_national_subject_fail_rate全国学科不及格率约1500行两表JOIN GROUP BY聚合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_idr.student_idLEFTJOINdw_exam_score tONs.student_idt.student_idWHEREs.is_deleted0ANDr.is_deleted0ANDt.is_deleted0GROUPBYs.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接近上限 CPU100% MySQL状态Waiting for disk space三、优化尝试失败篇3.1 尝试1调大tmp_table_sizeSETSESSIONtmp_table_size1073741824;-- 1GBSETSESSIONmax_heap_table_size1073741824;-- 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_deleted0)sINNERJOIN(SELECTstudent_idFROMdw_enrollmentWHEREis_deleted0)rONs.student_idr.student_idINNERJOIN(SELECTstudent_id,exam_id,exam_nameFROMdw_exam_scoreWHEREis_deleted0)tONs.student_idt.student_idGROUPBY...;结果MySQL优化器并不总是按子查询顺序执行执行计划可能还是全表扫描。临时表依然巨大。四、最终方案分批聚合4.1 核心思路不要一次性聚合所有数据而是按维度分批聚合。原始SQL是一次性聚合所有校区学科组合改为每次只聚合一个校区或一个分类组合的数据循环执行直到覆盖所有维度。4.2 实现代码defexecute_dm_batch_aggregation(conn,config):cursorconn.cursor()cursor.execute( SELECT DISTINCT subject_type, campus_id, campus_name FROM dw_student WHERE is_deleted 0 )campusescursor.fetchall()logger.info(f总校区数:{len(campuses)})execute_sql(conn,TRUNCATE TABLE dm_campus_subject_fail_rate)total_rows0batch_size10fori,campusinenumerate(campuses):connreconnect_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])rowsexecute_sql(conn,sql,params,configconfig)total_rowsrowsif(i1)%batch_size0:release_temp_tables(conn)logger.info(f进度:{i1}/{len(campuses)}, 已插入{total_rows}行)gc.collect()cursor.close()deletedexecute_sql(conn,DELETE FROM dm_campus_subject_fail_rate WHERE total_students 50)release_temp_tables(conn)4.3 为什么分批聚合有效对比项一次性聚合分批聚合单次SQL处理数据量50万行约5000-1万行临时表大小约5GB约50MB磁盘占用峰值接近100%15%执行时间崩溃无法完成约20-30分钟失败恢复从头来失败批次可重试五、配套优化措施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:passFLUSH TABLES会强制MySQL关闭所有不再使用的临时表立即释放磁盘空间。5.3 Python垃圾回收importgcif(i1)%batch_size0:gc.collect()5.4 连接保活与重连defreconnect_if_needed(conn,config):try:withconn.cursor()ascursor:cursor.execute(SELECT 1)returnconnexcept:returnpymysql.connect(**config)六、效果对比指标优化前优化后同步成功率0%必崩99%RDS磁盘峰值96%25%单次同步耗时无法完成约20-30分钟失败恢复从头来可从失败批次继续运维介入频率每次需人工处理基本无需介入七、总结多表聚合磁盘溢出的根因是一次性处理的数据量超过了MySQL临时表的承载能力。分批聚合的本质是把一个大问题拆成N个小问题每个小问题都在MySQL可控范围内。分批聚合的三个关键点选择合适的分批维度按分类维度如校区、科室、学科分批确保每批数据量均匀每批后释放资源FLUSH TABLESgc.collect()防止资源累积连接保活长时间执行必须处理连接断开的情况一句话总结当MySQL告诉你磁盘满了不是让你加磁盘而是让你把一个大SQL拆成N个小SQL。分批聚合大道至简。这篇文章如果帮到了你点赞收藏是对作者最大的支持有多表聚合经验的同学评论区分享你的方案~
