做数据库这行时间久了会发现一个挺有意思的现象面试的时候人人都能背出 inner join 和 left join 的区别可真到了线上写 SQL因为 join 写错导致数据对不上、报表翻倍、接口超时的事故依然层出不穷。原因不复杂——大多数人对 join 的理解停留在两个表拼一起这个比喻上而这个比喻恰好掩盖了真正重要的东西连接算法怎么选、驱动表是谁、on 和 where 分别在哪一步生效、一对多关系会把结果放大多少倍。这些细节决定了你的 SQL 是跑 40 毫秒还是 40 秒返回的是 100 行还是 100 万行。这篇内容就是冲着这些细节来的。我会从 join 在数据库内部的执行过程讲起把嵌套循环、哈希连接、排序合并这三种算法的适用条件说清楚然后拿一份可以自己动手复现的测试数据把几种 join 的行为差异一条条跑给你看最后落到索引设计、驱动表选择、慢 SQL 排查这些真正影响线上表现的地方。不管你是刚开始学 SQL 的新手还是已经写了几年业务查询但总觉得性能优化没抓手的人都能从里面挑到能直接用的东西。1. 为什么 join 值得单独花时间搞懂1.1 join 在数据库内部到底做了什么很多人以为 join 是数据库的一个高级功能其实它的本质非常朴素从两张表里各取一行判断它们是否满足条件满足就拼成一行输出。数据库做的事情就是把这个判断过程尽量少做、做快。以最常见的等值连接为例A JOIN B ON A.id B.a_id数据库要解决的核心矛盾是A 表有 m 行B 表有 n 行如果老老实实两两比较需要 m×n 次匹配操作。这个数字在几百行的测试表上完全看不出来但在两张千万行的表上就是天文数字。所以所有 join 优化的努力本质上都是在回答同一个问题怎么把 m×n 次比较降下来。降低的方式无非两类。一类是在 B 表的连接列上建立索引这样 A 表每取一行去 B 表里找匹配不需要全表扫一次 B 树查找就够了复杂度从 m×n 降到 m×log(n)。另一类是一次性把其中一张表的数据装进内存的哈希结构里另一张表扫一遍逐个探测复杂度降到 mn。这两种思路对应了不同的连接算法选择哪一种取决于表的大小、有没有索引、连接条件是不是等值。理解了这个前提后面所有的优化手段都能自己推导出来要么让比较次数变少要么让每次比较变快。1.2 三种连接算法的适用条件数据库里主流就三种算法MySQL、Oracle、SQL Server 的实现细节不同但思路是一致的。嵌套循环连接Nested Loop Join是最直白的一种外层表取一行内层表去找匹配找到就输出找不到就跳过然后外层取下一行。它的性能完全取决于内层表的查找效率——如果内层表的连接列有索引速度非常可观如果没有索引那就是灾难性的全表扫描反复执行。这也是为什么被驱动表的连接列必须建索引会成为一条铁律。哈希连接Hash Join的思路是先用数据量小的那张表在内存里建一张哈希表key 是连接列的值然后扫描大表每行拿连接列去哈希表里探测一次。它要求连接条件是等值条件因为哈希表没法处理范围匹配。哈希连接的优点是只扫两遍数据不依赖索引特别适合两张都很大、又都没法走索引的表。缺点是要吃内存内存放不下会退化成带磁盘临时文件的版本速度会掉一个档次。排序合并连接Merge Join则是把两张表分别按连接列排好序然后用两个游标同步推进谁小谁往前走。如果两边都已经有序比如连接列上有聚簇索引或已经排好序的子查询结果这一步几乎不需要额外开销如果没排序那排序本身的代价可能比连接还大。实际执行时选哪种是优化器根据统计信息算代价决定的。你要做的是看懂执行计划里出现的是哪一种进而判断它选得对不对。1.3 笛卡尔积是理解一切连接的起点不写 on 条件的A CROSS JOIN B或者A, B返回的就是笛卡尔积m 行乘以 n 行。100 行的表跟 100 行的表做笛卡尔积结果是 10000 行看起来还好但 10000 行的表跟 10000 行的表就是 1 亿行这种 SQL 扔到线上足以把内存打满。关键在于写上了 on 条件的 join本质上是先产生笛卡尔积再用条件筛掉不匹配的部分在逻辑层面的等价表达。实际执行时数据库当然不会真的先做笛卡尔积但这个逻辑模型能解释很多现象。比如为什么漏写关联条件会突然返回海量数据——因为条件都没了笛卡尔积被原样输出。再比如为什么三张表 join 的时候中间结果的膨胀速度会失控——假设 A 有 1000 行B 有 1000 行A 到 B 是一对多连接后变成 10000 行再跟 C 表 join如果这个 10000 行的中间结果还要跟 C 做一对多结果就是几十万行。很多人写多表 join 时只盯着最终的过滤条件完全没意识到中间结果已经膨胀了一百倍。所以我个人的习惯是写完一个多表 join先在脑子里推一遍每一步的行数变化如果某一步的行数超过最终需要的量级就说明这里可能需要提前聚合或者调整连接顺序。2. 几种 join 的语义差别与真实业务对照2.1 inner join 与 left join 的核心分界inner join 返回的是两张表都匹配上的行也就是交集。left join 返回的是左表全部的行右表匹配上就填值匹配不上就填 NULL。用业务场景说更清楚。假设要查所有用户以及他们的订单金额。用 inner join得到的结果里只有下过单的用户用 left join得到的结果里包括从没下过单的用户这些用户的订单金额列是 NULL。这两种结果对业务来说完全是两回事——前者是有订单的用户列表后者是全部用户及其消费情况。有意思的是很多线上事故就出在这里。报表同学想要全部用户写成了 inner join结果沉默用户全部消失数据看板上用户数莫名其妙少了一大截反过来运营想要本月有下单的用户写成了 left join 又没加过滤条件结果混进来一大批 NULL 行人均消费被拉低了。选哪种取决于你要的语义是只保留匹配成功的还是保留主表全集。判断方法很简单问一句主表里那些没有对应记录的行我要不要。要就 left join不要就 inner join。right join 和 left join 只是主表换了个位置实际项目里很少用因为可读性差——人的阅读顺序是从左往右把主表放在右边会让人多绕一道弯。我一般建议统一用 left join需要 right join 的时候把两张表顺序调换一下就行。2.2 full outer join 与 cross join 的适用边界MySQL 到现在也不支持 full outer join需要用 left join 和 right join 做 union 来模拟。写法大概是这样SELECT a.id, a.name, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id UNION SELECT a.id, a.name, b.amount FROM users a RIGHT JOIN orders b ON a.id b.user_id;注意这里必须用 UNION 而不是 UNION ALL因为两张表里同时匹配上的行会在两个结果集里各出现一次用 UNION ALL 会重复。这个写法的代价是两边都要扫一遍数据量大时开销不小能用其他方式表达需求就尽量别用。cross join 就是前面说的笛卡尔积听起来像是要避开的操作但它有个很实用的场景生成日期序列、数字序列这类基础数据。比如要补齐一份每天每商品的销量报表商品表跟日期表 cross join 就能造出完整的骨架再去 left join 实际销量数据空缺的日子补零。这种用法是合理且高效的前提是两张表的行数都受控。提示cross join 用错的地方通常是漏写了 join 条件而不是故意为之。写完多表查询后一定要数一遍 on 子句的个数n 张表连接应该有 n-1 个 on 条件。2.3 on 与 where 的位置决定了结果集大小这是我看过最高频的 SQL 错误没有之一。同样一段逻辑条件写在 on 后面和写在 where 后面结果完全不同。-- 写法一条件在 on 里返回所有用户未支付订单的金额为 NULL SELECT a.id, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id AND b.status 1; -- 写法二条件在 where 里等价于 inner join SELECT a.id, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id WHERE b.status 1;为什么差异这么大因为 left join 的执行逻辑是先按 on 条件去右表找匹配找不到就保留左表行、右表列全部填 NULL。写法一里b.status 1是匹配条件的一部分用户没有已支付订单就匹配不上于是保留左表行、右表列填 NULL用户还在结果里。写法二里left join 先把所有订单都关联上然后 where 对关联后的结果做过滤那些右表填 NULL 的行因为NULL 1不成立被过滤掉了左表行也跟着消失语义上就退化成了 inner join。记忆方法只有一个on 决定怎么匹配where 决定匹配完之后留下什么。只要你对右表的列在 where 里做非空过滤left join 就一定会变成 inner join。理解了这条就不会再困惑为什么我明明写了 left join结果却少了一批数据。3. 手把手跑通一组多表关联查询3.1 准备一份可复现的测试数据光看理论没用还是得自己跑一遍。下面这份数据我用过很多次结构简单但能覆盖大部分 join 场景。两张表用户和订单故意留了几个没有订单的用户也留了几个状态不同的订单。CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(32) NOT NULL, city VARCHAR(32) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), status TINYINT, KEY idx_user_id (user_id) ); INSERT INTO users VALUES (1, 张三, 北京), (2, 李四, 上海), (3, 王五, 北京), (4, 赵六, NULL); INSERT INTO orders VALUES (101, 1, 199.00, 1), (102, 1, 89.50, 1), (103, 2, 320.00, 1), (104, 2, 50.00, 0), (105, 3, 128.00, 0), (106, NULL, 66.00, 1);注意几个刻意的设计赵六没有订单用来观察 left join 保留左表的效果李四和王五各有未支付订单用来验证 on 和 where 的差别订单 106 的 user_id 是 NULL用来观察 NULL 值在连接中的表现。3.2 六条查询看清 join 的行为差异数据准备好之后把下面几条查询依次跑一遍对比结果行数比看十页文档都管用。第一条inner join用户下过单的记录SELECT a.name, b.order_id, b.amount FROM users a INNER JOIN orders b ON a.id b.user_id;结果是 5 行张三 2 行、李四 2 行、王五 1 行赵六和订单 106 都消失了。第二条left join看全部用户SELECT a.name, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id;结果 6 行多出来的那行是赵六order_id 和 amount 都是 NULL。第三条left join 加 where 过滤右表SELECT a.name, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id WHERE b.status 1;结果只剩 3 行只有已支付的订单。赵六没了李四和王五的未支付订单也没了。这条查询的语义跟 inner join 加 status 过滤完全一致但执行计划可能不一样因为优化器需要先做一次转换。第四条left join 加 on 条件SELECT a.name, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id AND b.status 1;结果回到 4 行赵六的 NULL 行回来了另外李四和王五的未支付订单被过滤掉但他们本人还在因为订单 103 是已支付的。第五条统计每个用户的订单数这个要特别小心SELECT a.name, COUNT(*) AS cnt, COUNT(b.order_id) AS cnt2 FROM users a LEFT JOIN orders b ON a.id b.user_id GROUP BY a.id, a.name;赵六这一行cnt 是 1cnt2 是 0。原因很清楚COUNT(*)数的是结果集的行数left join 给赵六补了一行 NULL所以算作 1COUNT(b.order_id)只统计非 NULL 值所以是 0。做用户订单数统计的时候必须用后者用前者会让所有没下单的用户都显示 1 单。第六条反向验证外键孤儿数据SELECT b.order_id, b.user_id FROM orders b LEFT JOIN users a ON b.user_id a.id WHERE a.id IS NULL;这条查询返回订单 106它的 user_id 是 NULL。这个套路在数据质量检查里非常常用用来找出子表里存在但主表里没有的脏数据。把两个表位置对调就能检查出所有类型的外键异常比写一堆 count 比对高效得多。3.3 用执行计划验证你的判断跑完查询之后在 MySQL 里加上EXPLAIN前缀再看一次重点盯三个字段。type表示访问类型从好到坏依次是 system、const、eq_ref、ref、range、index、ALL。join 场景里最常见的是 ref用到了非唯一索引和 eq_ref用到了唯一索引或主键如果出现 ALL说明被驱动表在做全表扫描这就是要优化的信号。rows是优化器估算要扫描的行数这个数字在 join 场景下容易被低估尤其是统计信息过期的时候。如果你看到 rows 是几百实际跑出来几百万先去ANALYZE TABLE更新一下统计信息。Extra字段信息量最大。出现Using join buffer (Block Nested Loop)或 MySQL 8.0 之后的Using join buffer (hash join)说明被驱动表没有可用索引正在用内存缓冲退而求其次出现Using filesort说明排序没走索引出现Using temporary说明用了临时表通常是 group by 或 distinct 引起的。一条 SQL 如果同时出现 join buffer、filesort、temporary 三个基本可以判定有优化空间。至于先优化哪个我的经验是先解决 join buffer因为它意味着数据量的放大后面两个往往会被顺带解决。4. join 性能优化的四个抓手4.1 被驱动表的连接列必须有索引这条是 join 优化的第一原则没有之一。前面说过嵌套循环连接里内层表的查找效率决定一切而内层表就是被驱动表。怎么判断驱动表是谁在 left join 里左表通常是驱动表MySQL 8.0 在外连接可以转换为内连接的情况下会重新选择在 inner join 里优化器会选结果集更小的那张表做驱动表。以A JOIN B ON A.id B.a_id为例如果 A 是被驱动表那 A.id 上要有索引如果 B 是被驱动表那 B.a_id 上要有索引。实际项目中经常遇到的情况是主键和唯一键都有索引但外键列忘了建。比如订单表的 user_id、日志表的 device_id这些列在业务查询里天天用来 join却没建索引导致每次关联都是全表扫描。加一个普通索引就能让查询从秒级降到毫秒级投入产出比极高。有一点要注意索引不只是在 where 里过滤时有用join 的连接列同样需要。很多人建索引时只考虑查询条件忘了连接条件这个盲区挺常见的。还有复合索引的顺序问题。如果连接列和过滤列经常一起出现可以考虑建复合索引把连接列放前面。比如(user_id, status)既能用于 join 匹配又能在匹配之后用 status 过滤一个索引顶两个用。4.2 驱动表选择与 join buffer 的调节驱动表选小表是基本原则原因是嵌套循环的外层循环次数直接等于驱动表行数外层少一次内层就少扫一遍。inner join 里优化器一般会自己选但它的判断基于统计信息统计信息不准的时候会选错。这时候可以用STRAIGHT_JOIN强制指定顺序不过这是最后的办法先尝试更新统计信息。left join 的顺序是语义决定的不能随便调换反过来讲如果你知道哪张表数据少把它放在左边写 left join天然就是小表驱动。当被驱动表实在没索引可用时MySQL 会用 join buffer 把驱动表的数据批量化加载到内存里再一次性去被驱动表比对减少重复扫描。这个缓冲区的大小由join_buffer_size控制默认 256KB可以适当调大。-- 查看当前值 SHOW VARIABLES LIKE join_buffer_size; -- 会话级调整测试用 SET SESSION join_buffer_size 4 * 1024 * 1024;注意join_buffer_size 是每个连接各自分配的调太大会在并发高的时候把内存吃光。生产环境建议先观察并发连接数再决定几百 MB 这种操作不要轻易尝试。而且从根本上说加索引比调大缓冲区更划算缓冲区是被迫的选择。4.3 降低参与连接的行数如果索引已经建好了速度还是上不去那要看的就不是连接本身而是有多少行参与了连接。优化方向有两个提前过滤和提前聚合。提前过滤的道理很直观。如果 A 表 1000 万行但真正需要的只有 1 万行那就先用子查询或者 CTE 把这 1 万行筛出来再 join而不是把 1000 万行全拖进连接过程。-- 不推荐先连接再过滤 SELECT a.id, b.amount FROM users a JOIN orders b ON a.id b.user_id WHERE a.city 北京 AND b.created_at 2024-01-01; -- 推荐先各自过滤再连接前提是过滤后行数明显变少 SELECT a.id, b.amount FROM (SELECT id FROM users WHERE city 北京) a JOIN (SELECT user_id, amount FROM orders WHERE created_at 2024-01-01) b ON a.id b.user_id;不过这里有个前提要判断清楚如果优化器本来就能把 where 条件下推两种写法执行计划是一样的那没必要改写反而降低了可读性。判断方法是把两条 SQL 都 EXPLAIN 一遍看 rows 估算和实际访问方式有没有区别。提前聚合解决的是另一类问题一对多连接导致结果膨胀。比如要查每个用户的订单总额直觉写法是先 join 再 group by。SELECT a.id, a.name, SUM(b.amount) AS total FROM users a LEFT JOIN orders b ON a.id b.user_id GROUP BY a.id, a.name;这段逻辑上没问题但如果用户表还跟另外几张表 join中间结果会被订单表放大很多倍。稳妥的做法是先在子查询里把订单聚合到用户粒度再 join。SELECT a.id, a.name, COALESCE(t.total, 0) AS total FROM users a LEFT JOIN ( SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ) t ON a.id t.user_id;这样参与 join 的右侧变成了每个用户一行不再膨胀。代价是子查询要单独扫一遍订单表在有索引的情况下这点开销是可以接受的。4.4 几个慢 SQL 排查实录说几个我实际处理过的案例都是 join 相关的典型问题。案例一报表查询从 40 毫秒变成 8 秒。改动只是加了一个 left join被驱动表的连接列没索引。EXPLAIN 一看type 是 ALLrows 是 80 万。加上索引之后回到 40 毫秒。这个案例说明一件事新加 join 一定要跑一次执行计划别想当然。案例二数据行数对不上翻了三倍。排查发现用户表跟地址表 join一个用户有多个地址本来以为是一对一。改成先对地址做聚合取默认地址再去 join行数恢复正常。这种问题不会报错只会静默地给出错误结果最危险。案例三两个大表 join 跑不出来。两边各百万行连接列都没法走索引。改写成先各自做条件过滤把行数压到几千再 join从跑不出来变成 200 毫秒。案例四索引明明建了却用不上。查看发现两张表的连接列字符集不一致一张是 utf8一张是 utf8mb4导致隐式转换索引失效。统一字符集后问题解决。这类问题很隐蔽只能靠仔细核对表结构。案例五连接列类型不一致。一边是 INT一边是 VARCHARMySQL 会把 VARCHAR 转成数字索引同样失效。这类问题在建表阶段就该避免事后修改成本很高因为要改数据类型或者加上转换函数但加了函数索引又用不上。实操心得遇到 join 慢排查顺序我一般是这样——先 EXPLAIN 看被驱动表是不是 ALL是就补索引不是就看 rows 估算跟实际差多少差得多就 ANALYZE TABLE还不行就看连接列的类型和字符集是否一致最后才考虑改写 SQL 或者调整参数。5. 常见问题速查与避坑心得5.1 常见问题速查表现象大概率原因处理方式left join 结果比预期少where 里过滤了右表列把条件挪到 on 里或改用 inner join结果行数成倍膨胀一对多连接未聚合先按主表粒度聚合再 join没下单的用户统计出 1 单用了 COUNT(*)改成 COUNT(右表主键)明明有索引却全表扫描类型或字符集不一致统一两侧列的类型与字符集连接条件匹配不上 NULLNULL 不参与等值比较用 IS NULL 或 COALESCE 预处理执行计划出现 join buffer被驱动表连接列无索引补索引索引优先于调参数多表 join 越来越慢中间结果膨胀减少参与连接的行数或调整顺序5.2 几个容易被忽略的细节NULL 在连接里的表现值得单独说一句。NULL NULL的结果不是 true而是 unknown所以两张表里连接列都是 NULL 的行永远匹配不上。如果你的数据里连接列可能为空要么在建表时就设成 NOT NULL要么在连接条件里显式处理别指望数据库帮你兜底。多表 join 的书写顺序会影响可读性也会影响优化器的选择空间。我的习惯是把数据量最小的表放在最左边然后按关联关系依次往右写每个 join 都紧跟它要关联的那张表。这样别人读你的 SQL 时能顺着数据流一路看下去不用来回跳。还有一个细节是别名。给每张表起了别名之后所有列都要带别名前缀哪怕这个列名在两张表里不重名。原因不是为了好看而是等以后有人往查询里加了第三张表恰好有个同名列那时候再回头加前缀成本比一开始就加高得多。关于 join 的列数也要控制。有些查询一口气 join 七八张表每张表都取一堆列。这种 SQL 一旦某张表的数据量上来整体就会失控而且很难定位是哪一段出的问题。能拆成两步的就拆开中间结果落到临时表或者用 CTE 分步表达可维护性会好很多。5.3 我在实际项目里踩过的坑最后聊点个人经验都是真金白银换来的。第一个坑是过度依赖 ORM 生成的 SQL。ORM 框架自动生成 join 语句很方便但它不知道你的数据分布也不知道哪张表该建什么索引。我见过一个接口的 SQL 被 ORM 拼成了五层嵌套子查询每层都带 joinSQL 本身三百多行。这种时候最好的办法是把这条 SQL 打印出来手动改写而不是继续在框架层面调参数。第二个坑是测试环境数据量太小性能问题测不出来。几百行的测试表上什么 join 都是毫秒级上线之后表变成几百万行同样的 SQL 直接超时。我的做法是在测试环境准备一份缩小版但分布接近真实的数据至少保证索引和连接算法的选择跟生产环境一致。第三个坑是改动线上 SQL 时没有回归验证。加了一个 left join结果因为 where 条件的位置问题把原本的 inner join 语义给改了数据静默变少一周后业务方才发现。从那以后我养成了一个习惯任何涉及 join 的改动都要把改动前后的结果集行数和几个关键聚合值做一次对比行数差一点都要问清楚为什么。第四个坑是索引建了但没被用上。有一次排查了半小时最后发现连接列上的索引因为列类型跟另一侧不一致完全没生效。从那之后我养成一个检查习惯EXPLAIN 出来的 key 字段如果是 NULL不管 type 看起来多正常都要回头确认连接列的类型和字符集。说到底join 这东西难的不是语法语法半小时就能学会难的是对数据分布有感觉——知道每张表大概多少行知道某个关联是一对一还是一对多知道哪个条件能把行数砍掉百分之九十九。这些东西没法从文档里抄只能靠自己一遍遍跑查询、看执行计划、对比结果慢慢积累。我现在写一个稍微复杂点的 join都会习惯性地在脑子里估算一遍每一步的行数估错了就去查查多了自然就有感觉了。
SQL JOIN 深度解析：连接算法、执行计划与索引优化
做数据库这行时间久了会发现一个挺有意思的现象面试的时候人人都能背出 inner join 和 left join 的区别可真到了线上写 SQL因为 join 写错导致数据对不上、报表翻倍、接口超时的事故依然层出不穷。原因不复杂——大多数人对 join 的理解停留在两个表拼一起这个比喻上而这个比喻恰好掩盖了真正重要的东西连接算法怎么选、驱动表是谁、on 和 where 分别在哪一步生效、一对多关系会把结果放大多少倍。这些细节决定了你的 SQL 是跑 40 毫秒还是 40 秒返回的是 100 行还是 100 万行。这篇内容就是冲着这些细节来的。我会从 join 在数据库内部的执行过程讲起把嵌套循环、哈希连接、排序合并这三种算法的适用条件说清楚然后拿一份可以自己动手复现的测试数据把几种 join 的行为差异一条条跑给你看最后落到索引设计、驱动表选择、慢 SQL 排查这些真正影响线上表现的地方。不管你是刚开始学 SQL 的新手还是已经写了几年业务查询但总觉得性能优化没抓手的人都能从里面挑到能直接用的东西。1. 为什么 join 值得单独花时间搞懂1.1 join 在数据库内部到底做了什么很多人以为 join 是数据库的一个高级功能其实它的本质非常朴素从两张表里各取一行判断它们是否满足条件满足就拼成一行输出。数据库做的事情就是把这个判断过程尽量少做、做快。以最常见的等值连接为例A JOIN B ON A.id B.a_id数据库要解决的核心矛盾是A 表有 m 行B 表有 n 行如果老老实实两两比较需要 m×n 次匹配操作。这个数字在几百行的测试表上完全看不出来但在两张千万行的表上就是天文数字。所以所有 join 优化的努力本质上都是在回答同一个问题怎么把 m×n 次比较降下来。降低的方式无非两类。一类是在 B 表的连接列上建立索引这样 A 表每取一行去 B 表里找匹配不需要全表扫一次 B 树查找就够了复杂度从 m×n 降到 m×log(n)。另一类是一次性把其中一张表的数据装进内存的哈希结构里另一张表扫一遍逐个探测复杂度降到 mn。这两种思路对应了不同的连接算法选择哪一种取决于表的大小、有没有索引、连接条件是不是等值。理解了这个前提后面所有的优化手段都能自己推导出来要么让比较次数变少要么让每次比较变快。1.2 三种连接算法的适用条件数据库里主流就三种算法MySQL、Oracle、SQL Server 的实现细节不同但思路是一致的。嵌套循环连接Nested Loop Join是最直白的一种外层表取一行内层表去找匹配找到就输出找不到就跳过然后外层取下一行。它的性能完全取决于内层表的查找效率——如果内层表的连接列有索引速度非常可观如果没有索引那就是灾难性的全表扫描反复执行。这也是为什么被驱动表的连接列必须建索引会成为一条铁律。哈希连接Hash Join的思路是先用数据量小的那张表在内存里建一张哈希表key 是连接列的值然后扫描大表每行拿连接列去哈希表里探测一次。它要求连接条件是等值条件因为哈希表没法处理范围匹配。哈希连接的优点是只扫两遍数据不依赖索引特别适合两张都很大、又都没法走索引的表。缺点是要吃内存内存放不下会退化成带磁盘临时文件的版本速度会掉一个档次。排序合并连接Merge Join则是把两张表分别按连接列排好序然后用两个游标同步推进谁小谁往前走。如果两边都已经有序比如连接列上有聚簇索引或已经排好序的子查询结果这一步几乎不需要额外开销如果没排序那排序本身的代价可能比连接还大。实际执行时选哪种是优化器根据统计信息算代价决定的。你要做的是看懂执行计划里出现的是哪一种进而判断它选得对不对。1.3 笛卡尔积是理解一切连接的起点不写 on 条件的A CROSS JOIN B或者A, B返回的就是笛卡尔积m 行乘以 n 行。100 行的表跟 100 行的表做笛卡尔积结果是 10000 行看起来还好但 10000 行的表跟 10000 行的表就是 1 亿行这种 SQL 扔到线上足以把内存打满。关键在于写上了 on 条件的 join本质上是先产生笛卡尔积再用条件筛掉不匹配的部分在逻辑层面的等价表达。实际执行时数据库当然不会真的先做笛卡尔积但这个逻辑模型能解释很多现象。比如为什么漏写关联条件会突然返回海量数据——因为条件都没了笛卡尔积被原样输出。再比如为什么三张表 join 的时候中间结果的膨胀速度会失控——假设 A 有 1000 行B 有 1000 行A 到 B 是一对多连接后变成 10000 行再跟 C 表 join如果这个 10000 行的中间结果还要跟 C 做一对多结果就是几十万行。很多人写多表 join 时只盯着最终的过滤条件完全没意识到中间结果已经膨胀了一百倍。所以我个人的习惯是写完一个多表 join先在脑子里推一遍每一步的行数变化如果某一步的行数超过最终需要的量级就说明这里可能需要提前聚合或者调整连接顺序。2. 几种 join 的语义差别与真实业务对照2.1 inner join 与 left join 的核心分界inner join 返回的是两张表都匹配上的行也就是交集。left join 返回的是左表全部的行右表匹配上就填值匹配不上就填 NULL。用业务场景说更清楚。假设要查所有用户以及他们的订单金额。用 inner join得到的结果里只有下过单的用户用 left join得到的结果里包括从没下过单的用户这些用户的订单金额列是 NULL。这两种结果对业务来说完全是两回事——前者是有订单的用户列表后者是全部用户及其消费情况。有意思的是很多线上事故就出在这里。报表同学想要全部用户写成了 inner join结果沉默用户全部消失数据看板上用户数莫名其妙少了一大截反过来运营想要本月有下单的用户写成了 left join 又没加过滤条件结果混进来一大批 NULL 行人均消费被拉低了。选哪种取决于你要的语义是只保留匹配成功的还是保留主表全集。判断方法很简单问一句主表里那些没有对应记录的行我要不要。要就 left join不要就 inner join。right join 和 left join 只是主表换了个位置实际项目里很少用因为可读性差——人的阅读顺序是从左往右把主表放在右边会让人多绕一道弯。我一般建议统一用 left join需要 right join 的时候把两张表顺序调换一下就行。2.2 full outer join 与 cross join 的适用边界MySQL 到现在也不支持 full outer join需要用 left join 和 right join 做 union 来模拟。写法大概是这样SELECT a.id, a.name, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id UNION SELECT a.id, a.name, b.amount FROM users a RIGHT JOIN orders b ON a.id b.user_id;注意这里必须用 UNION 而不是 UNION ALL因为两张表里同时匹配上的行会在两个结果集里各出现一次用 UNION ALL 会重复。这个写法的代价是两边都要扫一遍数据量大时开销不小能用其他方式表达需求就尽量别用。cross join 就是前面说的笛卡尔积听起来像是要避开的操作但它有个很实用的场景生成日期序列、数字序列这类基础数据。比如要补齐一份每天每商品的销量报表商品表跟日期表 cross join 就能造出完整的骨架再去 left join 实际销量数据空缺的日子补零。这种用法是合理且高效的前提是两张表的行数都受控。提示cross join 用错的地方通常是漏写了 join 条件而不是故意为之。写完多表查询后一定要数一遍 on 子句的个数n 张表连接应该有 n-1 个 on 条件。2.3 on 与 where 的位置决定了结果集大小这是我看过最高频的 SQL 错误没有之一。同样一段逻辑条件写在 on 后面和写在 where 后面结果完全不同。-- 写法一条件在 on 里返回所有用户未支付订单的金额为 NULL SELECT a.id, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id AND b.status 1; -- 写法二条件在 where 里等价于 inner join SELECT a.id, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id WHERE b.status 1;为什么差异这么大因为 left join 的执行逻辑是先按 on 条件去右表找匹配找不到就保留左表行、右表列全部填 NULL。写法一里b.status 1是匹配条件的一部分用户没有已支付订单就匹配不上于是保留左表行、右表列填 NULL用户还在结果里。写法二里left join 先把所有订单都关联上然后 where 对关联后的结果做过滤那些右表填 NULL 的行因为NULL 1不成立被过滤掉了左表行也跟着消失语义上就退化成了 inner join。记忆方法只有一个on 决定怎么匹配where 决定匹配完之后留下什么。只要你对右表的列在 where 里做非空过滤left join 就一定会变成 inner join。理解了这条就不会再困惑为什么我明明写了 left join结果却少了一批数据。3. 手把手跑通一组多表关联查询3.1 准备一份可复现的测试数据光看理论没用还是得自己跑一遍。下面这份数据我用过很多次结构简单但能覆盖大部分 join 场景。两张表用户和订单故意留了几个没有订单的用户也留了几个状态不同的订单。CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(32) NOT NULL, city VARCHAR(32) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), status TINYINT, KEY idx_user_id (user_id) ); INSERT INTO users VALUES (1, 张三, 北京), (2, 李四, 上海), (3, 王五, 北京), (4, 赵六, NULL); INSERT INTO orders VALUES (101, 1, 199.00, 1), (102, 1, 89.50, 1), (103, 2, 320.00, 1), (104, 2, 50.00, 0), (105, 3, 128.00, 0), (106, NULL, 66.00, 1);注意几个刻意的设计赵六没有订单用来观察 left join 保留左表的效果李四和王五各有未支付订单用来验证 on 和 where 的差别订单 106 的 user_id 是 NULL用来观察 NULL 值在连接中的表现。3.2 六条查询看清 join 的行为差异数据准备好之后把下面几条查询依次跑一遍对比结果行数比看十页文档都管用。第一条inner join用户下过单的记录SELECT a.name, b.order_id, b.amount FROM users a INNER JOIN orders b ON a.id b.user_id;结果是 5 行张三 2 行、李四 2 行、王五 1 行赵六和订单 106 都消失了。第二条left join看全部用户SELECT a.name, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id;结果 6 行多出来的那行是赵六order_id 和 amount 都是 NULL。第三条left join 加 where 过滤右表SELECT a.name, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id WHERE b.status 1;结果只剩 3 行只有已支付的订单。赵六没了李四和王五的未支付订单也没了。这条查询的语义跟 inner join 加 status 过滤完全一致但执行计划可能不一样因为优化器需要先做一次转换。第四条left join 加 on 条件SELECT a.name, b.order_id, b.amount FROM users a LEFT JOIN orders b ON a.id b.user_id AND b.status 1;结果回到 4 行赵六的 NULL 行回来了另外李四和王五的未支付订单被过滤掉但他们本人还在因为订单 103 是已支付的。第五条统计每个用户的订单数这个要特别小心SELECT a.name, COUNT(*) AS cnt, COUNT(b.order_id) AS cnt2 FROM users a LEFT JOIN orders b ON a.id b.user_id GROUP BY a.id, a.name;赵六这一行cnt 是 1cnt2 是 0。原因很清楚COUNT(*)数的是结果集的行数left join 给赵六补了一行 NULL所以算作 1COUNT(b.order_id)只统计非 NULL 值所以是 0。做用户订单数统计的时候必须用后者用前者会让所有没下单的用户都显示 1 单。第六条反向验证外键孤儿数据SELECT b.order_id, b.user_id FROM orders b LEFT JOIN users a ON b.user_id a.id WHERE a.id IS NULL;这条查询返回订单 106它的 user_id 是 NULL。这个套路在数据质量检查里非常常用用来找出子表里存在但主表里没有的脏数据。把两个表位置对调就能检查出所有类型的外键异常比写一堆 count 比对高效得多。3.3 用执行计划验证你的判断跑完查询之后在 MySQL 里加上EXPLAIN前缀再看一次重点盯三个字段。type表示访问类型从好到坏依次是 system、const、eq_ref、ref、range、index、ALL。join 场景里最常见的是 ref用到了非唯一索引和 eq_ref用到了唯一索引或主键如果出现 ALL说明被驱动表在做全表扫描这就是要优化的信号。rows是优化器估算要扫描的行数这个数字在 join 场景下容易被低估尤其是统计信息过期的时候。如果你看到 rows 是几百实际跑出来几百万先去ANALYZE TABLE更新一下统计信息。Extra字段信息量最大。出现Using join buffer (Block Nested Loop)或 MySQL 8.0 之后的Using join buffer (hash join)说明被驱动表没有可用索引正在用内存缓冲退而求其次出现Using filesort说明排序没走索引出现Using temporary说明用了临时表通常是 group by 或 distinct 引起的。一条 SQL 如果同时出现 join buffer、filesort、temporary 三个基本可以判定有优化空间。至于先优化哪个我的经验是先解决 join buffer因为它意味着数据量的放大后面两个往往会被顺带解决。4. join 性能优化的四个抓手4.1 被驱动表的连接列必须有索引这条是 join 优化的第一原则没有之一。前面说过嵌套循环连接里内层表的查找效率决定一切而内层表就是被驱动表。怎么判断驱动表是谁在 left join 里左表通常是驱动表MySQL 8.0 在外连接可以转换为内连接的情况下会重新选择在 inner join 里优化器会选结果集更小的那张表做驱动表。以A JOIN B ON A.id B.a_id为例如果 A 是被驱动表那 A.id 上要有索引如果 B 是被驱动表那 B.a_id 上要有索引。实际项目中经常遇到的情况是主键和唯一键都有索引但外键列忘了建。比如订单表的 user_id、日志表的 device_id这些列在业务查询里天天用来 join却没建索引导致每次关联都是全表扫描。加一个普通索引就能让查询从秒级降到毫秒级投入产出比极高。有一点要注意索引不只是在 where 里过滤时有用join 的连接列同样需要。很多人建索引时只考虑查询条件忘了连接条件这个盲区挺常见的。还有复合索引的顺序问题。如果连接列和过滤列经常一起出现可以考虑建复合索引把连接列放前面。比如(user_id, status)既能用于 join 匹配又能在匹配之后用 status 过滤一个索引顶两个用。4.2 驱动表选择与 join buffer 的调节驱动表选小表是基本原则原因是嵌套循环的外层循环次数直接等于驱动表行数外层少一次内层就少扫一遍。inner join 里优化器一般会自己选但它的判断基于统计信息统计信息不准的时候会选错。这时候可以用STRAIGHT_JOIN强制指定顺序不过这是最后的办法先尝试更新统计信息。left join 的顺序是语义决定的不能随便调换反过来讲如果你知道哪张表数据少把它放在左边写 left join天然就是小表驱动。当被驱动表实在没索引可用时MySQL 会用 join buffer 把驱动表的数据批量化加载到内存里再一次性去被驱动表比对减少重复扫描。这个缓冲区的大小由join_buffer_size控制默认 256KB可以适当调大。-- 查看当前值 SHOW VARIABLES LIKE join_buffer_size; -- 会话级调整测试用 SET SESSION join_buffer_size 4 * 1024 * 1024;注意join_buffer_size 是每个连接各自分配的调太大会在并发高的时候把内存吃光。生产环境建议先观察并发连接数再决定几百 MB 这种操作不要轻易尝试。而且从根本上说加索引比调大缓冲区更划算缓冲区是被迫的选择。4.3 降低参与连接的行数如果索引已经建好了速度还是上不去那要看的就不是连接本身而是有多少行参与了连接。优化方向有两个提前过滤和提前聚合。提前过滤的道理很直观。如果 A 表 1000 万行但真正需要的只有 1 万行那就先用子查询或者 CTE 把这 1 万行筛出来再 join而不是把 1000 万行全拖进连接过程。-- 不推荐先连接再过滤 SELECT a.id, b.amount FROM users a JOIN orders b ON a.id b.user_id WHERE a.city 北京 AND b.created_at 2024-01-01; -- 推荐先各自过滤再连接前提是过滤后行数明显变少 SELECT a.id, b.amount FROM (SELECT id FROM users WHERE city 北京) a JOIN (SELECT user_id, amount FROM orders WHERE created_at 2024-01-01) b ON a.id b.user_id;不过这里有个前提要判断清楚如果优化器本来就能把 where 条件下推两种写法执行计划是一样的那没必要改写反而降低了可读性。判断方法是把两条 SQL 都 EXPLAIN 一遍看 rows 估算和实际访问方式有没有区别。提前聚合解决的是另一类问题一对多连接导致结果膨胀。比如要查每个用户的订单总额直觉写法是先 join 再 group by。SELECT a.id, a.name, SUM(b.amount) AS total FROM users a LEFT JOIN orders b ON a.id b.user_id GROUP BY a.id, a.name;这段逻辑上没问题但如果用户表还跟另外几张表 join中间结果会被订单表放大很多倍。稳妥的做法是先在子查询里把订单聚合到用户粒度再 join。SELECT a.id, a.name, COALESCE(t.total, 0) AS total FROM users a LEFT JOIN ( SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ) t ON a.id t.user_id;这样参与 join 的右侧变成了每个用户一行不再膨胀。代价是子查询要单独扫一遍订单表在有索引的情况下这点开销是可以接受的。4.4 几个慢 SQL 排查实录说几个我实际处理过的案例都是 join 相关的典型问题。案例一报表查询从 40 毫秒变成 8 秒。改动只是加了一个 left join被驱动表的连接列没索引。EXPLAIN 一看type 是 ALLrows 是 80 万。加上索引之后回到 40 毫秒。这个案例说明一件事新加 join 一定要跑一次执行计划别想当然。案例二数据行数对不上翻了三倍。排查发现用户表跟地址表 join一个用户有多个地址本来以为是一对一。改成先对地址做聚合取默认地址再去 join行数恢复正常。这种问题不会报错只会静默地给出错误结果最危险。案例三两个大表 join 跑不出来。两边各百万行连接列都没法走索引。改写成先各自做条件过滤把行数压到几千再 join从跑不出来变成 200 毫秒。案例四索引明明建了却用不上。查看发现两张表的连接列字符集不一致一张是 utf8一张是 utf8mb4导致隐式转换索引失效。统一字符集后问题解决。这类问题很隐蔽只能靠仔细核对表结构。案例五连接列类型不一致。一边是 INT一边是 VARCHARMySQL 会把 VARCHAR 转成数字索引同样失效。这类问题在建表阶段就该避免事后修改成本很高因为要改数据类型或者加上转换函数但加了函数索引又用不上。实操心得遇到 join 慢排查顺序我一般是这样——先 EXPLAIN 看被驱动表是不是 ALL是就补索引不是就看 rows 估算跟实际差多少差得多就 ANALYZE TABLE还不行就看连接列的类型和字符集是否一致最后才考虑改写 SQL 或者调整参数。5. 常见问题速查与避坑心得5.1 常见问题速查表现象大概率原因处理方式left join 结果比预期少where 里过滤了右表列把条件挪到 on 里或改用 inner join结果行数成倍膨胀一对多连接未聚合先按主表粒度聚合再 join没下单的用户统计出 1 单用了 COUNT(*)改成 COUNT(右表主键)明明有索引却全表扫描类型或字符集不一致统一两侧列的类型与字符集连接条件匹配不上 NULLNULL 不参与等值比较用 IS NULL 或 COALESCE 预处理执行计划出现 join buffer被驱动表连接列无索引补索引索引优先于调参数多表 join 越来越慢中间结果膨胀减少参与连接的行数或调整顺序5.2 几个容易被忽略的细节NULL 在连接里的表现值得单独说一句。NULL NULL的结果不是 true而是 unknown所以两张表里连接列都是 NULL 的行永远匹配不上。如果你的数据里连接列可能为空要么在建表时就设成 NOT NULL要么在连接条件里显式处理别指望数据库帮你兜底。多表 join 的书写顺序会影响可读性也会影响优化器的选择空间。我的习惯是把数据量最小的表放在最左边然后按关联关系依次往右写每个 join 都紧跟它要关联的那张表。这样别人读你的 SQL 时能顺着数据流一路看下去不用来回跳。还有一个细节是别名。给每张表起了别名之后所有列都要带别名前缀哪怕这个列名在两张表里不重名。原因不是为了好看而是等以后有人往查询里加了第三张表恰好有个同名列那时候再回头加前缀成本比一开始就加高得多。关于 join 的列数也要控制。有些查询一口气 join 七八张表每张表都取一堆列。这种 SQL 一旦某张表的数据量上来整体就会失控而且很难定位是哪一段出的问题。能拆成两步的就拆开中间结果落到临时表或者用 CTE 分步表达可维护性会好很多。5.3 我在实际项目里踩过的坑最后聊点个人经验都是真金白银换来的。第一个坑是过度依赖 ORM 生成的 SQL。ORM 框架自动生成 join 语句很方便但它不知道你的数据分布也不知道哪张表该建什么索引。我见过一个接口的 SQL 被 ORM 拼成了五层嵌套子查询每层都带 joinSQL 本身三百多行。这种时候最好的办法是把这条 SQL 打印出来手动改写而不是继续在框架层面调参数。第二个坑是测试环境数据量太小性能问题测不出来。几百行的测试表上什么 join 都是毫秒级上线之后表变成几百万行同样的 SQL 直接超时。我的做法是在测试环境准备一份缩小版但分布接近真实的数据至少保证索引和连接算法的选择跟生产环境一致。第三个坑是改动线上 SQL 时没有回归验证。加了一个 left join结果因为 where 条件的位置问题把原本的 inner join 语义给改了数据静默变少一周后业务方才发现。从那以后我养成了一个习惯任何涉及 join 的改动都要把改动前后的结果集行数和几个关键聚合值做一次对比行数差一点都要问清楚为什么。第四个坑是索引建了但没被用上。有一次排查了半小时最后发现连接列上的索引因为列类型跟另一侧不一致完全没生效。从那之后我养成一个检查习惯EXPLAIN 出来的 key 字段如果是 NULL不管 type 看起来多正常都要回头确认连接列的类型和字符集。说到底join 这东西难的不是语法语法半小时就能学会难的是对数据分布有感觉——知道每张表大概多少行知道某个关联是一对一还是一对多知道哪个条件能把行数砍掉百分之九十九。这些东西没法从文档里抄只能靠自己一遍遍跑查询、看执行计划、对比结果慢慢积累。我现在写一个稍微复杂点的 join都会习惯性地在脑子里估算一遍每一步的行数估错了就去查查多了自然就有感觉了。