SELECT * FROM table LIMIT 1000000, 10的庖丁解牛

SELECT * FROM table LIMIT 1000000, 10是典型的深度分页查询，表面看是“跳过 100 万行取 10 行”，实则触发全表扫描 + 内存排序，导致磁盘 I/O 爆炸、响应时间飙升。

一、执行机制：MySQL 如何处理LIMIT offset, size？

▶ 1.执行流程

▶ 2.关键问题

• 必须扫描offset + size行：
  • 即使只需 10 行，也需读取 1,000,010 行
• 无法跳过中间行：
  • MySQL 不存储“第 N 行的物理位置”（除非聚簇索引）

💡核心认知：
LIMIT offset, size的成本 = O(offset + size)，而非 O(size)

二、性能陷阱：为什么深度分页如此昂贵？

▶ 1.磁盘 I/O 爆炸

• 场景：
  • 表数据未完全缓存到 Buffer Pool
  • 每读一行需 1 次磁盘随机读（HDD ≈ 10ms/次）
• 计算：
  • 1,000,010 行 × 10ms =2.78 小时（理论值，实际因缓存略低）

▶ 2.内存与 CPU 浪费

• 排序开销：
  • 若无合适索引，需filesort（磁盘临时文件）
• 网络传输：
  • 丢弃的 100 万行仍需从存储引擎传到 Server 层

▶ 3.锁竞争加剧

• InnoDB 行锁：
  • 扫描过程中持有行锁 → 阻塞其他写操作
• MVCC 版本链：
  • 大量历史版本堆积 → Undo Log 膨胀

三、工程优化：四种替代方案

▶ 方案 1：基于游标的分页（推荐）

• 原理：
  • 记录上一页最后一条记录的排序字段值
  • 下一页从该值开始查询
• 示例：

  -- 第一页SELECT*FROMordersWHEREid>0ORDERBYidLIMIT10;-- 第二页（假设上一页最大 id=100）SELECT*FROMordersWHEREid>100ORDERBYidLIMIT10;

• 优势：
  • 执行计划：range→ 直接定位起始点
  • 成本：O(size)，与 offset 无关

▶ 方案 2：延迟关联（Deferred Join）

• 原理：
  • 先通过覆盖索引获取主键
  • 再回表查询完整数据
• 示例：

  SELECTt.*FROMorders tINNERJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,10)tmpONt.id=tmp.id;

• 适用场景：
  • 主键为聚簇索引（InnoDB）
  • 覆盖索引可避免回表

▶ 方案 3：记录偏移量（适用于静态数据）

• 原理：
  • 预先计算每页的起始主键
  • 存储到缓存（如 Redis）
• 示例：

  // 缓存第 100000 页起始 ID$startId=Redis::get('page_100000_start_id');$rows=DB::select("SELECT * FROM orders WHERE id >= ? ORDER BY id LIMIT 10",[$startId]);

▶ 方案 4：禁止深度分页

• 产品设计：
  • Google 搜索仅显示前 10 页
  • 电商网站限制“跳转到第 N 页”
• 技术实现：

  if($page>100){thrownewException('超过最大页数');}

四、避坑指南

五、终极心法

**“LIMIT 不是分页，
而是性能的悬崖——

• 当你使用 OFFSET，
  你在支付线性成本；
• 当你切换游标，
  你在享受常数时间；
• 当你限制深度，
  你在守护系统。

真正的查询优化，
始于对执行计划的敬畏，
成于对细节的精控。”

结语

从今天起：

1. 深度分页必用游标方案（WHERE id > last_id）
2. 用EXPLAIN验证执行计划（避免Using filesort）
3. 产品层限制最大页数（如 ≤ 100 页）

因为最好的分页，
不是跳过百万行，
而是精准定位下一程。