基于 MySQL 的海量据量翻页优化

PS: 2026年了，5年过去了，烂尾了5年的文章，原本以为真的一直烂下去了，直到 Claude 帮我写完了最后的小节

PPS: 本文续篇 MySQL 深分页优化：2026 年的工程答案 给出了最新的工程答案

提出问题

在 MySQL 中，一张数据量膨胀到 1000万的表，进行全量分页查询的时候，随着页码往后翻，MySQL 响应的延迟越来越大。

例如，查询 900 万以后的 20 条数据，耗时长达 98s。

-- 98.4s
select * from `logs` limit 9000001, 20; 

而查询 100 万偏移的 20 条，时间要缩短不少，大约为 10s。

显然，按照这种趋势下去，单表膨胀到 1 亿时，将无法在有限的时间里完成分页查询。

解决问题

通过命中索引，进行大范围的跳过，同样的查询需求，改写成下面的查询语句：

-- 38ms
select * from `logs` where id > 9900001 limit 20; 

可以看到，执行时间只需 38ms，时间缩短了 2578 倍。

究竟是什么原因造成了如此大的时间差异呢？

通过 explain 命令可以查看每一个 sql 语句的执行计划。

优化前

看原始查询，通过 limit + offset 来翻页的执行计划如下：

mysql> explain select * from `logs` limit 9990000, 20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: logs
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16296105
        Extra:
1 row in set (0.00 sec)

优化后

而优化后的语句执行计划如下：

mysql> explain select * from `logs` where id > 9990000 limit 20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: logs
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 8148052
        Extra: Using where
1 row in set (0.00 sec)

可以看出来，此次查询在 where 子句中命中了 Primary 主键索引，执行了 range 扫描查询。

拓展

思考：MySQL 有哪些索引呢?

不同类型的索引

当添加了过滤条件之后，除了 Primary Key 索引可以用之外，其他字段的索引是不是也一样有效呢？

给字符串字段添加索引：

以下内容于 2026.06.28 与 Claude 合写

create index idx_logs_level on logs (level);

然后用该字段进行范围查询：

-- 使用普通索引
explain select * from `logs` where level = 'ERROR' and id > 9000000 limit 20\G;

执行计划同样会命中 idx_logs_level，走 ref 或 range 类型扫描，而不是全表扫描（ALL）。

索引的有效性取决于 选择性（Selectivity）：某列不重复值的比例越高，索引效率越好。对于 level 这样只有 ERROR / WARN / INFO 等几个枚举值的字段，MySQL 可能选择放弃索引直接全表扫描——因为回表代价更大。可以通过 force index 强制验证，也可以借助复合索引来提升选择性。

不同的存储引擎

MySQL 默认的存储引擎是 InnoDB，其索引结构是 B+ 树：叶子节点存储完整行数据（聚簇索引），二级索引的叶子节点存储主键值，查询时需要回表。

什么是回表？

在MySQL中，回表是指查询语句在使用二级索引（非主键索引）查找到目标数据后，由于二级索引树上只包含“索引列”和“主键值”，系统必须根据获取到的主键值，重新到聚簇索引（主键索引）树中检索完整行数据的过程。

这正是 limit offset 慢的本质原因：MySQL 必须从头遍历 B+ 树叶子链表，读取 offset 条完整行之后才丢弃，白白消耗大量 I/O。改写成 where id > N 后，直接定位到 B+ 树的起始节点，跳过了无效回表。

而 MyISAM 将索引与数据分开存储，不存在聚簇索引，回表始终需要一次额外的磁盘寻址。在大偏移分页场景下，MyISAM 的表现通常比 InnoDB 更差。

现代 MySQL（5.5+）默认使用 InnoDB，MyISAM 仅在只读归档场景偶尔使用。

小结

大表分页的核心思路只有一条：把随机的大偏移，变成有序的范围定位。