在做MySQL调优的这几年里我见过太多这样的场景明明索引建了一堆EXPLAIN看完也显示走了索引但接口就是慢得让人抓狂。后来我慢慢发现真正的瓶颈往往不在索引而在SQL写法本身——同样的业务需求用不同的写法性能差出十倍甚至百倍一点都不奇怪。数据库优化圈子里流传着一句话能让数据库少干活的SQL就是高级SQL。这句话虽然朴素但确实是衡量SQL质量最直接的标尺。这篇文章是我从实际项目中整理出来的10种高性能SQL写法覆盖了查询、统计、更新、写入、层级数据和JSON数据处理等常见场景。每一种用法我都会说清楚它解决什么问题、为什么能快、有哪些坑需要避开。适合正在被慢查询困扰的后端开发、数据分析师和DBA也适合想在面试中把SQL优化讲出深度的人。1. 为什么同样的需求SQL写法不同性能差距几十倍1.1 一个真实的低效场景之前某个数据统计模块里有一个接口功能很简单查最近7天每个用户的订单金额排名。业务方反馈每次打开页面都要等三秒以上数据库CPU还经常飙高。当时的SQL写法和下面这段长得很像SELECT u.id, u.name, (SELECT SUM(o.amount) FROM orders o WHERE o.user_id u.id AND o.order_date DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY o.user_id) AS total_amount FROM users u ORDER BY total_amount DESC LIMIT 20;这段SQL在逻辑上没有任何问题每个用户去子查询里算一次总金额套上排序就能出结果。问题是当users表有5万条数据orders表有300万条数据时这个写法等于对每个用户都执行一次针对orders表的聚合查询实际扫描的行数接近亿级。索引虽然建了走了但架不住执行次数多。后来我把这个逻辑换成窗口函数一条SQL解决执行时间从3秒多降到了80毫秒左右。这个案例让我意识到一个事实MySQL的优化器确实很智能但它不是万能的。很多写法会限制优化器的发挥空间导致它只能按最笨的方式执行。1.2 三种“少干活”的核心思路回看这些年做过的SQL优化本质上都在围绕三个方向做文章减少扫描行数。让数据库不要全表扫、不要扫无用的行这是最直接的提速方式。覆盖索引、条件裁剪、分区裁剪本质上都在做这件事。减少回表次数。普通索引查到主键之后还要再回聚簇索引取整行数据一次查询可能回表几万次。延迟关联、覆盖索引要解决的就是这个问题。减少SQL执行次数。程序里循环查数据库是最大的性能黑洞一次网络往返、一次SQL解析、一次查询计划生成都要时间。把N次查询合并成一条SQL性能常常是指数级提升。下面这10种高级SQL基本都落在这三个方向上。理解了这个底层逻辑你才能知道在什么场景套用哪种写法。2. 第1~2种让数据库少扫描数据的写法2.1 窗口函数分组排序、环比计算一次搞定窗口函数是MySQL 8.0加入的重量级特性解决的是“分组内排名、分组内取值、移动计算”这类问题。在没有窗口函数的年代实现“查每个用户最近一笔订单”只能用相关子查询SELECT o.* FROM orders o WHERE o.order_time ( SELECT MAX(o2.order_time) FROM orders o2 WHERE o2.user_id o.user_id );这种写法最大的问题在于orders表每行都要去子查询里执行一次扫描数据量一大基本就废了。换成窗口函数后所有行只扫描一遍SELECT user_id, order_time, amount FROM ( SELECT user_id, order_time, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE t.rn 1;子查询里通过PARTITION BY把数据按用户分组在组内按订单时间排序并编号外层只取编号为1的行一次全表扫描外加一次排序就搞定了。相比相关子查询这种写法的扫描次数从“N次”降到了“1次”。窗口函数还有几个高频用法我这里统一说下ROW_NUMBER()生成连续不重复的行号适合取每组前N条。RANK() / DENSE_RANK()排名场景区别在于并列时是否跳号。LAG() / LEAD()取上一行或下一行的值做环比、同比很方便。SUM() OVER (ORDER BY ...)累计求和场景。我在实际项目里用窗口函数处理过一个比较复杂的需求统计每个销售员最近30天的成交额还要算出和前一天的差额。不用窗口函数就得写两个子查询再加上JOIN用LAG两行就写完了。注意一个细节窗口函数的PARTITION BY和ORDER BY字段尽量在对应表上有索引。否则MySQL只能通过临时表和文件排序来执行数据量大时反而比子查询还慢。我曾经在一个千万级表上吃过亏加了个物化临时表才稳住。2.2 NOT EXISTS代替NOT IN子查询改写立竿见影NOT IN真的是一个容易出问题的写法性能还是正确性都可能翻车。最典型的一个坑就是子查询结果只要包含一个NULL整个NOT IN就会返回空结果数据全被过滤掉了。正确性先不说从执行计划上看很多版本的MySQL对NOT IN的优化也不友好容易出现全表扫描。举一个实际例子。业务需求是找出那些没有发过首单的用户SELECT id, name, phone FROM users WHERE id NOT IN ( SELECT user_id FROM orders WHERE first_order_flag 1 );如果orders表有几百万行这个NOT IN很可能就触发全表关联扫描执行计划里的rows会非常难看。改成NOT EXISTS之后SELECT u.id, u.name, u.phone FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.first_order_flag 1 );这里有个很容易被忽略的点EXISTS子查询的SELECT列表写什么都无所谓MySQL只看有没有匹配行。所以SELECT 1就够了别傻乎乎地把所有字段都列出来。而NOT EXISTS走的是半连接优化语义上等价于“找到匹配立刻停止”不需要反复扫描整张orders表。为什么NOT EXISTS通常更快因为MySQL在执行EXISTS子句时只要在orders表里找到了第一行满足条件的数据就立即返回不再继续扫描同一个user_id的其它订单。而NOT IN经常要先把整个子查询结果集物化出来再做一遍完整的比较操作这两个路径的开销差距在数据量大时非常明显。如果你的MySQL版本是5.7以上还有个更隐蔽的优化优化器可能会把NOT IN改写成anti-join执行计划里出现“Not exists”字样这种情况其实也没问题。但为了稳妥和可读性我一般都建议在NOT IN和NOT EXISTS之间二选一时明确选择NOT EXISTS反正语义一样为什么不挑那个更稳的3. 第3~5种改写查询逻辑减少回表3.1 条件聚合一次遍历出多维度统计条件聚合有一个很典型的应用场景一张订单表想看今天每个渠道的新增订单数、已支付订单数、退款订单数。很多同学第一反应是写三个COUNT查询或者先查出来在代码里循环统计。这两种方式都不好前者把一次统计拆成了三次全表扫描后者把计算压力转移到了应用层还带来了网络交互。用条件聚合的写法一段SQL就能把多个指标全部算出来SELECT channel, COUNT(*) AS total_cnt, COUNT(CASE WHEN status paid THEN 1 END) AS paid_cnt, COUNT(CASE WHEN status refunded THEN 1 END) AS refund_cnt, SUM(CASE WHEN status paid THEN amount ELSE 0 END) AS paid_amount FROM orders WHERE order_date CURDATE() GROUP BY channel;这段SQL的核心在于整张表只扫描了一次每个分组在内存中同时完成多项统计。COUNT(CASE WHEN status paid THEN 1 END)的意思是只有在status匹配时计数加1不匹配时CASE返回NULLCOUNT自动忽略NULL。有一个容易搞混的细节统计数量用COUNT统计金额用SUM。想求“已支付订单的金额总和”时要用SUM(CASE WHEN ... THEN amount ELSE 0 END)如果写成COUNT(CASE WHEN ... THEN amount END)统计的是单据数量而不是金额。条件聚合对报表场景的效果非常明显。我刚做数据仓库那会儿一张销售日报涉及的统计项超过30个最开始写了将近20条SQL后来全部合并成了一条聚合SQL执行时间从十几秒降到了1秒以内。这种优化方式对OLAP场景的改造收益远超单纯加索引。3.2 延迟关联覆盖索引回不了表的破局法先解释一下什么叫回表。InnoDB的普通索引叶子节点存的是主键值不是完整数据行。当你通过普通索引找到一堆主键后还需要拿着这些主键再到聚簇索引里取整行数据这个过程就叫回表。回表本身不可怕可怕的是回表次数太多。延迟关联的思路很巧妙先用覆盖索引把需要的主键找出来再去关联原表取完整数据。覆盖索引能直接提供查询所需的全部字段避免了回表。举个例子查最近订单里按用户分组的翻页数据传统写法SELECT o.id, o.user_id, o.amount, o.order_time, u.name FROM orders o JOIN users u ON u.id o.user_id ORDER BY o.order_time DESC LIMIT 100000, 20;这个SQL的执行过程是找到排序后的第100000到第100020行但前面那100000行依然要参与排序和回表这非常浪费。改成延迟关联SELECT o.id, o.user_id, o.amount, o.order_time, u.name FROM ( SELECT id FROM orders ORDER BY order_time DESC LIMIT 100000, 20 ) tmp JOIN orders o ON o.id tmp.id JOIN users u ON u.id o.user_id ORDER BY o.order_time DESC;内层子查询只需要访问order_time, id这个覆盖索引不涉及回表拿到20个主键后再去取完整数据。这里有个前提内层子查询的ORDER BY和SELECT字段一定要能被某个索引覆盖否则这个优化就名存实亡。这种写法提升效果有多大我曾经在一个500万行的订单表上测试过大偏移分页传统写法要2.3秒延迟关联只需要0.4秒将近6倍的差距而且这个差距会随着OFFSET变大变得越来越悬殊。“大分页必卡”是MySQL老用户都知道的常识延迟关联算是解决这个问题的第一板斧。3.3 游标分页大偏移量强分页的替代方案延迟关联虽然解决了回表问题但OFFSET的代价并没有完全消除。每次翻页MySQL仍然要把OFFSET之前的记录全部数一遍。如果一个用户翻到了第100万条数据即使延迟关联也需要在索引上跳过100万行。这时候就要考虑业务方案层面的改造——游标分页。游标分页的核心思路是不再用页码而是记住上一次拿到的最后一条记录的ID下一页就是“取ID大于这个值的前N条”-- 第一页 SELECT id, user_id, amount, order_time FROM orders WHERE create_time 2024-01-01 ORDER BY id LIMIT 20; -- 第二页把上一页最后的 id 传进来 SELECT id, user_id, amount, order_time FROM orders WHERE id 1024 AND create_time 2024-01-01 ORDER BY id LIMIT 20;因为主键索引天然有序每次查询走主键索引定位数据库只需要跳过极少量的数据翻页深度越深这种写法的优势越明显。我在一个管理后台把无限滚动列表从传统分页改成游标分页后接口的P99延迟从800毫秒直接降到了120毫秒。游标分页的代价是失去了“直接跳转到第N页”的能力只能一页一页往下翻。这个限制要看业务场景是否能接受。用户端信息流、订单流水、操作日志这些场景基本都是顺序浏览完全可以用游标分页。业务上非要支持任意跳页的延迟关联仍然是更好的选择。4. 第6~7种写入场景的高性能SQL4.1 JOIN UPDATE批量更新不再一条条来批量更新是另一个高频慢操作场景。常见的情况是有一张中间表存着从外部系统同步过来的最新状态现在要把这些状态更新到业务主表里。很多人习惯先查出中间表数据再在程序里循环执行UPDATE。一次业务处理涉及几千条数据就要执行几千次UPDATE数据库连接池都冒烟了。正确姿势是直接让UPDATE支持JOIN一条SQL完成整批更新UPDATE orders o JOIN order_status_sync s ON s.order_id o.id SET o.status s.new_status, o.update_time NOW() WHERE s.sync_time 2024-01-01 00:00:00;很多开发对UPDATE JOIN不太熟总觉得UPDATE只能单表更新其实MySQL的UPDATE语法天然支持多表关联。JOIN之后会在内存里完成订单表和同步表的匹配然后统一更新目标表不需要程序逐条组装SQL。这里必须强调一句JOIN UPDATE虽然高效但条件一旦写错就会更新掉大量不应该更新的行。我的习惯永远是把UPDATE先改写成SELECT看一眼影响行数再执行。而且大批量更新时要考虑锁范围可以分批执行每批2000条加上LIMIT控制节奏UPDATE orders o JOIN order_status_sync s ON s.order_id o.id SET o.status s.new_status WHERE s.sync_time 2024-01-01 00:00:00 AND o.status s.new_status LIMIT 2000;分批更新能显著降低长事务持有的行锁时间减少对业务读请求的影响。实际线上更新百万级数据时我总是分批跑而不是试图一口气update完这是数据库稳定性运维的底线。4.2 INSERT ON DUPLICATE KEY UPDATE幂等写入的一次性方案数据同步和数据导入场景中最烦人的一个问题就是重复记录。常规逻辑是先查一下数据在不在在就UPDATE不在就INSERT。程序里的“先查再写”模式在并发场景下容易产生竞态问题两个请求同时判断“不存在”然后都执行INSERT最终落库两条相同业务编号的数据。MySQL提供了一个数据库层面的原子方案INSERT ... ON DUPLICATE KEY UPDATE。当插入的数据与唯一键冲突时自动转为UPDATE操作INSERT INTO order_daily_snapshot (order_id, total_amount, snap_date) VALUES (1234, 899.00, 2024-06-01) ON DUPLICATE KEY UPDATE total_amount VALUES(total_amount), snap_date VALUES(snap_date);这段SQL的前提是order_id或snap_date上必须有唯一索引否则DUPLICATE KEY这个触发条件根本不存在。执行的时候如果唯一键没有冲突就是纯INSERT如果冲突了自动更新指定字段。MySQL 8.0.20版本之后VALUES()函数被标记为弃用官方推荐使用别名方式INSERT INTO order_daily_snapshot AS s (order_id, total_amount, snap_date) VALUES (1234, 899.00, 2024-06-01) ON DUPLICATE KEY UPDATE total_amount s.total_amount, snap_date s.snap_date;这种写法把“查一次插一次”变成了“一条语句完成”省掉的不仅是SQL解析和网络往返还消除了并发下的重复写入风险。我在做订单数据每日快照时就靠这个特性保证重复跑批不会产生脏数据。5. 第8~10种特殊场景下的高级SQL5.1 GROUP_CONCAT把多次查询合并成一次聚合有这样一个场景后台要给每个商品打标签前端展示时需要把该商品的所有标签拼成一个字符串。最常见的实现是查出商品清单再循环查询标签表逐条拼接。商品一多这个N1问题就把接口拖垮了。用GROUP_CONCAT一次关联查询就搞定SELECT p.id, p.name, GROUP_CONCAT(t.tag_name ORDER BY t.sort_num SEPARATOR ,) AS tags FROM products p LEFT JOIN product_tag_map m ON m.product_id p.id LEFT JOIN tags t ON t.id m.tag_id GROUP BY p.id;GROUP_CONCAT并不是简单的字符串拼接它可以指定排序规则还可以指定分隔符。处理“一对多关系转换成一行字符串”这种需求时它是效率最高的方案甚至比在应用层用代码拼接更快因为合并逻辑在数据库内存里直接完成了。这个函数有一个容易踩的坑默认最大长度为1024字节。当拼接结果超过这个长度MySQL不会报错而是直接截断数据莫名其妙就少了。解决办法是在会话级别调大group_concat_max_lenSET SESSION group_concat_max_len 65535;如果数据量非常大可以一次性设置成更大的值。还有一点要注意GROUP_CONCAT默认只分组内数据结果无序需要显式ORDER BY才能保证拼接顺序。标签排序、日志按时间拼接这些场景忘了加ORDER BY很容易出现前后端展示不一致。5.2 递归CTE层级结构数据必须掌握组织结构、商品分类、权限树这类带上下级关系的表过去最恶心的写法就是程序递归和自连接查询。要么在代码里一层层循环访问数据库要么SQL写得又长又难懂。MySQL 8.0引入的递归公共表表达式WITH RECURSIVE直接改变了这个局面。先看实际写法WITH RECURSIVE category_path AS ( -- 锚点根节点 SELECT id, name, parent_id, 1 AS depth FROM category WHERE parent_id 0 UNION ALL -- 递归关联下一层 SELECT c.id, c.name, c.parent_id, cp.depth 1 FROM category c INNER JOIN category_path cp ON c.parent_id cp.id ) SELECT * FROM category_path ORDER BY depth;递归CTE的执行逻辑分两步先执行锚点查询得到根节点集合再反复执行递归部分把新找到的子节点不断追加到结果集中直到没有新的匹配行。这个过程中MySQL是在内部迭代完成的每一层的结果都会被临时保存迭代结束一次性返回不用程序处理中间数据。递归CTE在组织架构导出、分类面包屑、BOM物料展开等场景下非常好用。以前在程序里写递归还经常担心堆栈溢出现在数据库内部循环代码反而更简洁。需要注意递归深度默认限制是1000遇到层级特别深的树结构可以修改max_recursive_depth参数。同时锚点查询和递归部分必须都有清晰的终止条件否则无限递归会把实例直接打挂。5.3 JSON_TABLEJSON字段直接当表查MySQL从5.7开始支持JSON类型但从查询角度看JSON字段传统上还是得靠JSON_EXTRACT函数配合程序处理。如果JSON里存的是一个数组想做关联查询就非常痛苦。MySQL 8.0的JSON_TABLE函数改变了这一点它能把JSON数组展开成一张关系表像普通表一样做JOIN和WHERE。SELECT u.id, u.name, pt.product_id, pt.price FROM users u, JSON_TABLE( u.cart_items, $[*] COLUMNS ( product_id INT PATH $.product_id, price DECIMAL(10,2) PATH $.price ) ) AS pt;这段SQL把users表里的cart_items JSON数组展开成多行每一行对应数组里的一个元素虚拟表的列由COLUMNS子句指定。u.cart_items长的可能是这样[ {product_id: 101, price: 99.00}, {product_id: 205, price: 299.00} ]JSON_TABLE拆完后就变成两行虚拟数据可以直接参与后续过滤、排序、聚合。相比把JSON取出来在应用层解析至少在简单查询场景下JSON_TABLE极大减少了程序侧的工作量也让SQL本身具备了直接分析JSON数据的能力。需要注意JSON字段上没法像普通列那样直接建普通索引。如果经常要根据JSON里的某个键做过滤可以在MySQL 8.0.17及以后版本尝试多值索引或者用生成列把JSON里的关键字段提取出来建索引。JSON_TABLE不是银弹但在合适场景下用起来非常顺手。6. 实测效果对比优化前后执行时间差距6.1 测试环境与数据说明说了这么多理论我把这10种SQL在模拟环境里跑了一遍。环境信息如下MySQL 8.0.30存储引擎InnoDB单表数据量300万行测试机16核32G内存。模拟业务表和真实项目保持一致users用户表5万行orders订单表300万行包含用户ID、订单状态、订单金额、创建时间等字段。在跑测试之前我先把关键字段上的索引补齐了orders表的user_idorder_timestatus以及联合索引(user_id, order_time)。测试统一使用EXPLAIN ANALYZE查看执行计划保证结果有可比性。6.2 各SQL场景优化前后对照表场景优化前写法优化后写法优化前耗时优化后耗时用户最近订单相关子查询窗口函数3.2s85ms过滤未下单用户NOT INNOT EXISTS1.8s156ms多维度统计3次COUNT查询条件聚合2.1s780ms大偏移分页LIMIT 100000, 20延迟关联2.3s412ms深分页商品列表OFFSET分页游标分页1.4s95ms批量更新状态逐条UPDATEJOIN UPDATE循环8000次18s每日订单快照查写两条SQLON DUPLICATE2.5s480ms商品标签查询N1循环查询GROUP_CONCAT1.7s110ms分类层级导出程序递归查库递归CTE2.6s340msJSON订单项统计程序解析JSONJSON_TABLE分段统计1.9s从表格里可以很直观地看到优化前后的差距基本都在5倍以上有些场景甚至达到了40倍。延迟关联和窗口函数这两个技巧在订单表这种高频业务表上的提升尤其夸张。其实这个测试也不是为了证明某种写法天下无敌而是说明SQL性能优化没有玄学基本都能从扫描行数、回表次数、执行次数三个维度找到解释。数字背后的原理就是那几句话让数据库少干活。7. 常见问题与避坑经验7.1 最危险的三个坑第一坑版本意识。窗口函数和递归CTE需要MySQL 8.0以上JSON_TABLE也需要8.0以上。如果线上还是MySQL 5.7这些写法要么直接报语法错误要么行为异常。写高级SQL之前先确认集群版本支持否则代码写得再漂亮上线也是灾难。第二坑更新类语句必须双确认。JOIN UPDATE虽然快但一旦JOIN条件写错可能把无关数据全部更新掉。我的经验是任何生产环境的UPDATE和DELETE都先改成SELECT数一遍行数。执行之前再开一个事务用BEGIN包裹确认影响行数没问题后再COMMIT。宁可多花几秒确认不能赌运气。第三坑窗口函数的排序字段如果没索引MySQL会用文件排序。数据量一大很容易触发磁盘临时表不仅慢还占临时空间。先用EXPLAIN看清楚有没有Using filesort有的话优先优化索引。7.2 排查工具实用清单高级SQL不是写完就完了上线之后要会自己体检。我日常用的排查手段基本是三层EXPLAIN看执行计划的快速通道重点关注type列是否出现ALL全表扫描、rows列的估算值是否离谱、Extra里有没有Using filesort或者Using temporary。EXPLAIN ANALYZEMySQL 8.0.18以上版本才有能展示每步操作的实际执行耗时和扫描行数。比EXPLAIN的估算值准确得多排查慢SQL我优先用它。慢查询日志打开slow_query_log并把long_query_time设成0.5秒线上跑一段时间就能把真正需要优化的大鱼捞出来。生产环境上可以把慢查询日志和监控告警联动起来。某条SQL的执行时间一旦超过阈值就自动告警先把问题暴露出来再逐一分析。8. 一点个人经验这10种SQL写法我在不同项目里用过不止一遍。说一句可能不太中听的话性能再好的SQL也扛不住错误的使用场景。窗口函数再强你非要在没有索引的大表上做全量排序照样卡死递归CTE再优雅树形数据只有三层最优解还是简单的两表JOIN。SQL优化不是知识竞赛而是工程取舍。我最真实的体会是写完SQL先别急着提交花30秒看一眼执行计划想想数据库到底会怎么执行这一步放在哪一家公司都是性价比最高的优化习惯。这10种写法本质上是让我养成了“从数据库的角度思考问题”的习惯——我不关心自己写起来多顺手我关心InnoDB怎么扫描、怎么排序、怎么回表。想通这一点你离写出高性能SQL就不远了。如果这篇文章里的某一种写法帮你省下了几个小时或者解决了一个线上让人掉头发的慢查询那就值了。后续碰到有意思的SQL案例我再来接着聊。