MyBatis 和 MyBatis Plus 分页查询大数据量的性能优化技巧

目录

• 背景介绍
• 性能问题分析
• 优化技巧
• 代码示例
• 最佳实践
• 常见问题

---

背景介绍

在实际开发中，当数据量达到百万或千万级别时，传统的分页查询方式会出现严重的性能问题。本文将详细讲解如何在 MyBatis 和 MyBatis Plus 中优化大数据量的分页查询性能。

常见性能问题

• 深分页性能急剧下降：查询越靠后的页码，响应时间越长
• COUNT 查询耗时：总记录数统计查询慢
• 内存占用高：大量数据加载到内存
• 数据库压力大：频繁的深分页查询拖垮数据库

---

性能问题分析

传统分页的问题

-- 传统 LIMIT OFFSET 方式
SELECT * FROM user ORDER BY id LIMIT 10 OFFSET 100000;

问题分析：

• MySQL 需要扫描前 100010 行记录，然后丢弃前 100000 行
• OFFSET 越大，扫描的无效数据越多
• 随着页码增加，性能呈指数级下降

COUNT 查询的性能损耗

-- 统计总记录数
SELECT COUNT(*) FROM user WHERE ...;

问题分析：

• 在大表上执行 COUNT 操作非常耗时
• 复杂条件的 COUNT 查询更慢
• 每次分页都执行 COUNT，加重数据库负担

---

优化技巧

1. 使用游标分页（推荐）

原理： 记录上次查询的最大/最小 ID，下次查询时基于该 ID 进行过滤

// MyBatis Plus 示例
public List<User> selectPageByCursor(Long lastId, Integer pageSize) {
    return lambdaQuery()
        .gt(User::getId, lastId)
        .orderByAsc(User::getId)
        .last("LIMIT " + pageSize)
        .list();
}

// 传统 MyBatis XML 写法
<select id="selectPageByCursor" resultType="User">
    SELECT * FROM user
    WHERE id > #{lastId}
    ORDER BY id
    LIMIT #{pageSize}
</select>

优点：

• 性能稳定，不受页码影响
• 利用主键索引，查询效率高
• 适合移动端无限加载场景

缺点：

• 不支持跳页查询
• 需要有序的连续字段

---

2. 延迟关联（子查询优化）

原理： 先通过子查询获取 ID，再关联查询详细信息

// MyBatis Plus 示例
public IPage<User> selectPageWithSubQuery(Page<User> page, Long userId) {
    return page.setRecords(
        lambdaQuery()
        .select(User::getId)  // 子查询只查 ID
        .eq(User::getUserId, userId)
        .orderByDesc(User::getId)
        .page(page)
        .getRecords()
    ).then(ids -> {
        // 通过 ID 列表查询完整数据
        return lambdaQuery()
            .in(User::getId, ids)
            .orderByDesc(User::getId)
            .list();
    });
}

// MyBatis XML 写法
<select id="selectPageWithSubQuery" resultType="User">
    SELECT u.* FROM user u
    INNER JOIN (
        SELECT id FROM user
        WHERE user_id = #{userId}
        ORDER BY id DESC
        LIMIT #{offset}, #{pageSize}
    ) AS tmp ON u.id = tmp.id
</select>

优点：

• 子查询走覆盖索引，速度快
• 避免回表查询所有字段
• 适合深分页场景

---

3. COUNT 查询优化

3.1 缓存 COUNT 结果

@Component
public class PageCountCache {

    @Cacheable(value = "pageCount", key = "#methodName + #params")
    public Long getTotalCount(String methodName, Object params) {
        // 执行 COUNT 查询
        return userMapper.selectCount(params);
    }
}

3.2 使用近似 COUNT

// 对于允许误差的场景，使用 EXPLAIN 估算
public Long getApproximateCount() {
    // MySQL 8.0+ 可以使用
    // 或从 information_schema.TABLES 获取估算值
    return tableStatsMapper.estimateRowCount("user");
}

// MyBatis XML
<select id="estimateRowCount" resultType="java.lang.Long">
    SELECT TABLE_ROWS
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = #{tableName}
</select>

3.3 避免 COUNT 的方案

// 方案1：预估总数
public PageResult<User> page(Integer pageNo, Integer pageSize) {
    // 前几页精确 COUNT，后面使用预估值
    Long total = pageNo <= 5 ? userMapper.selectCount(null) : ESTIMATED_COUNT;

    // 方案2：前端不显示总数，改为"加载更多"
    // 方案3：异步查询 COUNT，先返回数据
}

