MySQL 四大连接与笛卡尔乘积：从数据爆炸到执行计划

第一次见到"笛卡尔乘积"这个词，是很多年前帮一个做数据库课程设计的朋友排查问题。他的需求很简单：查一批订单以及下单用户的信息。SQL 写了三张表，跑出来的结果集有 120 多万行，而订单表实际只有 8 千多条数据。他以为是数据库坏了，甚至怀疑是 MySQL 的版本问题。我把 SQL 拉过来一看，两张表的关联字段类型一个是大整型、一个是字符串，再加上其中一张表压根没写连接条件，优化器只能老老实实做全排组合——也就是笛卡尔乘积。这件事以后，我养成了一个习惯：只要多表查询结果的行数比自己预估的高出一个数量级，第一反应不是查数据，而是先看连接条件。

这篇内容会把 MySQL 里的四大连接方式——INNER JOIN、LEFT JOIN（RIGHT JOIN）、CROSS JOIN、以及经常被忽略的自连接——和笛卡尔乘积放在一起讲清楚。不是教科书式的语法罗列，而是从"为什么会产生这个结果""什么情况下优化器会走哪条路""索引该怎么配"这些真正影响你项目的问题出发。不管你是正在做数据库课程设计的学生，还是写了几年 CRUD 但在多表查询上一直靠感觉的开发者，这里面的推演过程都可以直接拿去套用。

1. 从一次"数据爆炸"说起：笛卡尔乘积是怎么被制造出来的

1.1 那张突然变成 120 万行的订单表

先把场景还原一下，因为它太典型了。当时的表结构大致是这样：orders表存订单，users表存用户，order_items表存订单明细。朋友想统计每个用户的订单数量，SQL 写成了这样：

SELECT u.user_name, COUNT(o.id) AS order_cnt FROM users u, orders o, order_items oi WHERE u.id = o.user_id GROUP BY u.user_name;

注意看，order_items这张表被FROM出来了，但WHERE里完全没有出现它和另外两张表的关联条件。MySQL 拿到这条语句后的执行逻辑是：先把users和orders按u.id = o.user_id连起来，得到一个中间结果集，然后把order_items的每一行都和中间结果集的每一行组合一次。也就是说，如果中间结果集有 8000 行，order_items有 150 行，最终参与GROUP BY的行数就是 120 万。

COUNT(o.id)在这一步统计出来的数字自然全是错的，因为每一条订单都被重复计了 150 次。这就是笛卡尔乘积最朴素的表现形式：连接条件缺失，两个集合无条件两两组合。

这里有个经验值得记下来：当你的 SQL 里FROM后面跟了三张及以上的表，就应该养成一个自检动作——逐张表确认它是否在ON或WHERE里被"拴住"了。技巧是画一张小图，表名写成点，连接列写成线，如果有一张表是孤立点，那就是问题所在。这个方法比反复读 SQL 快得多，尤其是表超过五张的时候。

1.2 笛卡尔积的数学定义，以及 MySQL 实际是怎么执行的

从集合论的角度，A 集合和 B 集合的笛卡尔积记作 A × B，结果是所有有序对的集合，行数为 |A| × |B|。三张表就是 |A| × |B| × |C|，指数级往上蹿。这也是为什么它很难被"顺手写出来"——两个一千行的表组合起来就是一百万行，内存和磁盘都顶不住。

但需要澄清一个很多人误解的点：CROSS JOIN本身只是一种显式的书写方式，并不等于错误。真正危险的是"隐式笛卡尔积"，也就是用逗号分隔表却忘记写连接条件。MySQL 在解析阶段无法判断你到底是想要全组合还是漏写了条件，它只会按语法执行。

MySQL 对多表连接的处理，可以粗略理解为两层循环：

for row_a in table_A: for row_b in table_B: if 满足连接条件(row_a, row_b): output(row_a, row_b)

这是嵌套循环连接（Nested Loop Join）的简化模型。当条件过滤掉大部分组合时，它的效率是可以接受的；当没有条件时，内层循环每一轮都全量输出，就是纯粹的乘法。

