MySQL分页机制深度解析

1. MySQL分页机制原理与性能分析

MySQL的分页通常使用LIMIT offset, size语法实现。在讨论limit深度分页的性能问题之前，我们先来关注于一个场景。

假设场景​：按id倒序分页查询，每页10条。

​第一页​：SELECT * FROM table ORDER BY id DESC LIMIT 0, 10;(期望返回id 100, 99, 98, ..., 91)

​第二页​：SELECT * FROM table ORDER BY id DESC LIMIT 10, 10;(期望返回id 90, 89, ..., 81)

假设以下场景，场景之间是完全独立的：

• 遗漏：假设在查询第一页和第二页之间，id=100被删除，由于id=100被删除，原来第二页的第一条数据id=90现在变成了“当前所有数据”中的第10名。它既可能出现在第一页的末尾（如果此时页面被刷新），也可能直接别遗漏（用户翻页）。

• ​重复​：假设在查询第一页和第二页之间，id=101被插入，新插入的id=101现在排名第一，它“挤占”了原本第一页数据的位置。导致原本在第一页末尾的id=91被“挤”到了第二页的开头，而你之前已经看过了id=91（在第一页），这就感觉像是id=91这条记录被重复了。

• ​问题分析：原因在于limit关注的是数据本身，并不在乎两次查询之间数据顺序的动态变更，从用户视角来看，他是不希望在连续分页的过程中出现两页数据不一致的问题的。而要保持这种一致性，你在分页查询的时候不允许修改数据显然不太合理（甚至可能阻塞业务）。但是也不用担心，下文介绍的游标分页（基于上一次查询最后一条记录的有序字段值进行查询）可以简单而高效地解决这个问题。

核心原理与B+树表现

在B+树索引结构中的表现如下：

• ​索引扫描​：当执行SELECT * FROM table LIMIT 100000, 10时，

  • MySQL首先通过B+树索引（如有合适的索引）或全表扫描定位到第一条记录。如果无索引，存储引擎将执行全表扫描（本质上就是读取聚簇索引，然后逐行比较是否满足条件），虽然是顺序读取，但是需要扫描的数据量过大，IO频繁，很有可能会直接打挂数据库

• ​顺序遍历​：InnoDB引擎随后沿着B+树的叶子节点双向链表顺序扫描数据页。

  • 它需要读取并跳过前offset条记录，才能获取后续的size条记录。

  • 回表操作​：若查询使用了非聚簇索引​（二级索引），且所需字段未完全包含在索引中（即非覆盖索引），引擎需根据索引叶子节点中存储的主键值回到聚簇索引（主键索引）检索完整数据行，这个过程称为回表​（Referring Table）。每回表一次至少增加一次磁盘I/O（若目标数据页不在Buffer Pool中）。

  • 由于二级索引本身不存储数据，在不满足索引下推条件时必然会发生回表，而回表往往是随机IO操作，性能极差（当起始位置很深时，甚至比全表扫描还差，全表扫描最起码还是顺序IO）。

  • 注意即使目标数据页在Buffer Pool中，也会由于大量数据的查询导致缓存“污染”，大量真正的“热点数据”页被挤出LRU，缓存命中率降低

    • 注意MYSQL的LRU链表分别新生代和老年代，大范围的加载数据并不会真的导致当前的“热点”被挤出，实际被更新的只是老年代。但是被污染的“老年代”其实也是候选的热点数据，“污染”依然存在

  • 丢弃数据的操作是由存储引擎完成的，引擎必须物理地遍历和计数这些记录（在这个过程中发生了大量IO以及cpu计数），这也是深度分页性能劣化的根源

  • 例如以下查询，若name有索引但非覆盖索引，需先通过二级索引查找100000+10条记录的主键，再回表100000+10次获取完整数据，最后丢弃前100000条

    SELECT name, address FROM users WHERE name LIKE 'leafs_%' ORDER BY name LIMIT 100000, 10;

