在日常开发中，我们经常需要对数据库中的数据进行分页展示。特别是当表数据量达到几十万甚至上百万级时，传统的 LIMIT 分页方式会面临严重的性能瓶颈。今天，我将分享一个真实的性能优化案例，通过模拟大页码查询的现场，从90秒缩短到 965 毫秒，显著提升了查询效率。

本篇文章将从问题出现的原因、索引原理、优化思路和最终实战效果等方面，为你全面讲解如何高效处理 MySQL 大数据分页查询问题。

---

一、问题背景：大页码分页查询慢如龟速

假设我们有一张用户行为记录表 user_action_log，记录用户每次的页面浏览信息，包括访问时间、IP、行为类型等信息。

这张表的总数据量为：

1,030,000 条记录（103 万）

前端通过表格按「访问时间」升序排列展示数据，并提供分页功能。默认每页展示 20 条数据，总页数约为：

1,030,000 / 20 ≈ 51,500 页

用户提出一个需求，要能跳转到任意页查看，比如 第 50,000 页。

我们用常规的分页 SQL 查询如下：

SELECT * FROM user_action_log
ORDER BY create_time ASC
LIMIT 999980, 20;

当查询第 50,000 页（即第 999,980 条数据）时，这条 SQL 居然耗时 90 秒以上，严重影响用户体验。

---

二、为什么大页码分页会这么慢？

我们先来分析下 MySQL Limit 的原理：

• LIMIT offset, size 实际执行时，并非直接跳到 offset，而是从头开始扫描，跳过前 offset 条记录，然后再取 size 条。
• 如果 offset 是 999,980，就要扫描接近 100 万条数据再返回 20 条。

再来看看执行计划：

EXPLAIN SELECT * FROM user_action_log
ORDER BY create_time ASC
LIMIT 999980, 20;

输出如下：

type: ALL
possible_keys: create_time
key: NULL
Extra: Using filesort

• 明明 create_time 上建立了索引，为什么没用？
  • 因为没有 WHERE 条件，优化器认为不如全表扫描。
  • ORDER BY + LIMIT offset 仍会对所有数据排序后再截取，导致慢如蜗牛。

---

三、为什么索引没有生效？MySQL 索引原理复盘

在 InnoDB 中：

• 主键（如 ID）默认使用 聚集索引，叶子节点存储完整的行记录。
• 普通索引（如 create_time）为 二级索引，叶子节点仅存储字段值和对应的主键 ID。

当我们执行：

SELECT create_time FROM blog_browse_history 
ORDER BY create_time 
LIMIT 500000, 1;

如果这个字段是索引的一部分，并且查询字段只包括索引字段，则可以使用 索引覆盖（Covering Index），避免回表，性能非常好。

但是当我们用 SELECT * 时，MySQL 需要从索引跳转（回表）去主键索引中读取完整记录，仍然效率低下。

我们为 create_time 创建了一个普通的 B+ 树索引：

CREATE INDEX idx_create_time ON user_action_log(create_time);

但 MySQL 查询优化器仍然 选择全表扫描。原因是：

• 虽然 create_time 是有序的，但在没有过滤条件的前提下，优化器判断索引遍历成本更高。
• 另外，索引查询后还需回表取出完整行数据（因为用了 SELECT *），进一步拉低效率。

---

四、如何优化？引入“索引覆盖”+“二次查询”

核心思想是：

不要直接跳第 999,980 条，而是先“找到它”，再取后续数据。

第一步：利用索引覆盖查询目标页首条数据的 create_time

SELECT create_time
FROM user_action_log
ORDER BY create_time ASC
LIMIT 999980, 1;

• 因为只查询 create_time，刚好是索引字段，无需回表，利用了 索引覆盖（Covering Index），非常快。
• 实际执行时间 < 200ms

执行计划显示：

type: index
key: idx_create_time
Extra: Using index

说明确实用到了索引覆盖。

第二步：用上一步拿到的 create_time 查询该页数据

假设拿到的时间为 2025-07-05 10:15:23，则主查询变为：

SELECT *
FROM user_action_log
WHERE create_time >= '2025-07-05 10:15:23'
ORDER BY create_time ASC
LIMIT 20;

• 这条 SQL 用了 范围查询 + 有序索引，可以快速定位并读取数据。
• 执行时间约 2 秒，相比原来 90 秒提升巨大。

---

五、进一步优化：连续分页中的“时间游标”策略

大多数情况下，用户并不是直接跳到第 50000 页，而是连续点击「下一页」或「上一页」。

因此我们可以用 “上一页最后一条记录的 create_time” 作为下一页的起始时间：

SELECT *
FROM user_action_log
WHERE create_time > '2025-07-05 10:15:23'
ORDER BY create_time ASC
LIMIT 20;

这个优化有几个显著好处：

1. 无需计算 offset，查询性能稳定；
2. 只读一次索引树，无需跳过几十万行；
3. 完全避免使用 LIMIT offset 带来的性能问题。

执行时间优化到了：

965 毫秒

在真实生产环境，配合缓存，可以做到 <100ms 级别。

---

六、特殊情况处理：create_time 有重复怎么办？

如果在高并发系统中，有很多条记录 create_time 值完全相同，连续分页时就会导致死循环（一直翻不到下一页）。

如何解决？

引入第二排序字段，比如主键 id：

-- 分页查询
SELECT *
FROM user_action_log
WHERE (create_time, id) > ('2025-07-05 10:15:23', 1009234)
ORDER BY create_time ASC, id ASC
LIMIT 20;

保证：

• 即使 create_time 相同，id 也不会相同，避免重复读取；
• 同时满足有序性，保证分页一致性。

这也叫做 基于游标的分页（Keyset Pagination），是大数据分页的最佳实践。

---

七、为什么很多产品不再使用页码跳转？

你会发现，如淘宝、京东、微博等平台：

• 不再提供“跳转到第几页”
• 仅保留“上一页 / 下一页”
• 移动端更是采用 下拉加载 的方式（无限滚动）

原因有三：

1. 避免 COUNT(*) 查询
   SELECT COUNT(*) FROM table 在 InnoDB 中是实时计算，非常慢，尤其在大数据量、多表、分库的场景下。
2. 用户行为决定技术选型
   99.999% 的用户不会去点第 2 万页，第 5 万页，甚至不知道他们是否存在。
3. 业务设计优于技术优化
   与其花大量精力去提升第 5 万页的查询性能，不如限制最多显示 100 页、支持前端缓存等。

---

八、总结：MySQL 大数据分页的优化建议

场景	推荐策略
普通分页（小页码）	可用 LIMIT offset, size
大页码分页	使用索引覆盖 + 子查询定位游标
连续分页（上下页）	使用游标分页（基于上一页最后一条记录）
高并发下游标重复	使用 (create_time, id) 联合索引和双重条件确保唯一性
前端需要总页数	尽量避免 COUNT(*)，通过业务逻辑控制页面数量，或异步计算总数
移动端或交互更强的页面	使用“无限滚动”设计，按需加载

---

九、最后的思考

技术的本质是为业务服务的，分页查询的本质并不在于第 5 万页的查询有多快，而是：

• 用户是否真的需要这些数据？
• 能否通过业务限制（如最大页数、缓存、推荐等）绕过复杂计算？

所以，在设计系统时，技术优化只是手段，业务设计才是根本。