还有一种情况更隐蔽：连接条件写了，但因为类型或字符集不匹配，导致条件无法被有效利用。比如orders.user_id是BIGINT，而users.id是VARCHAR(32)，MySQL 在执行时会对每一行做隐式类型转换。这不会直接产生笛卡尔积，但它会让索引失效，把本该是eq_ref的访问方式降级成ALL全表扫描，复杂度从 O(n) 涨到 O(n²)，在大表上表现得跟笛卡尔积差不多难受。

我曾经处理过一个只差 3 倍数据量就雪崩的案例：两张表分别是 40 万和 60 万行，连接列一个是utf8mb4_general_ci排序规则，另一个是utf8mb4_unicode_ci。MySQL 在建表和连接时都允许这种差异存在，但在执行连接时无法走索引，最终查询耗时从 0.08 秒涨到 40 多秒。解决方式是把两边的字符集和排序规则统一，重建索引。

1.3 三种最容易被忽略的"隐性笛卡尔积"

除了漏写条件，还有几类写法会悄悄放大结果集，我在代码评审里见过很多次：

第一类是连接条件写在SELECT的派生列里。比如SELECT * FROM a, b WHERE a.id = 1，然后以为SELECT里算个b.aid就等于连上了。只要WHERE或ON里没有交集条件，组合就是全量的。

第二类是子查询没有正确关联主表。这种写法在外行看来完全没毛病：

SELECT o.id, o.amount, (SELECT COUNT(*) FROM order_items oi WHERE oi.status = 1) AS cnt FROM orders o;

子查询里的oi和外面的o没有任何关联，每一条orders都会拿到同一个全表统计值。虽然这里执行的是标量子查询而不是笛卡尔积，但结果集的口径已经错了，属于同一类认知误区。

第三类是多表连接时"漏中间表"。A 和 B 有直接关联，B 和 C 有直接关联，但 A 和 C 之间没有。如果你写FROM a JOIN c ON ...，就等于跳过了 B 这层关系，如果ON条件又写得不够严谨，A 里的一行可能匹配到 C 里多行，行数就被放大了。

排查这三类问题，最有效的手段是EXPLAIN。看到type列出现ALL且rows数值接近表的总行数，同时Extra里没有Using where，基本可以判定条件没被用上。另外一个非常好用的小技巧：在开发阶段先分别执行SELECT COUNT(*) FROM a;和SELECT COUNT(*) FROM b;，把两个数字乘一下，如果最终结果接近这个乘积，那一定有表没被拴住。

2. INNER JOIN：优化器最愿意配合的那一种连接

2.1 ON 和 WHERE 在内连接里等价，但语义层次不同

对INNER JOIN来说，ON里的条件和WHERE里的条件在执行结果上是等价的，优化器最终会把它们合并成一棵统一的过滤条件树。所以下面两条 SQL 的返回结果完全一样：

SELECT o.id, u.user_name FROM orders o INNER JOIN users u ON o.user_id = u.id AND u.status = 1; SELECT o.id, u.user_name FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.status = 1;

既然结果一样，为什么还要区分？因为可读性，以及为将来改成外连接留后路。连接条件表达的是"两张表之间靠什么对齐"，过滤条件表达的是"我对结果有什么额外要求"，这两件事在业务含义上是分开的。把它们混在一起写，几个月后自己回来看都要愣一下。

更重要的是，LEFT JOIN场景下这两者语义完全不同——这一点我在第 3 节会专门展开，因为它是外连接里最容易翻车的地方。

还有一个细节值得说：ON条件里的顺序不影响结果，但会影响你读 SQL 的思路。我个人的习惯是把"主键 = 外键"的等值条件放在最前面，范围条件、状态条件放后面，这样一眼能看出连接的主干是什么。

2.2 MySQL 实际用哪几种算法来算内连接

知道语法之后，真正决定快慢的是算法。MySQL 常见的有三种，理解它们各自的适用条件，能帮你在写 SQL 的时候有意识地"递刀子"给优化器。

