MySQL的limit 10 和 limit 1000000 的区别的庖丁解牛

“LIMIT 10和LIMIT 1000000, 10”常被误解为“只是取的数据位置不同”或“无非是多扫描几行”。但本质上它们是两种截然不同的 I/O 消耗模型LIMIT 10是**“浅层扫描”**是数据库最喜欢的操作几乎零成本。LIMIT 1000000, 10深分页是**“深层丢弃”是数据库的噩梦意味着要读取、解析、过滤一百万行数据然后残忍地扔掉前 999,990 行**只留最后 10 行。理解它们的区别就是理解为什么你的网站在数据量大了之后翻页会越来越慢以及如何避免这种“自杀式”的查询。一、核心本质获取 vs 丢弃1. 语义对比语句语义本质动作比喻LIMIT 10取前 10 条读取并返回去图书馆拿起书架上前 10 本书拿走。LIMIT 1000000, 10跳过 100 万条取接下来 10 条读取 过滤 丢弃 返回去图书馆把前 100 万本书全部拿下来翻开看一眼扔回地上直到第 100 万零 1 本才带走。2. 代价分析LIMIT 10MySQL 只需要找到起始点读 10 行结束。工作量 10 行。LIMIT 1000000, 10MySQL 必须找到起始点读1,000,010 行判断每一行是否符合WHERE条件排序如果需要然后丢弃前 100 万行最后返回 10 行。工作量 ≈100 万行。 核心洞察深分页的性能瓶颈不在于“返回的那 10 行”而在于“被丢弃的那 100 万行”。你付了读 100 万行的钱IO/CPU却只拿了 10 行的货。二、执行机制引擎是如何“受苦”的假设表中有 200 万行数据执行SELECT * FROM orders LIMIT 1000000, 10。1. 有索引的情况最好情况即使id有主键索引回表风暴MySQL 先在聚簇索引主键树上定位到第 1,000,001 个节点。但是如果你查询的是SELECT *非覆盖索引MySQL 必须进行回表操作。它需要读取 1,000,010 个主键 ID然后去二级索引或堆中查找对应的完整行数据。随机 I/O这 100 万次回表往往是随机的磁盘读取机械硬盘会直接卡死SSD 也会延迟飙升。排序开销如果带有ORDER BY且无法利用索引排序MySQL 需要在内存或临时文件中对 100 万 数据进行排序Filesort然后再截取。2. 无索引的情况灾难情况全表扫描直接从第一行开始逐行扫描。计数丢弃扫描一行计数器 1直到计数器 1,000,000。结果前 100 万行的扫描完全是无用功但 CPU 和 IO 实打实地被消耗了。 核心洞察LIMIT offset, size的复杂度是O(offsetsize)O(offset size)O(offsetsize)。当 offset 很大时性能线性下降直至超时。三、性能差异数量级的鸿沟让我们看一组典型的基准测试数据假设表数据量 500 万有主键索引查询语句耗时 (平均)扫描行数状态LIMIT 100.001s10⚡ 极速LIMIT 1000, 100.02s1,010 正常LIMIT 100000, 100.5s100,010 缓慢LIMIT 1000000, 105.0s1,000,010超时/锁死LIMIT 4000000, 10Timeout4,000,010☠️服务雪崩注随着 Offset 增大耗时呈线性增长。在并发高的场景下几个这样的查询就能把数据库 CPU 打满导致整个站点不可用。四、灾难场景用户行为引发的雪崩深分页不仅是慢还会引发连锁反应爬虫攻击恶意爬虫专门抓取page10000,page10001… 这种深层页面。数据库瞬间被拖垮。运营误操作运营人员在后台导出数据不小心翻到了第 5000 页直接导致生产库挂掉。长尾效应大部分用户只看前 10 页但只要有 1% 的用户翻到深处系统的平均响应时间就会被拉高拖累所有用户。连接池耗尽深分页查询执行时间长占用数据库连接的时间变长。高并发下连接池迅速被占满新请求无法获取连接导致应用层报错502/504。五、优化方案如何拯救深分页既然LIMIT offset, n有原罪我们该如何解决方案 1禁止深层翻页产品层面策略限制最大页码。例如只允许翻看前 100 页1000 条数据。理由真实用户极少会翻看第 1000 页以后的数据。如果需要找特定数据请使用搜索功能而不是翻页。话术“为了您的体验仅展示前 100 页结果请细化搜索条件。”方案 2游标法 / 延迟关联 (Seek Method / Deferred Join) ——最推荐利用上一页最后的 ID 作为起点避免扫描前面的数据。原理WHERE id last_seen_id LIMIT 10。SQL 改写-- 原始慢查询SELECT*FROMordersORDERBYidLIMIT1000000,10;-- 优化后 (假设上一页最后一条 ID 是 1000000)SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT10;优势无论偏移到多少永远只扫描 10 行利用主键索引跳跃。性能恒定与数据量无关。缺点只能连续翻页不能直接跳转到任意页码但在互联网产品中连续翻页是主流。进阶版延迟关联如果必须用 offset先查 ID再回表。SELECTt1.*FROMorders t1INNERJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,10)t2ONt1.idt2.id;原理子查询只查id覆盖索引无需回表速度极快。拿到 10 个 ID 后再通过JOIN回表查详细数据。将 100 万次随机 IO 减少为 10 次。方案 3搜索引擎替代 (Elasticsearch)场景复杂的筛选、排序、深分页需求。策略将数据同步到 ES。优势ES 的search_after机制天然支持高性能深分页且擅长全文检索。注意ES 也有深分页限制默认 10000同样推荐使用search_after而非from size。方案 4业务折中记录上下文策略不在 URL 传pagexxx而是传last_idxxx。体验用户点击“下一页”后端自动带上当前页最大 ID 去查下一条。对用户透明对数据库友好。六、架构启示设计时的“防呆”策略默认禁止大 Offset在代码层或 DB 中间件层做拦截当offset 10000时直接抛出异常或返回空防止误操作。强制使用主键排序深分页必须依赖有序的唯一索引通常是主键否则无法使用游标法。读写分离将深分页查询强制路由到从库避免拖垮主库的交易性能。缓存热点页前 10 页的数据变化频繁但访问极高可以缓存深层页面访问低可以直接查库反正也没人看或者缓存时间设长一点。 总结LIMIT深浅之争全景图维度LIMIT 10LIMIT 1000000, 10本质精准获取大量丢弃复杂度O(1)O(1)O(1)(常数级)O(N)O(N)O(N)(线性级N 为 offset)IO 类型少量随机/顺序 IO海量随机 IO (回表)性能毫秒级秒级甚至超时风险无拖垮数据库引发雪崩最优解直接使用游标法 (Where ID ?)终极心法LIMIT的偏移量是数据库的“毒性指标”。偏移量越大毒性越强。理解它们就是理解“不要让用户为数据库的无能买单”也不要让数据库为用户的随意翻页送命。记住没有免费的午餐每一行被丢弃的数据都是真金白银的 IO 资源。于索引中见捷径于游标中见智慧以限制为盾以搜索为矛于海量数据中求性能之真。最好的分页是让用户感觉不到分页的存在却让数据库始终轻盈如燕。行动指令给开发者/DBA审计慢查询立刻检查 Slow Query Log找出所有offset大于 1000 的 SQL。改造接口将前端分页参数从page/no改为cursor/id模式如果是无限滚动加载。添加保护在 DAO 层或 MyBatis/Hibernate 拦截器中设置max_offset 5000超过直接报错。优化现有 SQL对无法修改业务的深分页尝试改写为“延迟关联”写法。引入 ES如果业务强依赖复杂条件的深分页尽快将查询迁移到 Elasticsearch。教育产品告诉产品经理让用户翻到第 1000 页是反人类的设计引导他们使用搜索。监控报警对执行时间超过 1 秒的分页查询设置报警。这就是 MySQLLIMIT深浅分页于偏移中见代价于游标中见生机以索引为路避全表之坑于数据海洋中求极速之真。最后送你一句话“数据库不懂人类的耐心它只会忠实地执行你的愚蠢。别让那 100 万次的丢弃成为压垮系统的最后一根稻草。用游标代替偏移用智慧代替暴力让每一次翻页都轻如鸿毛。”