我每年处理线上MySQL请求超过两万条但真正让我在同事面前“翻车”的不是那些复杂的多表联查也不是什么高深的索引调优而是一行看起来人畜无害的SQLSELECT * FROM table LIMIT 1000000, 10。当时我信誓旦旦地在代码评审里说“这语句没问题就是取第100万行后面的10条数据”结果到了生产环境这条语句直接把一个核心库的CPU打满了整个页面卡了将近四秒。也正是那一次我决定把这条SQL彻底“庖丁解牛”一遍。这篇文章不是给你讲语法那种东西官方文档三行就讲完了。我想做的是把这条SQL从表面语法、底层执行逻辑、索引失效原理、深分页优化方案到它涉及的那些经典报错比如429 too many requests、code size limit这些“远房亲戚”全部拆开揉碎配合真实场景和踩坑经验一次性讲透。不管你是刚入门的学生、写业务代码的后端开发还是需要优化慢查询的DBA这篇文章都应该能让你对LIMIT的认知上一个台阶。1. 表面语法与底层逻辑之间的巨大鸿沟1.1 这条SQL的“字面意思”到底是什么先看语句结构SELECT * FROM table LIMIT 1000000, 10。在MySQL中LIMIT后跟两个参数时第一个参数代表偏移量offset第二个参数代表返回的行数row_count。所以字面意思就是跳过前面100万行从第1000001行开始取10条记录。这里有个特别容易混淆的点很多从Oracle或SQL Server转过来的开发人员会觉得这个写法很眼熟甚至误以为它等同于标准SQL里的OFFSET 1000000 ROWS FETCH NEXT 10 ROWS ONLY。但从语法层面讲MySQL的LIMIT offset, row_count并不属于标准SQL它是MySQL特有的方言。更麻烦的是不同数据库对这个语法的容忍度还不一样——比如在PostgreSQL里这种写法直接报语法错误必须写成LIMIT 10 OFFSET 1000000。同一个逻辑换个数据库就编译不过这就是第一个坑。从语义层面深挖这句SQL想表达的业务需求通常对应的是“分页查询的第100001页”每页10条或者是那种“我要跳过大部分历史数据只取中间一小段”的冷数据捞取。这种需求本身没问题但问题在于SQL语义上的“跳过”并不等于执行层面的“跳过”。1.2 为什么“跳过”在数据库里根本不存在这是整篇文章最关键的一个认知点。你在应用层写一个循环for i in range(1000000, 1000010)计算机是可以直接跳到第1000000个位置的因为内存数组支持随机访问。但MySQL的表数据存储在磁盘上底层是BTree索引结构数据行之间通过指针和页Page关联数据库引擎根本没有“直接跳到第100万行”的能力它唯一能做的就是一条一条地数。我用一个生活化的例子给你解释。假设你有一本100万页的电话簿不是电子版那种支持搜索的而是最老式的纸质版你要找到第1000001页到第1000010页的内容你会怎么做你不会真的从第1页开始翻——你会先估算大概位置然后直接从中间翻开。但数据库引擎不行它没有“估算”的能力。它从BTree的最左节点开始沿着叶子节点的双向链表一条一条往下走每经过一条记录就计数一次一直数到第1000001条才停下来然后才开始真正返回数据。换句话说LIMIT 1000000, 10这个写法的执行成本是由“1000000”决定的而不是由“10”决定的。偏移量越大需要扫描和丢弃的行就越多查询就越慢。这就是标题里那句“LIMIT 1000000, 10”最反直觉的地方——它看起来只是取10条实际上是在“数”100万条。1.3 为什么很多人测试时没发现问题我在公司里见过太多次这样的场景开发人员在测试库跑这条SQL表里总共就10万行数据LIMIT 10000, 10秒回然后直接把这条SQL原封不动搬到了生产环境生产库这张表有5000万行用户翻到第10万页时接口直接超时。问题就出在测试数据量级和生产数据量级相差了好几个数量级。很多人还容易忽略一个因素——innoDB缓冲池Buffer Pool命中率。测试时数据量小几乎所有数据页都能装进内存扫描100万行说白了就是内存遍历速度尚可。但生产环境数据量远超缓冲池容量随着偏移量增大你需要访问的叶子节点页大概率不在内存里每次都要触发磁盘I/O。一次随机I/O大约是10毫秒扫描10万个数据页那就是1000秒的I/O耗时。所以这条SQL在生产环境跑四秒不是数据库“卡了”而是它真的在实打实地做磁盘随机读。提示判断一条分页SQL是否存在深分页隐患最简单的经验法则是——当偏移量超过表总行数的十分之一时这条SQL基本可以判定为不合格需要立即考虑改写方案。2. 深分页的执行代价索引失效、回表与随机I/O的真实账本2.1 你以为走了索引其实InnoDB在“血亏”不少有一定基础的开发者会说“我给table表的id字段加了主键索引或者给查询条件字段加了普通索引这条SQL不是应该很快吗”这个想法很美好但现实很骨感。我们分两种情况来看场景一SELECT * AND 无WHERE子句。如果这张表没有合适的二级索引可走优化器大概率会选择全表扫描full table scan。全表扫描意味着InnoDB要读取聚簇索引的全部叶子节点顺序扫描并计数到100万行后开始取数。这种情况下整条SQL的时间复杂度是O(N)N是表的总行数。表越大翻到同样页数的耗时就越长而且增长趋势是线性的。场景二SELECT * AND 有WHERE子句但走的是二级索引。假设你查WHERE status 1 ORDER BY id LIMIT 1000000, 10并且status上建了普通索引。执行计划大概率是先通过二级索引定位到所有status 1的记录按主键排序然后一条一条数到100万行再开始取数。二级索引里存储的是索引字段和主键值不含其他列数据所以当你要取*所有列时每取一条有效记录都需要根据主键ID回表到聚簇索引里再去查一次完整数据行。这就引出了深分页一个更隐蔽的性能杀手——回表次数被放大。你以为只回表10次因为最终只返回10条错了数据库是按照“先扫描、后过滤、再丢弃”的逻辑执行的。它需要从二级索引扫描到第100万零10条记录这100万多条可能都满足status1条件其中每一条记录在真正“读取”时只要优化器判断需要回表就会触发一次主键查找。虽然InnoDB有自适应哈希索引和预读机制优化但在偏移量过大时随机I/O的量级依然可能是数十万次这个成本任何缓存都扛不住。2.2 整行数据的磁盘开销SELECT * 为什么是帮凶LIMIT的偏移问题是主角但SELECT *绝对是那个不断给主角“叠buff”的帮凶。如果你查询只需要id和name两个字段却把整行的所有字段都查出来意味着回表时需要拷贝和传输的数据量成倍增加MySQL Server层到存储引擎层之间的数据传递更多网络传输给应用服务器的字节数更大。一个真实案例我优化过一张20个字段的订单表每条记录平均2KB。用SELECT *做深分页时扫描100万行意味着从磁盘读出来丢弃的数据总量高达2GB改成SELECT id, order_no之后单行只有200字节同样的扫描路径I/O流量直接降到原来的十分之一。执行时间从3.8秒降到0.9秒。这不是什么高深的技巧就是让数据库少做点无用功。特别是当你用SELECT *配合WHERE条件做深分页时MySQL可能在优化阶段就放弃索引下推Index Condition Pushdown因为需要回表获取完整行才能判断后续操作。尽量减少SELECT的字段列表永远比事后加缓存更有效。2.3 用一个数学账本量化LIMIT的性能代价算一笔具体的账让大家对“LIMIT深分页到底有多贵”有个直观概念。假设表总行数5000万查询语句SELECT * FROM orders WHERE status 1 ORDER BY id LIMIT 1000000, 10平均行大小1KBInnoDB页大小16KB扫描并丢弃100万行时即使不考虑回表假定有覆盖索引单是顺序扫描聚簇索引的叶子节点每页大约可以存放16行1KB每行扫描100万行需要访问约62500个数据页如果这些页都在Buffer Pool中内存扫描每页耗时约0.1ms总耗时约6.25秒如果这些页部分在磁盘假设命中率90%那么6250次磁盘随机I/O每次10ms又是62.5秒。这就是为什么生产环境深分页经常能把数据库拖垮的原因。它不是慢一点点而是慢了整整一个数量级。而且别忘了这个查询可能还不止你一个人在跑如果有10个用户同时翻到第10万页数据库相当于同时执行10次百万级扫描那就不只是接口超时可能是整个实例的CPU和I/O双双打满拖垮所有业务。3. 庖丁解牛式的优化方案四种改写思路从入门到进阶3.1 方案一延迟关联Deferred Join——最推荐的第一板斧延迟关联的核心思想很简单先用覆盖索引把需要的主键ID快速定位出来再用这些ID去关联回原表取完整数据。让数据库在“找位置”的阶段做最少的I/O在“取数据”的阶段只针对命中的少数行做回表。改造后的SQLSELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY id LIMIT 1000000, 10 ) tmp ON t.id tmp.id;内层子查询只select了id字段status条件走二级索引时如果二级索引覆盖了status和主键id则全程不需要回表扫描100万条索引记录的成本远低于扫描100万条完整行。外层JOIN只回表10次取完整行数据。实测数据在我负责的一个订单系统里改写前SELECT * FROM orders WHERE status 1 ORDER BY id LIMIT 1000000, 10耗时3.2秒改用延迟关联后同样的偏移量耗时降到0.4秒提升了8倍。这是深分页优化里性价比最高的方案也是我优先推荐给所有人的。提示延迟关联成立的前提是内层查询的排序和WHERE条件能够使用同一个二级索引否则内层仍然可能走filesort性能提升有限。改造前务必用EXPLAIN确认执行计划。3.2 方案二书签定位Keyset Pagination——直接从根上消灭偏移量所谓书签定位就是不依赖OFFSET而是记住上一页最后一条记录的位置下一页直接从这个位置往后取。这话说起来简单落地时有两种写法写法A基于主键自增IDSELECT * FROM orders WHERE status 1 AND id {last_page_max_id} ORDER BY id LIMIT 10;上一页返回的最后一条记录id是1000010下一页就把WHERE id 1000010带进去直接走主键索引扫描10条即可。这个方案不仅快而且耗时和页码完全无关——你翻到第100万页和翻到第2页性能是一样的。写法B基于业务排序字段如果排序字段不是id而是比如create_time就需要在WHERE里同时带上时间和id做联合条件SELECT * FROM orders WHERE status 1 AND (create_time, id) (2024-01-01 10:00:00, 1000010) ORDER BY create_time, id LIMIT 10;这是利用了MySQL元组比较的特性保证排序稳定且不丢数据。这个方案的代价是接口语义变了不能随机跳页了只能“上一页/下一页”。但绝大多数C端业务用户根本不会去翻到第100万页他关心的只是“下一页”而已。产品经理硬要一个随机跳页的功能99%的情况下是伪需求。3.3 方案三禁止深分页——产品层面的硬性约束有些时候技术方案解决不了的问题要从产品逻辑上直接堵死。常见的做法包括限制可查询的最大页数比如最多允许用户翻到第1000页超过后提示“数据最多支持前10000条”用“加载更多”替代传统数字分页配合书签定位使用搜索引擎Elasticsearch或OLAP引擎处理海量数据的搜索和翻页场景MySQL只作为数据持久化层。这三种做法我从业务侧和研发侧都实践过。限制最大页数属于见效最快、改动最小的“立规矩”方式。虽然看起来有点“暴力”但用户体验上并没有那么糟糕——真正会翻到第10万页的用户要么是爬虫要么是在有意试探系统边界阻止他反而是保护系统。3.4 方案四覆盖索引与生成列——从数据模型层面优化如果你的查询需求非常固定比如就是按status create_time排序分页且需要查询的字段不太多可以建一个覆盖索引直接包含所有需要的字段把回表彻底干掉。ALTER TABLE orders ADD INDEX idx_status_time (status, create_time) INCLUDE (order_no, user_id, amount);不过MySQL不像SQL Server和PostgreSQL原生支持INCLUDE需要你把需要覆盖的字段全部添加到索引末尾注意主键是InnoDB二级索引自动包含的。这意味着每次插入和更新都要维护一个更大的索引写入成本不可忽视。所以这个方案只适合“读多写少且分页查询字段固定”的场景。还有一个思路是生成列Generated Column如果你需要对某个表达式做排序或过滤可以把这个表达式存储为一个虚拟列再对这个列建索引。比如你要按月分页查订单可以生成一个order_month列直接走索引过滤避免全表扫描。4. 那些“远房亲戚”LIMIT引发的连锁报错与高危语句形态4.1 从深分页到“429 too many requests”同一个LIMIT不同的战场写SQL的人可能想不到自己一个不小心写出来的深分页查询居然会和线上那些“429 too many requests”报错扯上关系。我在排查生产故障时发现过好几次这样的情况某个接口原来一直稳定突然开始大量返回HTTP 429请求过多或类似的限流错误。刚开始大家以为是外部调用方在恶意刷接口最后查下来才发现是产品上线了一个带有深分页逻辑的列表页用户快速翻页时每次翻页都触发百万级的扫描数据库连接被长时间占用连接池被打满新请求进不来网关就开始限流——于是上层表现为429底层根因其实是慢SQL。所以当你在日志里看到“exceeded retry limit, last status: 429 too many requests”这类报错时不要只盯着限流配置先查一下关联业务最近有没有上线新的分页需求。很可能就是一条LIMIT 100000, 20把整个系统的连接池拖垮了。4.2 “exceeded retry limit”的另一层隐藏含义连接被数据库杀掉了在高并发场景下数据库通常配置了max_execution_time或者innodb_lock_wait_timeout。一条深分页SQL执行超过阈值后会被数据库主动终止此时客户端连接会收到类似“Query execution was interrupted”的错误。应用层的重试机制如果没做好就会不断重发相同的深分页请求每次都超时每次都重试最终把数据库彻底压垮。这种“重试风暴”在日志里表现出来的就是“exceeded retry limit”。解决思路分两层应用层重试必须带退避策略exponential backoff且对查询超时类型的错误不能无脑重试SQL层必须彻底改写深分页逻辑。如果业务确实需要支持大数据量查询建议走独立的只读从库或者数据仓库不要跟在线交易库抢资源。4.3 “code size limit exceeded”与“swap limit support”的共性问题资源在受限环境下的溢出严格来说code size limit exceeded代码大小超限更多出现在嵌入式环境、编译受限的沙箱或一些Serverless平台中跟MySQL的LIMIT子句没有直接关系。但我在看技术群讨论时发现很多人会把这两个“limit”混为一谈所以我顺手把这个边界说清楚fatal error: ineffective mark-compacts near heap limit allocation failed这是Node.js等运行时内存堆达到上限时抛出的致命错误与SQL LIMIT无关*** fatal error l250: code size limit in restricted version exceeded module这是某些编译器的限制像Keil C51针对的是生成代码体积超限reboot and select proper boot device这是BIOS找不到启动设备和LIMIT更是八竿子打不着。这几个报错唯一的共同点是都叫“limit”都属于“资源受限”类问题。在面对这类报错的时候正确做法是先确认环境的能力边界比如堆内存调整为--max-old-space-size4096能缓解Node的heap limitC编译器可以通过优化代码体积或换用更高内存版本的编译器解决。千万不要一看到limit就以为是SQL的事。诊断问题先对齐上下文。4.4 顺手聊聊“GAMIT table数据下载”这类搜索词背后的表设计启示可能有人会觉得奇怪为什么“GAMIT table数据下载”会跟SELECT * FROM table LIMIT有关。其实这也涉及一个常见问题当你在网上搜“table数据”时会发现很多科研、农业、气象领域的公开数据集也是以关系表的形式存放的。GAMIT是GPS数据处理软件它的table目录下存放着各种参数文件如果你把这些参数文件导入MySQL并按行分页读取同样会遇到LIMIT深分页的问题。这一大类“数据表下载与读取”场景我建议的原则是把表按时间分区或按地点分区不要让SELECT * FROM table LIMIT 0, 1000000这种全量捞数据的SQL出现在任何线上环境里。下载数据请走离线导出SELECT INTO OUTFILE或ETL工具不要直接查在线库更不要用LIMIT来截断。5. 一条生产慢SQL的完整排查与修复复盘5.1 现象描述与初步定位去年年底我们线上有一个运营后台的订单列表页突然接到用户反馈翻到第500页左右就开始转圈有时直接白屏。我登录跳板机先看了慢查询日志果然找到一条打了红标的SQLSELECT * FROM order_info WHERE status COMPLETED ORDER BY id LIMIT 49900, 20;Rows_examined显示扫描了20万行Query_time高达5.8秒。再看当时的数据库CPU已经持续在85%以上了。初步判断是典型的深分页问题而status字段虽然有索引但因为需要回表拿所有列即使走了索引依然有接近20万次的回表操作。5.2 利用EXPLAIN逐步拆解执行计划我执行了EXPLAIN后看到关键信息列名值说明typeref通过二级索引定位statusCOMPLETED的记录keyidx_status用到了status索引rows245088预估扫描24.5万行ExtraUsing index condition索引条件下推但回表取*仍然存在这个执行计划说明优化器通过idx_status定位到约24.5万行statusCOMPLETED的数据但要取出所有列字段SELECT *所以要对这24.5万行中的大量数据都做回表操作然后丢弃掉前面的49880条只返回最后的20条。真正有效的回表操作只有20次但无效的回表操作有近24.5万次。5.3 两轮改写与实测数据对比第一轮我先采用延迟关联改写SELECT o.* FROM order_info o INNER JOIN ( SELECT id FROM order_info WHERE status COMPLETED ORDER BY id LIMIT 49900, 20 ) t ON o.id t.id;内层查询只需要扫描二级索引id和status不需要回表外层JOIN只对20个id做聚簇索引点查。改完后实测耗时1.1秒比原来的5.8秒提升了80%以上。但1.1秒对运营后台来说还是不够理想。于是第二轮我和产品沟通后把运营后台的分页模式改成了“上一页/下一页”的书签模式。前端不再传页码改为传上一页最后一条订单的IDSELECT * FROM order_info WHERE status COMPLETED AND id {last_id} ORDER BY id LIMIT 20;由于id是主键这个查询是纯粹的主键范围扫描加上二级索引status条件过滤执行时间稳定在12毫秒上下。不管运营翻到第几页性能几乎恒定。这个例子也验证了一个道理很多性能问题不是单纯靠优化SQL就能解决的而是要重塑交互模式。5.4 基础参数调优临时撑住场面的一些补充手段在做完SQL改写和应用改造后我顺手对数据库做了两项基础参数调整作为辅助兜底注意参数调优不能解决深分页本身的问题只是给系统留出更多缓冲空间将innodb_buffer_pool_size从8GB调到16GB让更多索引页和数据页能驻留内存把max_execution_time设置为3秒防止极端情况下个别慢SQL长时间占用数据库连接。这两个参数改完后数据库CPU峰值从85%降到了30%左右。但我要强调不要让参数调优变成你容忍慢SQL的理由——真正治本的是SQL改写和产品交互升级。5.5 同一战场的不同症状与“el-table抖动”和“cell-class-name不生效”的类比在这次复盘过程中有个前端同事正好在群里求助“ant design vue的table组件不停抖动晃动是什么问题”“el-table-column中cell-class-name不生效”。我一看就乐了因为这类问题和我们的深分页慢查询其实有一个共性表层症状和根因之间隔了一层。el-table抖动很多时候不是组件bug而是给表格绑定了不稳定的row-key或者数据是异步更新且每次生成新对象引用导致Diff算法失效反复渲染cell-class-name不生效通常是版本API变更或者class名被样式权重覆盖了跟表格数据本身无关我们那条深分页SQL表面是“页面卡”根因却在于“LIMIT偏移过大”。解决问题的第一步永远是定位问题在哪个层次。前端表格抖动先看key、再看数据引用SQL慢先看执行计划、再看扫描行数。千万不要头痛医头。6. 庖丁解牛的真正心法先看执行计划再谈优化6.1 EXPLAIN的每一列到底在告诉你什么这篇文章相当于一条SQL的“庖丁解牛”那牛刀是什么就是EXPLAIN。我给自己团队定了一条铁规矩任何涉及分页、统计、报表的SQL上线前必须贴出EXPLAIN结果。重点看几列type从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就要警惕全表扫描key实际用到的索引如果是NULL说明没走索引rows预估扫描行数。这个值乘以单行平均大小就是这条SQL大概要读多少数据Extra如果出现Using filesort说明排序没走索引Using temporary则可能建了临时表这两个都是性能预警信号。6.2 慢查询日志里的三个关键指标除了EXPLAIN慢查询日志也是深分页问题的第一发现者。重点关注三个指标指标含义危险阈值Rows_examined实际扫描行数超过表行数的10%应警惕Rows_sent最终返回行数与Rows_examined差距过大说明大量无效扫描Query_time单条SQL执行时间超过1秒需关注超过3秒必须优化Rows_examined和Rows_sent之间的比值是判断SQL是否“健康”的最直观指标。如果一条SQL扫描了10万行却只返回20行那么5000:1的扫描-返回比几乎一定存在深分页问题。6.3 一个压箱底的建议给所有分页接口加上“扫描行数”监控最后分享一个我在多个项目里落地过的实践在分页接口的响应头里加一个自定义字段X-Scan-Rows把每次查询的Rows_examined透出给前端监控。这样一来当某一天用户翻页到较深位置导致扫描行数激增时监控系统会在性能恶化之前提前告警扫描行数1万绿色健康扫描行数1万-10万黄色需要关注扫描行数10万红色必须触发优化流程。这个做法相当于给深分页问题装了一个“压力表”比等到页面卡顿、用户投诉、CPU打满的时候再被动排查要主动得多。在我自己的经验里绝大多数深分页故障都不是突发的都是慢慢累积直到某个阈值后暴露的只要你有监控就一定能提前发现。
