1. 先把语法记牢LIMIT 的两种写法和常见数据库差异LIMIT 看起来就是“截断结果集”这么简单但实际用起来不同数据库、不同写法、不同参数语义之间还是有很大差别的。我在帮业务方排查一个后台报表接口时看到同事写了这么一句SELECT * FROM orders ORDER BY created_at DESC LIMIT 200000, 50接口卡了 12 秒他以为是索引问题其实一半是 LIMIT 大偏移量带来的典型开销另一半是他把逗号写法和 OFFSET 写法混在一起用自己都没意识到这个查询到底在取哪一段数据。所以先把最基础的语法彻底搞清楚后面所有变体才不容易翻车。1.1 五个必须记住的语法变体第一种是 MySQL、PostgreSQL、SQLite 都支持的LIMIT offset, count形式SELECT * FROM employees LIMIT 10, 5;这里10是偏移量5是返回行数。意思是从第 11 行开始取 5 行。注意偏移量的计数是从 0 开始的不是从 1 开始所以LIMIT 0, 5才是取前 5 行而不是LIMIT 1, 5。这个 0 起始的设定是新手最容易踩的第一个坑。第二种是标准风格LIMIT count OFFSET offsetSELECT * FROM employees LIMIT 5 OFFSET 10;结果和上面那条一模一样但可读性明显更好一眼就能看出LIMIT后面跟的是“要多少行”OFFSET后面跟的是“跳过多少行”。我个人的习惯是永远用这种形式逗号版本能不用就不用。第三种是 SQL Server 和 Oracle 12c 以上支持的OFFSET ... FETCHSELECT * FROM employees ORDER BY id OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;注意SQL Server 里ORDER BY不是可选项而是必选项没有ORDER BY直接写OFFSET ... FETCH跑都跑不起来。这一点和 MySQL 很不一样跨库迁移时容易一头撞上。第四种是 Oracle 老版本12c 之前12c 开始支持 FETCH FIRST常用的ROWNUM方案SELECT * FROM ( SELECT e.*, ROWNUM AS rn FROM employees e ) WHERE rn BETWEEN 11 AND 15;ROWNUM 是在查询结果生成时分配的伪列不能直接对ROWNUM写大于某个值所以必须包一层子查询。老系统里看到这种写法基本都是 Oracle 11g 以下的遗留代码。第五种是标准 SQL 里的FETCH FIRST n ROWS ONLY新版本数据库大多开始兼容SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;DB2、Oracle 12c、PostgreSQL 13 都能用。如果团队里有多套数据库写这种标准语法迁移成本最低但要注意它不带偏移量只做“取前 N 条”。1.2 LIMIT 的 offset 和 count 到底在算哪几条数据用一个具体例子说明。假设employees表里有 20 条记录id 从 1 到 20SELECT * FROM employees LIMIT 5 OFFSET 5;偏移量 5 表示跳过前 5 条从第 6 条开始取一共取 5 条所以拿到的 id 是 6、7、8、9、10。偏移量 5 对应的是表中按当前排序方式的第 6 行因为底层行号从 0 开始计数。再比如取第二页数据每页 10 条第一页LIMIT 10 OFFSET 0第二页LIMIT 10 OFFSET 10第三页LIMIT 10 OFFSET 20。这里就容易出现一个常见的语义混淆业务上说的“第 2 页”和程序里的offset (page - 1) * pageSize是配套的如果直接把页码当成 OFFSET 传进去第二页就会变成从第 2 条开始取完全不搭边。1.3 不同数据库的 LIMIT 语法速查对照数据库取前 N 条跳 M 条取 N 条MySQLLIMIT NLIMIT N OFFSET MPostgreSQLLIMIT NLIMIT N OFFSET MSQLiteLIMIT NLIMIT N OFFSET MSQL Server 2012SELECT TOP (N) ...ORDER BY ... OFFSET M ROWS FETCH NEXT N ROWS ONLYOracle 12cFETCH FIRST N ROWS ONLYOFFSET M ROWS FETCH NEXT N ROWS ONLYOracle 11g-WHERE ROWNUM N子查询加 ROWNUM这里特别提醒一下SELECT TOP (N)在 SQL Server 里只能取前 N 条没有办法天然跳过偏移量要跳必须配OFFSET ... FETCH。而 MySQL 里也没有TOP关键字从 SQL Server 迁移过来的代码直接复制是跑不过的。2. 别忽略 ORDER BY取前 N 条和分页真正要排序刚接触 LIMIT 的人最容易产生一个错觉LIMIT 10就是返回“前 10 条”。问题是到底哪 10 条算“前”这完全取决于排序规则。2.1 不带 ORDER BY 的 LIMIT 结果不稳定很多人写查询为了图省事会这么写SELECT * FROM employees LIMIT 5;这条 SQL 的返回结果在同一个数据库里很可能是随机的。原因在于数据库引擎在处理没有ORDER BY的查询时可能走全表扫描可能走索引扫描也可能并行读取多个数据块后按任意顺序合并。数据量变大、执行计划发生变化甚至表上新增了索引都会导致同样的 LIMIT 语句返回不同的行。这就像往兜里抓一把糖每次抓出来的颗粒顺序都不完全一样。如果只是看一眼数据结构影响不大但如果把它当成业务数据用比如“最近的 5 条订单”结果很可能漏数据或者重复。任何生产环境里的分页、Top-N 查询都必须老老实实配ORDER BY。2.2 ORDER BY 的排序列必须唯一就算加了ORDER BY还有一个更隐蔽的坑排序字段如果不唯一分页依然可能出现重复或漏行。我举一个真实案例。有个订单表后端分页查询原本是这样写的SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;第一页没问题第二页LIMIT 20 OFFSET 20跑出来以后有一行数据在第一页和第二页里都出现了。原因很简单表里有多条订单的created_at完全一样它们谁在前谁在后数据库内部没有保证每次执行都可能不一样。第一页返回了 id101第二页排序时又把 id101 排进来了。解决思路是给 ORDER BY 追加一个绝对唯一的次要排序列最常用的就是主键SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20;只要主键唯一(created_at, id)这个二元组就绝对唯一排序就稳定分页才不会出现莫名其妙的重复和漏行。这是我处理分页问题时的最低标准所有线上分页 SQL 的 ORDER BY 末尾一定带主键。2.3 取“最近一条”和“工资前三名”的实操写法取最新一单的写法SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 1;这时LIMIT 1的意思是“在排好序的结果集里取第一行”。因为数据库只需要找到第一条理论上配合created_at索引可以走索引扫描到第一条直接返回效率很高。取工资最高的三名员工SELECT * FROM employees ORDER BY salary DESC, id ASC LIMIT 3;这里我把主键id作为第二排序键避免两个员工工资完全一样时到底取哪三个变得不可控。3. 分页参数怎么定页码、页大小和游标方案LIMIT 最主流的应用场景是分页。但分页并不只有“上一页下一页按钮”这一种形态不同交互方式对应不同的 SQL 设计参数的计算方式也完全不同。3.1 页码转 OFFSET 的标准公式传统分页组件通常会传两个参数进来currentPage当前页从 1 开始和pageSize每页条数。SQL 里需要的偏移量是offset (currentPage - 1) * pageSize比如每页 20 条第 3 页的偏移量是(3 - 1) * 20 40也就是跳过前 40 条取第 41 条到第 60 条。业务上说的“第 3 页”在 SQL 里是OFFSET 40因为底层行号从 0 开始。在实际编码时我建议所有分页 SQL 都用预编译参数传递避免把页面传入的值直接拼进 SQL 字符串里SELECT * FROM employees ORDER BY id LIMIT ? OFFSET ?;Java 侧的参数绑定大概是这样的思路int offset (currentPage - 1) * pageSize; preparedStatement.setInt(1, pageSize); preparedStatement.setInt(2, offset);如果是从前端接口直接接收参数后端一定要校验currentPage 1、pageSize在合理范围内否则currentPage0时偏移量变成负数数据库会直接报错或者某些数据库会把负数当 0 处理结果全是错的。3.2 总页数怎么算才靠谱分页组件一般还要显示“共 23 页”或者“共 450 条”这里通常需要单独跑一条 COUNT 查询SELECT COUNT(*) FROM orders WHERE status PAID;然后用总条数除以页大小向上取整totalPages (totalRows pageSize - 1) / pageSize注意不要用totalRows / pageSize 1这种公式整除时容易多出一页。(total pageSize - 1) / pageSize是经典的向上取整写法用整数运算就能搞定。在数据量大到一定程度后COUNT 本身也会变慢。如果业务允许可以在列表接口里只返回“是否有下一页”比如查询LIMIT pageSize 1如果多取到一条说明下一页存在这样能省掉一次 COUNT这也是很多 C 端列表接口的常见优化。3.3 无限滚动场景别再用 OFFSET直接记游标现在很多 App 的列表不是“点击第几页”而是上拉加载更多。这种场景再用页数和 OFFSET 会很别扭因为用户向下滑了很深以后OFFSET 会变得非常大查询性能急剧下降。更合理的做法是前端把当前列表最后一条记录的排序字段值传回后端后端用“游标”方式去取下一页。单主键游标分页最简单-- 前端传过来的最后一条记录 id 1050 SELECT * FROM orders WHERE id 1050 ORDER BY id DESC LIMIT 20;复合排序游标稍微复杂一点需要同时传上一页最后一条记录的created_at和idSELECT * FROM orders WHERE (created_at, id) (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20;这里的(created_at, id) (?, ?)是行值比较MySQL、PostgreSQL 都支持。它表示先比较created_at如果相同再比较id找出“排在这一组排序键之前”的数据。这种方案的性能瓶颈不再是偏移量而是 WHERE 条件能不能走索引。只要在(created_at, id)上建了复合索引即使翻到很深的页执行计划也只需要从索引的某个位置往后扫 20 条速度非常稳定这是我在实际项目里最推荐的分页方式。4. 大偏移量分页为什么慢三种优化方案实测如果你上过生产环境大概率见过这样的 SQLSELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 200000, 50;表面上是“只取 50 条”但数据库实际要做的是按排序键扫描 200050 行跳过前面的 200000 行最后只留下 50 行返回。加粗强调一下前 20 万行不是不读而是读了又丢弃所有 IO 和排序成本都发生在被丢弃的数据上。4.1 慢的本质是回表和丢弃假设订单表上已有created_at的普通索引排序部分可以走索引但SELECT *意味着每一行都要回表拿完整数据。哪怕只返回 50 行为了确认前面那 20 万行的created_at和id数据库也要把 20 万条索引记录全部访问一遍并每条都触发一次回表。这种开销随着偏移量增大呈线性增长所以才会“越翻越慢”。4.2 优化方案一延迟关联延迟关联的核心思路是先在索引里把目标行的主键找出来只对“最终要返回的那一页”回表取完整数据。示例SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 200000, 50 ) t ON o.id t.id ORDER BY o.created_at DESC, o.id DESC;子查询里只查id而且排序字段有索引覆盖整个过程在索引上完成不需要回表。拿到这 50 个主键后再通过主键回表取完整行。虽然偏移量还是 20 万但每条被丢弃的索引记录都只是访问一个很小的索引页成本比原来低得多。实测里这个方案通常能让深分页查询从十几秒降到一两秒。4.3 优化方案二keyset 分页彻底消除偏移量延迟关联只是缓解要根治还是得放弃“跳到任意一页”的交互改用游标分页。也就是第 3.3 节里提到的方案。SELECT * FROM orders WHERE (created_at, id) (?, ?) ORDER BY created_at DESC, id DESC LIMIT 50;不管是第 100 页还是第 10000 页每页查询都只需要从 WHERE 条件指定的位置开始扫描扫描行数最多就是 50 条加少量索引定位开销整体性能几乎恒定。代价是用户不能直接跳页只能一页一页往下翻。在“上拉加载更多”已经成为主流的今天绝大多数前台列表都适用这种方案。4.4 优化方案三给排序字段建对索引无论用哪种方案索引都是地基。很多分页慢的问题根源不是 LIMIT 本身而是 ORDER BY 的字段没有合适的索引支持。比如上面游标分页的查询推荐建这样一个复合索引CREATE INDEX idx_orders_created_id ON orders(created_at DESC, id DESC);让排序键和 WHERE 条件尽量命中同一个索引避免排序发生在内存或临时文件里。如果表里已有的索引是(created_at)排序时还需要额外按 id 做一次稳定排序虽然差距在小数据量时看不出来到百万级以上就会拉开明显差距。5. 高频踩坑与问题排查实录这一节是我这些年被问得最多的 LIMIT 相关问题的汇总每一类都写一下现象、原因和解法。5.1 参数为 NULL 或负数时会怎样LIMIT 和 OFFSET 后面的值要求是非负整数。如果参数来自前端且没有做类型校验可能出现几种情况OFFSET 传负数MySQL 会直接报错You have an error in your SQL syntax或者OFFSET must be a non-negative integer。LIMIT 传 0不报错返回空结果集。LIMIT 传 NULL预编译里绑定 NULL 时会报类型转换错误某些 ORM 生成了LIMIT null也会异常。稳妥做法是在进入 SQL 之前统一做参数归一化先转成 int再夹取范围currentPage 1一律按 1 处理pageSize超过上限就截断。后端永远不要信任前端传过来的分页参数。5.2 分页翻页后出现重复或丢数据现象第一页和第二页之间有几条记录重复出现或者第二页漏掉了一些原本应该出现的记录。原因ORDER BY 的排序列不唯一比如只按created_at排序同一个时间戳下有大量记录每次查询时这些同值记录的相对顺序无法保证。解法排序键末尾追加主键ORDER BY created_at DESC, id DESC我已经把“排序键必须唯一”写进团队分页规范了只要遵守这一条90% 的分页重复问题都能提前规避。5.3 在 SQL Server 里写 LIMIT 直接报语法错误如果你从 MySQL 跳到 SQL Server 环境第一反应可能是把 MySQL 的 SQL 原封不动粘过去但LIMIT在 SQL Server 里根本没有这个关键字直接报错。SQL Server 的正确写法是SELECT * FROM employees ORDER BY id OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;Oracle 12c 以上的写法类似SELECT * FROM employees OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;跨库兼容的思考顺序我建议是这样如果所有目标数据库都支持标准OFFSET ... FETCH优先用标准写法如果还在维护老 Oracle 11g只能子查询加 ROWNUM 包一层如果项目里大量使用 ORM尽量让 ORM 自动生成方言 SQL而不是手写到处兼容。5.4 已知问题速查表场景现象原因解法无 ORDER BY 的 LIMIT每次返回行不一样行序不确定加稳定排序键ORDER BY 不唯一分页重复、漏数据同值排序不稳定ORDER BY ... , id偏移量过大查询越来越慢需要扫描大量丢弃行延迟关联/游标分页OFFSET 传负数语法报错参数非法接口层统一校验SQL Server 跑 LIMIT语法错误方言不支持改用 OFFSET ... FETCHOracle 11g 跑 FETCH FIRST语法错误版本不支持子查询 ROWNUM6. 这些“非主流”LIMIT 用法也很实用除了分页和取 Top-NLIMIT 还有一些不太起眼但在实际项目中很好用的场景。6.1 随机抽查一条数据MySQL 里最简单的随机取一行写法是SELECT * FROM employees ORDER BY RAND() LIMIT 1;这个方法在小表上很实用但大表上ORDER BY RAND()会全表扫描并且给每一行生成随机数再排序性能极差。数据量超过几十万级别的表我会尽量避免。换一种思路先取主键的最大值和最小值随机生成一个值再通过主键定位虽然随机性不如 RAND() 均匀但快得多。6.2 用 LIMIT 1 高效判断数据是否存在很多人判断是否“有数据”习惯这样写SELECT COUNT(*) FROM employees WHERE id 123;其实更高效的是SELECT 1 FROM employees WHERE id 123 LIMIT 1;数据库只需要找到一行满足条件的记录就立刻返回不需要统计完整数量。应用层只要判断返回结果是否为空就可以。6.3 分批清理数据批量删除大量数据时一次性执行事务锁住所有行很容易造成主从延迟或锁竞争。我常用的做法是循环分批处理每批限制行数DELETE FROM employees WHERE id IN ( SELECT id FROM ( SELECT id FROM employees WHERE status inactive LIMIT 1000 ) tmp );MySQL 的DELETE不能直接带LIMIT也不能在子查询里直接引用要删除的表所以通常要套两层子查询。每批删 1000 条循环执行直到删完可以有效避免单条 SQL 锁太多行导致的问题。6.4 分组内取前 N 条LIMIT 负责的是“整体取前几条”但业务上经常需要“每个分类取前几条”比如每个部门工资最高的三个人。MySQL 8.0 和 PostgreSQL 支持窗口函数SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees e ) t WHERE t.rn 3;老版本 MySQL 不支持窗口函数时可以用关联子查询加 LIMIT 替代但性能通常不理想。如果分组数不多、每个组的数据量不大可以把(department_id, salary)上的复合索引建好后再跑。我把这些年折腾分页的体会浓缩成这样一句话LIMIT 本身只是一个截断工具真正决定它好不好用的是前面的排序字段、后面的偏移量和底层的索引设计。今天分享的这些写法里游标分页和延迟关联是我现在最常用的两招而“ORDER BY 末尾加主键”这条则是所有分页查询都适用的保命口诀。