MySQL 大数据量分页查询优化指南

问题分析

当对包含50万条记录的edu_test表进行分页查询时，发现随着分页越深入，查询时间越长：

• limit 0,10：0.05秒
• limit 200000,10：0.14秒
• limit 499000,10：0.21秒

通过EXPLAIN分析发现，limit offset, size会进行全表扫描，扫描后从offset位置开始取size条记录返回。

优化方案

方案1：通过有序唯一索引缩小扫描范围

-- 使用主键范围查询
SELECT * FROM edu_test WHERE id > 499000 ORDER BY id ASC LIMIT 10;
-- 执行时间：0.14秒-- 进一步缩小扫描范围
SELECT * FROM edu_test WHERE id BETWEEN 499000 AND 499020 ORDER BY id ASC LIMIT 10;
-- 执行时间：0.09秒

优点：利用索引快速定位，减少扫描数据量
前提：必须使用有序且唯一的字段（如自增主键）

方案2：子查询优化

SELECT * FROM edu_test 
WHERE id >= (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 1) 
LIMIT 10;
-- 执行时间：0.16秒

方案3：JOIN查询优化

SELECT s.* FROM edu_test s
JOIN (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 10) t
ON s.id = t.id;
-- 执行时间：0.16秒

方案2和3的共同优点：

• 先通过索引快速定位到起始ID
• 再通过主键精确查询所需记录
• 避免了全表扫描

实际业务应用建议

1. 主键设计原则：

   • 保持主键唯一且有序（推荐自增ID）
   • 避免使用业务逻辑复杂的字符串作为主键
   • 考虑热点业务场景，减少回表操作

2. 复杂场景处理：

   • 对于分布式ID或字符串主键，可使用WHERE id LIKE '10289%'先缩小范围
   • 考虑添加辅助索引优化特定查询场景

3. 性能权衡：

   • 简单分页（前几页）：直接使用LIMIT
   • 深度分页：采用优化方案
   • 考虑使用"上一页/下一页"替代具体页码跳转

通过合理的主键设计和查询优化，可显著提升大数据量下的分页查询性能。