**嵌套循环连接（Nested Loop Join）**是最基础的形态。它从驱动表取一行，去被驱动表的索引上查匹配行。如果被驱动表的连接列有索引，每次查找是 O(log n) 级别，整体复杂度大致是 O(m × log n)，这通常是可以接受的。这也是为什么"连接列必须建索引"几乎是所有 MySQL 优化建议里的第一条。

**块嵌套循环连接（Block Nested Loop Join，BNL）**是被驱动表连接列没有索引时的退化方案。它会把驱动表的一批行先读进 join buffer，然后扫描一遍被驱动表，逐行比对。它的优势是减少了被驱动表的扫描次数，但依然是一次全表扫描的复杂度。你会在EXPLAIN的Extra列看到Using join buffer (Block Nested Loop)。

**哈希连接（Hash Join）**从 MySQL 8.0.18 开始引入，8.0.20 之后扩展到外连接。它的思路是给小表建哈希表，然后扫描大表逐行探测，复杂度接近 O(m + n)。对于两个大表且没有合适索引的场景，哈希连接比 BNL 快得多。同时，BNL 在 8.0.20 之后被标记为废弃，因为哈希连接几乎在所有场景下都更优。

这三种算法的选择不是你能直接指定的，但你可以通过索引设计来引导。判断方式很直接：

2.3 驱动表的顺序不是随意的，但也不建议人为干预

驱动表（外层循环的表）越小，整体循环次数越少，这是直觉。MySQL 的优化器会根据统计信息估算各表的过滤后行数，选出它认为最优的顺序。多数情况下它算得比人准，因为统计信息是实时的，而人的判断常常停留在"我以为这张表很小"的印象上。

但优化器偶尔会失手，典型场景是统计信息过期、数据分布严重倾斜、或者WHERE条件里的取值范围估计不准。这时候可以用STRAIGHT_JOIN强制指定连接顺序：

SELECT o.id, u.user_name FROM users u STRAIGHT_JOIN orders o ON o.user_id = u.id WHERE u.created_at >= '2024-01-01';

STRAIGHT_JOIN的作用是强制让FROM中先出现的表作为驱动表。它是一把双刃剑：数据量分布变化之后，原本有效的顺序可能变成拖累。我的建议是，只有在确认优化器选错、并且通过EXPLAIN验证过强制顺序确实更快时才使用，同时一定要在注释里写清楚为什么。

顺带说一句，很多教程里提到的"小表驱动大表"其实要加个限定：在索引有效的前提下，它成立；在无索引的全表扫描场景下，谁是驱动表影响没那么大，因为两边都是全扫。真正要优先解决的是索引问题，而不是纠结顺序。

3. LEFT JOIN 与 RIGHT JOIN：外连接的保底逻辑和三个高频翻车点

3.1 ON 条件被挪进 WHERE，左连接就退化成内连接

这是外连接里最经典、也最容易犯的错误。看两个写法：

-- 写法 A：条件在 ON 里 SELECT u.id, u.user_name, o.id AS order_id FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 1; -- 写法 B：条件在 WHERE 里 SELECT u.id, u.user_name, o.id AS order_id FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 1;

写法 A 的逻辑是：先把orders里status = 1的行筛出来，再去和users匹配，匹配不到的用户保留，订单字段为NULL。所以结果里会出现"有用户但没订单"的行。

写法 B 的逻辑是：先做完整左连接，保留所有用户，此时没订单的用户那一行的o.status是NULL，然后WHERE o.status = 1把这些NULL行全部过滤掉。最终结果与INNER JOIN完全一致。

LEFT JOIN的意义就在于"以左表为准，右表没有也保留"，而WHERE里对右表字段加非空判断，等于亲手把这个保底机制拆掉了。这个坑之所以高频，是因为写代码的人往往先写了连接，再想到"哦对了还要筛个状态"，顺手加在WHERE里。检查方法很机械：只要 SQL 里有LEFT JOIN，就逐条检查WHERE中是否出现了右表的列，出现就要停下来想一秒。如果确实想过滤，又不想丢掉用户，正确写法是WHERE o.status = 1 OR o.id IS NULL，但这样写通常意味着你的业务需求本身就该重新梳理。

