从含多年度数据的4000万条大表中插入每组最新3条记录

现有一张存储多年度数据的大表，总记录数达4000万条，每年新增超1000万条数据。表结构及示例数据如下：

Id name date
------------------
1  a    2018-01-01
2  b    2018-02-01
3  a    2018-6-01
4  a    2018-07-01
5  a    2018-10-01

6  a    2019-01-01
7  b    2019-02-01
8  a    2019-6-01
9  a    2019-07-01
10 a    2019-10-01
11 a    2020-01-01
12 b    2020-02-01
13 a    2020-6-01
14 a    2020-07-01
15 a    2020-10-01

需要将表中每个name分组下的最新3条记录插入至另一张表，但常规SQL查询因数据量过大无法正常执行。预期输出如下：

Id  name date
----------------
15  a    2020-10-01
14  a    2020-07-01
13  a    2020-6-01

7   b    2019-02-01
2   b    2018-02-01

针对4000万级大表，核心优化思路是避免全表扫描+利用索引加速分组排序，以下是可落地的实现方案：

1. 前置必做：添加复合索引 #

先给原表创建复合索引，这是所有方案性能的基础，能让数据库快速定位每个name下的最新记录：

CREATE INDEX idx_name_date ON 原表名(name, date DESC);

2. 数据库专属高效查询 #

MySQL/MariaDB 8.0+（支持窗口函数） #

用窗口函数ROW_NUMBER()配合索引，避免低效的子查询关联：

INSERT INTO 目标表(Id, name, date)
SELECT Id, name, date
FROM (
    SELECT 
        Id, name, date,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY date DESC) AS rn
    FROM 原表名
) t
WHERE rn <= 3;

MySQL 5.x（无窗口函数） #

用关联查询替代窗口函数，同样依赖上述复合索引：

INSERT INTO 目标表(Id, name, date)
SELECT t1.Id, t1.name, t1.date
FROM 原表名 t1
LEFT JOIN 原表名 t2 
    ON t1.name = t2.name AND t2.date > t1.date
GROUP BY t1.Id, t1.name, t1.date
HAVING COUNT(t2.Id) < 3;

PostgreSQL 版本 #

PostgreSQL对窗口函数的优化更成熟，直接使用即可：

INSERT INTO 目标表(id, name, date)
SELECT id, name, date
FROM (
    SELECT 
        id, name, date,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY date DESC) AS rn
    FROM 原表名
) sub
WHERE rn <= 3;

3. 极端大表：分批处理 #

如果上述方法仍因数据量过大超时，可按name范围拆分任务，避免一次性加载全表数据：

-- 示例：按name首字母分批处理
INSERT INTO 目标表(Id, name, date)
SELECT Id, name, date
FROM (
    SELECT 
        Id, name, date,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY date DESC) AS rn
    FROM 原表名
    WHERE name BETWEEN 'a' AND 'm'
) t
WHERE rn <=3;

-- 处理剩余批次
INSERT INTO 目标表(Id, name, date)
SELECT Id, name, date
FROM (
    SELECT 
        Id, name, date,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY date DESC) AS rn
    FROM 原表名
    WHERE name BETWEEN 'n' AND 'z'
) t
WHERE rn <=3;

也可通过脚本（Shell/Python）遍历所有name值，逐个处理单分组数据，进一步降低内存占用。

4. 插入性能优化 #

• 执行插入前关闭目标表的触发器、外键约束，完成后再重新开启；
• 若目标表已有数据，添加ON DUPLICATE KEY UPDATE（MySQL）或ON CONFLICT DO NOTHING（PostgreSQL）避免重复插入；
• 选择业务低峰期执行，减少对线上服务的影响。

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