SQL自关联查询耗时过长求助：80万行表INSERT SELECT无限运行

首先，你的SQL语句跑这么久甚至跑不完，核心问题有两个：自连接导致的中间结果集爆炸，以及可能存在的逻辑错误，咱们一步步拆解：

为什么原语句这么慢？ #

你的语句是对80万行的t1做自连接，逻辑上是把t1的每一行和t1的所有行做匹配，筛选出user_id和card_id相同、且n1.card_version_id > n2.card_version_id的n2行。这里有两个致命问题：

1. 无索引导致全表扫描+笛卡尔积：如果t1没有针对user_id, card_id, card_version_id的索引，数据库会先把两个t1的所有行做笛卡尔积（80万×80万=6.4万亿行的中间数据），再过滤条件——这完全超出了数据库的处理能力，自然跑几个小时都没结果。
2. 重复插入同一行：假设某个user_id+card_id组合下有3个版本号（1、2、3），那么版本1的行会被匹配两次（和版本2、3的n1行），版本2的行会被匹配一次（和版本3的n1行），最终t2里会出现大量重复行——这大概率不是你想要的结果吧？

优化方案（分场景） #

场景1：你想插入「每个user_id+card_id组中，不是最大card_version_id的所有行」 #

这是最符合原语句意图的合理需求，用窗口函数替代自连接，性能会提升几个数量级：

INSERT INTO t2
SELECT *
FROM (
    SELECT 
        *,
        -- 按user_id+card_id分组，组内按version降序排名
        ROW_NUMBER() OVER (PARTITION BY user_id, card_id ORDER BY card_version_id DESC) AS rn
    FROM t1
) sub_query
-- 取排名>1的行，也就是排除每组中version最大的行
WHERE rn > 1;

这个语句只需要扫描t1一次，通过窗口函数完成分组排序，避免了自连接的巨大开销，而且每个符合条件的行只会插入一次。

场景2：你确实需要保留重复插入的逻辑（比如要记录每个行对应的所有更小版本的关联） #

如果这是你的预期需求，那必须给t1添加合适的索引来优化自连接：

-- 创建联合索引，覆盖WHERE条件的所有字段
CREATE INDEX idx_t1_user_card_version ON t1(user_id, card_id, card_version_id);

有了这个索引，数据库可以快速定位到每个user_id+card_id组内的行，避免全表扫描，大幅减少中间匹配的时间。但即使这样，结果集可能依然很大，建议分批插入：

-- 按user_id分段处理，比如每次处理10000个user_id
INSERT INTO t2
SELECT n2.* 
FROM t1 AS n1
JOIN t1 AS n2 
  ON n1.user_id = n2.user_id 
  AND n1.card_id = n2.card_id 
  AND n1.card_version_id > n2.card_version_id
WHERE n1.user_id BETWEEN 1 AND 10000; -- 替换为实际的分段范围

通用优化建议 #

• 先确认需求：如果不确定自己要什么，先跑个小范围的测试查询（比如加LIMIT 100）看看结果是否符合预期。
• 监控数据库状态：跑查询时可以看看数据库的CPU、内存、磁盘IO使用率，判断是不是资源瓶颈。
• 避免大事务：如果插入的行数很多，分批插入可以避免事务过大导致的锁问题和日志膨胀。

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