MySQL复合查询详解：JOIN、子查询与UNION的性能优化

有一次帮朋友排查一个报表接口的慢查询三张表的数据量都不算大就是用户表、订单表、订单明细表这种最普通的电商结构SQL却要跑好几秒。页面是给运营看的等得人心焦。刚开始大家以为是服务器配置不行后来我把SQL拿出来一看问题不在机器而在查询本身——一条能用复合查询解决的问题被拆成了三个临时数组在代码里拼接来回调了三四次数据库接口。这件事让我特别想聊一聊MySQL复合查询。它不是一个多高深的概念无非就是JOIN、子查询、UNION这些把多张表、多个结果集组合起来的查询方式但恰恰是这些看似基础的操作最容易在真实业务里写出性能极差或者结果不正确的SQL。这篇文章不打算绕概念直接把复合查询的常用写法、优化思路、踩坑经历讲透适合正在写业务SQL的后端开发、统计数据时经常跟多表打交道的分析同学也适合刚学完单表查询、准备进阶的自学者。1. 先搞清楚复合查询到底在查什么1.1 一个让业务代码里写满临时数组的需求我遇到的那次排查原始需求其实特别简单页面上要展示用户列表每个用户后面带上他最近的订单金额、订单状态再显示他买过哪些商品分类。最粗暴的做法是在代码里先查用户表然后循环每个用户再去查订单表再循环订单去查商品表。三层循环下来数据库被打了上百次接口耗时自然暴涨。更麻烦的是如果用户在查询过程中下了新订单内存里拼出来的数据可能是旧的跟前端展示的其他数据对不上。这种场景就是复合查询的典型用武之地。把三个独立的查询合并成一个让数据库一次性把关联数据算好返回无论从耗时、一致性还是代码可维护性来说都是更优解。那次我改成一条三表JOIN的SQL之后运行时间从几秒直接降到了几百毫秒。1.2 复合查询的三个分支连接、子查询、集合合并MySQL里的复合查询按我的习惯会分成三大类。第一类是连接查询也就是JOIN。它做的事情是把两张表按某个关联字段横向拼在一起用户表一行对应订单表多行最后形成一张包含了双方字段的大表。几乎所有多表统计需求都会用到它。第二类是子查询把一条查询的结果作为另一条查询的条件、数据来源甚至直接当作一个字段输出。它解决的是“先算出一个集合再基于这个集合做下一步判断”的问题。第三类是集合查询也就是UNION和UNION ALL。它跟JOIN不一样JOIN是横向扩展列UNION是纵向追加行把多个SELECT的结果首尾相接合并成一张结果集。这三类可以单独用也可以嵌套在一起。比如“先UNION合并两个子查询的结果再JOIN用户表做关联展示”这在报表场景里非常常见。理解了这三个分支后面所有复杂的SQL其实都是它们的排列组合。1.3 什么时候别急着上复合查询有一句话我一直跟组里的新人强调复合查询不是万能的能用单表查清楚的事情不要硬凑多表。我见过不少同事一遇到订单相关需求就直接JOIN三五张表结果ORDER BY、GROUP BY全乱套。其实很多时候冗余字段已经存在了或者业务上可以先查一张小表拿到ID集合再二次查询效率反而更高。判断标准很简单如果你发现一条复合查询里某张表只用来过滤一个值压根不输出任何字段那完全可以改成子查询甚至先查出值再去查主表。复合查询是用来解决问题的不是为了展示SQL技巧。先想清楚业务上到底需要哪几张表的哪些数据再决定查询形态这个顺序别搞反。2. JOIN连接最常用的复合查询方式但最容易写错2.1 三种JOIN语义对照哪张表的数据必须保留先看最核心的语义问题。我见过太多人写JOIN只凭感觉连INNER JOIN和LEFT JOIN到底有什么区别都没完全搞明白就开始写代码。INNER JOIN只返回两张表都匹配上的行。LEFT JOIN返回左表的全部行右表没有匹配就补NULL。RIGHT JOIN跟LEFT JOIN方向相反实际开发里我很少直接用需要时把表的顺序调换一下改写成LEFT JOIN更直观。JOIN类型返回行数特征不匹配时表现典型场景INNER JOIN只返回匹配成功的行直接消失查“有订单的用户”、“有明细的订单”LEFT JOIN返回左表全部行右表字段填NULL查“所有用户及其订单没有订单也要显示用户”RIGHT JOIN返回右表全部行左表字段填NULL极少用通常可以改写为LEFT JOIN我举个例子。用户表里10个用户订单表里只有3个用户下过单。用INNER JOIN查出来是3行用LEFT JOIN查出来是10行没下单的用户订单字段显示为NULL。看似简单但一旦SQL变长、条件变多很多人就会把这两种语义写混。判断方式很简单先问自己“哪张表的每一行都必须出现在结果里”。如果答案是左表用LEFT JOIN如果答案是“两边都必须有匹配才要”用INNER JOIN。把这个想清楚再动手能避免一大半错误。2.2 把过滤条件放WHERELEFT JOIN也会悄悄变INNER JOIN这是一个高频坑我几乎每次带人都会讲一遍。看这条SQLSELECT u.id, u.name, o.order_no, o.amount FROM user u LEFT JOIN orders o ON o.user_id u.id WHERE o.status paid;表面上写的是LEFT JOIN看起来好像会返回所有用户没下单的用户订单字段是NULL。但实际上WHERE子句里加了o.status paid之后那些没有订单的用户右表字段是NULLNULL跟paid比较的结果不是TRUE整行被过滤掉了。也就是说这个查询最终只会返回“有已支付订单的用户”LEFT JOIN的外连接效果被WHERE条件抵消了行为跟INNER JOIN几乎一样。要想既保留所有用户又只关联已支付的订单正确写法是把过滤条件放到ON后面SELECT u.id, u.name, o.order_no, o.amount FROM user u LEFT JOIN orders o ON o.user_id u.id AND o.status paid;这两条SQL的差别在数据不完整时结果完全不同。开发环境数据少可能怎么看都对生产库里有大量没有订单的用户第二种写法的价值就体现出来了。我的经验是凡是LEFT JOIN右表的过滤条件默认放ON只有确认要过滤掉左表里“没有匹配数据”的行时才把右表条件放WHERE。这是连接查询里最重要的一个心智模型。2.3 驱动表、连接字段索引与三表JOIN的写法JOIN的性能问题核心其实就两个词驱动表和索引。驱动表可以理解成嵌套循环里外层的那个表每遍历驱动表的一行就去被驱动表里找匹配行。MySQL的优化器会自动选择执行顺序但在没有足够统计信息或复杂度较高时写SQL的人最好自己心里有数优先让小表做驱动表被驱动表的连接字段要建索引。举个例子三张表做统计SELECT u.name, COUNT(DISTINCT oi.product_id) AS product_cnt, SUM(oi.quantity) AS total_qty FROM user u JOIN orders o ON o.user_id u.id JOIN order_items oi ON oi.order_id o.id GROUP BY u.name;这种三表JOINorders.user_id和order_items.order_id必须建索引否则MySQL只能对连接字段做全表扫描数据量一旦上来查询就是灾难。我通常在每次JOIN后都看一眼执行计划确认被驱动表走的不是ALL。如果是ALL要么加索引要么把大表拆出去不要指望优化器能凭空变出索引来。另外多说一句业务SQL里JOIN的表数量尽量不要超过4张。一旦超过执行计划的复杂度会大幅上升优化器误判的概率也高。真遇到需要六七张表才能出结果的场景优先考虑是不是表结构设计有问题比如该冗余的字段没有冗余或者该拆的宽表没拆。3. 子查询什么时候该用什么时候它就是性能杀手3.1 标量、派生表、相关子查询三种形态的分工子查询在MySQL里大体有三种形态每一种的适用场景都不一样。标量子查询是返回单个值的子查询通常放在SELECT字段列表里或者当作WHERE的比较值。比如查每个用户的最近一次下单时间SELECT u.id, u.name, (SELECT MAX(created_at) FROM orders o WHERE o.user_id u.id) AS last_order_time FROM user u;FROM子句里的子查询叫派生表查询结果会被当成一张临时表使用。它必须起别名比如SELECT d.user_id, AVG(d.amount) AS avg_amount FROM ( SELECT user_id, amount FROM orders WHERE created_at 2024-01-01 ) d GROUP BY d.user_id;WHERE条件里配合IN或EXISTS使用的是相关子查询它跟外层查询有关联条件最典型的就是“查有订单的用户”SELECT id, name FROM user u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );三种形态各有优势。标量子查询适合“每行补充一个聚合值”但要注意它会对每一行都执行一次子查询数据量大时会有性能压力。派生表适合先缩小数据集再参与运算。EXISTS适合做存在性判断。了解了它们的分工才能谈选型。3.2 IN和EXISTS别再背口诀先看执行计划很长一段时间里社区里流行一句口诀外层小表用IN外层大表用EXISTS。这句话在早期优化器不智能的时代有一定道理但现在已经不能当作铁律了。我做过多次测试在MySQL 8.0里很多IN和EXISTS的写法最终会被优化器改写成半连接semi-join执行计划差别不大。真正影响性能的是子查询里的表有没有走索引、外层查询的过滤条件能不能先缩小范围。比起纠结IN还是EXISTS我更建议关注三个细节。第一子查询结果集里如果有NULLIN的判断会变得不符合直觉。WHERE user_id IN (1, 2, NULL)这类写法在某些情况下会返回空结果因为NULL跟任何值比较都是未知SQL的NULL传播逻辑很容易让新手困惑。EXISTS则完全不受NULL影响。第二子查询里尽量不要查多余字段。写EXISTS (SELECT * FROM ...)和EXISTS (SELECT 1 FROM ...)优化器通常会忽略SELECT列表但养成只写1的习惯能减少误读。第三真实业务里如果发现IN子查询慢先看子查询表的数据量和索引再尝试把它改成JOIN。但要注意JOIN可能会因为一对多关系产生重复行需要配合DISTINCT或GROUP BY处理这又是一层新的开销。所以别盲目改写一切以EXPLAIN的结果为准。3.3 派生表物化一个容易被忽略的临时表陷阱派生表看起来方便但有一个容易被忽略的性能问题物化。MySQL 5.7及以上版本引入了一个优化叫derived_merge优化器会把一部分派生表合并到外层查询里避免真正生成临时表。但很多情况下派生表无法合并比如内部用了GROUP BY、LIMIT、聚合函数、DISTINCT的时候MySQL只能把子查询的结果物化成一张内部临时表。问题在于物化出来的临时表默认没有合适的索引。如果外层JOIN需要基于派生表的结果做关联表里数据量一大就可能全表扫描。我之前调过一个SQL先按订单明细聚合出商品销量再JOIN商品表拿商品名称就是因为派生表里有GROUP BY导致物化临时表随后JOIN时无法高效查找查询跑了五六秒。解决思路有三个。一是缩小派生表的数据范围在子查询内部就加WHERE条件过滤让物化后的临时表尽量小。二是考虑改成两张表的直接JOIN让优化器有更多选择空间。三是MySQL 8.0支持LATERAL派生表可以在FROM子句中引用前面表的字段实现更灵活的逐行关联但这是进阶用法基础场景用不上。3.4 一次“先IN后EXISTS”的真实调优说一个我印象很深的调优案例。某个后台页面要导出“有过已支付订单的用户”最初的SQL长这样SELECT u.id, u.name, u.mobile FROM user u WHERE u.id IN ( SELECT DISTINCT user_id FROM orders WHERE status paid );orders表有几百万行DISTINCT user_id之后还要跟外层user表做IN匹配。上线之后这个查询要跑十多秒页面直接卡死。我先看了执行计划问题在于子查询里的DISTINCT产生了大量的临时表操作而且整个IN子查询的结果集非常大。我改成EXISTS的写法SELECT u.id, u.name, u.mobile FROM user u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.status paid );改写之后优化器可以对orders表使用(user_id, status)的复合索引做半连接判断不再需要先物化整个DISTINCT集合查询耗时降到了两秒以内。后面我又给orders表加了(user_id, status)索引耗时进一步降到几百毫秒。这个案例给我的启发是写SQL不能只看结果对不对要顺着执行计划去理解数据是怎么被处理的。子查询里任何一步产生大临时表或大排序都可能成为整条SQL的瓶颈。4. UNION与UNION ALL合并结果集的正确打开方式4.1 UNION和UNION ALL只差一个去重性能差一个量级UNION和UNION ALL的区别很多人第一反应是“一个去重一个不去重”但很少去想这个“去重”的成本到底有多大。UNION在做完两个SELECT的拼接之后还要额外做一次去重操作。MySQL通常需要一个临时表来存储中间结果再对每行做比较数据量大时可能还要排序。UNION ALL则直接把所有行拼在一起返回没有这层额外开销。我做过一个简单的对比两张百万行的分表用UNION查询比UNION ALL多消耗了接近一倍的时间。如果业务上明确知道两个结果集不可能有重复行或者重复行不影响最终展示那完全可以无脑用UNION ALL。什么时候才需要UNION比如统计“用户名里含测试”和“手机号尾号为88”的两个用户集合一个人可能同时满足两个条件如果要求结果集展示不重复就得用UNION。如果只是要把两段日志合并导出重复行无所谓用UNION ALL就够了。原则就是不需要去重就别让数据库做无谓的去重。4.2 列数、列名与排序UNION结果集的规整规则UNION的语法限制严格但正因为严格新手容易在这里踩壳。最基础的一条每个SELECT的列数必须一致。第一段SELECT有两个字段第二段列三个字段直接报错。这个问题比较明显通常一眼就能发现。比较隐蔽的是列类型不一致。比如第一个SELECT返回的订单金额是DECIMAL类型第二个SELECT返回的是VARCHAR字符串MySQL会做隐式类型转换可能造成精度问题或结果不符合预期。跨表合并时字段类型尽量保持一致不要指望数据库每次都帮你转得聪明。还有一个高频误区是ORDER BY和LIMIT的放置位置。很多人喜欢在UNION的每一段里加ORDER BY但MySQL的行为是单段内的ORDER BY只有在配合LIMIT时才有意义否则整个UNION执行时很可能忽略它。要想对最终结果排序必须把ORDER BY放在最后一段SELECT之后。正确姿势像是这样SELECT order_no, amount, created_at FROM orders_2024_01 UNION ALL SELECT order_no, amount, created_at FROM orders_2024_02 ORDER BY created_at DESC LIMIT 100;理解的锚点在于UNION先把多个结果集拼起来形成一个“整体”之后再统一做排序和分页。所以排序和分页不属于任何一段SELECT而是属于合并后的整体。4.3 按月分表后用UNION ALL把全年数据拼回来我在实际业务里用UNION ALL最多的场景是历史分表的汇总。有一类订单系统为了控制单表数据量会按月拆表比如orders_202401、orders_202402。业务上要查全年的销售总额很多人的第一反应是改代码循环12个月分别查一次再在内存里加起来。这样既慢又绕直接写一条UNION ALL SQL反而更干净。SELECT SUM(amount) AS total_amount FROM ( SELECT amount FROM orders_202401 UNION ALL SELECT amount FROM orders_202402 UNION ALL SELECT amount FROM orders_202403 ) t;这种写法把12个月的订单金额纵向拼起来外层再聚合一次数据库只处理一条SQL代码和SQL都更清晰。有一点提醒一下如果需要进一步计算汇总值UNION ALL的结果需要再包一层不能直接对UNION结果写SUM因为UNION作用于SELECT语句层面外面想聚合就要先变成派生表。另外分表查询虽然能解决问题但从长远看分表策略本身需要谨慎设计。如果只是数据量大了点还没到分表必要程度硬拆反而会让所有查询都背上UNION ALL的复杂度。能用分区表解决的优先考虑分区表。5. 复合查询的EXPLAIN实战定位慢查询的完整过程5.1 三秒慢查询的第一眼EXPLAIN里的ALL和Filesort前面说的都是具体的SQL写法这一节我们看一个完整的排查过程。假设有个订单统计接口SQL大概长这样SELECT u.id, u.name, COUNT(o.id) AS order_cnt, SUM(o.amount) AS total_amount FROM user u LEFT JOIN orders o ON o.user_id u.id WHERE o.created_at BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY u.id, u.name ORDER BY total_amount DESC;运行耗时三秒多。我第一件事就是执行EXPLAIN看执行计划核心看几个字段type、key、rows、Extra。当时的执行计划里orders表驱动查询时type是ALL说明连接字段没走索引在扫全表。Extra里还有Using temporary和Using filesort说明GROUP BY和ORDER BY都借助了临时表和文件排序。一个查询最怕看到的就是ALL配合Using temporary、Using filesort同时出现这意味着扫描了大量数据额外做了临时表和排序整条SQL的性能可想而知。5.2 加索引、缩范围三次改写后的执行计划变化我针对这个执行计划做三步调整。第一步给orders的user_id字段加上索引。这一步让JOIN走后变成ref被驱动表不再全表扫描。对于大多数订单表user_id是高频查询字段这个索引通常早就该建了。第二步把WHERE条件往前挪。原本LEFT JOIN之后才过滤created_at这一步其实已经把没有匹配订单的user行过滤掉了可以考虑直接用INNER JOIN或者至少确认业务是否真的需要保留“没有订单的用户”。确认后发现这个报表只关心有过订单的用户于是改成INNER JOIN让优化器有更多优化空间。第三步优化GROUP BY和ORDER BY。由于我们按user_id和name分组再按聚合结果排序排序是不可避免的但可以确保临时表空间足够、排序缓冲区合理并且在orders表建立了(user_id, created_at)的复合索引让COUNT和SUM能在索引扫描过程中完成更多工作。三步改完再执行EXPLAIN时type从ALL变成了ref或者rangerows从百万级降到万级Extra里的Using temporary消失Using filesort也只在最终结果排序时才出现。实际执行时间从三秒降到了二三百毫秒。整个过程的思路不是一次性想出最优解而是顺着执行计划的提示一步步逼近问题根源。看到ALL就找索引看到Using temporary就看看GROUP BY能不能用索引或缩小数据看到filesort就判断排序能否消除。5.3 字符集、隐式转换与函数包裹索引是怎么悄悄失效的除了明显的缺索引复合查询里还有一种更难察觉的索引失效场景我单独拿出来讲。第一个坑是隐式类型转换。比如users表id字段是数值类型但SQL写成WHERE u.id 123MySQL有时会尝试把字符串转成数值多数情况下还能走索引但如果是反过来——字符串字段跟数值常量比较索引失效就很常见了。第二个坑是字符集不一致。两张表的关联字段一张是utf8一张是utf8mb4JOIN时MySQL要做字符集转换连接条件的字段索引很可能失效。我曾经排查过一个慢JOIN最后发现就是两套遗留表用了不同字符集统一成utf8mb4之后瞬间正常。第三个坑是在字段上套函数。WHERE DATE_FORMAT(created_at, %Y-%m) 2024-01这种写法特别优雅但created_at字段上的函数会让索引失效。正确做法是写成范围条件WHERE created_at 2024-01-01 AND created_at 2024-02-01这三个坑在复合查询里更加隐蔽因为SQL里一旦有JOIN、有子查询执行计划变复杂很多人会自动忽略这些基础问题。遇到慢查询除了看有没有索引还应该检查一下关联字段的类型和字符集是不是一致。6. 复合查询的常见误区和我的避坑清单6.1 三类“看着对实际上错”的复合查询先列三个我经常看到的错误写法每一类都踩过或者帮别人排查过。第一类是LEFT JOIN配合右表过滤条件放在WHERE这条前面已经详细说过它会把左连接悄悄变成内连接结果集少了“没有匹配”的行。第二类是无脑用DISTINCT掩盖重复行。JOIN产生重复行正确做法是分析重复来源是驱动表字段重复还是一对多关系导致。DISTINCT不会解决根因还会引入额外的排序去重开销。第三类是子查询返回多列却报错或者搞不清IN和EXISTS的使用边界。前者一般是语法理解问题后者是性能问题需要结合执行计划判断不能用一句口诀解决所有场景。这些错误的共同点在于写的时候感觉逻辑通了但没走一遍数据验证。我的建议是任何复合查询在上线前都务必用真实或接近真实的数据量跑一遍不要只在小数据集上验证结果。6.2 大分页场景下的延迟关联LIMIT慢的真正解法复合查询配合深分页是我在生产环境里见过最多的性能事故场景。直接写LIMIT 100000, 20看起来很合理但MySQL的执行方式是先扫描前100020行取最后20行返回前100000行全部丢弃。如果JOIN的表数据量大这个“扔掉”的过程非常昂贵。遇到深分页时我通常用延迟关联来改写。先只取出当前页需要的主键范围再回头JOIN明细数据SELECT u.name, o.order_no, o.amount FROM ( SELECT id FROM user ORDER BY id LIMIT 100000, 20 ) t JOIN user u ON u.id t.id LEFT JOIN orders o ON o.user_id t.id;这样做的核心思想是先用最小的代价在覆盖索引上完成分页拿到20个主键再去关联真正的宽表数据避免把大表前十万行都拖出来。实际效果很明显。我处理过的一个接口原本翻到第100页时查询耗时七八秒改成延迟关联后稳定在一秒以内。如果页数访问特别深还可以考虑基于游标的分页方式用WHERE id 上一页最大ID替代LIMIT这才是根治方案。6.3 我的复合查询避坑清单完整版每次跟组里同学做代码评审我都会把下面这张清单过一遍。这里直接分享出来写SQL前先明确业务到底要保留哪张表的全部数据据此选INNER JOIN还是LEFT JOIN。LEFT JOIN的右表过滤条件放在ON里左表过滤条件放WHERE不要混。多表JOIN一定要检查被驱动表的连接字段有没有索引字符集和类型是否一致。IN和EXISTS的选择不要凭口诀以EXPLAIN实际执行计划为准注意NULL值的坑。子查询结果集别做不必要的DISTINCT尽量在内部缩小数据范围。UNION不需要去重时一律用UNION ALL排序和LIMIT放在整体之后。深分页用延迟关联或游标分页别指望大LIMIT能快。不要在查询字段上套函数时间范围过滤用区间条件。每条即将上线的复杂SQL都要看过输出结果和执行计划后再放行。清单里的每一条都是从真实排障中沉淀下来的。多花几秒钟检查背后省下的是几小时甚至几天的排查时间。最后再分享一个自己的习惯拿到任何一条复合查询先别急着运行花十秒把它的连接顺序、过滤条件、分组字段在脑子里过一遍再执行EXPLAIN确认每一步的访问类型。踩过的坑越多越会发现MySQL的优化器虽然越来越聪明但SQL写得不清晰时它能帮你兜底的能力是有限的。把复合查询当成一种表达方式像讲话一样写清楚每张表的职责和每步过滤的目的很多性能问题在落笔那一刻就已经避免了。这篇文章没有高深的理论但每一段都是从真实需求和真实故障里打磨出来的希望对正在跟多表查询较劲的你有点帮助。