这里插一句关于RIGHT JOIN的话。它在语义上和LEFT JOIN完全对称，只是把保留方换到右边。实际项目里几乎没人用RIGHT JOIN，因为多表串联时它会让表的阅读顺序和逻辑顺序相反，脑子要来回倒。我的做法是永远只用LEFT JOIN，需要保留右表时把两张表的位置换一下。

3.2 一对多关系会让主表的行数被"放大"，这不是 Bug

LEFT JOIN还有一个被误解很深的行为：如果右表对左表的某一行有多个匹配，左表的那一行会被重复输出多次。这跟笛卡尔积是同源的——只是被连接条件限制在了局部。

举个例子：users表里一个用户有 3 个订单，那么users LEFT JOIN orders的结果里，这个用户就会出现 3 行。如果你想统计用户数，用COUNT(*)得到的是 3 而不是 1。正确写法是COUNT(DISTINCT u.id)，或者干脆拆成两条 SQL 分别统计。

这个特性在实务中经常被利用，比如查"每个订单及其明细"。但一旦和聚合函数组合，就很容易算错。我在做数据核对时有一个固定动作，值得分享：在写任何带JOIN的聚合查询之前，先单独跑一次SELECT 主表主键, COUNT(*) FROM 主表 LEFT JOIN 从表 GROUP BY 主表主键 HAVING COUNT(*) > 1 LIMIT 10;。如果返回了行，说明从表对主表是一对多关系，那么你在外层用SUM、AVG这些函数时就必须格外小心，因为数值会被重复累加。

一个真实的例子：用SUM(o.amount)统计用户总消费，如果orders又LEFT JOIN了order_items，且一个订单有多个明细，那么o.amount会被明细数量重复累加，统计出来的金额虚高好几倍。修复方式有两种——要么先把order_items聚合到订单粒度再连接，要么改用子查询在SELECT里单独算。我一般选前者，因为可读性和执行效率都更好。

3.3 多表左连接的链式传递，以及它的性能代价

当LEFT JOIN连续出现三次以上时，会出现两个现象：一是连接关系变成一条链，前一步的结果决定后一步能否匹配到数据；二是执行计划里的中间结果集急剧膨胀，如果每一步都有一对多关系，行数会呈乘积增长。

看一个典型的三表左连接：

SELECT u.id, u.user_name, o.id AS order_id, p.pay_no FROM users u LEFT JOIN orders o ON o.user_id = u.id LEFT JOIN payments p ON p.order_id = o.id;

如果用户 A 有 2 个订单，每个订单有 3 条支付记录，那么用户 A 在结果里会出现 6 行。链条越长，膨胀越厉害。这是"结构性放大"，不是数据错误，但你的业务代码如果不加处理直接渲染，页面上就会出现重复数据。

应对思路有三种，我按推荐程度排一下：

• 拆解：每个业务实体单独查一次，在应用层组装。适合关联层级深、聚合多的报表场景。
• 先聚合再连接：把一对多的从表用子查询或派生表压缩到一对一的粒度，再进行连接。这是最通用的方案。
• 用窗口函数标注序号：MySQL 8.0 支持ROW_NUMBER()，可以在连接后只取每个订单的第一条支付记录。适合"取最新一条"这类需求。

说到性能，还有一个容易被忽略的点：LEFT JOIN之后的ORDER BY如果排序字段来自被连接的表，往往无法利用索引，会触发Using filesort。在数据量大的时候，这个排序本身可能比连接还慢。我遇到过的处理方式是先把数据筛到足够小的范围，再排序，或者干脆把排序交给上层应用。

4. CROSS JOIN 与自连接：第四种连接的正当用途

4.1 CROSS JOIN 不一定是错误，它可以用来"造数据"

