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

01. 环境准备与场景构建

在深入探讨 MySQL 复合查询之前，脱离业务场景谈 SQL 都是“耍流氓”。为了让后续的讲解具备实战意义，我们需要构建一个经典的电商系统数据库模型。

我们将使用以下四张表来演示所有的查询操作：

1. User（用户表）：存储用户信息。
2. Product（商品表）：存储商品基本信息。
3. Orders（订单表）：存储订单主表信息。
4. OrderItem（订单详情表）：存储订单中具体的商品和数量。

1.1 初始化 SQL 脚本

请在你的本地 MySQL 环境（推荐 MySQL 5.7 或 8.0+）中执行以下脚本：

-- 创建用户表
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `level` int(4) DEFAULT '1' COMMENT '用户等级',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 创建商品表
CREATE TABLE `products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `name` varchar(100) NOT NULL COMMENT '商品名称',
  `price` decimal(10,2) NOT NULL COMMENT '单价',
  `stock` int(11) NOT NULL DEFAULT '0' COMMENT '库存',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 创建订单主表
CREATE TABLE `orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `order_no` varchar(64) NOT NULL COMMENT '订单号',
  `total_amount` decimal(12,2) DEFAULT '0.00' COMMENT '订单总金额',
  `status` tinyint(4) DEFAULT '0' COMMENT '状态：0-待支付, 1-已支付, 2-已取消',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  UNIQUE KEY `uk_order_no` (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

-- 创建订单详情表
CREATE TABLE `order_items` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '详情ID',
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `product_id` bigint(20) NOT NULL COMMENT '商品ID',
  `quantity` int(11) NOT NULL COMMENT '购买数量',
  `price` decimal(10,2) NOT NULL COMMENT '购买时单价',
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单详情表';

-- 插入模拟数据
INSERT INTO `users` (`username`, `email`, `level`) VALUES 
('Alice', 'alice@example.com', 1),
('Bob', 'bob@example.com', 2),
('Charlie', 'charlie@example.com', 1),
('David', 'david@example.com', 3); -- David 没有下过单

INSERT INTO `products` (`name`, `price`, `stock`) VALUES 
('MacBook Pro', 12000.00, 10),
('iPhone 15', 6000.00, 50),
('AirPods', 1200.00, 100),
('Type-C Cable', 50.00, 200);

INSERT INTO `orders` (`user_id`, `order_no`, `total_amount`, `status`) VALUES 
(1, 'ORD20231101001', 13200.00, 1), -- Alice 的订单
(2, 'ORD20231101002', 6000.00, 1),  -- Bob 的订单
(1, 'ORD20231102001', 50.00, 0);   -- Alice 的第二个订单

INSERT INTO `order_items` (`order_id`, `product_id`, `quantity`, `price`) VALUES 
(1, 1, 1, 12000.00), -- Alice 买了 MacBook
(1, 3, 1, 1200.00),  -- Alice 买了 AirPods
(2, 2, 1, 6000.00),  -- Bob 买了 iPhone
(3, 4, 1, 50.00);    -- Alice 买了线

---

02. 复合查询的核心概念

在单表查询（SELECT * FROM table WHERE ...）无法满足业务需求时，我们需要从多个维度获取数据，或者将多个逻辑步骤合并为一次交互。这就是复合查询。

复合查询主要包含以下三种形态：

1. 集合运算（Set Operations）：将多个查询结果集纵向合并（如 UNION）。
2. 连接查询（Joins）：将多个表横向拼接（如 LEFT JOIN）。
3. 子查询（Subqueries）：查询中嵌套查询。

为什么需要复合查询？

• 数据归一化（Normalization）的代价：为了减少数据冗余，我们将数据拆分到了不同的表中（如用户和订单分开）。读取时，必须通过复合查询将它们“组装”回来。
• 减少网络交互：相比于在应用代码（Java/Python）中进行多次数据库调用并在内存中组装数据，一次复杂的 SQL 查询往往能利用数据库引擎的优化器，且大幅减少网络 I/O 开销。
• 复杂逻辑处理：诸如“查询购买金额前 10% 的用户的平均客单价”这类逻辑，单表查询无法直接完成。

---

03. 集合运算：UNION 与 UNION ALL

集合运算处理的是结果集，它要求参与运算的两个 SELECT 语句具有相同数量的列，且对应列的数据类型兼容。

3.1 基础用法

假设我们需要导出一份名单，包含所有的“用户名”和“商品名”，用于制作一份混合索引。

-- 这里的 SELECT 列表必须数量一致
SELECT username AS name, 'User' AS type FROM users
UNION ALL
SELECT name AS name, 'Product' AS type FROM products;

3.2 UNION vs UNION ALL（面试高频）

特性	UNION	UNION ALL
去重	是（默认去除重复行）	否（保留所有行）
排序	可能会引起隐式排序	不会排序
效率	低（需要遍历结果集去重）	高（直接追加结果）
推荐场景	必须要求结果唯一时	绝大多数场景，特别是数据量大时

最佳实践：除非你非常确定结果集中包含不需要的重复数据，否则永远优先使用 UNION ALL。它省去了数据库去重（Deduplication）的高昂开销。

---

04. 连接查询（JOIN）：横向的数据拼图

JOIN 是关系型数据库的灵魂。它基于两个表之间的关联字段（Foreign Key）将行匹配起来。

4.1 连接的本质：集合论视角

理解 JOIN 最好的方式是韦恩图（Venn Diagram）：

• INNER JOIN（内连接）：交集。只返回两个表中都有匹配的数据。
• LEFT JOIN（左连接）：左表全集 + 右表匹配集。左表有但右表没有的，右表字段补 NULL。
• RIGHT JOIN（右连接）：右表全集 + 左表匹配集。通常用 LEFT JOIN 替代，保持阅读习惯一致。

4.2 实战：查询用户的订单详情

需求：查询所有下过单的用户及其订单号。

SELECT u.username, o.order_no, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

结果分析：用户 David 不在结果中，因为他没有下过单（orders 表中没有 user_id 为 David ID 的记录）。

需求：查询所有用户及其订单号（即使没买过东西也要显示用户）。

SELECT u.username, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

结果分析：David 会出现在结果中，对应的 order_no 为 NULL。

4.3 多表级联 JOIN

需求：查询“Alice”买过的所有“商品名称”。这涉及三张表的连接：users -> orders -> order_items -> products。

SELECT 
    u.username, 
    p.name AS product_name, 
    oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE u.username = 'Alice';

4.4 常见陷阱：ON 与 WHERE 的区别

在 LEFT JOIN 中，过滤条件写在 ON 子句和 WHERE 子句中效果完全不同。

错误示例：想要查询所有用户，以及他们 2023 年 11 月 2 日的订单。

-- 错误写法：LEFT JOIN 失效，退化为 INNER JOIN
SELECT u.username, o.order_no, o.create_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.create_time >= '2023-11-02 00:00:00';

问题：WHERE 子句是在连接操作完成之后执行的。如果 o.create_time 为 NULL（即用户没订单），NULL >= '...' 结果为 False，该行被过滤掉。于是 David 消失了。

正确写法：

-- 正确写法：条件写在 ON 中，作为连接时的匹配条件
SELECT u.username, o.order_no, o.create_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.create_time >= '2023-11-02 00:00:00';

解析：这表示“取出所有用户，如果该用户有 11 月 2 号后的订单就关联，没有就填 NULL”。David 依然存在。

---

05. 子查询（Subqueries）：嵌套的逻辑

子查询是指嵌套在另一个 SQL 语句（SELECT, INSERT, UPDATE, DELETE）中的 SELECT 语句。

5.1 标量子查询（Scalar Subquery）

子查询结果只有一行一列（一个值）。

需求：查询比“MacBook Pro”价格高的所有商品。

SELECT * FROM products 
WHERE price > (SELECT price FROM products WHERE name = 'MacBook Pro');

5.2 IN 与 EXISTS 的爱恨情仇

场景：查询下过订单的用户信息。

方法 A：使用 IN

SELECT * FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM orders);

