1. 一个真实的性能噩梦
某电商平台订单表达到5000万数据量时，运营人员反馈翻到100页后加载时间超过8秒。技术团队发现前端使用的经典分页SQL：
SELECT * FROM orders
ORDER BY create_time DESC
LIMIT 1000000, 20;
这种写法在偏移量达到百万级时，查询耗时呈现指数级增长。通过EXPLAIN分析发现，虽然create_time字段有索引，但执行计划显示using filesort，实际扫描行数高达1000020行。
2. 分页性能瓶颈的四大元凶
2.1 全表扫描陷阱
当使用LIMIT offset, size语法时，MySQL需要先读取offset+size条完整记录，再丢弃前offset条。对于百万级偏移量，相当于完整扫描百万条数据。
2.2 排序黑洞
未合理使用索引的ORDER BY会导致filesort操作，在5000万数据量下，临时文件可能超过内存限制，触发磁盘排序。
3.3 索引失效现场
复合索引(a,b,c)遇到WHERE a=1 ORDER BY c的查询时，由于中间跳过了b字段，导致无法利用索引排序。
3.4 数据碎片化
频繁的UPDATE/DELETE操作导致索引页出现空洞，物理存储不连续，增加磁盘随机I/O次数。
3. 六大优化方案实战（MySQL 8.0技术栈）
3.1 延迟关联技巧
将数据定位与字段获取分离，先通过覆盖索引获取主键，再用主键关联获取完整数据：
SELECT * FROM orders
INNER JOIN (
SELECT id FROM orders
ORDER BY create_time DESC
LIMIT 1000000, 20
) AS tmp USING(id);
执行计划对比：
- 优化前：type=ALL，rows=1000020
- 优化后：type=index，rows=20
3.2 游标分页法（适用于顺序访问）
记录上一页最后一条记录的ID，实现无偏移量分页：
-- 第一页
SELECT * FROM orders
WHERE id > 0
ORDER BY id
LIMIT 20;
-- 后续分页（假设最后一条ID=20）
SELECT * FROM orders
WHERE id > 20
ORDER BY id
LIMIT 20;
优势：
- 查询时间恒定在0.01秒级别
- 支持千万级数据快速翻页
3.3 索引覆盖优化
创建包含查询字段和排序字段的复合索引：
ALTER TABLE orders
ADD INDEX idx_cover_search(status, create_time, id);
查询示例：
SELECT id, status, create_time
FROM orders
WHERE status = 1
ORDER BY create_time DESC
LIMIT 1000000, 20;
执行计划显示Using index，无需回表查询。
3.4 预计算分页策略
对于固定条件的分页查询，使用汇总表存储分页元数据：
CREATE TABLE page_metadata (
page_num INT PRIMARY KEY,
first_id INT,
last_id INT,
update_time DATETIME
);
-- 定时任务维护元数据
REPLACE INTO page_metadata
SELECT
FLOOR((row_number - 1)/20) + 1 AS page_num,
MIN(id) AS first_id,
MAX(id) AS last_id,
NOW() AS update_time
FROM (
SELECT id, ROW_NUMBER() OVER(ORDER BY id) AS row_number
FROM orders
) AS tmp
GROUP BY FLOOR((row_number - 1)/20);
分页查询转换为：
SELECT * FROM orders
WHERE id BETWEEN (SELECT first_id FROM page_metadata WHERE page_num=1000)
AND (SELECT last_id FROM page_metadata WHERE page_num=1000);
3.5 分区表分页优化
按时间范围进行分区，缩小查询范围：
CREATE TABLE orders (
id INT PRIMARY KEY,
create_time DATETIME,
...
) PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023)
);
-- 指定分区查询
SELECT * FROM orders PARTITION (p2022)
ORDER BY create_time DESC
LIMIT 1000000, 20;
3.6 异步分页加载
结合应用程序实现分段加载：
// 伪代码示例
public Page<Order> getOrders(int pageSize, String lastSeenId) {
String query = "SELECT * FROM orders WHERE id > ? ORDER BY id LIMIT ?";
return jdbcTemplate.query(query, new Object[]{lastSeenId, pageSize}, rowMapper);
}
4. 应用场景分析
4.1 延迟关联
- 适用场景：需要完整字段的中等数据量分页
- 典型特征：存在覆盖索引可能性
4.2 游标分页
- 适用场景：无限滚动、固定排序的分页
- 典型特征：不需要随机跳页
4.3 预计算分页
- 适用场景：数据变更频率低的静态分页
- 典型特征：分页参数相对固定
5. 技术方案对比
| 方案 | 优点 | 缺点 | 适用数据量 |
|---|---|---|---|
| 延迟关联 | 无需修改业务逻辑 | 需要索引支持 | 百万级 |
| 游标分页 | 性能最优 | 无法随机跳页 | 千万级+ |
| 预计算分页 | 极速响应 | 维护成本高 | 静态数据 |
| 分区表分页 | 减少扫描范围 | 需要合理分区策略 | 时间序列数据 |
| 异步加载 | 用户体验好 | 实现复杂度高 | 实时性要求高 |
6. 关键注意事项
- 索引维护成本：复合索引字段不宜超过5个，避免影响写入性能
- 统计信息准确性：定期执行
ANALYZE TABLE更新索引统计信息 - 冷热数据分离：将历史数据归档到历史表，减少主表体积
- 连接池配置：确保max_prepared_stmt_count参数足够大
- 业务妥协方案：限制最大分页深度（如只允许查看前1000页）
7. 总结与展望
通过本文的六个优化方案，我们构建了分页查询优化的完整解决方案体系。实际应用中需要根据业务特点组合使用多种策略：
- 优先使用游标分页+索引覆盖的基础组合
- 对开放式的分页查询使用延迟关联
- 定期数据维护结合预计算策略
- 分区表与异步加载作为补充方案
随着MySQL 8.0窗口函数的增强，可以探索更多基于ROW_NUMBER()的分析型分页方案。同时，结合Redis等缓存中间件，可以构建多级缓存分页体系，进一步提升用户体验。