MySQL临时表与文件排序

概要

  • Using temporary：当查询需要中间结果集无法在内存中完成时（如 GROUP BY + ORDER BY 不同列），MySQL 创建临时表（Server 层自动创建，用户不可见）。

    • 优先在内存（MEMORY 引擎），超过 tmp_table_size / max_heap_table_size（默认 16MB）后转磁盘。
    • 磁盘临时表默认使用 InnoDB（MySQL 5.7.5+，由 internal_tmp_disk_storage_engine 控制；5.6 时代是 MyISAM）。
    • 中间结果含 TEXT/BLOB 列时，直接落盘（MEMORY 引擎不支持 TEXT）。
    • 磁盘临时表极慢，是性能杀手。

  • Using filesort：当 ORDER BY 不能利用索引顺序时，MySQL 需要单独排序。

    • sort_buffer_size 不够 → 使用磁盘文件分段排序 → 慢。
    • 优化方向：让 ORDER BY 列匹配索引顺序。

案例

统计一下用户来自哪些城市，哪个城市的用户最多，哪个最少。

-- users表，先按照city分组进行count，然后按照count统计结果倒序排序
SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city
ORDER BY cnt DESC;

EXPLAIN SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city
ORDER BY cnt DESC;

Extra ： Using index; Using temporary; Using filesort

Using index 表示索引覆盖了，没回表
Using temporary; Using filesort 表示用了临时表，并且还是磁盘临时表

临时表

一、临时表是 MySQL 自己创建的

完全自动、对用户不可见。Server 层在执行 SQL 的过程中发现"中间结果没地方放了"，就自己建一张临时表，查询结束后自动销毁。你永远看不到它，只能在 EXPLAIN 的 Using temporary 里察觉到它存在。

二、磁盘临时表的引擎，临时表的创建流程

引擎：
SHOW VARIABLES LIKE 'internal_tmp_disk_storage_engine';
-- 5.7 返回: InnoDB

准确的流程是：

需要临时表时：
① 先尝试用 MEMORY 引擎建在内存里（速度快）
- 大小上限：min(tmp_table_size, max_heap_table_size)，默认 16MB
② 满足以下任一条件 → 转磁盘临时表（InnoDB）：
- 中间结果超过了 16MB
- 中间结果包含 TEXT/BLOB 列（MEMORY 引擎不支持）

三、什么时候会触发创建临时表

问题：分组结果流出来时是 city 顺序，要按 cnt 输出，
而 cnt 是"聚合之后才算出来"的值，索引里根本没有
→ 流式输出做不到了，必须把整个分组结果先存下来再排序
→ 这个"先存下来"的容器就是临时表

常见的触发 Using temporary 的情况：


┌───────────────────────────────┬────────────────────────────────────┐
│             场景              │          为什么需要临时表             │
├───────────────────────────────┼────────────────────────────────────┤
│ GROUP BY 列和 ORDER BY 列不同  │ 分组按 A 流出来，却要按 B 输出          │
├───────────────────────────────┼────────────────────────────────────┤
│ DISTINCT 列无索引              │ 去重要记录"见过哪些值"，得有个容器       │
├───────────────────────────────┼────────────────────────────────────┤
│ UNION（非 UNION ALL）         │ 合并后还要去重                        │
├───────────────────────────────┼────────────────────────────────────┤
│ 派生表（FROM 里的子查询）       │ 子查询结果要物化成一张"表"给外层用       │
└───────────────────────────────┴────────────────────────────────────┘

注意：不是所有 GROUP BY 都产生临时表。如果 GROUP BY city 能走 idx_city 的索引顺序，MySQL 可以边扫边分组流式输出（Loose Index Scan），无需临时表。只有当"分组/去重后的结果还要再做一次操作"时，才被迫物化。

文件排序

四、关于排序

order by 字段不是索引字段么，或者索引失效的时候，
优先会在sort_buffer_size 内存里，由 Server 用内存引擎的排序算法做排序，如果内存不够，走文件分段排序吗？

  1. 不一定是"字段没索引"。 更多的情况是过滤和排序用不到同一个索引：

SELECT * FROM users WHERE status = 1 ORDER BY created_at DESC;
-- created_at 有索引 idx_created_at，status 也有索引
-- 但过滤用 status、排序用 created_at，两个索引没法同时满足
-- → 无论选哪个索引，另一件事都做不了 → 触发 filesort

  1. 排序的完整过程：

① MySQL 把需要排序的行读进 sort_buffer_size（每会话一份，默认 256K）
② 数据量 ≤ sort_buffer → 直接在内存里快排/归并 → 完事（快）
③ 数据量 > sort_buffer → 落盘分段排序：
- 先把数据分成若干块，每块读进内存排好，写回磁盘临时文件
- 最后多路归并这些有序块（经典外部排序）
→ 多了磁盘读写，慢

④ 有 LIMIT 时的特例：不用排全部，用堆维护 top-N 即可（快很多）

所以优化方向就是：让 ORDER BY 列和 WHERE 条件落在同一个复合索引上，索引天生有序，连 sort_buffer 都不需要。

总结

  • 临时表：Server 自动创建、不可见；内存 MEMORY（≤16MB）→ 磁盘 InnoDB； GROUP BY/ORDER BY 不同列、无索引 DISTINCT、UNION、派生表是常见触发源
  • filesort：过滤和排序无法共用同一索引时触发；先 sort_buffer 内存排序，超了落盘分段归并；优化方向是让 WHERE + ORDER BY 落在同一复合索引上

posted on 2026-08-14 09:59  幽州散人  阅读(19)  评论(0)    收藏  举报

导航