MySQL复合查询优化实战：JOIN、子查询与UNION性能拆解

上周排查一个慢查询三张表关联总共不到十万行数据却跑了三秒多。我把那条涉及 MySQL 复合查询的 SQL 翻来覆去地看最后发现问题不在索引而在于我把子查询写在了 WHERE 条件里优化器选择了奇怪的执行路径。这件事让我意识到很多人写复杂查询时语法都会但真正理解数据库是怎么把这些表拼起来、怎么决定执行顺序的人并不多。这篇博文想做的就是把 MySQL 复合查询这件事彻底拆开——JOIN、子查询、UNION 这三种基本形态各自解决什么问题哪些写法会在数据量上来以后变得特别慢以及排查复合查询性能问题时应该抓哪些核心线索。内容主要面向写过两年左右 SQL、开始接触多表复杂业务的开发者和数据分析师不需要你会读执行计划我会从最基础的逻辑讲起。1. 复合查询的本质为什么单表SQL撑不住真实业务1.1 范式化拆表带来的查询难题刚接触数据库设计时第一课往往就是范式化为了消除冗余把业务数据拆成多张表表之间靠外键关联。我在项目里最常见的一个例子是订单表。订单表里不会直接放商品名称、用户电话而只放product_id和user_id。商品名称要去商品表查用户电话要去用户表查。这样的设计在写入和更新时很舒服因为一份数据只存在于一个地方不会有某个字段被改漏的风险。但代价就是查询变得麻烦。当你需要在页面上展示“最近30天的订单列表包含商品名称、用户昵称、订单金额”时单表查询做不到因为关键信息分散在至少三张表里。这时候就必须把多张表的数据重新组合起来。这种把多个数据源在查询阶段合并、筛选、计算的过程就是复合查询。所以说复合查询不是什么高级技巧它是范式化数据库设计的必然产物。你拆表拆得越规范查询时就越需要把表重新“拼”起来。这也是为什么单表 SELECT 写得再熟练、索引建得再好面对真实业务照样捉襟见肘。1.2 复合查询的三种基本形态如果说复合查询是一栋房子那它主要由三种结构搭建而成分别是连接查询、子查询、集合查询。很多人会把它们混在一起用但理解各自定位非常重要。连接查询JOIN这是最直观的“拼表”方式。订单表和商品表都包含product_id只要以这个列为连接条件就能把两张表的列拼成一张更宽的结果集。这就像把两块拼图按咬合口接上。子查询在一个 SELECT 的里面再套一个 SELECT。外层查询的筛选条件依赖于内层查询的结果。这像是剥洋葱先算里面那一层再决定外面怎么处理。集合查询UNION把两条或多条 SELECT 语句的结果行按顺序合并起来。和 JOIN 不同它不增加列而是增加行。这就像把两个文件柜里的档案倒进同一个抽屉里。这三种形态解决了三类不同的问题。JOIN 解决的是“列不够用要横向取数”子查询解决的是“一次查询的结果要作为另一次查询的输入”UNION 解决的是“多段独立查询的结果要纵向汇总”。实际业务里经常是三种混着用的但每一种的使用场景和心理模型要分清。1.3 哪些场景非复合查询不可我在实际工作中归纳了几个典型场景遇到这些情况基本就意味着必须写复合查询一对多关系取明细比如一个商品对应多条订单记录要统计商品维度时需要把 orders 表和 products 表关联。多对多关系通过中间表查询比如用户和角色的关系存在 user_role 中间表想查出某用户的所有角色名称必须连续 JOIN 三张表。分组后的集合筛选比如要找出“下单次数超过 10 次的用户”先用 GROUP BY 统计每个用户的订单数再用外层查询筛选。这种需求如果用单表只能全部捞出来到代码里算非常低效。分表后的数据合并业务量大时一张用户表被拆成了 users_2024、users_2025 等结构相同的分区表用 UNION 把多个表的数据合并成一份完整结果。这些场景不是偶然出现而是随着系统功能增加必然会遇到的。所以不要问“复合查询有没有用”而是要问“我写的复合查询是否用了最适合当前数据量和业务形态的写法”。2. JOIN类型选择INNER、LEFT、RIGHT背后的取舍逻辑2.1 内连接与外连接的语义差异JOIN 看起来简单但很多人都没搞懂内连接和外连接真正差在哪里。我从字段匹配的角度解释一下。内连接INNER JOIN只保留两边都能匹配上的行。比如订单表里有 100 个订单其中 3 个订单的商品被删掉了商品表里查不到那么 INNER JOIN 出来就是 97 行。这种逻辑适合只关心有效匹配的场景。左连接LEFT JOIN会把左表的所有行都保留下来右表如果没有匹配行就用 NULL 填充。同样是上面那个例子LEFT JOIN 会返回 100 行那 3 个商品的名称字段显示为 NULL。这种逻辑适合“以左侧表为主右侧信息作为一种补充”的场景。右连接RIGHT JOIN是左连接的反向操作保留右表全部行。实际开发里我几乎不用 RIGHT JOIN因为只要把表的顺序换个位置写成 LEFT JOIN 就能达到同样效果。多写 RIGHT JOIN 反而容易让读代码的人多绕一层弯。2.2 ON 与 WHERE 的过滤顺序陷阱这是 LEFT JOIN 最经典的坑我见过太多人在这里翻车。先看一段问题 SQLSELECT u.id, u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id u.id WHERE o.status paid;这个 SQL 想表达的意思可能是“每个用户有多少笔已支付订单没支付的用户也要显示”。但实际执行结果会漏掉所有没有已支付订单的用户甚至完全等同于 INNER JOIN。原因在于WHERE子句是在 JOIN 完成之后对最终结果集进行过滤的。它在过滤o.status paid时把右表为 NULL 的那些行全删掉了而 NULL 行恰恰就是那些没有已支付订单的用户。正确的写法应该是把过滤条件放到ON里SELECT u.id, u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id u.id AND o.status paid;这样ON负责决定左表行是否能匹配到右表的哪一行匹配不到就补 NULL之后WHERE不再过滤右表字段左表全量保留。判断标准很简单如果这个过滤条件只对右表生效且你希望左表行不被删掉那就放到ON里如果过滤条件涉及左表或者你本来就要做严格过滤那放在WHERE更合适。2.3 多表连接驱动表顺序与 STRAIGHT_JOIN三张表以上连接时MySQL 会选择一张表作为“驱动表”先读它再拿结果去其他表里找匹配行。优化的核心原则是让小的结果集先跑也就是“小表驱动大表”。举一个例子用户表 10000 行订单表 1000000 行如果先全表扫描订单表再逐行去用户表匹配那代价就是一百万次索引查找非常难受。反过来先扫描用户表再逐行去订单表用user_id索引查则只有一万次查找代价小得多。大多数情况下优化器能正确选择驱动表但偶尔也会因为统计信息不准而选错。MySQL 提供了STRAIGHT_JOIN关键字强制指定连接顺序SELECT * FROM users u STRAIGHT_JOIN orders o ON o.user_id u.id;它强制按照书写顺序前面的表作为驱动表。不过这种写法不是万能的如果实际应该用订单表作为驱动表强行指定用户表反而更慢。我自己的经验是先用 EXPLAIN 确认当前驱动表是谁看到明显不合理的全表扫描后再用STRAIGHT_JOIN尝试并且要对比优化前后耗时不要凭感觉。3. 子查询写法与执行路径从EXISTS到派生表的血泪经验3.1 非相关子查询的执行逻辑子查询可以分成两大类非相关子查询和相关子查询。非相关子查询的执行顺序很好理解它先被独立执行得到一个结果集外层查询再用这个结果集继续处理。最常见的写法是IN条件SELECT * FROM products WHERE cate_id IN ( SELECT cate_id FROM categories WHERE status 1 );MySQL 会先执行括号里的查询拿到所有启用分类的cate_id再回到 products 表去匹配。这个逻辑本身没问题但新手容易在这里踩两个坑。第一子查询结果集如果很大外层 IN 的效率会下降。虽然 MySQL 会自动把子查询结果物化为临时表并加索引但大结果集的物化和搜索依然有成本。第二NOT IN遇到 NULL 会出大问题。如果括号里的子查询结果集包含 NULLNOT IN不会返回你想要的结果这和 SQL 的三值逻辑有关。解决办法是把NOT IN改成NOT EXISTS这个写法对 NULL 更安全。3.2 相关子查询与 EXISTS 的取舍相关子查询不像非相关子查询那样只执行一次它会对外层结果的每一行都执行一次。听起来很恐怖但有了索引和优化器半连接优化之后实际表现不一定差。举例一个经典需求是“列出购买过编号为 1001 商品的用户”。用IN可以写SELECT * FROM users WHERE id IN ( SELECT user_id FROM orders WHERE product_id 1001 );用EXISTS可以写SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.product_id 1001 );过去很多老经验说“EXISTS 一定比 IN 快”这在 MySQL 新版里已经不完全成立了。优化器会把IN子查询改写成半连接执行路径和EXISTS可能完全一样。真正决定性能的是user_id和product_id上有没有索引以及外层表的数据规模。我的建议是优先写出语义清晰的写法然后一定用 EXPLAIN 看执行计划。如果发现子查询导致了全表扫描或者临时表过大再考虑改写。3.3 派生表在 FROM 中的运行机制子查询不仅能放在 WHERE还能放在 FROM 后面当作临时表用这种就叫派生表。例如SELECT t.cate_id, AVG(t.price) FROM ( SELECT cate_id, price FROM products WHERE status 1 ) t GROUP BY t.cate_id;在 MySQL 5.7 之前派生表基本都会被物化成临时表意味着子查询结果先完整存一遍外层查询再读一遍代价很高。MySQL 5.7 之后优化器做了改进可以把派生表合并到外层查询里避免物化前提是派生表不涉及 GROUP BY、DISTINCT 或者相关引用等复杂操作。使用派生表时有一条铁律派生表必须有自己的别名比如上面的t而且同一查询里如果对派生表有多个引用MySQL 可能物化两次造成性能浪费。比如FROM (SELECT ...) t1 JOIN (SELECT ...) t2 ON t1.id t2.id如果两个子查询结构相同还不如写成 JOIN 一张真实表或者把结果先存到临时表。我踩过最深的坑是在派生表里用了ORDER BY以为能控制外层结果的顺序结果外层一加 GROUP BY派生表里的排序完全失效。后来才明白派生表只是个数据源顺序不是它的属性想要最终排序必须在最外层写 ORDER BY。4. UNION与UNION ALL合并结果集时的去重陷阱与性能真相4.1 去重机制背后的排序或哈希成本UNION 和 UNION ALL 都能把多条 SELECT 语句的结果按行合并起来唯一的区别是 UNION 会去重UNION ALL 不会。这个“唯一的区别”听起来不大实际影响却非常惊人。UNION 去重的实现方式MySQL 会对最终结果集做排序或者哈希去重。如果两个查询各返回十万行UNION 就得把二十万行放一起比较重复行这个过程需要额外的内存和 CPU。UNION ALL 则只是把两个结果集直接拼接连比较都没有速度差异在数据量大时可以达到数量级。我做过一个实际测试两个各包含 30 万行的查询UNION ALL 耗时 0.2 秒UNION 耗时 1.4 秒。这里的问题不是“去重这个需求不该有”而是很多场景你根本不需要去重。比如两张分区表按年份拆分年份必然不同id不可能重复这时候用 UNION 完全是浪费。判断是否该用 UNION ALL 的方法很简单如果业务上能确认各个查询的返回结果之间不可能有重复就用 UNION ALL。如果无法确认再仔细想想你到底是想做“合并”还是做“合并且去重”。4.2 列数、类型与排序限制UNION 看似宽松实际上对列的要求很严格。它要求每条 SELECT 语句返回的列数必须相同并且对应列的数据类型需要兼容。这是因为结果集的表结构是第一条 SELECT 确定的后面的语句相当于往这个表里灌数据。如果两边列数不一致SQL 直接报错。类型不兼容时 MySQL 可能会做隐式转换但转换可能丢失精度比如一个是字符串一个是数字。这种情况我会主动用CAST把列统一成同一类型避免结果集出现不可控的截断。还有一个非常容易踩的坑是ORDER BY和LIMIT的位置。整个 UNION 语句里只能有一个 ORDER BY 和一个 LIMIT而且它们作用于合并后的完整结果集不能作用于单独某一个分支。比如你希望第一个子查询只取前 100 条第二个子查询取前 50 条然后再合并不能直接写... LIMIT 100 UNION ... LIMIT 50。正确做法是用子查询包一层SELECT * FROM ( SELECT * FROM table_a WHERE type 1 ORDER BY create_time LIMIT 100 ) a UNION ALL SELECT * FROM ( SELECT * FROM table_b WHERE type 2 ORDER BY create_time LIMIT 50 ) b;反之如果你想让整个合并结果再按某个字段排序就必须把 ORDER BY 放在最外层。4.3 UNION 和 JOIN 的适用场景对比很多人在 JOIN 和 UNION 之间不知道该选哪个。我的判断标准是JOIN 是横向扩展列UNION 是纵向扩展行。举一个场景你要查“使用了优惠券的订单”和“退款订单”两种订单集合。如果用 JOIN你会把订单表和优惠券表、退款表连接起来逻辑上是在同一张结果集里增加字段这会把订单行重复展开充满了坑。如果用 UNION则可以让两个独立查询分别返回自己的订单列表再合并展示非常自然。还有分表场景系统里用户量巨大按用户 ID 后缀拆成了users_0到users_9十张表。要查某个范围内的所有用户最适合的就是把十个查询结果用 UNION ALL 合起来。这种场景如果用 JOIN反而无从下手。我自己的习惯是先问自己最终结果的行和列分别代表什么。如果行数不变但列数变多用 JOIN如果列数不变但行数叠加用 UNION。5. 复合查询性能排查工具箱EXPLAIN、索引与改写策略5.1 EXPLAIN 的 type 字段怎么读排查复合查询性能问题第一件事不是加索引而是看执行计划。MySQL 里用EXPLAIN加在 SQL 前面就能看到优化器打算怎么执行这条查询。EXPLAIN SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON o.user_id u.id GROUP BY u.name;返回结果里最值得看的是type字段它表示访问类型const常量定位比如按主键查一行最快eq_ref被驱动表通过唯一索引或主键被连接非常高效ref通过非唯一索引等值匹配不错range使用了索引范围扫描比如 between、in、like 前缀index遍历整个索引比全表扫描稍好ALL全表扫描通常需要警惕复合查询的优化目标就是让多表连接时访问类型尽可能达到eq_ref或ref。只要看到某个表是ALL并且它还参与 JOIN 或子查询匹配那基本就是性能瓶颈点。另外要结合rows字段看优化器估算了多少行。rows太大说明结果集扩散严重可能需要重新评估连接条件或加过滤条件。5.2 复合查询的最佳索引设计逻辑索引在复合查询中的角色是让“找匹配行”这个动作更快。设计索引前先分析复合查询里哪些字段参与连接哪些字段参与过滤哪些字段参与排序分组。连接字段必须有索引这是最基本的要求。例如订单表的user_id必须建索引否则 JOIN 时被驱动表只能全表扫描。过滤字段也基本必须有索引尤其是WHERE里的高频条件。排序分组字段则可以尝试用联合索引避免 file sort。我举一个实际的索引组合订单表有product_id和create_time两列你经常写“查某个商品在某个时间段内的订单”那创建一个复合索引(product_id, create_time)会非常划算。因为 MySQL 可以先用product_id快速定位商品再利用索引有序性直接截取时间段连排序都省了。还有一个容易被忽略的优化是索引覆盖。如果查询只需要索引列不用回表读数据执行速度会快很多。比如只查product_id和count(*)类似SELECT product_id, COUNT(*) FROM orders GROUP BY product_id只要在product_id上建索引优化器就能直接使用索引扫描完成不需要回表。5.3 三招改写慢复合 SQL 的经验第一招尽量缩小驱动表。前面提过“小表驱动大表”的原则但现实中驱动表往往可以被 WHERE 条件先过滤。比如在 LEFT JOIN 之前先用WHERE status 1把用户表缩小再去连接大订单表效果常比直接连接好得多。这也是为什么我会习惯把表连接放到过滤条件之后考虑。第二招相关子查询改写为 JOIN。很多查询如“查有订单的用户”既可以用EXISTS也可以用 JOINSELECT DISTINCT u.* FROM users u JOIN orders o ON o.user_id u.id;如果 orders 表的user_id有索引JOIN 写法能利用索引去重往往比相关子查询更直接。同样的场景也适用于“查没有订单的用户”用LEFT JOIN加WHERE o.id IS NULL比NOT IN更快、更稳定。第三招避免在索引列上使用函数或表达式。WHERE DATE(order_time) 2025-01-01会让 order_time 上的索引失效因为他要把每一行的order_time先算成日期再比较。应该改写成范围查询WHERE order_time 2025-01-01 AND order_time 2025-01-02。类似的还有在索引列上做加减运算、字符串拼接等都要尽量避免。这三招不是什么高深理论都是我从线上慢查询日志里一条一条揪出来的实际问题。每次遇到慢 SQL不要急着加索引先看逻辑能不能改写改写完之后再考虑索引设计。6. 一个真实业务场景从需求到复合SQL的完整落地过程6.1 业务需求与表结构理论讲太多容易晕我用一个我处理过的模拟需求来完整走一遍复合查询的落地过程。需求可以简化成统计每个商品分类下近 30 天订单量最高的 3 个商品输出分类名称、商品名称和订单量。涉及三张表分类表、商品表、订单明细表。定义如下CREATE TABLE categories ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE products ( id INT PRIMARY KEY, cate_id INT, name VARCHAR(50), KEY idx_cate_id (cate_id) ); CREATE TABLE order_items ( id INT PRIMARY KEY, product_id INT, create_time DATETIME, KEY idx_product_time (product_id, create_time) );订单明细表里每一行代表一个商品被下单一次。现在要做的复合查询至少经历了“连接 分组 聚合 排名筛选”这四个阶段。6.2 从分组统计到取 Top3 的两种写法第一步统计每个分类每个商品的订单量。这一步需要把order_items和products连接起来因为订单明细里没有分类信息SELECT p.cate_id, p.id AS product_id, COUNT(*) AS order_cnt FROM order_items oi JOIN products p ON oi.product_id p.id WHERE oi.create_time NOW() - INTERVAL 30 DAY GROUP BY p.cate_id, p.id;这是典型的“先缩小数据范围再分组聚合”思路。WHERE 条件写在 JOIN 之后但优化器会先过滤order_items的时间范围再和商品表连接。第二步要按分类分组取订单量前三的商品。在 MySQL 8.0 里最优雅是用窗口函数WITH sales AS ( SELECT p.cate_id, p.id AS product_id, p.name AS product_name, COUNT(*) AS order_cnt FROM order_items oi JOIN products p ON oi.product_id p.id WHERE oi.create_time NOW() - INTERVAL 30 DAY GROUP BY p.cate_id, p.id, p.name ) SELECT c.name AS category_name, s.product_name, s.order_cnt FROM ( SELECT s.*, ROW_NUMBER() OVER ( PARTITION BY s.cate_id ORDER BY s.order_cnt DESC ) AS rn FROM sales s ) s JOIN categories c ON s.cate_id c.id WHERE s.rn 3 ORDER BY c.name, s.order_cnt DESC;这条复合查询里CTEWITH sales AS先算每个商品销量窗口函数ROW_NUMBER()在分类内编号外层 JOIN 分类表取分类名最后过滤rn 3。整个流程每一步都读起来很顺。如果项目还在用 MySQL 5.7没有窗口函数那就得用相关子查询去模拟“每个分类内销量前 3”。逻辑是对每个分类里的每个商品统计销量大于它的商品个数小于 3 就代表它在前 3。这种写法非常绕而且在大数据量下性能很差我当年写过一次之后就再也不想了也是因为那次我下了决心建议团队升到 8.0。6.3 执行计划验证与最终优化不管用哪种写法最终一定要做性能验证。我对上面的 SQL 执行一遍EXPLAIN关注两个点一是order_items是否走idx_product_time索引来过滤时间范围二是products是否通过主键或索引被连接。结果理想时优化器会用order_items的索引范围扫描再对每个商品去products按主键回查categories 表数据量很小成本极低。如果发现order_items走了全表扫描那就要检查create_time的数据类型和 WHERE 写法确认没有函数包裹。最终我还会给order_items表增加一个包含create_time前缀的复合索引来支持时间过滤(create_time, product_id)。如果业务上经常做“某商品多时间区间”的查询那(product_id, create_time)更合适。这种取舍没有绝对答案只能结合业务里的真实查询模式决定。我自己的体会是复合查询写多了之后最重要的能力不是记语法而是知道每条 SQL 会被数据库拆成哪几步去执行。以前我也是先写出来再说跑得慢就去加索引后来发现很多慢查询其实不是索引不够而是逻辑写反了比如把过滤条件放错位置、用子查询强行替代 JOIN、该用 UNION ALL 却用了 UNION。从今天开始你可以试着写完每条复合查询后立刻跑一次 EXPLAIN盯着 type 和 rows 看两分钟很多性能隐患当场就能发现。这比收藏一堆优化文章管用得多。