MySQL 在执行深度分页时，绝不会一次性将行数据加载到内存中

MySQL 在执行 LIMIT 1000000, 10 时，绝不会一次性将前 100 万行数据加载到内存中。它的执行机制是典型的**“流式逐行处理”**，内存占用是固定且可控的，与 offset 大小无关。

下面从 InnoDB 存储引擎的执行细节为你拆解真实过程：

---

🔍 实际执行过程（非内存加载，而是流式扫描）

1. 按页读取，非按行加载
   InnoDB 数据存储在 16KB 的 Page 中。MySQL 会从索引/数据页的起始位置开始，逐页加载到 Buffer Pool 或执行缓冲区，而不是把 100 万行一次性塞进内存。

2. 逐行解析 + 条件判断 + 丢弃
   对页中的每一行，执行引擎会：

   • 解析行记录结构
   • 评估 WHERE 条件（是否匹配）
   • 参与 ORDER BY 排序比较（如有）
   • 判断当前行序号是否 < offset
   • 若满足跳过条件，直接丢弃，不进入结果集，也不长期驻留内存

3. 仅保留目标行
   当扫描到第 1000001 行时，才开始将后续 10 行组装成结果集返回给客户端。

4. 内存使用上限固定
   整个过程使用的内存主要来自几个固定参数：

   • read_buffer_size / read_rnd_buffer_size（顺序/随机读缓冲）
   • sort_buffer_size（排序临时缓冲）
   • tmp_table_size / max_heap_table_size（临时表）
     这些缓冲区大小通常在几 MB 到几十 MB，与 offset 无关。

---

📉 为什么“不占内存”却依然极慢？

深度分页的性能杀手不是内存，而是以下三项硬成本：

成本类型	具体表现	影响
🔹 I/O 成本	即使数据在 Buffer Pool 中，CPU 仍需从内存页中逐行解析；若未缓存，则触发大量磁盘随机/顺序读取	磁盘 IOPS 飙升，响应时间线性增长
🔹 CPU 成本	解析 100 万行记录头、解码变长字段、执行 WHERE 过滤、排序比较，消耗大量 CPU 周期	查询线程长时间占用 CPU，影响并发
🔹 Buffer Pool 污染	大量无关数据页被加载进内存，挤占热点页缓存位置，导致后续查询缓存命中率下降	拖慢整个实例的其他查询
🔹 排序放大效应	若带 ORDER BY，MySQL 需对过滤后的中间结果集排序。offset 越大，排序数据集越大，可能触发 filesort 或落盘临时表	内存/磁盘开销呈指数级上升

---

✅ 核心结论

LIMIT offset, size 的瓶颈是 “无效扫描的 I/O + CPU 成本”，而非“内存占用”。
MySQL 采用流式处理，内存使用恒定，但扫描行数、回表次数、排序开销会随 offset 线性/指数增长，这才是性能断崖的根源。

这也正是工业界优化深度分页的底层逻辑：

• 游标分页：直接定位起始点，扫描行数从 O(offset+size) 降至 O(size)
• 延迟关联：子查询仅扫描索引（不触发表数据回表），大幅降低 I/O 与 CPU 解析成本
• 覆盖索引：彻底消除回表，让扫描成本趋近于纯内存索引遍历

如果你在实际业务中遇到具体慢查询，可以提供 EXPLAIN 执行计划，我可以帮你精准定位是回表过多、排序开销大，还是 Buffer Pool 命中问题，并给出针对性 SQL 改造方案。