写SQL这些年LIMIT是我用得最频繁的关键字之一。查前10条、分页查列表、取某个条件下的Top-N几乎每个业务系统都离不开它。但说实话LIMIT看着简单真正用明白的人不多。我见过同事写分页SQL直接LIMIT 100000, 20页面一慢就甩锅给数据库也见过在子查询里滥用LIMIT导致整条SQL性能崩塌的案例。这篇文章不聊架构就踏踏实实把LIMIT在数据查询里的用法、性能原理、分页优化和常见坑一次说清楚新手可以照着学老手当查漏补缺。1. 先搞明白LIMIT的本质它到底是怎样工作的1.1 一条带LIMIT的SQL数据库到底做了什么很多人以为LIMIT是先查出满足条件的所有数据再截断前N条。数据库没那么傻。以InnoDB为例执行器从存储引擎一层层取数据时取到第N条满足条件的记录后就会通知存储引擎够了别再传了。这个机制叫短路LIMIT高效的根本原因就在这里。但这有个前提如果查询本身需要全表扫描引擎依然要从第一行开始找只是找到足够数量的行后提前结束扫描。什么场景下会全表扫描最常见的就是WHERE条件没有索引或者优化器认为走索引不如全表扫。举个例子一张100万行的日志表执行SELECT * FROM t WHERE msg LIKE %error% LIMIT 10即使LIMIT只取10条满足匹配条件的行可能排在很后面MySQL也得把前面大量数据扫一遍LIMIT的提前终止在这里帮不上太大忙。理解这一点你就能明白LIMIT优化的是取数据的过程而不是找数据的过程。找数据靠的是索引一个没有索引的查询LIMIT无论写得多小都救不回来。这也是为什么很多慢SQL最后排查下来问题不在LIMIT而在索引设计。1.2 边界情况和容易被忽略的写法细节LIMIT的完整语法是LIMIT [offset,] row_count。LIMIT 5, 10表示跳过前5条返回接下来的10条。另一种等价写法是LIMIT 10 OFFSET 5语义完全一样PostgreSQL风格迁移过来的同事经常用这种写法。几个容易被忽略的细节LIMIT 0不报错直接返回空结果但SQL依然会经过解析和优化器。有些人调试时用LIMIT 0看执行计划这是浪费直接EXPLAIN才是正道。LIMIT后面的数字必须是整数常量或预处理语句参数。MySQL不支持在SQL文本里写LIMIT offset, size直接引用变量预处理语句里用LIMIT ?, ?是可以的这一点和SQL Server的OFFSET n ROWS不一样容易踩坑。在IN子查询里直接使用LIMIT老版本MySQL会直接报错。比如WHERE id IN (SELECT id FROM t ORDER BY id LIMIT 3)MySQL会说This version of MySQL doesnt yet support LIMIT IN/ALL/ANY/SOME subquery。解决办法是先包一层派生表后面我会单独讲。1.3 LIMIT不是先查出来再扔它影响的是执行计划LIMIT的存在本身就会改变优化器的执行策略。比如ORDER BY ... LIMIT NMySQL会采用优先队列排序一个大小为N的小顶堆遍历一遍就能拿到Top-N并不会把全部数据完整排序再截取。这是server层一个非常经典的优化行为。同时LIMIT还会影响关联查询的执行计划。有的情况下优化器会因为LIMIT较小选择先查驱动表然后逐行去探查被驱动表的方式而不是先做完整的哈希连接再截断。好处是减少了很多无谓计算坏处是如果探查路径不是最优反而可能变慢。这个坑我在第4章细讲。理解了LIMIT的执行本质你写SQL的时候就会多想一层这个LIMIT能不能借助索引的有序性提前终止扫描比如SELECT * FROM t WHERE status1 ORDER BY id DESC LIMIT 10如果status和id能组成一个复合索引MySQL可以在索引树上直接定位到满足status1的最大id值往下扫10条就结束回表次数也只有10次性能非常漂亮。如果只有status单列索引排序还得单独做效率就差了不止一个量级。2. 分页查询LIMIT最核心的应用场景2.1 经典分页写法与背后的执行逻辑管理后台的分页SQL十有八九长这样SELECT * FROM orders WHERE status 1 ORDER BY id DESC LIMIT 40, 20;offset40limit20意思是跳过前40条满足条件的记录从第41条开始取20条。前端传入pageIndex和pageSize后端算offset (pageIndex - 1) * pageSize然后拼进SQL。MyBatis-Plus、PageHelper、Spring Data JPA的Pageable底层生成的基本都是这个套路。这套写法的核心生理结构是MySQL必须扫描并丢弃offset条记录才能开始返回真正需要的数据。也就是说翻到越后面的页丢弃的数据越多。一页20条翻到第5000页时offset是100000MySQL要先把前100000条不需要的数据扫描一遍再扔掉磁盘IO和CPU开销全花在了丢弃上。2.2 深分页为什么会慢到怀疑人生我在一个亿级订单表上实测过-- 无优化的情况下 SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;这条SQL耗时接近4秒。而换一种写法SELECT * FROM orders WHERE id 100000 ORDER BY id ASC LIMIT 20;耗时直接降到30毫秒以内。同样都是返回20条数据性能差了一百多倍。原因很简单第一条SQL要扫描10万行再扔掉第二条SQL利用主键索引直接定位到id100000的位置往后只扫20行。这个思路就是业界常说的键集分页也叫游标分页、seek分页。适用场景很明确用户不断往下翻页的信息流、流水列表、日志查看器。只要产品上不需要跳转到任意页码键集分页基本是首选。2.3 什么时候用传统分页什么时候用游标分页先说结论数据量在几万条以内、用户访问深度浅的管理后台传统分页完全够用数据量上了百万、用户可能会翻几十上百页的场景赶紧换游标分页。游标分页有两种方向正序WHERE id #{lastId} ORDER BY id ASC LIMIT 20倒序WHERE id #{lastId} ORDER BY id DESC LIMIT 20如果排序条件不止一个字段比如ORDER BY create_time DESC, id DESC游标条件要写成行值比较SELECT * FROM orders WHERE (create_time, id) (#{lastCreateTime}, #{lastId}) ORDER BY create_time DESC, id DESC LIMIT 20;注意行值比较是逐列从左到右比较的MySQL 8.0支持这种写法很多人不知道宁可用DATE(create_time) ?这种让索引失效的写法非常可惜。还有一个折中方案叫延迟关联。当排序和过滤字段复杂、没法直接用游标时先把本页需要的主键id查出来再回表取完整数据SELECT * FROM orders WHERE id IN ( SELECT id FROM orders WHERE status 1 ORDER BY id DESC LIMIT 40, 20 );比直接大offset分页快很多因为内层子查询只查id可以走覆盖索引外层才针对20个id回表。这个方案适合没法改前端产品逻辑、必须支持随意跳页的老系统。另外提醒一句分页查询通常还要COUNT(*)算总页数。很多人没意识到大表上的COUNT可能比分页本身还慢。如果产品上只需要上一页/下一页没必要精确显示总页数可以直接去掉COUNT如果必须有考虑用近似值或者缓存预计算。这是我在实际项目中一次优化里最大的意外收获。3. LIMIT在高级场景中的实战经验3.1 Top-N查询的多种姿势不只是ORDER BY LIMIT除了分页LIMIT最常见的用途是取Top-N。比如查销量最高的10个商品SELECT * FROM products ORDER BY sales DESC LIMIT 10;前面说过MySQL对这个语句会使用优先队列排序复杂度比全排序好很多数据量越大优势越明显。但有个前提排序字段没有索引时MySQL仍然需要把所有满足条件的行都读出来在内存或临时文件中建立排序堆只是最终只保留N条。如果满足条件的数据行数是几百万这个读出来的过程本身就慢。所以要进一步提升Top-N查询性能优化重点还是在索引。ORDER BY字段有索引时MySQL可以顺着索引有序扫描边扫边收集结果收集到N条后直接停止。这是极限情况下最理想的Top-N执行方式但前提是WHERE过滤条件也能同时落到索引上。我通常在(status, sales DESC)这种方向建复合索引一个Top-N查询就变得非常轻快。还有一类是分组Top-N比如每个品类销量前三的商品。这个LIMIT做不了MySQL 8.0以下要用用户变量模拟8.0之后用窗口函数SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn FROM products ) t WHERE rn 3;虽然窗口函数里没有LIMIT但ROW_NUMBER() OVER (PARTITION BY ...)的执行逻辑本质上也是一种短路每个分组只数到3行就够了不会有无限排序的负担。3.2 LIMIT与ORDER BY、GROUP BY、DISTINCT的配合陷阱有一个坑非常隐蔽排序字段有重复值时LIMIT返回的行顺序不稳定。比如商品价格都是100元的有10件商品执行ORDER BY price LIMIT 5MySQL按price排序时这10件商品内部顺序没有保证两次查询可能返回不同的5件。前端展示列表时就会出现第一页最后一条和第二页第一条重复或某条数据凭空消失的情况。解决办法是排序字段后面追加唯一字段SELECT * FROM products ORDER BY price DESC, id DESC LIMIT 5;加了id DESC之后排序就完全确定了分页结果自然稳定。LIMIT和DISTINCT配合时要特别注意SELECT DISTINCT name FROM users LIMIT 10DISTINCT是全结果集去重后才截断LIMIT的短路优势在去重面前基本被削弱因为MySQL要先把所有不同的name找出来才能确定前10条。数据量大、去重字段又没有索引时这个语句会非常慢。我的建议是如果NAME字段上有索引DISTINCT可以利用索引有序扫描快速去重LIMIT 10时只需扫索引的前面一部分。LIMIT和GROUP BY配合时也有类似情况。优化器有时会把LIMIT下推到分组之前从而避免对所有分组做完整聚合。能不能下推取决于分组字段是否有序。如果GROUP BY的字段有索引下推概率大如果分组字段没索引通常只能完整分组后再截断。所以别小看一个索引它对LIMIT执行策略的影响是全方位的。3.3 在UPDATE和DELETE中使用LIMIT线上维护的利器LIMIT不只用在SELECT里。MySQL的UPDATE和DELETE语句也支持LIMIT这在清理历史数据、批量更新状态时非常实用。举个例子线上表不能一次性删太多数据否则会锁大量行、拖慢主从同步、引发业务抖动。我习惯用分批删除DELETE FROM app_logs WHERE create_time 2024-01-01 LIMIT 500;应用层循环执行这条SQL每次删除500行直到影响行数为0。每次操作都在一个很小的事务里锁范围小对线上影响完全可控。UPDATE同理UPDATE users SET vip_level 2 WHERE vip_level 1 LIMIT 1000;这个场景常见于给一批用户发放权益不能一次性更新几十万行分批处理既安全又可以观察进度。再进一步DELETE和UPDATE还能和ORDER BY一起用实现删最小的N条或改最老的N条。比如清理分数最低的5条记录DELETE FROM scores ORDER BY score ASC LIMIT 5;我曾经写过一个定时任务用于清理最老的一批未支付订单核心就是DELETE FROM orders WHERE status0 ORDER BY create_time ASC LIMIT 100每次只删100条配合循环执行效果很好。注意这种写法一定要确认ORDER BY字段有索引否则全表排序再删100条代价会非常大。另外一个细节在存储过程中循环执行UPDATE ... LIMIT 1时务必在循环里检查ROW_COUNT()并且让WHERE条件在每次更新后变化否则可能死循环更新同一条记录。我见过一次线上事故就是循环脚本没更新条件一条SQL反复执行某字段被来回改写直接把数据弄坏了。4. 常见问题与排查技巧实录4.1 为什么加了LIMIT反而变慢这是最反直觉的坑。某些情况下LIMIT的存在会改变优化器的执行计划而且改得不一定更好。我遇到过一个真实案例一条关联统计SQL本来不加LIMIT跑150ms在子查询外面套了个LIMIT 10后变成500ms。用EXPLAIN一对比发现优化器因为LIMIT而选择了不同的驱动表和连接顺序type从range变成了ALL还多了临时表。排查思路很简单分别EXPLAIN带LIMIT和不带LIMIT的SQL对比执行计划的type、possible_keys、key、rows。如果发现LIMIT让索引选择变了或者驱动表变了就要考虑强制索引FORCE INDEX或者改写SQL。另外优化器的选择是成本模型估算的结果不是绝对的遇到性能异常别猜直接用EXPLAIN看。4.2 分页跳页时出现数据重复或丢失怎么办这个问题的根源绝大多数是排序不稳定我在3.2节已经说过。线上最常见的场景是订单列表按pay_time DESC分页同一秒内有多笔支付订单。因为pay_time重复MySQL返回这些订单的相对顺序没有保证第1页最后一条和第2页第一条可能就是同一笔订单用户会看到重复数据。排查和解决思路排序字段追加唯一键ORDER BY pay_time DESC, id DESC改成键集分页以上一页最后一条的(pay_time, id)作为边界条件如果用的是ORM框架检查是否被框架改动过排序另外如果表里某些数据被并发更新分页也可能出现跳变翻下一页时某条记录因为排序字段被更新而移走这是业务数据本身的正常现象不是Bug但产品上要能容忍这种变化。键集分页在这个问题上比offset分页更稳定因为它锁定了上一次的位置。4.3 一个高危操作LIMIT导致的全表扫描慢日志里高频出现的这类SQLSELECT ... FROM t WHERE deleted 0 ORDER BY gmt_create DESC LIMIT 0, 10如果deleted和gmt_create都没有索引即使LIMIT只取10条MySQL也要扫描全表再做排序大数据量下几乎必然慢查询。尤其这种逻辑删除标记时间排序的组合在业务表里太常见了一定要建联合索引。正确设计索引时要注意字段顺序ALTER TABLE t ADD INDEX idx_deleted_create (deleted, gmt_create, id);这个复合索引能让MySQL在索引树上先按deleted0过滤再按gmt_create有序扫描取到10条后立即停止回表。记住索引的字段顺序不能乱等值条件的字段放前面排序字段放后面。如果业务上不同的deleted值分布极不均匀有时候优化器会认为全表扫描比索引更便宜这时可以考虑FORCE INDEX或者调整统计信息。4.4 子查询中LIMIT的奇怪报错与规避方法老版本MySQL中IN子查询里直接用LIMIT会报错我前面提过。报错信息是This version of MySQL doesnt yet support LIMIT IN/ALL/ANY/SOME subquery解决办法是包一层派生表SELECT * FROM users WHERE dept_id IN ( SELECT id FROM ( SELECT id FROM departments ORDER BY id LIMIT 3 ) t );这个多包一层的方式在MySQL 5.7以上基本都能解决问题。不过要注意包了派生表之后优化器可能将派生表物化执行效率不一定高。如果数据量不大问题不大数据量大时更推荐改成JOIN写法SELECT u.* FROM users u INNER JOIN ( SELECT id FROM departments ORDER BY id LIMIT 3 ) d ON u.dept_id d.id;另外提醒一下MySQL对LIMIT的解析相对宽松但其他数据库不一定。比如PostgreSQL支持LIMIT ALLMySQL不支持SQL Server用OFFSET ... FETCHOracle用ROWNUM或FETCH FIRST。如果项目有跨数据库迁移计划LIMIT差异一定要提前标注我在一个从PostgreSQL迁移到MySQL的项目里就踩过LIMIT ALL的坑。4.5 分库分表场景下LIMIT被改写的坑最后补充一个分布式数据库场景。用ShardingSphere这类中间件做分库分表时分页查询的LIMIT会被改写。比如逻辑表分10个库一条LIMIT 100000, 20的SQL中间件需要从每个分片都查出10000020100020条然后归并排序再丢弃前100000条最后取20条返回。这个放大效应非常恐怖数据分布在10个分片实际扫描量接近单库的10倍。这就是为什么在分库分表架构里更推荐用排序键游标的分页方式或者干脆不用深分页。之前有同事在分片环境下翻到2000页直接把后端打挂了后来改成基于主键的游标翻页问题才彻底解决。另外ShardingSphere中GROUP BY和LIMIT同时出现时也会对LIMIT做改写热搜词里就有sharding groupby改写了limit这个说法原理是分布式场景下必须把分组聚合提前LIMIT才能正确生效代价是中间结果集被放大。所以在分片环境下宁可在应用层做聚合也别把复杂的GROUP BYORDER BYLIMIT全部交给中间件。回到根本LIMIT本身不复杂复杂的是它和索引、排序、执行计划的互动关系。我在实际项目里有一个习惯凡是写分页或者Top-N查询一定先EXPLAIN看一眼再上线。执行计划里rows如果远大于LIMIT本身说明中间扫描的量太大大概率有优化空间。别怕多这一步很多线上慢查询本质就是当初少看了一眼执行计划。最后再分享一个小技巧排查LIMIT相关慢查询时别只盯着SQL本身把SHOW ENGINE INNODB STATUS里的TRANSACTIONS段和SHOW PROCESSLIST打开看看这条SQL是不是被锁等待卡住了。我遇到过不止一次数据量不大、索引也有、LIMIT也小但就是慢最后发现是另一条长事务把要读的行锁住了LIMIT查询排队等了十几秒。这种问题SQL再优化也没用得先治理事务的持有时间。LIMIT这个关键字你越是把它放到整个数据库的运行环境里去理解越能用得顺手。