如何在SQL中优化基于子查询的大规模历史数据归档-SQL-PHP中文网

子查询归档易卡死因默认不去重、不控范围、不压平主键；应分两步：先物化收敛id到临时表，再关联查完整记录。

如何在sql中优化基于子查询的大规模历史数据归档

子查询在归档场景下极易放大行数、拖慢执行、甚至触发锁表或OOM——根本原因不是语法错，而是它默认不做去重、不控范围、不压平逻辑主键。

为什么归档时用子查询容易卡死或报错

历史归档常需“找出要归档的订单ID，再查完整记录”，但直接写 SELECT * FROM orders WHERE id IN (SELECT id FROM orders WHERE status = 'closed' AND create_time 会出问题：

子查询若未加 DISTINCT 或 GROUP BY，可能返回重复 id，外层查询自动去重但代价是临时表膨胀
MySQL 8.0+ 对 IN (subquery) 的优化有限，尤其子查询含 JOIN 或聚合时，常退化为循环嵌套（N×M）
子查询未限定分区或时间范围，易触发全表扫描，归档任务一跑就是几小时，还占满 buffer pool
Oracle 中类似写法配合 MERGE 更危险：ORA-30926 就是因为子查询输出不稳定，导致 ON 匹配抖动

用 EXISTS 替代 IN 是最简见效的改法

EXISTS 不生成中间结果集，只做布尔判断，对大表更友好。关键是要把子查询写成“半连接”语义，且确保关联字段有索引：

SELECT o.* 
FROM orders o 
WHERE EXISTS (
  SELECT 1 
  FROM order_status_log l 
  WHERE l.order_id = o.id 
    AND l.status = 'closed' 
    AND l.update_time

必须确保 order_status_log.order_id 有索引，否则 EXISTS 内部仍是全表扫
避免在子查询里用 SELECT * 或聚合函数，SELECT 1 足够
如果 order_status_log 是分区表（如按月），务必在子查询中显式加 AND l.dt >= '202312'，防止优化器忽略分区裁剪

归档前先物化中间结果到临时表

当逻辑复杂（比如要取每个订单最新一条归档快照），硬塞进子查询只会让优化器放弃挣扎。不如分两步：先收敛，再关联。

-- 第一步：安全压平源数据
CREATE TEMPORARY TABLE tmp_archive_ids AS
SELECT order_id
FROM (
  SELECT order_id,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) rn
  FROM archive_log 
  WHERE dt >= '202312' AND dt -- 第二步：用临时表驱动主表
INSERT INTO archive_db.orders 
SELECT o.* 
FROM orders o 
INNER JOIN tmp_archive_ids t ON o.id = t.order_id;