如何高效处理超亿级别数据表的分页查询？

引言

在项目初期，架构设计和数据库优化往往不足，导致面对大规模数据时性能瓶颈明显。尤其是当数据量达到千万甚至亿级时，简单的分页查询可能会导致系统响应时间过长，严重影响用户体验。本文将探讨如何通过合理的设计和策略来提升分页查询的效率。

问题背景

最常见的分页查询语句如下：

SELECT * FROM orders 
WHERE status = 'completed' 
ORDER BY create_time DESC 
LIMIT 1000000, 20;

使用EXPLAIN命令分析该SQL语句，可以发现其执行效率低下。例如，当偏移量为1000000时，需要扫描超过100万行数据才能返回结果。

核心优化方案

游标分页

基于主键的游标分页

通过利用MySQL对主键查询的优势，可以实现高效的数据分页。具体思路是按主键递增顺序进行分页，避免了需要跳过大量数据的情况。

@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;

    /**
     * 游标分页查询
     *
     * @param lastId 上一页最后一条记录的ID
     * @param pageSize 页面大小
     * @return 订单列表
     */
    public List
<Order> getOrdersByCursor(Long lastId, int pageSize) {
        if (Objects.isNull(lastId)) {
            // 第一页查询
            return orderMapper.selectFirstPage(pageSize);
        } else {
            // 后续页：基于上一页最后ID查询
            return orderMapper.selectNextPage(lastId, pageSize);
        }
    }
}

对应的SQL语句如下：

-- 查询第一页的数据
SELECT * FROM orders 
WHERE status = 'completed' 
ORDER BY id DESC LIMIT #{pageSize};

-- 后续页：基于主键分页查询
SELECT * FROM orders 
WHERE status = 'completed' AND id < #{lastId} 
ORDER BY id DESC LIMIT #{pageSize};

游标分页的优势在于时间复杂度为O(1)，响应速度非常快，一般在50毫秒以内。适用场景包括Feed流、商品列表和消息列表等无限滚动的场景。

复合索引游标分页

当查询条件较为复杂时，可以使用复合索引来优化查询性能。

-- 使用复合索引进行游标分页查询
SELECT * FROM orders 
WHERE status = 'completed' AND (create_time, id) < ('2024-01-01 10:00:00', 789) ORDER BY create_time DESC, id DESC LIMIT 20;
-- 创建复合索引
CREATE INDEX idx_status_create_time_id ON orders(status, create_time DESC, id DESC);

覆盖索引优化法

当业务逻辑需要支持跳转到指定页码时，可以考虑使用覆盖索引来提升查询性能。

-- 步骤1：通过覆盖索引获取主键列表
SELECT id FROM orders 
WHERE status = 'completed' 
ORDER BY create_time DESC LIMIT 1000000, 20;

-- 步骤2：通过主键回表查询详细信息
SELECT * FROM orders WHERE id IN (/* 上面查询的结果 */);

/** 客户端实现方法 */
public List
<Order> getPageWithCoveringIndex(int pageNum, int pageSize) {
    int offset = (pageNum - 1) * pageSize;
    // 第一步：获取主键列表（走覆盖索引）
    List
<Long> ids = orderMapper.selectIdsOnly(offset, pageSize);

    if (Objects.isNull(ids) || ids.isEmpty()) {
        return Collections.emptyList();
    }

    // 第二步：回表查询详细信息
    return orderMapper.selectByIds(ids);
}

创建覆盖索引的SQL如下：

-- 覆盖索引设计：包含WHERE条件 + ORDER BY字段 + 主键
CREATE INDEX idx_covering ON orders(status, create_time DESC, id);

通过使用覆盖索引，性能可以从28秒提升到约200毫秒。

延迟关联（Deferred Join） - 高级优化

延迟关联是更高级的优化技术，适用于复杂的查询场景。

-- 延迟关联写法
SELECT o.* FROM orders o 
INNER JOIN ( 
    SELECT id FROM orders 
    WHERE status = 'completed' 
    ORDER BY create_time DESC LIMIT 1000000, 20) t ON o.id = t.id;

通过延迟关联，可以减少不必要的数据扫描和重复计算。

总结

本文详细介绍了几种优化超亿级别数据表分页查询的方法。包括基于主键的游标分页、复合索引游标分页以及覆盖索引和延迟关联等高级技术手段。每种方法都有其适用场景，通过合理选择可以大幅提升系统的性能表现。

在实际应用中，建议根据具体业务需求和数据特性进行优化设计，并结合A/B测试来验证不同方案的效果。

数据归档与分区表

冷热数据分离

将冷数据从主表中移除并存储到历史表可以显著减轻主表的压力，提升查询性能。

-- 创建历史表以存储旧数据
CREATE TABLE orders_history LIKE orders;

-- 将一年前的数据迁移到历史表
INSERT INTO orders_history 
SELECT * FROM orders WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);

DELETE FROM orders 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);

> 冷热数据分离效果： > > 主表中的数据量从亿级别减少到千万级别 > 分页查询性能可以提升至少十倍 > * 用户通常只关注最近的数据

MySQL分区技术

通过按时间范围对大表进行分区，可以将大数据集划分为更小、更容易管理的子集。

-- 创建一个按年份分区的订单表
CREATE TABLE orders_partitioned (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(32),
    create_time DATETIME NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_current VALUES LESS THAN MAXVALUE
);

-- 查询时自动路由到相应的分区中去执行
SELECT * FROM orders_partitioned 
WHERE status = 'completed' AND create_time >= '2024-01-01'
ORDER BY create_time DESC LIMIT 1000000, 20;

缓存预加载策略

通过事先将热门数据缓存到本地或者分布式缓存中，可以在用户请求时迅速返回结果。

@Component 
public class PaginationCachePreloader { 
    @Scheduled(fixedRate = 300000) // 每5分钟执行一次  
    public void preloadPopularPages() {
        List
<Long> hotUserIds = getUserHotList();

        for (Long userId : hotUserIds) {
            for (int page = 1; page <= 100; page++) {
                CompletableFuture.runAsync(() -> {
                    List
<Order> orders = orderService.getOrdersByCursor( getLastIdForPage(userId, page), 20 );
                    cacheService.set( "user_orders:" + userId + ":page:" + page, orders, Duration.ofHours(1) );
                });
            }
        }  
    } 
}

数据表设计索引优化

索引设计原则

合理的复合索引可以大大加快查询速度，减少不必要的计算开销。

-- 错误的单列索引
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create_time ON orders(create_time);

-- 正确的复合索引设计
CREATE INDEX idx_optimal ON orders(status, create_time DESC, id);

> 索引设计黄金法则： > > 1. WHERE 条件字段放在最前面 > 2. ORDER BY 字段紧跟其后 > 3. SELECT 字段如果能被索引覆盖则更好 > 4. 主键通常作为最后字段

索引选择性分析

需要评估表中各个字段的选择性，以决定是否可以使用该列进行索引。

-- 分析status和create_time字段的选择度
SELECT COUNT(DISTINCT status) / COUNT(*) as status_selectivity, 
       COUNT(DISTINCT create_time) / COUNT(*) as time_selectivity FROM orders;

-- 高选择性字段应该置于复合索引的前部，反之则调整顺序。

不同场景方案选择

不同的业务场景可能适合采用不同优化策略：

实战性能对比

以下是根据多年工作经验总结的数据（仅供参考）：

总结

面对上亿甚至更大的数据量分页查询，没有一劳永逸的解决方案。需要根据具体情况选择合适的策略，并结合多种优化方法提高系统性能和用户体验。

> 如果本文对您有所帮助，请分享给更多的开发者朋友一起交流提升！