在MySQL中，传统的分页查询使用LIMIT offset, size语句，例如SELECT * FROM users ORDER BY create_time DESC LIMIT 100000, 10。看起来很简单，但随着offset值增大，性能会急剧下降。
这是因为MySQL执行深分页查询时：
- 从索引或全表扫描前
offset + size行数据（比如100010行） - 丢弃前
offset行（100000行） - 返回最后
size行（10行）
整个过程的时间复杂度为O(N)，N就是offset的值。想象一下，翻到第10万页时，数据库要扫描10万多条数据，能不慢吗？
优化方案
1. 覆盖索引 + 子查询优化
原理：通过子查询先获取主键ID，再通过主键ID查询完整数据，减少回表操作。
示例SQL：
-- 传统低效写法
SELECT * FROM users ORDER BY create_time DESC LIMIT 100000, 10;
-- 优化后写法
SELECT u.*
FROM users u
JOIN (
SELECT id FROM users ORDER BY create_time DESC LIMIT 100000, 10
) AS tmp ON u.id = tmp.id; --这时候的tmp临时表其实只有需要的10条id记录，它因为没有回表，所以深分页情况下也没事
--相当于用小表驱动大表
关键点：
- 子查询仅扫描索引树，无需回表
- 主查询通过主键ID快速定位数据行
2. 书签分页（Bookmark Pagination）
原理：记住上一页的最后一条记录（书签），下一页查询从此处开始。
示例SQL：
-- 假设上次查询的最后一条记录ID=100000
SELECT *
FROM users
WHERE id > 100000 -- 注意是大于，ID一般升序
ORDER BY id ASC
LIMIT 10;
-- 按时间排序的场景
SELECT *
FROM users
WHERE create_time > '2023-01-01 10:00:00'
ORDER BY create_time ASC
LIMIT 10;
优点：
- 时间复杂度O(1)，无论翻到第几页性能恒定
- 适合按时间或ID排序的场景
3. 预计算分页数据
原理：对于热点数据（如排行榜），定期预计算并缓存结果。
示例SQL：
-- 创建物化视图（MySQL 5.7+）
CREATE OR REPLACE VIEW hot_users AS
SELECT * FROM users ORDER BY score DESC LIMIT 1000;
-- 查询预计算的结果
SELECT * FROM hot_users LIMIT 0, 10;
4. 覆盖索引原理详解
什么是覆盖索引？ 当索引包含查询所需的所有字段时，数据库可以直接通过索引返回结果，无需回表查询数据行。
示例对比：
-- 普通索引查询（需回表）
SELECT order_id, status, create_time
FROM orders
WHERE status = 'PAID';
-- 覆盖索引查询（直接从索引获取结果），所有字段都在索引中
SELECT status, create_time, amount
FROM orders
WHERE status = 'PAID';
创建原则：
- 确保
SELECT、WHERE、ORDER BY中的字段都在索引中 - 使用复合索引（如
(status, create_time)） - 避免冗余，权衡索引更新成本
优化方案对比与选择
| 方案 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| 覆盖索引+子查询 | 实现简单 | 无法彻底解决深分页问题 | 数据量较小（百万级） |
| 书签分页 | 性能稳定O(1) | 需要有序字段和连续翻页 | 按时间/ID排序的列表 |
| 预计算 | 查询极快 | 数据实时性差 | 排行榜、热门列表 |
| 覆盖索引 | 减少回表操作 | 索引维护成本高 | 频繁查询固定字段的场景 |
总结
解决MySQL深分页问题没有“一招鲜”，需要根据业务场景选择合适的方案：
- 数据量较小：优先使用覆盖索引+子查询
- 按时间/ID排序：强烈推荐书签分页
- 热点数据：预计算分页数据
- 频繁查询：优化索引，使用覆盖索引
通过这些优化技巧，下次再遇到深分页问题，就能轻松应对啦！如果觉得有用，记得点赞收藏哦~