一、引言
在 Web 应用开发中，分页功能极为常见。当数据量较小时，简单的分页查询并无问题，但随着数据量增长到数十万甚至数百万条，传统的分页查询会出现性能急剧下降的情况。那么我们该怎么处理这种请况呢？
二、传统分页查询的问题
在 MySQL 中，传统的分页查询通常使用 LIMIT 和 OFFSET 关键字。示例如下：
SELECT * FROM large_table LIMIT 100000, 10;
上述查询表示从 large_table 表中跳过前 100000 条记录，然后取 10 条记录。然而，随着 OFFSET 值的增大，查询性能会显著降低。这是因为 MySQL 会先扫描 OFFSET + LIMIT 条记录，然后丢弃前面 OFFSET 条记录，只返回最后的 LIMIT 条记录。大量的磁盘 I/O 和内存开销使得查询速度变得非常缓慢，影响系统的性能。
三、解决方案
1. 基于游标分页
基于游标分页的核心思想是利用上一页最后一条记录的某个唯一标识（如主键）作为查询下一页的条件，避免使用 OFFSET。
示例代码
假设表 large_table 有自增主键 id，查询第 100001 到 100010 条记录的 SQL 如下：
-- 假设上一页最后一条记录的 id 是 100000
SELECT * FROM large_table WHERE id > 100000 LIMIT 10;
这样避免了直接使用OFFSET
优缺点
- 优点：避免了
OFFSET带来的性能问题，查询速度快，不受分页深度影响。 - 缺点：不支持跳页查询，只能按顺序一页一页访问。
2.基于覆盖索引结合子查询的形式进行优化
在子查询中使用覆盖索引，减少回表操作。首先确保 id 列有索引。
-- 创建 id 列的索引
CREATE INDEX idx_id ON tb_sku(id);
-- 优化后的查询
SELECT *
FROM tb_sku
WHERE id IN (
SELECT id
FROM tb_sku
FORCE INDEX (idx_id)
ORDER BY id
LIMIT 9000000, 10
);
优点：
- 利用覆盖索引减少回表操作，提升查询性能。
- 可以利用索引的有序性加速排序。
缺点：
- 当
OFFSET很大时，性能提升有限。 - 索引会占用额外的存储空间，影响写操作性能
四、总结
在处理 MySQL 超大分页问题时，没有一种通用的解决方案，需要根据具体的业务场景和数据特点选择合适的方法。基于游标分页适用于顺序访问的场景；子查询优化可以在一定程度上提升性能；通过合理选择和运用这些方法，可以有效解决 MySQL 超大分页的性能瓶颈问题。
