写LIMIT的文章我其实犹豫过要不要写因为它看起来实在太简单了。任何一个写过几天SQL的人都会用LIMIT 10都知道它能限制返回行数也都拿它做过TopN或者分页。但恰恰是这种人人都会的语法在实际生产环境里埋的坑最多。我见过把LIMIT 100000, 20当分页标配的接口也见过ORDER BY RAND() LIMIT 1抽奖直接把慢查询日志刷屏的线上事故。所以这篇不是教你怎么写LIMIT而是把LIMIT的语法细节、执行原理、分页优化和踩坑经验一次讲透适合刚学MySQL的初学者也适合写了好几年SQL但没深究过LIMIT行为的开发同学。1. LIMIT语法你真的吃透了吗三种写法与边界行为1.1 三种写法与一条等价规则先快速过一遍LIMIT的基础语法。MySQL里LIMIT有两种主写法第三种是MySQL 8.0才推荐使用的标准写法实际效果完全等价-- 写法一只限制返回行数 SELECT * FROM t LIMIT 10; -- 写法二跳过 offset 行再返回 count 行经典写法 SELECT * FROM t LIMIT 10, 20; -- 写法三skip 参数语义更清楚MySQL 8.0 起推荐 SELECT * FROM t LIMIT 20 OFFSET 10;很多人第一次接触LIMIT 10, 20时会犯错以为它是最多返回20条里的10条。实际上逗号前面的数字是偏移量后面的数字才是返回行数。也就是说LIMIT 10, 20的意思是跳过前10条从第11条开始取20条等价于LIMIT 20 OFFSET 10。这个顺序一旦记反分页接口直接错位。我个人的习惯是能写LIMIT n OFFSET m就不用逗号写法。不是因为它性能更好而是因为可读性。LIMIT 20 OFFSET 10一眼能看出来偏移是10、返回是20函数调用式的语义结构比逗号分隔清楚得多尤其代码评审的时候别人扫一眼就能确认你有没有写反。1.2 参数边界从0开始计数与超界行为LIMIT的几个边界行为很多人在试过之后才明白我先一次性列清楚OFFSET从0开始LIMIT 10 OFFSET 0才是第一页。如果你写LIMIT 10 OFFSET 1第一行数据会被跳过。OFFSET为负数直接报错MySQL会抛出You have an error in your SQL syntax之类的语法错误因为OFFSET必须是整型常量或表达式。返回条数超过剩余数据时不会报错有多少返回多少。比如表里只有5条你写LIMIT 100返回5条。返回条数为0LIMIT 0返回空集但注意这不是错误也不代表查询没执行。LIMIT后面的参数不只是数字字面量还支持用户变量、预处理语句占位符和存储过程参数。比如说你要做一个分页存储过程就可以这样写SET page_size 20; SET offset 40; PREPARE stmt FROM SELECT * FROM t ORDER BY id LIMIT ? OFFSET ?; EXECUTE stmt USING page_size, offset; DEALLOCATE PREPARE stmt;踩过的坑提醒一下预处理语句里LIMIT的参数如果绑定成字符串类型在MySQL 8.0某些版本上会报Incorrect arguments to mysqld_stmt_execute因为LIMIT要求整数参数。用PDO或者JDBC做预编译时要注意绑定参数的类型别默认绑成字符串。2. 分页核心深分页为什么慢以及三个能落地的优化方案2.1 深分页慢在哪里扫描与回表的双重代价LIMIT在生产环境里最容易翻车的地方就是分页。很多业务用这行SQL做分页SELECT * FROM t ORDER BY id LIMIT 100000, 20;表面上看它只返回20条数据怎么想都不该慢。但实测下来表里如果有几百万行这个查询可能要到几百毫秒甚至秒级。为什么会这样因为MySQL执行LIMIT时必须从满足条件的第一行开始扫描扫过100000行之后才开始取后面那20行。它不是通过什么跳跃机制直接跳到第100001行而是一行一行数过去的。更麻烦的是如果ORDER BY的字段不是主键而是走了二级索引那MySQL会先在二级索引上扫描每拿到一条索引记录还要回聚集索引查一次完整行。这等于说前100000行每一行都做了一次回表操作。二级索引和聚集索引的数据页是分散在磁盘上的100000次随机I/O累积下来不慢才怪。用个生活化的比喻这就像你要从一摞书里取第100001本只能一本本地数过去每数一本还要翻开确认里面内容数完10万本才能动手拿书。2.2 方案一延迟关联先取主键再回表第一个实用优化叫延迟关联思路是先通过覆盖索引拿到目标主键再用主键回原表取完整数据。这样前100000行的扫描全部在索引这个小表里完成不需要回表SELECT t.* FROM t INNER JOIN ( SELECT id FROM t ORDER BY id LIMIT 100000, 20 ) AS tmp ON t.id tmp.id;这个方案直接把二级索引扫描阶段的大量回表操作去掉了第一段查询只取主键ID在索引里扫10万行比在聚簇索引里扫10万行加回表快一大截。但要注意它只是缓解了回表开销并没有消除偏移扫描本身。真正的病根是那个OFFSETMySQL依然要数过10万行才能定位。2.3 方案二书签分页用WHERE id last_id替代OFFSET如果你想彻底干掉OFFSET只有一条路书签分页也叫键集分页。做法是不再依赖页码和偏移量而是记住上一页最后一条记录的某个唯一键值下一页直接从这个值往后查-- 上一页最后一条记录的 id 100000 SELECT * FROM t WHERE id 100000 ORDER BY id LIMIT 20;这个方案的关键在于MySQL用不上OFFSET它只需要在聚集索引上从id 100000的位置开始顺序扫描取够20条就停。不管翻到多深的页扫描的行数都固定在20行左右性能完全可控。实测上千万行的表翻到最后一页也就是毫秒级。代价是它不支持随机跳页只支持上一页/下一页这种点击式分页。用户想直接跳到第5000页做不到因为客户端没有维护那页的书签。所以业务上如果必须支持任意页码跳转可以用延迟关联兜底如果产品形态是瀑布流、列表翻页这种顺序浏览书签分页是性价比最高的方案强烈推荐。3. LIMIT叠加排序、去重与联表查询时的真实行为3.1 ORDER BY LIMIT索引排序与优先队列排序的博弈LIMIT单独用很单纯但它一旦和ORDER BY组合执行路径就变成了两类完全不同的玩法。第一类是排序字段能走索引。假设索引是idx_created_at(created_at)你执行SELECT * FROM t ORDER BY created_at LIMIT 10;MySQL可以直接在索引上按created_at有序扫描取到10条满足条件的记录就停止根本不需要把所有数据都排一遍序。这时候执行计划里的Extra不会出现Using filesortrows预估也会比较小查询非常快。第二类是排序字段没有索引。MySQL必须先对所有满足条件的行排序才能取前10条。但这里有一个很多人不知道的细节如果LIMIT的返回条数很小MySQL不会把全部数据排序后再丢弃而是用优先队列排序算法维护一个LIMIT n大小的最小堆只保留当前最小的n条记录遍历完所有数据后堆里就是最终结果。这个算法的时间复杂度接近O(n)内存占用也小代价是要多扫一遍数据但比全量排序后取前n条的做法高效太多。但是注意LIMIT越大这个优先队列排序的优势越弱。当你要取10万条中的5万条时它和全量排序的性能差距就不明显了。如果排序字段本身建立了索引那不管LIMIT多大索引扫描的优势都在。所以这类查询的核心优化手段还是给ORDER BY字段建合适的索引并进一步考虑覆盖索引让查询在索引里完成排序和截断。如果你的MySQL版本在8.0.31以上还可以用SQL标准写法FETCH FIRST n ROWS ONLY替代LIMIT效果等价读起来更贴近标准SQL习惯但团队没有统一规范时没必要特意迁移。3.2 LIMIT与DISTINCT/GROUP BY/JOIN/UNION的配合陷阱LIMIT与其它子句组合时语义往往会比直觉更复杂我挑四个典型场景说LIMIT DISTINCT先对所有行去重再取前N条。如果你以为LIMIT能提前截断去重范围就错了。比如有100万行、去重后有1万条LIMIT 10的结果是等扫描完足够多的行完成去重后才确定的。好在MySQL对DISTINCT的处理通常会借助索引去重但你不能指望LIMIT能显著降低去重本身的开销。LIMIT GROUP BYLIMIT作用于分组后的结果集不是组内的记录。SELECT user_id FROM t GROUP BY user_id LIMIT 10取的是前10个分组而不是每个组取10条。想取每个组的前N条要用窗口函数ROW_NUMBER()MySQL 8.0才支持。LIMIT JOINLIMIT在JOIN完成之后才生效。如果两张表是一对多关系某一条主表记录关联出100条明细那LIMIT 10取的是关联结果的前10行这些行可能全部来自同一条主记录。做每用户取一条需求时别直接靠LIMIT得先在子查询或窗口函数里做分组取数。LIMIT UNIONSELECT ... UNION SELECT ... LIMIT 10这个LIMIT是作用于整个UNION结果集的。如果你在两个SELECT里各写LIMIT 5UNION后再LIMIT 10语义完全不同。前者是各取5条合并后再取10条后者是合并后只取10条。另外MySQL对UNION子查询中的LIMIT有时会发生优化器改写这也是分布式分片场景下最头疼的地方——分库中间件改写LIMIT时必须把offset count放大到各分片去取再在内存里重新排序裁剪稍不注意结果就不一致。4. 用EXPLAIN快速判断LIMIT查询能不能走索引4.1 执行计划中关于LIMIT的三个关键信号我排查LIMIT慢查询时习惯固定看EXPLAIN输出里的三列检查项好信号坏信号key用上了二级索引或覆盖索引为NULL全表扫描rows预估扫描行数远小于表总行数和表总行数接近或为深OFFSET翻倍Extra没有filesort或filesort前的rows很小出现Using filesort 深OFFSET拿之前的深分页例子来看EXPLAIN SELECT * FROM t ORDER BY id LIMIT 100000, 20;如果key显示PRIMARY、rows显示100020说明优化器是在主键索引上有序扫描扫描行数基本等于OFFSET加返回行数这是可以预见的。但如果key显示某个普通索引、Extra出现Using filesort那说明排序和索引顺序对不上深OFFSET的影响会叠加排序开销问题更严重。还有一个快速判断技巧你可以在原SQL外面套一个EXPLAIN看rows和Extra然后把OFFSET改成0再看一次对比两个rows的差值。如果差值极大说明OFFSET是大头如果Extra一直是filesort说明排序才是瓶颈。4.2 LIMIT 0与派生表下推两个容易被忽略的细节有两个和小细节很多DBA都会用但很少在文档里看到明确解释。第一个是LIMIT 0。SELECT * FROM t LIMIT 0不会真的执行数据扫描但MySQL会为这条语句解析列结构。所以快速查看表结构或者在脚本里确认一条SQL能查出哪些列时会有人用它。实际上直接用DESC t还更直观但在某些动态SQL动态拼列的框架里LIMIT 0能零成本拿到结果集元数据。第二个是LIMIT在派生表里可能被优化器合并掉。MySQL 5.7以后默认对派生表做merge优化比如SELECT * FROM ( SELECT * FROM t ORDER BY id LIMIT 5 ) AS d LEFT JOIN t2 ON d.id t2.t_id;优化器有可能把派生表合并进外层JOIN导致子查询里那个LIMIT 5并没有先独立执行。这时候执行计划会变得很反直觉你以为先在内存里做了一次LIMIT 5的小结果集再去JOIN实际上MySQL把两层查询揉一起了。如果你需要“先LIMIT后JOIN”的严格语义可以用物化派生表SELECT * FROM ( SELECT * FROM t ORDER BY id LIMIT 5 ) AS d LEFT JOIN t2 ON d.id t2.t_id;并在SQL里通过/* DERIVED_CONDITION_PUSHDOWN */或者调整外层查询来避免合并行为。这一点在复杂报表SQL里遇到结果集对不上时可以作为排查方向之一。5. LIMIT翻车现场我踩过的坑与排查实录5.1 分页重复数据多列排序不稳定有个真实案例一个订单列表分页接口SQL写的是SELECT * FROM orders WHERE status 1 ORDER BY create_time LIMIT 20 OFFSET 0;翻页时用户发现第1页出现过的订单在第2页又出现了另一条订单反而消失了。原因不是LIMIT写错而是create_time字段有大量重复值。MySQL对排序值相同的行不保证稳定的返回顺序两次查询的物理扫描顺序可能因为并发变更、索引选择等因素发生变化而分页本质上是靠OFFSET跳过的排序不稳定就直接导致重复和遗漏。解决办法很简单排序条件里追加一个唯一键做次级排序SELECT * FROM orders WHERE status 1 ORDER BY create_time, id LIMIT 20 OFFSET 0;加了id兜底后排序顺序就完全确定了不管翻到哪一页结果都不会重不漏。这条经验对所有分页场景通用只要排序键里有重复值就一定补一个唯一列。5.2 ORDER BY RAND() LIMIT全表随机排序另一个高频翻车现场是随机取一条数据。不少新手写SELECT * FROM t ORDER BY RAND() LIMIT 1;这条SQL看着人畜无害实际执行时MySQL要为每一行生成一个随机数然后对这个随机数做全排序最后取第一条。表里有100万行它就要生成并排序100万个随机数性能极差几秒都很正常直接拖垮接口。替代方案有很多最简单的做法是SELECT * FROM t WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t))) ORDER BY id LIMIT 1;这个方案只查一次主键边界和一个范围查询速度比ORDER BY RAND()快几个数量级。缺点是有空洞时产生的随机分布不是完全均匀的但绝大多数业务场景下够用。如果系统表里数据量极大又对随机性要求很高可以在应用层先随机出一个ID列表再WHERE id IN (...)取数。5.3 LIMIT常见错误速查表现象根本原因解决方案分页数据错位LIMIT 10, 20理解成取10到20条实际是跳过10条取20条统一用LIMIT n OFFSET m显式表达深分页接口超时LIMIT 100000, 20扫描行数过大且伴随大量回表延迟关联或书签分页翻页出现重复/缺失数据排序键包含重复值MySQL排序不稳定排序条件追加唯一列idORDER BY RAND() LIMIT 1极慢每行生成随机数并全量排序改成WHERE id RAND() * max(id)范围内取值子查询的LIMIT失效派生表被优化器合并下推调整查询结构或使用物化派生表预处理语句绑定LIMIT参数报错参数类型绑定成字符串绑定为整数类型UNION后LIMIT结果不对没区分LIMIT作用于单个SELECT还是UNION整体明确LIMIT位置全局取数写在UNION最后我自己的习惯是上线前把所有带LIMIT的分页SQL都用EXPLAIN跑一遍重点看扫描行数是否合理、排序走没走索引。另外如果一个分页接口的OFFSET可能超过几十万就直接在产品层面限制最大页码与其让数据库硬扛不如在需求阶段就让用户用搜索条件来缩小范围。最后想分享一个改造技巧如果老系统里已经到处是LIMIT m, n这种深分页代码替换成本最低的方案是在应用层记录上一页的最大主键把SQL改成WHERE id ? ORDER BY id LIMIT ?接口返回值里多带一个last_id字段就行前端拿到的数据形态完全不变但数据库压力直接降一个量级。这个改动我做过很多次每次都立竿见影。