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('超过最大页数');}

四、避坑指南

陷阱	破局方案
盲目使用`OFFSET`	深度分页必用游标方案
忽略排序字段选择	游标字段必须是索引且唯一（如自增 ID）
宽表全字段查询	仅`SELECT`必要字段，减少回表