如何优化基于ar表RESTORE操作的arh表条件删除SQL语句？

现有arh和ar两张表，需针对每个accountMappingId，删除arh表中actionRequestId小于等于ar表中对应mappingId的'RESTORE'操作最大actionRequestId的记录，仅操作arh表，不影响ar表。accountMappingId可取值包括1001、1002、1003等。

表结构 #

创建表语句 #

create table arh (
  accountMappingId TEXT,
  actionRequestId integer,
  actionrequesthistoryid integer
);

CREATE TABLE ar (
  mappingId TEXT,
  actionRequestId integer,
  operation TEXT
);

测试数据 #

插入arh表记录 #

INSERT INTO arh VALUES ('1001', 1, 1);
INSERT INTO arh VALUES ('1001', 1, 2);
INSERT INTO arh VALUES ('1001', 2, 1);
INSERT INTO arh VALUES ('1001', 2, 2);
INSERT INTO arh VALUES ('1001', 3, 1);
INSERT INTO arh VALUES ('1001', 3, 2);
INSERT INTO arh VALUES ('1001', 4, 1);
INSERT INTO arh VALUES ('1001', 4, 2);
INSERT INTO arh VALUES ('1001', 5, 1);
INSERT INTO arh VALUES ('1001', 5, 2);
INSERT INTO arh VALUES ('1001', 6, 1);
INSERT INTO arh VALUES ('1001', 6, 2);
INSERT INTO arh VALUES ('1001', 7, 1);
INSERT INTO arh VALUES ('1001', 7, 2);
INSERT INTO arh VALUES ('1001', 8, 1);
INSERT INTO arh VALUES ('1001', 8, 2);
INSERT INTO arh VALUES ('1002', 1, 1);
INSERT INTO arh VALUES ('1002', 1, 2);
INSERT INTO arh VALUES ('1002', 2, 1);
INSERT INTO arh VALUES ('1002', 2, 2);
INSERT INTO arh VALUES ('1002', 3, 1);
INSERT INTO arh VALUES ('1002', 3, 2);

插入ar表记录 #

INSERT INTO ar VALUES ('1001', 1, 'COPY');
INSERT INTO ar VALUES ('1001', 2, 'REMOVE');
INSERT INTO ar VALUES ('1001', 3, 'RESTORE');
INSERT INTO ar VALUES ('1001', 4, 'COPY');
INSERT INTO ar VALUES ('1001', 5, 'REMOVE');
INSERT INTO ar VALUES ('1001', 6, 'RESTORE'); -- 1001对应的RESTORE操作最大actionrequestId
INSERT INTO ar VALUES ('1001', 7, 'COPY');
INSERT INTO ar VALUES ('1001', 8, 'REMOVE');
INSERT INTO ar VALUES ('1002', 1, 'COPY');
INSERT INTO ar VALUES ('1002', 2, 'REMOVE');
INSERT INTO ar VALUES ('1002', 3, 'RESTORE'); -- 1002对应的RESTORE操作最大actionrequestId

现有删除语句 #

delete from arh 
where (arh.actionRequestId, arh.accountMappingId) in (
select arh.actionRequestId, new_table.mappingId
from arh 
inner join (
  SELECT mappingId, max(actionrequestid) as actionrequestid
  FROM ar 
  where operation = 'RESTORE'
  group by mappingid
  ) 
as new_table
on arh.accountMappingId = new_table.mappingId
and arh.actionRequestId <= new_table.actionRequestId
order by arh.actionRequestId, new_table.mappingId)

优化方案 #

你的现有语句有几个可以精简和提效的点：

1. 子查询里不需要再次关联arh表，直接通过关联判断条件即可，避免重复扫描arh数据
2. 去掉ORDER BY子句——IN子查询的排序对删除逻辑毫无意义，只会增加额外计算开销

优化版1：使用EXISTS关联子查询 #

逻辑更直观，避免冗余的表关联：

DELETE FROM arh
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT mappingId, MAX(actionRequestId) AS max_restore_id
        FROM ar
        WHERE operation = 'RESTORE'
        GROUP BY mappingId
    ) AS restore_max
    WHERE arh.accountMappingId = restore_max.mappingId
      AND arh.actionRequestId <= restore_max.max_restore_id
)

优化版2：使用JOIN直接关联删除 #

如果你的数据库支持（如MySQL、PostgreSQL），这种写法性能通常更优，查询优化器更容易生成高效执行计划：

DELETE arh
FROM arh
JOIN (
    SELECT mappingId, MAX(actionRequestId) AS max_restore_id
    FROM ar
    WHERE operation = 'RESTORE'
    GROUP BY mappingId
) AS restore_max ON arh.accountMappingId = restore_max.mappingId
WHERE arh.actionRequestId <= restore_max.max_restore_id

额外性能优化建议 #

• 给ar表创建联合索引：(mappingId, operation, actionRequestId)，大幅提升分组求最大值的查询效率
• 给arh表创建联合索引：(accountMappingId, actionRequestId)，加速删除时的条件匹配

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