为什么深度分页性能极差？

这是一个非常经典的数据库与 ORM 框架协同问题。首先需要澄清一个核心认知：深度分页性能差的根本原因不在 MyBatis 本身，而在于底层关系型数据库（如 MySQL）的 LIMIT offset, size 执行机制，以及传统分页插件的“机械翻译”方式。 MyBatis 只是一个 SQL 映射执行器，它忠实地把 LIMIT 1000000, 10 交给数据库，数据库怎么执行，性能瓶颈就出在哪里。

下面从底层原理、框架行为到工业级优化方案，为你系统拆解。

---

🔍 一、为什么深度分页性能极差？（4 大根本原因）

1️⃣ LIMIT offset, size 的“扫描+丢弃”机制

MySQL 执行 LIMIT 1000000, 10 时，并不是直接定位到第 100 万行，而是：

1. 从索引/表的第一行开始逐行扫描
2. 跳过前 100 万行（不返回，但必须读）
3. 取出接下来的 10 行返回

👉 本质是 O(N) 操作。offset 越大，数据库要“白读”的数据越多，CPU、磁盘 I/O、Buffer Pool 命中率全部暴跌。

2️⃣ 索引回表放大效应（Bookmark Lookup）

如果查询使用了非覆盖索引（如 WHERE status=1 ORDER BY create_time DESC），MySQL 的执行路径是：

1. 走二级索引找到满足条件的主键 ID
2. 每扫描一行都要回表查完整行数据（用于 SELECT * 或过滤未命中索引的字段）
3. offset 越大，回表次数呈线性增长，随机 I/O 爆炸。

3️⃣ WHERE + ORDER BY + LIMIT 组合拳成本

实际业务分页几乎都带条件排序。MySQL 需要先：

1. 过滤 WHERE 条件
2. 排序 ORDER BY（可能触发 filesort 或临时表）
3. 再执行 LIMIT offset, size
   深度分页时，排序和过滤的中间结果集可能高达数百万行，内存与临时磁盘开销极大。

4️ MyBatis 分页插件的“双重查询”开销

主流插件（如 PageHelper、MyBatis-Plus 分页插件）会自动拦截 SQL 并改写为两条：

-- 1. 统计总数（往往走全表扫描或复杂索引）
SELECT COUNT(*) FROM table WHERE ... 
-- 2. 分页查询
SELECT ... FROM table WHERE ... LIMIT 1000000, 10

👉 COUNT(*) 在大数据量下本身就慢，加上 LIMIT 深度扫描，一次翻页 = 两次重型查询。

---

🛠️ 二、工业级优化方案（按推荐程度排序）

✅ 方案 1：游标分页 / 键集分页（Keyset Pagination） 最优解

核心思想：放弃 offset，改用上一页最后一条记录的主键/排序字段作为游标。

-- 传统分页（慢）
SELECT * FROM orders WHERE user_id = 100 ORDER BY id DESC LIMIT 1000000, 10;

-- 游标分页（快，O(1) 定位）
SELECT * FROM orders 
WHERE user_id = 100 AND id < #{last_id}  -- 上一页最后一条的 id
ORDER BY id DESC 
LIMIT 10;

优点：

• 执行计划稳定，始终走索引范围扫描，EXPLAIN 显示 type=range
• 不受数据量增长影响，性能恒定
• 天然适合“无限滚动”、“下一页”场景

缺点：

• 不支持跳页（如直接点“第 500 页”）
• 业务需改造前端交互（隐藏页码，改为“加载下一页”）

MyBatis 实现建议：手写 SQL，使用 @SelectProvider 动态拼接游标条件。

---

✅ 方案 2：延迟关联（Deferred Join）📈 传统优化

核心思想：先通过覆盖索引查出主键 ID，再 JOIN 原表获取完整数据。

SELECT t.* 
FROM orders t
INNER JOIN (
    SELECT id FROM orders 
    WHERE status = 'PAID' 
    ORDER BY create_time DESC 
    LIMIT 1000000, 10
) tmp ON t.id = tmp.id;

优点：

