一、问题背景：为什么LIMIT分页会成为性能瓶颈？

在日常数据库开发中，分页查询是最常见的操作之一。然而，当数据量达到百万级别时，使用传统的LIMIT offset, size方式进行分页会出现严重的性能问题。本文将深入分析这一问题的根源，并提供多种实战验证的优化方案。

二、LIMIT分页的性能分析原始分页查询的性能问题SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

这种查询方式的性能瓶颈在于：

MySQL需要先扫描前100000条记录然后丢弃这些记录最后返回最后的20条记录

随着offset的增大，需要扫描和丢弃的数据量呈线性增长，导致查询性能急剧下降。

三、核心优化方案方案一：基于游标的分页（推荐方案）

这是最高效的分页优化方案，特别适合顺序翻页场景。

实现原理：记录上一页最后一条记录的ID，查询下一页时直接基于该ID进行范围查询。

-- 第一页查询SELECT * FROM orders ORDER BY id DESC LIMIT 20;-- 第二页查询（假设上一页最后一条记录的ID为120）SELECT * FROM orders WHERE id > 120 ORDER BY id DESC LIMIT 20;

优点：

查询性能稳定，不受数据总量影响充分利用索引，扫描数据量最小适合移动端和Web端的无限滚动场景

缺点：

不支持直接跳转到指定页码需要客户端维护最后一条记录的状态方案二：覆盖索引优化

当必须使用传统分页时，通过覆盖索引减少IO开销。

-- 先通过覆盖索引获取目标行IDSELECT id FROM orders ORDER BY id LIMIT 100000, 20;-- 再通过ID回表查询完整数据SELECT * FROM orders WHERE id IN (/* 上一步查询得到的ID列表 */);方案三：延迟关联优化

这是覆盖索引方案的进阶版本，通过子查询优化性能。

SELECT * FROM orders INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20) AS tmp USING (id);四、特殊场景优化非主键排序场景

当需要按非主键字段（如create_time）排序时，优化方案需要调整：

SELECT * FROM orders WHERE (create_time, id) < ('2023-06-01 12:00:00', 1000)ORDER BY create_time DESC, id DESC LIMIT 20;复合排序场景

对于多字段排序需求，需要创建合适的复合索引：

-- 创建复合索引ALTER TABLE orders ADD INDEX idx_time_status (create_time, status);-- 分页查询SELECT * FROM orders WHERE create_time < '2023-06-01 12:00:00'ORDER BY create_time DESC, status ASC LIMIT 20;五、架构级优化方案1. 读写分离

将分页查询路由到只读副本，减轻主库压力。

2. 数据分区

按时间或业务维度进行数据分区，减少单次查询的数据量。

-- 按月份分区CREATE TABLE orders ( id BIGINT PRIMARY KEY, ... created_at DATETIME) PARTITION BY RANGE (MONTH(created_at)) ( PARTITION p1 VALUES LESS THAN (2), PARTITION p2 VALUES LESS THAN (3), ...);3. 缓存策略对热门页码的查询结果进行缓存使用Redis缓存分页元数据实施查询结果预加载策略六、实战案例分析案例背景

电商订单系统，订单表有500万条记录，需要支持分页查询。

原始方案性能：

SELECT * FROM orders ORDER BY id LIMIT 4000000, 20;-- 执行时间：约3.5秒

优化后方案：

SELECT * FROM orders WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 4000000, 1)ORDER BY id LIMIT 20;-- 执行时间：约0.05秒

性能提升：70倍

七、性能对比数据

方案

数据量

查询时间

适用场景

原始LIMIT

100万条

约1.2秒

小数据量

游标分页

100万条

约0.02秒

顺序翻页

覆盖索引

100万条

约0.3秒

跳页查询

延迟关联

100万条

约0.2秒

复杂查询

八、总结与最佳实践优先使用游标分页：对于顺序浏览场景，基于游标的分页是最佳选择合理使用索引：确保排序字段和过滤字段都有合适的索引避免大偏移量：通过业务设计避免用户访问过于靠后的页码监控慢查询：定期分析慢查询日志，及时发现分页性能问题综合优化：结合缓存、分区、读写分离等架构手段提升整体性能

通过合理的分页方案选择和系统优化，即使是在亿级数据量的情况下，也能保证分页查询的毫秒级响应。