MySQL带GROUP_CONCAT的查询关联3张1TB大表导出数据耗时过长求助

• 语法逻辑错误：缺少FROM关键字、OrderLines表的JOIN条件写法错误、使用GROUP_CONCAT但未添加对应GROUP BY orderId子句，要么执行报错要么输出结果不符合预期，额外增加无效计算开销。
• 关联逻辑不合理：三个TB级大表直接JOIN后再聚合，会产生严重的数据膨胀：单个orderId对应多条Order记录、多条OrderLines记录，JOIN后中间结果行数是各表同orderId行数的乘积，远大于原始数据量，内存不足以承载时会频繁刷磁盘，导致耗时剧增。
• 索引缺失：三个表的关联键orderId如果没有建立索引，会触发全表扫描，TB级表的全表扫描开销极高。
• JSON拼接逻辑低效且不安全：手动通过多层CONCAT拼接JSON，计算效率低，且未处理字段值中的双引号转义，容易生成非法JSON，同时默认GROUP_CONCAT长度上限很低，会导致JSON被截断，需要额外重试开销。

• 补充关联索引：给Order、Address、OrderLines三张表的orderId字段建立索引，可同时把查询需要的业务字段加入索引做成覆盖索引，避免回表查询。
• 调整执行逻辑，先聚合再关联：先单独对OrderLines按orderId聚合生成对应的JSON字段，再和另外两张表关联，大幅减少中间结果的数据量。
• 优先使用数据库内置JSON函数：如果使用的是MySQL8.0+等支持JSON聚合函数的版本，直接用JSON_OBJECTAGG生成LineDetails，比手动拼接效率高30%以上，且自动处理转义问题，避免格式错误。
• 调整聚合参数：提前调大group_concat_max_len会话参数，避免JSON内容被截断。
• 分批导出：TB级数据不建议一次性导出，可按orderId范围拆分多个查询分批导出，避免单次请求占用过多数据库资源，也方便失败重试。

-- 调整会话级GROUP_CONCAT长度上限，按需调整大小
SET SESSION group_concat_max_len = 1024000;

SELECT 
  a.orderId,
  a.item,
  b.Adress,
  JSON_OBJECTAGG(e.likeKey, e.lineValue) AS LineDetails
INTO OUTFILE '/datadrive/tmp/testfile.csv' 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM `Order` a
INNER JOIN Address b 
  ON a.orderId = b.orderId
INNER JOIN OrderLines e 
  ON a.orderId = e.orderId
GROUP BY a.orderId, a.item, b.Adress;

如果是更低版本不支持JSON_OBJECTAGG，可以用手动拼接的方式，但要补充转义逻辑：

SET SESSION group_concat_max_len = 1024000;

SELECT 
  a.orderId,
  a.item,
  b.Adress,
  CONCAT('{', GROUP_CONCAT(CONCAT('"', likeKey, '":"', REPLACE(lineValue, '"', '\\"'), '"')), '}') AS LineDetails
INTO OUTFILE '/datadrive/tmp/testfile.csv' 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM `Order` a
INNER JOIN Address b 
  ON a.orderId = b.orderId
INNER JOIN OrderLines e 
  ON a.orderId = e.orderId
GROUP BY a.orderId, a.item, b.Adress;

如果数据量实在太大，建议换用Spark等大数据计算组件读取三张表做关联导出，性能会比单数据库处理高一个数量级。

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