复合查询这四个字乍一听好像很高端其实拆开看就是多表连接、子查询、集合查询这几件事。我早年带组里新人的时候发现能把单表SQL写得很溜的人不少但一遇到“查每个城市下单用户的消费总额”“找出比所有电子类商品都便宜的数码配件”这种稍微绕一点的业务需求就开始蒙圈。复合查询就是专门用来解决这类问题的当单表已经无法满足业务需要需要把多张表的数据横向拼在一起、把一条查询的结果作为另一条查询的条件、或者把几个结果集纵向合并时靠的就是这套东西。这篇文章适合三类人一是刚学完基础SQL、知道SELECT FROM WHERE但是面对JOIN就手抖的读者二是准备面试想一口气把复合查询的考点、坑点都过一遍的同学三是工作里被复杂SQL折磨、想少走弯路的开发。我会用一套电商场景的建表语句贯穿全文从连接查询讲起依次拆子查询和集合查询最后用EXPLAIN聊性能排查顺便填掉几道高频面试题。读的时候最好把电脑打开跟着SQL跑一遍光看不练等于白看。1. 复合查询的整体设计思路先搞懂它到底解决什么问题1.1 单表查询的瓶颈与三种复合形态先想一个问题为什么单表查询不够用因为业务数据天生是被打散存储的。关系型数据库在建模阶段就要做规范化设计把用户、订单、商品拆到不同的表里避免一份数据反复存储。这样虽然省了空间、方便了维护却给查询带来了一个麻烦你要的最终结果往往分散在多张表中。举个最直白的例子。订单表里存了user_id、订单金额但页面要显示的是用户名。如果你只会单表查询就只能查出订单再在程序里循环拿着user_id去用户表里逐条查用户名。这就是传说中的N1查询接口慢、代码丑、数据库压力还大。复合查询的价值就在这里把关联、过滤、聚合、合并这些事情在数据库内部一次做完。复合查询在MySQL里通常指三种形态形态核心作用典型场景连接查询横向合并多张表的字段订单表显示用户名、商品名子查询一条查询的结果作为另一条查询的条件或临时表筛选“下单金额超过500的用户”集合查询纵向合并多个查询的结果集合并两个活动的用户名单理解这三种形态的区别很关键。连接查询是“左右拼”把表A和表B的列拼成更宽的结果子查询是“上下套”把一条查询包在另一条里面集合查询是“上下拼”把多个结构相同的结果集叠在一起。大部分复杂SQL都是这三种的排列组合所以不用怕逐个拆开看都不复杂。1.2 一套能直接复现的电商表结构为了后面所有例子都有可跑的土壤先建一套电商场景的表。选这个模型是因为它覆盖了一对多用户到订单、多对多订单到商品的明细以及最常见的自引用结构足够练手。CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET utf8mb4; USE shop; CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, city VARCHAR(20) NOT NULL, reg_date DATETIME NOT NULL ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, total DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_user_id (user_id) ); CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, category VARCHAR(20) NOT NULL, price DECIMAL(10,2) NOT NULL ); CREATE TABLE order_items ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, qty INT NOT NULL, price DECIMAL(10,2) NOT NULL, KEY idx_order_id (order_id), KEY idx_product_id (product_id) );再插一点测试数据别太多够用就行。INSERT INTO users (name, city, reg_date) VALUES (用户A, 北京, 2024-01-10 10:00:00), (用户B, 上海, 2024-02-11 11:00:00), (用户C, 广州, 2024-03-12 12:00:00), (用户D, 深圳, 2024-04-13 13:00:00), (用户E, 杭州, 2024-05-14 14:00:00), (用户F, 北京, 2024-06-15 15:00:00); INSERT INTO products (name, category, price) VALUES (手机, 电子, 2999.00), (耳机, 电子, 499.00), (键盘, 电子, 399.00), (办公椅, 家具, 899.00), (台灯, 家具, 129.00), (咖啡壶, 生活, 259.00); INSERT INTO orders (user_id, total, status, created_at) VALUES (1, 3498.00, 2, 2024-07-01 10:00:00), (1, 129.00, 1, 2024-07-05 11:00:00), (2, 2999.00, 2, 2024-07-02 12:00:00), (3, 259.00, 1, 2024-07-03 13:00:00), (4, 158.00, 0, 2024-07-06 14:00:00), (6, 998.00, 2, 2024-07-08 15:00:00); INSERT INTO order_items (order_id, product_id, qty, price) VALUES (1, 1, 1, 2999.00), (1, 2, 1, 499.00), (2, 5, 1, 129.00), (3, 1, 1, 2999.00), (4, 6, 1, 259.00), (5, 4, 1, 158.00), (6, 4, 1, 899.00);后面所有例子都基于这套数据你完全可以跟着跑。建完表先别急着写SQL先花一分钟感受一下每张表的字段分布哪些是关键外键哪些字段会经常作为连接条件。这个习惯很重要很多人写不好复合查询不是不会语法而是对表结构不熟。2. 连接查询复合查询的地基也是面试最爱的考点2.1 INNER JOIN与LEFT JOIN结果集的差别一句话说清连接查询是复合查询里最基础、也最常用的形态。内连接和左连接的差别我用一句话概括INNER JOIN只保留两边都匹配上的行LEFT JOIN保留左表的全部行右表没匹配上就补NULL。具体看代码。第一个需求查订单归属的用户名。SELECT o.id AS order_id, u.name, o.total, o.status FROM orders o INNER JOIN users u ON o.user_id u.id;执行结果里只会出现有有效用户关联的订单。因为orders表里所有user_id都在users表里有对应这里看不出差别。来看左连接SELECT u.id, u.name, o.id AS order_id, o.total FROM users u LEFT JOIN orders o ON o.user_id u.id ORDER BY u.id;左连接把users表所有用户都留下来了。用户5杭州没有订单所以order_id和total是NULL。这就是LEFT JOIN的核心语义左表是主体右表是补充信息。那什么时候用INNER什么时候用LEFT我的经验是先问自己“结果集以谁为准”。如果你关心的是“某某动作”主体在动作方比如“已发货订单”就用INNER JOIN如果你关心的是“全集”主体在实体方比如“所有用户及其订单状态”哪怕没下单也要展示就用LEFT JOIN。顺带提一嘴RIGHT JOIN。MySQL支持它但实际工作中很少用因为把左右表换一下、改成LEFT JOIN就能表达同样语义。FULL OUTER JOIN在MySQL里不支持如果你真需要“两边都保留、没匹配补NULL”那要用LEFT JOIN和RIGHT JOIN的结果集做UNION后面讲到集合查询时会看到。2.2 ON与WHERE的差异一个藏得很深的逻辑陷阱这是我在日常Code Review里看到频率最高的坑LEFT JOIN的过滤条件写在WHERE里直接把LEFT JOIN变成了INNER JOIN而且结果还不报错。看这个对比。需求场景“查所有用户及其超过1000元的订单没有大额订单的用户也要保留”。第一种写法SELECT u.id, u.name, o.id AS order_id, o.total FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.total 1000 ORDER BY u.id;第二种写法SELECT u.id, u.name, o.id AS order_id, o.total FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.total 1000 ORDER BY u.id;第二种写法的结果会把没超过1000元订单的用户整行过滤掉。为什么会这样因为WHERE是在连接完成之后执行的连接先把所有用户和所有订单拼出来然后WHERE再砍掉不满足total 1000的行。一旦过滤那些右表全NULL的行自然也被砍了LEFT JOIN就名存实亡。所以规则很简单对右表做过滤、但希望保留左表所有行时条件必须写在ON里面写在WHERE里的条件会改变连接语义。这个坑在慢查询和结果集对不上的排查里特别常见凡是LEFT JOIN的结果比预期少第一条要查的就是WHERE里是不是误写了右表字段。2.3 自连接与多表连接一张表当成两张表用自连接是很多新人的盲区。它本质还是连接只不过连接的对象是同一张表。什么时候需要当一张表里的字段指向自己这张表的主键时。最典型的就是员工和上级的关系。看这张表CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT, KEY idx_manager (manager_id) ); INSERT INTO employees VALUES (1, 老板, NULL), (2, 经理A, 1), (3, 经理B, 1), (4, 员工A, 2), (5, 员工B, 2);要查出每个员工的上级名字就得把employees表同时当成员工表和经理表来用SELECT e.name AS employee_name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id m.id;核心是两个别名e和m。数据库里其实只有一张物理表但逻辑上通过两个别名拆成了两张虚拟表一张做主表一张做查找表。如果不用LEFT JOIN而用INNER JOIN老板那一行因为manager_id是NULL就会被丢掉这通常不是你要的结果。再说多表连接。电商里最常见的场景是“订单明细要同时显示用户名和商品名”order_items表要关联orders拿user_id再关联users拿用户名还要关联products拿商品名。SELECT oi.id AS item_id, u.name AS user_name, p.name AS product_name, oi.qty, oi.price FROM order_items oi JOIN orders o ON oi.order_id o.id JOIN users u ON o.user_id u.id JOIN products p ON oi.product_id p.id;三张以上表连接时建议每张表都起短的别名ON条件紧跟其后。连接的顺序一般不需要你操心优化器会自己调整但把ON写清楚是基本素养。这里有一个重要原则多表JOIN时连接的字段一定要有索引否则表一旦变大性能会断崖式下跌。我们在第5节讲执行计划时会展开。3. 子查询让一条SQL复用另一条SQL的结果3.1 标量子查询返回单值的子查询子查询就是嵌套在SQL里的另一个SELECT根据返回结果的形式分为标量子查询、行子查询、表子查询。先讲最简的标量子查询它返回一行一列像普通的值一样使用。典型场景是在SELECT列表里补字段。比如要查每个用户的订单总数不写JOIN直接用一个相关子查询SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.id) AS order_cnt FROM users u;这里子查询访问了外层users表的u.id所以叫相关子查询。执行逻辑大致是外层每取到一行用户就去orders表里按user_id统计一次。数据量小的时候很直观但数据量大了要小心——外层有多少行内层就要执行多少次和程序里的N1问题一个道理。标量子查询还经常出现在WHERE里。比如“查用户A的订单”SELECT * FROM orders WHERE user_id (SELECT id FROM users WHERE name 用户A);这里有个硬性要求标量子查询必须保证只返回一行一列。如果子查询结果是多行MySQL直接报错“Subquery returns more than 1 row”。所以写这种SQL之前要确认子查询的条件有唯一性约束兜底否则线上跑着跑着突然报错很尴尬。3.2 多行子查询与IN、ANY、ALL、EXISTS子查询返回多行时就不能用等号直接比较了必须配合IN、ANY、ALL、EXISTS这些关键字。IN最简单。比如“查所有下过单的用户”SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);ANY和IN在等值比较场景下是一个意思。WHERE id ANY (SELECT user_id FROM orders)就是WHERE id IN (...)的另一种写法IN更直观日常用得更多。ALL则表达“比子查询结果里的每一个都…”。比如要找出比所有电子类商品都便宜的商品SELECT id, name, price, category FROM products WHERE price ALL ( SELECT price FROM products WHERE category 电子 );这个查询的逻辑等价于“价格小于电子类商品里的最低价”也就是p.price 399。用ALL写的好处是语义直白不用自己再包一层MIN聚合。EXISTS是另一种思路它不关心子查询返回什么列只关心有没有行。典型写法SELECT u.id, u.name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.total 500 );子查询里写SELECT 1而不是SELECT *是一个好习惯因为EXISTS只看行的存在性列内容毫无意义。和IN比较经典的经验是外层表小、内层表大时EXISTS往往更快反过来则IN更快。但随着MySQL版本升级优化器会把IN子查询改写成半连接未必慢。所以别背死结论关键时候上EXPLAIN看真实计划。3.3 派生表与FROM子句把查询结果当成一张临时表子查询放在FROM子句里叫派生表。它的核心价值在于先对原始数据做一次加工把加工结果当成一张临时表再在外面继续查询。最经典的场景是“先聚合再基于聚合结果过滤”。比如“查出平均单价超过300的商品分类以及它们的最高价”SELECT category, max_price FROM ( SELECT category, MAX(price) AS max_price FROM products GROUP BY category ) t WHERE max_price 300;注意两个细节。第一派生表必须要有别名例子里的t就是。这是MySQL的强制语法要求不写直接报错。第二外层可以直接引用内层SELECT里定义的别名max_price因为这个别名在派生表里已经变成了真实列。MySQL对派生表的处理在不同版本里有差别。5.7开始优化器会尝试把派生表合并到外层查询减少物化开销但在某些情况下依然会物化成临时表。所以如果你发现一个带派生表的查询很慢可以先EXPLAIN看Extra里有没有“Materialize”字样再决定是优化索引还是改写法。派生表的另一个高频用法是“二次聚合”。比如先按用户算消费总额再找出消费总额超过全体平均值的人SELECT user_id, total_spent FROM ( SELECT user_id, SUM(total) AS total_spent FROM orders GROUP BY user_id ) t WHERE total_spent ( SELECT AVG(total_spent) FROM ( SELECT user_id, SUM(total) AS total_spent FROM orders GROUP BY user_id ) avg_t );这种嵌套看着吓人拆开读其实每一层都很简单。写复杂SQL最有效的方法是自顶向下拆需求先想最终要什么列再想这些列从哪一层来逐层包上去。4. 集合查询与综合案例UNION到底去不去重怎么选4.1 UNION与UNION ALL的差异连接查询是横向拼列集合查询是纵向拼行。MySQL里最常用的是UNION和UNION ALL。举个例子。把北京和上海的注册用户放在一个结果集里SELECT id, name, city FROM users WHERE city 北京 UNION SELECT id, name, city FROM users WHERE city 上海;两种写法跑出来的结果看起来一样但内部差别很大UNION默认会对结果集做去重而去重的实现往往需要在临时表里做排序或哈希UNION ALL完全没有这个步骤直接把两个结果集拼接。性能差异随着数据量增大非常明显。我实测过几十万行的量级UNION ALL可能毫秒级返回UNION因为多了去重排序可能慢一个数量级。所以在业务里只要你能确定两个集合逻辑上不可能重复一律用UNION ALL确实需要去重时再去单独评估用UNION还是用DISTINCT。什么时候需要真的UNION比如合并两个活动报名名单同一用户可能在两个活动里都报名了业务上只统计一次这时UNION就派上用场。4.2 集合查询的三条硬性规则集合查询虽然看着简单但有硬性规则违反任何一条都会报错。第一条每条SELECT返回的列数必须一致。二列拼三列直接报错。第二条对应位置的列类型要兼容。MySQL在类型不一致时做了隐式转换比如整型列拼字符串列虽然不报错但如果转换规则不符合预期结果可能很怪。尽量让对应列类型一致。第三条ORDER BY只能出现在整个UNION的最后。因为UNION是对“合并后的结果集”做排序所以单独给第一个SELECT加ORDER BY是语法错误。如果你真的需要对每个子查询先排序再合并需要把子查询用括号包起来并且配合LIMIT才有效(SELECT id, name, city FROM users WHERE city 北京 ORDER BY id LIMIT 3) UNION ALL (SELECT id, name, city FROM users WHERE city 上海 ORDER BY id LIMIT 3) ORDER BY city;另外合并后结果集的列名默认取第一个SELECT的列名后续SELECT的列名会被忽略。如果你需要统一列名以第一个为准就行。4.3 一个综合实战连接子查询聚合的组合用法单看每种语法都很简单真正考验人的是组合。这里我把前面的知识穿起来讲一个真实风格的综合需求“统计每个城市的用户数、下单用户数、订单总数和总金额按总金额降序取前3个城市并额外展示每个城市销量最高的商品名。”第一步先做城市维度的汇总。这里用LEFT JOIN因为要保留所有城市的所有用户即使用户没下过单SELECT u.city, COUNT(DISTINCT u.id) AS user_cnt, COUNT(DISTINCT o.id) AS ordered_user_cnt, COUNT(o.id) AS order_cnt, COALESCE(SUM(o.total), 0) AS total_amount FROM users u LEFT JOIN orders o ON o.user_id u.id GROUP BY u.city ORDER BY total_amount DESC LIMIT 3;注意COUNT(DISTINCT u.id)和COUNT(DISTINCT o.id)的区别前者统计城市用户数后者统计有订单的用户数。直接COUNT(o.id)统计的是订单数没下单的用户会贡献一个NULL不会计入但因为LEFT JOIN城市仍会保留。第二步查每个城市的头号商品。这里用窗口函数先按城市分组按销量排序再取每组第一名SELECT city, product_name, sold_qty FROM ( SELECT u.city, p.name AS product_name, SUM(oi.qty) AS sold_qty, ROW_NUMBER() OVER ( PARTITION BY u.city ORDER BY SUM(oi.qty) DESC, p.name ) AS rn FROM orders o JOIN order_items oi ON oi.order_id o.id JOIN products p ON p.id oi.product_id JOIN users u ON u.id o.user_id GROUP BY u.city, p.name ) t WHERE rn 1;这两条SQL需要的数据源不同第一条主体是用户维度第二条主体是订单明细维度所以分开写更清晰。工程上不必追求“一条SQL解决所有问题”拆成多条再在应用层或临时表里拼接往往更好维护、更好排查。这也是复合查询的一个重要思路查询是手段不是目的。5. 常见问题排查与性能优化实录5.1 先看执行计划EXPLAIN的几个关键字段我接手过很多“为什么这个SQL这么慢”的问题第一件事永远是EXPLAIN。不要猜数据量、索引、数据分布都会影响执行计划肉眼分析经常翻车。下面这条SQL我们看它的执行计划EXPLAIN SELECT o.id, u.name FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE u.city 北京;EXPLAIN结果里有几个字段要重点看。type是访问类型从好到差大致是system const eq_ref ref range index ALL。ALL是全表扫描通常意味着要优化ref是非唯一索引等值访问常见于JOIN条件命中索引eq_ref是唯一索引等值访问多发生在主键连接。key是实际用到的索引。如果key为NULL说明没走索引要排查where条件或join条件为什么没命中。rows是优化器预估的扫描行数这是个估算值不代表真实扫描行数但可以用来横向比较不同写法的代价。Extra里如果出现Using temporary或Using filesort通常意味着GROUP BY或ORDER BY没能利用索引产生了临时表和文件排序。数据量一大这两个词就是性能杀手。有一点要特别注意EXPLAIN并不会真正执行查询它只是基于统计信息模拟优化器选路。所以刚插入大量数据后统计信息可能过期需要ANALYZE TABLE更新一下否则EXPLAIN的结果会误导你。5.2 实战里的六个大坑坑现象原因解决方案漏写JOIN ON结果行数暴增形成笛卡尔积所有JOIN必须写ONLEFT JOIN WHERE右表条件左表行被过滤WHERE在连接后执行右表过滤条件移到ON里NOT IN遇到NULL结果为空NULL参与比较返回UNKNOWN改用NOT EXISTS大表IN子查询查询极慢子查询物化大列表改写为JOIN隐式类型转换索引失效、结果错误字段类型不一致统一字段类型GROUP BY非索引列Using temporary分组无法走索引加索引或按需降级展开讲一下NOT IN遇NULL这个坑因为它隐蔽且后果严重。假设orders表里有一个user_id是NULL执行SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);结果可能是空集。逻辑上id NOT IN (1, NULL)等价于id 1 AND id NULL而id NULL的结果是UNKNOWN整个条件不成立。所以只要子查询结果里出现NULLNOT IN的直接含义就变了。这个问题用NOT EXISTS改写最安全SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );NOT EXISTS只关心存在性不涉及NULL比较。这个改写值得记下来面试里问“NOT IN为什么可能返回空”就是在考这个点。5.3 三道高频面试题分组TopN、连续记录、行列转换面试和工作中常遇到的复合查询题型练一遍比背一百道题有用。第一题每个分类里价格最高的商品。SELECT id, name, category, price FROM products p WHERE price ( SELECT MAX(price) FROM products p2 WHERE p2.category p.category );这是典型的关联子查询内层按外层分类求最大值。如果有并列最高价这个写法会把并列的都查出来。如果业务上只要一行可以在MySQL 8.0里用窗口函数SELECT id, name, category, price FROM ( SELECT id, name, category, price, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn FROM products ) t WHERE rn 1;第二题连续3天都有订单的用户。这个题用窗口函数很经典思路是把日期减去按时间排序的行号连续日期会落在同一个组里SELECT user_id FROM ( SELECT user_id, created_at, DATE_SUB(created_at, INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at ) DAY) AS grp FROM orders ) t GROUP BY user_id, grp HAVING COUNT(*) 3;如果用户同一天有多笔订单要配合DISTINCT日期处理如果需要窗口函数按日期去重可以先用DISTINCT date再做窗口。这类题的共同点是把“连续性”转换成“日期减去行号的分组相等”掌握这一招连续登录、连续签到、连续消费都能解。第三题行列转换。MySQL没有PIVOT只能用条件聚合实现。比如统计每个用户不同状态的订单数把状态从行变成列SELECT user_id, SUM(CASE WHEN status 0 THEN 1 ELSE 0 END) AS pending_cnt, SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS paid_cnt, SUM(CASE WHEN status 2 THEN 1 ELSE 0 END) AS shipped_cnt FROM orders GROUP BY user_id;这种写法在报表场景里非常常见一个CASE WHEN就是一个目标列。列数固定时很好使如果列不固定就得靠程序侧动态拼接SELECT列表MySQL层面没有现成的PIVOT函数。这些题型我都反复用过可以说一句实在话复合查询的语法不难难的是把需求拆成正确的查询步骤。遇到复杂需求先画清楚表之间的关系再确定主体表然后逐层加连接、加过滤、加聚合。写完之后EXPLAIN看一眼确认没有全表扫描、没有Using filesort这个SQL才算真正写完。我个人这几年带项目最深的体会是能把复杂查询写对的人通常不是语法记得多熟而是对表结构、索引和执行计划有敬畏心。SQL写出来结果对只是第一步跑得快才是生产环境真正需要的。所以如果你正在学复合查询不用急着背各种写法先把我这套表建起来把例子逐个跑一遍再对着EXPLAIN调一调索引很快就能建立手感。那些看起来高不可攀的复杂SQL拆到最后也无非是连接、子查询、聚合、排序这几板斧。