逻辑：先执行子查询拿到所有 user_id 列表，再在主查询中过滤。适用于子查询结果集较小的情况。

方法 B：使用 EXISTS

SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

逻辑：遍历 users 表的每一行，将 u.id 传入子查询检查是否存在。适用于主表（users）较小，子查询表（orders）很大的情况，因为 EXISTS 如果命中索引，可以快速返回 true。

5.3 派生表（Derived Tables）

将子查询的结果当做一个临时表来使用，通常出现在 FROM 子句中。

需求：查询每个用户的订单总数，并筛选出订单数大于 1 的用户。

SELECT temp.username, temp.order_count
FROM (
    SELECT u.username, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id
) AS temp
WHERE temp.order_count > 1;

5.4 致命性能杀手：相关子查询（Correlated Subquery）

请看下面的 SQL：

-- 查询每个用户最近的一笔订单金额
SELECT u.username, 
  (SELECT total_amount FROM orders o 
   WHERE o.user_id = u.id 
   ORDER BY create_time DESC LIMIT 1) as last_amount
FROM users u;

原理：对于 users 表中的每一行，数据库都要执行一次括号里的子查询。如果 users 有 10 万行，子查询就要执行 10 万次。这就是 N+1 问题在 SQL层面的体现。 优化：通常使用 JOIN 或窗口函数（MySQL 8.0+）来重写。

