含索引列OR条件的MySQL查询性能差异原因排查

表A有5000万条记录，表B有7000万条记录，表B通过id列与表A建立**外键（Foreign Key）**关联。

我有两个结构相似的查询，性能却天差地别：一个执行耗时20秒，另一个仅耗时5毫秒。

20秒查询 #

SELECT     min(table a.id),
           TABLE b.COLUMN
FROM       TABLE a
INNER JOIN TABLE b
ON         TABLE a.id = TABLE b.id
AND        b.id2 /* 已建立索引的列 */ = '12345'
WHERE      b.COLUMN IN (value)
OR         a.id     IN(idslist)
GROUP BY   b.id2,
           b.COLUMN
-- 注：b.COLUMN也是已建立索引的列

5毫秒查询 #

SELECT     min(table a.id),
           TABLE b.COLUMN
FROM       TABLE a
INNER JOIN TABLE b
ON         TABLE a.id = TABLE b.id
AND        b.id2 /* 已建立索引的列 */ = '12345'
WHERE      b.COLUMN IN (value)
GROUP BY   b.id2,
           b.COLUMN -- 移除了WHERE中的a.id IN(idslist)条件

我不清楚为何性能差异如此巨大，原本以为A.id作为**主键（Primary Key）**不会影响性能，希望有人帮忙排查原因。

问题原因分析 #

核心差异在于WHERE子句中的OR a.id IN(idslist)，这直接破坏了MySQL的索引优化逻辑：

1. 索引失效与扫描范围扩大

   • 5毫秒的查询中，WHERE条件仅针对B表的b.COLUMN，结合JOIN条件里的b.id2 = '12345'，MySQL可以直接利用b.id2和b.COLUMN的索引快速定位到小范围的B表记录，再通过外键关联到A表（A.id是主键，关联时直接走主键索引，效率极高），最后做GROUP BY的成本也极低。
   • 20秒的查询中，OR连接了两个来自不同表的过滤条件：b.COLUMN IN (value)依赖B表索引，a.id IN(idslist)依赖A表主键索引。MySQL优化器无法同时利用两个跨表的索引来处理OR逻辑，大概率会放弃索引，转而进行全表扫描或大范围索引扫描。如果idslist包含的ID数量较多，会导致需要扫描、关联大量A、B表记录，直接拖慢执行速度。

2. 执行计划的逻辑变化

   • 5毫秒查询的优化器会优先选择B表作为驱动表，因为B表的过滤条件能快速缩小数据集，后续关联A表的操作量极小。
   • 20秒查询的OR条件会让优化器改变驱动表选择，比如先扫描A表中符合a.id IN(idslist)的记录，再关联B表，同时还要处理B表中符合b.COLUMN IN (value)的记录，这会导致关联的数据量大幅增加，加上GROUP BY的排序分组操作，进一步拉长了执行时间。

3. 主键的性能误区

   • 虽然A.id作为主键单独查询时速度极快，但当它出现在跨表的OR条件中时，优化器无法将两个独立的索引条件合并，也就无法发挥主键的优势，反而因为要同时处理两个分支的数据集，导致性能暴跌。

优化建议 #

如果需要保留原逻辑的OR查询，可以将其拆分为两个独立的子查询，用UNION ALL合并结果后再做GROUP BY，这样每个子查询都能利用各自的索引，性能会大幅提升：

SELECT min(a.id), b.COLUMN
FROM TABLE a
INNER JOIN TABLE b
ON a.id = b.id
AND b.id2 = '12345'
WHERE b.COLUMN IN (value)
GROUP BY b.id2, b.COLUMN

UNION ALL

SELECT min(a.id), b.COLUMN
FROM TABLE a
INNER JOIN TABLE b
ON a.id = b.id
AND b.id2 = '12345'
WHERE a.id IN(idslist)
GROUP BY b.id2, b.COLUMN

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