MySQL多查询条件下深度分页性能优化技巧及示例总结

深度分页(Deep Pagination)是MySQL中常见的性能瓶颈问题，特别是在多查询条件下，当offset值很大时，查询性能会急剧下降。本文将总结多种优化技巧，并提供实际示例。

一、深度分页的性能问题分析

当执行类似SELECT * FROM table WHERE condition1 AND condition2 LIMIT 1000000, 10的查询时，MySQL需要：

1. 扫描满足条件的1000010条记录
2. 丢弃前1000000条
3. 返回最后的10条

这种"全表扫描+丢弃"机制导致：

• I/O负载高：需要读取大量数据
• 内存消耗大：需要缓存中间结果
• 响应时间长：随着offset增加，性能线性下降

二、多查询条件下的优化技巧

1. 游标分页(记录最大ID法)

原理：利用有序字段(通常是主键)作为游标，避免使用OFFSET

-- 第一页
SELECT * FROM orders 
WHERE user_id=123 AND status='completed' 
ORDER BY id LIMIT 10;-- 后续页(假设上一页最后一条记录的id是100)
SELECT * FROM orders 
WHERE user_id=123 AND status='completed' AND id > 100
ORDER BY id LIMIT 10;

优点：

• 性能稳定，不受页码影响
• 只需扫描目标数据行

缺点：

• 只支持顺序分页(不能随机跳页)
• 需前端维护最后一条记录的ID

2. 延迟关联(子查询优化)

原理：先通过覆盖索引获取主键，再关联查询完整数据

SELECT o.* FROM orders o
JOIN (SELECT id FROM ordersWHERE user_id