先说一个我经常遇到的场景。你在维护一个后台系统订单列表页要把订单号、下单人、商品名、金额、下单时间一次性查出来可这些字段分散在用户表、订单表、商品表三张表里。最常规的写法就是一条JOIN语句但恰恰是这条语句我见过太多人栽跟头查出来的数据对不上、慢得像蜗牛、一翻页就卡死。这篇笔记我把MySQL联合查询从语法到执行原理、从地板级入门到面试高频题完整地捋一遍。如果你正在学SQL、被一句JOIN折磨过、或者马上要出去面试读这一篇就够了。1. 联合查询的应用场景与设计思路先搞清楚为什么需要JOIN1.1 拆开存、合起来查范式设计必然带来的查询需求很多人第一次接触JOIN时会有一个困惑为什么数据库非要把数据拆到好几张表里搞得我查个数据还要连接来连接去直接一张大表全塞进去不香吗这个问题得从一个经典的更新异常说起。假设订单表里直接存了用户姓名、商品名称那么当用户改了昵称或者某个商品改了名历史订单里的冗余字段全部要跟着改。漏改一处报表数据就对不上这就是典型的更新异常。所以关系型数据库设计时强调规范化把数据拆分到职责单一的表里用户表只管用户商品表只管商品订单表只管订单表之间通过外键字段比如user_id、product_id来关联。拆是拆开了但业务查询往往需要跨表数据。比如后台要看到“哪个用户买了哪件商品”就必须把三张表重新“拼”回来。这个拼接动作就是联合查询。用个生活化的类比。图书馆管理一本书图书基本信息是一张卡片借阅记录是另一张卡片两张卡共用同一个书编号。你光看借阅卡只能知道谁借过这本书不知道作者是谁光看书目卡也不知道书现在在谁手上。想了解全貌你必须拿书编号去对两张卡把信息合并起来。数据库里的表就是这些卡片JOIN就是那个对编号的动作。范式解决的是“怎么写数据不出错”JOIN解决的是“怎么把拆开的数据读回完整视图”两者是一体两面缺一不可。1.2 横向联合与纵向联合JOIN 和 UNION 不要混为一谈联合查询这个词在MySQL里可以分成两个完全不同的方向很多人一开始就搞混了。第一个方向是把两张表的“列”左右拼接起来。比如学生表和班级表学生表有1班、2班的学生班级表有班级编号和班主任连接之后一行记录里既有学生信息又有班级信息列变多了。这一类是JOIN系列INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN。第二个方向是把两张表的“行”上下拼接起来。比如一班成绩单和二班成绩单两张表的列结构相同合并之后总行数等于两个班人数之和。这一类是UNION系列UNION、UNION ALL。区分这两个方向是学习联合查询的第一个关键分水岭。你面试时如果连JOIN和UNION都说不清后面基本就不用聊了。1.3 什么场景下最常用订单、权限、报表我说几个最常见的真实业务场景你对照着想想是不是都得用JOIN。订单分析报表订单表连接用户表拿姓名和城市连接商品表拿品类和价格有时候还要连接门店表、渠道表。这种多表连接是数据报表的家常便饭。权限系统经典的RBAC模型用户表、角色表、用户角色关系表、权限表至少要连接两次才能拿到“某个用户拥有哪些权限”。日志关联分析埋点日志表通常只记录用户ID和商品ID要把日志翻译成人话就得连接用户表和商品表否则日志就是一堆数字ID谁也看不懂。还有一种情况也要注意如果你发现某张表里存了另一个表的全部冗余字段而且业务上还能接受往往不是设计优良而是图省事留下的技术债。这种表一旦数据量上来同步逻辑会非常痛苦。正确做法就是拆分后查询时通过JOIN把数据拼回来。2. 从语法到执行原理JOIN 的七种打开方式2.1 基础连接语法速览INNER、LEFT、RIGHT、CROSS为了讲清楚我先建两张最简单的演示表班级表和学生表。CREATE TABLE classes ( id INT PRIMARY KEY, name VARCHAR(20) ); CREATE TABLE stu ( id INT PRIMARY KEY, name VARCHAR(20), class_id INT ); INSERT INTO classes VALUES (1, 一班), (2, 二班), (3, 三班); INSERT INTO stu VALUES (1, 小明, 1), (2, 小红, 1), (3, 小刚, 2), (4, 小丽, NULL);这里学生表我故意留了一个class_id为NULL的小丽后面讲NULL陷阱时还要用它。INNER JOIN内连接只返回两边都匹配得上的记录SELECT s.name, c.name AS class_name FROM stu s INNER JOIN classes c ON s.class_id c.id;结果就是小明、小红、小刚三行每个学生都能在班级表里找到对应班级。小丽因为class_id是NULL匹配不上被丢掉了。内连接的关键词就是“两家都要有”缺一边就不给结果。LEFT JOIN左连接左表的记录全部保留右表能匹配就匹配匹配不上就用NULL填充SELECT s.name, c.name AS class_name FROM stu s LEFT JOIN classes c ON s.class_id c.id;结果是四行小明、小红、小刚、小丽都在。小丽那一行的class_name是NULL。左连接的核心语义是“以左表为基准右边的尽量补补不上就留空”。RIGHT JOIN右连接语义和LEFT JOIN完全对称以右表为基准SELECT s.name, c.name AS class_name FROM stu s RIGHT JOIN classes c ON s.class_id c.id;结果是三行一班、二班、三班都出来了三班没有学生所以s.name是NULL。这里用RIGHT JOIN能看出“补全右表”的效果。实际项目里RIGHT JOIN用得很少因为想保留哪张表把它放在左边写LEFT JOIN就行可读性更好。CROSS JOIN交叉连接返回两表的笛卡尔积。比如4个学生、3个班级CROSS JOIN结果就是12行每个学生和每个班级组合一次。这种写法99%的情况下是灾难只有少数场景有用比如批量生成测试数据、或者用数字表做日期序列补全。MySQL还有一个坑官方不支持FULL JOIN。很多从SQL Server或PostgreSQL转过来的朋友会一时间找不到北。需要用“全连接”效果时只能用LEFT JOIN和RIGHT JOIN配合UNION来模拟这在第6章会给出具体写法。2.2 最容易答错的点ON 和 WHERE 的过滤时机ON和WHERE的区别是JOIN问题里最经典、也最容易被忽略的考点实际开发中写错的人一大片。先看一个具体例子。我用之前的订单表和用户表。用户表里有张三、李四、王五三个人订单表里有四笔订单张三两笔一笔499、一笔1599李四一笔1299王五一笔499。第一种写法把金额过滤条件放在ON里SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.amount 1000;结果是什么张三、李四、王五三个用户全都出现在结果里。张三匹配到了1599的订单李四匹配到了1299的订单王五没有大于1000的订单但因为他左表用户的身份仍然在结果里金额字段显示NULL。第二种写法把金额过滤条件放在WHERE里SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.amount 1000;结果完全不同王五消失了只剩张三和李四。因为WHERE是在两张表连接完成之后才执行的它会把不满足条件的整行删掉包括原本因为左连接而保留的用户记录。理解这个差异核心是记住执行顺序ON先执行WHERE后执行。ON里面的额外条件只影响“右侧表要不要拼进来”左表的行无论如何都保留WHERE里的条件影响的是“最终结果集的哪些行存在”。用个生活化的说法ON条件是入场资格审查审核不通过的人站在门外但不影响你这家店开门营业WHERE条件是营业后的清场条件不满足的人连人带店一起清掉。很多线上数据查出来“少了几行”就是因为在LEFT JOIN里把过滤条件随手写进了WHERE把左表原本要保留的行也误杀了。2.3 USING 与 NATURAL JOIN省事但要小心的写法连接条件如果两边列名完全相同可以简写成USING。比如学生表有class_id班级表也有class_idSELECT s.name, c.name AS class_name FROM stu s LEFT JOIN classes c USING (class_id);USING语法等价于ON s.class_id c.class_id但结果集里只保留一个class_id列不会出现两个同名列。对于列名统一的表这种写法确实简洁。但USING有一个明显的限制要求两边表必须存在同名列。如果两边列名不同比如一个叫class_id、一个叫cidUSING就写不了只能老老实实用ON。更需要注意的是同名列必须语义相同才敢用USING要是两个表的同名列分别代表不同含义比如一个是创建人编号、一个是部门编号你写USING(id)就酿成大祸了。NATURAL JOIN则更激进它会自动找出两张表的全部同名列作为连接条件。我强烈不建议在生产环境用它。原因很简单你永远不知道哪一天有人给表加了个同名字段一条NATURAL JOIN的语义可能悄悄改变查询结果直接错乱。这种隐式依赖太危险。2.4 MySQL 底层怎么执行 JOINNLJ 与 BNL搞清楚JOIN语法后应该再看一眼MySQL底层是怎么跑的这对于排查慢SQL有直接帮助也是面试能拉开差距的地方。最基础的算法叫嵌套循环连接英文缩写NLJNested Loop Join。你可以把它想象成两层for循环MySQL先选择一张表作为驱动表遍历驱动表的每一行然后拿着这一行的连接字段去被驱动表里查找匹配的记录。外层循环每走一步内层就查一次。这个模式的关键在于内层查找走不走索引效率天差地别。如果被驱动表的连接字段有索引每次内层查找就是一次B树定位速度很快如果没有索引MySQL就得把被驱动表整个扫一遍在外层每一行都做一次全表扫描这个代价是灾难级的。在没有索引的情况下MySQL通常会改用另一种算法Block Nested-Loop Join简称BNL。它的思路是把驱动表的数据分块加载进内存里的join_buffer然后扫描被驱动表的每一行在内存中快速比对。这样虽然还是扫描被驱动表但减少了对驱动表的重复扫描和磁盘IO。BNL在EXPLAIN里很容易认出来Extra列会出现Using join buffer (Block Nested Loop)。看到这几个字第一反应就是“被驱动表没走索引”。第5章我会详细讲排查链路这里先记住这个特征。你可能会问那为什么业内都在强调“小表驱动大表”因为NLJ的外层循环次数等于驱动表的行数如果驱动表有100万行就算被驱动表有索引也要发起100万次索引查找反过来用10行的小表做驱动只需要发起10次查找。这个差距是数量级的一点都不玄学。3. 动手实战从订单列表需求写出一条可落地的三表连接3.1 建表与造数先用小数据集验证结果纯讲语法容易飘我们来玩一个具体需求。假设后台有一个订单列表页需要展示订单ID、下单用户名、商品名称、金额、下单时间并且支持按商品分类筛选、按下单时间倒序。这是后台系统最普通不过的查询任何电商项目都会遇到。为了演示索引的作用我先故意不给orders表建辅助索引让你看看未优化版本的执行计划长什么样然后再加上索引做对比。-- 用户表 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, city VARCHAR(50) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 商品表 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, category VARCHAR(50) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 订单表暂时不建 user_id 和 product_id 的索引 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入几条演示数据INSERT INTO users (name, city) VALUES (张三, 北京), (李四, 上海), (王五, 广州); INSERT INTO products (name, price, category) VALUES (机械键盘, 499.00, 外设), (显示器, 1299.00, 外设), (人体工学椅, 1599.00, 办公家具); INSERT INTO orders (user_id, product_id, amount, create_time) VALUES (1, 1, 499.00, 2024-10-01 10:00:00), (1, 3, 1599.00, 2024-10-02 11:30:00), (2, 2, 1299.00, 2024-10-03 09:15:00), (3, 1, 499.00, 2024-10-04 14:20:00);先花一分钟用肉眼看一遍数据张三买了一台键盘和一把椅子李四买了一台显示器王五也买了一台键盘。谁是“外设”分类键盘和显示器都是外设椅子不是。按照需求最后应该返回3行王五的键盘订单、李四的显示器订单、张三的键盘订单按时间倒序排列。这种“先自己手算期望结果”的习惯很重要写SQL之前先知道正确答案写完才知道自己写没写对。3.2 写出第一版三表连接查询直接上第一版SQLSELECT o.id, u.name AS user_name, p.name AS product_name, o.amount, o.create_time FROM orders o INNER JOIN users u ON o.user_id u.id INNER JOIN products p ON o.product_id p.id WHERE p.category 外设 ORDER BY o.create_time DESC;这条SQL的思路很清晰订单表作为事实表居中左连用户表拿用户名右连商品表拿商品名最后在外层过滤商品分类并排序。执行结果如下iduser_nameproduct_nameamountcreate_time4王五机械键盘499.002024-10-04 14:20:003李四显示器1299.002024-10-03 09:15:001张三机械键盘499.002024-10-01 10:00:00和我们手算的结果完全一致。这里INNER JOIN用得没问题订单表是事实表每一行都必须有合法的用户和商品没有哪个订单会想“我的用户查不到也不影响展示”。3.3 用 EXPLAIN 检查执行计划并建立索引SQL写对了但性能怎么样用EXPLAIN看执行计划EXPLAIN SELECT o.id, u.name, p.name, o.amount, o.create_time FROM orders o INNER JOIN users u ON o.user_id u.id INNER JOIN products p ON o.product_id p.id WHERE p.category 外设 ORDER BY o.create_time DESC;MySQL 8.0的输出大概有这个几个关键列id、select_type、table、type、possible_keys、key、rows、Extra。重点看type这一列它是连接类型的核心指标从最优到最差大致是system const eq_ref ref range index ALL。ALL就是噩梦级的全表扫描。在我的演示环境里这条SQL的执行计划会显示orders表typeALLpossible_keys为空rows4说明MySQL要从头到尾扫描整个订单表。users表和products表则是eq_ref因为它们是通过主键连接的每次内层查找都是点查效率最高。看到这个结果心里要有一个判断小表数据量只有4行全表扫描确实无所谓但如果orders表有100万行呢每查一次就把订单表从头扫一遍谁也顶不住。所以真正的问题是被驱动表的连接字段没有索引。修复方法很简单ALTER TABLE orders ADD INDEX idx_user_id (user_id), ADD INDEX idx_product_id (product_id);加上索引后再跑EXPLAINorders表仍然是typeALL因为订单表作为驱动表本来就要把订单数据读出来但users表和products表会稳定地走eq_ref每次内层查找用主键索引直接定位。这里有一个经常被误解的知识点不是“JOIN的两边都建索引”而是“被驱动表的连接字段必须有索引”。谁是被驱动表取决于执行计划选谁做驱动你需要EXPLAIN看清楚。更关键的一点是当orders表数据量变大、统计信息更新后优化器可能会改变驱动顺序例如让users过滤后的少量用户作为驱动表去驱动orders表此时orders的idx_user_id就派上用场了。所以连接字段两侧尤其是外键侧索引基本是必建的。4. 性能调优实战让 JOIN 从几十秒回到毫秒级4.1 小表驱动大表不是口号是优化准则前面说过嵌套循环连接的代价和驱动表的行数强相关。假设A表过滤后剩100行B表过滤后剩100万行用A驱动B就是循环100次每次在B的索引上做一次快速查找反过来用B驱动A就是循环100万次。这个差距你感受一下。MySQL的优化器通常会基于表统计信息选择代价更低的驱动顺序大多数时候是靠谱的。但优化器也不是万能的比如连接字段的数据分布严重倾斜、统计信息过期、或者某些复杂查询条件下推失败就可能选错驱动表。遇到这种情况可以用STRAIGHT_JOIN强制指定连接顺序SELECT ... FROM users u STRAIGHT_JOIN orders o ON o.user_id u.id ...STRAIGHT_JOIN会强制按照SQL里表的书写顺序来驱动。注意这是一把双刃剑用之前你必须确定自己清楚数据分布否则可能越强制越慢。我建议先把EXPLAIN和实际数据分布看明白了再动手不要为了炫技乱加。还有一个辅助判断EXPLAIN里第一行出现的表就是驱动表后面跟着的都是被驱动表。你想让哪张小表当驱动就看它是不是在第一个位置。4.2 连接字段类型与字符集必须对齐这是线上慢SQL的高发原因之一而且非常隐蔽。表面看SQL写得没问题EXPLAIN却显示索引没走。最典型的两个坑类型不一致。比如一张表的user_id是INT另一张表是VARCHAR。连接时MySQL会对其中一边做隐式类型转换。一旦转换发生在索引列上索引就失效了。举个例子ON a.user_id b.user_code如果a.user_id是INT、b.user_code是VARCHARMySQL可能把b.user_code转成数字再比较导致b表无法使用索引。字符集或排序规则不一致。在MySQL里两张表连接字段的字符集必须兼容否则即使有索引也可能用不上甚至直接报错“Illegal mix of collations”。常见的是旧库用utf8mb4_general_ci新库用utf8mb4_0900_ai_ci两边连不上索引。我最开始接手老项目时就碰到过两个库字符集不一致一个筛选字段始终走不了索引排查了好久才发现。实操建议建表时统一数据库字符集为utf8mb4排序规则统一主键和外键字段必须有相同的类型和长度。用SHOW FULL COLUMNS FROM 表名可以一键查看每列的字符集和排序规则是一个排查利器。4.3 深分页场景用延迟关联别让 JOIN 拖垮翻页后台订单列表翻到第5000页页面加载开始变慢这是很多人都经历过的事。直接原因通常是这条SQLSELECT o.id, u.name, p.name, o.amount, o.create_time FROM orders o JOIN users u ON o.user_id u.id JOIN products p ON o.product_id p.id ORDER BY o.create_time DESC LIMIT 100000, 20;问题出在LIMIT 100000, 20MySQL要先排序然后跳过前100000行再取20行。在这个过程里三张表连接后的完整中间结果可能非常大排序、临时表、回表全都压在一张巨大的中间结果上。延迟关联延迟连接的思路是先在最窄的地方定位出需要的主键ID再回表补全字段。改写如下SELECT o.id, u.name, p.name, o.amount, o.create_time FROM ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20 ) tmp JOIN orders o ON o.id tmp.id JOIN users u ON u.id o.user_id JOIN products p ON p.id o.product_id ORDER BY o.create_time DESC;内层子查询只查订单ID和排序字段尽量用覆盖索引完成排序和分页外层再拿这20个ID回到原表补齐所有字段。这样需要回表的只有20行而不是中间结果里的几十万行。很多慢查询优化都是这个套路重点在于“先把要取的行缩小到最小集合再放大”。4.4 多表串行连接时先缩结果集三表连接实际上包含两个连接步骤第一步先连接A和B生成中间结果第二步再拿中间结果连接C。中间结果越大第二步就越慢。所以优化多表连接的一个核心思路是在连接之前让每张表都尽量通过自己的WHERE条件把行数压到最小。举例来说商品表有一个筛选条件category外设如果MySQL能把条件下推到扫描商品表的阶段那么商品表实际上只有两行参与连接中间结果瞬间小很多。MySQL 8.0的优化器在这方面做得不错会自动做条件下推。但你在写SQL时也要有意识地配合优化器把每张表的独立过滤条件写在靠近该表的位置不要所有条件都堆在最后一层WHERE里。写完后用EXPLAIN验证一下每张表的rows值如果某张表rows特别大而它的过滤条件明明很强就要考虑是不是没有下推成功或者少了索引。另外一次连接太多表也要克制。MySQL在理论上允许连接61张表但对业务系统来说超过4到5张表的连接可读性和性能都会急剧恶化。遇到复杂的多表场景要么拆成临时表/派生表逐层处理要么引入OLAP类的分析引擎别让OLTP数据库去硬扛重型报表。5. 常见问题与排查技巧实录我踩过的 JOIN 坑5.1 笛卡尔积爆炸少写了条件表越大越致命现象查询返回的行数远大于预期例如两个100万行的表连接后返回了100亿行数据库直接被打爆。原因通常是忘了写ON条件或者ON里只写了部分关联字段。有时候更隐蔽ON只给了部分条件。比如订单和订单明细表理论上应该用订单ID行号两个字段关联你只写了订单ID结果每个订单会关联出所有明细行的组合。排查方法很简单肉眼检查ON条件再用EXPLAIN看rows如果两个表的rows相乘与你实际返回行数吻合基本就是笛卡尔积了。写JOIN之前先想清楚两个表的粒度关系是1:1、1:N还是N:M这是防止笛卡尔积的第一道防线。5.2 NULL 值陷阱LEFT JOIN 后加 IS NULL 到底在查什么“查所有没下过单的用户”最常规的写法是SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id u.id WHERE o.id IS NULL;思路没错左连接后没匹配到订单的用户右边字段全为NULL用IS NULL就能筛出来。但这里有个隐患如果orders表本身存在NULL值列比如o.remark是NULL你用WHERE o.remark IS NULL去筛就会把明明有订单但备注为空的用户也捞出来造成逻辑错误。更让人头疼的是LEFT JOIN后对右表做非NULL过滤。很多人写出这种SQLSELECT u.* FROM users u LEFT JOIN orders o ON o.user_id u.id WHERE o.amount 0;从语义上讲WHERE o.amount 0会把所有NULL行过滤掉于是LEFT JOIN的“保留左表”特性完全失效这条SQL的实际效果退化成INNER JOIN。必须记住一条铁律对LEFT JOIN的右表列做任何过滤都可能威胁左表的行保留特性。如果你确实想保留左表所有行该放ON的过滤就放ON该放WHERE的业务过滤要想清楚是否接受左表行被删。5.3 一对多连接导致结果翻倍用户表一条记录对应订单表多条记录。当你LEFT JOIN订单表时用户信息会随着订单数量重复出现N次。这不是SQL错误而是粒度改变了结果集从“一个用户一行”变成了“一个订单一行”。如果业务上想看到每个用户的订单总金额正确做法是先把订单表按用户聚合再连接SELECT u.name, o.order_cnt, o.total_amount FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) o ON o.user_id u.id;这样每个用户仍然只有一行订单统计信息通过派生表预先算好。还有一种常见误操作发现结果行数变多了直接给查询加上DISTINCT。这种做法非常危险DISTINCT只能去掉完全相同的整行如果联结膨胀导致同一主表记录出现在不同上下文中DISTINCT往往会掩盖真正的逻辑错误。看到“需要加DISTINCT才能查对”的SQL第一反应应该是检查连接粒度而不是高兴地加个去重了事。5.4 字符集不一致导致索引失效我在4.2节提过这个问题这里再补充一个实际案例。有一年我排查一个统计报表慢查询EXPLAIN显示一张表始终是typeALL但明明连接字段有索引、类型也是INT就很奇怪。后来用SHOW FULL COLUMNS一查发现两边表的排序规则一个是utf8mb4_general_ci另一个是utf8mb4_unicode_ciMySQL为了保证比较的正确性无法直接使用索引。把两张表的排序规则统一之后执行计划立即变成了ref查询从几秒降到几十毫秒。字符集问题还容易在数据库迁移、导入导出之后冒出来。建议在建表规范里就统一固定字符集比如全库utf8mb4、排序规则统一成一套并用代码规范或数据库管理工具做检查从源头杜绝这类问题。5.5 慢 JOIN 的排查链路如果你遇到一个慢到离谱的JOIN查询我建议按下面这个顺序排查第一步开启慢查询日志先确认到底哪条SQL慢以及它每次执行的耗时。第二步对慢SQL执行EXPLAIN按type、rows、Extra三列快速判断。type达到index甚至ALL说明扫描行数过大。第三步看Extra是否出现Using join buffer (Block Nested Loop)如果出现基本可以断定被驱动表的连接字段没有索引。第四步看是否出现Using filesort或Using temporary说明排序或分组合并的代价压在临时表上。第五步回到表结构检查连接字段的索引、类型、字符集是否都对得上。我自己处理过一次典型case一个订单列表查询跑了1.8秒EXPLAIN发现orders表Extra列出现Using join buffer被驱动表全表扫描。给连接字段加上索引后查询降到40毫秒左右。差别就是这么大原因也很纯粹BNL算法在数据量起来以后内存里的比对开销非常大远不如直接在B树里做点查。6. 面试中的联合查询高频题与作答思路6.1 JOIN 家族区别用集合概念十秒说清面试官问“JOIN家族的区别”如果你从SQL语法开始背容易又长又乱。我提供一个特别清晰的思路用集合类比。假设A是用户表值集合为{1,2,3}B是订单表匹配的集合为{2,3,4}。INNER JOIN取交集结果是{2,3}。LEFT JOIN取A全部集合加上与B的交集结果是{1,2,3}其中1没有匹配B。RIGHT JOIN取B全部集合加上与A的交集结果是{2,3,4}其中4没有匹配A。FULL JOIN取并集结果是{1,2,3,4}MySQL需要UNION模拟。这样答思路清晰面试官能几秒钟看出你是真懂还是背概念。最好再补一句实际工作中RIGHT JOIN用得少要保留右表就把表换到左边用LEFT JOINSQL可读性更好。6.2 LEFT JOIN 的 ON 与 WHERE 优先级题这道题我在2.2节已经详细讲过这里从面试角度再总结一次作答模板。先讲执行顺序ON先执行WHERE后执行。再讲语义差异LEFT JOIN时ON里的过滤条件只决定“右侧表哪些记录参与拼接”不影响左表记录的保留WHERE里的过滤条件作用于连接后的完整结果集会让左表记录被删除。最后落一个结论凡是希望“左表全保留右边按条件补数据”的过滤写进ON凡是业务上明确要过滤最终语义结果的写进WHERE。别混着写更别图省事把业务过滤一股脑写进ON里。6.3 UNION 与 UNION ALL 的性能取舍面试里另一个高频点。UNION会对合并结果去重因此MySQL可能需要使用临时表并执行排序或哈希去重代价不小UNION ALL只是简单拼接直接追加结果完全不去重。作答时可以补一个实用建议当业务上能确定两个查询结果不会出现重复行时一律用UNION ALL。比如从订单表和退款表各取前20条两者主键体系可能完全不同大概率没有重复用UNION ALL性能更稳。顺带一提模拟FULL JOIN也是用UNION实现的SELECT s.name, c.name AS class_name FROM stu s LEFT JOIN classes c ON s.class_id c.id UNION SELECT s.name, c.name AS class_name FROM stu s RIGHT JOIN classes c ON s.class_id c.id;这里一定要用UNION而不是UNION ALL因为两个查询会重复覆盖交叠部分UNION的去重正好把交叠区合并成一份。6.4 与事务和锁的边界JOIN 加锁要注意什么JOIN本身和事务、锁是两个话题但它们会在一个场景碰撞在事务里对关联查询加排他锁。比如SELECT * FROM users u JOIN orders o ON o.user_id u.id WHERE u.id 1 FOR UPDATE;这条语句不仅会锁住users表id1的记录还会锁住orders表里user_id1相关的订单记录。如果你在多个事务里一个事务先锁users再锁orders另一个事务先锁orders再锁users就可能形成锁等待严重时触发死锁。InnoDB检测到死锁后会将其中一个事务回滚但回滚本身也是损耗。实践层面的建议多表加锁的场景事务里访问表的顺序要保持一致比如统一“先用户表再订单表”同时尽量缩短事务时间。另外要知道FOR UPDATE的加锁范围与WHERE条件扫描到的记录相关过滤条件越精确加锁范围越小。收个尾我的一点个人经验写了这么多最后说点掏心窝的话。我带过不少新人也看过无数条线上慢SQL发现凡是JOIN写得熟练的人脑子里都有一张“行数变化图”主表多少行连接后大概多少行哪些行会消失哪些行会翻倍心里大概有数。写SQL之前先用几十条数据预判一下结果远比写完再瞎试高效得多。还有一个习惯值得养成凡是写LEFT JOIN我默认把业务过滤条件放WHERE把只影响“右边是否匹配”的条件放ON并且在心里过一遍两种写法的结果差异再决定最终怎么写。有些坑真的只在踩过之后才记得住。希望这篇笔记能帮你少踩几个。