优化含子查询的MySQL单表查询——百万级数据性能优化求助

问题根源分析 #

原查询使用相关子查询，会对主查询过滤出的每条记录（约49.9万条）单独执行一次子查询，即使子查询用到了索引，累计执行次数过多也会导致耗时剧增。同时主查询仅使用type单字段索引，过滤范围过大，无法有效缩小数据量。

优化方案 #

1. 替换相关子查询为JOIN（批量计算聚合值） #

将逐行执行的子查询改为一次性计算所有polizzennummer的最小firstdelivery，再与主表关联，大幅减少执行次数：

SELECT I.id
FROM abc.items AS I
JOIN (
    -- 预计算每个polizzennummer对应的最小firstdelivery
    SELECT polizzennummer, MIN(firstdelivery) AS min_firstdelivery
    FROM abc.items
    GROUP BY polizzennummer
) AS agg ON I.polizzennummer = agg.polizzennummer 
       AND I.firstdelivery = agg.min_firstdelivery
WHERE I.type = 10 
  AND I.revisit < '2022-11-17T00:00:00Z'
LIMIT 50;

2. 优化索引，避免回表与无效过滤 #

现有索引无法覆盖主查询的所有条件，建议新增覆盖联合索引：

CREATE INDEX idx_type_revisit_polizzennum_fd_id 
ON abc.items (type, revisit, polizzennummer, firstdelivery, id);

该索引的作用：

• 先通过type=10快速定位数据分区
• 再通过revisit < 时间进一步缩小范围
• 直接从索引中获取polizzennummer、firstdelivery和id，无需回表查询原数据
• 关联聚合子查询时无需额外读取表数据

另外，现有idx_abc_polizzennummer_firstdelivery索引已能满足聚合子查询的需求（GROUP BY和MIN计算可直接通过索引完成），无需修改。

3. 可选：先过滤再关联（进一步缩小数据集） #

如果type=10且revisit < 时间的记录占比仍较高，可以先筛选出符合条件的记录，再与聚合结果关联：

SELECT filtered.id
FROM (
    -- 先过滤出符合type和revisit条件的记录
    SELECT id, polizzennummer, firstdelivery
    FROM abc.items
    WHERE type = 10 
      AND revisit < '2022-11-17T00:00:00Z'
) AS filtered
JOIN (
    SELECT polizzennummer, MIN(firstdelivery) AS min_firstdelivery
    FROM abc.items
    GROUP BY polizzennummer
) AS agg ON filtered.polizzennummer = agg.polizzennummer 
       AND filtered.firstdelivery = agg.min_firstdelivery
LIMIT 50;

配合上述覆盖索引，该子查询可直接从索引中获取所需字段，性能更优。

额外检查点 #

• 确认revisit字段的存储格式与查询条件的时间格式一致，避免隐式类型转换导致索引失效
• 定期分析表碎片：ANALYZE TABLE abc.items;，确保统计信息准确，优化器能选择最优执行计划

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