MySQL Limit深度分页性能问题与优化方案

问题背景

先来看一个典型场景。使用MySQL进行数据查询，特别是涉及分页操作时，随着页码向后翻动，查询速度会持续下降。翻到第1页可能毫秒级响应，翻到第100页尚可接受，但翻到第1000页或第10000页时，情况如何？

这就是所谓的“深度分页”——当数据量增大，偏移量（offset）升高，性能问题便暴露出来。

什么是深度分页

深度分页指查询偏移量很大的分页场景。例如以下SQL语句，从第一页到第1000页，再到第10万页，速度差距非常明显：

-- 第一页：查询很快SELECT * FROM orders ORDER BY id LIMIT 0, 10;-- 第1000页：开始变慢SELECT * FROM orders ORDER BY id LIMIT 9990, 10;-- 第100000页：非常慢SELECT * FROM orders ORDER BY id LIMIT 999990, 10;

哪些业务场景会遇到这个问题？

• 电商商品列表，百万级商品很常见
• 订单历史查询，千万级订单是常态
• 日志数据查询，亿级日志很普遍
• 社交媒体动态，海量用户内容需要展示

性能问题分析

MySQL Limit 执行原理

当执行 SELECT * FROM table ORDER BY column LIMIT offset, limit 时，MySQL内部的工作流程大致分为三步：

1. 根据索引查找排序后的前 offset + limit 条记录
2. 丢弃前 offset 条记录
3. 返回剩余的 limit 条记录

关键问题：MySQL必须扫描并读取前 offset + limit 条记录，即使最终只需要 limit 条。这是性能瓶颈的根源。

性能问题根源

通过具体数字可以更直观地理解：

查询: SELECT * FROM orders ORDER BY id LIMIT 1000000, 10执行过程:┌─────────────────────────────────────────────────────┐│ 1. 扫描 1000010 条记录                              ││ 2. 读取前 1000000 条记录到内存                      ││ 3. 丢弃前 1000000 条记录                           ││ 4. 返回最后 10 条记录                              │└─────────────────────────────────────────────────────┘时间复杂度: O(offset + limit)空间复杂度: O(offset + limit)

MySQL花费大量时间扫描和读取了100万条记录，最后全部丢弃，只保留10条。这是典型的“吃力不讨好”。

性能测试数据

假设 orders 表有1000万条记录，进行性能测试：

Offset	执行时间	扫描行数	返回行数
0	0.01s	10	10
1000	0.05s	1010	10
10000	0.3s	10010	10
100000	2.5s	100010	10
1000000	25s	1000010	10
5000000	120s	5000010	10

结论：执行时间与offset呈线性关系，offset越大，性能越差。在高并发场景中不可接受。

EXPLAIN 分析

使用EXPLAIN可以更清晰地查看MySQL的执行计划：

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

输出示例：

+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------+| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------+|  1 | SIMPLE      | orders | NULL       | index | NULL          | PRIMARY | 4       | NULL | 1000010 |   100.00 | NULL  |+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------+

关键观察：

• type: index — 虽然使用了索引扫描，但实际上是全量扫描。
• rows: 1000010 — 预计扫描1000010行，与实际一致。
• 即使有索引，仍需扫描大量数据，这是问题的核心。

优化方案

了解根源后，解决方案可以有针对性地展开。以下六种优化方案覆盖了从简单到复杂的各种场景。

方案一：子查询优化（ID 范围查询）

原理

先获取第1000000条记录的ID，然后让数据库从该ID之后开始取数据，避免从头扫描到第1000000条再丢弃前面的。

实现

-- 原始查询（慢）SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;-- 优化后（快）SELECT * FROM orders WHERE id > (    SELECT id FROM orders ORDER BY id LIMIT 1000000, 1) ORDER BY id LIMIT 10;

更简洁的写法：

-- 先获取上一页最后一条记录的 IDSELECT id FROM orders ORDER BY id LIMIT 1000000, 1;-- 假设返回: 1000001-- 使用 ID 范围查询SELECT * FROM orders WHERE id > 1000001 ORDER BY id LIMIT 10;

性能提升

场景	原始查询	优化后查询	性能提升
Offset: 1000000	25s	0.05s	500x
Offset: 5000000	120s	0.08s	1500x

适用场景

• 主键是连续的自增ID
• 按主键排序
• 不需要跳页查询

局限性

• 不支持跳页（如直接跳到第500页）
• 主键不连续时需要额外处理

方案二：延迟关联（Deferred Join）

原理

延迟关联的核心思想是：先取ID，再拿数据。类似于去图书馆借书，先查索引找到书的位置（ID），然后去书架取书（完整数据），而不是直接搬下书架。

实现

