一、延迟关联分页
延迟关联分页是一种优化数据库查询性能的技术，特别适用于大数据量下的分页查询。它的核心思想是先通过子查询获取分页所需的主键，再用这些主键关联获取完整数据，避免了大偏移量时的性能问题。
实现方式
- 原生SQL实现
@Repository
public class UserRepository {
@PersistenceContext
private EntityManager entityManager;
public Page<User> findUsersWithDelayJoin(int page, int size) {
// 1. 先查询分页的ID
String idQuerySql = "SELECT u.id FROM User u ORDER BY u.createTime DESC";
Query idQuery = entityManager.createNativeQuery(idQuerySql);
idQuery.setFirstResult((page - 1) * size);
idQuery.setMaxResults(size);
List<Long> ids = idQuery.getResultList();
if (ids.isEmpty()) {
return new PageImpl<>(Collections.emptyList(), PageRequest.of(page - 1, size), 0);
}
// 2. 根据ID查询完整数据
String dataQuerySql = "SELECT u FROM User u WHERE u.id IN :ids ORDER BY u.createTime DESC";
List<User> users = entityManager.createQuery(dataQuerySql, User.class)
.setParameter("ids", ids)
.getResultList();
// 3. 查询总数
String countSql = "SELECT COUNT(u) FROM User u";
long total = entityManager.createQuery(countSql, Long.class)
.getSingleResult();
return new PageImpl<>(users, PageRequest.of(page - 1, size), total);
}
}
- MyBatis实现
@Mapper
public interface UserMapper {
@Select("SELECT id FROM user ORDER BY create_time DESC LIMIT #{offset}, #{size}")
List<Long> selectUserIds(@Param("offset") int offset, @Param("size") int size);
@Select("SELECT * FROM user WHERE id IN " +
"<foreach item='id' collection='ids' open='(' separator=',' close=')'>" +
"#{id}" +
"</foreach> " +
"ORDER BY create_time DESC")
List<User> selectUsersByIds(@Param("ids") List<Long> ids);
@Select("SELECT COUNT(*) FROM user")
long countUsers();
}
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
public Page<User> getUsersWithDelayJoin(int page, int size) {
int offset = (page - 1) * size;
// 1. 先查询分页的ID
List<Long> ids = userMapper.selectUserIds(offset, size);
if (ids.isEmpty()) {
return new PageImpl<>(Collections.emptyList(), PageRequest.of(page, size), 0);
}
// 2. 根据ID查询完整数据
List<User> users = userMapper.selectUsersByIds(ids);
// 3. 查询总数
long total = userMapper.countUsers();
return new PageImpl<>(users, PageRequest.of(page, size), total);
}
}
性能对比
延迟关联分页相比传统分页的优势：
- 大偏移量时性能更好：传统分页 LIMIT 100000, 20 需要扫描100020行；延迟关联先获取20个ID再关联，只扫描20行完整数据。
- 减少数据传输量：第一次查询只返回ID，不返回所有字段。
- 缓存友好：ID查询结果更容易被缓存。
注意事项
- 排序一致性：确保两次查询使用相同的排序条件，否则可能出现数据不一致。
- ID查询优化：确保ID查询使用了适当的索引。
- 事务一致性：如果数据在两次查询间发生变化，可能导致结果不一致，必要时加锁或使用事务。
- 小数据量：对于小数据量，传统分页可能更简单高效。
这种实现方式特别适合在数据量大、翻页深的场景下使用，可以显著提高分页查询性能。
二、游标分页实现
游标分页是一种更高效的分页方式，特别适合无限滚动或社交媒体动态流等场景。相比传统的偏移量分页，它避免了偏移量大时的性能问题
游标分页原理
使用一个唯一且有序的字段（通常是ID或创建时间）作为游标
客户端传递"上一页最后一条记录的游标值"来获取下一页
不需要知道总页数或总记录数
- 基于MyBatis的实现
@Mapper
public interface PostMapper {
@Select("SELECT * FROM post ORDER BY created_at DESC LIMIT #{size}")
List<Post> findFirstPage(@Param("size") int size);
@Select("SELECT * FROM post WHERE created_at < #{cursor} ORDER BY created_at DESC LIMIT #{size}")
List<Post> findByCursor(@Param("cursor") LocalDateTime cursor, @Param("size") int size);
}
@Service
public class PostService {
@Autowired
private PostMapper postMapper;
public CursorPage<Post> getPosts(LocalDateTime cursor, int size) {
List<Post> posts = cursor == null
? postMapper.findFirstPage(size)
: postMapper.findByCursor(cursor, size);
LocalDateTime nextCursor = posts.isEmpty() ? null : posts.get(posts.size() - 1).getCreatedAt();
boolean hasNext = posts.size() == size;
return new CursorPage<>(posts, size, nextCursor, hasNext);
}
}
游标分页的优势
- 性能更好：避免了OFFSET带来的性能问题，特别是大数据量时
- 实时性更好：新增或删除记录不会影响分页结果的一致性
- 无限滚动友好：非常适合无限滚动的UI设计
- 无跳跃问题：不会出现传统分页跳转到某页时数据变化的问题
注意事项
- 游标字段选择：应选择唯一且有序的字段（如自增ID、创建时间戳）
- 排序方向：确保查询排序与游标方向一致
- 多字段排序：如需多字段排序，游标应包含所有这些字段
- 安全性：游标值不应暴露敏感信息，必要时可加密
游标分页是实现高效、稳定分页的优选方案，特别适合现代Web和移动应用中的动态内容加载场景。
三、覆盖索引分页
覆盖索引分页是一种高效的分页优化技术，它通过创建包含查询所需全部字段的索引，使查询可以完全通过索引完成，避免回表操作，从而大幅提升分页性能。
覆盖索引分页原理
- 覆盖索引：索引包含查询需要的所有字段，查询只需扫描索引而无需访问数据表
- 分页优化：结合覆盖索引和延迟关联技术，先通过索引获取分页主键，再关联获取完整数据
实现步骤
- 数据库准备
首先确保有合适的覆盖索引，例如对于用户表：
-- 创建包含常用查询字段的覆盖索引
CREATE INDEX idx_user_covering ON users(id, username, email, create_time);
- Spring Boot + MyBatis 实现
@Mapper
public interface UserMapper {
// 覆盖索引查询
@Select({
"SELECT id, username, email FROM users",
"ORDER BY create_time DESC",
"LIMIT #{offset}, #{size}"
})
List<Map<String, Object>> selectCoveringIndex(@Param("offset") int offset,
@Param("size") int size);
// 总数查询
@Select("SELECT COUNT(*) FROM users")
long countUsers();
// 完整实体查询
@Select({
"<script>",
"SELECT * FROM users WHERE id IN",
"<foreach item='id' collection='ids' open='(' separator=',' close=')'>",
"#{id}",
"</foreach>",
"ORDER BY create_time DESC",
"</script>"
})
List<User> selectByIds(@Param("ids") List<Long> ids);
}
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
public Page<User> getUsersWithCoveringIndex(int page, int size) {
int offset = page * size;
// 1. 使用覆盖索引获取分页数据
List<Map<String, Object>> coveringData = userMapper.selectCoveringIndex(offset, size);
if (coveringData.isEmpty()) {
long total = userMapper.countUsers();
return new PageImpl<>(Collections.emptyList(),
PageRequest.of(page, size),
total);
}
// 2. 提取ID列表
List<Long> ids = coveringData.stream()
.map(map -> (Long) map.get("id"))
.collect(Collectors.toList());
// 3. 根据ID获取完整实体
List<User> users = userMapper.selectByIds(ids);
// 4. 获取总数
long total = userMapper.countUsers();
return new PageImpl<>(users,
PageRequest.of(page, size),
total);
}
}
优化说明
索引设计：
- 确保索引包含查询所需的所有字段
- 排序字段必须包含在索引中
示例：CREATE INDEX idx_covering ON table(col1, col2, col3)
查询优化：
第一阶段查询只从索引获取数据（Using index）
第二阶段通过主键批量获取完整数据
性能对比：
- 传统分页：需要访问表数据+排序+跳过OFFSET
- 覆盖索引分页：仅访问索引+按主键精确查找
适用场景
- 需要显示部分字段的列表页
- 大数据量下的分页查询
- 排序字段固定的分页需求
- 读多写少的业务场景
注意事项
- 索引维护成本：覆盖索引会占用更多存储空间，并影响写入性能
- 字段变更：查询字段变化时需要调整索引
- 索引选择：不是所有字段都适合包含在覆盖索引中
- 查询计划：定期检查EXPLAIN结果，确保查询使用了正确的索引
高级优化（组合策略）
public Page<User> getOptimizedUsers(int page, int size, String sortField) {
// 1. 使用覆盖索引获取ID和排序字段
Page<Object[]> idPage = userRepository.findIdsAndSortField(
PageRequest.of(page, size, Sort.by(sortField).descending())
);
// 2. 延迟关联获取完整数据
List<User> users = userRepository.findByIdIn(
extractIds(idPage.getContent()),
Sort.by(sortField).descending()
);
return new PageImpl<>(users, idPage.getPageable(), idPage.getTotalElements());
}
这种组合策略可以应对更复杂的分页需求，同时保持高性能。
网友评论