优化MySQL大数据集下生成笛卡尔积的查询：演职人员配对场景

一、原查询逻辑确认 #

你的原查询逻辑是正确的：通过关联movie与movie_cast、movie_crew表，为每部电影的所有演职人员（cast）和工作人员（crew）生成笛卡尔积配对，完全匹配预期结果。大数据场景下的性能瓶颈核心是单查询生成的结果集规模过大，以及关联过程中可能出现的全表扫描。

二、基础优化：索引优化 #

索引是提升关联查询性能的核心，需确保以下索引存在：

• movie表：主键movie_id（默认应已存在，若缺失需添加）
• movie_cast：复合索引(movie_id, person_id)，覆盖关联与查询所需字段
• movie_crew：复合索引(movie_id, person_id)，理由同上
• person表：主键person_id（默认应已存在）

创建索引语句：

CREATE INDEX idx_movie_cast_mid_pid ON movie_cast(movie_id, person_id);
CREATE INDEX idx_movie_crew_mid_pid ON movie_crew(movie_id, person_id);

三、正确使用LATERAL派生表（MySQL 8.0+支持） #

你之前用LATERAL结果不准确，大概率是写法有误。正确的LATERAL用法会为每部电影独立查询对应的cast和crew列表，再做笛卡尔积，能避免跨电影的无效关联，执行计划更高效：

SELECT 
    m.title,
    pc.person_name AS cast_member,
    pr.person_name AS crew_member
FROM 
    movie m
JOIN LATERAL (
    SELECT p.person_name
    FROM movie_cast mc
    JOIN person p ON mc.person_id = p.person_id
    WHERE mc.movie_id = m.movie_id
) pc ON 1=1
JOIN LATERAL (
    SELECT p.person_name
    FROM movie_crew mcc
    JOIN person p ON mcc.person_id = p.person_id
    WHERE mcc.movie_id = m.movie_id
) pr ON 1=1;

四、分批次处理结果集 #

大数据集下一次性生成所有笛卡尔积结果易引发内存溢出或磁盘IO过高，建议按movie_id分批次查询，示例如下：

-- 分批次处理，每次处理movie_id范围1-100的电影，可循环调整范围
SELECT 
    m.title,
    pc.person_name AS cast_member,
    pr.person_name AS crew_member
FROM 
    movie m
JOIN movie_cast mc ON m.movie_id = mc.movie_id
JOIN person pc ON mc.person_id = pc.person_id
JOIN movie_crew mcc ON m.movie_id = mcc.movie_id
JOIN person pr ON mcc.person_id = pr.person_id
WHERE m.movie_id BETWEEN 1 AND 100;

可结合程序循环逐步导出或处理结果，避免一次性负载过高。

五、预生成中间结果（物化视图替代方案） #

MySQL无原生物化视图，但可通过定时任务预计算并存储结果，适合非实时查询场景：

1. 创建结果表：

CREATE TABLE movie_cast_crew_pairs (
    movie_title VARCHAR(255),
    cast_member VARCHAR(255),
    crew_member VARCHAR(255),
    PRIMARY KEY (movie_title, cast_member, crew_member)
);

2. 定时执行数据更新（例如每日凌晨）：

TRUNCATE TABLE movie_cast_crew_pairs;
INSERT INTO movie_cast_crew_pairs
SELECT 
    m.title,
    pc.person_name AS cast_member,
    pr.person_name AS crew_member
FROM 
    movie m
JOIN movie_cast mc ON m.movie_id = mc.movie_id
JOIN person pc ON mc.person_id = pc.person_id
JOIN movie_crew mcc ON m.movie_id = mcc.movie_id
JOIN person pr ON mcc.person_id = pr.person_id;

后续直接从movie_cast_crew_pairs读取数据，性能会大幅提升。

六、过滤不必要数据（业务允许时） #

若研究场景可缩小范围，提前过滤能显著减少笛卡尔积规模，示例：

-- 仅处理有至少2名演职人员和2名工作人员的电影
SELECT 
    m.title,
    pc.person_name AS cast_member,
    pr.person_name AS crew_member
FROM 
    movie m
JOIN (
    SELECT movie_id FROM movie_cast GROUP BY movie_id HAVING COUNT(*) >=2
) mc_filter ON m.movie_id = mc_filter.movie_id
JOIN movie_cast mc ON m.movie_id = mc.movie_id
JOIN person pc ON mc.person_id = pc.person_id
JOIN (
    SELECT movie_id FROM movie_crew GROUP BY movie_id HAVING COUNT(*) >=2
) mcc_filter ON m.movie_id = mcc_filter.movie_id
JOIN movie_crew mcc ON m.movie_id = mcc.movie_id
JOIN person pr ON mcc.person_id = pr.person_id;

内容的提问来源于stack exchange，提问作者Sérgio Mergen