-- 原始查询（慢）SELECT * FROM orders WHERE status = 'completed' ORDER BY create_time LIMIT 1000000, 10;-- 优化后（快）SELECT o.* FROM orders oINNER JOIN (    SELECT id FROM orders     WHERE status = 'completed'     ORDER BY create_time     LIMIT 1000000, 10) AS tmp ON o.id = tmp.id;

为什么有效

1. 子查询只查询ID列，数据量小，索引可以轻松处理
2. 避免读取完整行数据到内存，降低内存压力
3. 减少磁盘I/O操作，这是最昂贵的操作

性能对比

-- 测试表结构CREATE TABLE orders (    id BIGINT PRIMARY KEY AUTO_INCREMENT,    user_id INT NOT NULL,    status VARCHAR(20),    create_time DATETIME,    -- 其他字段...    INDEX idx_status_create_time (status, create_time));-- 数据量: 1000万条-- 查询: status='completed' 的记录约 500万条

查询方式	执行时间	扫描行数	读取数据量
原始查询	18s	1000010	完整行数据
延迟关联	2.5s	1000010	仅 ID + 10 行完整数据

方案三：游标分页（Cursor-based Pagination）

原理

游标分页的思路是：记住上一页最后一条记录的位置，下一页从该位置之后开始取。类似于看书，读到哪一页、哪一行，下次从那里继续。

实现

-- 第一页SELECT * FROM orders ORDER BY id LIMIT 10;-- 假设返回最后一条记录的 id = 10-- 下一页（使用游标）SELECT * FROM orders WHERE id > 10 ORDER BY id LIMIT 10;-- 再下一页SELECT * FROM orders WHERE id > 20 ORDER BY id LIMIT 10;

复杂排序场景

如果排序条件是多个字段，可以组合游标：

-- 按多个字段排序SELECT * FROM orders ORDER BY create_time DESC, id DESC LIMIT 10;-- 下一页SELECT * FROM orders WHERE (create_time, id) < ('2024-01-01 12:00:00', 10000)ORDER BY create_time DESC, id DESC LIMIT 10;

API 设计示例

// 请求GET /api/orderslimit=10&cursor=eyJpZCI6MTAwMDAsImNyZWF0ZV90aW1lIjoiMjAyNC0wMS0wMSAxMjowMDowMCJ9// 响应{  "data": [...],  "pagination": {    "next_cursor": "eyJpZCI6MTAwMTAsImNyZWF0ZV90aW1lIjoiMjAyNC0wMS0wMSAxMjowMTowMCJ9",    "has_more": true  }}

优缺点

优点	缺点
性能稳定，不受 offset 影响	不支持跳页
适合无限滚动场景	需要客户端保存游标
减少数据库压力	实现相对复杂

方案四：覆盖索引优化

原理

覆盖索引指查询所需的所有字段都包含在索引中，查询时不需要回表获取完整行数据，直接读取索引即可，速度快得多。

实现

-- 原始查询SELECT id, user_id, status FROM orders ORDER BY create_time LIMIT 1000000, 10;-- 创建覆盖索引CREATE INDEX idx_covering ON orders(create_time, id, user_id, status);-- 优化后的查询可以直接从索引获取所有字段

EXPLAIN 对比

-- 优化前EXPLAIN SELECT id, user_id, status FROM orders ORDER BY create_time LIMIT 1000000, 10;-- Extra: Using filesort-- 优化后（有覆盖索引）EXPLAIN SELECT id, user_id, status FROM orders ORDER BY create_time LIMIT 1000000, 10;-- Extra: Using index

区别在于“Using index”意味着MySQL只需要扫描索引即可完成查询，不需要碰表数据。

适用场景

• 查询字段较少且固定
• 可以接受额外索引存储空间
• 查询模式相对稳定

方案五：预计算/缓存

原理

每次去数据库拉数据太慢，因此可以提前计算好分页数据并存储。Redis、MySQL缓存表均可实现。

实现

-- 创建分页缓存表CREATE TABLE orders_page_cache (    page_num INT PRIMARY KEY,    start_id BIGINT,    end_id BIGINT,    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);-- 定期更新缓存INSERT INTO orders_page_cache (page_num, start_id, end_id)SELECT     FLOOR((rn - 1) / 10) + 1 AS page_num,    MIN(id) AS start_id,    MAX(id) AS end_idFROM (    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn    FROM orders) AS numberedGROUP BY page_numON DUPLICATE KEY UPDATE     start_id = VALUES(start_id),    end_id = VALUES(end_id);-- 查询时使用缓存SELECT o.*FROM orders oINNER JOIN orders_page_cache c ON o.id >= c.start_id AND o.id <= c.end_idWHERE c.page_num = 100000ORDER BY o.idLIMIT 10;

Redis 缓存实现

