PostgreSQL百万级表关联优化：快速生成用户电影聚合表

一、基础索引优化 #

• 给MOVIES表的user_id字段建立单独普通索引，若频繁按user_id聚合，可升级为联合索引(user_id, object)，实现索引覆盖查询，避免回表读取数据，大幅降低IO开销。
• 确认USERS表的id主键索引无碎片，保证主键查询效率。

二、改写SQL，替换低效嵌套查询 #

用分组聚合替代关联子查询 #

原嵌套查询（类似下方写法）会对每个用户执行一次子查询，百万级数据下相当于百万次独立查询，效率极低：

INSERT INTO USERS_MOVIES(user_id, movies)
SELECT u.id, (
    SELECT JSON_ARRAYAGG(m.object)
    FROM MOVIES m
    WHERE m.user_id = u.id
)
FROM USERS u;

改为直接分组聚合的写法，将关联逻辑合并为单次扫描：

INSERT INTO USERS_MOVIES(user_id, movies)
SELECT 
    m.user_id, 
    JSON_ARRAYAGG(m.object) AS movies
FROM MOVIES m
JOIN USERS u ON m.user_id = u.id
GROUP BY m.user_id;

若需包含无电影记录的用户（movies字段设为[]），改用左连接：

INSERT INTO USERS_MOVIES(user_id, movies)
SELECT 
    u.id, 
    COALESCE(JSON_ARRAYAGG(m.object), '[]') AS movies
FROM USERS u
LEFT JOIN MOVIES m ON u.id = m.user_id
GROUP BY u.id;

三、数据库临时配置优化 #

• 关闭目标表USERS_MOVIES的自动提交与事务日志实时刷盘（仅临时操作，完成后恢复）：
  以MySQL为例，执行以下命令：

  SET autocommit = 0;
  SET innodb_flush_log_at_trx_commit = 0;
  

  插入完成后务必改回innodb_flush_log_at_trx_commit = 1，保障数据安全性。
• 先禁用USERS_MOVIES的索引，插入完成后再重建。批量插入时维护索引会消耗大量资源，先插数据后建索引的效率远高于边插边建。

四、分批处理数据 #

若单条SQL仍超时，按user_id范围拆分批次处理，示例如下：

-- 每次处理10000个用户，可根据数据库性能调整批次大小
INSERT INTO USERS_MOVIES(user_id, movies)
SELECT 
    u.id, 
    COALESCE(JSON_ARRAYAGG(m.object), '[]') AS movies
FROM USERS u
LEFT JOIN MOVIES m ON u.id = m.user_id
WHERE u.id BETWEEN 1 AND 10000
GROUP BY u.id;

可通过Shell、Python脚本自动循环生成批次SQL，减少手动操作成本。

五、选择原生JSON聚合函数 #

避免手动拼接JSON字符串（如用GROUP_CONCAT加引号），优先使用数据库原生JSON聚合函数：

• MySQL用JSON_ARRAYAGG（需5.7及以上版本支持）；
• PostgreSQL用json_agg或jsonb_agg，jsonb类型的聚合效率更高。

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