---

4. 索引优化

4.1 创建合适的索引

-- 为常用分页字段创建索引
CREATE INDEX idx_user_create_time ON user(create_time, id);
CREATE INDEX idx_user_status_time ON user(status, create_time, id);

-- 复合索引注意字段顺序
-- 查询条件字段在前，排序字段在后

4.2 覆盖索引优化

-- 查询只使用索引字段，避免回表
-- 示例：只需要 ID 和 status 字段
SELECT id, status FROM user
WHERE status = 1
ORDER BY id
LIMIT 10000, 10;

-- 创建覆盖索引
CREATE INDEX idx_user_status_id ON user(status, id);

---

5. 分页插件优化

MyBatis Plus 分页插件配置

@Configuration
public class MyBatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        // 分页插件
        PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);

        // 设置单页分页条数限制（防止恶意查询）
        paginationInterceptor.setMaxLimit(1000L);

        // 设置查询总记录数的 SQL 解析优化
        paginationInterceptor.setCountSqlParser(
            new JsqlParserCountOptimize(true)
        );

        // 超出最大页数后的处理策略
        paginationInterceptor.setOverflow(false);

        interceptor.addInnerInterceptor(paginationInterceptor);

        return interceptor;
    }
}

自定义 COUNT 查询

// 方式1：禁用 COUNT 查询
Page<User> page = new Page<>(current, size);
page.setSearchCount(false);  // 不查询总数
userMapper.selectPage(page, wrapper);

// 方式2：自定义 COUNT SQL
@Select("SELECT count(1) FROM user WHERE status = #{status}")
Long countByStatus(@Param("status") Integer status);

---

代码示例

完整的分页查询优化方案

@Service
@RequiredArgsConstructor
public class UserOptimizedService {

    private final UserMapper userMapper;
    private final RedisTemplate<String, Object> redisTemplate;

    /**
     * 方案1：游标分页（推荐用于移动端）
     */
    public List<User> pageByCursor(Long lastId, Integer pageSize) {
        return userMapper.selectList(
            new LambdaQueryWrapper<User>()
                .gt(User::getId, lastId)
                .orderByAsc(User::getId)
                .last("LIMIT " + pageSize)
        );
    }

    /**
     * 方案2：深分页优化（子查询方式）
     */
    public IPage<User> pageDeepOptimized(Page<User> page, UserQuery query) {
        // 先查 ID
        Page<Long> idPage = new Page<>(page.getCurrent(), page.getSize());
        IPage<Long> idResult = userMapper.selectIdsPage(idPage, query);

        if (idResult.getRecords().isEmpty()) {
            return new Page<>(page.getCurrent(), page.getSize(), 0);
        }

        // 再查完整数据
        List<User> users = userMapper.selectBatchIds(idResult.getRecords());
        page.setRecords(users);
        page.setTotal(idResult.getTotal());

        return page;
    }

    /**
     * 方案3：缓存优化的分页
     */
    public IPage<User> pageWithCache(Page<User> page, UserQuery query) {
        // 生成缓存 key
        String cacheKey = "page:count:" + query.hashCode();

        // 从缓存获取总数
        Long total = (Long) redisTemplate.opsForValue().get(cacheKey);

        if (total == null) {
            total = userMapper.selectCount(query.toWrapper());
            // 缓存 5 分钟
            redisTemplate.opsForValue().set(cacheKey, total, 5, TimeUnit.MINUTES);
        }

        page.setTotal(total);
        return userMapper.selectPage(page, query.toWrapper());
    }

    /**
     * 方案4：异步 COUNT 查询
     */
    @Async
    public CompletableFuture<Long> countAsync(UserQuery query) {
        return CompletableFuture.completedFuture(
            userMapper.selectCount(query.toWrapper())
        );
    }

    public PageResult<User> pageAsync(Page<User> page, UserQuery query) {
        // 异步查询总数
        CompletableFuture<Long> countFuture = countAsync(query);

        // 同步查询数据
        IPage<User> userPage = userMapper.selectPage(
            page.setSearchCount(false),  // 不查总数
            query.toWrapper()
        );

        // 等待 COUNT 完成
        Long total = countFuture.join();
        userPage.setTotal(total);

        return PageResult.of(userPage);
    }
}

