MySQL关联middle、entityA、entityB三表查询缓慢优化求助

性能瓶颈分析 #

从执行计划可以看出，当前查询的核心问题有两个：

1. 先走全索引扫描middle表的近90万行数据，逐行关联entityA和entityB后才做条件过滤和排序，分页逻辑没有提前生效，大量无效数据参与了关联和计算。
2. WHERE条件中的entityB.colA = "XXX" OR entityA.colA = "XXX"是OR逻辑，无法触发索引下推，必须关联后才能过滤，效率极低。

优化方案 #

一、SQL改写（收益最高）

把原来的关联查询拆分为两个独立查询用UNION ALL合并，完全避免OR逻辑，同时各自可以命中索引：

-- 第一部分：优先查询存在entityB的记录，不管是否关联entityA
SELECT 
  b.colA, 
  b.colB
  -- 其他需要展示的entityB字段
FROM middle m
INNER JOIN entityB b ON m.entityBId = b.id
WHERE b.colA = 'XXX'
-- 补充其他针对entityB的查询条件

UNION ALL

-- 第二部分：查询只有entityA、无对应entityB的记录
SELECT 
  a.colA, 
  a.colB
  -- 其他需要展示的entityA字段
FROM middle m
INNER JOIN entityA a ON m.entityAId = a.id
WHERE m.entityBId IS NULL 
AND a.colA = 'XXX'
-- 补充其他针对entityA的查询条件

ORDER BY colA -- 合并后排序，别名和第一部分查询的输出字段名保持一致
LIMIT 20 OFFSET 0;

该改写的优势：

• 两个子查询可以分别命中entityB、entityA上colA的索引，直接过滤出符合条件的少量数据再关联middle表，不需要扫描全量middle数据
• 完全避免了LEFT JOIN后才过滤的无效计算，数据处理量级从近百万降到符合条件的几十/几百条
• 两个子查询的数据集完全无重叠（第二部分明确加了entityBId IS NULL条件），UNION ALL没有额外去重开销

二、索引补充（配合改写后的SQL性能最大化）

• 给middle表新增联合索引idx_middle_bid_aid (entityBId, entityAId)，刚好匹配两个子查询的关联条件：第一个子查询关联entityBId可以走索引前缀，第二个子查询过滤entityBId IS NULL+关联entityAId可以覆盖完整索引
• 建议给middle表新增自增主键id，InnoDB表没有主键会默认生成隐藏主键，影响索引效率，也不方便后续数据维护

三、可选进阶优化

如果这类列表查询非常高频，可以将需要查询、排序的公共字段（比如colA、colB）冗余存储到middle表，同时加上对应索引，查询时直接扫middle表就可以拿到过滤和排序字段，不需要再关联两张主表，性能会进一步提升。

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