mysql深分页优化

这是一个非常经典且高频的数据库性能问题 —— “深分页 + 时间排序”（例如：LIMIT 1000000, 20 按时间排序）。直接使用 OFFSET 会导致性能急剧下降，甚至超时。

下面我将从 问题本质 → 优化思路 → 具体方案 → 注意事项 系统性地为你讲解如何高效优化。


一、为什么深分页会慢？

❌ 原始 SQL 示例：

SELECT id, title, create_time 
FROM articles 
ORDER BY create_time DESC 
LIMIT 1000000, 20;

🐌 执行过程：

  1. MySQL 要先 扫描并排序前 1,000,020 行数据；
  2. 然后丢弃前 1,000,000 行；
  3. 最后返回第 1,000,001 ~ 1,000,020 行。
即使 create_time 有索引，也要 遍历 100 万+ 行，I/O 和 CPU 开销巨大。


二、优化核心思路

“不要跳过数据，而是定位到起点。”

我们不再使用 OFFSET，而是通过 记录上一页的最后一条数据的排序字段值，用 WHERE 条件直接定位到下一页的起始位置。


三、优化方案（推荐顺序）

✅ 方案1：基于时间范围的分页（最常用）

前提：

  • 排序列（如 create_time）唯一或接近唯一（避免时间重复太多）
  • 允许“近似分页”（不要求绝对精确的偏移量）

优化写法：

-- 第一页（正常）
SELECT id, title, create_time 
FROM articles 
ORDER BY create_time DESC 
LIMIT 20;

-- 第二页开始：记住上一页最后一条记录的时间
-- 假设上一页最后一条记录的 create_time = '2024-05-01 10:30:00'

SELECT id, title, create_time 
FROM articles 
WHERE create_time < '2024-05-01 10:30:00'  -- 关键：跳过前面所有数据
ORDER BY create_time DESC 
LIMIT 20;

✅ 优点：

  • 性能极佳，直接走索引范围扫描；
  • WHERE create_time < ? 可高效利用 INDEX(create_time)。

⚠️ 缺点：

  • 如果同一时间有多条记录，可能漏数据或重复；
  • 不支持跳页（如直接跳到第 100 页）。


✅ 方案2：基于主键 + 时间的复合条件（推荐用于高并发）

适用场景：

  • create_time 不唯一，但 (create_time, id) 唯一。

优化写法：

sql深色版本-- 上一页最后一条记录：create_time='2024-05-01 10:30:00', id=12345

SELECT id, title, create_time 
FROM articles 
WHERE 
  create_time < '2024-05-01 10:30:00'
  OR (create_time = '2024-05-01 10:30:00' AND id < 12345)
ORDER BY create_time DESC, id DESC 
LIMIT 20;

✅ 优点：

  • 避免时间重复导致的数据错乱；
  • 依然能走复合索引。

📌 配套索引：

CREATE INDEX idx_time_id ON articles (create_time DESC, id DESC);


✅ 方案3：延迟关联（Deferred Join）—— 适用于无法改逻辑的场景

思路：

先通过索引快速定位主键，再回表获取完整数据。

-- 原始：LIMIT 1000000, 20（慢）
-- 优化：
SELECT a.id, a.title, a.create_time
FROM articles a
INNER JOIN (
    SELECT id
    FROM articles
    ORDER BY create_time DESC
    LIMIT 1000000, 20   -- 只查主键，速度快
) AS b ON a.id = b.id;

✅ 优点：

  • 不改变分页逻辑，兼容性强；
  • 子查询只扫描主键，效率高。

⚠️ 缺点：

  • 仍有一定性能损耗，不如方案1/2；
  • OFFSET 很大时依然慢。


✅ 方案4：游标分页（Cursor-based Pagination）

常见于 API 设计：

返回下一页的“游标”（通常是加密的时间+ID），客户端携带游标请求下一页。

{
  "data": [...],
  "next_cursor": "MTIzNDU6MjAyNC0wNS0wMSAxMDozMDowMA=="
}

后端解析游标后执行：

WHERE (create_time < ? OR (create_time = ? AND id < ?))
ORDER BY create_time DESC, id DESC 
LIMIT 20;

✅ 优点：

  • 性能最好；
  • 支持海量数据分页；
  • 适合移动端、Feed 流等场景。


四、其他优化手段

1. 强制使用索引

sql深色版本SELECT ... FROM articles USE INDEX (idx_time_id) WHERE ...

2. 冷热数据分离

  • 热点数据（最近 1 个月）放主表；
  • 历史数据归档到 articles_history 表；
  • 分页只查热点表。

3. 引入缓存层

  • 使用 Redis 缓存首页或前几页数据；
  • 深分页走数据库，但用户很少翻到。


五、总结：优化策略选择建议

场景推荐方案
普通后台管理列表✅ 方案1：时间范围分页
高并发、时间可能重复✅ 方案2：时间+ID 复合条件
无法修改分页逻辑✅ 方案3：延迟关联
App/Feed 流/无限滚动✅ 方案4：游标分页
数据量极大（亿级）✅ 游标 + 分区 + 缓存


✅ 最佳实践口诀：

“深分页，莫用 offset， 记住最后时间点， where 定位快如电， 复合索引要建全， 游标分页最极限。”

掌握这些方法，你就能轻松应对百万、千万级数据的分页性能问题。如果你有具体的表结构和查询 SQL，我可以帮你写出最优解。