MyBatis XML Mapper 示例

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mapper.UserMapper">

    <!-- 游标分页查询 -->
    <select id="selectPageByCursor" resultType="User">
        SELECT * FROM user
        <where>
            <if test="lastId != null">
                AND id > #{lastId}
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
        </where>
        ORDER BY id
        LIMIT #{pageSize}
    </select>

    <!-- 深分页优化查询（子查询方式） -->
    <select id="selectPageDeepOptimized" resultType="User">
        SELECT u.* FROM user u
        INNER JOIN (
            SELECT id FROM user
            <where>
                <if test="query.status != null">
                    AND status = #{query.status}
                </if>
                <if test="query.keyword != null">
                    AND name LIKE CONCAT('%', #{query.keyword}, '%')
                </if>
            </where>
            ORDER BY id DESC
            LIMIT #{offset}, #{pageSize}
        ) AS tmp ON u.id = tmp.id
        ORDER BY u.id DESC
    </select>

    <!-- 只查询 ID 列表（用于子查询优化） -->
    <select id="selectIdsPage" resultType="java.lang.Long">
        SELECT id FROM user
        <where>
            <if test="query.status != null">
                AND status = #{query.status}
            </if>
        </where>
        ORDER BY id DESC
        LIMIT #{page.offset}, #{page.size}
    </select>

    <!-- 优化的 COUNT 查询 -->
    <select id="selectCountOptimized" resultType="java.lang.Long">
        SELECT COUNT(1) FROM (
            SELECT 1 FROM user
            <where>
                <if test="query.status != null">
                    AND status = #{query.status}
                </if>
            </where>
            LIMIT 10001  -- 只统计前 10001 条，超过则返回 10001+
        ) AS tmp
    </select>

</mapper>

---

最佳实践

1. 选择合适的分页方案

场景	推荐方案	理由
移动端无限加载	游标分页	性能稳定，用户体验好
管理后台（页码小）	传统 LIMIT OFFSET	实现简单，满足需求
深分页查询	子查询优化	避免 OFFSET 性能问题
实时性要求低	缓存 COUNT 结果	减少 COUNT 查询次数

2. 分页参数校验

public Page<User> validateAndPage(Integer current, Integer size) {
    // 限制最大页大小
    int maxSize = 100;
    size = Math.min(size != null ? size : 10, maxSize);

    // 限制最大页码
    int maxPage = 1000;
    current = Math.min(current != null ? current : 1, maxPage);

    return new Page<>(current, size);
}

3. 监控和告警

@Aspect
@Component
public class SlowQueryMonitor {

    @Around("execution(* com.example.mapper.*Mapper.*(..))")
    public Object monitorSlowQuery(ProceedingJoinPoint pjp) throws Throwable {
        long start = System.currentTimeMillis();

        Object result = pjp.proceed();

        long cost = System.currentTimeMillis() - start;

        if (cost > 1000) {
            log.warn("慢查询告警: {} 耗时: {}ms",
                pjp.getSignature().getName(), cost);

            // 发送告警通知
            alertService.sendAlert("慢查询", pjp.getSignature().getName(), cost);
        }

        return result;
    }
}

4. 数据归档策略

-- 定期归档旧数据到历史表
INSERT INTO user_history
SELECT * FROM user WHERE create_time < '2024-01-01';

DELETE FROM user WHERE create_time < '2024-01-01';

-- 使用分区表（MySQL 8.0+）
ALTER TABLE user
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

---

常见问题

Q1: 为什么深分页会越来越慢？

A: 因为 LIMIT offset, size 需要扫描前 offset + size 行数据，offset 越大，扫描的无效数据越多。使用游标分页或子查询优化可以解决这个问题。

Q2: 如何判断是否需要优化分页？

A:

• 单页查询时间 > 1 秒
• 数据量 > 100 万
• 经常查询页码 > 100 的页面
• COUNT 查询时间 > 500ms

Q3: MyBatis Plus 的分页插件是如何优化 COUNT 的？

A: MyBatis Plus 默认会自动优化 COUNT SQL，将 SELECT * 改为 SELECT count(1)，并去除 ORDER BY 等不必要的子句。可以通过配置进一步优化。

Q4: 游标分页和传统分页如何选择？

A:

• 游标分页：适合移动端、无限加载、顺序浏览场景
• 传统分页：适合管理后台、需要跳页、数据量小的情况

---

参考资料和扩展阅读

• MyBatis Plus 官方文档 - 分页插件
• MySQL 官方文档 - LIMIT Optimization
• 阿里巴巴 Java 开发手册 - 分页规范