mysql 如何解决深度分页

油墨香^_^

3028人浏览 · 2023-04-17 09:43:49

油墨香^_^ · 2023-04-17 09:43:49 发布

1. 游标分页（推荐）

使用基于索引列的游标来替代传统的OFFSET：

sql

-- 传统分页（慢）
SELECT * FROM table ORDER BY id LIMIT 10000, 20;

-- 游标分页（快）
SELECT * FROM table WHERE id > 10000 ORDER BY id LIMIT 20;

前端需要配合：

第一次请求：SELECT * FROM table ORDER BY id LIMIT 20
后续请求：SELECT * FROM table WHERE id > [最后一条记录的id] ORDER BY id LIMIT 20

2. 子查询优化

sql

-- 优化后的深度分页
SELECT * FROM table 
WHERE id >= (SELECT id FROM table ORDER BY id LIMIT 10000, 1)
ORDER BY id LIMIT 20;

3. 覆盖索引 + 延迟关联

sql

-- 先通过覆盖索引获取主键
SELECT id FROM table 
ORDER BY create_time 
LIMIT 10000, 20;

-- 再通过主键查询完整数据
SELECT * FROM table 
WHERE id IN (/* 上一步查询到的主键ID */);

4. 业务层优化

限制最大分页深度

sql

-- 设置最大偏移量
SELECT * FROM table 
ORDER BY id 
LIMIT 20 OFFSET LEAST(10000, 1000); -- 最大只能到1000页

使用时间范围过滤

sql

-- 如果按时间排序，可以先确定时间范围
SELECT * FROM table 
WHERE create_time > '2023-01-01'
ORDER BY create_time 
LIMIT 20;

5. 分区表策略

对于超大数据表，可以考虑按时间或其他维度分区：

sql

-- 创建按月分区的表
CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT,
    content TEXT,
    created_at DATETIME,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)*100 + MONTH(created_at)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    -- ...
);

6. 完整示例

sql

-- 创建测试表
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created_at (created_at)
);

-- 游标分页实现
DELIMITER //
CREATE PROCEDURE paginate_users(
    IN last_id BIGINT,
    IN page_size INT
)
BEGIN
    IF last_id = 0 THEN
        SELECT * FROM users 
        ORDER BY id 
        LIMIT page_size;
    ELSE
        SELECT * FROM users 
        WHERE id > last_id 
        ORDER BY id 
        LIMIT page_size;
    END IF;
END //
DELIMITER ;

7. 前端配合方案

javascript

// 前端存储最后一条记录的ID
class Pagination {
    constructor() {
        this.lastId = 0;
        this.hasMore = true;
    }
    
    async loadNextPage() {
        if (!this.hasMore) return;
        
        const response = await fetch(`/api/users?lastId=${this.lastId}&limit=20`);
        const data = await response.json();
        
        if (data.users.length > 0) {
            this.lastId = data.users[data.users.length - 1].id;
            this.hasMore = data.hasMore;
            return data.users;
        }
        this.hasMore = false;
        return [];
    }
}

性能对比

方法	10万数据(ms)	100万数据(ms)	优点	缺点
LIMIT OFFSET	120	1200+	简单易用	性能差
游标分页	2	3	性能最佳	不能跳页
子查询优化	15	50	支持跳页	比游标慢
延迟关联	8	25	平衡性好	需要两次查询