LIMIT offset, size中offset非常大时）的性能问题，有以下几种主流解决方案：
一、问题分析
-- 传统分页：offset越大性能越差（需要扫描前offset条记录）
SELECT * FROM table ORDER BY id LIMIT 1000000, 20;
二、解决方案大全
1. 基于游标的分页（Cursor-based Pagination）
原理：记录上一页最后一条记录的位置
// API设计
GET /items?cursor=last_id&size=20&direction=next
// SQL实现
SELECT * FROM table
WHERE id > last_id
ORDER BY id
LIMIT 20;
// 支持向前翻页
SELECT * FROM table
WHERE id < first_id
ORDER BY id DESC
LIMIT 20;
优点：
缺点：
2. 覆盖索引优化
-- 原始查询（慢）
SELECT * FROM table ORDER BY create_time DESC LIMIT 1000000, 20;
-- 优化后
SELECT t.* FROM table t
INNER JOIN (
SELECT id FROM table
ORDER BY create_time DESC
LIMIT 1000000, 20
) AS tmp ON t.id = tmp.id;
3. 延迟关联（Deferred Join）
@Query(nativeQuery = true, value = """
SELECT t.* FROM (
SELECT id FROM table
WHERE status = :status
ORDER BY create_time DESC
LIMIT :offset, :size
) AS tmp
INNER JOIN table t ON t.id = tmp.id
""")
List<Entity> findWithDeferredJoin(@Param("status") int status,
@Param("offset") long offset,
@Param("size") int size);
4. 业务边界限制
// 限制最大翻页深度
public Page<T> paginate(int page, int size) {
int maxPage = 100; // 最大允许100页
if (page > maxPage) {
throw new BusinessException("最多查询前" + maxPage + "页");
}
// 正常分页逻辑
}
5. 基于ES/Solr的搜索方案
// 使用Elasticsearch进行分页搜索
SearchRequest request = new SearchRequest("index");
SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
sourceBuilder.query(QueryBuilders.matchAllQuery());
sourceBuilder.from(offset);
sourceBuilder.size(size);
request.source(sourceBuilder);
// 对于深度分页，使用search_after
sourceBuilder.searchAfter(lastSortValues);
sourceBuilder.size(size);
6. 分区/分表策略
// 按时间分表查询
public List<Order> getOrders(int page, int size, Date startDate) {
// 根据时间范围确定表名
String tableName = getTableNameByDate(startDate);
String sql = "SELECT * FROM " + tableName + " ORDER BY id LIMIT ? OFFSET ?";
return jdbcTemplate.query(sql, rowMapper, size, (page-1)*size);
}
7. 物化视图/汇总表
-- 创建汇总表
CREATE TABLE summary_table (
id BIGINT PRIMARY KEY,
main_id BIGINT,
summary_data VARCHAR(500),
KEY idx_created(created_time)
) ENGINE=InnoDB;
-- 查询时先查小表
SELECT * FROM summary_table
ORDER BY created_time DESC
LIMIT 1000000, 20;
8. 二级缓存方案
@Component
public class PaginationCacheService {
@Cacheable(value = "pageCache",
key = "'page:' + #page + ':size:' + #size + ':params:' + #paramsHash")
public PageResult<T> getCachedPage(int page, int size, String paramsHash) {
// 数据库查询
return dao.findPage(page, size, params);
}
// 使用布隆过滤器防止缓存穿透
public boolean mightExist(int page) {
return bloomFilter.mightContain("page:" + page);
}
}
9. 流式查询处理
// 使用JPA Stream
@Query("SELECT e FROM Entity e WHERE e.status = :status")
Stream<Entity> streamAllByStatus(@Param("status") String status);
// 分页处理
try (Stream<Entity> stream = repository.streamAllByStatus("active")) {
List<Entity> pageData = stream
.skip(offset)
.limit(size)
.collect(Collectors.toList());
}
10. 混合方案：游标+缓存
public class HybridPaginationService {
private Map<String, CursorPosition> cursorCache = new ConcurrentHashMap<>();
public PageResult<T> paginate(String cursorToken, int size) {
CursorPosition cursor = cursorCache.get(cursorToken);
if (cursor == null) {
cursor = new CursorPosition(0, System.currentTimeMillis());
}
List<T> items = dao.findAfterCursor(cursor.getLastId(), size);
// 生成新游标
String newCursor = generateCursor(items.get(items.size()-1).getId());
cursorCache.put(newCursor, new CursorPosition(
items.get(items.size()-1).getId(),
System.currentTimeMillis()
));
return new PageResult<>(items, newCursor);
}
}
三、方案选择建议
| 场景 |
推荐方案 |
说明 |
|---|
| 后台管理系统 |
延迟关联 + 缓存 |
允许一定延迟，支持跳页 |
| C端无限滚动 |
游标分页 |
性能最优，体验好 |
| 搜索功能 |
ES/Solr |
支持复杂查询和深度分页 |
| 报表导出 |
流式查询 |
大数据量导出 |
| 实时性要求高 |
覆盖索引优化 |
简单有效 |
四、完整示例：游标分页实现
@RestController
@RequestMapping("/api/products")
public class ProductController {
@GetMapping
public ApiResponse<CursorPageResult<ProductDTO>> listProducts(
@RequestParam(required = false) String cursor,
@RequestParam(defaultValue = "20") int size,
@RequestParam(defaultValue = "next") String direction) {
CursorPageResult<ProductDTO> result;
if ("prev".equals(direction)) {
result = productService.getPreviousPage(cursor, size);
} else {
result = productService.getNextPage(cursor, size);
}
return ApiResponse.success(result);
}
}
@Service
public class ProductService {
public CursorPageResult<ProductDTO> getNextPage(String cursor, int size) {
Long lastId = parseCursor(cursor); // 解析游标
List<Product> products;
if (lastId == null) {
products = productRepository.findFirstPage(size);
} else {
products = productRepository.findAfterId(lastId, size);
}
if (products.isEmpty()) {
return CursorPageResult.empty();
}
String nextCursor = encodeCursor(products.get(products.size()-1).getId());
String prevCursor = encodeCursor(products.get(0).getId());
return CursorPageResult.of(
products.stream().map(this::toDTO).collect(Collectors.toList()),
nextCursor,
prevCursor,
products.size() == size
);
}
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
@Query("SELECT p FROM Product p WHERE p.id > :lastId ORDER BY p.id ASC")
List<Product> findAfterId(@Param("lastId") Long lastId,
Pageable pageable);
default List<Product> findAfterId(Long lastId, int size) {
return findAfterId(lastId, PageRequest.of(0, size));
}
@Query("SELECT p FROM Product p ORDER BY p.id ASC")
List<Product> findFirstPage(Pageable pageable);
default List<Product> findFirstPage(int size) {
return findFirstPage(PageRequest.of(0, size));
}
}
}
五、监控与优化建议
监控慢查询
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
-- 分析执行计划
EXPLAIN SELECT * FROM table LIMIT 1000000, 20;
2. **索引优化原则**
- 为ORDER BY字段建立索引
- 复合索引顺序：(where条件字段, order by字段)
- 避免filesort
3. **架构层面考虑**
- 读分离，将分页查询定向到从库
- 考虑使用TiDB等分布式数据库
- 对于历史数据归档处理
选择哪种方案需要根据具体业务场景、数据量、并发量和技术栈综合考虑。通常建议优先使用游标分页，如果必须支持跳页，则使用延迟关联+缓存方案。
