设计高性能分页查询并解决深分页性能问题

题目

设计高性能分页查询并解决深分页性能问题

信息

• 类型：问答
• 难度：⭐⭐⭐

考点

MyBatis分页原理,SQL优化,深分页性能问题,插件开发

快速回答

高性能分页方案需解决深分页性能瓶颈：

• 避免使用RowBounds内存分页，改用数据库物理分页（如MySQL的LIMIT）
• 针对深分页问题，采用游标分页（基于索引列）或延迟关联优化
• 通过MyBatis插件统一处理分页逻辑，自动改写SQL
• 只查询必要字段，避免SELECT *

1. 问题背景与原理

传统分页（如MySQL的LIMIT offset, size）在深分页场景（offset值极大时）性能急剧下降：数据库需扫描offset+size条数据后丢弃前offset条。例如LIMIT 1000000, 20实际扫描1,000,020行。

2. 优化方案与代码实现

方案1：游标分页（基于索引列）

// Mapper接口
List<User> selectAfterId(@Param("lastId") Long lastId, @Param("size") int size);

// XML映射
<select id="selectAfterId" resultType="User">
  SELECT id, name, email
  FROM users
  WHERE id > #{lastId}
  ORDER BY id
  LIMIT #{size}
</select>

优点：利用索引直接定位，时间复杂度O(size)
限制：要求排序字段唯一且连续

方案2：延迟关联（Deferred Join）

SELECT * 
FROM users u
JOIN (
  SELECT id
  FROM users
  ORDER BY create_time
  LIMIT 1000000, 20
) AS tmp USING(id);

内层查询仅扫描索引，避免全表回查。

3. MyBatis插件实现统一分页

@Intercepts(@Signature(
  type = Executor.class,
  method = "query",
  args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
))
public class PaginationInterceptor implements Interceptor {

  @Override
  public Object intercept(Invocation invocation) throws Throwable {
    Object[] args = invocation.getArgs();
    RowBounds rb = (RowBounds) args[2];

    // 非分页查询直接放行
    if (rb == RowBounds.DEFAULT) return invocation.proceed();

    // 获取原始SQL
    MappedStatement ms = (MappedStatement) args[0];
    BoundSql boundSql = ms.getBoundSql(args[1]);
    String originalSql = boundSql.getSql();

    // 游标分页改写（示例）
    String newSql = originalSql + 
                   " WHERE id > #{__lastId} ORDER BY id LIMIT #{__size}";

    // 创建新BoundSql
    BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), newSql, 
        boundSql.getParameterMappings(), boundSql.getParameterObject());

    // 使用新SQL创建MappedStatement
    MappedStatement newMs = newMappedStatement(ms, new BoundSqlSqlSource(newBoundSql));
    args[0] = newMs;
    args[2] = RowBounds.DEFAULT; // 重置分页参数

    return invocation.proceed();
  }

  // 辅助方法：创建新MappedStatement（需处理缓存等细节）
  private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
    // 实现细节省略...
  }
}

4. 最佳实践

• 索引优化：分页排序字段必须建立索引
• 避免SELECT ***：只查询必要字段减少数据传输
• 业务妥协：限制可跳转页数（如只允许前100页）
• 二级缓存：高频访问数据使用MyBatis二级缓存

5. 常见错误

• 索引缺失：排序字段无索引导致全表扫描
• 错误计数：使用SELECT COUNT(*)统计全表，大表应改用估算值
• 内存溢出：插件中未重置RowBounds导致循环分页
• 参数混淆：游标分页未处理参数边界条件（如首次查询无lastId）

6. 扩展知识

• MyBatis-Plus分页插件：内置PaginationInterceptor支持多数据库方言
• 分布式ID方案：雪花算法等保证游标分页的连续性
• 二级缓存策略：分页查询结果慎用二级缓存，易致数据不一致
• NoSQL替代方案：Elasticsearch的search_after机制处理千万级分页