• 补充一下在MySalm引擎中，其表数据（.MYD文件）和索引（.MYI文件）是分开存储的，索引（B树）中存储的是指向数据文件物理位置的“指针”​​，所以其回表本质上是去读取存储数据的物理文件

  • 在深度分页下，其会发生多次根据索引中的指针去读取物理表文件，这个过程会发生大量的随机IO，而且由于MySalm引擎不支持行级锁，在这个过程中会锁表，这是大型系统绝对不能接受的。

  • 在MySalm引擎下绝对不要使用深度分页，大概率会直接导致数据库不可用

深度分页性能劣化根源

深度分页（如LIMIT 1000000, 10）性能急剧下降的主要原因：

1. ​无效记录的巨额扫描与丢弃​：MySQL必须先扫描并跳过前offset条记录。offset值越大，需扫描的数据量越多，消耗的CPU计算资源和磁盘I/O也越多。例如LIMIT 1000000, 10需先扫描前1000010条记录。

2. ​巨大的回表开销​：若查询需回表，深度分页将引发大量随机I/O。引擎需先从二级索引顺序获取大量主键，再根据这些主键随机访问聚簇索引中的数据页。这种大量随机I/O在机械硬盘上性能极差。

3. ​缓冲池污染与缓存命中率下降​：深度分页查询可能将大量非热点数据（前offset条记录所在的数据页）加载到InnoDB Buffer Pool，​挤占真正热点数据的内存空间，降低整体缓存命中率。

4. ​优化器可能放弃索引​：当offset极大时，优化器经成本估算后可能认为全表扫描比使用索引且回表大量数据成本更低，从而选择性能更差的全表扫描。

​性能下降本质​：深度分页性能瓶颈并非数据获取本身，而在于大量无效数据的定位、扫描、传输和丢弃。其时间复杂度近似O(N)​，随offset增大线性增长。

2. 游标分页

首先需要明确的是，游标分页不局限于正序，逆序也是可以的，因此是很适用于滚动翻页的。而且对于数据库的批量查询，通过游标的方式也可以合理规划每批次的数据量，避免增大数据库io和网络传输压力。

​工作原理​：游标分页利用有序且唯一的字段​（如自增主键id、或时间戳created_at结合id）作为“游标”，记录上一页最后一条记录的位置。查询下一页时，直接定位到该游标之后的数据。

-- 第一页
​​​​​​​SELECT * FROM table ORDER BY id ASC LIMIT 10;
-- 假设最后一条记录的id=10，下一页查询则为
SELECT * FROM table WHERE id > 10 ORDER BY id ASC LIMIT 10;

对于多字段排序（如按created_at和id降序），游标需包含这两个字段：

SELECT * FROM posts WHERE (created_at < '2023-10-01 12:00:00') 
   OR (created_at = '2023-10-01 12:00:00' AND id < 100) 
ORDER BY created_at DESC, id DESC LIMIT 10;

​优势​：

• ​极致性能​：通过WHERE条件直接利用B+树索引的定位能力，跳过所有前置记录。时间复杂度为O(logN)​，几乎恒定，不受页数深度影响。

• ​数据一致性相对更好​：在数据增量追加的场景下（如按时间倒序分页且很少删除或更新历史数据），能较好避免传统LIMIT OFFSET因数据漂移导致的重复或遗漏。

​劣势​：

• ​无法随机跳页​：业务上通常只支持顺序翻页​（上一页、下一页），难以直接跳转到任意页码。

  • 可以考虑缓存每一页尾部对应的“游标”，通过传入这个游标，就可以获得下一页的数据。通过这种方式可以实现随机跳页的效果

• ​实现稍复杂​：需业务逻辑记录和传递游标值（如最后一条记录的id或复合值）。

  • 建议封装在Repository 层，在内部实现起始游标的查询和分页

