mysql 自然连接、内连接、外连接这三个词摆在一起几乎每本 SQL 入门书都会用同一张示意图一笔带过。可真到了线上翻车的往往就是它们之间那些看着差不多、实际差很远的细节。我印象比较深的一次是帮人排查一张报表员工表和部门表关联预期几十行跑出来只剩三行。SQL 拿过来一看NATURAL JOIN三个字赫然在目而这两张表刚好都有一列create_time自然连接把所有同名列都当成了连接条件结果自然就空了。这类问题不会报错只会安静地把数据变少或者变多排查起来极其耗时。这篇就围绕 MySQL 里这三类连接的语义差异、执行路径和实际选型展开。我会先把三种连接放在同一份数据上跑出结果做对照让人一眼看清区别再分别拆内连接、外连接、自然连接各自的坑点最后落到优化器怎么看这些语句以及实际项目里该怎么选。适合已经能写基本 SQL、但对连接语义边界还没完全吃透的开发者也适合做数据核对、报表开发的同学拿来当检查清单用。1. 三种连接先用同一份数据跑出结果对照1.1 连接的本质先做笛卡尔积再按条件筛理解连接最省事的办法是先记住一句话任何连接的第一步都是笛卡尔积ON 条件只是在这个中间结果上做过滤。假设 A 表有 m 行B 表有 n 行那么两表连接产生的中间集合就是 m×n 行然后再根据连接条件挑出符合条件的行。这个心智模型一旦建立内连接、外连接、自然连接的区别就全部变成了过滤规则不同和保留哪些行的问题。外连接之所以特殊是因为它在笛卡尔积过滤之后还多了一步把没匹配上的驱动表行补回来另一侧用 NULL 填充。自然连接之所以危险是因为它的过滤条件不是你写的而是数据库根据列名自动推断的。内连接则是三者里语义最直白的只保留两边都能匹配上的行匹配不上的直接丢弃。为了后面能对照着看先建两张极简的表。员工表四个人其中一个人没有分配部门部门表三个部门其中一个部门没有员工。这种两边都有孤儿数据的结构最能暴露三种连接的差异。CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(32), dept_id INT ); CREATE TABLE departments ( id INT PRIMARY KEY, dept_name VARCHAR(32) ); INSERT INTO employees VALUES (1, 张三, 10), (2, 李四, 20), (3, 王五, NULL), (4, 赵六, 10); INSERT INTO departments VALUES (10, 研发部), (20, 市场部), (30, 行政部);注意employees表和departments表都有一个叫id的列。这不是我随手起的名字而是真实项目里极常见的命名习惯——每张表都用id做主键。这个命名习惯本身没问题但它和自然连接凑在一起就是事故的起点。1.2 同一份数据在三种连接下的输出差异把三种写法放在一起跑结果差异一目了然。下面的对照表按输出行数和关键特征整理方便直接比对。写法输出行数张三(10)王五(NULL)行政部(30)INNER JOIN ... ON e.dept_id d.id3保留丢弃丢弃LEFT JOIN ... ON e.dept_id d.id4保留保留右表全 NULL丢弃RIGHT JOIN ... ON e.dept_id d.id4保留丢弃保留左表全 NULLNATURAL JOIN0丢弃丢弃丢弃前三行的行为是符合直觉的内连接只保留能配对的左连接保住左表全部右连接保住右表全部。第四行的结果才是真正值得注意的地方——NATURAL JOIN输出 0 行。原因在于它自动把两张表所有同名列都拿来当连接条件也就是employees.id departments.id AND employees.dept_id departments.id而员工 id 是 1 到 4部门 id 是 10、20、30两个条件同时成立的行根本不存在。这个例子里业务语义上正确的连接条件应该是e.dept_id d.id但自然连接自动推导出的是e.id d.id条件本身就用错了。它不会给你任何警告也不会在语法检查阶段报错只会安静地返回一个错误的结果集。1.3 自然连接在工程实践里基本被禁用的原因把自然连接的问题归纳一下核心是三条。第一连接条件隐式化条件不在 SQL 文本里读代码的人必须去查两张表的完整结构才能知道它连了什么代码审查时几乎无法被发现。第二对表结构变更零防御任何人往任意一张表加一个同名同类型的列连接条件就悄悄变了比如两张表都加tenant_id自然连接立刻变成多条件连接结果可能从数据偏多变成数据偏少。第三没有同名列时退化成笛卡尔积如果两张表恰好一列同名都没有NATURAL JOIN的行为等价于CROSS JOIN行数直接爆炸。我在实际项目里的做法很明确业务代码里一律不写NATURAL JOIN只在一次性探索数据、临时写 ad-hoc 查询时偶尔用一下而且用完立刻丢掉。团队里如果有新人写了这个代码评审阶段就应该拦下来。这条规则听起来有点绝对但比起事后排查数据对不上的成本绝对一点更划算。2. 内连接看起来最简单写错的方式却最多2.1 ON 和 WHERE 在内连接里到底等不等价网上流传很广的一个说法是内连接里 ON 和 WHERE 完全等价随便写。这句话在结果集层面基本成立因为优化器会把内连接的 ON 条件合并进 WHERE 一起处理执行计划通常一致。但结果一样和应该这么写是两码事我建议严格区分连接条件放 ON行过滤条件放 WHERE。原因有两个。一是可读性。一条涉及四五张表的查询如果所有条件都堆在 WHERE 里读的人根本分不清哪个条件是表 A 和表 B 的连接键、哪个是业务过滤。等你哪天要把内连接改成左连接就得从头捋一遍每个条件该往哪挪。二是可维护性。内连接改成外连接时原本堆在 WHERE 里的连接条件如果不挪到 ON 里语义立刻出错——这个问题在外连接那一节会详细讲。有一种情况 ON 和 WHERE 确实不等价ON 条件里包含子查询或者非确定性函数时优化器的处理路径可能不同。不过这种写法本身就少见也不推荐正常业务查询尽量别在 ON 里塞子查询。2.2 USING 关键字列合并带来的输出变化USING是介于ON和NATURAL JOIN之间的一个折中语法。它要求你显式写出列名但会像自然连接一样把同名输出列合并成一列。-- 用 ON 时两表的 id 列都会输出SELECT * 有 5 列 SELECT * FROM employees e JOIN departments d ON e.dept_id d.id; -- 用 USING 时前提是两表有同名列同名列只输出一次 SELECT * FROM employees e JOIN departments d USING (dept_id);第二句要求departments表也有dept_id列这在实际表结构里不一定成立所以USING的适用范围比想象中窄。它的坑在于如果程序里用SELECT *然后按列位置取值比如某些 ORM 的简易映射或者老式的 ResultSet 按索引读USING会让输出列数比ON少字段映射直接错位。我的建议是生产代码里永远不要SELECT *做表连接把需要的列一个个写出来既避免这个问题也减少不必要的回表和数据传输。2.3 隐式连接与显式 JOIN 的执行差异老派写法用逗号加 WHERESELECT e.name, d.dept_name FROM employees e, departments d WHERE e.dept_id d.id;这种写法在 MySQL 里会被优化器改写成显式 JOIN执行计划通常完全一致性能上没有区别。它的真正风险是漏写 WHERE 条件。一旦条件漏了语句照样能跑返回的是完整的笛卡尔积。表小的时候察觉不到表一大就是灾难。显式JOIN ... ON语法强制你把连接条件写在结构里虽然理论上也能写成JOIN不带ON等价于CROSS JOIN但至少视觉上缺失得很明显代码评审容易发现。顺带说一句MySQL 里JOIN、INNER JOIN、CROSS JOIN三者语法上是等价的CROSS JOIN也可以带ON条件。这跟某些数据库的实现不一样跨库迁移的时候要注意。2.4 多表内连接时书写顺序不等于执行顺序三张以上的表做内连接SQL 里谁写在前面不代表数据库就先读谁。MySQL 优化器会基于统计信息和成本估算决定驱动表可能把你写在最后面的表当成驱动表。想确认实际执行顺序看EXPLAIN的id和table列id相同的行从上到下大致反映连接顺序但id不同就要结合select_type判断。如果确实需要强制某张表做驱动表比如小表驱动大表能显著减少扫描量可以用STRAIGHT_JOIN强制按书写顺序连接或者用优化器提示SELECT /* JOIN_ORDER(d, e) */ e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id d.id;不过这类 hint 属于最后手段先确认统计信息是不是过期、索引是不是缺失比上来就写 hint 更靠谱。我见过不少项目堆了一堆 hint实际上是ANALYZE TABLE很久没跑、基数估算严重失真导致的。3. 外连接左右之分背后是谁是主表的问题3.1 LEFT JOIN 的语义拆解与 NULL 填充位置左连接的规则可以概括成一句话左表每一行都必须在结果里出现至少一次右表匹配不上就填 NULL。这个每一行是关键。很多人以为左连接是左表所有列都保留、右表尽量匹配其实更准确的理解是结果集的行数以左表为基准右表的匹配情况只决定每行右半部分有没有值。右连接就是把左右对调逻辑完全对称。工程上我基本只用LEFT JOINRIGHT JOIN只在一个场景下用——多表连接时前一张表已经被左连接固定住后面要接的表需要以它为主这时候用右连接可以让 SQL 的书写顺序和语义顺序一致。但这种场景不多绝大多数情况下用左连接加调整书写顺序就能表达清楚可读性更好。NULL填充的具体位置也要注意外连接产生的是真正的 NULL不是空字符串也不是 0。这在后续做COUNT、SUM、字符串拼接时都会带来影响后面专门说。3.2 ON 和 WHERE 在外连接里的分工写反了结果全错这是外连接最容易出错的地方也是我见过最多的线上问题。规则很简单外连接的过滤条件写在 WHERE 里会把外连接退化成内连接。-- 错误写法想要所有员工 他们所在的研发部结果只剩研发部员工 SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id WHERE d.dept_name 研发部; -- 正确写法过滤条件放进 ON SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id AND d.dept_name 研发部;第一句为什么错因为王五没有部门右表的dept_name是 NULLNULL 研发部的结果是 UNKNOWN在 WHERE 里被当作不成立整行被过滤掉。于是所有员工变成了研发部员工左连接的语义被悄悄吃掉。第二句把条件放进 ON过滤只作用于右表能不能匹配上不匹配的照样补 NULL 保留左表行。这才是左连接该有的行为。这里要澄清一点并不是说 WHERE 里永远不能出现右表字段。如果业务需求本身就是只要那些有部门且部门是研发部的员工那写 WHERE 也完全正确只是这时候用INNER JOIN更直白。判断标准是你先想清楚这一行该不该保留再决定条件放哪而不是先写 SQL 再倒推语义。3.3 MySQL 没有 FULL OUTER JOIN怎么模拟MySQL 至今不支持FULL OUTER JOIN语法。要表达两边都保留的语义标准做法是左连接和右连接取并集SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id UNION SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id d.id;注意这里用UNION而不是UNION ALL。因为两边都能匹配上的行会在两个查询里各出现一次用UNION会自动去重。但如果结果集里本来就有完全重复的业务行比如两个同名员工同一个部门去重会把它们合并这时候就得改用反连接的方式拼先取左连接的完整结果再UNION ALL上右表有、左表没有的那部分。SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id UNION ALL SELECT NULL, d.dept_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id d.id);这种写法啰嗦但能精确控制去重行为。实际做数据核对、找两边对不上的记录时反连接LEFT JOIN ... WHERE 右表主键 IS NULL或者NOT EXISTS比模拟全外连接更常用、也更快。3.4 外连接配 COUNT 时的经典数字陷阱外连接加聚合函数是统计类需求的标配组合也是数字出错的常发地带。核心区别只有一条COUNT(*)统计行数COUNT(列名)只统计该列非 NULL 的值。-- 需求每个部门有多少员工没有员工的部门显示 0 SELECT d.dept_name, COUNT(*) AS emp_cnt FROM departments d LEFT JOIN employees e ON e.dept_id d.id GROUP BY d.dept_name;上面这句对行政部会返回 1 而不是 0因为左连接给行政部补了一行右表全 NULL 的记录COUNT(*)把这行算进去了。正确写法是用右表的主键或者任意非空列SELECT d.dept_name, COUNT(e.id) AS emp_cnt FROM departments d LEFT JOIN employees e ON e.dept_id d.id GROUP BY d.dept_name;COUNT(e.id)遇到 NULL 会跳过行政部就是 0。这个坑我在报表项目里见过不止一次而且往往要等业务方质疑数据才被发现。类似的还有SUM外连接产生的 NULL 行参与SUM时结果不变NULL 被忽略但如果用SUM(IFNULL(列, 0))这类写法逻辑就要重新捋一遍别想当然。4. 自然连接语法糖省下的键盘都变成了排查时间4.1 NATURAL JOIN 到底自动做了什么NATURAL JOIN的规则可以精确描述成三步第一扫描两张表的列定义找出所有名字相同且类型可比较的列第二用这些同名列的等值比较作为连接条件第三输出结果时这些同名列只保留一份。换句话说A NATURAL JOIN B等价于A JOIN B USING (所有同名列)。理解这个等价关系就能推导出它的所有风险。条件不是你写的是数据库猜的。数据库猜的依据只有列名完全不管业务语义。前面那个员工表和部门表都有id的例子从列名角度看连接条件毫无破绽从业务角度看完全是错的。还有一种更隐蔽的情况两表完全没有同名列。这时候NATURAL JOIN的行为退化成笛卡尔积不报错不警告直接返回 m×n 行。表大一点查询直接超时或者把内存打满。4.2 加列引发的雪崩同名列污染我见过最典型的一次事故是这样的某业务系统的两张表一开始只有id一个同名列NATURAL JOIN一直在正常跑。后来做数据治理两张表都加了tenant_id和create_time用于多租户隔离和审计。上线之后没人注意连接语句结果连接条件从 1 个变成 3 个create_time精确到秒两张表分别在不同时刻写入几乎不可能相等结果集直接变成空。这类问题的排查成本极高因为它满足三个特征不报错、不慢、结果结构完全正常只是数据不对。等业务方发现数据缺失再去定位中间可能已经过去好几天。而且因为 SQL 语法本身没毛病静态检查工具也发现不了。4.3 NATURAL LEFT JOIN 这类变体的风险自然连接还能和外连接组合写成NATURAL LEFT JOIN语义是以左表为主用所有同名列做连接条件。这种写法在实际代码里出现频率很低但风险比NATURAL JOIN更高——因为左连接本身就有保留所有左表行的语义一旦连接条件被同名列污染结果不是变空而是变成所有左表行都保留、右表全部补 NULL表面上行数还对得上实际所有关联字段都是空的更难发现。我的态度很直接这类写法在生产代码里应该被完全禁掉。如果非要用语法糖减少重复用USING(列名)更安全至少列名是显式写出来的加新列不会自动改变连接条件。4.4 什么场景下可以考虑使用自然连接说了这么多缺点也得给个相对中立的使用边界。以下几种情况可以考虑临时查询探索数据表结构完全清楚且确认两表只有一个语义正确的同名列教学演示用来说明连接概念两张表结构高度对称的宽表做快速比对。除此之外尤其是任何要进代码仓库、要被别人阅读、要长期维护的 SQL都不建议用。判断标准其实很简单如果这段 SQL 三个月后由另一个人来看他能不能只看 SQL 本身就知道连接条件是什么自然连接永远通不过这个测试。5. 优化器怎么执行连接理解它才知道该改什么5.1 Nested Loop Join 的基本形态MySQL 处理连接的主流算法是嵌套循环。外层循环遍历驱动表的每一行内层循环到被驱动表里找匹配的行。如果被驱动表的连接列上有索引内层循环每次是索引查找复杂度接近 O(驱动表行数 × log 被驱动表行数)如果没有索引内层循环每次都是全表扫描复杂度退化成笛卡尔积级别。这解释了一条最实用的优化原则驱动表选小表被驱动表的连接列必须有索引。反过来如果被驱动表的连接列没索引哪怕驱动表只有几十行被驱动表几百万行每条驱动记录都要扫一遍全表查询时间直接不可接受。外连接在这条规则上有个额外约束驱动表是固定的。左连接的驱动表必须是左表右连接必须是右表这个顺序不能由优化器随意调整因为调整会改变语义。所以左连接写反了主表优化器也救不了你。这也是为什么外连接比内连接更容易出现性能问题——你实际上是在用 SQL 的书写顺序替优化器做决策。5.2 MySQL 8.0 的 Hash Join 与 BNL 的替换MySQL 8.0.18 引入了 Hash Join用于等值连接且被驱动表没有可用索引的场景。原理是把较小的那部分数据在内存里建成哈希表然后遍历另一部分数据做哈希探测复杂度接近线性。8.0.20 之后原来的 Block Nested-Loop 算法被 Hash Join 取代。这对开发者意味着什么以前遇到被驱动表没索引的连接只能眼睁睁看着 BNL 扫来扫去现在优化器可能自动切成 Hash Join性能好很多。但 Hash Join 有前提必须是等值连接且内存够用。连接条件里写、、!或者LIKE %xx这类非等值条件还是走嵌套循环。连接数据量超过join_buffer_size会溢写到磁盘性能会明显下降。判断有没有走 Hash Join看EXPLAIN的Extra列出现Using join buffer (hash join)就是走了。如果看到Using join buffer (Block Nested Loop)说明版本较老或者条件不满足 Hash Join 的触发条件。5.3 EXPLAIN 里应该重点看哪几列MySQL 的EXPLAIN输出信息很多看连接性能主要盯这几列。列名关注点type访问类型ALL是全表扫描ref/eq_ref/range是走索引key实际使用的索引为 NULL 说明没走索引rows预估扫描行数驱动表这一行要尽量小filtered过滤后剩余行的百分比过低说明索引选择性差ExtraUsing join buffer说明没索引Using temporary/Using filesort说明有额外排序开销一个典型的问题是type显示ALL、rows很大、Extra是Using join buffer (hash join)。这时候虽然 Hash Join 能跑但本质上还是全表数据参与哈希构建数据量一大照样慢。正确的做法是给连接列补索引让type变成refrows降下来Extra里不再出现 join buffer。另一个容易忽略的点是filtered。假设驱动表rows是 1000filtered是 1%实际传给下一张表的只有 10 行如果filtered是 100%就是 1000 行全部传下去。驱动表的选择不只看行数还要看过滤后的实际行数。6. 实战选型三种连接到底该怎么选6.1 选型决策表把前面讨论的内容压缩成一张决策表实际写 SQL 时可以直接对照。业务需求推荐写法理由只要两边都匹配上的数据INNER JOIN ... ON语义最直白优化器自由度最高以一边为主另一边可有可无LEFT JOIN ... ON或右连接主表行全部保留NULL 填充另一侧找主表有、从表没有的孤儿数据LEFT JOIN ... WHERE 从表主键 IS NULL反连接比NOT IN在 NULL 场景下更安全两边都要保留LEFT JOIN UNION RIGHT JOINMySQL 无全外连接只能模拟临时探索数据NATURAL JOIN快但用完即弃不进代码库同名列显式且确定JOIN ... USING(列名)比自然连接安全比 ON 简洁表格之外还有一条经验能内连接就别外连接能单表就别连接。外连接的分析成本、索引要求、NULL 处理复杂度都比内连接高一个量级。很多需求看起来必须外连接其实拆成两条简单查询在应用层拼装更清晰也更好优化。6.2 几个真实踩过的坑第一个坑是外连接条件写进了 WHERE。某次做员工考勤报表需求是所有员工 本月请假天数SQL 写成了LEFT JOIN 请假表 ... WHERE 请假月份 当前月。结果没有请假的员工全部消失报表人数比 HR 系统少了一截。修复方式就是把月份条件挪进ON或者用IFNULL在 SELECT 里处理。这个错误之所以常见是因为 SQL 本身语法合法、查询还很快只有对数据才能发现异常。第二个坑是多表连接中的外连接被后续内连接截断。三张表 A LEFT JOIN B LEFT JOIN C一切正常后来为了过滤某个状态在末尾加了一句INNER JOIN D结果 A 中那些在 D 里匹配不上的行全被丢弃前两个左连接的语义全部失效。修复思路有两种把 D 也改成左连接或者把 D 的过滤下推到子查询里。选择哪种取决于业务语义——到底是要所有 A还是有 D 的 A。第三个坑是自然连接加列。前面已经详细说过某张表新增了同名列自然连接的条件悄悄变化数据从有变空或者从少变多。这类问题唯一的预防手段就是禁用自然连接没有别的办法。第四个坑是外连接配聚合函数。除了前面说的COUNT(*)问题还有SUM在空集上的行为。SUM(NULL)返回 NULL 而不是 0如果前端拿到 NULL 直接展示会出问题通常需要在 SQL 层用IFNULL(SUM(...), 0)兜底或者在应用层处理。6.3 一份可以直接套用的自检清单写完一条多表连接 SQL提交之前过一遍下面这几条能挡掉大部分低级错误。连接条件是否全部写在ON里业务过滤条件是否全部写在WHERE里两者有没有混放。外连接的主表是否符合业务语义我要的是左表全部还是右表全部有没有写反。WHERE里有没有引用外连接从表的列除了IS NULL这种反连接用法有的话确认是不是有意为之。聚合函数用的是COUNT(*)还是COUNT(列名)和要不要统计空行的需求是否一致。有没有使用NATURAL JOIN如果有能不能换成显式ON或USING。SELECT *有没有用在多表连接上能不能改成明确列出所需列。EXPLAIN跑一下被驱动表的连接列有没有索引type是不是ALLrows是否在可接受范围。多表连接里是否混用了内连接和外连接内连接的位置会不会把前面外连接保留的行截断。这套清单看起来琐碎但每一条都对应过真实的问题。我个人在改别人 SQL 的时候基本是照着这个顺序从头过一遍比直接跑EXPLAIN调优更高效——很多问题压根不是性能问题是语义写错了跑得越快错得越多。最后分享一个我自己养成的小习惯任何涉及外连接的查询我都会额外写一条只查左表或者只查右表的对照查询两边行数对一下。比如左连接之后的COUNT(*)必须等于左表的COUNT(*)不等就说明有问题。这个对照操作花不到一分钟但能挡掉绝大部分外连接写错的情况比事后被业务方追着问数据为什么不对要划算得多。
mysql 自然连接、内连接、外连接这三个词摆在一起几乎每本 SQL 入门书都会用同一张示意图一笔带过。可真到了线上翻车的往往就是它们之间那些看着差不多、实际差很远的细节。我印象比较深的一次是帮人排查一张报表员工表和部门表关联预期几十行跑出来只剩三行。SQL 拿过来一看NATURAL JOIN三个字赫然在目而这两张表刚好都有一列create_time自然连接把所有同名列都当成了连接条件结果自然就空了。这类问题不会报错只会安静地把数据变少或者变多排查起来极其耗时。这篇就围绕 MySQL 里这三类连接的语义差异、执行路径和实际选型展开。我会先把三种连接放在同一份数据上跑出结果做对照让人一眼看清区别再分别拆内连接、外连接、自然连接各自的坑点最后落到优化器怎么看这些语句以及实际项目里该怎么选。适合已经能写基本 SQL、但对连接语义边界还没完全吃透的开发者也适合做数据核对、报表开发的同学拿来当检查清单用。1. 三种连接先用同一份数据跑出结果对照1.1 连接的本质先做笛卡尔积再按条件筛理解连接最省事的办法是先记住一句话任何连接的第一步都是笛卡尔积ON 条件只是在这个中间结果上做过滤。假设 A 表有 m 行B 表有 n 行那么两表连接产生的中间集合就是 m×n 行然后再根据连接条件挑出符合条件的行。这个心智模型一旦建立内连接、外连接、自然连接的区别就全部变成了过滤规则不同和保留哪些行的问题。外连接之所以特殊是因为它在笛卡尔积过滤之后还多了一步把没匹配上的驱动表行补回来另一侧用 NULL 填充。自然连接之所以危险是因为它的过滤条件不是你写的而是数据库根据列名自动推断的。内连接则是三者里语义最直白的只保留两边都能匹配上的行匹配不上的直接丢弃。为了后面能对照着看先建两张极简的表。员工表四个人其中一个人没有分配部门部门表三个部门其中一个部门没有员工。这种两边都有孤儿数据的结构最能暴露三种连接的差异。CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(32), dept_id INT ); CREATE TABLE departments ( id INT PRIMARY KEY, dept_name VARCHAR(32) ); INSERT INTO employees VALUES (1, 张三, 10), (2, 李四, 20), (3, 王五, NULL), (4, 赵六, 10); INSERT INTO departments VALUES (10, 研发部), (20, 市场部), (30, 行政部);注意employees表和departments表都有一个叫id的列。这不是我随手起的名字而是真实项目里极常见的命名习惯——每张表都用id做主键。这个命名习惯本身没问题但它和自然连接凑在一起就是事故的起点。1.2 同一份数据在三种连接下的输出差异把三种写法放在一起跑结果差异一目了然。下面的对照表按输出行数和关键特征整理方便直接比对。写法输出行数张三(10)王五(NULL)行政部(30)INNER JOIN ... ON e.dept_id d.id3保留丢弃丢弃LEFT JOIN ... ON e.dept_id d.id4保留保留右表全 NULL丢弃RIGHT JOIN ... ON e.dept_id d.id4保留丢弃保留左表全 NULLNATURAL JOIN0丢弃丢弃丢弃前三行的行为是符合直觉的内连接只保留能配对的左连接保住左表全部右连接保住右表全部。第四行的结果才是真正值得注意的地方——NATURAL JOIN输出 0 行。原因在于它自动把两张表所有同名列都拿来当连接条件也就是employees.id departments.id AND employees.dept_id departments.id而员工 id 是 1 到 4部门 id 是 10、20、30两个条件同时成立的行根本不存在。这个例子里业务语义上正确的连接条件应该是e.dept_id d.id但自然连接自动推导出的是e.id d.id条件本身就用错了。它不会给你任何警告也不会在语法检查阶段报错只会安静地返回一个错误的结果集。1.3 自然连接在工程实践里基本被禁用的原因把自然连接的问题归纳一下核心是三条。第一连接条件隐式化条件不在 SQL 文本里读代码的人必须去查两张表的完整结构才能知道它连了什么代码审查时几乎无法被发现。第二对表结构变更零防御任何人往任意一张表加一个同名同类型的列连接条件就悄悄变了比如两张表都加tenant_id自然连接立刻变成多条件连接结果可能从数据偏多变成数据偏少。第三没有同名列时退化成笛卡尔积如果两张表恰好一列同名都没有NATURAL JOIN的行为等价于CROSS JOIN行数直接爆炸。我在实际项目里的做法很明确业务代码里一律不写NATURAL JOIN只在一次性探索数据、临时写 ad-hoc 查询时偶尔用一下而且用完立刻丢掉。团队里如果有新人写了这个代码评审阶段就应该拦下来。这条规则听起来有点绝对但比起事后排查数据对不上的成本绝对一点更划算。2. 内连接看起来最简单写错的方式却最多2.1 ON 和 WHERE 在内连接里到底等不等价网上流传很广的一个说法是内连接里 ON 和 WHERE 完全等价随便写。这句话在结果集层面基本成立因为优化器会把内连接的 ON 条件合并进 WHERE 一起处理执行计划通常一致。但结果一样和应该这么写是两码事我建议严格区分连接条件放 ON行过滤条件放 WHERE。原因有两个。一是可读性。一条涉及四五张表的查询如果所有条件都堆在 WHERE 里读的人根本分不清哪个条件是表 A 和表 B 的连接键、哪个是业务过滤。等你哪天要把内连接改成左连接就得从头捋一遍每个条件该往哪挪。二是可维护性。内连接改成外连接时原本堆在 WHERE 里的连接条件如果不挪到 ON 里语义立刻出错——这个问题在外连接那一节会详细讲。有一种情况 ON 和 WHERE 确实不等价ON 条件里包含子查询或者非确定性函数时优化器的处理路径可能不同。不过这种写法本身就少见也不推荐正常业务查询尽量别在 ON 里塞子查询。2.2 USING 关键字列合并带来的输出变化USING是介于ON和NATURAL JOIN之间的一个折中语法。它要求你显式写出列名但会像自然连接一样把同名输出列合并成一列。-- 用 ON 时两表的 id 列都会输出SELECT * 有 5 列 SELECT * FROM employees e JOIN departments d ON e.dept_id d.id; -- 用 USING 时前提是两表有同名列同名列只输出一次 SELECT * FROM employees e JOIN departments d USING (dept_id);第二句要求departments表也有dept_id列这在实际表结构里不一定成立所以USING的适用范围比想象中窄。它的坑在于如果程序里用SELECT *然后按列位置取值比如某些 ORM 的简易映射或者老式的 ResultSet 按索引读USING会让输出列数比ON少字段映射直接错位。我的建议是生产代码里永远不要SELECT *做表连接把需要的列一个个写出来既避免这个问题也减少不必要的回表和数据传输。2.3 隐式连接与显式 JOIN 的执行差异老派写法用逗号加 WHERESELECT e.name, d.dept_name FROM employees e, departments d WHERE e.dept_id d.id;这种写法在 MySQL 里会被优化器改写成显式 JOIN执行计划通常完全一致性能上没有区别。它的真正风险是漏写 WHERE 条件。一旦条件漏了语句照样能跑返回的是完整的笛卡尔积。表小的时候察觉不到表一大就是灾难。显式JOIN ... ON语法强制你把连接条件写在结构里虽然理论上也能写成JOIN不带ON等价于CROSS JOIN但至少视觉上缺失得很明显代码评审容易发现。顺带说一句MySQL 里JOIN、INNER JOIN、CROSS JOIN三者语法上是等价的CROSS JOIN也可以带ON条件。这跟某些数据库的实现不一样跨库迁移的时候要注意。2.4 多表内连接时书写顺序不等于执行顺序三张以上的表做内连接SQL 里谁写在前面不代表数据库就先读谁。MySQL 优化器会基于统计信息和成本估算决定驱动表可能把你写在最后面的表当成驱动表。想确认实际执行顺序看EXPLAIN的id和table列id相同的行从上到下大致反映连接顺序但id不同就要结合select_type判断。如果确实需要强制某张表做驱动表比如小表驱动大表能显著减少扫描量可以用STRAIGHT_JOIN强制按书写顺序连接或者用优化器提示SELECT /* JOIN_ORDER(d, e) */ e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id d.id;不过这类 hint 属于最后手段先确认统计信息是不是过期、索引是不是缺失比上来就写 hint 更靠谱。我见过不少项目堆了一堆 hint实际上是ANALYZE TABLE很久没跑、基数估算严重失真导致的。3. 外连接左右之分背后是谁是主表的问题3.1 LEFT JOIN 的语义拆解与 NULL 填充位置左连接的规则可以概括成一句话左表每一行都必须在结果里出现至少一次右表匹配不上就填 NULL。这个每一行是关键。很多人以为左连接是左表所有列都保留、右表尽量匹配其实更准确的理解是结果集的行数以左表为基准右表的匹配情况只决定每行右半部分有没有值。右连接就是把左右对调逻辑完全对称。工程上我基本只用LEFT JOINRIGHT JOIN只在一个场景下用——多表连接时前一张表已经被左连接固定住后面要接的表需要以它为主这时候用右连接可以让 SQL 的书写顺序和语义顺序一致。但这种场景不多绝大多数情况下用左连接加调整书写顺序就能表达清楚可读性更好。NULL填充的具体位置也要注意外连接产生的是真正的 NULL不是空字符串也不是 0。这在后续做COUNT、SUM、字符串拼接时都会带来影响后面专门说。3.2 ON 和 WHERE 在外连接里的分工写反了结果全错这是外连接最容易出错的地方也是我见过最多的线上问题。规则很简单外连接的过滤条件写在 WHERE 里会把外连接退化成内连接。-- 错误写法想要所有员工 他们所在的研发部结果只剩研发部员工 SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id WHERE d.dept_name 研发部; -- 正确写法过滤条件放进 ON SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id AND d.dept_name 研发部;第一句为什么错因为王五没有部门右表的dept_name是 NULLNULL 研发部的结果是 UNKNOWN在 WHERE 里被当作不成立整行被过滤掉。于是所有员工变成了研发部员工左连接的语义被悄悄吃掉。第二句把条件放进 ON过滤只作用于右表能不能匹配上不匹配的照样补 NULL 保留左表行。这才是左连接该有的行为。这里要澄清一点并不是说 WHERE 里永远不能出现右表字段。如果业务需求本身就是只要那些有部门且部门是研发部的员工那写 WHERE 也完全正确只是这时候用INNER JOIN更直白。判断标准是你先想清楚这一行该不该保留再决定条件放哪而不是先写 SQL 再倒推语义。3.3 MySQL 没有 FULL OUTER JOIN怎么模拟MySQL 至今不支持FULL OUTER JOIN语法。要表达两边都保留的语义标准做法是左连接和右连接取并集SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id UNION SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id d.id;注意这里用UNION而不是UNION ALL。因为两边都能匹配上的行会在两个查询里各出现一次用UNION会自动去重。但如果结果集里本来就有完全重复的业务行比如两个同名员工同一个部门去重会把它们合并这时候就得改用反连接的方式拼先取左连接的完整结果再UNION ALL上右表有、左表没有的那部分。SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id UNION ALL SELECT NULL, d.dept_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id d.id);这种写法啰嗦但能精确控制去重行为。实际做数据核对、找两边对不上的记录时反连接LEFT JOIN ... WHERE 右表主键 IS NULL或者NOT EXISTS比模拟全外连接更常用、也更快。3.4 外连接配 COUNT 时的经典数字陷阱外连接加聚合函数是统计类需求的标配组合也是数字出错的常发地带。核心区别只有一条COUNT(*)统计行数COUNT(列名)只统计该列非 NULL 的值。-- 需求每个部门有多少员工没有员工的部门显示 0 SELECT d.dept_name, COUNT(*) AS emp_cnt FROM departments d LEFT JOIN employees e ON e.dept_id d.id GROUP BY d.dept_name;上面这句对行政部会返回 1 而不是 0因为左连接给行政部补了一行右表全 NULL 的记录COUNT(*)把这行算进去了。正确写法是用右表的主键或者任意非空列SELECT d.dept_name, COUNT(e.id) AS emp_cnt FROM departments d LEFT JOIN employees e ON e.dept_id d.id GROUP BY d.dept_name;COUNT(e.id)遇到 NULL 会跳过行政部就是 0。这个坑我在报表项目里见过不止一次而且往往要等业务方质疑数据才被发现。类似的还有SUM外连接产生的 NULL 行参与SUM时结果不变NULL 被忽略但如果用SUM(IFNULL(列, 0))这类写法逻辑就要重新捋一遍别想当然。4. 自然连接语法糖省下的键盘都变成了排查时间4.1 NATURAL JOIN 到底自动做了什么NATURAL JOIN的规则可以精确描述成三步第一扫描两张表的列定义找出所有名字相同且类型可比较的列第二用这些同名列的等值比较作为连接条件第三输出结果时这些同名列只保留一份。换句话说A NATURAL JOIN B等价于A JOIN B USING (所有同名列)。理解这个等价关系就能推导出它的所有风险。条件不是你写的是数据库猜的。数据库猜的依据只有列名完全不管业务语义。前面那个员工表和部门表都有id的例子从列名角度看连接条件毫无破绽从业务角度看完全是错的。还有一种更隐蔽的情况两表完全没有同名列。这时候NATURAL JOIN的行为退化成笛卡尔积不报错不警告直接返回 m×n 行。表大一点查询直接超时或者把内存打满。4.2 加列引发的雪崩同名列污染我见过最典型的一次事故是这样的某业务系统的两张表一开始只有id一个同名列NATURAL JOIN一直在正常跑。后来做数据治理两张表都加了tenant_id和create_time用于多租户隔离和审计。上线之后没人注意连接语句结果连接条件从 1 个变成 3 个create_time精确到秒两张表分别在不同时刻写入几乎不可能相等结果集直接变成空。这类问题的排查成本极高因为它满足三个特征不报错、不慢、结果结构完全正常只是数据不对。等业务方发现数据缺失再去定位中间可能已经过去好几天。而且因为 SQL 语法本身没毛病静态检查工具也发现不了。4.3 NATURAL LEFT JOIN 这类变体的风险自然连接还能和外连接组合写成NATURAL LEFT JOIN语义是以左表为主用所有同名列做连接条件。这种写法在实际代码里出现频率很低但风险比NATURAL JOIN更高——因为左连接本身就有保留所有左表行的语义一旦连接条件被同名列污染结果不是变空而是变成所有左表行都保留、右表全部补 NULL表面上行数还对得上实际所有关联字段都是空的更难发现。我的态度很直接这类写法在生产代码里应该被完全禁掉。如果非要用语法糖减少重复用USING(列名)更安全至少列名是显式写出来的加新列不会自动改变连接条件。4.4 什么场景下可以考虑使用自然连接说了这么多缺点也得给个相对中立的使用边界。以下几种情况可以考虑临时查询探索数据表结构完全清楚且确认两表只有一个语义正确的同名列教学演示用来说明连接概念两张表结构高度对称的宽表做快速比对。除此之外尤其是任何要进代码仓库、要被别人阅读、要长期维护的 SQL都不建议用。判断标准其实很简单如果这段 SQL 三个月后由另一个人来看他能不能只看 SQL 本身就知道连接条件是什么自然连接永远通不过这个测试。5. 优化器怎么执行连接理解它才知道该改什么5.1 Nested Loop Join 的基本形态MySQL 处理连接的主流算法是嵌套循环。外层循环遍历驱动表的每一行内层循环到被驱动表里找匹配的行。如果被驱动表的连接列上有索引内层循环每次是索引查找复杂度接近 O(驱动表行数 × log 被驱动表行数)如果没有索引内层循环每次都是全表扫描复杂度退化成笛卡尔积级别。这解释了一条最实用的优化原则驱动表选小表被驱动表的连接列必须有索引。反过来如果被驱动表的连接列没索引哪怕驱动表只有几十行被驱动表几百万行每条驱动记录都要扫一遍全表查询时间直接不可接受。外连接在这条规则上有个额外约束驱动表是固定的。左连接的驱动表必须是左表右连接必须是右表这个顺序不能由优化器随意调整因为调整会改变语义。所以左连接写反了主表优化器也救不了你。这也是为什么外连接比内连接更容易出现性能问题——你实际上是在用 SQL 的书写顺序替优化器做决策。5.2 MySQL 8.0 的 Hash Join 与 BNL 的替换MySQL 8.0.18 引入了 Hash Join用于等值连接且被驱动表没有可用索引的场景。原理是把较小的那部分数据在内存里建成哈希表然后遍历另一部分数据做哈希探测复杂度接近线性。8.0.20 之后原来的 Block Nested-Loop 算法被 Hash Join 取代。这对开发者意味着什么以前遇到被驱动表没索引的连接只能眼睁睁看着 BNL 扫来扫去现在优化器可能自动切成 Hash Join性能好很多。但 Hash Join 有前提必须是等值连接且内存够用。连接条件里写、、!或者LIKE %xx这类非等值条件还是走嵌套循环。连接数据量超过join_buffer_size会溢写到磁盘性能会明显下降。判断有没有走 Hash Join看EXPLAIN的Extra列出现Using join buffer (hash join)就是走了。如果看到Using join buffer (Block Nested Loop)说明版本较老或者条件不满足 Hash Join 的触发条件。5.3 EXPLAIN 里应该重点看哪几列MySQL 的EXPLAIN输出信息很多看连接性能主要盯这几列。列名关注点type访问类型ALL是全表扫描ref/eq_ref/range是走索引key实际使用的索引为 NULL 说明没走索引rows预估扫描行数驱动表这一行要尽量小filtered过滤后剩余行的百分比过低说明索引选择性差ExtraUsing join buffer说明没索引Using temporary/Using filesort说明有额外排序开销一个典型的问题是type显示ALL、rows很大、Extra是Using join buffer (hash join)。这时候虽然 Hash Join 能跑但本质上还是全表数据参与哈希构建数据量一大照样慢。正确的做法是给连接列补索引让type变成refrows降下来Extra里不再出现 join buffer。另一个容易忽略的点是filtered。假设驱动表rows是 1000filtered是 1%实际传给下一张表的只有 10 行如果filtered是 100%就是 1000 行全部传下去。驱动表的选择不只看行数还要看过滤后的实际行数。6. 实战选型三种连接到底该怎么选6.1 选型决策表把前面讨论的内容压缩成一张决策表实际写 SQL 时可以直接对照。业务需求推荐写法理由只要两边都匹配上的数据INNER JOIN ... ON语义最直白优化器自由度最高以一边为主另一边可有可无LEFT JOIN ... ON或右连接主表行全部保留NULL 填充另一侧找主表有、从表没有的孤儿数据LEFT JOIN ... WHERE 从表主键 IS NULL反连接比NOT IN在 NULL 场景下更安全两边都要保留LEFT JOIN UNION RIGHT JOINMySQL 无全外连接只能模拟临时探索数据NATURAL JOIN快但用完即弃不进代码库同名列显式且确定JOIN ... USING(列名)比自然连接安全比 ON 简洁表格之外还有一条经验能内连接就别外连接能单表就别连接。外连接的分析成本、索引要求、NULL 处理复杂度都比内连接高一个量级。很多需求看起来必须外连接其实拆成两条简单查询在应用层拼装更清晰也更好优化。6.2 几个真实踩过的坑第一个坑是外连接条件写进了 WHERE。某次做员工考勤报表需求是所有员工 本月请假天数SQL 写成了LEFT JOIN 请假表 ... WHERE 请假月份 当前月。结果没有请假的员工全部消失报表人数比 HR 系统少了一截。修复方式就是把月份条件挪进ON或者用IFNULL在 SELECT 里处理。这个错误之所以常见是因为 SQL 本身语法合法、查询还很快只有对数据才能发现异常。第二个坑是多表连接中的外连接被后续内连接截断。三张表 A LEFT JOIN B LEFT JOIN C一切正常后来为了过滤某个状态在末尾加了一句INNER JOIN D结果 A 中那些在 D 里匹配不上的行全被丢弃前两个左连接的语义全部失效。修复思路有两种把 D 也改成左连接或者把 D 的过滤下推到子查询里。选择哪种取决于业务语义——到底是要所有 A还是有 D 的 A。第三个坑是自然连接加列。前面已经详细说过某张表新增了同名列自然连接的条件悄悄变化数据从有变空或者从少变多。这类问题唯一的预防手段就是禁用自然连接没有别的办法。第四个坑是外连接配聚合函数。除了前面说的COUNT(*)问题还有SUM在空集上的行为。SUM(NULL)返回 NULL 而不是 0如果前端拿到 NULL 直接展示会出问题通常需要在 SQL 层用IFNULL(SUM(...), 0)兜底或者在应用层处理。6.3 一份可以直接套用的自检清单写完一条多表连接 SQL提交之前过一遍下面这几条能挡掉大部分低级错误。连接条件是否全部写在ON里业务过滤条件是否全部写在WHERE里两者有没有混放。外连接的主表是否符合业务语义我要的是左表全部还是右表全部有没有写反。WHERE里有没有引用外连接从表的列除了IS NULL这种反连接用法有的话确认是不是有意为之。聚合函数用的是COUNT(*)还是COUNT(列名)和要不要统计空行的需求是否一致。有没有使用NATURAL JOIN如果有能不能换成显式ON或USING。SELECT *有没有用在多表连接上能不能改成明确列出所需列。EXPLAIN跑一下被驱动表的连接列有没有索引type是不是ALLrows是否在可接受范围。多表连接里是否混用了内连接和外连接内连接的位置会不会把前面外连接保留的行截断。这套清单看起来琐碎但每一条都对应过真实的问题。我个人在改别人 SQL 的时候基本是照着这个顺序从头过一遍比直接跑EXPLAIN调优更高效——很多问题压根不是性能问题是语义写错了跑得越快错得越多。最后分享一个我自己养成的小习惯任何涉及外连接的查询我都会额外写一条只查左表或者只查右表的对照查询两边行数对一下。比如左连接之后的COUNT(*)必须等于左表的COUNT(*)不等就说明有问题。这个对照操作花不到一分钟但能挡掉绝大部分外连接写错的情况比事后被业务方追着问数据为什么不对要划算得多。
