大家好，我是BiggerBoy。在上一篇文章咱们提到如果排序字段没有索引，MySQL需将全部数据加载到内存排序，深分页场景下直接“内存爆炸”。
但如果内存不够用呢？所以今天我们一起来探究一下。
当你在MySQL中执行一个包含 ORDER BY 的查询时，如果排序字段没有索引，且数据量过大导致内存不足，MySQL确实会使用文件排序（Filesort） 。这个过程就像“临时借硬盘当内存用”，虽然能完成任务，但效率会大打折扣。下面我们通过一个实际场景，拆解它的原理和影响。
1. 一个没有索引的排序查询会经历什么？
假设有一张用户表 users（100万条数据），执行以下查询：
SELECT * FROM users ORDER BY register_time DESC LIMIT 999990, 10;
如果 register_time 没有索引，MySQL的处理流程如下：
步骤1：全表扫描，加载数据
- • MySQL需要扫描整个表，把所有满足条件的行（这里是全部100万行）的
register_time和对应的主键id加载到内存。 - • 如果内存不够，会分批次处理，但深分页场景下几乎必然触发文件排序。
步骤2：内存排序 vs 文件排序
内存排序：如果数据量小于 sort_buffer_size（默认256KB~1MB），直接在内存中排序。
文件排序：如果数据量太大，MySQL会将数据分成多个小块：
-
- 每次从磁盘读取一部分数据到内存排序；
-
- 将排序结果写入临时文件；
-
- 最终合并所有临时文件，得到完整排序结果。
步骤3：丢弃数据，返回结果
排序完成后，MySQL需要跳过前999990行，仅返回最后10行。但由于没有索引，它依然需要遍历整个排序结果。
2. 文件排序的性能有多差？
通过一个对比实验感受一下：
场景：100万行数据，register_time 无索引，执行 LIMIT 999990,10。
测试结果：
- • 内存排序（小数据量）：耗时约0.01秒；
- • 文件排序（大数据量）：耗时约15秒~30秒（取决于磁盘速度）。
性能杀手：
- 1. 磁盘I/O：频繁读写临时文件，机械硬盘尤其明显；
- 2. CPU计算：多轮排序和合并；
- 3. 数据丢弃：即使最终只返回10行，仍需处理全部数据。
3. 延迟关联在无索引时还能用吗？
如果排序字段没有索引，延迟关联的优化效果会大打折扣。比如：
SELECT * FROM users
INNER JOIN (
SELECT id FROM users
ORDER BY register_time DESC -- 无索引，触发文件排序
LIMIT 999990, 10
) AS tmp USING(id);
虽然子查询只返回10个ID，但子查询内部的 ORDER BY 仍然需要全表扫描+文件排序，性能可能比传统分页更差（因为多了一次JOIN操作）。
结论：
延迟关联生效的前提是排序字段有索引！否则，它只是“换汤不换药”。
4. 如何避免文件排序？
方案1：为排序字段加索引
-
• 如果按
register_time排序，直接创建索引：ALTER TABLE users ADD INDEX idx_register_time(register_time); -
• 如果查询中还有
WHERE条件，需创建联合索引（如(status, register_time)）。
方案2：业务妥协
- • 禁止深分页：只允许“上一页/下一页”，用游标分页（如记录最后一条的
register_time和id）。 - • 分页限制：比如最多允许翻到第100页。
方案3：预计算或缓存
- • 定时统计热门数据，将结果缓存到Redis；
- • 使用Elasticsearch等搜索引擎优化复杂查询。
5. 文件排序的“救命”配置
如果实在无法加索引，可以调整MySQL参数缓解痛苦：
-
1. 增大
sort_buffer_size：SET sort_buffer_size = 8*1024*1024; -- 8MB让更多数据在内存中排序，减少临时文件数量。
-
2. 使用SSD：提升临时文件的读写速度。
总结
- • 文件排序是深分页的“性能黑洞” ，尤其在排序字段无索引时；
- • 延迟关联不是万能药，必须结合索引才能发挥威力；
- • 终极解决方案：索引设计 + 业务妥协 + 合理分页策略。
互动讨论：
你在项目中是否遇到过文件排序导致的性能问题？是如何解决的？欢迎留言分享你的实战经验～
往期推荐：#mysql系列文章
如果对你有用还请各位点赞、转发支持一下吧！
关注【BiggerBoy】公众号，持续获取更多技术干货！
