MySQL 5.7高效分页查询实现方法与优化技巧

MySQL 5.7 在深度分页场景中之所以性能明显下降，本质原因就在于：执行 LIMIT 时，前面 offset+size 这部分记录通常仍然需要先被扫描一遍，再把不需要的数据丢弃，查询成本并不会自动减少。真正实用且可落地的 MySQL 分页优化方案，其实主要有两类：一类是使用主键或有序字段实现游标分页，例如 WHERE id > 上一页末id ORDER BY id LIMIT 20，这种写法更适合滚动加载和无限下拉；另一类是延迟关联，也就是先在子查询中拿到 id，再通过 JOIN 回表查询完整数据，优点是支持跳页，但前提是必须具备可用的主键索引。

在 MySQL 5.7 中，分页查询一旦把 offset 提升到几万甚至更高，响应速度通常会明显变慢，严重时还可能直接触发 max_statement_time 超时。问题根源并不在数据库参数配置，而在 LIMIT 的执行机制：前面的 offset + size 行数据，数据库依旧需要先扫描一遍，再逐条丢弃。真正有效的优化路径通常只有两种——要么使用游标分页，尽量避免大 offset；要么采用延迟关联，绕开全字段扫描带来的额外开销。

用主键/有序字段做游标分页（适合滚动加载）

这是 MySQL 5.7 环境下最稳定、最容易见效的高效分页方式，前提是排序字段具备索引并且值尽量唯一，例如自增 id 或建立了索引的 created_at。

• 第一页查询结束后，记录返回结果中最后一条数据的 id 值（例如 1056）
• 下一页直接使用 WHERE id > 1056 ORDER BY id ASC LIMIT 20，不再使用 OFFSET
• 必须保证 ORDER BY 对应字段已经建立索引；如果字段可能出现 NULL，需要在 WHERE 条件中显式过滤（MySQL 5.7 不支持 NULLS FIRST）
• 这种方式无法随意跳页（例如从第 1 页直接跳到第 100 页），但对于无限滚动、下拉加载、列表追加等业务场景完全够用

延迟关联优化大 offset 场景（适合后台管理）

当业务必须支持任意页码跳转（例如“跳转到第 892 页”），又无法改成游标分页时，延迟关联通常是 MySQL 5.7 深分页优化中最实用的兜底方案。

• 原始低效 SQL：SELECT * FROM users ORDER BY id LIMIT 100000, 20，会先扫描 100020 行，再丢弃前 10 万行
• 优化后的写法：SELECT u.* FROM users u INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 100000, 20) t ON u.id = t.id
• 核心原理：子查询只扫描主键索引，代价更轻；外层再通过主键精准回表，从而减少大量无效字段读取
• 注意事项：子查询中的 ORDER BY 必须与外层保持一致；如果数据表没有主键，这种优化方式基本无法发挥作用

别踩这些坑（5.7 特有雷区）

MySQL 5.7 对索引使用非常敏感，下面这些常见错误会让分页优化直接失效：

• ORDER BY 字段没有索引 → 会触发 Using filesort，查询性能会大幅下降
• 复合排序（例如 ORDER BY status, created_at）却只建立了单列索引 → 无法有效利用索引，分页依然会很慢
• 使用 COUNT(*) 统计总页数 → 对百万级数据表往往意味着一次全表扫描，开销甚至比查询当前页数据更大；更建议缓存总数，或直接改成“加载更多”模式
• 在 WHERE 条件中使用函数（如 WHERE DATE(created_at) = '2025-01-01'）→ 会导致索引失效，无论是游标分页还是延迟关联都很难补救

没有自增主键怎么办（fs_id 无序时）

如果主键类似 fs_id 这种 UUID 或天然无序值，游标分页可能出现顺序不稳定的问题，但依然有可行方案：

• 先通过 ORDER BY fs_id 强制排序（即使 fs_id 本身无序，只要排序后也能形成稳定的结果序列）
• 首次查询可使用 WHERE fs_id > '' ORDER BY fs_id LIMIT 20，拿到第一页最后一条记录的 fs_id
• 后续页继续使用 WHERE fs_id > '上一页最后值' ORDER BY fs_id LIMIT 20
• 缺点是排序本身仍然存在额外开销，但相比 LIMIT 100000, 20 这种深分页写法，整体会稳定得多；实测 30 万条数据下每页耗时波动可控制在 ±0.2 秒以内

游标分页依赖数据顺序稳定，延迟关联依赖主键索引足够高效；两者都无法彻底解决“跳页 + 无序主键 + 高频 COUNT”这类复杂组合问题。真正遇到这种场景，还是需要回到业务层面处理——要么增加搜索筛选条件来缩小数据集，要么通过缓存机制承接热点页访问压力。