MySQL的深度分页如何优化?

MySQL的深度分页如何优化?

MySQL 深度分页的优化是衡量数据库功底的重要指标。我将从 原理分析、优化方案、生产实践 三个层面，为您提供一套完整的解决方案。

一、 问题本质：为什么深度分页会慢？

让我们从执行计划的角度来理解这个核心问题。

原始深度分页SQL：

SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 1000000, 10;  -- 跳过100万条，取10条

MySQL 的执行过程：

1. 解析查询：识别需要查询的表和条件
2. 执行排序：按照 ORDER BY create_time DESC 对所有数据进行排序
3. 定位偏移量：遍历排序结果，实际跳过前 1000000 条记录
4. 返回结果：取接下来的 10 条记录

性能瓶颈分析：

• I/O 开销巨大：即使只需要最后 10 条，MySQL 也必须读取并排序前 1000000+10 条数据
• 内存压力：如果排序无法在内存中完成，会使用临时文件进行磁盘排序
• 网络传输：虽然只返回 10 条，但内部处理了百万级数据

执行计划解释：

EXPLAIN SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;

会出现 Using filesort，并且 rows 列会显示需要处理 1000010 行。

二、 六大优化方案详解

方案一：覆盖索引优化法（最常用）

核心思想：让查询只需要扫描索引，避免回表。

-- 优化前（需要回表）：
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;

-- 优化后（覆盖索引）：
SELECT * FROM orders 
INNER JOIN (
    SELECT id FROM orders  -- 只查主键
    ORDER BY create_time DESC 
    LIMIT 1000000, 10
) AS tmp USING(id);

为什么快？

1. 子查询只查询 id 和 create_time，如果创建了 (create_time, id) 的联合索引，这就是覆盖索引
2. 子查询结果只有 10 条，主查询只需回表 10 次
3. 数据量减少几个数量级

创建合适的索引：

-- 最佳索引：覆盖排序字段和查询字段
CREATE INDEX idx_cover ON orders(create_time DESC, id);

-- 如果还需要其他字段，可以考虑包含列
-- 但注意：MySQL 的包含列只在特定条件下有效

执行计划对比：

• 优化前：Using filesort，扫描全表
• 优化后：Using index，只扫描索引

方案二：游标分页法（连续分页最优解）

适用场景：只能下一页/上一页，不能跳页。

原理：记录上一页最后一条记录的位置。

-- 第一页
SELECT * FROM orders 
ORDER BY create_time DESC, id DESC  -- 双字段排序确保唯一性
LIMIT 10;

-- 第二页（假设上一页最后一条的 create_time='2024-01-01 10:00:00', id=999）
SELECT * FROM orders 
WHERE (create_time, id) < ('2024-01-01 10:00:00', 999)
ORDER BY create_time DESC, id DESC 
LIMIT 10;

索引设计：

-- 创建支持游标分页的索引
CREATE INDEX idx_cursor ON orders(create_time DESC, id DESC);

优势：

• 时间复杂度从 O(N) 降到 O(log N + M)
• 完全避免偏移量计算

限制：

• 不支持随机跳页
• 需要客户端记录游标位置

方案三：业务妥协法（改变产品逻辑）

很多时候，技术问题可以通过产品设计解决。

方案对比：

• 限制最大翻页：只允许查看前 100 页（电商常用）
• 分段加载：如 Google 搜索，不显示总页数，只显示"查看更多"
• 时间范围筛选：默认只查最近 3 个月，需要更早数据需手动选择时间段

-- 添加时间筛选条件
SELECT * FROM orders 
WHERE create_time >= '2024-01-01'
ORDER BY create_time DESC 
LIMIT 1000000, 10;  -- 如果数据量仍然大，结合其他方案

方案四：延迟关联法（JOIN 优化）

这是覆盖索引法的变体，在某些场景下性能更好。

SELECT * FROM orders 
INNER JOIN (
    SELECT id 
    FROM orders 
    ORDER BY create_time DESC 
    LIMIT 1000000, 10
) AS tmp ON orders.id = tmp.id
ORDER BY orders.create_time DESC;  -- 外层再次排序

方案五：ID 范围限定法（特定场景）

如果 ID 是自增且基本有序的，可以利用 ID 范围缩小查询集。

-- 先确定ID范围
SELECT MIN(id), MAX(id) FROM orders 
WHERE create_time >= '2024-01-01';

-- 然后分页
SELECT * FROM orders 
WHERE id BETWEEN 1000000 AND 2000000
ORDER BY create_time DESC 
LIMIT 0, 10;

方案六：分区表法（海量数据终极方案）

对于亿级以上数据，考虑按时间分区。

-- 创建按月分区的表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    create_time DATETIME,
    -- 其他字段...
) PARTITION BY RANGE (YEAR(create_time) * 100 + MONTH(create_time)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    -- 更多分区...
);

