MySQL复合查询详解：多表JOIN、自连接与子查询实战

我们天天写SQL尤其在业务系统里折腾数据的时候绝大多数查询都不是“SELECT * FROM 表”就能收工的。两张表、三张表甚至更多表的数据搅在一起要按某个条件去匹配要按层级关系去递归找上下级还要嵌套一层一层子查询去过滤目标集合这套玩法在MySQL里有一个统一的名字——复合查询。面试的时候它是高频考点工作里它是你写报表、做统计、修数据的基础功。这篇就把多表查询、自连接和子查询放在一起用最贴近实际开发的例子把它们掰开揉碎讲清楚你看完不止能应付面试回到工位上写复杂查询也会顺手很多。文章里的示例统一用一套员工表和部门表演示后面也会顺带讲一讲订单类的多对多场景。所有案例你都可以直接复制到自己的测试库里跑一遍一边执行一边看结果比干读文字要牢靠得多。1. 多表查询的核心思路先把“关系”想明白再动手写SQL1.1 为什么你不能只查一张表数据库设计的时候有个核心动作叫规范化目的是减少数据冗余。比如员工表里存了部门编号deptno但它不会把部门名称、部门地址也一起存进去因为同一份部门信息在员工表里重复几百次既浪费空间又容易在维护时产生不一致。所以部门名称会单独放在dept表里员工表只存一个关联用的外键。但业务查询的时候用户不会跟你讲“我要的是deptno10这个编号”他要的是“研发部”要的是部门负责人名字。这时候就必须把两张表通过连接条件拼起来。多表查询的本质是把“分散存储的规范化数据”在查询时重新“组装”成一张对业务有意义的宽表。理解了这个动机你就知道为什么几乎所有正式项目里多表JOIN都是家常便饭了。1.2 弄清楚笛卡尔积你就弄懂了一半的JOIN两张表做连接MySQL会先做一步“排列组合”。员工表14行部门表4行如果不写连接条件直接FROM emp, dept结果是14×456行。这个过程叫笛卡尔积。第1个员工会跟4个部门各配一次第2个员工也会跟4个部门各配一次依此类推。实际业务里这种全组合大多数是噪音因为员工和部门之间并不是两两都有关系。一个员工只属于某一个部门你真正想要的是“emp.deptno等于dept.deptno”的那些配对。所以写多表查询最重要的一步就是找到表与表之间的关联字段把它作为连接条件。忘了写条件的笛卡尔积在数据量大的时候是灾难我见过有人把5万行的会员表和3万行的订单表直接FROM不带WHERE查询跑到数据库直接卡死CPU飙满最后只能kill进程。这不是夸张是真实事故。1.3 内连接与外连接到底该用哪个老话讲“INNER JOIN是求交集LEFT JOIN是以左表为基准”但这种解释太抽象。我习惯用一套更实在的判断标准你查询结果的“主体”是哪张表你是否允许这张表里有记录在另一端找不到匹配。内连接INNER JOIN两个表都必须满足连接条件结果只包含两边能对上的数据。查“员工姓名和对应部门名称”时如果某个员工deptno在部门表里不存在这个员工就不会出现在结果里因为他的部门信息是缺失的。这个行为在数据完整性强、都设了外键约束的库里通常没问题但一旦某张表有脏数据、孤儿记录内连接就会悄悄把记录丢掉。左连接LEFT JOIN左表的记录必须全部保留右表匹配不上就补NULL。典型场景是查“所有客户的订单情况”即使某个客户一单都没下过你也希望客户名字出现在结果里订单字段显示NULL而不是把客户直接滤掉。右连接就是反过来实际工作中用得少你完全可以通过调换表的顺序来避免写RIGHT JOIN这样自己也容易理解别人读你的SQL也不至于绕弯子。举一个非常实际的例子。假设要查每个员工的姓名和它的部门名称这两张表是emp和dept你会写SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno;如果换成LEFT JOIN结果就会包含那些deptno没有在dept表匹配上的员工。关于两者区别很多人在面试时被问“你什么时候用LEFT JOIN而不是INNER JOIN”最好的答案永远是围绕“主体表记录是否允许缺失匹配”来回答。1.4 多表连接的条件写法ON与WHERE的边界感SQL92之后主流写法是用JOIN语法把连接条件放进ON子句把过滤条件放进WHERE子句。比如查“部门编号为10和20的员工及其部门名称”SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno WHERE e.deptno IN (10, 20);ORDER BY最好也放在最后头。有一个新手很容易踩的坑是ON和WHERE位置搞混。对内连接来说把过滤条件写进ON里或者WHERE里结果是一样的因为内连接只认匹配到的行。但放到LEFT JOIN里这两个位置的结果就完全不同了。LEFT JOIN时如果把右表的过滤条件写进WHERE相当于把LEFT JOIN的结果又做了一遍过滤那些右表为NULL的行就被清掉了左连接形同虚设。正确的做法是想筛选左表条件放在WHERE没事想限制右表参与连接的范围就得放在ON里。举一个场景查所有部门以及每个部门的平均工资但不想要平均工资太低的部门参与统计你可以在ON里先把低工资的人排除掉再算平均。SELECT d.deptno, d.dname, AVG(e.sal) AS avg_sal FROM dept d LEFT JOIN emp e ON e.deptno d.deptno AND e.sal 2000 GROUP BY d.deptno, d.dname;这种写法在报表场景中非常实用。很多新人用WHERE e.sal 2000最后发现没达到2000工资的员工所在部门直接消失了整个部门列表都不全这就是没搞懂LEFT JOIN和WHERE的先后逻辑。1.5 三张表以上的连接控制连接顺序和中间结果集业务系统里三张表、四张表JOIN也不少见。比如电商要查“下单用户的姓名、订单编号、订单商品名称”涉及用户表、订单表、订单明细表。这时候通常是以订单表作为中间桥梁先JOIN用户表拿用户信息再JOIN明细表拿商品信息。写起来像这样SELECT u.username, o.order_id, od.product_name FROM users u JOIN orders o ON u.user_id o.user_id JOIN order_details od ON o.order_id od.order_id;要提醒的是不要一上来就把所有表JOIN完再过滤MySQL的优化器虽然会做一定程度的调整但很多时候它并不完美。缩小数据范围的过滤条件能放在子查询里提前过滤就尽量提前过滤能用小表驱动大表就尽量用小表做驱动表。多表连接最怕的其实不是SQL写不出来而是中间结果集膨胀得太厉害15行表JOIN 20行表能出300行楼层一多整个查询就肉眼可见地变慢。2. 自连接一张表当成两张表用自己跟自己玩2.1 什么时候需要自连接自连接听起来玄乎本质就是一张表跟自己JOIN。既然是一张表那为什么还要JOIN因为表里的数据之间存在“层级关系”或者“同构关系”你需要把同一张表的某一行数据去匹配另一行数据。最经典的场景就是员工表。假设emp表里既有普通员工又有经理普通员工和经理都存放在emp表里通过mgr字段指向经理的员工编号。员工“张三”的mgr指向员工编号7839而7839这行记录本身也是emp表里的一员叫“李四”。现在业务想查“每个员工的姓名及其经理的姓名”你发现光靠一张emp表无论如何都只能查出员工自己的名字经理名字也是员工名字如果不做两次匹配就只能得到员工自己的那一行。这种场景下自连接就登场了。你需要把同一张emp表在FROM里出现两次一次当作员工表另一次当作经理表然后用员工的mgr等于经理的empno作为连接条件。因为FROM里出现了两次同一个表名MySQL会分不清你指的是哪一份所以必须给它们起不同的别名。2.2 员工-经理经典案例先把别名搞清楚来看最标准的写法SELECT e.ename AS employee_name, m.ename AS manager_name FROM emp e LEFT JOIN emp m ON e.mgr m.empno;在这个查询里e是“员工副本”m是“经理副本”。匹配条件是e.mgr m.empno含义就是“我这行员工的经理编号在经理表副本里对应的那条记录的员工编号”。这里我特意用了LEFT JOIN。为什么不用INNER JOIN因为top-level的大老板mgr字段是NULL他没有经理。如果用内连接大老板这一行会因为连接条件不成立而直接消失。实际上老板本人也是员工你查员工及其经理时通常也希望老板出现在结果里只是它对应的经理名字显示为NULL。这就是自连接和外连接组合的一个典型应用。许多面试官也喜欢在这道题上追加一问那你怎么查“哪些员工的工资比他的经理还高”抓住关键点仍然用自连接把两张表连接起来后在WHERE里比较这两张表的工资字段SELECT e.ename AS employee_name, e.sal AS employee_sal, m.ename AS manager_name, m.sal AS manager_sal FROM emp e JOIN emp m ON e.mgr m.empno WHERE e.sal m.sal;注意这里用了内连接JOIN因为大老板没有经理不可能存在比较。凡是能通过连接条件和WHERE一起表达的业务都是自连接的统治区。2.3 深入自连接两个容易翻车的细节自连接看起来简单实际写起来容易在三个地方翻车。第一个是忘记别名或者别名混用。你让MySQL分不清e和m是谁SQL直接报错或者在WHERE里误写了e.empno e.mgr结果返回一堆无意义数据。所以自连接第一步永远是“想清楚这张表要扮演哪两个角色然后用别名把它俩固化下来”。第二个是连接条件方向搞反。员工找经理是e.mgr m.empno而不是e.empno m.mgr。后者表示的是“这个员工是哪位经理的手下”反向推导语义完全变了。很多新手照着网上例子抄抄完能跑但压根不懂条件为什么这么写。你自己写的时候别急着调通先想想业务逻辑到底是要“查上属”还是“查下属”。第三个是漏掉NULL值。mgr字段允许为NULL时记得思考要不要用LEFT JOIN保留根节点这也呼应了前面讲的JOIN类型选择。此外如果你想通过子查询方式实现自连接比如EXISTS相关子查询查“是否带团队”的经理写法其实也源于同一套逻辑SELECT DISTINCT m.empno, m.ename FROM emp e JOIN emp m ON e.mgr m.empno;这里把e当员工表、m当经理表查出来的其实是“当过经理的人”再用DISTINCT去重。想想看如果你能从这些案例里归纳出“自连接本质上就是复制一份表然后按角色做关联”这句话后面无论出什么题都能套进去。2.4 自连接的扩展连续签到、商品推荐、好友关系自连接不只存在于层级关系表里它还广泛应用于“同类记录之间的比较”。比如一张商品表product(id, name, category_id, price)想查“同一分类下价格比自己高的其他商品”这种就必须自连接否则你不给同一张表分组怎么跟同分类比较这里的思路是product表一分为二p1代表“自己”p2代表“同分类下的其他商品”然后通过p1.category_id p2.category_id和p1.price p2.price来匹配。再比如社交场景的好友表一条记录代表一对好友关系(user_a, user_b)你查“和某个用户有共同好友的人”其实也需要把好友表JOIN两次。先查第一层好友再查第二层好友最后过滤掉第一层已经出现的那些人。这类需求看似高级底层还是自连接/多表连接的排列组合。自连接的价值就在于它提供了一种“把行与行之间的关系变成列与列之间比较”的手段。在关系模型里行与行默认是独立的自连接就是打破这种独立性、让同一张表的行进行比较的主要方式之一。3. 子查询查询套查询逻辑分层的好帮手3.1 子查询的三大类型先会分才会用子查询说白了就是一条SQL里嵌套了另一条SELECT。MySQL里的子查询可以出现在SELECT子句、FROM子句、WHERE子句和HAVING子句里返回的结果可以是一个数值、一列值、一行值甚至一张临时表。按返回结果的不同我把它分成三类分类能帮你判断该用哪种写法。标量子查询返回单个值一行一列最典型的用法是放在SELECT后面当做一个计算列。如果你要查每个员工的基本信息以及他的部门名称除了JOIN也可以用标量子查询SELECT e.ename, e.sal, (SELECT d.dname FROM dept d WHERE d.deptno e.deptno) AS dept_name FROM emp e;这种写法的好处是逻辑非常直白甚至不需要理解JOIN和分组就能写出来。缺点是如果外层表行数多MySQL要对每一行都执行一次子查询性能可能成为瓶颈。数据量小、临时分析的时候用用无妨线上大表得掂量一下。列子查询返回一列多行。最常见的搭配是IN、NOT IN、ANY、ALL。比如查“部门名称叫SALES或RESEARCH的员工有哪些”可以先查出这些部门编号的集合再把它作为外层查询的过滤条件SELECT ename, job, deptno FROM emp WHERE deptno IN ( SELECT deptno FROM dept WHERE dname IN (SALES, RESEARCH) );表子查询返回多行多列通常是FROM里套一层子查询充当派生表。老一点的数据库管它叫“内联视图”。这种写法的核心思想是把复杂的聚合先做掉把结果当成一张临时表再跟其他表连接。比如要查每个部门的最高工资员工是谁先算每个部门的最高工资再把它跟员工表连接缺了这步会非常麻烦SELECT e.deptno, e.ename, e.sal FROM emp e JOIN ( SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno ) t ON e.deptno t.deptno AND e.sal t.max_sal;很多新手第一步学不会子查询就是没先给子查询的形态归类。看到标量当成普通值看到集合就用IN或EXISTS看到结果集就放FROM里当临时表这套思路练熟了写嵌套SQL就不会慌了。3.2 相关子查询和非相关子查询怎么区分很多资料喜欢把子查询分成相关子查询和非相关子查询。所谓“相关”指的是内层子查询里有没有引用外层查询的列。如果子查询完全不依赖外层它可以先独立执行一次再把结果拿给外层用这叫非相关子查询如果子查询要使用外层表当前行的某些字段每处理一行都得重新执行一遍子查询这叫相关子查询。最简单的相关子查询案例就是上面那个标量子查询dept表不依赖员工表严格说是非相关。真正的相关子查询场景比如查“每个部门中工资高于该部门平均工资的员工”。这条如果不相关子查询子查询里根本不知道当前员工属于哪个部门所以必须引用外层员工的deptnoSELECT e1.ename, e1.sal, e1.deptno FROM emp e1 WHERE e1.sal ( SELECT AVG(e2.sal) FROM emp e2 WHERE e2.deptno e1.deptno );逻辑很自然外层每取出一个员工就跑到内层去算一下同部门的平均工资如果不是低于平均工资就返回。这种逐行关联的方式叫相关子查询语义最强但代价是计算量大在没有索引的情况下往往要反复扫描内层表。优化思路通常是把这种相关子查询改写成JOIN 分组 临时表的推导式让引擎一次算完平均工资再关联。3.3 IN与EXISTS的选择性能以外还有语义坑子查询里最经典的一道面试题就是“IN和EXISTS到底谁快”。网上答案五花八门核心原则其实非常简单如果子查询结果集非常小IN通常够用如果外层表很小、子查询表很大且需要关联判断EXISTS往往更好。现代MySQL优化器已经做了不少改写但大表面前这两者的执行计划仍有差异。不过在谈性能之前得先看清语义这里有一个非常容易踩的坑IN在遇到NULL值时行为比较特殊。假如dept表的deptno列里含有NULL你可以自己试一下“WHERE deptno IN (10, NULL)”在SQL的三值逻辑里跟NULL比较的结果不是TRUE也不是FALSE而是UNKNOWN所以IN一个含NULL的集合可能导致查询结果跟你预期不一致。相对地EXISTS是“有即真”对NULL不敏感。日常开发中如果集合字段本身存在NULL风险我建议尽量使用EXISTS或者先在子查询里过滤NULL。举个常见的业务“找出所有有员工的部门”SELECT d.deptno, d.dname FROM dept d WHERE EXISTS ( SELECT 1 FROM emp e WHERE e.deptno d.deptno );EXISTS子查询的SELECT列表你完全可以写SELECT 1或者SELECT *因为EXISTS只关心“有没有记录”不关心具体值。执行时找到第一条就返回TRUE不需要全程遍历。很多人在EXISTS里写了复杂的列计算纯粹是浪费。同样是“找出有员工的部门”用IN就能写SELECT deptno, dname FROM dept WHERE deptno IN (SELECT deptno FROM emp);两个写法结果一样但语义上一个偏向“逐行探测”一个偏向“先把列表算出来再比对”。真正要注意的是NOT IN和NOT EXISTS。NOT IN在子查询结果包含NULL时有可能出现“一行都查不出来”的诡异情况而NOT EXISTS不会。所以凡是要表达“不在某个集合里”的需求我基本默认写NOT EXISTS。3.4 把子查询和JOIN结合复杂的业务大多靠组合拳实际业务里的查询很少是单一技巧。比如要查“哪些部门的平均工资高于全公司平均工资”直接写SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING AVG(sal) ( SELECT AVG(sal) FROM emp );这里子查询当作标量用在HAVING里逻辑清爽。如果子查询的聚合结果需要跟其他维度做更多比较那可以把它放FROM当派生表再JOIN出去。这种场景帮我形成一个判断标准如果一个查询要经过多步“先聚合再关联”的处理用派生表往往比一堆相关子查询嵌套要快得多而且可读性更好。毕竟SQL是声明式语言你告诉数据库“要什么结果”而不是教它“怎么一步步做”写得太绕只会自找麻烦。4. 复合查询实战从需求到SQL的完整推演4.1 场景A查每个部门工资最高的员工附带部门名称这道题网上有大量变体是复合查询很好的练手案例。如果只查员工表你可能会写SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno;但这张表只能拿到部门和最高工资拿不到“哪个员工是这个最高工资”。因为SQL里非聚合列不在GROUP BY范围内MySQL虽然用only_full_group_by模式会直接拒掉这种查询你用别的模式勉强能查出结果但那个员工名往往是随机的。所以正确思路是两步第一步在派生表里算每个部门最高工资第二步把员工表跟这个派生表做连接ON条件是部门相同且工资相同这样就能精确定位到对应员工。如果还要带出部门名称那就再JOIN一次dept表。SELECT d.dname, e.ename, e.sal FROM emp e JOIN dept d ON e.deptno d.deptno JOIN ( SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno ) t ON e.deptno t.deptno AND e.sal t.max_sal ORDER BY d.dname;注意一个细节最高工资的员工可能不止一个如果同一部门有两个人并列第一这个查询会返回两行这是正确的。如果业务只要任意一个你得改用窗口函数ROW_NUMBER()或者MIN/MAX的写法否则结果就可能飘。很多开发者在测试数据上跑没问题一上生产出现重复行就开始怀疑SQL写错其实问题出在业务规则定义不清楚。4.2 场景B查工资低于本部门平均工资的员工这道题我在面试候选人的时候经常出。数据是同一张员工表emp要求员工跟“本部门平均工资”做比较。你没法用普通GROUP BY一步到位因为平均工资是部门级的聚合结果而你又要返回员工级的明细。最直白的写法是相关子查询前面已经演示过。另一种常规思路是用JOIN 派生表。先按部门分组求平均工资再把它作为子查询和emp做连接SELECT e.ename, e.sal, e.deptno, t.avg_sal FROM emp e JOIN ( SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno ) t ON e.deptno t.deptno WHERE e.sal t.avg_sal;这道题设计的巧妙之处在于它把“明细”和“聚合”放进了同一个结果集而GROUP BY天然会把明细压扁所以必须通过子查询先算出聚合结果再跟原表关联。理解了这一点遇到“比平均值低/高”“比最大值少多少”这类问题就别再琢磨怎么在一个GROUP BY里硬凑了。4.3 场景C三表联查加去重、排序和条件过滤复杂的订单分析现在把难度往上推一步模拟一个更完整的订单业务。假设有三张表customers客户表包含customer_id、customer_nameorders订单表包含order_id、customer_id、order_dateorder_items订单明细表包含order_id、product_id、quantity要查“2024年1月份下过单的客户以及他们购买过的商品种类数并按商品种类数降序排列”。一般人的第一反应是直接JOIN三张表把订单明细展开再按客户分组统计COUNT(DISTINCT product_id)。这没毛病SELECT c.customer_id, c.customer_name, COUNT(DISTINCT oi.product_id) AS product_cnt FROM customers c JOIN orders o ON c.customer_id o.customer_id JOIN order_items oi ON o.order_id oi.order_id WHERE o.order_date BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY c.customer_id, c.customer_name ORDER BY product_cnt DESC;但必须注意一个问题JOIN完明细表之后行数等于客户-订单-商品明细的笛卡尔积放大如果同一客户有多个订单、一个订单里有多行商品客户信息会在每一行上重复。COUNT(DISTINCT oi.product_id)是为了去重后的统计但如果统计的是订单数量直接用COUNT(DISTINCT o.order_id)更稳妥别直接COUNT()。这是所有“先展开再聚合”查询的通用警示连表后行数膨胀了聚合粒度已经变了COUNT的语义要重新确认默认COUNT()极容易产生奇怪的结果。如果想统计“哪些客户买过至少3种商品”加上HAVING COUNT(DISTINCT oi.product_id) 3即可。HAVING必须放在GROUP BY之后它跟WHERE的区别在于WHERE是分组前对原始行过滤HAVING是分组后对聚合结果过滤。这道题完整展示了多表JOIN、分组聚合、去重和排序如何组合到一条SQL里属于实习岗到中级开发都应该熟练掌握的标准套路。4.4 场景D自连接子查询连环套逐层递进找“层级数据”最后来一道自连接和子查询结合的题目查“每个经理手下工资最高的员工”。需求里经理本身也是员工他可能还有上级这里暂时不关心多级只关心直接上下级。你可以先用自连接把员工和经理放一起再用聚合求每个经理手下的最高工资SELECT m.ename AS manager_name, e.ename AS employee_name, e.sal FROM emp e JOIN emp m ON e.mgr m.empno JOIN ( SELECT mgr, MAX(sal) AS max_sal FROM emp WHERE mgr IS NOT NULL GROUP BY mgr ) t ON e.mgr t.mgr AND e.sal t.max_sal;这段SQL里第一层JOIN负责自关联员工和经理第二层JOIN负责把“经理编号和最高工资”的条件对齐。没经理的大老板会被第一层JOIN自动过滤因为没人mgr指向他这也是自连接内连接的一个正常副作用。如果再往上加需求要求“经理自己也希望看到他的上级是谁”那就再加一层自连接即可。许多“组织架构报表”就是这种层层叠叠的模式看到这里你应该能体会到复合查询的威力了它基本不受表数量的限制只要你理清表之间的关联路径就能一层一层JOIN下去把树形结构拍平成表格。4.5 把OR改成UNION一个容易被搜索引擎点名的优化细节标题里提到多表查询和子查询热词里也经常有“mysql的or能去重吗”。这里说个大家常遇到的坑一条SQL里用了OR连接多个条件对应多个不同字段MySQL优化器有时候没法很好地利用多个索引可能退化成全表扫描。比如SELECT ename, job, deptno FROM emp WHERE job MANAGER OR deptno 20;如果job和deptno上分别有索引优化器理论上可以把两个条件各自的索引结果合并取并集但实话说OR对索引的利用远不如UNION稳定。当数据量大、查询慢时可以改写成两个独立查询的UNIONSELECT ename, job, deptno FROM emp WHERE job MANAGER UNION SELECT ename, job, deptno FROM emp WHERE deptno 20;UNION自带去重语义正好回应了“mysql的or能去重吗”——如果你不想要重复UNION比OR更符合预期。如果你明确知道两个子查询结果不会有交集想省掉去重排序的开销就写UNION ALL。这个细节用在大表上效果立竿见影必要时你可以用EXPLAIN验证执行计划变化。唯一需要注意的是UNION两个SELECT的列数和列类型必须兼容排序和分页要放到整个UNION之后处理这个跟子查询规则类似。5. 常见问题排查与慢查询优化实战5.1 从“跑得动”到“跑得快”EXPLAIN是排查第一工具SQL写得再花哨最终都要交给优化器执行。如果你的查询在十万行级别就慢得像爬大概率不是MySQL“不行”而是你没有让执行计划走上正确的索引路径。这里强烈建议所有做开发的同学凡是写完复杂查询先花30秒看一遍EXPLAINEXPLAIN SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno WHERE e.sal 2000;EXPLAIN的输出会告诉你每一张表的访问类型type列、可能用到的索引possible_keys、实际用到的索引key、大概扫描多少行rows以及是否有额外的排序或临时表Extra。type列重点关注几个档位ALL是最差的全表扫描index是扫了整棵索引树比ALL稍好但也不理想range是指标范围扫描例如WHERE deptno BETWEEN 100 AND 200ref和eq_ref是真正的索引等值匹配效率高eq_ref通常出现在主键或唯一索引的连接匹配里。如果内层大表的连接字段上没有索引EXPLAIN会显示ALL这就是性能瓶颈。rows是优化器预估的扫描行数不是精确值但可以作为量级参考。Extra列里如果出现Using temporary说明查询需要临时表来处理去重或排序数据量大时会比较吃力出现Using filesort说明排序没走索引需要把排序字段考虑加入索引出现Using index condition说明索引条件下推生效是好事。我自己排查慢SQL的固定套路就是先看type有没有ALL再看rows有多大最后看Extra有没有临时表和文件排序逐项击破。5.2 复合查询慢的常见原因索引失效、隐式类型转换、提前过滤不够复合查询慢很多人第一反应是“把JOIN改成子查询”。实际上大多数慢查询的根本原因不在JOIN语法而在底层数据访问方式。一是连接字段没有索引。JOIN的关键是右表连接的字段上要有索引。比如emp JOIN dept ON e.deptno d.deptno需要在dept.deptno上建立索引。同理自连接e.mgr m.empnom.empno是主键通常没问题但如果在字段上没有索引每次关联都要扫描右表多表查询瞬间变成N×M的全表扫描。二是隐式类型转换。连接字段或过滤字段的字符集、类型不一致时MySQL会强制把一边转成另一边。最常见的是字符串字段跟整数比较、utf8mb4和utf8字符集互相连接。字段一旦套了函数索引就直接失效。比如WHERE DATE(order_date) 2024-01-01order_date字段上有索引也白搭应该写成order_date 2024-01-01 AND order_date 2024-01-02。三是过滤太晚。理论上应该先用WHERE条件把大表缩成小结果集再做JOIN或子查询。如果你把过滤条件写到最外层的HAVING里内层却把几百上千万行全量JOIN完那数据库再强也救不了你。一个典型错误是三层JOIN聚合却对某张明细表没有任何时间范围限制明明可以先在明细表子查询里把时间过滤掉再跟主表连你偏要先连完再过滤。5.3 一条慢查询的排查实录之前有个订单报表需求大概结构是orders(订单主表)、products(商品表)、order_items(订单明细)。开发同学写出来的SQL是一口气三表JOIN再按商品分组统计月销量。数据量也就几十万行但查询经常超过5秒。我用EXPLAIN跑了一遍发现问题出在order_items的JOIN上。orders表过滤时间范围后只查出两万行但order_items表里没有以order_id为前导的复合索引type列显示ALL几十万行全扫描一遍。这相当于让两万行订单挨个去匹配几十万行明细不做死才怪。后来给order_items表加了(order_id, product_id, quantity)的组合索引查询时间直接降到0.1秒以内。这里的关键点是复合查询的性能不取决于你写了多少聪明逻辑而取决于你给数据库提供了多少“走索引的路”。而且要注意索引顺序。多列索引要遵循最左前缀原则把等值筛选的列放前面把范围筛选用到的列放后面。如果业务上固定按order_id关联那么index(order_id)就是基础再考虑是否需要把product_id带进去覆盖查询。5.4 连接查询和锁、事务的关系复合查询不仅影响查询性能还会影响数据一致性。这里说个跟锁有关的知识点作为扩展。如果你在一个事务里同时锁定多张表的数据JOIN的连接条件如果没走索引MySQL可能需要先对某张表做全表扫描来确定匹配行期间加锁的范围可能远超预期。行锁的加锁范围在某些隔离级别下会扩大到索引区间甚至出现间隙锁把别人要插入的数据也挡住。实际业务里因为多表UPDATE引发死锁的案例特别多。比如两个事务同时做“先SELECT JOIN再UPDATE”的操作加锁顺序恰好不同就可能互相等待。这种问题排查起来要靠SHOW ENGINE INNODB STATUS里面LATEST DETECTED DEADLOCK部分会记录最近一次死锁的SQL和执行步骤。真遇到这种情况除了在代码层规范加锁顺序SQL层面也可以考虑把复合查询拆成两步先查出要更新的主键集合再按主键去更新。5.5 复合查询面试高频题速查结合上面的知识点我整理了一份我在面试里非常爱问的复合查询题目清单每道题都能考察候选人对连接和子查询的理解深度。查每个部门人数大于3的部门编号及人数这考的是GROUP BY HAVING。查每个员工姓名及其经理姓名考自连接和LEFT JOIN。查哪些员工的工资高于其所在部门的平均工资考相关子查询或JOIN 派生表。查每个部门工资最高的员工考派生表与员工表的二次匹配。查没有员工的部门考NOT EXISTS或LEFT JOIN IS NULL。找出连续三天有交易的用户的通用写法考窗口函数或自连接日期差。每道题的答案都不是唯一的你用一种写法解出来后最好再想一想能不能换一种思路。比如“没有员工的部门”用NOT EXISTS写得出来用LEFT JOIN加IS NULL也写得出来用NOT IN加子查询也写得出来三者的执行计划在数据量不同时差异明显。能把一套题用三种解法写出来才说明你对复合查询的底层逻辑是真的通了而不是停留在背答案阶段。如果你现在准备面试我建议把上面这几个场景手写一遍SQL再把执行计划讲给别人听。如果身边没人听就自己开个测试库把索引建上把EXPLAIN的每一列列出来对着官方文档查含义。搞懂这些跟面试官聊多表查询基本不会冷场。6. 写在实操之外的小建议复合查询这块知识看十篇教程不如自己把练习数据建起来跑一遍。我见过不少同事单表查询很熟一到两张表就卡壳。原因不是不懂连接语法而是没建立起“先把业务关系画出来”的思维习惯。建议你拿到任何多表需求时先做三件事明确结果集的粒度是什么——一行代表一个员工、一张订单还是一次聚合画出表和表之间的关联路径——谁是主表怎么关联到另一张表想清楚过滤和聚合的先后顺序——哪些条件应该在JOIN前过滤哪些应该在分组后判断。我个人在实践中最深的体会是复合查询写得好不好跟对业务数据的理解深度是正相关的。索引能帮你跑得快但逻辑错了跑得再快也没用。所以写完复杂SQL先用小数据集人工核验结果确认输出没问题再交给生产环境跑。这个方法看起来笨但实实在在能帮你避开很多“测试环境没问题、生产环境数据一变结果就飞了”的雷。