MySQL大表INNER JOIN查询耗时过长优化方案咨询

现有查询慢的核心原因 #

你当前的写法存在两个明显的性能损耗点：

• DISTINCT需要对B表所有匹配id的记录（单id对应多条，总数据量可能达到几十万甚至上百万）做全量去重，CPU和内存开销极高
• 现有索引都是单列索引，查询过程中需要多次回表读取主数据，IO开销大

优化方案 #

1. 改写查询逻辑，避免全量DISTINCT #

你需求是每个id只返回任意一条B表记录，完全不需要对全量结果做去重，直接按id分组取第一条即可，效率远高于全量DISTINCT：

• 支持窗口函数的数据库（MySQL8.0+、PostgreSQL等）写法：

SELECT 
    t.id, t.fieldB_1, t.fieldB_2, t.fieldB_3, t.fieldB_4,
    A.fieldA_2, A.fieldA_3, A.fieldA_4
FROM (
    SELECT 
        id, fieldB_1, fieldB_2, fieldB_3, fieldB_4,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
    FROM B
    WHERE id IN (SELECT id FROM A WHERE fieldA_1 > 0)
) t
INNER JOIN A ON t.id = A.id
WHERE t.rn = 1;

• 不支持窗口函数的低版本MySQL写法：

SELECT 
    B.id, B.fieldB_1, B.fieldB_2, B.fieldB_3, B.fieldB_4,
    A.fieldA_2, A.fieldA_3, A.fieldA_4
FROM B
INNER JOIN A ON B.id = A.id
WHERE A.fieldA_1 > 0
GROUP BY B.id;

注意：低版本MySQL如果开启了ONLY_FULL_GROUP_BY，可以用MAX(fieldB_2)这类聚合函数包裹非分组字段，不影响你取任意一条的需求

2. 新增覆盖索引，消除回表开销 #

现有单列索引无法覆盖查询需要的所有字段，每次查询都需要回表读取主数据，新增两个联合索引即可解决：

• 表A加联合索引：idx_A_fieldA1_all(fieldA_1, id, fieldA_2, fieldA_3, fieldA_4)，过滤fieldA_1>0之后直接就能从索引拿到所有需要返回的A表字段，不需要回表
• 表B加联合索引：idx_B_id_all(id, fieldB_1, fieldB_2, fieldB_3, fieldB_4)，通过id匹配B表记录时直接从索引拿所有返回字段，不需要回表扫B表主数据
  索引调整+逻辑改写后，10万级结果查询速度可提升5-10倍，基本可以跑到毫秒级

3. 可选拆分查询（适合极端性能要求场景） #

如果业务允许，可以先查A表拿到符合条件的id列表，再按id批量查B表的单条记录，完全避免join开销，性能还能再提升。

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