• 子查询只走索引，不触发回表，大幅减少 I/O
• 兼容现有页码交互，无需改前端

缺点：

• 仍依赖 LIMIT offset，offset 极大时子查询依然慢
• 仅适用于 ORDER BY 字段有索引的场景

---

✅ 方案 3：覆盖索引 + 限制 SELECT 字段

确保 SELECT 的所有字段 + WHERE/ORDER BY 字段能组成一个覆盖索引：

-- 创建复合索引
CREATE INDEX idx_status_time ON orders(status, create_time, id, amount);

-- 查询只取索引字段
SELECT id, create_time, amount FROM orders 
WHERE status = 'PAID' ORDER BY create_time DESC LIMIT 1000000, 10;

效果：彻底避免回表，性能提升 3~10 倍。但受限于业务字段需求，难以全覆盖。

---

✅ 方案 4：业务层限制最大深度 + 引导搜索

• 限制最大页码（如 maxPage = 100），超过提示“请使用搜索/筛选”
• 提供时间范围、状态筛选等维度，缩小结果集后再分页
• 导出功能走异步任务（生成 CSV/Excel 下载链接）

架构原则：深度分页往往是产品交互设计问题，而非纯技术问题。C 端 App（如抖音、微博）早已淘汰页码，改用游标无限加载。

---

✅ 方案 5：海量数据交由专用引擎

当单表 > 5000 万行，且频繁深度分页/复杂检索时：

• Elasticsearch：search_after 游标分页，毫秒级响应
• ClickHouse / Doris：列存 + 向量引擎，适合分析型分页
• Redis + 业务缓存：热点数据预计算分页结果

---

📊 三、方案对比与选型指南

方案	适用场景	性能	是否支持跳页	改造成本	推荐指数
游标分页	列表无限加载、移动端、日志流	⭐⭐⭐⭐⭐	❌ 仅支持上一页/下一页	中（需改交互）	🏆 首选
延迟关联	传统后台系统、必须保留页码	⭐⭐⭐⭐	✅	低（改 SQL 即可）	🥈 常用
覆盖索引	字段少、查询模式固定	⭐⭐⭐⭐	✅	低（加索引）	辅助
限制深度+搜索	用户习惯跳页、数据量大	⭐⭐⭐	✅	低（产品调整）	✅ 必做
ES/数仓替代	海量数据、复杂检索+分页	⭐⭐⭐⭐⭐	✅	高（架构升级）	终局方案

---

💡 四、MyBatis 开发实践建议

1. 警惕自动分页插件：PageHelper/MP 分页插件在 offset > 50 万时性能断崖式下跌。生产环境建议：
   • 小数据量（< 10 万）：可继续使用插件
   • 中大数据量：关闭插件，针对慢查询手写优化 SQL
2. 关闭不必要的 COUNT：前端若不需要总条数/总页数，插件配置 optimizeCountSql=false 或手动跳过 COUNT 查询。
3. 使用 @SelectProvider 动态构建游标 SQL：

   @SelectProvider(type = OrderSqlProvider.class, method = "selectByCursor")
   List<Order> selectNextPage(@Param("userId") Long userId, 
                              @Param("lastId") Long lastId, 
                              @Param("size") int size);
   

4. 监控与告警：通过 slow_query_log + Prometheus 监控 rows_examined，当单页扫描行数 > 10 万时触发告警。

---

✅ 总结

MyBatis 不背锅，数据库的 LIMIT offset 机制才是性能杀手。
深度分页优化本质是：用“定位”代替“扫描”，用“游标”代替“偏移”，用“业务约束”代替“技术硬扛”。

在实际项目中，推荐组合拳：

1. 默认使用 游标分页（产品侧接受无限加载）
2. 必须保留页码的场景使用 延迟关联 + 覆盖索引
3. 单表超 5000 万行直接上 Elasticsearch
4. 所有分页接口设置 最大深度限制 与 慢 SQL 熔断

如果你提供具体的表结构、查询语句和分页场景，我可以为你输出针对性的优化 SQL 与 MyBatis 映射配置。