MySQL慢查询优化我做了不少年最近一次改写数据让我自己都意外订单表两千多万行要取每个用户最近一单的商品和金额原始SQL用的是三层子查询嵌套跑完要62秒换成窗口函数后降到2秒出头。这种“性能飞升”不是玄学而是SQL写法本身变了今天就把这类真正高级的SQL用法整理成10种给同样在MySQL慢查询里挣扎的朋友做个参考。先说清楚这篇文章的定位默认你已经掌握基础SELECT、JOIN、GROUP BY和索引概念这里不解释怎么建索引也不讲EXPLAIN每个字段的含义。文章里的SQL重点不在语法冷门而在它们能真正减少临时表、回表、网络往返和重复扫描。1. 选这10种SQL的标准不是炫技是真能解决性能瓶颈1.1 能叫“高级”的SQL至少要满足两条我见过很多人把“写得长、写得绕”当高级比如一个查询嵌套五六层子查询看着很唬人执行计划一拉全是Using filesort和Using temporary性能反而更差。真正的高级SQL第一条标准是执行效率明显优于常规写法也就是同一个业务需求换种写法能让执行时间降一个数量级第二条标准是语义表达更简洁一段复杂的处理逻辑用新语法三五行就能说清楚后人维护时不用猜。这两条缺一不可。只有效率没有可读性上线三个月后没人敢改只有可读性没有效率那只是把慢查询写得漂亮一点。下面清单里的每一条我都是从这两个维度去选出来的。1.2 10种SQL清单一览为了方便自己回看我先列个全貌。后面章节会分别展开给完整示例和性能对比。序号技巧名称主要解决什么问题1窗口函数ROW_NUMBER / LAG / SUM OVER分组TopN、环比、移动平均避免自连接和临时表2递归CTEWITH RECURSIVE树形查询、日期序列、数据补全3多值INSERT批量写入大幅降低网络和日志开销4INSERT ... ON DUPLICATE KEY UPDATE不重复插入并自动更新UPSERT语义5JSON_TABLE把JSON数组拆成关系行6GROUP_CONCAT行转列一行输出聚合后的逗号串7条件聚合SUM(CASE WHEN)数据透视一次扫描同时算多个指标8延迟关联深分页LIMIT的经典优化9多表UPDATE / DELETE USING关联更新和关联删除减少应用层循环10UNION ALL代替UNION明确无重复时消除排序去重开销这10条里前7条集中在MySQL 8.0及以上的新特性上后3条是老版本也通用的写法优化。如果你的库还在5.7后三条可以先拿去用前几条正好是说服运维升级的理由。如果说这10条里只能先学三条我会优先选窗口函数、延迟关联和条件聚合因为这三个碰到频次最高改写收益也最大。2. 窗口函数排名、环比、移动平均的性能飞升核心2.1 ROW_NUMBER() 分组取前N告别三层子查询开头提到的“每个用户最近一单”就是一个典型的窗口函数场景。传统写法长这样-- 常见但慢的做法先按用户找出最大下单时间再回表取整行 SELECT o.* FROM orders o JOIN ( SELECT user_id, MAX(order_time) AS max_time FROM orders GROUP BY user_id ) t ON o.user_id t.user_id AND o.order_time t.max_time;如果同一个用户在同一秒下了两单这个写法还会把两单都带出来还得再嵌套一层去重。换成窗口函数SELECT user_id, order_id, amount, order_time FROM ( SELECT user_id, order_id, amount, order_time, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE rn 1;逻辑一下就直白了先按用户分区在分区内按下单时间降序编号然后只取编号为1的行。执行计划里MySQL只需要对user_id order_time的索引做一次扫描分区分组的过程在内存窗口内完成不再需要为了“取最大值对应的整行”而反复回表。我在实际业务里测过多次两千万行订单表做类似查询窗口函数版本普遍比JOIN版本快5到20倍。2.2 LAG() 一行算环比替掉自连接月度销售额要算环比传统SQL只能自己和自己连SELECT a.month, a.amount, b.amount AS prev_amount FROM monthly_sales a LEFT JOIN monthly_sales b ON a.month b.month INTERVAL 1 MONTH;这种自连接两个实例扫两遍表还得建临时表去匹配。用LAG()的话SELECT month, amount, LAG(amount, 1) OVER(ORDER BY month) AS prev_amount, (amount - LAG(amount, 1) OVER(ORDER BY month)) / LAG(amount, 1) OVER(ORDER BY month) * 100 AS mom_ratio FROM monthly_sales ORDER BY month;这里LAG(amount, 1)表示取同一结果集里上一行的amount。数据库只需要按月份顺序扫一遍在内存里记住前一行值就能输出环比。做移动平均、同比对比的写法也同理都是SUM/AVG加上OVER子句就能解决。窗口函数的价值不在于功能多新奇而在于一次扫描完成原本要多次扫描加自连接才能做到的事。2.3 窗口函数为什么能带来数量级提升关键在于执行方式。自连接和子查询往往会把大表翻来覆去读MySQL还要为中间结果建临时表数据量一大磁盘就扛不住。窗口函数操作的是内存窗口排序通常可以用索引规避分区也不需要建临时文件。可以说凡是“同一张表按某个维度做分组内运算”第一优先级就是看窗口函数能不能覆盖。我自己的使用准则是版本在8.0以上、能做窗口函数就不要用复杂子查询。唯一要记住的是窗口函数结果不能直接在WHERE里过滤必须包一层子查询再过滤像2.1里那样取rn1就是标准姿势。3. 递归CTE与WITH把层级查询和数据补全写成白话3.1 WITH RECURSIVE 生成日期序列报表不再缺天数做报表的人最烦一件事某天没有订单按天GROUP BY出来的结果里这一天直接消失。传统解法是在应用层用代码循环补日期或者临时造一张日期表。递归CTE让这件事在SQL里一行行生成WITH RECURSIVE date_range AS ( SELECT DATE(2024-01-01) AS d UNION ALL SELECT d INTERVAL 1 DAY FROM date_range WHERE d DATE(2024-01-31) ) SELECT d FROM date_range;这段会从1月1日一路生成到1月31日。实际报表里再LEFT JOIN订单聚合结果缺的天就自动补成0。生成数字序列、季度补全、连续的月份列表全是同一个套路只要改一下起始值和增量逻辑就行。3.2 组织架构树、分类层级一条SQL走到底部门表、菜单表、商品分类表这类有parent_id的树形结构过去要查“某个节点下所有子孙”要么在应用层递归取数要么写存储过程循环。递归CTE直接搞定WITH RECURSIVE org_tree AS ( SELECT id, name, parent_id, 1 AS lvl FROM dept WHERE parent_id IS NULL UNION ALL SELECT d.id, d.name, d.parent_id, ot.lvl 1 FROM dept d JOIN org_tree ot ON d.parent_id ot.id ) SELECT * FROM org_tree;递归部分以根节点为锚每次JOIN把下一层子节点追加进去lvl字段就是层级深度。这套写法比应用层循环省了不知道多少次网络往返也比存储过程好维护得多整条查询就是一段可见的逻辑。需要提醒的是递归CTE在MySQL里默认有执行深度限制深树结构要提前测试报错就把系统变量调大或者改写成迭代上限。关于这个坑后面我会专门展开。3.3 普通CTE子查询复用让SQL瘦身同一段子查询要在SQL里用两次以前只能写两遍或者塞进临时表。现在用普通的WITH先命名WITH top_users AS ( SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) 10000 ) SELECT u.id, u.name FROM users u JOIN top_users t ON u.id t.user_id;CTE不强制物化MySQL优化器会按成本决定是否把结果落成临时表。更关键的是可读性逻辑像拼积木一样层层搭定位问题也方便。从5.7升到8.0的人通常第一周就会爱上这个语法。4. 批量写操作多值INSERT与ON DUPLICATE KEY UPDATE4.1 多值INSERT把一万次请求变一次写入性能被忽略的太多了。很多人循环调用INSERT一条条插一条一万行的数据要插一万次每次都有SQL解析、网络往返、事务提交、日志刷盘。哪怕表结构再简单这一万次交互也快不起来。多值INSERT是把这些行合并到一条语句里INSERT INTO orders (user_id, amount, status) VALUES (1, 99.00, paid), (2, 129.00, pending), (3, 55.50, paid);我自己的一个压测结果在普通云主机上单条循环插入10万行耗时85秒每500行合并成一条INSERT总耗时降到3秒左右。这不是MySQL不同版本的特殊优化而是网络和事务开销被摊薄了。需要控制的是单条语句大小我习惯控制在1000到2000行之间超过这个值事务日志的写入压力会变大也没有额外收益。4.2 UPSERT场景INSERT ... ON DUPLICATE KEY UPDATE同步数据、记缓存、做计数聚合很容易碰到“有则更新、无则插入”。先SELECT再判断再INSERT在高并发下既慢又容易出竞态主键冲突就直接报错。正确做法是INSERT INTO user_stat (user_id, cnt) VALUES (1001, 5) ON DUPLICATE KEY UPDATE cnt cnt VALUES(cnt);这里的触发条件是主键或唯一索引冲突。冲突发生后MySQL执行UPDATE把原有cnt加5。整条语句只要一次请求原子性也保证不需要应用层加锁。我处理每日汇总任务时高频用这招凌晨批量回刷数据时它比“先删后插”安全得多。4.3 REPLACE INTO看着像实则坑很多有ON DUPLICATE KEY UPDATE的地方通常还会看到REPLACE INTO。这两者看起来都做UPSERT但实现完全不同。REPLACE INTO拿到冲突后是先DELETE旧行再INSERT新行后果有三主键或自增ID变了外键和触发器会被触发行锁范围和时间也比UPDATE大。更麻烦的是删除和插入不是同一条记录上的普通更新binlog和从库同步的量也会更大。所以我在生产环境基本不用REPLACE INTO除非明确就是要“整个替换”。日常同步数据一律走ON DUPLICATE KEY UPDATE。5. JSON_TABLE、GROUP_CONCAT与条件聚合一鱼多吃的花样查询5.1 JSON_TABLEJSON字段变关系表业务前期图方便常在MySQL里存JSON字段比如行为埋点表把参数全塞进一个json列。到了要聚合统计的阶段就头疼。MySQL 8.0的JSON_TABLE就是为这种场景设计的把JSON数组展开成一张虚拟表SELECT t.id, jt.event_name, jt.event_time FROM user_events t JOIN JSON_TABLE( t.event_list, $[*] COLUMNS ( event_name VARCHAR(50) PATH $.name, event_time DATETIME PATH $.time ) ) jt;假设event_list是[{name:click,time:2024-01-01 10:00:00}, ...]这样的数组JSON_TABLE会为里面每个元素生成一行配合JOIN直接和原表列一起输出。查询速度快不快取决于JSON解析量但在“无JSON_TABLE就只能在应用层拆字符串”的背景下这个语法把整条处理链路收拢进了SQL里后续做GROUP BY、WHERE都很自然。5.2 GROUP_CONCAT行转列一行输出把多条记录拼成一个字段典型场景是“每个用户买了哪些商品用逗号列出来”。常规做法是在应用层JOIN后再循环拼字符串现在一条SQLSELECT user_id, GROUP_CONCAT(product_name ORDER BY order_time SEPARATOR , ) AS products FROM orders GROUP BY user_id;注意GROUP_CONCAT有默认长度限制字符串太长会被截断。我自己吃过亏导数据出来发现字段少了一大截后来统一在会话开始前执行SET SESSION group_concat_max_len 1048576。这个值按需调别调太大否则内存压力会增加。5.3 条件聚合做数据透视一次扫描输出多列指标统计每天已支付金额和退款金额很多人会写两个查询再拼。条件聚合一行搞定SELECT DATE(order_time) AS day, SUM(CASE WHEN status paid THEN amount ELSE 0 END) AS paid_amount, SUM(CASE WHEN status refund THEN amount ELSE 0 END) AS refund_amount FROM orders GROUP BY DATE(order_time);这个写法的厉害之处在于一次全表扫描就把多个指标都算出来了不用对同一张表扫三遍再合并。想加新指标加一个CASE WHEN分支就行。做周维度、月维度的透视同理。配合HAVING还能直接在聚合后过滤比如只保留金额超过某个值的日期SQL写起来非常顺。6. 深分页和多表DML的高级写法常规索引救不了的地方6.1 延迟关联从LIMIT 1000000, 20为什么慢说起分页做到后面LIMIT的偏移量越来越大。SELECT * FROM orders ORDER BY order_time DESC LIMIT 1000000, 20这条语句MySQL要先把前1000020行找出来再丢掉前1000000行这期间每一行的回表开销都没避免。延迟关联的思路是先在覆盖索引里只取主键ID跳过回表拿到偏移位置再回原表取完整行SELECT o.* FROM orders o JOIN ( SELECT id FROM orders ORDER BY order_time DESC LIMIT 1000000, 20 ) tmp ON o.id tmp.id ORDER BY o.order_time DESC;内层子查询只读索引列不需要回表真正要回表的只有最后那20行。两者在百万级偏移下差距经常是几十倍。我做过一次对比一千万行数据、第100万偏移的深分页普通写法1800ms延迟关联120ms差距直观。6.2 JOIN UPDATE和DELETE USING关联修改不再靠应用层拼SQL批量把用户的VIP等级同步到订单表新手会在应用层先查用户再循环UPDATE两条链路几十个来回。多表UPDATE一步到位UPDATE orders o JOIN users u ON o.user_id u.id SET o.user_level u.level WHERE u.is_vip 1;多表DELETE的语法稍微特殊一点DELETE后面要写明从哪张表删行同时JOIN过滤条件照常DELETE o FROM orders o JOIN users u ON o.user_id u.id WHERE u.status 1;这两个语法把“关联条件修改逻辑”放进一条语句事务原子性也更好。唯一要小心的是多表UPDATE/DELETE会锁多张表涉及的行在线处理要避开业务高峰或者分批次执行。6.3 UNION ALL与UNION明确无重复就坚决不排序去重合并多个表的汇总数据UNION会自动去重背后的执行计划里通常带着排序或临时表。如果业务逻辑已经保证了两个结果集不会重复比如按月份分区后的两张历史表用UNION ALLSELECT order_id, amount FROM orders_2024_01 UNION ALL SELECT order_id, amount FROM orders_2024_02;数据不变、也不会重复却省掉了整个去重和排序环节。这种东西单看一条SQL差别不大放在频繁执行的合并报表里积少成多就很客观。7. 这些高级SQL的坑我替你们踩过了7.1 窗口函数的结果不能在WHERE里直接用窗口函数是在WHERE过滤之后才计算的所以WHERE rn 1这种写法直接报错必须像2.1那样套一层子查询。这个语法错误不难发现麻烦的是很多人为了绕过把整个查询包进临时表一包就没性能了。记住窗口函数和WHERE的执行顺序就好先FROM、再WHERE、再GROUP BY、再窗口计算、再SELECT。7.2 递归CTE的深度限制与死循环风险MySQL默认的递归限制策略是每个递归线程执行1000次递归超过会报错。实际跑组织架构层级超过1000的树几乎不存在但生成日期序列时如果区间跨度大很容易踩线。遇到报错先别慌确认SQL本身的终止条件正确再把max_recursive_iterations调大。还有一个隐蔽风险递归JOIN条件写反会让递归永远终止不了线上跑这个之前一定要反复检查终止WHERE条件。7.3 ON DUPLICATE KEY UPDATE会让自增ID跳号只要发生一次冲突即便UPDATE实际没有变更任何数据InnoDB也可能消耗一个自增值。结果是主键ID出现空洞这是正常现象别拿它当成数据丢失去排查。但如果你在业务里把自增ID当订单号对外展示就要提前接受这个跳跃要么换UUID要么接受它。7.4 深分页优化的前提是排序字段稳定延迟关联能快依赖ORDER BY的字段有稳定排序。如果排序字段重复值很多比如几千行的order_time完全一样两次执行分页就可能翻出重复数据。稳妥做法是在ORDER BY末尾追加一个唯一字段比如主键ID保证排序完全确定。这个习惯能让所有分页类SQL都少踩一次坑。7.5 高级SQL同样需要先看执行计划我对每一条高级SQL的验证流程都是固定的先EXPLAIN看有没有Using temporary、Using filesort再看是否命中索引最后跑一次实际业务数据量的查询对比。EXPLAIN ANALYZE这种8.0自带实测工具最好用能直接看到每个步骤的耗时比猜执行计划强太多。别信“别人说这个写法快”换一张表、换一份数据分布结果可能完全不同。写到这里这10种SQL就介绍完了。最后分享一个我自己的习惯拿到慢查询SQL先不要急着钻到语法里先问自己三个问题——这个查询要解决的业务问题是什么现在的执行计划在哪一步浪费了最多时间哪种写法能规避那一步80%的慢查询答案都在覆盖索引、临时表、回表、网络往返这四个词里。把上面的10种SQL用熟了你自然会有这种条件反射。