做后端开发这几年SQL里最不起眼又最常用的关键字LIMIT绝对排得上号。一个LIMIT就能解决数据量大了之后的展示问题但真正把LIMIT用明白的人其实不多。网上一搜SQL Limit用法出来的大多是limit 10、limit 0, 20这种抄来抄去的笔记真到了线上慢查询、分页翻到最后几页卡死的时候很少有人能说清楚问题出在哪。这篇文章我就从LIMIT的底层逻辑讲起把两种写法、分页计算、性能陷阱、和其他数据库的对比一次聊透。不管你是刚接触SQL的新手还是被线上分页接口折磨过的老手都能从里面找到能直接用的经验。1. LIMIT基本语法两种写法与三个隐藏细节1.1 两种写法的本质区别LIMIT在MySQL里有两种标准写法看起来差不多实际使用场景略有差异。-- 写法一偏移量 行数 SELECT * FROM users ORDER BY id LIMIT 10, 20; -- 写法二行数 OFFSET SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 10;写法一里LIMIT 10, 20表示跳过前面10行从第11行开始取20行。写法二更直观一点LIMIT 20 OFFSET 10同样是跳过10行取20行只是把偏移量放到了后面。从我日常使用的习惯来说写法一输入更快但可读性稍差。写法二在代码评审时更友好因为OFFSET这个关键字把跳过多少行这个意图表达得很明确。团队协作的项目里我会倾向于要求统一使用写法二减少阅读成本。1.2 参数行为正数、负数与超大值很多人不知道LIMIT的参数在不同数据库里行为差异很大。MySQL里最值得记住的规则LIMIT后的两个参数都必须是整数常量正负号有讲究第一个参数偏移量如果是负数MySQL会报语法错误第二个参数行数如果是负数MySQL会返回所有剩余行。-- 返回第11行到最后一行的所有记录 SELECT * FROM users ORDER BY id LIMIT 10, -1;这条冷知识在某些特殊场景很实用。比如你要导出某张表从某个位置往后的全部数据又懒得先统计总数直接给行数传个-1就能搞定。但这里有个坑同一个语句在不同版本里行为可能不一致。MySQL 8.0仍然支持负数行数表示取到末尾但如果你把代码迁移到PostgreSQL这个写法直接报错。生产环境里我建议别依赖这类边界行为规规矩矩写一个足够大的数值或者用其他方式实现更稳妥。1.3 细节LIMIT 0、与ORDER BY的搭配LIMIT 0是个特别有意思的写法它一个数据都不返回但SQL语法完全合法。很多人会用EXPLAIN SELECT ... LIMIT 0来快速判断一张表的索引情况因为LIMIT 0让优化器直接略过数据扫描执行计划特别干净。另一个经典场景是快速建表CREATE TABLE users_backup AS SELECT * FROM users WHERE 1 0;如果你只想复制表结构而不想复制数据WHERE 1 0或者配合LIMIT 0都是常用手段。LIMIT和ORDER BY是强绑定关系。分页查询如果少了ORDER BY返回顺序是不确定的。MySQL表数据在InnoDB里默认按主键聚簇存放但查询走了不同索引时物理顺序就不同了。没有ORDER BY的分页你第一页看到的记录和第二页的记录之间完全可能发生重复或者遗漏。提示LIMIT从不过滤数据它只是切取一段已经排序好的结果集。排序是靠ORDER BY完成的务必记得两者一起用。2. 分页场景下的LIMIT从入门到正确的分页姿势2.1 三种常见分页计算方式LIMIT最核心的应用就是分页。页面的第N页换算成SQL里的OFFSET有几种常见做法很容易被搞混。方式一偏移量从0开始这是后端开发最常用的约定-- 每页20条第一页 SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET 0; -- 第二页 SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET 20; -- 第N页 SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET (N - 1) * 20;方式二从当前页最后一条记录的ID继续-- 第一页 SELECT * FROM articles WHERE id 0 ORDER BY id LIMIT 20; -- 第二页传入上一页最后一条ID SELECT * FROM articles WHERE id 100 ORDER BY id LIMIT 20;这种方式不叫传统分页叫游标分页或者键集分页后面的性能章节我会细讲。这里你先记住一个结论数据量大了之后方式二比方式一快得多。实际开发中还有个容易踩的坑前端展示的页码从1开始后端计算offset (page - 1) * size如果后端误把页码传给了OFFSET第一页永远显示的是第2条到第21条。这种Bug多半不是SQL写错而是边界计算没对齐建议在接口层统一封装分页参数别让SQL层直接接收前端传值。2.2 分页与COUNT(*)分页接口通常要返回总条数这就逼着你在查询数据之外再跑一条SELECT COUNT(*)。SELECT COUNT(*) FROM articles WHERE status 1; SELECT * FROM articles WHERE status 1 ORDER BY id LIMIT 20 OFFSET 0;这两条SQL在数据量大时都会慢但慢的原因不一样。COUNT(*)需要扫描满足条件的所有记录并计数而LIMIT的慢更多来自偏移量增大后的回表开销。很多优化方案可以复用索引却没办法省掉COUNT(*)的成本。于是有人想了个取巧的办法用LIMIT估算总数比如查LIMIT 1000如果返回了1000条就显示超过1000条否则返回实际条数。这个方案在列表展示场景足够用能省掉一次大查询的损耗代价是精确性没了。我的建议是管理后台这种低频场景老老实实COUNT(*)C端高并发列表接口再用估算策略。2.3 排序字段不唯一导致的分页错乱这个坑我印象很深。早期做订单列表分页时我用ORDER BY create_time LIMIT 20 OFFSET 0取第一页用LIMIT 20 OFFSET 20取第二页结果第二页的第一条数据跟第一页的最后一条重复了。原因很简单create_time字段在表里不是唯一的同秒内可能有几十条订单。数据库的排序只保证create_time这个维度有序create_time相同的记录之间顺序是不稳定的。第一页取完20条第二页重新执行查询时同秒记录的顺序可能已经变了。解决办法一句话排序字段必须唯一至少组合起来唯一。要么直接按主键id排序要么用ORDER BY create_time DESC, id DESC。主键参与排序之后每一条记录的位置就完全确定了分页才不会被重复和遗漏困扰。我在生产环境的标准做法所有分页SQL的ORDER BY一律加上主键兜底。即使业务上只需要按时间排序也会写成ORDER BY create_time, id。这个习惯帮我省掉了大量页面数据跳来跳去的Bug。3. 分页慢的根源大偏移量LIMIT的性能瓶颈3.1 为什么LIMIT 100000, 20会很慢这是LIMIT经典性能问题。一张表里有2000万条数据你要取第100001页的20条记录SQL这么写SELECT * FROM articles ORDER BY id LIMIT 100000, 20;你以为数据库只查20条实际上它做了这样的工作从第一行开始扫描找到所有满足条件的记录按ORDER BY排序如果有从第1行数到第100000行全部丢弃留下第100001到第100020行返回。也就是说偏移量越大扫描和丢弃的行越多。即使你有完美的索引数据库也没法直接跳到第100001行只能一行一行数过去。这个过程的CPU和IO开销是实打实的不会因为你写了LIMIT就变少。对比一下LIMIT 20 OFFSET 0几乎秒开LIMIT 200000, 20可能要几百毫秒LIMIT 2000000, 20直接卡死。这种问题在数据量上百万之后就非常明显如果你负责的是千万级流水表传统分页撑死翻个几百页就扛不住了。3.2 方案一延迟关联延迟关联是解决大偏移分页最实用的手段之一思路是先查主键再查全行数据。-- 普通写法慢 SELECT * FROM articles ORDER BY id LIMIT 200000, 20; -- 延迟关联快 SELECT * FROM articles WHERE id IN ( SELECT id FROM articles ORDER BY id LIMIT 200000, 20 );你可以把它理解成少搬点东西。子查询只查id列一个整型字段在索引里就有不需要回表读整行数据。MySQL扫描索引拿到20个id之后再用IN去聚簇索引里精确取这20行的完整数据。这样做的效率提升来自两个方面一是索引扫描比全表扫描快得多二是真正需要搬运的行从二十万行都被读取再丢弃变成了只精确读取20行。实测在百万级表上这个改写通常能带来10倍以上的性能提升。不过要注意子查询里LIMIT 200000, 20还是会扫描二十万个索引项。也就是说延迟关联改善了回表读取的开销但没有根治扫描大量偏移量的问题。数据量继续膨胀到千万级这个方案也会衰减。3.3 方案二游标分页 / 键集分页如果说延迟关联是治标游标分页就是治本。它的核心思想是不通过偏移量定位而是通过上一页最后一条记录的排序值定位。-- 第一页取id最大的前20条 SELECT * FROM articles WHERE id 0 ORDER BY id LIMIT 20; -- 第二页记住上页最后一条ID100直接从100之后取 SELECT * FROM articles WHERE id 100 ORDER BY id LIMIT 20; -- 第三页 SELECT * FROM articles WHERE id 300 ORDER BY id LIMIT 20;这个写法为什么快因为WHERE id 100直接利用了主键索引的B树查找能力数据库能瞬间定位到id101的位置然后顺序向后取20条。它面对的扫描量永远只是20条记录的大小与总数据量无关与翻到第几页也无