Mysql:分页有什么性能问题？如何去优化呢？

一、MySQL分页为什么会慢常见分页SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;LIMIT offset, size表示跳过前offset条再返回size条。上面的 SQL 不是直接跳到第 1000001 条而是需要沿着结果顺序读取大量记录跳过前 100 万条只返回最后 20 条。页码越深需要检查并丢弃的记录越多。可以简单理解为LIMIT 0, 20 检查约20条 LIMIT 1000, 20 检查约1020条 LIMIT 1000000, 20 检查约1000020条因此传统分页的成本通常随着offset增大而增长。二、分页的主要性能问题1. 深分页扫描大量无用记录SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;真正返回20条但前面100万条都属于无效工作带来更多BTree叶子节点遍历Buffer Pool访问CPU条件判断磁盘I/O可能的回表查询。2. 二级索引排序可能产生大量回表假设有索引CREATE INDEX idx_created_at ON orders(created_at);查询SELECT * FROM orders ORDER BY created_at LIMIT 1000000, 20;二级索引叶子节点主要保存(created_at, 主键id)但SELECT *需要完整记录所以可能通过主键到聚簇索引查询数据。InnoDB二级索引记录包含主键完整行数据则存放在聚簇索引中。深分页情况下可能出现扫描大量二级索引记录 ↓ 通过主键回表 ↓ 丢弃前面的记录 ↓ 只返回20条具体是否以及何时回表由执行计划决定。3. 排序不能使用索引时出现filesortSELECT * FROM orders WHERE status 1 ORDER BY amount LIMIT 100000, 20;如果没有合适索引MySQL可能先找出符合条件的记录再进行filesort。即使最终只返回20条也可能需要读取和处理大量候选记录。可以通过EXPLAIN的Extra是否出现Using filesort判断。4. 分页结果可能不稳定不写ORDER BYSELECT * FROM orders LIMIT 20, 20;数据库不保证每次返回顺序一致。即使写了ORDER BY created_at如果多条记录的created_at相同它们之间的顺序仍不确定应增加唯一字段ORDER BY created_at DESC, id DESCMySQL官方文档也建议增加额外排序列使顺序具有确定性。三、优化一建立匹配条件和排序的联合索引查询SELECT id, user_id, status, created_at FROM orders WHERE user_id 100 AND status 1 ORDER BY created_at DESC, id DESC LIMIT 20;建立索引CREATE INDEX idx_user_status_time_id ON orders(user_id, status, created_at DESC, id DESC);索引顺序可以理解为等值查询列 → 排序列 → 唯一排序列 user_id, status, created_at, id这样MySQL可以先定位用户和状态再按照索引顺序读取减少扫描和额外排序。MySQL能够在索引顺序满足ORDER BY时避免filesort但要注意索引能够减少排序和过滤成本却不能从根本上解决巨大offset带来的跳过成本。四、优化二游标分页最推荐也叫Keyset PaginationSeek Pagination基于最后一条记录分页第一页SELECT * FROM orders WHERE user_id 100 AND status 1 ORDER BY created_at DESC, id DESC LIMIT 20;记录最后一条数据created_at 2026-08-01 10:00:00 id 5000下一页SELECT * FROM orders WHERE user_id 100 AND status 1 AND ( created_at 2026-08-01 10:00:00 OR (created_at 2026-08-01 10:00:00 AND id 5000) ) ORDER BY created_at DESC, id DESC LIMIT 20;配合索引CREATE INDEX idx_user_status_time_id ON orders(user_id, status, created_at DESC, id DESC);执行过程变成从上一页最后位置附近定位 ↓ 继续读取20条优点深度增加时性能比较稳定不需要跳过前面几十万条数据新增时不容易出现重复数据特别适合信息流、订单列表和滚动加载缺点不能方便地直接跳到第10000页前端需要保存上一页最后一条记录的游标排序字段最好稳定且不允许为NULL最好加入唯一字段id避免游标位置不唯一五、优化三延迟关联如果业务必须使用页码和大offset可以先使用覆盖索引找到20个主键再查询完整数据SELECT o.* FROM orders AS o JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY created_at DESC, id DESC LIMIT 1000000, 20 ) AS p ON p.id o.id ORDER BY o.created_at DESC, o.id DESC;配合索引CREATE INDEX idx_status_time_id ON orders(status, created_at DESC, id DESC);内部查询只读取索引中的小字段扫描覆盖索引 → 获得20个id → 只回表20次它减少了大量无意义的回表但仍然需要跳过100万条索引记录所以是缓解方案不是深分页的根本解决方案