MySQL高性能SQL写法：10种优化技巧让查询从3秒降到80毫秒

在做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返回NULL，COUNT自动忽略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种：写入场景的高性能SQL

4.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种：特殊场景下的高级SQL

5.1 GROUP_CONCAT：把多次查询合并成一次聚合

有这样一个场景：后台要给每个商品打标签，前端展示时需要把该商品的所有标签拼成一个字符串。最常见的实现是查出商品清单，再循环查询标签表，逐条拼接。商品一多，这个N+1问题就把接口拖垮了。

用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_len：

SET 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_TABLE：JSON字段直接当表查

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_id，order_time，status，以及联合索引(user_id, order_time)。测试统一使用EXPLAIN ANALYZE查看执行计划，保证结果有可比性。

6.2 各SQL场景优化前后对照表

从表格里可以很直观地看到，优化前后的差距基本都在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 ANALYZE：MySQL 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案例，我再来接着聊。