写这篇文章的起因是我接手一个电商后台报表时碰到的事一条带子查询的SQL数据量才二十来万查一次要三秒多运营点一次页面刷一次超时。我花了半天把子查询从头到尾捋了一遍改写之后压到几十毫秒。后来发现类似的写法在团队代码里到处都是——大家不是不会写子查询而是不知道子查询在不同的写法、不同的数据量、不同的索引条件下性能差距能有一个数量级甚至两个数量级。这篇就围绕MySQL里子查询的实战展开把执行原理、改写手法、索引配合和踩过的坑一次性说透。适合刚接触SQL不久、想系统搞懂子查询性能逻辑的开发者也适合写了好几年SQL但没认真看过执行计划的老手。1. 子查询到底是什么从三个典型的业务问题说起子查询说白了就是SQL里再嵌一条SQLMySQL执行的时候把内层的查询结果交给外层去用。它解决的通常是三类问题一是带条件的聚合比较比如找出每个分类里卖得最好的商品二是集合归属判断比如找出所有下过订单的会员三是构造中间结果集比如先统计出每个类目的平均销量再找出高于平均值的商品。1.1 什么时候该用子查询什么时候别用很多教程教你能不用子查询就不用这个说法太武断。我在实际项目里的判断标准就三条子查询的结果集很小比如几百行以内且被外层多次引用可以用子查询写在WHERE里做过滤且能走到索引可以用业务逻辑不复杂子查询写出来一眼能看懂优先保留。反过来遇到下面这些情况就得多留个心眼子查询在SELECT后面当标量用外层每扫一行就执行一次内层查询子查询的结果集很大外层还要做全表扫描去匹配子查询嵌套超过两层执行计划开始变得不可控。一句话总结子查询不是洪水猛兽它是SQL工具箱里的一个工具。你要搞清楚的是它会在什么时机执行、执行多少次这才是判断该不该用的核心。1.2 子查询的三种形态标量、表、存在性第一种是标量子查询返回单个值通常配合比较运算符。SELECT product_name, sales_count, (SELECT AVG(sales_count) FROM products) AS avg_sales FROM products;这种写法看起来简洁但它是对products表的每一行都执行一次内层聚合查询。如果外层表有10万行内层就执行10万次。哪怕单次只要0.1毫秒累计起来也是10秒。这是标量子查询最典型的性能陷阱。第二种是表子查询出现在FROM子句里MySQL称它为派生表Derived Table。SELECT category_id, AVG(monthly_sales) AS avg_sales FROM ( SELECT category_id, DATE_FORMAT(create_date, %Y-%m) AS month, SUM(sales_count) AS monthly_sales FROM products GROUP BY category_id, DATE_FORMAT(create_date, %Y-%m) ) t GROUP BY category_id;派生表的问题在于旧版本MySQL会把内层查询结果物化成一张临时表如果内层返回几十万行内存临时表会转成磁盘临时表性能直接崩。MySQL 5.7之后优化器会把派生表和外部查询合并优化情况好了很多但还是要注意内层不能有LIMIT、聚合函数这类阻止合并的因素。第三种是EXISTS/IN子查询做存在性判断。SELECT customer_id, customer_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );这种写法本身不算差关键看子查询关联字段有没有索引以及优化器会不会把它改写成semi-join。后面第三节会专门讲怎么让它跑得又快又稳。2. 性能瓶颈的根源子查询是怎么被执行的不了解子查询的执行机制就很难理解为什么有时候改了写法就快有时候改了反而更慢。这里最重要的一个概念是相关子查询和非相关子查询。2.1 相关子查询 vs 非相关子查询的执行逻辑非相关子查询的意思是内层查询不依赖外层任何字段它可以独立执行一次结果固定。SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM vip_customers);这里内层vip_customers的查询结果不会变理论上执行一次就够了。MySQL 5.6之前确实是这样内层先执行把结果作为常量列表传给外层做IN判断。但这样有个隐患内层返回的列表如果太大内存开销会很高。相关子查询就麻烦一点内层查询引用了外层的字段外层每访问一行内层就可能要根据这一行的值重新执行一次。SELECT category_id, product_name FROM products p WHERE sales_count ( SELECT MAX(sales_count) FROM products WHERE category_id p.category_id );这条SQL的逻辑是找出每个分类中销量最高的商品。它就是一个典型的相关子查询外层有多少行内层就可能执行多少次。如果不走索引每次内层都要扫一遍products表20万行的表就是20万次全表扫描不卡才怪。2.2 为什么慢查询里总有子查询的身影慢查询日志里最常见的子查询几乎都是同一个套路外层大表 内层大表 相关条件无索引。我处理过一个案例event_logs表有800万行user_levels表有30万行。开发同学写了一条SELECT * FROM event_logs e WHERE user_level ( SELECT max_level FROM user_levels WHERE user_id e.user_id );这条SQL跑了47秒。原因很清楚外层800万行每一行都要去user_levels里查一次user_id对应的max_level而user_id字段恰好没有索引每次内层查询都是全表扫30万行。800万乘以30万这个计算量根本没法接受。这种情况我叫它N1次查询问题和内层索引缺失叠加在一起威力翻倍。排查慢SQL时只要看到执行计划里内层查询的type是ALL全表扫描而外层又是大表基本可以直接断定是这个原因。2.3 用执行计划看子查询的真实成本与其靠猜不如直接看EXPLAIN。我举一个典型的例子执行下面这句EXPLAIN SELECT customer_id, customer_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );可能的执行计划如下idselect_typetabletypepossible_keyskeyrows1PRIMARYcALLNULLNULL100002DEPENDENT SUBQUERYorefidx_customer_ididx_customer_id4DEPENDENT SUBQUERY就表示这是一个相关子查询外层每行都会触发内层查询。好在内层的type是ref说明用到了customer_id索引每次查询只看4行整体成本就是1万次索引点查完全能接受。另一个常见情况是select_type显示SUBQUERY而不是DEPENDENT SUBQUERY说明它是个非相关子查询先独立执行一次物化成结果集再给外层用。这时候要看rows如果内层返回的行数很大比如上万行就要考虑改写否则物化临时表和内存开销都会成为隐患。我自己的习惯是凡是涉及子查询的慢SQL第一件事就是跑一遍EXPLAIN重点看三个字段——select_type、type、rows。这三个字段基本能告诉你子查询是哪种执行方式、有没有走索引、大概扫了多少行。3. 实战优化改写子查询的几种有效姿势看懂了执行机制接下来就是动手改。下面这四种改写方式是我这几年的实战总结覆盖了大部分慢子查询的场景。3.1 EXISTS 还是 IN数据量不同选择不同很多文章说用EXISTS代替IN可以提升性能这句话只对了一半。在MySQL 5.6之后优化器引入了semi-join优化IN和EXISTS在很多场景下会被改写成同一种执行方式性能差异已经很小。真正的分水岭在于外层表大、内层表小用IN往往更合适外层表小、内层表大用EXISTS配合内层索引更稳定。举个反直觉的例子。查下过订单的客户客户表5万行订单表200万行-- 写法AIN SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders); -- 写法BEXISTS SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id c.customer_id);如果orders.customer_id上有索引两条SQL的执行计划很可能是相同的都被优化成semi-join。但如果orders.customer_id没有索引IN子查询会把200万行订单全部物化出来而EXISTS会针对每个客户去订单表里做一次索引查找。这种情况下EXISTS完胜。反过来如果内层子查询的结果集很小比如只有几十个VIP客户IDIN反而更好。因为优化器可以直接把这个小列表当成常量处理不需要逐行关联。我的建议是不要死记口诀用EXPLAIN看实际执行计划。如果IN子查询的select_type是SUBQUERY且rows很小直接用如果rows巨大换成EXISTS并确保关联字段有索引。3.2 把相关子查询改成 JOIN一个订单报表优化案例相关子查询性能不好最常见的原因就是外层大表逐行触发内层查询。把它改写成JOIN让MySQL一次性做连接操作往往能带来数量级的提升。看一个真实案例。当时的业务需求是查询每个客户最近一笔订单的金额。原始写法是一个相关子查询SELECT c.customer_id, c.customer_name, (SELECT o.order_amount FROM orders o WHERE o.customer_id c.customer_id ORDER BY o.order_time DESC LIMIT 1) AS last_amount FROM customers c;这条SQL在客户表5万行、订单表180万行的数据量下跑了9.6秒。原因就是标量子查询加相关子查询的双重debuff外层每行执行一次内层内层还要排序取最新一条。第一次改写是把它变成派生表SELECT c.customer_id, c.customer_name, t.last_amount FROM customers c LEFT JOIN ( SELECT customer_id, order_amount AS last_amount FROM orders WHERE (customer_id, order_time) IN ( SELECT customer_id, MAX(order_time) FROM orders GROUP BY customer_id ) ) t ON c.customer_id t.customer_id;这里利用(customer_id, order_time)的复合索引子查询只做了一次全表分组扫描然后外层JOIN走主键匹配。优化后跑了0.8秒。后来MySQL 8.0上线我们直接用窗口函数进一步简化SELECT c.customer_id, c.customer_name, t.last_amount FROM customers c LEFT JOIN ( SELECT customer_id, order_amount AS last_amount, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_time DESC) rn FROM orders ) t ON c.customer_id t.customer_id AND t.rn 1;执行时间在0.5秒左右。这个案例说明相关子查询慢的时候优先想到的应该是能不能把内层变成一次性的集合操作不管是派生表还是窗口函数都比外层逐行触发内层要高效得多。3.3 派生表FROM 子句中的子查询的正确打开方式派生表是把子查询放在FROM里先算出一个临时结果集再和外层查询做连接。它的性能关键点有三个第一MySQL 5.7之后支持了Derived Merge优化器可能把派生表和外层查询合并不物化临时表。但合并的前提是内层查询没有聚合函数、没有DISTINCT、没有LIMIT、没有UNION。所以如果你的派生表里用了聚合函数就没法合并会老实物化。第二物化后的临时表如果很大MySQL会从内存临时表转成磁盘临时表性能会断崖式下跌。判断标准可以用EXPLAIN看Extra列有没有Using temporary。第三派生表的连接字段一定要有索引。MySQL 8.0支持了optimizer_use_condition_enhancement会在物化临时表上自动建索引但之前版本要靠手工。所以生产环境里如果派生表要和外层做JOIN我一般建议先把它拆开分别确认每一步的结果集大小再决定要不要合并。一个实用的技巧是用派生表之前先单独跑一遍内层查询看返回多少行。如果返回几万行物化开销还可以接受如果返回百万行就要考虑改写逻辑或者先用临时表加索引的方式处理。3.4 窗口函数部分替代子查询的场景MySQL 8.0开始支持窗口函数这给了我们一个很好的替代方案。尤其适合分组取TopN分组后和组内比较这类原来需要用复杂子查询才能实现的场景。举一个经典的例子查询每个分类销量前3的商品。用子查询写会很绕SELECT category_id, product_name, sales_count FROM products p WHERE ( SELECT COUNT(*) FROM products p2 WHERE p2.category_id p.category_id AND p2.sales_count p.sales_count ) 3;这种写法的问题很明显通过统计比自己销量高的数量小于3来模拟排名效率低不说遇到销量并列的还会漏数据。换成窗口函数就清晰多了SELECT category_id, product_name, sales_count FROM ( SELECT category_id, product_name, sales_count, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY sales_count DESC) AS rn FROM products ) t WHERE rn 3;如果希望并列也保留把ROW_NUMBER()换成RANK()即可。这个方案的好处是只扫一遍表利用窗口函数在内存中分组排序执行计划比多次相关子查询简单很多。不过要注意一点窗口函数在数据量特别大的时候排序内存会是一个新的瓶颈。需要关注sort_buffer_size的设置以及EXPLAIN里是否出现Using filesort。多数OLTP场景下TopN的行数都不大窗口函数配合合适的索引性能是可控的。4. 索引设计配合子查询这是效率的真正分水岭改写子查询只能解决一部分问题。真正让子查询跑得快的往往是索引设计。4.1 子查询涉及的列索引到底要怎么建子查询的性能瓶颈通常发生在两类列上关联列内外层连接的字段和过滤列WHERE和GROUP BY使用的字段。拿订单表和客户表举例SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );这里的关联列是orders.customer_id必须在orders表上建索引。建了索引内层查询就是索引点查不建索引内层就是全表扫描。这是第一优先级。第二优先级是过滤列。比如这个场景SELECT category_id, product_name FROM products p WHERE sales_count ( SELECT MAX(sales_count) FROM products WHERE category_id p.category_id );内层子查询的WHERE条件是category_id聚合列是sales_count。这种情况下最合适的索引不是单独给category_id建索引而是建一个复合索引(category_id, sales_count)。这样内层查询既能通过category_id快速定位数据又能在索引里直接完成MAX(sales_count)的计算不需要回表取数据。4.2 覆盖索引如何让子查询变成索引扫描覆盖索引的意思是查询需要的所有字段都在索引里MySQL可以直接扫索引而不回表。这在子查询场景里优势巨大因为内层查询往往只需要少数几个字段。举一个实际优化案例。当时的SQL是查询无有效地址的客户SELECT customer_id, customer_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM addresses a WHERE a.customer_id c.customer_id AND a.is_valid 1 );addresses表有600万行。最初的索引是customer_id单列索引执行计划显示每次内层查询都要回表查看is_valid字段整体跑了4.2秒。优化方案是把索引改成复合索引(customer_id, is_valid)。这样一来EXISTS的内层查询只需要在索引里确认是否存在满足条件的记录根本不用回表。改造后执行时间降到0.3秒。这个案例给我一个习惯给子查询涉及的关联列建复合索引时要去看看内层子查询里用了哪些其他条件字段把它们一起塞进索引往往效果立竿见影。4.3 慢查询日志里的典型案例分析分享一个慢查询日志里反复出现的模式算是典型的子查询索引错误示范。SELECT id, order_no FROM orders WHERE buyer_id IN ( SELECT buyer_id FROM buyers WHERE level VIP );这条SQL执行了3.8秒。看执行计划外层orders是ALL全表扫描内层buyers也是ALL全表扫描两个表都在互相拖后腿。查buyers表结构发现level字段上没有索引buyer_id也没有。导致内层子查询每次都要扫描全部买家记录外层又要扫描全部订单记录。解决思路分两层先解决内层在buyers(level)上建索引。这样内层查询VIP会员列表直接从全表扫描变成索引范围扫描结果集会从几十万行缩到几百行。再看外层orders.buyer_id加索引配合semi-join优化外层就能通过索引快速匹配内层结果集。改造后同样的SQL执行时间掉到0.1秒以内。这个例子再次说明很多时候不是子查询本身慢而是表设计阶段就没有为子查询的常用路径预留索引。5. 陷阱与维护子查询的隐藏坑和可读性平衡优化做到后面你会发现真正难的不是性能而是可维护性。代码里留一个性能不错的复杂子查询三个月后没人看得懂照样是个坑。5.1 容易导致全表扫描的三个写法第一在子查询的结果列上做函数运算。SELECT * FROM orders WHERE customer_id IN ( SELECT CAST(buyer_id AS CHAR) FROM buyers );一旦内层查询结果列上有类型转换、函数运算优化器就没法直接使用索引可能把外层也拖成全表扫描。最好的做法是保持两侧字段类型一致尽量不在子查询结果列上做任何加工。第二隐式类型转换。customer_id在订单表是整数在子查询里却返回字符串MySQL会做隐式转换索引可能失效。排查时用EXPLAIN看type如果是ALL第一步就是检查两边字段类型是否一致。第三在OR条件里套子查询。SELECT * FROM orders WHERE status PAID OR customer_id IN (SELECT buyer_id FROM blacklist);OR条件很容易让优化器放弃索引改成对两个条件分别计算再合并。这类SQL建议改写成UNION两个独立查询或者用UNION ALL让每一路都能单独走索引。5.2 子查询里的 LIMIT 陷阱LIMIT在子查询里是个容易踩坑的地方。MySQL 8.0之前派生表内部如果用了LIMIT会导致优化器无法把它和外层查询合并必定物化临时表。更隐蔽的问题是逻辑层面的。取每个分类的最新商品如果用SELECT category_id, product_name, create_date FROM products p WHERE create_date ( SELECT MAX(create_date) FROM products WHERE category_id p.category_id );这个写法先取最大值再比较逻辑正确但有性能隐患。很多人图省事直接写SELECT category_id, product_name, create_date FROM products p WHERE create_date IN ( SELECT create_date FROM products WHERE category_id p.category_id ORDER BY create_date DESC LIMIT 1 );后来会发现LIMIT 1出现在相关子查询里会让优化器执行大量重复的子查询而且逻辑上如果最大值有多条记录结果还可能丢失。正确做法是改用窗口函数SELECT category_id, product_name, create_date FROM ( SELECT category_id, product_name, create_date, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY create_date DESC) rn FROM products ) t WHERE rn 1;5.3 可读性优先什么时候保留子查询而不是强行改写优化不是目的可维护才是。有些子查询写出来比JOIN直观得多而且性能也不差我就不强行改写。典型的例子是NOT EXISTS和LEFT JOIN ... WHERE IS NULL的取舍SELECT * FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );等价写法SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id c.customer_id WHERE o.order_id IS NULL;执行计划显示两者的性能可能差不多但对新手来说NOT EXISTS读起来更接近业务语义不存在订单的客户。遇到这种等价改写我一般保留原写法只在注释里说明。强行改成JOIN反而增加了理解成本。另一个保留子查询的场景是逻辑隔离。子查询把计算逻辑封装起来外层只需要关注主查询的目标字段。比如SELECT order_no, customer_name FROM orders o JOIN customers c ON c.customer_id o.customer_id WHERE o.total_amount ( SELECT AVG(total_amount) * 1.5 FROM orders );这里的标量子查询表达的是一个业务规则高于平均金额1.5倍的订单。写成子查询比先算出平均金额再手动带入要清晰得多而且因为它是非相关子查询只执行一次性能开销完全可以忽略。这种子查询留着没毛病。6. 实测下来我对子查询优化的几点个人原则文章最后分享几条我踩过不少坑之后总结出来的实操原则都是经验层面的东西不一定写在任何官方文档里。第一慢SQL优化永远从EXPLAIN开始不要靠猜。执行计划里的select_type、key、rows三个字段能在30秒内告诉你子查询有没有走上正确的路。没有证据的优化都是碰运气。第二改子查询时一次只改一种东西。要么加索引要么改写法要么改数据结构千万不要同时改三个因素然后看结果。否则哪一步生效了你根本不知道下次遇到类似问题还是两眼一抹黑。第三对于线上系统子查询的改动一定要小流量验证。我见过有人把一条80秒的IN子查询改成EXISTS后因为没注意索引差异反而把查询拖到更慢。子查询改写不是越复杂就越快一切以实际执行计划为准。第四建立慢SQL监控的习惯把执行时间超过100毫秒的查询自动收集起来。子查询问题有个特点数据量小的时候完全没感觉等到数据量翻倍性能可能突然崩盘。提前在慢查询日志里发现苗头比事后救火轻松太多。第五也是我最想强调的子查询优化是SQL开发里面性价比很高的一块技能。它不像分库分表、读写分离那样需要架构层面的改造只要你理解了相关子查询的执行机制学会看执行计划再掌握几种常见的改写姿势大多数慢子查询都能在半小时内搞定。这种能力在排查线上问题的时候是真的能救命的。如果有条件建议你在自己的测试环境里造一份20万行以上的数据把今天提到的几种写法挨个跑一遍对比执行计划和耗时。这个过程跑通了以后看到任何子查询都不会慌。