分库分表后的分页与排序问题
问题的本质
分库分表后，数据被分散到多个分片中。当我们需要全局排序和分页时——比如”查询第 10 页的订单，按时间降序排列”——不再能简单地 LIMIT OFFSET。
-- 单库中的简单分页
SELECT * FROM order ORDER BY create_time DESC LIMIT 10 OFFSET 90;
-- 只需扫描 100 行
-- 分库后（4 个分片）
-- 每个分片各自排序取前 100 行 → 归并排序 → 取第 90-100 行
-- 需要扫描 4 × 100 = 400 行，翻到后面几页时剧增
浅分页：LIMIT offset + Limit 的归并
MySQL 层的处理
当使用中间件（如 Sharding-JDBC）时，分页查询的执行流程：
用户请求：SELECT * FROM order ORDER BY gmt_create DESC LIMIT 10 OFFSET 90
第 1 步：中间件把 SQL 发送到所有分片
每个分片执行：LIMIT 10 OFFSET 90 → 返回 10 行
第 2 步：中间件收集各分片的 10 行（共 40 行）
第 3 步：在内存中做归并排序，取最终的 10 行
深分页问题
当翻到后面时，性能急剧下降：
-- 请求第 10000 页
SELECT * FROM order ORDER BY gmt_create DESC LIMIT 10 OFFSET 99990;
-- 每个分片需要取 100000 行
-- 4 个分片总共取 400000 行
-- 中间件内存中归并 400000 行 → 最终取 10 行
-- 几乎做了 99.99% 的无效工作
| 页码 | 每个分片取的行数 | 总行数 | 消耗 |
|---|---|---|---|
| 第 1 页（offset=0） | 10 | 40 | 极小 |
| 第 10 页（offset=90） | 100 | 400 | 较小 |
| 第 1000 页（offset=9990） | 10000 | 40000 | 大 |
| 第 10000 页（offset=99990） | 100000 | 400000 | 极大 |
深分页的优化方案
方案一：游标分页（推荐）
不翻页，而是基于上一页最后一条记录的位置继续查询：
-- 第一页（正常分页）
SELECT * FROM order ORDER BY id DESC LIMIT 20;
-- 返回最后一条记录的 id = 100100
-- 第二页（游标分页）
SELECT * FROM order WHERE id < 100100 ORDER BY id DESC LIMIT 20;
-- 第三页
SELECT * FROM order WHERE id < 99980 ORDER BY id DESC LIMIT 20;
优势：
– 每个分片只需 LIMIT 20 行，无论翻到多深
– 可以通过 ID 索引直接定位，性能稳定
– 总取行数 = 分片数 × 每页行数，不随页数增加
局限：
– 只能做条件分页（需要依赖排序字段的连续性）
– 不支持指定页码跳转（只能”上一页/下一页”）
方案二：禁止深分页
如果业务必须支持指定页码，在前端限制最大可查看页数：
if (pageNum > 100) {
throw new BusinessException("超过最大查询页数，请使用搜索条件过滤");
}
方案三：使用 ES 做分页
将分页查询的字段同步到 Elasticsearch，利用 ES 的分布式分页能力。
// ES 的分页性能远好于 MySQL 分片 + 应用层归并
GET /order/_search
{
"from": 99990,
"size": 10,
"sort": [{ "gmt_create": "desc" }]
}
方案四：数据汇总 + 二次查询
对于需要精确分页的场景，可以采用二次查询：
第 1 次查询：到各分片获取排序后的主键和排序值
第 2 次查询：根据归并后的结果按需回表获取完整记录
避免在中间件层传输大量完整行数据。
全局排序的策略
排序字段选择
推荐排序字段：ID（递增）、create_time（近似递增）
不推荐排序字段：随机字符串、非索引列排序
分片内排序优化
确保排序字段在分片键上有索引，避免每个分片内也全表扫描：
-- 需要复合索引 (shard_key, sort_column)
ALTER TABLE order_0 ADD INDEX idx_user_create (user_id, create_time);
ALTER TABLE order_1 ADD INDEX idx_user_create (user_id, create_time);
面试要点
- 深分页问题是分库分表后最容易被忽视的性能陷阱
- 核心矛盾：中间件需要从每个分片取 OFFSET + LIMIT 行，远多于需要真正返回的行
- 推荐方案：游标分页（没有深分页问题）或禁止深分页
- 如果在面试中遇到”我要支持跳转到任意页”的需求，深分页是躲不开的
- 全局排序一定要在分片键+排序字段上建立联合索引
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容