-- 查询时只扫描相关分区
SELECT * FROM orders 
WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY create_time DESC 
LIMIT 1000000, 10;

三、 生产环境最佳实践

1. 索引设计黄金法则

-- 1. 为排序字段创建索引
CREATE INDEX idx_sort ON orders(create_time DESC);

-- 2. 为游标分页创建复合索引
CREATE INDEX idx_cursor ON orders(create_time DESC, id DESC);

-- 3. 为覆盖索引创建包含列（MySQL 8.0+）
CREATE INDEX idx_covering ON orders(create_time DESC, id)
INCLUDE (user_id, amount);  -- 包含其他查询字段

2. 分页查询封装（Java示例）

@Component
public class PaginationService {
    
    // 方法1：覆盖索引分页
    public Page<Order> pageByCoveringIndex(int page, int size) {
        String sql = """
            SELECT o.* FROM orders o
            INNER JOIN (
                SELECT id FROM orders
                ORDER BY create_time DESC
                LIMIT ?, ?
            ) AS tmp ON o.id = tmp.id
            ORDER BY o.create_time DESC
            """;
        return jdbcTemplate.query(sql, new Object[]{(page-1)*size, size}, rowMapper);
    }
    
    // 方法2：游标分页
    public Page<Order> pageByCursor(Long lastId, LocalDateTime lastTime, int size) {
        String sql = """
            SELECT * FROM orders
            WHERE (create_time, id) < (?, ?)
            ORDER BY create_time DESC, id DESC
            LIMIT ?
            """;
        return jdbcTemplate.query(sql, new Object[]{lastTime, lastId, size}, rowMapper);
    }
    
    // 方法3：Elasticsearch辅助搜索（超大数据量）
    public Page<Order> pageByES(int page, int size) {
        // 使用ES搜索id列表
        List<Long> ids = elasticsearchService.searchOrderIds(page, size);
        // 然后从MySQL中批量查询
        return orderRepository.findAllByIdIn(ids);
    }
}

3. 监控与调优

-- 1. 监控慢查询
SELECT * FROM mysql.slow_log 
WHERE query_text LIKE '%LIMIT%' 
ORDER BY start_time DESC 
LIMIT 10;

-- 2. 分析执行计划
EXPLAIN ANALYZE 
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;

-- 3. 查看索引使用情况
SELECT 
    index_name,
    rows_selected,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM sys.schema_index_statistics
WHERE table_name = 'orders';

4. 架构级解决方案

读写分离 + 搜索引擎：

客户端 → 负载均衡 → 
    写操作 → MySQL主库
    读操作 → 
        简单分页 → MySQL从库
        复杂分页/搜索 → Elasticsearch

具体实现：

1. 实时数据：MySQL 处理事务和简单查询
2. 分页查询：Elasticsearch 建立索引，支持快速深度分页
3. 数据同步：使用 Canal 或 Debezium 同步 MySQL 数据到 ES

@Service
public class HybridSearchService {
    @Autowired
    private OrderRepository mysqlRepo;
    
    @Autowired
    private ElasticsearchRestTemplate esTemplate;
    
    public Page<Order> searchOrders(OrderQuery query, int page, int size) {
        if (query.isSimpleQuery() && page <= 100) {
            // 前100页走MySQL
            return mysqlRepo.search(query, PageRequest.of(page, size));
        } else {
            // 深度分页或复杂查询走ES
            NativeSearchQuery esQuery = buildEsQuery(query, page, size);
            SearchHits<Order> hits = esTemplate.search(esQuery, Order.class);
            return convertToPage(hits, page, size);
        }
    }
}

四、 不同场景的选型建议

总结

面试官，MySQL 深度分页优化是一个系统工程，我的解决方案可以总结为：

1. 先诊后治：通过 EXPLAIN ANALYZE 分析性能瓶颈，确定是排序问题还是回表问题。

2. 索引为王：90% 的深度分页问题可以通过 覆盖索引 解决。创建 (排序字段, 主键, 查询字段) 的复合索引是最有效的手段。

3. 架构升级：当数据量达到亿级时，必须考虑 读写分离 + 搜索引擎 的架构方案。MySQL 不适合做深度分页的"重型"查询。

4. 业务妥协：最好的优化是改变需求。限制翻页深度、添加时间筛选、改为游标分页，这些产品层面的优化往往比技术优化更有效。

作为架构师，我最常用的组合拳是：

对于千万级数据：覆盖索引 + 游标分页 对于亿级数据：MySQL + Elasticsearch 双写，简单查询走 MySQL，复杂分页走 ES

这背后的架构哲学是：不要用数据库做它不擅长的事。MySQL 擅长事务和简单查询，Elasticsearch 擅长搜索和分页，各司其职才能构建高性能系统。