很多人把CROSS JOIN等同于事故，其实它有一个非常正当的用途：构造完整的组合骨架。

设想一个统计需求：查最近 7 天每天的订单数，包括订单数为 0 的日期。如果直接对orders表做GROUP BY DATE(created_at)，没有订单的日期根本不会出现在结果里，图表上就会断档。解决办法是先造一个 7 天的日期序列，再LEFT JOIN订单表：

WITH RECURSIVE dates AS ( SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM dates WHERE d < CURDATE() ) SELECT d.d, COUNT(o.id) AS cnt FROM dates d LEFT JOIN orders o ON DATE(o.created_at) = d.d GROUP BY d.d;

类似地，当需要"所有门店 × 所有商品"的组合矩阵时，CROSS JOIN是标准做法。它的关键区别在于：这是有意为之的全组合，而且你清楚它会产出多少行。

另一个常见场景是处理排列组合问题，比如从若干选项中生成所有两两配对。只要参与组合的集合很小（几十行以内），CROSS JOIN完全可控。

判断"是正当用途还是事故"的标准很简单：能不能说出结果集的大致行数。说得出来，是设计；说不出来，是事故。

4.2 自连接：同一张表的左右互搏

自连接虽然不是语法上的独立类型，但在业务里用得极多，而且很多人第一次遇到时不知道怎么下笔。它本质上就是给同一张表起两个别名，然后把这两份"副本"连起来。

最典型的场景是层级结构，比如员工和上级：

SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

注意这里用的是LEFT JOIN而不是INNER JOIN，因为最高层的人没有上级，用内连接会被过滤掉。这是自连接里最容易出错的第一个点。

第二个典型场景是"同一张表内的比较"，比如找出每个商品比它同类别平均价格还贵的商品。这类需求如果不用窗口函数，就得靠自连接把"当前行"和"同类别的所有行"对上：

SELECT a.id, a.name, a.price FROM products a JOIN products b ON a.category_id = b.category_id GROUP BY a.id, a.name, a.price HAVING a.price > AVG(b.price);

这里有个必须注意的细节：GROUP BY里要把a的所有非聚合列都列出来，否则在ONLY_FULL_GROUP_BY模式下会直接报错。很多人第一次遇到这个报错会以为是语法错了，其实只是 SQL 模式更严格了。

自连接的性能开销需要留意：它意味着同一张表被访问两次，如果数据量大、又没有合适索引，代价是双倍的。在 MySQL 8.0 上，很多原本需要自连接的场景可以用窗口函数替代，可读性和性能都更好。但如果是老版本或者有兼容性要求，自连接依然是必备技能。

4.3 MySQL 没有 FULL OUTER JOIN，只能自己拼

这是个经常被问到的问题：为什么 MySQL 没有FULL OUTER JOIN？标准 SQL 有这个语法，用来保留左右两边所有不匹配的行。MySQL 一直没实现，需要自己用LEFT JOIN和RIGHT JOIN的UNION拼出来：

SELECT u.id, u.user_name, o.id AS order_id FROM users u LEFT JOIN orders o ON o.user_id = u.id UNION SELECT u.id, u.user_name, o.id AS order_id FROM users u RIGHT JOIN orders o ON o.user_id = u.id;

这里必须用UNION而不是UNION ALL，因为两边都会返回匹配上的行，不去重就会出现重复。代价是 MySQL 需要对结果集做一次去重排序，如果结果集很大，这一步会很慢。

在实际业务里，需要用到完整外连接的场合其实不多。大多数时候需求是"找出左表里有、但右表里没有的数据"，这本质上是一个反连接（Anti Join），用LEFT JOIN ... WHERE 右表主键 IS NULL或者NOT EXISTS就能解决，比完整外连接轻量得多：

-- 找出从未下单的用户 SELECT u.id, u.user_name FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;

这个写法的性能关键点在于：o.user_id上必须有索引，而且这个索引能让 MySQL 在发现第一个匹配行时就停止扫描。如果索引结构不合适，MySQL 可能需要扫完整个orders表才能确定某用户是否有订单。

