MySQL深分页性能优化实战：大数据量情况下如何进行优化

概要

当MySQL查询遇到LIMIT 10 OFFSET 1000000深分页问题时，数据库可能正在执行一个看似简单实则昂贵的操作——扫描并丢弃前100万行记录。本文将深入探讨深分页问题的本质，并提供多种高效解决方案。

问题现象：为什么OFFSET 1000000如此缓慢？

查询示例：

SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 10 OFFSET 1000000; 

性能瓶颈分析：

• 全量扫描：MySQL必须读取前1,000,010行数据
• 排序开销：对所有扫描行进行排序操作
• 内存压力：大偏移量导致临时表可能使用磁盘存储
• 无效丢弃：最终丢弃前1,000,000行数据

优化方案

方案一：延迟关联

SELECT o.* 
FROM orders o
JOIN (SELECT idFROM orders ORDER BY create_time DESC LIMIT 1000000, 10
) AS tmp ON o.id = tmp.id
ORDER BY create_time DESC;

优化原理：

• 子查询先执行：在覆盖索引 (create_time, id) 中直接定位 10 条目标数据的主键
• 减少数据扫描：子查询只需扫描索引（100万+10行），无需访问实际数据行
• 外层精确获取：通过主键快速获取 10 条完整数据

方案二：游标分页

-- 获取第一页
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 10;-- 获取下一页（使用上一页最后一条记录的create_time）
SELECT * FROM orders 
WHERE create_time < '2023-06-01 12:00:00' 
ORDER BY create_time DESC 
LIMIT 10;

优化原理：

• 完全消除OFFSET
• 仅扫描目标数据行

方案三：ID范围限定法（ID有序场景)

SELECT *
FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 1
)
ORDER BY create_time DESC
LIMIT 10;

优化原理：

• 子查询先通过覆盖索引快速定位到起始ID
• 主查询通过主键ID范围过滤