• ​需要谨慎查询条件​：排序条件或WHERE条件改变时，游标可能失效。

  • 排序条件本身并不影响到游标，但是游标分页的设计一定要足够鲁棒，确保查询到的传入条件以及排序条件在一次“完整的分页查询”中不会改变。

    • 例如第一页使用ORDER BY created_at DESC, id DESC，第二页使用ORDER BY name ASC，肯定会导致两页的内容是混乱的

    • 以及要小心排序方向的改变也会导致页面的重复或者遗漏

  • 游标应该加入一定的保密机制，如不透明令牌，避免篡改

  • 底层数据变更不会影响到游标分页，游标后的数据变更本身就是可见的。因为过去的页是通过游标得到的（如果你希望底层旧数据-游标之前的数据的变更对游标保持可见性，需要额外设计，如引入redis直接缓存要展示的数据id等，此次不再赘述，我也并不建议底层数据的变更影响到用户当前这一轮次的分页查询）。但是要求作为游标的字段必须要是唯一的。如果要求按照非唯一字段排序，一定不要忘记在排序条件中添加唯一字段的排序，避免分页错乱

3.PageHelper分页

本小节我们来讨论一下Mybatis（java orm框架）的PageHelper插件。其常常被应用于分页场景中，但是其使用时存在着一些坑，我们下面进行具体分析

​工作原理​：PageHelper是MyBatis的流行分页插件。

• 调用 PageHelper.startPage(pageNum, pageSize)时，其核心作用是创建一个 Page对象并将分页参数存入 ​ThreadLocal​ 中。

  • ThreadLocal保证了Page对象对该线程都可见，但是与此同时也可以反推，如果这个对象没能被正确清理，他会影响到Mybatis中后续该线程的所有查询

  • 尽量不要在一个事务中，多次调用startPage()，会导致意义不明

• PageHelper 实现了一个 MyBatis ​Interceptor​（拦截器），包装Executor对象，进行代理

  • 它会在 MyBatis 执行 Executor#query()方法时进行拦截。

    • Executor: 执行 SQL 的核心对象，负责增删改查、事务管理等。可拦截 update, query, commit, rollback等方法StatementHandler: 处理 JDBC Statement，负责 SQL 语句的预编译、执行等。可拦截 prepare, parameterize等方法。
      ​ParameterHandler: 处理 SQL 参数绑定。可拦截 setParameters等方法。
      ​ResultSetHandler: 处理结果集映射，将 JDBC ResultSet 转换为 Java 对象。可拦截 handleResultSets等方法。

    • 拦截器从 ThreadLocal 中获取到分页参数后，会做两件核心事：

    • 生成并执行 COUNT 查询：​​ 为了得到总记录数，拦截器会自动生成一条基于原始 SQL 的 COUNT(*)查询语句并执行，将结果存入 Page对象。

    • 改写原始 SQL：​​ 根据配置的数据库方言（如 MySQL, Oracle），将原始 SQL 改写为对应的分页语句。

      ​MySQL:​​ SELECT * FROM table_name→ SELECT * FROM table_name LIMIT offset, pageSize

      ​Oracle:​​ 会改写为更复杂的三层子查询，使用 ROWNUM。

• 执行改写后的 SQL，得到当前页的数据列表。

  • 拦截器会将数据列表和总记录数一同封装回最初的 Page对象。

​优势​：

• ​使用极其简便​：通过简单API调用即可实现分页，​自动化程度高。

• ​支持随机跳页​：可直接指定页码，业务应用灵活。

​劣势​：

• ​深度分页性能极差​：其底层基于LIMIT offset, size，因此存在前述的所有深度分页性能问题。页码越深，性能越差。

• ​潜在线程安全问题​：PageHelper使用ThreadLocal传递分页参数。若代码在PageHelper.startPage()后、实际执行查询前发生异常，可能导致分页参数未被及时清理，进而被后续同一线程的其他查询误用。

4.总结

• 使用limit时一定要仔细评估性能，避免深度分页拖垮数据库。可以考虑引入关联查询（子查询）减少回表数据量优化分页性能。对于必须使用limit的场景，考虑redis缓存id，避免引擎层面的大规模丢弃数据

• 游标分页的本质是基于上一次查询数据的翻页，所以其本质上是基于上一个“游标”开始的条件，底层对于旧数据的变更不会影响到新的页

• 深度分页场景下，如果不需要随机翻页的场景，建议直接议使用游标分页。需要随机翻页的场景，建议引入redis动态缓存页码与游标的对应关系，然后使用游标分页

• 使用PageHelper时牢记其底层依然依赖于limit，在深度分页时仍需要考虑性能问题