企业官网建设流程全解析

复合查询这四个字，乍一听好像很高端，其实拆开看就是多表连接、子查询、集合查询这几件事。我早年带组里新人的时候，发现能把单表SQL写得很溜的人不少，但一遇到“查每个城市下单用户的消费总额”“找出比所有电子类商品都便宜的数码配件”这种稍微绕一点的业务需求，就开始蒙圈。复合查询就是专门用来解决这类问题的：当单表已经无法满足业务需要，需要把多张表的数据横向拼在一起、把一条查询的结果作为另一条查询的条件、或者把几个结果集纵向合并时，靠的就是这套东西。

这篇文章适合三类人：一是刚学完基础SQL、知道SELECT FROM WHERE但是面对JOIN就手抖的读者；二是准备面试，想一口气把复合查询的考点、坑点都过一遍的同学；三是工作里被复杂SQL折磨、想少走弯路的开发。我会用一套电商场景的建表语句贯穿全文，从连接查询讲起，依次拆子查询和集合查询，最后用EXPLAIN聊性能排查，顺便填掉几道高频面试题。读的时候最好把电脑打开，跟着SQL跑一遍，光看不练等于白看。

1. 复合查询的整体设计思路：先搞懂它到底解决什么问题

1.1 单表查询的瓶颈与三种复合形态

先想一个问题：为什么单表查询不够用？因为业务数据天生是被打散存储的。关系型数据库在建模阶段就要做规范化设计，把用户、订单、商品拆到不同的表里，避免一份数据反复存储。这样虽然省了空间、方便了维护，却给查询带来了一个麻烦：你要的最终结果往往分散在多张表中。

举个最直白的例子。订单表里存了user_id、订单金额，但页面要显示的是用户名。如果你只会单表查询，就只能查出订单，再在程序里循环拿着user_id去用户表里逐条查用户名。这就是传说中的N+1查询，接口慢、代码丑、数据库压力还大。复合查询的价值就在这里：把关联、过滤、聚合、合并这些事情在数据库内部一次做完。

复合查询在MySQL里通常指三种形态：

理解这三种形态的区别很关键。连接查询是“左右拼”，把表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统计一次。数据量小的时候很直观，但数据量大了要小心——外层有多少行，内层就要执行多少次，和程序里的N+1问题一个道理。

标量子查询还经常出现在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 实战里的六个大坑

展开讲一下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，整个条件不成立。所以只要子查询结果里出现NULL，NOT 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，拆到最后，也无非是连接、子查询、聚合、排序这几板斧。