SQL优化之LIMIT语法limit n,m 和 limit n 到底差在哪儿先说一下我为什么要专门写一篇LIMIT的文章。前不久在压测一个同事做的报表查询接口这个接口只按创建时间倒序取最近一条数据SQL写得很朴素SELECT ... ORDER BY create_time DESC LIMIT 1。跑起来没有任何问题毫秒级返回大家都没在意。但同一天下午另一个运营后台的分页列表翻到第30多页的时候页面上那个加载圈转了四五秒DBA抓出慢查询一看SQL长这样SELECT * FROM order_list ORDER BY create_time DESC LIMIT 700000, 20;两条SQL都用了LIMIT一条是LIMIT 1一条是LIMIT n,m。差距不是几倍是几千倍。这个对比就是今天这篇博文的引子LIMIT语法看起来只有一行但它内部的行为逻辑完全不同用对了是神器用歪了就是定时炸弹。这篇文章适合谁看后端开发、数据分析、还在学校写课程设计的同学都适用只要你写过带分页的SQL就值得花十分钟把这里面的底层机制捋一遍。1. 先把语法擦干净LIMIT n 和 LIMIT n,m 到底各表达什么很多人在写SQL的时候其实没认真想过这两个形态的差异。LIMIT n是取前n条LIMIT n,m是从第n条往后取m条。这个第n条是从0开始还是从1开始偏移量算不算第n条本身这些问题如果靠猜早晚会在边界条件上翻车。1.1 从语义层面拆解LIMIT n等价于LIMIT 0, n意思是从第0条开始也就是从第一条开始取n条记录最多返回n行。LIMIT n,m的意思是跳过前n条记录然后取m条记录。比如说LIMIT 100, 20表达的就是跳过前100条取第101条到第120条一共20条。这个n在两种写法里的位置特别容易混淆在LIMIT n里n是返回的行数在LIMIT n,m里n是偏移量m才是返回的行数所以LIMIT 20和LIMIT 0, 20结果完全一样都是返回前20条。但很多人写成LIMIT 20, 20本意是想取第21条到第40条这个语义没问题问题是他们没意识到这个写法和LIMIT 20 OFFSET 20是等价的——两个参数第一位永远先看偏移量。-- 下面三种写法结果一致返回第11条到第30条 SELECT * FROM t LIMIT 10, 20; SELECT * FROM t LIMIT 20 OFFSET 10; SELECT * FROM t LIMIT 0, 20 OFFSET 10; -- 不建议太绕还有个容易踩的坑LIMIT m OFFSET n和LIMIT n, m参数顺序是相反的。LIMIT 10, 20是偏移10取20而LIMIT 20 OFFSET 10同样是偏移10取20。OFFSET这个写法更接近自然语言所以可读性上更优但很多老系统里全是LIMIT n,m的遗留写法。1.2 偏移量的起点是从0开始这一点必须单独拎出来说因为它直接决定分页公式怎么写。假设每页20条第page页page从1开始的数据应该怎么写-- 正确写法偏移量 (page - 1) * pageSize SELECT * FROM t ORDER BY id LIMIT (page - 1) * 20, 20; -- 等价写法 SELECT * FROM t ORDER BY id LIMIT 20 OFFSET (page - 1) * 20;第一条数据偏移量是0不是1。如果第1页你写了LIMIT 1, 20那就从第二条开始取了第1条数据永远看不见。这个错误在实习生代码里出现频率非常高甚至在GitHub上搜都能搜到很多公开项目犯这个毛病。1.3 两种形态的适用场景天生不同LIMIT n最常见的用途是Top N查询。比如取最近登录的10个用户、取价格最高的前5个商品、判断某个用户是否存在加LIMIT 1等。这类查询的特点是只关心最有代表性的那几条不关心翻页。LIMIT n,m则是为分页而生的目的是在完整结果集里切出第几页这一段。它隐含了一个前提你得先能拿到全部符合条件的数据再从中切页。问题恰恰出在这个先拿到全部上后面第2节会详细讲。关键结论先放这儿LIMIT n是拿够就停LIMIT n,m是明明只要m条却得先跑完全部nm条再扔掉前n条。前者是线性开销的一部分后者是隐藏的线性开销乘数。2. 执行层视角多写一个OFFSET数据库到底多干了多少活理解了语义下一步就是看MySQL内部是怎么执行这两种LIMIT的。很多人以为LIMIT只是最后返回前的截断动作执行引擎前期的扫描还是照常扫完的。实际上不是MySQL在扫描过程中就会实时判断要不要继续扫这个判断逻辑才是性能差异的根源。2.1 提前停止 vs 扫完再扔对于一个最简单的全表扫描场景没有WHERE条件、没有ORDER BYSELECT * FROM t LIMIT 5InnoDB从第一条记录开始读读到第5条就立即停止不再继续扫剩下的数据。这种机制叫提前终止early termination是引擎层面在迭代器里实现的一个很朴素且很有效的优化。SELECT * FROM t LIMIT 100000, 5情况完全不同。引擎不知道第100000条之后在哪它必须从第一条开始一条一条往后数数到第100005条然后把前面这100000条全部扔掉只把最后5条返回给客户端。对于引擎来说它扫描了100005行但有效输出只有5行前面100000行的磁盘IO、Buffer Pool访问、行格式解析全部白干。用一句话概括就是LIMIT n的扫描成本是O(n)LIMIT n,m的扫描成本是O(nm)但因为引擎不知道偏移量后面还有没有数据它必须把偏移量这一段完整扫完成本随偏移量线性增长永远不会提前停止。2.2 排序场景下问题直接放大如果查询里还有ORDER BY事情就更麻烦了。SELECT * FROM t ORDER BY create_time DESC LIMIT 5这种Top N查询MySQL可以使用优先队列排序Priority Queue只需要在内存里维护一个大小为n的堆边遍历边和堆顶元素比较最终直接得到前n条。这个优化在优化器里叫top N sort内存占用只和n有关。但SELECT * FROM t ORDER BY create_time DESC LIMIT 100000, 5呢它必须把排序后的完整结果集算出来然后才能知道第100000条之后是哪5条。这意味着如果排序字段能用索引索引本来就是有序的扫描部分依然要扫到100005条如果排序字段没有索引必须对全表或大范围做filesort把排序结果全部落盘或放内存再扔掉前100000条注意一下ONDER BY加上LIMIT 1这种写法本质是降级成了一个找出最大/最小值的问题属于可以被索引加速的Top N查询但一旦偏移量变大Top N优化基本失效又退回全量排序的老路。2.3 explain里的rows字段会告诉你真相不看执行计划谈性能都是耍流氓。用EXPLAIN看一下这两种SQL你会很直观地看到扫描行数的差异EXPLAIN SELECT * FROM t ORDER BY id LIMIT 10; EXPLAIN SELECT * FROM t ORDER BY id LIMIT 100000, 10;对于第二种rows这一列会显示一个很大的估算值大约在100010左右。这个值越大MySQL需要访问的行越多IO成本越高。但别只盯着rows还要看Extra列。如果出现了Using filesort意味着排序无法走索引这两条SQL都会先产生一个完整的排序结果集。哪怕LIMIT 10那条同样可能产生全量filesort只是代价相对可控数据量大了照样慢。我把这两种形态的执行成本对比整理成一个表格方便你里面记场景LIMIT nLIMIT n,m无排序扫描约n行后停止扫描约nm行丢弃前n行有排序列索引按索引顺序扫描n行可提前停按索引顺序扫描nm行有排序但无索引可能全量filesort用堆取前n全量filesort再切偏移量内存压力较小堆排序省内存较大filesort结果集大查询耗时曲线基本稳定随n线性增长3. 深分页现场当LIMIT偏移量到了百万级别慢得让人绝望前面是原理层面的分析下面给一个具体的压测数据看看默认分页写法在真实生产环境里会慢到什么程度。3.1 一个1000万行表的压测记录我用MySQL 8.0InnoDB引擎一张订单表1000万行数据表结构大致是CREATE TABLE order_list ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_create_time (create_time) ) ENGINEInnoDB;查询需求按创建时间倒序每页20条翻到第35000页。默认写法SELECT * FROM order_list ORDER BY create_time DESC, id DESC LIMIT 700000, 20;实际压测结果平均耗时3.8秒。这个数字在压测环境还算好的生产环境如果并发高点、磁盘IO慢点直接奔着5秒以上去。而如果只查第一页也就是LIMIT 20耗时是8毫秒。差了将近475倍。关键问题来了同样是查20条数据为什么偏移量大了之后就差了三个数量级因为这条SQL完整做了以下几件事从二级索引idx_create_time从右往左扫倒序对每个索引项用主键回表去拿order_no、user_id、amount、status这些完整行数据一路扫到第700020个索引项同时回表700020次把前面700000行数据全部丢弃只把最后20行返回给应用层。由于create_time有重复值的可能排序上还需要加id DESC做稳定排序MySQL在扫描到大量相同的create_time时还要做额外比较实际成本比理论值更高。3.2 要命的不是那20条而是前面的700000条很多人优化分页时眼睛只盯着我要的20条能不能快一点很少去想那700000条才是成本大头。深分页的本质问题在于MySQL在拿到offset之前无法跳过前面的行必须全部扫描回表一遍。这个成本完全取决于偏移量和你要取多少条关系不大。所以在极端场景下你甚至能看到LIMIT 9990000, 1这种只取一条却要扫一千万行的SQL。对于数据库来说它这一条的成本比很多人想象的贵得多。另外还有一个容易忽略的点回表。二级索引里存的只是索引字段和主键值SELECT * 需要的其他列都在聚簇索引主键索引里。每扫描到一个索引项就要用主键到聚簇索引里再找一次完整行这就是回表。深分页场景下这种回表不是20次而是700020次光这部分的随机IO就能把磁盘拖垮。3.3 业务隔离分页不是原罪无界分页才是这里得说句公道话深分页慢不完全是因为LIMIT语法写得不对而是分页需求本身在超大偏移量下就是反数据库直觉的。用户真的会翻到第35000页吗大概率不会。但爬虫、定时任务、数据导出这类无界遍历场景是真实存在的。所以在讲优化方案之前先明确一条准则能改需求就先改需求改不了需求再谈SQL优化。比如前台产品上限制最多翻100页比如导出任务放到离线系统用流式游标处理这种业务侧的截断能力比任何SQL优化都彻底。后面第4节的方案更多是给必须支持大偏移量的接口兜底用的。4. 三个能打的分页优化方案按真实场景排序如果业务就是绕不开大偏移量下面这三招是我强烈推荐的每一招都比加索引这种空泛建议实在得多。4.1 延迟关联先用覆盖索引把主键捞出来再回表取数据这是深分页优化里性价比最高的一招。核心思想是别让数据库带着SELECT * 的所有列去深海里捞鱼先派一个轻量级的侦察兵把主键捞出来再用主键精准回表拿完整数据。侦察兵SQL长这样SELECT id FROM order_list ORDER BY create_time DESC, id DESC LIMIT 700000, 20;这个查询只查id、create_time两个字段而idx_create_time这个二级索引里恰好包含了这两个字段。也就是说执行这个查询的时候MySQL只需要扫二级索引不用回表扫描700020个索引项的成本比扫描回表700020个完整行低一个量级。拿到这20个主键id之后再join回原表拿完整数据SELECT t.* FROM order_list t INNER JOIN ( SELECT id FROM order_list ORDER BY create_time DESC, id DESC LIMIT 700000, 20 ) tmp ON t.id tmp.id ORDER BY t.create_time DESC, t.id DESC;实测下来前面那个3.8秒的查询用延迟关联改写后能压到0.6秒左右。提升还是很大的。这里有个小细节必须注意内层查询LIMIT出来的顺序在join之后不一定能保持。你必须在外层也写上同样的ORDER BY子句否则翻页顺序会乱。很多人改写完之后发现数据顺序不对就是漏了这一步。4.2 基于主键的游标分页彻底告别OFFSET延迟关联能解决一部分问题但它不是万能的——偏移量特别大的时候比如百万条以上哪怕只扫二级索引扫描成本也很可观。这时候我就建议换一种分页思路不做偏移量分页做游标分页。原理很简单你既然能拿到上一页最后一条数据的id那下一页就直接从它后面开始查-- 第一页取20条 SELECT * FROM order_list ORDER BY create_time DESC, id DESC LIMIT 20; -- 第二页假设上一页最后一条记录的id是100200 SELECT * FROM order_list WHERE (create_time 2025-06-01 10:00:00) OR (create_time 2025-06-01 10:00:00 AND id 100200) ORDER BY create_time DESC, id DESC LIMIT 20;这里的WHERE条件用了一个(create_time, id)的联合游标来避免跳数据和重复数据。由于id是主键、create_time是二级索引如果建一个(create_time, id)的联合索引这个查询可以直接走索引定位引擎从游标位置开始扫20条就停扫描行数恒等于20跟翻到第几页完全无关。这个方案的唯一限制是它不支持跳页。用户想从第1页直接跳去第100页不给偏移量游标分页做不了。所以它特别适合无限滚动、信息流这种只能一页页往下刷的场景。而且改造成本很低前端只需要记住上一页最后一条记录的游标值传回来即可。4.3 加索引能不能救深分页一半能一半不能给排序字段加个索引不就行了——这个建议我经常在群里看到但它只答对了一半。索引能解决的是排序成本。如果ORDER BY字段没有索引MySQL要filesort加了索引之后引擎可以按索引顺序直接扫描省掉排序这一步。我们前面4.1的延迟关联能跑得快很大程度也是因为create_time有索引否则内层查询还得全量filesort虽然不用回表排序本身仍然贵。但索引救不了从第一条开始数偏移量这个本质行为。哪怕排序字段有索引LIMIT 700000, 20依然要沿着索引扫700020个索引项。索引让每次扫描更快但扫描的次数没变大偏移量依然是大偏移量。再说句实在话有些排序字段加了索引也没用。比如按amount排序分页如果amount索引的区分度不高、或者查询里还有复杂的WHERE条件走不了这个索引排序照样回落到filesort。索引不是银弹它是必要不充分条件。5. 容易被忽略的细节ORDER BY、覆盖索引与LIMIT 1的微妙关系很多人写完分页SQL就跑了根本没认真想过排序和LIMIT在优化器里是联动处理的。这一节说三个高频但容易被忽略的细节。5.1 ORDER BY LIMIT 1 的特殊意义LIMIT 1在优化器里是个特殊存在。对于取最大值/最小值这类需求优化器能走索引直接定位到边界的首条记录几乎零成本。-- 取最新一条利用索引天然降序 SELECT * FROM order_list ORDER BY create_time DESC LIMIT 1;如果create_time有索引这条SQL的扫描行数严格等于1引擎只要找到索引最右边那条记录就停了根本不回看其他任何记录。这也是为什么开头提到同事那条LIMIT 1的报表SQL毫秒级返回的原因。但如果把LIMIT 1换成LIMIT n在无索引排序场景下引擎就需要在filesort里维护一个大小为n的优先队列n越大堆操作越多。虽然比全量排序好但也不是零成本。所以如果业务只需要最新一条或者是否存在那就明确写LIMIT 1别写LIMIT 10然后让代码自己取第一条——这既浪费数据库资源也会让查询时间悄悄变长。5.2 覆盖索引的含义连回表都省了前面延迟关联的核心就是覆盖索引这里把概念展开讲清楚。所谓覆盖索引指的是查询所需的全部字段都包含在某个索引里这样引擎只扫描索引不用回表拿其他列。举个实际场景。如果订单表经常要做这样的分页查询SELECT id, order_no, create_time FROM order_list ORDER BY create_time DESC LIMIT ?, ?;如果只建idx_create_time那引擎扫描索引拿到id和create_time之后还得因为order_no回一次表。但如果你把索引改成idx_create_time_order_no (create_time, order_no)这个查询的id主键、create_time、order_no都在索引里引擎完全不用回表直接在索引覆盖范围内完成扫描和裁切。覆盖索引优化的关键注意事项是别把所有字段都塞进索引。索引也是数据维护它也有成本字段越多占用磁盘越大写入越慢。一般只在查询频率极高的分页接口上把固定查询的SELECT字段精准塞进联合索引。5.3 LIMIT 1在EXISTS/IN场景里的优化价值LIMIT 1不止能用在主查询在子查询里也是个优化利器。经常有人写这样的SQL判断某用户是否有下单SELECT * FROM user_info u WHERE EXISTS ( SELECT 1 FROM order_list o WHERE o.user_id u.id );如果order_list的user_id有索引MySQL在执行EXISTS的时候会自动做半连接优化不需要显式加LIMIT。但某些复杂查询下优化器不一定能识别出只需要判断存在性这时候你可以在子查询里手动加LIMIT 1帮助优化器理解只要找到一条就可以停了SELECT * FROM user_info u WHERE EXISTS ( SELECT 1 FROM order_list o WHERE o.user_id u.id LIMIT 1 );这条SQL的执行计划里order_list的访问会变成扫描到第一条匹配就停止扫描行数从可能的上千行直接降到1行。写不写LIMIT 1对整个查询的开销差异很大尤其在order_list数据量大的时候。6. 实战中我踩过的坑和现在的分页落地习惯最后一部分分享几个真实的翻车现场和我整理的分页检查清单算是给这篇文章收个尾也都是能用得上的经验。6.1 三个真实的翻车现场第一个翻车现场LIMIT参数传负数。有些后台系统让用户自定义每页条数前端没做校验传了个-1过来SQL变成了LIMIT -1, 20MySQL直接报错。不要指望数据库帮你做参数校验分页参数的合法性必须在上游就拦住。第二个翻车现场LIMIT的偏移量超过了INT上限。LIMIT 2147483648, 20这种写法在老版本的MySQL里会直接报错或者产生不可预期行为因为偏移量被当作有符号整数处理。现在的8.0版本对超大偏移量的支持要好一些但这种查询除了等着超时没有任何意义业务阈值必须卡死。第三个翻车现场分页接口的所有参数都能被用户传进来包括排序字段。比如用户传了个ORDER BY amount DESC而amount没有索引整个分页查询就变成全表filesort。更严重的是如果排序字段可以被注入成任意表达式这并不是传统SQL注入但也可能拖垮数据库。分页接口的排序字段最好是白名单制后端代码里写死允许排序的字段列表。6.2 我现在的分页接口测试清单每次写分页接口我都会在测试环境过一遍下面这组场景测完基本心里就有底了检查项期望结果第1页、第2页、中间页数据是否有重叠/遗漏无重复无跳变最后一页不足pageSize时是否正常返回返回剩余行数不报错page填0、负数、null拒绝请求或按第1页处理pageSize填0、超大数据拒绝或限制上限排序字段传不存在的列直接报错或返回固定排序并发翻页时有新数据插入允许轻微顺序抖动不得报错大数据量下翻到最后一页延迟可接受或改用游标分页还有一点写分页SQL之前养成EXPLAIN看一眼的习惯。重点看三个地方type是不是range/ref/eq_ref而不是ALLrows估算值是不是跟你预期的扫描范围匹配Extra里有没有Using filesort。这三项全绿这条SQL基本不会翻车。6.3 一句话版本的LIMIT分页口诀我在团队内部经常用一句话总结今天的全部内容限制返回行数用LIMIT n分页用LIMIT n,m但要警惕大偏移量大偏移量分页优先游标游标不可行就延迟关联延迟关联之前先确认索引覆盖了你需要的一切。回到文章开头那个报表SQLLIMIT 1和LIMIT 700000, 20之间的差距本质上不是语法写法不同而是快速拿到边界和遍历整个历史数据再扔掉的差距。希望这篇东西能让你在下次写分页SQL的时候多想一想那句老话数据库不是不能干活但你别让它在OFFSET的海里白游一公里只为了捞20条鱼。最后给个我个人的经验如果业务里真的有大偏移量分页需求先去找产品经理聊一聊这个需求存在的意义。多数时候翻到第几万页并不是真实的用户路径它只是某种导出任务或者定时任务被硬生生塞进了同步接口里。这种问题架构和产品上的收敛永远比SQL上的调优更划算。