MySQL中LEFT JOIN连表查询与OR跨表条件优化指南

前言

日常开发经常遇到一类SQL场景：使用LEFT JOIN左连接两张表，WHERE条件中使用OR，并且一部分条件属于左表，另一部分条件属于右表。

很多同学写完直接上线，上线后发现SQL性能急剧下降，EXPLAIN一看直接全表扫描，甚至逻辑结果和预期不符。

先展示一条典型问题SQL：

SELECT t1.id, t1.usernameFROM `user` t1LEFT JOIN `app_key` t2 ON t1.id = t2.user_idWHERE t1.username = 'test_user' OR t2.access_key = 'key_001';

这条语句包含两大高危点：

1. LEFT JOIN 左连接；
2. OR 条件横跨左表t1、右表t2两个不同数据表。

本文深度分析问题根源，给出稳定通用的优化方案，同时讲解隐藏的逻辑BUG。

一、先搞懂两个致命问题

1. 逻辑隐患：LEFT JOIN 语义直接失效

LEFT JOIN语义：保留左表所有数据，右表无匹配时填充NULL。

但如果WHERE子句中存在右表字段判断条件：

WHERE ... OR t2.access_key = 'key_001'

数据库要求t2.access_key不为NULL才能满足条件。

原本的左连接会被隐式转换成 INNER JOIN，左表无匹配右表的数据会被直接过滤，查询结果和业务预期不一致！

很多开发只关注速度，忽略数据出错，造成业务隐藏BUG。

2. OR跨表导致索引无法正常利用

MySQL优化器处理OR时存在限制：

同一个WHERE里的条件分布在两张关联表，优化器很难生成高效执行计划。

现象：

• 无法同时使用两张表各自索引；
• 很难触发索引范围扫描；
• 大概率出现全表扫描 type: ALL；
• 不要寄希望于index merge索引合并，跨表场景几乎不会触发，且性能不可控。

重点区分：

• OR所有条件都在同一张表：优化难度低，有机会正常走索引
• OR条件分布在两张JOIN后的表：高危，极易慢查询

二、错误尝试（网上流传的无效方案，避坑）

方案1：把右表条件移动到ON后面（治标不治本）

SELECT t1.id, t1.usernameFROM `user` t1LEFT JOIN `app_key` t2 ON t1.id = t2.user_id AND t2.access_key = 'key_001'WHERE t1.username = 'test_user' OR t2.access_key = 'key_001';

缺陷：WHERE依然存在跨表OR，无法解决索引失效问题，只是临时修正部分逻辑，查询速度依旧很差。

方案2：调整WHERE条件书写顺序

前文博客讲过：WHERE条件书写顺序不影响执行计划。单纯调换OR两边条件位置，完全无法提速，不要浪费时间尝试。

三、最优标准优化方案：拆分SQL + UNION ALL

核心思想

把OR代表的多种匹配场景拆分为多条独立单表/简单查询，分别执行，最后合并结果。

每条独立查询只负责一种匹配逻辑，可以完美使用各自表的索引。

原始需求逻辑拆解：满足下面任意一种情况

1. 用户表 user.username = 目标值
2. 密钥表 app_key.access_key = 目标值，关联查询对应用户

优化后SQL模板：

-- 场景1：匹配左表usernameSELECT id, username FROM `user` WHERE username = 'test_user'UNION ALL-- 场景2：匹配右表access_key，关联拿到用户信息SELECT t1.id, t1.usernameFROM `user` t1INNER JOIN `app_key` t2 ON t1.id = t2.user_idWHERE t2.access_key = 'key_001';

关键知识点：UNION ALL VS UNION

1. UNION ALL：直接纵向拼接结果，不去重、不排序，性能高；
2. UNION：自动去重，底层创建临时表排序，开销更大。

如果业务存在同一个用户在两条分支同时命中、需要去重，外层包一层DISTINCT

SELECT DISTINCT id, username FROM (    SELECT id, username FROM `user` WHERE username = 'test_user'    UNION ALL    SELECT t1.id, t1.username FROM `user` t1    INNER JOIN `app_key` t2 ON t1.id = t2.user_id    WHERE t2.access_key = 'key_001') tmp;

为什么拆分后速度大幅提升

1. 消除跨表OR，不再有复杂关联条件；
2. 两条子查询互相独立，各自使用对应字段索引；
3. 第二条场景不需要LEFT JOIN，直接改用INNER JOIN，减少扫描数据；
4. 执行计划清晰，EXPLAIN容易排查性能问题。

四、配套必须建立的索引

想要优化生效，索引不能缺少：

-- user表CREATE INDEX idx_user_username ON `user`(username);-- app_key表CREATE INDEX idx_key_access ON `app_key`(access_key);-- 关联字段索引，JOIN加速CREATE INDEX idx_key_userid ON `app_key`(user_id);

五、拓展业务场景：只需要查询匹配第一条数据

很多业务场景（账号检索、登录识别）不需要全部结果，找到任意一条匹配数据即可，可以加上LIMIT短路查询：

SELECT id, username FROM (    SELECT id, username FROM `user` WHERE username = 'test_user' LIMIT 1    UNION ALL    SELECT t1.id, t1.username FROM `user` t1    INNER JOIN `app_key` t2 ON t1.id = t2.user_id    WHERE t2.access_key = 'key_001' LIMIT 1) tmp LIMIT 1;

执行逻辑：命中第一条分支后直接返回，不会继续执行第二条查询，极致节约数据库开销。

六、备选方案：EXISTS子查询（不推荐复杂场景）

如果业务不方便拆分UNION，可使用EXISTS改写，但可读性较差，数据量大时性能上限低于UNION ALL方案：

SELECT DISTINCT t1.id, t1.usernameFROM `user` t1LEFT JOIN `app_key` t2 ON t1.id = t2.user_idWHERE t1.username = 'test_user'OR EXISTS (    SELECT 1 FROM `app_key` k    WHERE k.user_id = t1.id AND k.access_key = 'key_001');

适用：结果集很小的场景；大批量检索优先选择UNION ALL方案。

七、开发编码规范总结

1. 杜绝 LEFT JOIN + OR跨表条件 写法，同时存在性能BUG和逻辑BUG双重风险；
2. 遇到OR条件分布在JOIN的多张表，首选方案：拆分多条查询 + UNION ALL；
3. 如果需要去重，外层增加DISTINCT，优先使用UNION ALL而不是UNION；
4. 拆分后对应的查询字段建立单列索引，保障分支查询可以快速检索；
5. 不要尝试调整条件顺序、强行使用USE INDEX等偏方，治标不治本；
6. 牢记：左连接后WHERE过滤右表字段，极易导致LEFT JOIN语义失效。

八、验证方式

优化前后使用EXPLAIN对比执行计划：

• 优化前：type大概率出现ALL全表扫描
• 优化后：两条子查询type为ref索引查找，扫描行数大幅下降

生产环境遇到同类慢查询，直接套用拆分UNION ALL思路，是经过大量线上验证稳定可靠的优化手段。