MySQL实战：基于游标与覆盖索引的高效分页解决分页查询卡顿

原创

小明互联网技术分享社区

发布于 2025-07-03 13:09:32

7680

文章被收录于专栏：MYSQLMYSQL

当分页成为性能杀手

你是否遇到过这样的场景？

用户浏览电商订单时，翻到第100页需要等待8秒
后台管理系统查询日志，每次翻页都触发数据库CPU飙升
移动端APP瀑布流加载，越往下滑动卡顿越明显

根本原因：传统的LIMIT offset, size分页方式在大数据量下会产生全表扫描+临时排序，当offset值达到10万量级时，MySQL需要遍历并丢弃前10万行数据才能返回结果。今天给大家分享游标分页与覆盖索引两大核心技术，实测将百万级数据分页耗时从秒级降至毫秒级！

一、传统分页的性能困境

1.1 问题复现

典型的慢查询示例：

SELECT * FROM order_history 
WHERE user_id = 100 
ORDER BY create_time DESC 
LIMIT 100000, 10;

执行计划分析：

type=ALL（全表扫描）
rows=100010（实际扫描行数）
Extra=Using filesort（文件排序）

1.2 性能损耗原理

三级性能瓶颈：

IO成本：扫描全部索引树或数据页
CPU成本：排序丢弃前N条数据
网络成本：传输冗余数据

二、游标分页：像翻书一样连续翻页

2.1 核心原理

利用有序唯一值作为定位锚点，避免遍历历史数据：

-- 下一页  
SELECT * FROM order_history  
WHERE user_id = 100 AND id > 上一页最后一条ID  
ORDER BY id ASC  
LIMIT 10;  

-- 上一页  
SELECT * FROM order_history  
WHERE user_id = 100 AND id < 当前页第一条ID  
ORDER BY id DESC  
LIMIT 10;

2.2 实战案例：电商订单分页优化

原始表结构：

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    create_time DATETIME,
    INDEX idx_user_create(user_id, create_time)
);

优化后查询：

-- 第一页  
SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY create_time DESC, id DESC 
LIMIT 10;

-- 下一页（假设上一页最后一条create_time='2023-08-20 15:30:00', id=9527）  
SELECT * FROM orders  
WHERE user_id = 100 
AND (create_time < '2023-08-20 15:30:00' 
     OR (create_time = '2023-08-20 15:30:00' AND id < 9527))  
ORDER BY create_time DESC, id DESC  
LIMIT 10;

优化效果：

执行时间从**1200ms**降至**8ms**
扫描行数从**100010行**变为**10行**

三、覆盖索引分页：让查询“悬浮”在索引上

3.1 核心原理

通过索引覆盖避免回表查询，结合延迟关联（Deferred Join） 技术：

SELECT t.* FROM (
    SELECT id FROM orders  
    WHERE user_id = 100  
    ORDER BY create_time DESC  
    LIMIT 100000, 10  
) AS tmp  
INNER JOIN orders t ON tmp.id = t.id;

3.2 实战案例：用户行为日志分析

表结构：

CREATE TABLE user_behavior (
    id BIGINT AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(20),
    device VARCHAR(50),
    log_time DATETIME,
    PRIMARY KEY(id),
    INDEX idx_user_log(user_id, log_time)
);

优化前后对比：

-- 原始查询（耗时1.2秒）
SELECT * FROM user_behavior  
WHERE user_id = 500  
ORDER BY log_time DESC  
LIMIT 80000, 20;  

-- 覆盖索引优化（耗时45毫秒）
SELECT t.* FROM (
    SELECT id FROM user_behavior  
    WHERE user_id = 500  
    ORDER BY log_time DESC  
    LIMIT 80000, 20  
) AS tmp  
INNER JOIN user_behavior t ON tmp.id = t.id;

执行计划变化：

子查询Using index（仅扫描索引）
主查询Using where（快速主键检索）

四、组合拳：终极优化方案

4.1 延迟关联 + 游标分页

SELECT t.* FROM (
    SELECT id FROM orders  
    WHERE user_id = 100 AND id > 上一页最后ID  
    ORDER BY id ASC  
    LIMIT 10  
) AS tmp  
INNER JOIN orders t ON tmp.id = t.id;

4.2 业务层优化策略

禁止跳页：只允许“上一页/下一页”操作
冷热分离：近期数据与历史数据分表存储
异步加载：前端分页与后端分批加载解耦

五、其他优化技巧

5.1 分区表分页

按时间范围分区后查询：

-- 按月分区  
PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    ...
);  

-- 查询指定分区  
SELECT * FROM orders PARTITION (p202307)  
WHERE user_id = 100  
ORDER BY create_time DESC  
LIMIT 10;

5.2 读写分离分页

将分页查询路由到只读副本：

-- 主库写入
INSERT INTO orders(...) VALUES(...);  

-- 从库分页查询  
SELECT * FROM orders_slave  
WHERE user_id = 100  
ORDER BY create_time DESC  
LIMIT 100000, 10;

总结：分页优化的三维突破

优化维度	技术手段	适用场景
查询模式	游标分页	连续分页（如APP瀑布流）
索引设计	覆盖索引 + 延迟关联	复杂排序分页
架构设计	分区表 + 读写分离	超大数据量场景

互动问答：

💡 你在项目中遇到过哪些分页性能问题？最终是如何解决的？欢迎在评论区分享你的实战经验！

日常建议：

立即使用EXPLAIN分析现有分页查询
在测试环境对比LIMIT与游标分页性能差异
为高频分页查询创建专用覆盖索引

让技术产生价值，让数据流畅滑动！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S14#新手村

mysql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S14#新手村

mysql

登录后参与评论

0 条评论

热度