5. 把四种连接塞进一个真实场景：从需求到 SQL 的完整推演

5.1 一套够用的示例表结构与造数脚本

光讲语法容易飘，下面用一套贴近"课程设计 / 小型电商后台"的表结构把前面所有内容串一遍。四张表，分别是用户、订单、订单明细、商品：

CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(64) NOT NULL, status TINYINT NOT NULL DEFAULT 1 COMMENT '1正常 0禁用', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_users_status_created (status, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 1 COMMENT '1待支付 2已支付 3已取消', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_orders_user (user_id, created_at), KEY idx_orders_status_created (status, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, qty INT NOT NULL DEFAULT 1, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, KEY idx_items_order (order_id), KEY idx_items_product (product_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(128) NOT NULL, category_id BIGINT UNSIGNED NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, KEY idx_products_category (category_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

注意几个设计细节，它们直接决定了后面连接查询的性能和正确性：

第一，所有连接列的类型、字符集、排序规则完全统一。orders.user_id和users.id都是BIGINT UNSIGNED，order_items.order_id和orders.id也是。这样连接时不需要任何隐式转换，索引能被完整利用。前面提到的"字符集不一致导致索引失效"，在这套结构里被提前规避了。

第二，索引里带了时间列。idx_orders_user (user_id, created_at)这个复合索引，既支持按用户过滤，又支持按用户分组后的时间排序。复合索引的列顺序遵循"等值条件在前、范围与排序在后"的原则，这是最实用的索引设计经验之一。

第三，状态列也进了索引。idx_orders_status_created (status, created_at)是为了支撑"查最近 7 天已支付订单"这类高频查询。如果不加这个索引，status = 2这种低基数条件单独建索引意义不大，但和created_at组合起来就很有价值。

造数方面，我一般用存储过程或者简单的循环脚本灌几千条数据，重点是让数据分布"不平均"——比如 20% 的用户贡献 80% 的订单。平均分布的数据会让优化器的估算看起来很美，掩盖掉很多真实问题。这一点在做数据库课程设计时特别重要，很多人的测试数据太规整，跑什么都快，一上线就原形毕露。

5.2 五个典型需求，从"人话"推演到 SQL

下面这五个需求都是我在实际项目或者课程设计评审里反复见到的，逐个拆解。

需求一：查所有正常用户及其订单数量，没有订单的显示 0。

这里必须用LEFT JOIN，而且计数要用COUNT(o.id)而不是COUNT(*)。原因前面讲过：COUNT(*)会计入左表中那些右表为NULL的行，结果永远是 1 而不是 0。COUNT(列名)会忽略NULL值，这才是我们想要的语义。

SELECT u.id, u.user_name, COUNT(o.id) AS order_cnt FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.status = 1 GROUP BY u.id, u.user_name;

注意WHERE u.status = 1作用在左表上，不会破坏左连接的保底逻辑，这是安全的。如果把条件换成o.status IN (2,3)，那就是第 3.1 节讲的错误写法了。

需求二：统计每个已支付订单的商品总金额。

SELECT o.id, SUM(oi.qty * oi.price) AS item_amount, o.amount AS order_amount FROM orders o LEFT JOIN order_items oi ON oi.order_id = o.id WHERE o.status = 2 GROUP BY o.id, o.amount;

这里LEFT JOIN的意义是：如果一个订单没有任何明细（数据异常），它依然会被统计出来，item_amount为NULL。如果用INNER JOIN，这类异常订单会被静默丢弃。在统计类查询里，我倾向于用LEFT JOIN并保留异常数据，因为把问题藏起来比报错更危险。你可以再加一个HAVING item_amount IS NULL的口径去专门捞异常。

需求三：找出所有从未下单的用户。

前面讲过的反连接，LEFT JOIN ... WHERE 右表主键 IS NULL或者NOT EXISTS。两种写法在 MySQL 8.0 上的执行计划通常会被优化成同一类反连接，性能差异不大。但在老版本上，LEFT JOIN ... IS NULL往往表现更稳定。

需求四：查每个分类下最贵的商品，以及它比同类平均价高出多少。

这需要自连接或者窗口函数。自连接的写法在 4.2 节给过，这里给窗口函数版本，更简洁：

SELECT id, product_name, category_id, price, avg_price FROM ( SELECT id, product_name, category_id, price, AVG(price) OVER (PARTITION BY category_id) AS avg_price FROM products ) t WHERE price > avg_price;

需求五：给每个订单打上"是否含某类商品"的标记。

这是EXISTS的典型场景，比LEFT JOIN + DISTINCT清晰得多：

SELECT o.id, EXISTS ( SELECT 1 FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.order_id = o.id AND p.category_id = 10 ) AS has_target_category FROM orders o;

EXISTS在找到第一条匹配后就会短路返回，不需要把从表全部展开，性能上通常优于连接后去重。这个写法在 MySQL 8.0 上还有额外的优化空间，因为优化器会做半连接（Semi-Join）转换。

5.3 索引该建在哪几个列上，以及为什么是这个顺序

把这套结构里的索引决策重新捋一遍，因为这是最实用、也最容易出错的部分。

三条通用原则值得记住：

第一，连接列必须建索引，而且两边类型要一致。这几乎是所有连接性能问题的第一优化点。EXPLAIN里如果type是ALL而rows很大，先检查被驱动表的连接列有没有索引。

第二，复合索引的列顺序遵循"等值 — 范围 — 排序"。等值条件放最左，范围条件次之，ORDER BY的列放最后。这个顺序能让索引同时覆盖过滤和排序，一次扫描解决两个问题。

第三，不要为了"可能用到"而堆索引。每个索引在写入时都要维护，INSERT、UPDATE都会变慢。我在实际项目里见过一张表建了 11 个索引，写入性能惨不忍睹。判断方法是用SHOW INDEX FROM 表名看一下，如果有索引的前缀列是另一个索引的前缀（比如既有(a)又有(a,b)），那单独的那个通常可以删掉。

6. 执行计划与排错：连接写对了，为什么还是慢

6.1 读执行计划时，我只看这四列

EXPLAIN的输出有十几列，但日常排查我最关注的就四列，按优先级排。

type列反映访问方式，从好到差大致是：system>const>eq_ref>ref>range>index>ALL。连接场景下，被驱动表最理想的是eq_ref，表示用唯一索引精确定位一行。如果出现ALL，说明在扫描全表，这是最直接的性能红灯。

rows列是优化器估算的扫描行数。它的绝对值不一定准，但多张表之间做对比很有参考价值。如果驱动表的rows是 10 万，被驱动表也是 10 万，那这个连接基本没戏，必须重新设计索引或者改写查询。

key和possible_keys列告诉你实际用了哪个索引、有哪些可用。如果possible_keys有值但key是NULL，说明有索引但没被选中，通常是因为条件选择性差，或者统计信息过期。这时候可以试着跑一次ANALYZE TABLE，但更根本的解决方式是调整索引的列顺序。

Extra列是最有信息量的一列。Using index表示覆盖索引，性能最好；Using where表示在存储引擎返回结果后又做了过滤；Using temporary和Using filesort是两个需要警惕的信号，通常出现在带GROUP BY或ORDER BY的查询里。

还有一个进阶技巧：MySQL 8.0 支持EXPLAIN ANALYZE，它不只给出估算值，而是真正执行一遍并输出每个节点的实际耗时和实际行数。对比估算行数和实际行数，能快速发现优化器的判断偏差。我处理过的一个案例里，优化器估算某个连接只需要扫 200 行，实际扫了 40 万行，原因是status列的统计直方图没更新。跑一次ANALYZE TABLE之后，执行计划立刻换成了哈希连接，耗时从 12 秒降到 0.3 秒。

6.2 结果异常与报错的对照排查表

把连接相关的常见问题整理成一张表，出现症状时可以直接对照：

关于"查询突然变慢却没人改代码"这一条，我想多说一句。这是生产环境里非常典型的现象，通常有两个原因：一是数据量增长跨过了某个临界点，优化器换了执行计划；二是统计信息过期，导致优化器的估算严重偏离实际。两者的排查方式都是先看EXPLAIN，再对比估算行数和实际行数。养成定期对核心表跑ANALYZE TABLE的习惯，能省掉很多深夜排查的工夫。

6.3 我在实际项目里踩过的几个坑

最后分享几个具体的、文档里通常不会写的问题。

第一个坑：UPDATE配合子查询时的连接语义。我见过这样的写法，想把订单金额同步到用户表：

UPDATE users u SET u.total_amount = (SELECT SUM(amount) FROM orders WHERE user_id = u.id);

这在 MySQL 里是可行的，但性能很差——每一行users都会触发一次子查询。更好的写法是先用连接把所有用户的汇总算出来，再更新：

UPDATE users u JOIN ( SELECT user_id, SUM(amount) AS total FROM orders WHERE status = 2 GROUP BY user_id ) t ON t.user_id = u.id SET u.total_amount = t.total;

这个写法只扫一遍orders，然后用派生表去连接更新。要注意的是派生表在 MySQL 5.7 之前会被物化成临时表，8.0 之后有派生条件下推的优化，性能更好。另外，更新大批量数据时要控制事务大小，避免长事务锁表。

第二个坑：分页查询里的连接。LIMIT配合多表连接时，MySQL 通常需要先完成连接和排序，再做截取。如果连接后的结果集很大，前面几页可能很快，翻到后面越来越慢。我的处理方式是把LIMIT尽量下推到"驱动表"这一侧，先用主表筛出这一页的主键，再拿这批主键去连接其他表：

SELECT o.id, o.amount, u.user_name FROM (SELECT id, user_id, amount FROM orders WHERE status = 2 ORDER BY id LIMIT 20 OFFSET 10000) o JOIN users u ON u.id = o.user_id;

这样连接的数据量被限制在 20 行，代价是子查询里仍然要扫描大量行来定位偏移量。彻底解决需要基于游标的分页（记住上一页最后一个id），这个我们以后有机会再展开。

第三个坑：把LEFT JOIN当成"性能优化手段"。有些同学觉得LEFT JOIN保留了所有左表行，就顺手用来替代INNER JOIN，理由是"防止数据丢失"。这是个误解——两者返回的数据语义完全不同，选择哪一个应该由业务需求决定，而不是性能。而且在很多场景下，INNER JOIN因为过滤后行数更少，反而更快。

第四个坑：忽略NULL值在连接中的行为。NULL在 SQL 里不等于任何值，包括它自己。所以ON a.col = b.col永远匹配不到两边都是NULL的行。如果需要把NULL也当作一个有效值来匹配（比如按手机号关联，双方都为空时视为同一组），就得用<=>这个 NULL 安全的等号运算符。我在处理数据清洗任务时被这个坑坑过一次，两个字段都是NULL的记录怎么都匹配不上，排查了半天才发现问题出在这里。

第五个坑：在循环里执行连接查询。这是最典型的 N+1 问题——先查出 100 个用户，然后循环 100 次去查每个用户的订单。100 次往返加上 100 次连接开销，性能可想而知。正确做法是一次性把用户的订单全部查出来，在内存里做分组映射。这个问题在应用层比在数据库层更常见，但只要多表连接写熟了，自然就会想到合并查询。

说到底，四大连接和笛卡尔乘积这件事，语法层面半小时就能学完，真正花时间的是建立起"数据形状"的直觉——看到一条 SQL 就能大致判断出结果集有多少行、哪些表是一对多、哪个条件会破坏外连接的保底逻辑。我的建议是拿自己的项目库随便挑几张有关联的表，把四种连接各写一遍，然后用EXPLAIN看一遍执行计划，再把索引去掉看一遍对比。这种"手动制造差异"的练习，比看十篇文章都管用。