MySQL之Limit深度分页性能问题与优化指南

MySQL之Limit深度分页性能问题与优化指南

目录

1. 问题背景
2. 性能问题分析
3. 优化方案
4. 最佳实践
5. 性能对比

---

问题背景

什么是深度分页

深度分页是指在数据量较大的情况下，查询偏移量（offset）很大的分页场景。例如：

-- 第一页：查询很快SELECT*FROMordersORDERBYidLIMIT0,10;-- 第1000页：开始变慢SELECT*FROMordersORDERBYidLIMIT9990,10;-- 第100000页：非常慢SELECT*FROMordersORDERBYidLIMIT999990,10;

典型场景

• 电商商品列表（百万级商品）
• 订单历史查询（千万级订单）
• 日志数据查询（亿级日志）
• 社交媒体动态（海量用户内容）

---

性能问题分析

MySQL Limit 执行原理

当执行SELECT * FROM table ORDER BY column LIMIT offset, limit时：

1. MySQL 根据索引找到排序后的前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)

性能测试数据

假设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 分析

EXPLAINSELECT*FROMordersORDERBYidLIMIT1000000,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 范围查询）

原理

先查询出当前页起始 ID，再通过 ID 范围查询数据。

实现

-- 原始查询（慢）SELECT*FROMordersORDERBYidLIMIT1000000,10;-- 优化后（快）SELECT*FROMordersWHEREid>(SELECTidFROMordersORDERBYidLIMIT1000000,1)ORDERBYidLIMIT10;

或者更简洁的写法：

-- 先获取上一页最后一条记录的 IDSELECTidFROMordersORDERBYidLIMIT1000000,1;-- 假设返回: 1000001-- 使用 ID 范围查询SELECT*FROMordersWHEREid>1000001ORDERBYidLIMIT10;

性能提升

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

适用场景

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

局限性

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

---

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

原理

先通过索引查询出符合条件的 ID，再根据 ID 关联查询完整数据。

实现

-- 原始查询（慢）SELECT*FROMordersWHEREstatus='completed'ORDERBYcreate_timeLIMIT1000000,10;-- 优化后（快）SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersWHEREstatus='completed'ORDERBYcreate_timeLIMIT1000000,10)AStmpONo.id=tmp.id;

为什么有效

1. 子查询只查询 ID 列，数据量小，可以在索引中完成
2. 避免了读取完整行数据到内存
3. 减少了 I/O 操作

性能对比

-- 测试表结构CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idINTNOTNULL,statusVARCHAR(20),create_timeDATETIME,-- 其他字段...INDEXidx_status_create_time(status,create_time));-- 数据量: 1000万条-- 查询: status='completed' 的记录约 500万条

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

---

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

原理

使用上一页最后一条记录的某个字段作为游标，查询大于该游标的记录。

实现

-- 第一页SELECT*FROMordersORDERBYidLIMIT10;-- 假设返回最后一条记录的 id = 10-- 下一页（使用游标）SELECT*FROMordersWHEREid>10ORDERBYidLIMIT10;-- 再下一页SELECT*FROMordersWHEREid>20ORDERBYidLIMIT10;

复杂排序场景

-- 按多个字段排序SELECT*FROMordersORDERBYcreate_timeDESC,idDESCLIMIT10;-- 下一页SELECT*FROMordersWHERE(create_time,id)<('2024-01-01 12:00:00',10000)ORDERBYcreate_timeDESC,idDESCLIMIT10;

API 设计示例

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

优缺点

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

---

方案四：覆盖索引优化

原理

创建覆盖索引，使查询可以直接从索引获取数据，无需回表。

实现

-- 原始查询SELECTid,user_id,statusFROMordersORDERBYcreate_timeLIMIT1000000,10;-- 创建覆盖索引CREATEINDEXidx_coveringONorders(create_time,id,user_id,status);-- 优化后的查询可以直接从索引获取所有字段

EXPLAIN 对比

-- 优化前EXPLAINSELECTid,user_id,statusFROMordersORDERBYcreate_timeLIMIT1000000,10;-- Extra: Using filesort-- 优化后（有覆盖索引）EXPLAINSELECTid,user_id,statusFROMordersORDERBYcreate_timeLIMIT1000000,10;-- Extra: Using index

适用场景

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

---

方案五：预计算/缓存

原理

预先计算分页数据并缓存，减少实时查询压力。

实现

-- 创建分页缓存表CREATETABLEorders_page_cache(page_numINTPRIMARYKEY,start_idBIGINT,end_idBIGINT,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);-- 定期更新缓存INSERTINTOorders_page_cache(page_num,start_id,end_id)SELECTFLOOR((rn-1)/10)+1ASpage_num,MIN(id)ASstart_id,MAX(id)ASend_idFROM(SELECTid,ROW_NUMBER()OVER(ORDERBYid)ASrnFROMorders)ASnumberedGROUPBYpage_numONDUPLICATEKEYUPDATEstart_id=VALUES(start_id),end_id=VALUES(end_id);-- 查询时使用缓存SELECTo.*FROMorders oINNERJOINorders_page_cache cONo.id>=c.start_idANDo.id<=c.end_idWHEREc.page_num=100000ORDERBYo.idLIMIT10;

Redis 缓存实现

importredisimportjson r=redis.Redis(host='localhost',port=6379,db=0)defget_page_data(page_num,page_size=10):cache_key=f"orders:page:{page_num}:{page_size}"cached=r.get(cache_key)ifcached:returnjson.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))returndata

---

方案六：搜索引擎替代

原理

对于超大数据量的分页查询，使用专门的搜索引擎（如 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. 索引优化建议

-- 确保排序字段有索引CREATEINDEXidx_sort_columnONtable_name(sort_column);-- 复合索引注意顺序CREATEINDEXidx_status_timeONorders(status,create_time);-- 覆盖索引优化CREATEINDEXidx_coveringONorders(create_time,id,user_id,status);

3. 查询优化技巧

-- 避免 SELECT *SELECTid,user_id,statusFROMordersLIMIT1000000,10;-- 使用 FORCE INDEX 提示SELECT*FROMordersFORCEINDEX(PRIMARY)LIMIT1000000,10;-- 限制最大 offset-- 在应用层控制，如不允许查询超过 10000 页

4. 应用层优化

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

5. 监控与告警

-- 慢查询监控SETGLOBALslow_query_log='ON';SETGLOBALlong_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 深度分页性能问题的核心在于 MySQL 必须扫描并读取 offset 之前的所有记录。针对不同场景，有多种优化方案：

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

在实际应用中，应根据具体场景选择合适的方案，并结合索引优化、查询优化和缓存策略，构建高性能的分页系统。