如何将MySQL表数据转换为嵌套JSON格式并兼顾大数据量下性能

方案1：MySQL直接生成JSON（优先推荐，性能最优） #

适合数据量较大、不需要额外业务处理的场景，直接在数据库层完成聚合，避免全量扁平数据传输到应用层，性能损耗最低，要求MySQL版本≥5.7。
示例SQL如下：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'sName', sName,
        'sId', sId,
        'pId', pId,
        'sNumber', sNumber,
        'Lists', list_json
    )
) AS final_json
FROM (
    -- 子查询按分组键聚合子列表
    SELECT 
        sName, sId, pId, sNumber,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'qty', qty,
                'price', CAST(price AS CHAR) -- 适配要求的字符串类型price
            )
        ) AS list_json
    FROM 你的表名
    GROUP BY sName, sId, pId, sNumber
) t;

该方案优势：

• 数据库原生聚合优化成熟，配合联合索引性能极高
• 仅返回最终JSON结果，大幅降低网络IO和应用层内存占用
  注意点：若单条JSON结果过大，需要调整MySQL的max_allowed_packet参数适配。

方案2：应用层哈希聚合（灵活可控） #

适合需要在聚合过程中添加自定义业务逻辑、数据库版本不支持JSON函数的场景，采用哈希表做分组索引，时间复杂度仅为O(n)，性能远高于常规遍历匹配的方式。
Python示例代码如下：

import json

# raw_data为从数据库查询到的原始行数据，支持流式读取逐行处理
raw_data = [
    ("test", 3, 1, 12345, 10, 10),
    ("test", 3, 1, 12345, 750, 9),
    # 其余原始数据行
]

group_map = {}
for row in raw_data:
    sName, sId, pId, sNumber, qty, price = row
    group_key = (sName, sId, pId, sNumber)
    # 哈希表O(1)匹配分组，避免循环遍历查找
    if group_key not in group_map:
        group_map[group_key] = {
            "sName": sName,
            "sId": sId,
            "pId": pId,
            "sNumber": sNumber,
            "Lists": []
        }
    group_map[group_key]["Lists"].append({"qty": qty, "price": str(price)})

# 转换为最终数组结构
result = list(group_map.values())
# 输出JSON字符串
print(json.dumps(result, indent=2))

该方案优势：

• 灵活性极高，可在聚合过程中插入任意业务处理逻辑
• 不受数据库版本限制，适配所有场景
  性能优化点：数据量超百万级时可采用流式读取，不需要把全量原始数据加载到内存，内存占用极低。

通用性能优化建议 #

• 数据库层面给分组字段sName, sId, pId, sNumber建立联合索引，分组聚合速度可提升数倍
• 数据量超千万级时建议做分页分批处理，避免单次请求压力过大
• 若使用PostgreSQL等其他数据库，也有对应原生JSON聚合函数，逻辑与MySQL方案一致

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