Java大数据量分页查询

大数据量分页查询是性能优化的重点，不同数据库和场景需要不同策略。

分页SQL实现

MySQL分页

-- 基础分页（LIMIT offset, size）
SELECT * FROM users ORDER BY id LIMIT 0, 10;     -- 第1页
SELECT * FROM users ORDER BY id LIMIT 10, 10;    -- 第2页
SELECT * FROM users ORDER BY id LIMIT 100, 10;   -- 第11页

-- 简化写法（LIMIT size OFFSET offset）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100;

-- 计算公式
offset = (page - 1) * pageSize

Oracle分页

-- Oracle 12c+
SELECT * FROM users ORDER BY id OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

-- 传统方式（ROWNUM）
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM users ORDER BY id
    ) a WHERE ROWNUM <= 110
) WHERE rn > 100;

PostgreSQL分页

-- 与MySQL类似
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100;

SQL Server分页

-- SQL Server 2012+
SELECT * FROM users ORDER BY id OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

-- 传统方式
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY id) as rn FROM users
) t WHERE rn BETWEEN 101 AND 110;

分页性能问题

LIMIT深分页问题

-- 第1页：很快
SELECT * FROM users ORDER BY id LIMIT 0, 10;

-- 第100000页：很慢！
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;

-- 原因：
-- MySQL先扫描1000010条记录
-- 然后丢弃前1000000条
-- 只返回最后10条
-- 扫描量与offset成正比

性能对比

分页优化方案

方案1：子查询优化

-- 原SQL（慢）
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;

-- 优化：子查询先获取ID
SELECT * FROM users WHERE id >= (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 1
) ORDER BY id LIMIT 10;

-- 原理：
-- 子查询只扫描ID列（覆盖索引）
-- 主查询用ID定位，直接扫描10条

方案2：JOIN优化

-- JOIN方式
SELECT u.* FROM users u
INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 10
) t ON u.id = t.id;

-- 原理：同上，子查询只扫描索引

方案3：游标分页（上次位置）

-- 传统分页
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;

-- 游标分页（记录上次最大ID）
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 优势：
-- 直接用索引定位
-- 不需要扫描offset条记录
-- 翻页性能稳定

-- 缺点：
-- 只能下一页，不能跳页
-- 需要记录上次位置

方案4：索引覆盖

-- 无索引：全表扫描
SELECT * FROM users ORDER BY create_time LIMIT 100000, 10;

-- 有索引：索引扫描
CREATE INDEX idx_create_time ON users(create_time);
SELECT * FROM users ORDER BY create_time LIMIT 100000, 10;

-- 覆盖索引：更优
CREATE INDEX idx_cover ON users(create_time, id, name);
SELECT id, name FROM users ORDER BY create_time LIMIT 100000, 10;
-- 不需要回表查询

Java分页实现

基础分页

public PageResult<User> findPage(int page, int pageSize) {
    int offset = (page - 1) * pageSize;

    String sql = "SELECT * FROM users ORDER BY id LIMIT ?, ?";
    List<User> users = jdbcTemplate.query(sql,
        new BeanPropertyRowMapper<>(User.class), offset, pageSize);

    String countSql = "SELECT COUNT(*) FROM users";
    int total = jdbcTemplate.queryForObject(countSql, Integer.class);

    return new PageResult<>(users, total, page, pageSize);
}

子查询优化分页

public PageResult<User> findPageOptimized(int page, int pageSize) {
    int offset = (page - 1) * pageSize;

    // 子查询先获取ID
    String sql = "SELECT * FROM users WHERE id >= (" +
                 "SELECT id FROM users ORDER BY id LIMIT ?, 1" +
                 ") ORDER BY id LIMIT ?";

    List<User> users = jdbcTemplate.query(sql,
        new BeanPropertyRowMapper<>(User.class), offset, pageSize);

    String countSql = "SELECT COUNT(*) FROM users";
    int total = jdbcTemplate.queryForObject(countSql, Integer.class);

    return new PageResult<>(users, total, page, pageSize);
}

游标分页

public List<User> findNextPage(Long lastId, int pageSize) {
    String sql = "SELECT * FROM users WHERE id > ? ORDER BY id LIMIT ?";
    return jdbcTemplate.query(sql,
        new BeanPropertyRowMapper<>(User.class), lastId, pageSize);
}

// 使用
List<User> page1 = findNextPage(0L, 10);      // 首页
Long lastId = page1.get(page1.size() - 1).getId();
List<User> page2 = findNextPage(lastId, 10);  // 下一页

MyBatis分页

<!-- 基础分页 -->
<select id="findPage" resultType="User">
    SELECT * FROM users ORDER BY id LIMIT #{offset}, #{pageSize}
</select>

<!-- 子查询优化 -->
<select id="findPageOptimized" resultType="User">
    SELECT * FROM users WHERE id >= (
        SELECT id FROM users ORDER BY id LIMIT #{offset}, 1
    ) ORDER BY id LIMIT #{pageSize}
</select>

<!-- 游标分页 -->
<select id="findNextPage" resultType="User">
    SELECT * FROM users WHERE id > #{lastId} ORDER BY id LIMIT #{pageSize}
</select>

PageHelper插件

// 使用PageHelper（MyBatis插件）
PageHelper.startPage(page, pageSize);
List<User> users = userMapper.selectAll();

PageInfo<User> pageInfo = new PageInfo<>(users);
// pageInfo.getList()    数据列表
// pageInfo.getTotal()   总记录数
// pageInfo.getPages()   总页数

分页策略选择

总数查询优化

避免每次COUNT

// 不推荐：每次查询COUNT
public PageResult<User> findPage(int page) {
    List<User> users = ...;
    int total = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", Integer.class);
    return new PageResult<>(users, total);
}

// 推荐：总数缓存或估算
// 1. 缓存总数（数据变化时更新）
// 2. 估算总数（适用于大表）
// 3. 不显示总数（只显示上一页/下一页）

总数估算

-- MySQL估算
SHOW TABLE STATUS LIKE 'users';
-- Rows列是估算值

-- 或使用EXPLAIN
EXPLAIN SELECT * FROM users;
-- rows列是估算扫描行数

注意事项

深分页(offset大)性能差，需优化

游标分页不支持跳页，适合无限滚动

总数COUNT在大表上很慢，可缓存或估算

分页必须有ORDER BY，否则结果不稳定

ORDER BY字段应有索引，否则全表扫描

覆盖索引避免回表，性能更优

要点总结

1. MySQL LIMIT offset在大offset时性能差
2. 子查询优化先获取ID，减少扫描量
3. 游标分页记录上次位置，性能最优
4. 索引覆盖避免回表，提升性能
5. 大表总数COUNT慢，可缓存或估算