---

06. 进阶武器：CTE（公共表表达式）与 窗口函数

如果你的 MySQL 版本是 8.0 及以上，请尽量放弃复杂的嵌套子查询，拥抱 CTE。

6.1 CTE (Common Table Expressions)

CTE 使用 WITH 关键字，可以将复杂的子查询定义为一个“临时视图”，让代码逻辑像瀑布一样清晰。

重写 5.3 的案例：

WITH UserOrderStats AS (
    SELECT u.username, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.username
)
SELECT * FROM UserOrderStats 
WHERE order_count > 1;

优势：可读性极高，且 CTE 在同一个查询中可以被多次引用。

6.2 窗口函数（Window Functions）

这是解决“分组取 Top N”类问题的神器。

需求：查询每个用户最贵的一笔订单。如果用传统 Group By 很难直接拿到对应的 Order ID。

SELECT *
FROM (
    SELECT 
        id, user_id, total_amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total_amount DESC) as rn
    FROM orders
) t
WHERE t.rn = 1;

解析：PARTITION BY user_id 相当于在内存中按用户分组，ORDER BY 排序，ROW_NUMBER() 给每组生成序号。最后外层取序号为 1 的记录。

---

07. 性能优化与最佳实践

会写 SQL 不叫资深，会写高性能 SQL 才是。

7.1 小表驱动大表

在 JOIN 查询中，MySQL 优化器通常会自动选择驱动表（Driving Table），但在复杂查询中，我们需要有意识地控制。

• 原则：用数据量小（经过 WHERE 过滤后）的表去匹配数据量大的表。
• 原因：驱动表全表扫描（或索引扫描），被驱动表走索引查询。驱动表越小，循环次数越少。

7.2 索引失效的 JOIN

确保 ON 子句中的关联字段类型完全一致，且都建立了索引。

• 类型隐式转换：如果 users.id 是 varchar，而 orders.user_id 是 bigint，JOIN 时索引会失效，导致全表扫描，性能雪崩。
• 字符集不一致：如果一个表是 utf8，另一个是 utf8mb4，JOIN 时索引也可能失效。

7.3 分页查询优化（Deep Paging）

场景：orders 表有 1000 万数据，要查第 100 万页的数据。 SELECT * FROM orders LIMIT 1000000, 10;

问题：MySQL 需要读取 1000010 条记录，丢弃前 100 万条，非常慢。

优化策略（延迟关联）：

SELECT t1.* FROM orders t1
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) t2 ON t1.id = t2.id;

原理：子查询只查主键 ID（走覆盖索引，不需要回表），速度极快。拿到 10 个 ID 后，再通过主键 JOIN 原表获取详情。

7.4 避免 SELECT *

在复合查询中，SELECT * 危害倍增：

1. 增加网络带宽：传输了不需要的字段。
2. 阻碍覆盖索引：如果只查 SELECT id, order_no，可能直接从索引树返回结果，不需要去磁盘读行数据（回表）。SELECT * 强制回表。

---

08. 实战案例：统计报表生成

让我们把学到的知识整合，完成一个复杂的报表需求。

需求：生成“高价值用户分析报表”，要求：

1. 列出等级为 1 或 2 的用户。
2. 统计他们的总消费金额。
3. 统计他们购买过最多的商品名称。
4. 只显示总消费超过 5000 的用户。

SELECT 
    u.username,
    u.level,
    SUM(o.total_amount) as total_spend,
    -- 获取购买量最大的商品名（此处简化逻辑，仅作为复杂SQL演示）
    (
        SELECT p.name 
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        WHERE oi.order_id IN (SELECT id FROM orders WHERE user_id = u.id)
        ORDER BY oi.quantity DESC 
        LIMIT 1
    ) as favorite_product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.level IN (1, 2)
GROUP BY u.id, u.username, u.level
HAVING total_spend > 5000;

注：实际生产中，第 3 点“购买最多的商品”通常建议拆分为独立查询或使用 ETL 处理，因为在 Group By 列表中嵌入复杂关联子查询会严重拖慢速度。这里仅展示 SQL 的表达能力。

---

09. 总结

从单表查询到复合查询，是从“查字典”到“逻辑推理”的跨越。

1. 基础：牢记 UNION ALL 优于 UNION。
2. 核心：理解 LEFT JOIN 是保留左表全集，注意 WHERE 条件的位置。
3. 进阶：善用 MySQL 8.0 的 CTE 和窗口函数，让 SQL 更优雅。
4. 性能：永远关注索引、驱动表选择，以及避免相关子查询。

掌握复合查询，不仅是为了应付面试，更是为了在面对复杂的业务逻辑时，能从数据库层面给出最高效的解决方案，而不是把压力全部甩给后端代码。