如何用SQL为3亿条device_id生成全视频分辨率关联记录？

完全可以用SQL实现这个需求，核心是通过**交叉连接（CROSS JOIN）**让每个device_id与所有分辨率做全量匹配，以下是具体实现方案和性能优化建议：

核心实现语句 #

需求本质是把device_id表和固定分辨率列表做笛卡尔积，SQL中用CROSS JOIN即可完成。

1. 生成分辨率列表 #

不同SQL方言的写法略有差异，以下是常见数据库的实现方式：

• 支持VALUES子句的数据库（PostgreSQL、BigQuery、SQL Server等）：

SELECT resolution
FROM (
    VALUES ('360p'), ('480p'), ('540p'), ('720p'), ('1080p')
) AS res(resolution)

• MySQL（8.0之前版本不支持直接用VALUES生成子表）：

SELECT '360p' AS resolution UNION ALL
SELECT '480p' UNION ALL
SELECT '540p' UNION ALL
SELECT '720p' UNION ALL
SELECT '1080p'

2. 交叉连接生成全量记录 #

将device_id表与上面的分辨率列表做交叉连接，得到最终结果：

SELECT 
    d.device_id,
    r.resolution AS video_resolution
FROM 
    device_ids d
CROSS JOIN (
    -- 替换为对应数据库的分辨率生成语句
    VALUES ('360p'), ('480p'), ('540p'), ('720p'), ('1080p')
) AS r(resolution)

针对3亿条数据的性能优化 #

由于数据量巨大（最终生成15亿条记录），直接执行可能会遇到性能或内存问题，可通过以下方式优化：

• 分批次处理：按device_id的范围或哈希值拆分任务，比如每次处理device_id BETWEEN 1 AND 60000000，分批执行后合并结果，降低单次处理的数据量。
• 调整数据库执行配置：确保数据库使用磁盘化执行计划，避免将全量数据加载到内存。例如MySQL调整join_buffer_size、PostgreSQL调整work_mem时，不要设置过大，防止内存溢出。
• 利用分区表：如果目标表支持分区（如按device_id哈希分区、按分辨率分区），可以大幅提升写入和后续查询的性能。
• 临时禁用冗余索引：执行交叉连接前，临时禁用device_id表上非必要的索引，减少IO开销，执行完成后再恢复。
• 直接写入目标存储：尽量避免中间结果落地，直接将查询结果写入目标表或存储系统，减少数据拷贝的开销。

与Spark方案的对比 #

Spark出现OOM通常是因为Executor内存不足以承载全量数据的shuffle或缓存，而SQL数据库（尤其是OLAP类数据库如Hive、Presto、BigQuery）本身针对大数据量的磁盘级处理做了优化，只要配置合理，更适合这类大规模笛卡尔积场景。

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