import redisimport jsonr = redis.Redis(host='localhost', port=6379, db=0)def get_page_data(page_num, page_size=10):    cache_key = f"orders:page:{page_num}:{page_size}"    cached = r.get(cache_key)        if cached:        return json.loads(cached)        # 查询数据库    offset = (page_num - 1) * page_size    data = db.query(        "SELECT * FROM orders ORDER BY id LIMIT %s, %s",        (offset, page_size)    )        # 缓存结果，设置过期时间    r.setex(cache_key, 3600, json.dumps(data))        return data

方案六：搜索引擎替代

原理

当数据量达到亿级，MySQL即使使用各种优化也难以应对。此时需要专业搜索引擎，如Elasticsearch，它专为全文本搜索和大数据量分页设计。

实现示例

// Elasticsearch 查询GET /orders/_search{  "from": 1000000,  "size": 10,  "sort": [    { "create_time": "desc" },    { "_id": "desc" }  ]}// 使用 search_after 进行深度分页GET /orders/_search{  "size": 10,  "sort": [    { "create_time": "desc" },    { "_id": "desc" }  ],  "search_after": [    "2024-01-01T12:00:00",    "10000"  ]}

适用场景

• 数据量超过亿级
• 需要复杂搜索条件
• 对实时性要求不高

最佳实践

1. 根据场景选择方案

场景	推荐方案
小数据量（<10万）	原始 LIMIT 即可
中等数据量（10万-1000万）	延迟关联、子查询优化
大数据量（>1000万）	游标分页、搜索引擎
需要跳页	子查询优化 + 缓存
无限滚动	游标分页

2. 索引优化建议

-- 确保排序字段有索引CREATE INDEX idx_sort_column ON table_name(sort_column);-- 复合索引注意顺序CREATE INDEX idx_status_time ON orders(status, create_time);-- 覆盖索引优化CREATE INDEX idx_covering ON orders(create_time, id, user_id, status);

3. 查询优化技巧

-- 避免 SELECT *SELECT id, user_id, status FROM orders LIMIT 1000000, 10;-- 使用 FORCE INDEX 提示SELECT * FROM orders FORCE INDEX (PRIMARY) LIMIT 1000000, 10;-- 限制最大 offset-- 在应用层控制，如不允许查询超过 10000 页

4. 应用层优化

# Python 示例：限制最大分页MAX_OFFSET = 100000def get_orders(page=1, page_size=10):    offset = (page - 1) * page_size        if offset > MAX_OFFSET:        raise ValueError("分页超出限制")        # 使用游标分页    if page > 1:        last_id = get_last_id_of_page(page - 1)        query = "SELECT * FROM orders WHERE id > %s ORDER BY id LIMIT %s"        return db.query(query, (last_id, page_size))    else:        query = "SELECT * FROM orders ORDER BY id LIMIT %s"        return db.query(query, (page_size,))

5. 监控与告警

-- 慢查询监控SET GLOBAL slow_query_log = 'ON';SET GLOBAL long_query_time = 1;-- 分析慢查询-- 使用 pt-query-digest 或 MySQL 慢查询日志分析工具

性能对比

综合性能测试

测试环境：

• MySQL 8.0
• 表数据量：1000 万条
• 测试查询：SELECT * FROM orders ORDER BY id LIMIT offset, 10

Offset	原始 LIMIT	子查询优化	延迟关联	游标分页
0	0.01s	0.01s	0.01s	0.01s
1,000	0.05s	0.02s	0.03s	0.02s
10,000	0.3s	0.03s	0.05s	0.02s
100,000	2.5s	0.04s	0.3s	0.02s
1,000,000	25s	0.05s	2.5s	0.02s
5,000,000	120s	0.08s	12s	0.02s

方案选择决策树

是否需要支持跳页？├─ 是 → 是否数据量 > 1000万？│       ├─ 是 → 考虑搜索引擎│       └─ 否 → 子查询优化 + 缓存└─ 否 → 是否是无限滚动场景？        ├─ 是 → 游标分页        └─ 否 → 延迟关联

总结

MySQL Limit深度分页性能问题的本质在于：offset越大，需要扫描和丢弃的数据越多，而真正需要的数据却极少。针对不同场景，本文总结了六种优化方案：

1. 子查询优化：适合主键连续的场景，性能提升显著
2. 延迟关联：通用性强，适合各种查询条件
3. 游标分页：性能最优，但不支持跳页
4. 覆盖索引：减少回表，适合固定查询模式
5. 预计算/缓存：适合查询模式固定的场景
6. 搜索引擎：适合超大数据量和复杂搜索

在实际应用中，选择方案没有银弹，关键是根据业务场景、数据量大小、查询模式和性能要求做出决策。结合索引优化、查询优化和缓存策略，才能构建真正高性能的分页系统。