t2表存在数十万条记录时关联子查询取MAX值执行过慢如何优化

• 原SQL存在基础语法错误：子查询的WHERE条件中，t1.id = t2.due_id与t2.deleted_at IS NULL之间缺失AND连接符，直接执行会抛出语法异常。
• 核心性能损耗来自N+1查询问题：当前写法属于关联标量子查询，执行逻辑是遍历t1表每一条符合deleted_at IS NULL的记录时，都要单独去t2表做一次匹配查询。如果t1符合条件的记录有上千条，就会对数十万级的t2表触发上千次查询，重复扫描的开销会被急剧放大。
• 缺失适配索引导致全表扫描：如果没有对应索引支撑，每次子查询匹配t2记录时都会走全表扫描，磁盘IO开销极高。

1. 改写SQL逻辑，消除N+1重复查询 #

把逐行触发的关联子查询，调整为先对t2做一次性聚合、再左关联t1的写法，只需要扫描一次t2表就能拿到所有需要的聚合结果，改写后的SQL如下：

SELECT 
  t1.id,
  t2_agg.last_paid_date AS lastPaidDate
FROM t1
LEFT JOIN (
  SELECT
    due_id,
    MAX(paid_date) AS last_paid_date
  FROM t2
  WHERE deleted_at IS NULL
  GROUP BY due_id
) t2_agg ON t1.id = t2_agg.due_id
WHERE t1.deleted_at IS NULL;

这种写法兼容所有主流关系型数据库（MySQL 5.x/8.x、PostgreSQL、SQL Server等），不依赖高版本专属特性。如果你的环境是MySQL 8.0及以上版本，也可以用窗口函数改写，性能表现和上述写法基本一致。

2. 建覆盖索引，从存储层降低扫描开销 #

索引是优化这类聚合关联查询性价比最高的手段，添加两个联合索引即可：

• 针对t1表创建索引：idx_t1_del_id (deleted_at, id)
  这个索引可以直接过滤出t1表中未删除的记录id，不需要回表查询t1主表数据，t1过滤阶段的开销可以降到极低。
• 针对t2表创建索引：idx_t2_dueid_del_paid (due_id, deleted_at, paid_date)
  这个是覆盖索引：过滤t2未删除记录、按due_id分组、计算MAX(paid_date)的所有操作都可以直接在索引上完成，完全不需要回表扫描t2主表数据，t2聚合阶段的开销会下降90%以上。

按上述方案调整后，即使t2表数据量涨到百万级，查询耗时也能稳定在毫秒级。

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