对千万级单表进行分页查询
对千万级单表进行分页查询
面试官视角：这道题在考察什么？
- 你是否了解 MySQL 底层索引结构和查询原理
- 你是否遇到过真实的大数据量性能问题
- 你是否有系统的性能优化思维
- 你是否能根据业务场景选择合适的方案
传统分页的致命问题 💥
SELECT * FROM user ORDER BY id LIMIT 1000000, 10;
为什么慢？ MySQL 需要先扫描 1000010 条记录，然后丢弃前 1000000 条，只返回最后 10 条。
性能衰减曲线 📉
核心优化方案大全 🛠️
方案 1：主键游标分页（推荐指数：⭐⭐⭐⭐⭐）
原理：利用主键索引的有序性，记录上一页最后一条的 ID，直接定位下一页
-- 第一页
SELECT * FROM user ORDER BY id LIMIT 10;
-- 第二页（假设第一页最后一条ID是10）
SELECT * FROM user WHERE id > 10 ORDER BY id LIMIT 10;
优点：
- 无论偏移量多大，性能都稳定在毫秒级
- 实现简单，无额外依赖
- 天然支持数据一致性
缺点：
- 不支持跳页（只能上一页 / 下一页）
- 不支持复杂排序（只能按主键或唯一索引排序）
方案 2：子查询优化（推荐指数：⭐⭐⭐⭐）
原理：先在索引中定位偏移量，再回表查询数据
-- 优化前
SELECT * FROM user ORDER BY id LIMIT 1000000, 10;
-- 优化后
SELECT * FROM user WHERE id >= (
SELECT id FROM user ORDER BY id LIMIT 1000000, 1
) ORDER BY id LIMIT 10;
性能对比表 📊
| 偏移量 | 传统 LIMIT | 子查询优化 | 性能提升 |
|---|---|---|---|
| 1000 | 1ms | 1ms | 0% |
| 100000 | 100ms | 10ms | 90% |
| 1000000 | 10s | 20ms | 99.8% |
方案 3：覆盖索引 + 回表（推荐指数：⭐⭐⭐⭐）
原理：先通过覆盖索引获取需要的 ID 列表，再通过 ID 关联查询完整数据
SELECT u.* FROM user u
INNER JOIN (
SELECT id FROM user ORDER BY create_time LIMIT 1000000, 10
) t ON u.id = t.id
ORDER BY u.create_time;
适用场景：需要按非主键字段排序的场景
方案 4：业务层降级方案（推荐指数：⭐⭐⭐）
- 限制最大翻页数：只允许用户翻到第 100 页
- 搜索引擎同步：将数据同步到 Elasticsearch，由 ES 负责分页查询
- 冷热数据分离：将历史数据归档到单独的表或存储引擎
不同业务场景的最佳实践 ✅
| 业务场景 | 推荐方案 | 注意事项 |
|---|---|---|
| 后台管理系统 | 子查询优化 | 限制最大翻页数 |
| APP 列表页 | 主键游标分页 | 不支持跳页 |
| 电商商品搜索 | Elasticsearch | 注意数据同步延迟 |
| 日志查询系统 | ClickHouse | 列式存储天生适合分页 |
面试官常追问的坑点 ⚠️
Q：主键游标分页如果有数据删除怎么办？
A：不会漏数据，因为是基于 ID 的范围查询；但会导致每页数量不一致，可通过业务层补偿。
Q：如果需要按多个字段排序怎么办？
A：建立联合索引，游标条件包含所有排序字段；或使用覆盖索引 + 回表方案。
Q：千万级单表真的需要优化吗？
A：当偏移量超过 10 万时，传统分页就会出现明显性能问题；千万级表偏移量到 100 万时，基本会超时。
加分项回答 🌟
- 我会先评估业务是否真的需要支持跳页，大部分 APP 场景都不需要
- 我会在代码中做双重保护：既限制最大偏移量，又设置 SQL 超时时间
- 对于超大数据量，我会提前做分库分表，从根本上解决单表过大问题
- 我会监控慢查询日志，及时发现和优化分页性能问题
核心代码实现（MyBatis-Plus 生产级）💻
1 通用分页请求 DTO（技术亮点：入口层参数校验）
import jakarta.validation.constraints.Max;
import jakarta.validation.constraints.Min;
import lombok.Data;
@Data
public class PageRequest {
@Min(value = 1, message = "页码不能小于1")
private Integer pageNum = 1;
@Min(value = 1, message = "每页条数不能小于1")
@Max(value = 100, message = "每页条数不能超过100") // 技术亮点：防止超大pageSize攻击
private Integer pageSize = 10;
// 游标分页专用：上一页最后一条记录的排序字段值
private Long lastId;
// 技术亮点：全局最大偏移量限制，双重保护数据库
private static final int MAX_OFFSET = 100000;
public int getOffset() {
int offset = (pageNum - 1) * pageSize;
if (offset > MAX_OFFSET) {
throw new IllegalArgumentException("不支持翻页超过" + MAX_OFFSET / pageSize + "页");
}
return offset;
}
}
2 主键游标分页（推荐首选，通用封装）
技术亮点：泛型通用设计、函数式编程解耦、毫秒级稳定性能
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import java.util.List;
import java.util.function.Function;
public class CursorPageUtils {
/**
* 通用游标分页方法
* @param mapper MyBatis-Plus Mapper
* @param queryWrapper 查询条件
* @param lastId 上一页最后一条记录的ID
* @param pageSize 每页条数
* @param idGetter 获取实体ID的函数
* @return 分页结果
*/
public static List cursorPage(BaseMapper mapper,
LambdaQueryWrapper queryWrapper,
Long lastId,
int pageSize,
Function idGetter) {
// 技术亮点：动态拼接游标条件，第一页无需过滤
if (lastId != null && lastId > 0) {
queryWrapper.gt(T::getId, lastId);
}
queryWrapper.orderByAsc(T::getId).last("LIMIT " + pageSize);
List records = mapper.selectList(queryWrapper);
// 技术亮点：自动计算下一页的lastId，返回给前端
if (!records.isEmpty()) {
Long nextLastId = idGetter.apply(records.get(records.size() - 1));
// 可将nextLastId封装到分页结果中返回
}
return records;
}
}
// 业务层调用示例
public List getUserListByCursor(PageRequest request) {
LambdaQueryWrapper wrapper = Wrappers.lambdaQuery(User.class)
.eq(User::getStatus, 1)
.eq(User::getDeleted, 0);
return CursorPageUtils.cursorPage(
userMapper,
wrapper,
request.getLastId(),
request.getPageSize(),
User::getId
);
}
3 子查询优化分页（支持跳页）
技术亮点：利用索引覆盖定位偏移量，减少 99% 的回表次数
// Mapper层注解SQL（比XML更简洁）
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Options;
import java.util.List;
public interface UserMapper extends BaseMapper {
// 技术亮点：设置SQL超时时间，防止慢查询拖垮数据库
@Options(timeout = 3)
@Select("SELECT * FROM user " +
"WHERE id >= (SELECT id FROM user WHERE status=1 AND deleted=0 ORDER BY id LIMIT #{offset}, 1) " +
"AND status=1 AND deleted=0 " +
"ORDER BY id LIMIT #{pageSize}")
List selectBySubQuery(int offset, int pageSize);
}
// 业务层调用
public List getUserListBySubQuery(PageRequest request) {
return userMapper.selectBySubQuery(request.getOffset(), request.getPageSize());
}
4 覆盖索引 + 回表分页（支持非主键排序）
技术亮点：先通过覆盖索引获取 ID 列表，再小结果集关联查询
@Select("SELECT u.* FROM user u " +
"INNER JOIN (SELECT id FROM user WHERE status=1 AND deleted=0 ORDER BY create_time LIMIT #{offset}, #{pageSize}) t " +
"ON u.id = t.id " +
"ORDER BY u.create_time")
@Options(timeout = 3)
List selectByCoverIndex(int offset, int pageSize);
核心技术难点与完整解决方案 🧩
| 技术难点 | 问题表现 | 解决方案 | 适用场景 |
|---|---|---|---|
| 🔥 大偏移量性能灾难 | 偏移量 > 10 万时查询超时，数据库 CPU 飙升至 100% | 1. 主键游标分页（首选）2. 子查询优化3. 覆盖索引 + 回表 | 所有千万级单表分页场景 |
| 🚫 数据删除导致游标异常 | 游标分页时每页数量不一致，出现 "缺页" 现象 | 1. 使用逻辑删除而非物理删除2. 记录上一页所有排序字段值3. 业务层自动补全数据 | 游标分页 + 数据频繁删除场景 |
| 📊 非主键 / 多字段排序 | 按 create_time、status 等排序时索引失效，性能极差 | 1. 建立联合覆盖索引2. 游标条件包含所有排序字段3. 同步至 Elasticsearch | 复杂排序 + 高并发场景 |
| 🎯 必须支持跳页的需求 | 后台管理、搜索结果页需要跳转到指定页码 | 1. 子查询优化 + 最大翻页限制2. ES 搜索引擎3. 预生成页码缓存 | 低并发 + 需要跳页的后台场景 |
| ⚠️ ES 数据一致性问题 | ES 同步延迟导致查询结果与数据库不一致 | 1. 双写一致性保证2. 延迟双删策略3. 业务层设计容忍度（如 "数据可能有 1 秒延迟"） | ES 分页 + 对一致性要求不高的场景 |
| 🗄️ 分库分表后全局分页 | 分库分表后无法直接进行全局排序分页 | 1. 业务层归并排序（小偏移量）2. 雪花 ID 保证全局有序3. ES 同步全局数据 | 亿级以上数据分库分表场景 |
| 🛡️ 恶意请求攻击 | 攻击者传入超大 offset/pageSize，拖垮整个数据库 | 1. 入口层参数校验2. 全局最大偏移量限制3. SQL 超时设置4. 接口限流熔断 | 所有对外暴露的 API 接口 |
面试加分项补充 ✨
- 性能监控：在代码中埋点统计分页查询耗时，超过 500ms 自动告警
- 降级策略：当数据库压力过大时，自动切换到 ES 分页或返回缓存数据
- 索引优化：定期分析慢查询日志，调整索引结构，避免索引失效
- 压测验证：上线前对分页接口进行 10 万级偏移量压测，确保性能达标
真实面试模拟
真实面试模拟
面试官 👨💼：
“咱们来道场景题。假设有一张千万级别的用户订单表，需要做分页查询，你会怎么设计和优化？先说说普通分页有什么坑？”
我 🙋♂️：
“好的。普通 LIMIT offset, size 在浅分页时没问题，但一旦 offset 达到百万级，就会变成灾难。比如：
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
数据库需要先扫描并丢弃前 100 万行，才能拿到最后的 20 行，这个过程浪费大量 IO 和 CPU，RT 可能飙到秒级。底层逻辑大概是这样：
所以优化的核心就是：尽量减少扫描行数 + 避免无效回表。
面试官 🤔：
“那你优先会怎么解决？”
我 🚀：
“最推荐的方案是游标分页，也就是 Keyset Pagination。思路是：每次记住上一页最后一条记录的主键（或排序字段），下一页直接从这个位置往后取。
-- 假设上一页最后一个 id 是 999980
SELECT * FROM orders WHERE id > 999980 ORDER BY id LIMIT 20;
这样每次只扫描 20 行，性能几乎不受 offset 影响，非常稳。我做过简单压测对比：
| 分页方式 | offset=1万 | offset=100万 | offset=500万 |
|---|---|---|---|
| LIMIT offset | 12ms | 3.2s 😵 | 15s+ 💀 |
| WHERE id > 游标 | 0.8ms ✅ | 0.9ms ✅ | 0.9ms ✅ |
不过它也有明显缺点：不支持任意跳页，只适合「加载更多」或瀑布流场景。
面试官 😏：
“但如果产品要求必须能跳页，比如管理后台要翻到第 200 页，游标分页就不好使了，你怎么做？”
我 🧠：
“这种场景我会用子查询 + 延迟关联。核心思想是：先让子查询只走覆盖索引拿到主键，避免回表，再用主键关联原表取完整字段。
SELECT t1.* FROM orders t1
INNER JOIN (
SELECT id FROM orders
ORDER BY create_time LIMIT 1000000, 20
) t2 ON t1.id = t2.id;
执行过程可以这样理解：
前提是必须建有 (create_time, id) 这类覆盖索引。虽然仍要扫描 100 万行索引，但比起扫描数据行要轻量得多，最后只回表 20 次，性能会有数量级提升。”
面试官 👍：
“除了这两种，还有没有更极致的优化思路？”
我 🏎️：
“如果查询的字段恰好全都在一个索引里，可以直接用覆盖索引分页，连回表都省了。例如只需要 id, create_time, user_name，并且索引覆盖了这些列，那即使是大 offset 也快很多。这需要和业务方一起抠字段，能少查绝不多查。
另外，有些时候技术手段不够，还可以从产品层面软优化：
- 限制最大翻页数，比如只允许看前 200 页（百度、谷歌都这么干）；
- 强制用户选择时间范围或分类，让分页在较小的数据子集内进行；
- 提供搜索和筛选功能，替代盲目翻页。
这些在工程上非常实用。”
面试官 💼：
“嗯，那如果数据量再大，或带复杂筛选，你会考虑其他存储吗？”
我 🌐：
“会。单表千万级 MySQL 还能扛，但如果加上复杂全文检索、多维筛选与排序的分页，我会建议将数据同步到 Elasticsearch，用 search_after 或 scroll 实现深度分页。不过这就属于架构演进，单表分页的核心解法还是上面那几种。总结一下：
这样就能覆盖绝大多数场景了。” 😎
面试官 💡：
“你刚才讲的方案都很到位，那能不能写一下核心代码，让我看看实际落地你怎么写？另外，你觉得这整个深分页场景的技术难点有哪些，你是怎么解决的？”
我 🧑💻：
“没问题，我分别给出游标分页和延迟关联的关键代码，然后拆解难点。”
1. 游标分页核心代码（Spring Boot + MyBatis）
这是一种代码可复用、防SQL注入的写法，技术亮点在于用 WHERE > + 动态排序参数处理。
Controller 层
@GetMapping("/orders/next")
public Result> getNextPage(
@RequestParam(required = false) Long lastId, // 上一页最后一条的 id
@RequestParam(defaultValue = "20") int size) {
List list = orderService.nextPage(lastId, size);
return Result.ok(list);
}
Mapper 接口 + XML
@Mapper
public interface OrderMapper {
List selectNextPage(@Param("lastId") Long lastId,
@Param("size") int size);
}
✨ 技术亮点
- 用
id > #{lastId}让索引直接定位，避免扫描历史数据。 - 首屏请求不传
lastId时， - 排序字段和方向可配置，配合前端瀑布流，性能极稳。
2. 子查询 + 延迟关联核心代码（MyBatis）
Mapper 注解写法（更简洁）
@Select("SELECT t1.* FROM orders t1 " +
"INNER JOIN (" +
" SELECT id FROM orders " +
" ORDER BY create_time DESC " +
" LIMIT #{offset}, #{size}" +
") t2 ON t1.id = t2.id")
List selectPageWithDelayJoin(@Param("offset") long offset,
@Param("size") int size);
必要的覆盖索引
ALTER TABLE orders ADD INDEX idx_create_time_id (create_time, id);
🎯 代码亮点解析
- 子查询仅扫描索引
idx_create_time_id，不回表，速度快。 - 外层 JOIN 回表只捞 20 条完整记录，避免大量随机 I/O。
- 对于跳页需求（如后台管理），这是性价比最高的技术方案。
3. 技术难点 & 解决方案总结 📊
| 技术难点 | 具体表现 | 解决方案 |
|---|---|---|
| 深分页性能断崖 | offset 超过百万后，RT 从毫秒飙到秒级 | 游标分页 / 子查询延迟关联，大幅降低扫描行数 |
| 回表开销巨大 | 二级索引扫描后还要聚簇索引取完整行 | 覆盖索引 + 延迟关联，让扫描只发生在索引树 |
| 无法随意跳页 | 游标分页不支持输入页码跳到第N页 | 产品侧限制最大翻页数，或改用延迟关联分页 |
| 查询字段多变 | 不能为了覆盖索引把所有字段建索引 | 与业务对齐，按需建立薄索引；必要时用 ES 承担复杂查询 |
| 排序字段非唯一 | 用 create_time 排序时，大量相同时间导致分页数据重复/丢失 |
追加主键作为第二排序字段：ORDER BY create_time, id |
| 高并发下的数据变更 | 游标分页过程中新增/删除数据可能引起数据错位 | 采用 immutable cursor（如基于时间戳生成快照流），或者接受弱一致性 |
| SQL 注入风险 | 动态拼接排序字段和方向 | 使用 MyBatis ${} 必须做白名单校验，或者用 #{} + `` 标签限制入参 |
4. 难点解决案例：排序字段非唯一导致的重复数据
很多同学没注意：只按 create_time 排序，若很多行时间相同，MySQL 在两次查询间可能返回重复记录或漏掉数据。解决办法很简单：
-- 有问题的写法
WHERE create_time > #{lastCreateTime}
ORDER BY create_time
-- 正确写法：追加主键保证唯一性
WHERE (create_time, id) > (#{lastCreateTime}, #{lastId})
ORDER BY create_time, id
Java 代码里把上一页最后一个记录的 createTime 和 id 都传过来即可，完美保证游标准确性 🎯。
面试官 😄：
“不错，代码和难点梳理得很扎实，尤其排序唯一性问题能注意到，说明你确实在工程里踩过坑。那我没什么可问的了，咱们HR环节见！”
原文地址: https://www.cveoy.top/t/topic/qHfP 著作权归作者所有。请勿转载和采集!