标签MySQL、LEFT JOIN、OR优化、索引失效、SQL调优、多表查询前言日常开发经常遇到一类SQL场景使用LEFT JOIN左连接两张表WHERE条件中使用OR并且一部分条件属于左表另一部分条件属于右表。很多同学写完直接上线上线后发现SQL性能急剧下降EXPLAIN一看直接全表扫描甚至逻辑结果和预期不符。先展示一条典型问题SQLSELECTt1.id,t1.usernameFROMusert1LEFTJOINapp_keyt2ONt1.idt2.user_idWHEREt1.usernametest_userORt2.access_keykey_001;这条语句包含两大高危点LEFT JOIN左连接OR条件横跨左表t1、右表t2两个不同数据表。本文深度分析问题根源给出稳定通用的优化方案同时讲解隐藏的逻辑BUG。一、先搞懂两个致命问题1. 逻辑隐患LEFT JOIN 语义直接失效LEFT JOIN语义保留左表所有数据右表无匹配时填充NULL。但如果WHERE子句中存在右表字段判断条件WHERE...ORt2.access_keykey_001数据库要求t2.access_key不为NULL才能满足条件。原本的左连接会被隐式转换成 INNER JOIN左表无匹配右表的数据会被直接过滤查询结果和业务预期不一致很多开发只关注速度忽略数据出错造成业务隐藏BUG。2. OR跨表导致索引无法正常利用MySQL优化器处理OR时存在限制同一个WHERE里的条件分布在两张关联表优化器很难生成高效执行计划。现象无法同时使用两张表各自索引很难触发索引范围扫描大概率出现全表扫描type: ALL不要寄希望于index merge索引合并跨表场景几乎不会触发且性能不可控。重点区分✅ OR所有条件都在同一张表优化难度低有机会正常走索引❌ OR条件分布在两张JOIN后的表高危极易慢查询二、错误尝试网上流传的无效方案避坑方案1把右表条件移动到ON后面治标不治本SELECTt1.id,t1.usernameFROMusert1LEFTJOINapp_keyt2ONt1.idt2.user_idANDt2.access_keykey_001WHEREt1.usernametest_userORt2.access_keykey_001;缺陷WHERE依然存在跨表OR无法解决索引失效问题只是临时修正部分逻辑查询速度依旧很差。方案2调整WHERE条件书写顺序前文博客讲过WHERE条件书写顺序不影响执行计划。单纯调换OR两边条件位置完全无法提速不要浪费时间尝试。三、最优标准优化方案拆分SQL UNION ALL核心思想把OR代表的多种匹配场景拆分为多条独立单表/简单查询分别执行最后合并结果。每条独立查询只负责一种匹配逻辑可以完美使用各自表的索引。原始需求逻辑拆解满足下面任意一种情况用户表user.username 目标值密钥表app_key.access_key 目标值关联查询对应用户优化后SQL模板-- 场景1匹配左表usernameSELECTid,usernameFROMuserWHEREusernametest_userUNIONALL-- 场景2匹配右表access_key关联拿到用户信息SELECTt1.id,t1.usernameFROMusert1INNERJOINapp_keyt2ONt1.idt2.user_idWHEREt2.access_keykey_001;关键知识点UNION ALL VS UNIONUNION ALL直接纵向拼接结果不去重、不排序性能高UNION自动去重底层创建临时表排序开销更大。 如果业务存在同一个用户在两条分支同时命中、需要去重外层包一层DISTINCTSELECTDISTINCTid,usernameFROM(SELECTid,usernameFROMuserWHEREusernametest_userUNIONALLSELECTt1.id,t1.usernameFROMusert1INNERJOINapp_keyt2ONt1.idt2.user_idWHEREt2.access_keykey_001)tmp;为什么拆分后速度大幅提升消除跨表OR不再有复杂关联条件两条子查询互相独立各自使用对应字段索引第二条场景不需要LEFT JOIN直接改用INNER JOIN减少扫描数据执行计划清晰EXPLAIN容易排查性能问题。四、配套必须建立的索引想要优化生效索引不能缺少-- user表CREATEINDEXidx_user_usernameONuser(username);-- app_key表CREATEINDEXidx_key_accessONapp_key(access_key);-- 关联字段索引JOIN加速CREATEINDEXidx_key_useridONapp_key(user_id);五、拓展业务场景只需要查询匹配第一条数据很多业务场景账号检索、登录识别不需要全部结果找到任意一条匹配数据即可可以加上LIMIT短路查询SELECTid,usernameFROM(SELECTid,usernameFROMuserWHEREusernametest_userLIMIT1UNIONALLSELECTt1.id,t1.usernameFROMusert1INNERJOINapp_keyt2ONt1.idt2.user_idWHEREt2.access_keykey_001LIMIT1)tmpLIMIT1;执行逻辑命中第一条分支后直接返回不会继续执行第二条查询极致节约数据库开销。六、备选方案EXISTS子查询不推荐复杂场景如果业务不方便拆分UNION可使用EXISTS改写但可读性较差数据量大时性能上限低于UNION ALL方案SELECTDISTINCTt1.id,t1.usernameFROMusert1LEFTJOINapp_keyt2ONt1.idt2.user_idWHEREt1.usernametest_userOREXISTS(SELECT1FROMapp_keykWHEREk.user_idt1.idANDk.access_keykey_001);适用结果集很小的场景大批量检索优先选择UNION ALL方案。七、开发编码规范总结杜绝LEFT JOIN OR跨表条件写法同时存在性能BUG和逻辑BUG双重风险遇到OR条件分布在JOIN的多张表首选方案拆分多条查询 UNION ALL如果需要去重外层增加DISTINCT优先使用UNION ALL而不是UNION拆分后对应的查询字段建立单列索引保障分支查询可以快速检索不要尝试调整条件顺序、强行使用USE INDEX等偏方治标不治本牢记左连接后WHERE过滤右表字段极易导致LEFT JOIN语义失效。八、验证方式优化前后使用EXPLAIN对比执行计划优化前type大概率出现ALL全表扫描优化后两条子查询type为ref索引查找扫描行数大幅下降生产环境遇到同类慢查询直接套用拆分UNION ALL思路是经过大量线上验证稳定可靠的优化手段。