在MySQL中，ORDER BY与LIMIT经常同时出现在列表查询和分页接口里。当数据量增长后，这类语句容易成为性能瓶颈。理解优化器如何处理排序与分页，并针对性地设计索引，是提升响应速度的关键。
为什么ORDER BY和LIMIT查询会变慢
在没有合适索引时，MySQL需要先读取满足条件的行，再在内存或磁盘中进行排序，最后取前N行返回。如果LIMIT偏移量很大，比如LIMIT 100000, 20，优化器仍然要先查出100020行，排序后丢掉前100000行，只返回20行。这种写法让数据库做了大量无用功。
通过EXPLAIN可以看到，Extra列出现Using filesort就意味着排序没有用到索引。filesort并不一定是磁盘排序，但至少说明MySQL无法按索引顺序直接读取，需要额外排序步骤。随着偏移量增大，排序成本线性上升，查询延迟越来越明显。
利用索引消除排序与偏移开销
如果ORDER BY的列和WHERE中的过滤列能够组成联合索引，并且顺序合理，MySQL就可以沿着索引叶子节点顺序读取，不再需要filesort。对于常见的按创建时间倒序分页的场景，可以建立(user_id, created_at)这样的联合索引，让过滤和排序都命中索引。
以下示例展示了一个典型的慢查询以及对应的索引创建方式：
-- 原始慢查询：深翻页导致filesort SELECT id, title, created_at FROM articles WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 100000, 20; -- 建立联合索引，覆盖过滤与排序 CREATE INDEX idx_user_created ON articles(user_id, created_at);
建立索引后，MySQL可以基于user_id定位到索引区间，并按created_at逆序遍历，直接跳过前100000个索引项，读取后续20条主键再回表。虽然跳过动作仍会发生，但避免了全量排序，性能通常提升一个数量级。
用游标分页替代OFFSET分页
更彻底的方案是放弃LIMIT offset, size，改用基于上一页最后一条记录值的游标分页。这种方式让查询条件直接携带排序边界，优化器可以精确定位起始位置，不再扫描被丢弃的行。
示例代码如下，假设按created_at倒序、id倒序作为稳定排序：
-- 第一页
SELECT id, title, created_at
FROM articles
WHERE user_id = 1001
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 下一页：last_created_at和last_id是上一页最后一行的值
SELECT id, title, created_at
FROM articles
WHERE user_id = 1001
AND (created_at < '2023-08-01 10:00:00'
OR (created_at = '2023-08-01 10:00:00' AND id < 55231))
ORDER BY created_at DESC, id DESC
LIMIT 20;
这种写法要求联合索引包含(user_id, created_at, id)，使WHERE中的范围条件和排序键完全由索引提供。由于每一次查询都从明确边界开始，扫描行数恒定在LIMIT大小附近，不会随页码加深而恶化。
验证索引是否被正确使用
写完查询和索引后，必须用EXPLAIN确认执行计划。重点观察type列是否为range或ref，key列是否显示期望的索引，Extra列是否不再有Using filesort。如果仍出现filesort，可能是索引顺序不对、字符集不匹配或查询中使用了函数导致索引失效。
还可以对比不同写法的Rows列估算值。游标分页的预估扫描行数应明显小于OFFSET写法，尤其在深翻页时差距巨大。结合慢查询日志和性能压测，就能确认索引优化是否真正生效。
| 分页方式 | 索引要求 | 深翻页扫描行数 | 稳定性 |
|---|---|---|---|
| OFFSET分页 | 过滤+排序列联合索引 | 随偏移量增长 | 一般 |
| 游标分页 | 过滤+排序+唯一列联合索引 | 约等于页大小 | 高 |
常见误区与注意事项
有人误以为只要ORDER BY的列有单列索引就足够，实际上如果WHERE条件未纳入索引前缀，MySQL往往仍要回表后再排序。联合索引必须遵循最左前缀原则，把等值过滤列放在前面，范围或排序列放在后面。
另外，当排序方向混合时，比如ORDER BY a ASC, b DESC，MySQL在旧版本无法用单一索引同时正向和逆向遍历，可能需要改写业务排序或升级到支持降序索引的版本。理清这些边界情况，才能让ORDER BY和LIMIT查询稳定高效。
mysqlindex_optimizationorder_by_limit修改时间：2026-07-31 21:00:26