数据库分页与深分页优化

分页基础

LIMIT 语法

-- MySQL 分页语法
SELECT * FROM user LIMIT offset, size;
SELECT * FROM user LIMIT size OFFSET offset;
 
-- 示例：每页 10 条
-- 第 1 页
SELECT * FROM user LIMIT 0, 10;
 
-- 第 2 页
SELECT * FROM user LIMIT 10, 10;
 
-- 第 N 页
SELECT * FROM user LIMIT (N-1) * 10, 10;

分页公式

offset = (pageNo - 1) * pageSize
size = pageSize

深分页问题

问题现象

-- 前 10 条，很快
SELECT * FROM user LIMIT 0, 10;         -- ~1ms
 
-- 中间 10 条，还行
SELECT * FROM user LIMIT 10000, 10;     -- ~10ms
 
-- 深分页，很慢！
SELECT * FROM user LIMIT 1000000, 10;   -- ~500ms
SELECT * FROM user LIMIT 5000000, 10;   -- ~2s

问题原因

MySQL LIMIT 执行过程：

读取 offset + size 条记录
抛弃前 offset 条记录
返回剩余 size 条记录

LIMIT 1000000, 10 执行过程：
读取 1000010 条 → 抛弃前 1000000 条 → 返回 10 条
                    ↑
                大量无效读取

EXPLAIN 分析

EXPLAIN SELECT * FROM user LIMIT 1000000, 10;
 
-- 结果：
-- type: ALL（全表扫描）
-- rows: 1000010（扫描行数）
-- Extra: NULL

深分页优化方案

方案一：覆盖索引延迟关联

-- 优化前：全表扫描
SELECT * FROM user LIMIT 1000000, 10;
 
-- 优化后：先查 ID，再关联
SELECT a.* FROM user a
INNER JOIN (
    SELECT id FROM user LIMIT 1000000, 10
) b ON a.id = b.id;

原理：

子查询只走索引（覆盖索引）
避免读取大量无用数据
通过主键关联获取完整数据

-- EXPLAIN 分析
EXPLAIN SELECT id FROM user LIMIT 1000000, 10;
-- type: index（索引扫描）
-- Extra: Using index（覆盖索引）
 
-- 优化后性能
-- 从 ~500ms 降到 ~100ms

方案二：游标分页（推荐）

-- 记住上一页最后一条的 ID
-- 假设上一页最后一条 id = 1000000
SELECT * FROM user WHERE id > 1000000 ORDER BY id LIMIT 10;

原理：

直接定位到起始位置
避免扫描 offset 条记录
利用索引快速定位

-- EXPLAIN 分析
EXPLAIN SELECT * FROM user WHERE id > 1000000 ORDER BY id LIMIT 10;
-- type: range（范围扫描）
-- rows: 10（只扫描需要的行）

优点：

性能稳定，不受 offset 影响
支持大数据量分页

缺点：

无法跳页（不能直接跳到第 N 页）
需要有序字段

方案三：ID 范围分页

-- 假设 ID 连续或近似连续
-- 第 100000 页（每页 10 条）
SELECT * FROM user WHERE id BETWEEN 1000001 AND 1000010;

适用场景：

ID 连续自增
没有删除操作（ID 无断号）

问题：

有删除时 ID 不连续
无法精确定位页码

方案对比

方案	性能	是否支持跳页	适用场景
LIMIT offset	差	✅	数据量小
延迟关联	中	✅	通用优化
游标分页	好	❌	移动端/无限滚动
ID 范围	好	❌	ID 连续场景

业务层优化

限制最大页数

// 避免用户请求过深的页码
public Page<User> listUsers(int pageNo, int pageSize) {
    int maxPage = 1000; // 限制最大页数
    if (pageNo > maxPage) {
        throw new BusinessException("页码超出限制");
    }
    // ...
}

引导用户行为

搜索引擎做法：
- 显示前 100 页（或更少）
- 隐藏总条数显示
- 引导用户缩小搜索范围

// 百度/Google 的做法
public Page<User> search(String keyword, int pageNo) {
    // 只查询前 N 条匹配记录
    // 不计算总数，不显示总页数
}

分页缓存

// 热门页面缓存
@Cacheable(value = "userPage", key = "#pageNo + '_' + #pageSize")
public Page<User> listUsers(int pageNo, int pageSize) {
    // ...
}

COUNT 优化

分页中的总数问题

-- COUNT(*) 在大表上很慢
SELECT COUNT(*) FROM user;  -- 全表扫描

优化方案

1. 缓存总数

// Redis 缓存总数，定期更新
String countKey = "user:count";
Long count = redisTemplate.opsForValue().get(countKey);
if (count == null) {
    count = userMapper.count();
    redisTemplate.opsForValue().set(countKey, count, 1, TimeUnit.HOURS);
}

2. 估算总数

-- 使用 EXPLAIN 估算
EXPLAIN SELECT * FROM user;
-- rows 字段是估算值
 
-- 或使用信息_schema
SELECT TABLE_ROWS FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'user';

3. 不计算总数

移动端/无限滚动场景：
- 不显示总条数和总页数
- 只返回是否有下一页
- 减少一次 COUNT 查询

public PageResult<User> listUsers(int pageNo, int pageSize) {
    List<User> list = userMapper.list((pageNo - 1) * pageSize, pageSize + 1);
    
    boolean hasNext = list.size() > pageSize;
    if (hasNext) {
        list.remove(list.size() - 1); // 移除多查的一条
    }
    
    return new PageResult<>(list, hasNext); // 不返回总数
}

各数据库分页语法

数据库	分页语法
MySQL	`LIMIT offset, size`
PostgreSQL	`LIMIT size OFFSET offset`
Oracle	`ROWNUM` 或 `OFFSET ... FETCH`
SQL Server	`OFFSET ... FETCH NEXT`
SQLite	`LIMIT size OFFSET offset`

-- Oracle 12c+
SELECT * FROM user OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
 
-- SQL Server 2012+
SELECT * FROM user ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

MyBatis 分页插件

PageHelper 使用

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.3.2</version>
</dependency>

// 使用方式
PageHelper.startPage(pageNo, pageSize);
List<User> list = userMapper.selectAll();
PageInfo<User> pageInfo = new PageInfo<>(list);
 
// pageInfo 包含：list, total, pages, pageNum, pageSize 等

PageHelper 原理

1. 拦截查询 SQL
2. 自动添加 LIMIT 子句
3. 自动执行 COUNT 查询
4. 封装分页结果

注意：PageHelper 没有解决深分页问题，仍需手动优化。

面试高频问题

Q1: 深分页为什么慢？

MySQL 的 LIMIT 会读取 offset + size 条记录，然后抛弃前 offset 条。深分页时大量数据读取后丢弃，造成性能浪费。

Q2: 如何优化深分页？

延迟关联：子查询只查 ID（覆盖索引），再关联查数据
游标分页：记录上一页最后 ID，WHERE id > lastId
业务限制：限制最大页数，引导用户缩小范围

Q3: 游标分页的优缺点？

优点：

性能稳定，O(1) 复杂度
适合大数据量

缺点：

不支持跳页
需要有序字段
无法显示总页数

Q4: 分页时 COUNT 慢怎么办？

缓存总数
使用估算值
不计算总数（只返回是否有下一页）

总结

深分页优化核心：
1. 原因：LIMIT 扫描 offset + size 行，抛弃前 offset 行
2. 优化方案：
   - 延迟关联：子查询用覆盖索引
   - 游标分页：WHERE id > lastId（推荐）
   - ID 范围：适合连续 ID
3. 业务优化：限制页数、缓存、不计算总数