MySQL 深度分页与 LIMIT 性能优化

一、核心问题：LIMIT offset, size 性能差异

1. 执行机制差异（以 LIMIT 100000000, 10 vs LIMIT 10 为例）

MySQL 采用 分层架构（Server 层 + 存储引擎层）：

  • Server 层：负责 SQL 解析、优化、执行调度。
  • 存储引擎层（如 InnoDB）：负责具体数据读写。

当执行 LIMIT offset, size 时，逻辑为：

  • 存储引擎需遍历并读取前 offset 条数据，再返回后续 size 条。
  • 对 LIMIT 100000000, 10：需先读取并丢弃前 1 亿条，再取后续 10 条, 会导致 磁盘 I/O、内存、CPU 浪费严重。
  • 对 LIMIT 10：直接读取前 10 条 所以 性能更优。

二、深度分页（大 offset）优化方案

深度分页的本质是 offset 过大导致数据库需跳过海量数据，成本极高。以下是主流优化思路：

方案 1：记录上一次 ID（游标式分页）

核心逻辑

利用主键/唯一键的有序性，我们可以记录“上一页最后一条数据的 ID”，下一次请求时通过 WHERE id > 上一页最后 ID 实现“续页查询”，彻底绕开 offset。

示例

假设当前页最后一条数据 ID 为 last_id，每页取 10 条：

SELECT * FROM table 
WHERE id > #{last_id} 
ORDER BY id 
LIMIT 10;

适用场景

  • 下拉加载、连续滚动翻页（如小红书评论列表、抖音信息流）。
  • 优势：轻量高效，无需扫描海量历史数据。
  • 局限：不支持直接跳页（如直接跳到第 56 页，因无法预知中间页 [如55页最后一条数据] 的 ID 边界）。

方案 2：子查询优化（利用二级索引减少扫描）

核心逻辑

通过二级索引的“轻量性”（仅包含索引列 + 主键），先定位到“偏移后的数据主键 ID”，再通过主键索引回查完整数据，避免全表扫描。

示例

原查询（全表扫描风险高）：

SELECT * FROM A 
WHERE name = 'user_longyu' 
ORDER BY id 
LIMIT 100000000, 10;

优化后（先查二级索引拿 ID，再回查主键）：

SELECT * FROM A 
WHERE name = 'user_longyu' 
  AND id >= (
    SELECT id FROM A 
    WHERE name = 'user_longyu' 
    ORDER BY id 
    LIMIT 100000000, 1
  ) 
ORDER BY id 
LIMIT 10;

适用场景

  • 表存在二级索引（如 name 字段有索引），且需按索引字段筛选 + 分页的场景。

  • 优势：减少扫描范围，利用索引加速。

  • 扩展：子查询也可改写为 JOIN 形式，本质逻辑一致。

    SELECT A.* FROM A 
INNER JOIN (
	SELECT id FROM A 
    WHERE name = 'user_longyu' 
    ORDER BY id 
    LIMIT 10 OFFSET 100000000
) as t ON A.id=t.id
ORDER BY id;

方案 3：引入搜索引擎（如 Elasticsearch）

核心逻辑

对“高并发 + 复杂筛选 + 深分页”场景（如电商商品搜索、微博热搜），用 Elasticsearch 等分布式搜索引擎承接分页查询，利用其分布式存储 + 近实时索引特性扛量。

警示

若对 ES 不熟，面试中慎提！因为 ES 自身也有深度分页问题（如 from + size 过大时性能暴跌），需额外学习 scroll 或 search after 等优化手段，否则易暴露知识盲区。

补充优化方向

中间件层：分库分表 + 路由优化

  • 分库分表后按分片键分页：若数据已分库分表，通过“分片键 + 分页”路由到指定分片，避免跨分片全量扫描（如按 user_id % 100 分库，查用户订单时仅扫对应分片）。
  • 分页中间件：如 ShardingSphere、MyCat 等，部分版本支持分页语句的智能路由优化（需结合场景评估）。