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

前言

日常开发经常遇到一类sql场景：使用left join左连接两张表，where条件中使用or，并且一部分条件属于左表，另一部分条件属于右表。
很多同学写完直接上线，上线后发现sql性能急剧下降，explain一看直接全表扫描，甚至逻辑结果和预期不符。

先展示一条典型问题sql：

select t1.id, t1.username
from `user` t1
left join `app_key` t2 on t1.id = t2.user_id
where t1.username = 'test_user' or t2.access_key = 'key_001';

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

left join 左连接；
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.username
from `user` t1
left 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代表的多种匹配场景拆分为多条独立单表/简单查询，分别执行，最后合并结果。

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

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

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

优化后sql模板：

-- 场景1：匹配左表username
select id, username from `user` where username = 'test_user'

union all

-- 场景2：匹配右表access_key，关联拿到用户信息
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';

关键知识点：union all vs union

union all：直接纵向拼接结果，不去重、不排序，性能高；
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;

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

消除跨表or，不再有复杂关联条件；
两条子查询互相独立，各自使用对应字段索引；
第二条场景不需要left join，直接改用inner join，减少扫描数据；
执行计划清晰，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.username
from `user` t1
left join `app_key` t2 on t1.id = t2.user_id
where 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方案。