MySQL 深分页查询优化实践与经验总结
在企业级项目中，深分页查询经常会成为性能瓶颈。本篇文章总结了我在实践中优化深分页 SQL 的经验，包括执行计划分析、索引优化、游标分页改写等内容。
一、问题场景
假设我们有一张订单表orders，包含字段：
id, user_id, status, total_amount, create_time原始查询为：
SELECT id, user_id, status, total_amount, create_time FROM orders WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 990, 10;业务背景：查询某用户最近的订单，且需要支持分页。
数据量假设：企业级通常几十万到几百万条订单。
二、原始 SQL 执行计划分析
使用 EXPLAIN查看原始 SQL：
| id | select_type | table | type | possible_keys | key | key_len | rows | Extra |
|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | ref | user_id | user_id | 4 | 50 | Using filesort |
分析：
type=ref→ 使用了
user_id索引进行精确匹配。key=user_id→ 索引选择正确。
Extra=Using filesort→ ORDER BY create_time DESC 未覆盖索引，需要额外排序。
扫描行数→ MySQL 会扫描前 990 条行再丢弃（LIMIT 偏移量大），深分页效率低。
✅ 结论：单列索引只能加速 WHERE 条件，排序仍需额外操作。
三、复合索引优化
为了提升查询效率，我们创建复合索引：
ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time DESC);作用：
覆盖
WHERE user_id=…条件。覆盖
ORDER BY create_time DESC条件。
优化后 EXPLAIN：
| id | select_type | table | type | possible_keys | key | key_len | rows | Extra |
|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | ref | idx_user_create | idx_user_create | 8 | 20 | Using index condition |
分析：
type=ref → ref→ 使用索引范围扫描，避免全表扫描。
key_len=8→ 复合索引长度增加。
Extra=Using index condition→ ICP (Index Condition Pushdown) 优化回表行数。
✅ 结论：复合索引同时覆盖 WHERE + ORDER BY，大幅减少扫描行数和排序成本。
四、游标分页改写
深分页仍有偏移量大的问题，可以改写为游标分页：
SELECT id, user_id, status, total_amount, create_time FROM orders WHERE user_id = 12345 AND create_time < '2024-06-01 12:00:00' ORDER BY create_time DESC LIMIT 10;优势：
不用 OFFSET，避免扫描前面大量行。
对大数据量分页性能稳定。
EXPLAIN 输出：
| id | select_type | table | type | possible_keys | key | key_len | rows | Extra |
|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | range | idx_user_create | idx_user_create | 8 | 10 | Using index condition |
✅ 结论：扫描行数恒定，排序在索引中完成，性能最佳。
五、实践总结
深分页性能问题：
OFFSET 大时，MySQL 会扫描并丢弃大量行。
ORDER BY 未覆盖索引 →
filesort。
优化策略：
复合索引覆盖 WHERE + ORDER BY。
游标分页替代大 OFFSET 分页。
EXPLAIN 解析技巧：
type→ 尽量是
ref、range或const，避免ALL。key / key_len→ 关注索引是否被正确使用。
Extra→ 理解
Using index condition和Using filesort。
经验总结：
使用 ICP 能减少回表行数。
游标分页适合大数据量分页查询。
SQL 优化不仅是索引，改写查询逻辑同样重要。