MySQL分页查询性能优化：从 LIMIT Offset 到书签分页的演变
一、分页查询的基本原理
分页查询是几乎所有Web应用都会用到的功能，无论是电商网站的商品列表，还是社交媒体的动态消息，都需要分页展示数据。在MySQL中，最基础的分页查询方式就是使用LIMIT子句。
-- 基础分页查询示例
SELECT * FROM products
ORDER BY id DESC
LIMIT 10 OFFSET 20; -- 获取第3页数据，每页10条
这个查询看起来简单直接，但当数据量变大时，问题就来了。OFFSET实际上会让MySQL先扫描并跳过指定数量的行，然后再返回需要的行。对于大数据表，这会导致性能急剧下降。
二、LIMIT OFFSET的性能瓶颈
让我们做个实验，假设我们有个百万级用户表：
-- 创建测试表
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_created (created_at)
) ENGINE=InnoDB;
-- 插入100万测试数据
-- 这里省略插入语句，实际测试时需要先准备测试数据
现在我们来比较不同分页位置的查询性能：
-- 查询前10条 - 非常快
SELECT * FROM users ORDER BY id LIMIT 10;
-- 查询第10000页(跳过99990条) - 明显变慢
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 99990;
为什么后者这么慢？因为MySQL实际上需要先扫描前99990+10条记录，然后丢弃前99990条。随着OFFSET增大，查询时间几乎线性增长。
三、书签分页的优化方案
聪明的开发者们想出了"书签分页"（也叫"游标分页"）的方法来解决这个问题。核心思想是：记住上一页最后一条记录的位置，下次查询时直接从那个位置开始。
3.1 基于主键的书签分页
-- 第一页查询
SELECT * FROM users ORDER BY id LIMIT 10;
-- 假设最后一行的id是100，第二页查询
SELECT * FROM users
WHERE id > 100 -- 从上次最后一条记录之后开始
ORDER BY id
LIMIT 10;
这种方法完全避免了OFFSET带来的性能问题，查询时间保持恒定，不受页码影响。
3.2 基于非主键字段的书签分页
有时候我们需要按非主键字段排序，比如按创建时间：
-- 第一页
SELECT * FROM users
ORDER BY created_at DESC, id DESC -- 添加id确保排序唯一性
LIMIT 10;
-- 假设最后一条记录的created_at是'2023-01-01 12:00:00'，id是500
-- 第二页
SELECT * FROM users
WHERE (created_at < '2023-01-01 12:00:00')
OR (created_at = '2023-01-01 12:00:00' AND id < 500)
ORDER BY created_at DESC, id DESC
LIMIT 10;
这种复合条件查询确保了分页的准确性，即使有相同created_at的记录也能正确处理。
四、实际应用中的进阶优化
4.1 覆盖索引优化
对于只需要显示部分字段的列表页，可以使用覆盖索引避免回表：
-- 创建覆盖索引
ALTER TABLE users ADD INDEX idx_cover (created_at, id, username);
-- 使用覆盖索引的分页查询
SELECT id, username, created_at FROM users
ORDER BY created_at DESC, id DESC
LIMIT 10 OFFSET 0; -- 即使使用OFFSET，性能也比全表扫描好
4.2 预加载下一页
在移动端应用中，可以预加载下一页数据：
-- 当前页查询
SELECT * FROM users WHERE id > ? ORDER BY id LIMIT 10;
-- 同时后台预加载
SELECT * FROM users WHERE id > ? ORDER BY id LIMIT 20;
这样当用户滑动到下一页时，数据已经准备好了。
4.3 分布式环境下的分页挑战
在分库分表环境中，传统的LIMIT OFFSET几乎不可用。书签分页成为必选项：
-- 假设数据按id范围分片
-- 第一页需要从所有分片各取10条，然后在内存中排序
SELECT * FROM users_shard1 ORDER BY id LIMIT 10;
SELECT * FROM users_shard2 ORDER BY id LIMIT 10;
-- ...合并结果后取前10条
-- 下一页基于最大id
SELECT * FROM users_shard1 WHERE id > ? ORDER BY id LIMIT 10;
SELECT * FROM users_shard2 WHERE id > ? ORDER BY id LIMIT 10;
-- ...合并结果
五、技术选型与注意事项
5.1 适用场景
书签分页特别适合：
- 无限滚动的feed流
- 后台管理系统的大数据列表
- 需要稳定响应时间的API
5.2 优缺点分析
优点：
- 性能稳定，不受页码影响
- 适合大数据量场景
- 在分布式系统中表现良好
缺点：
- 实现比LIMIT OFFSET复杂
- 不支持直接跳转到任意页码
- 需要客户端配合维护游标状态
5.3 实现注意事项
- 确保排序字段有索引
- 复合排序时要包含唯一字段（如主键）
- 前端需要保存"上一页最后一条记录"的游标
- 处理新增数据对分页的影响
六、完整示例：电商商品分页
让我们看一个电商平台的商品分页完整实现：
-- 商品表结构
CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
sales INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_sort (sales DESC, id DESC)
);
-- 第一页查询
SELECT id, name, price, sales FROM products
ORDER BY sales DESC, id DESC
LIMIT 20;
-- 假设最后一条记录sales=1000, id=50
-- 第二页查询
SELECT id, name, price, sales FROM products
WHERE (sales < 1000) OR (sales = 1000 AND id < 50)
ORDER BY sales DESC, id DESC
LIMIT 20;
对应的API设计：
GET /api/products?limit=20
响应包含：
{
"data": [...],
"next_cursor": "sales_1000_id_50" // 用于获取下一页
}
GET /api/products?cursor=sales_1000_id_50&limit=20
七、总结
从简单的LIMIT OFFSET到高效的书签分页，MySQL分页查询的优化之路反映了数据处理规模增长带来的技术演进。虽然书签分页实现起来稍复杂，但在大数据量场景下带来的性能提升是显著的。作为开发者，我们应该根据实际业务需求选择合适的分页策略，在便捷性和性能之间找到平衡点。
评论