mysql临时表转磁盘的判断依据是tmp_table_size与max_heap_table_size中较小值被耗尽，哪怕超出1字节即落盘；常见现象包括show processlist显示creating tmp table等、慢查日志rows_examined远大于rows_sent、explain出现using temporary且未走索引。
临时表写入磁盘的判断依据
MySQL 一旦把 tmp_table_size 或 max_heap_table_size 中较小的那个值用完，就会把内存临时表转成磁盘临时表（MyISAM 或 InnoDB，取决于 internal_tmp_disk_storage_engine）。这不是“慢了才换”，而是“满了就换”——哪怕只超 1 字节，也会落盘。
常见错误现象：SHOW PROCESSLIST 里看到状态是 Creating tmp table 或 Copying to tmp table on disk；慢查日志里 Rows_examined 远大于 Rows_sent；EXPLAIN 显示 Using temporary 且没走索引。
- 检查当前会话实际限制：
SELECT @@tmp_table_size, @@max_heap_table_size; - 临时表是否用到索引？
GROUP BY/DISTINCT字段必须有联合前缀索引，否则即使有索引也无效 -
ORDER BY和GROUP BY字段不一致时，大概率触发磁盘临时表（例如GROUP BY a,b却ORDER BY c）
拆分 SQL 时避免跨分片关联失效
直接把一个含多表 JOIN + GROUP BY + ORDER BY 的大查询拆成多个小查询，容易漏掉聚合逻辑或排序边界。比如原语句按用户分组取最近 3 条订单，拆开后变成“先取所有用户 ID，再对每个 ID 查 3 条”，这在并发或数据变动时结果不一致。
真正可落地的拆分方式是：把“能提前过滤、能局部聚合”的部分下沉到子查询或 CTE，让主查询只处理最终维度。
- 优先用
WHERE把数据量压到万级以内再JOIN，别依赖LIMIT在最后截断 - 用
WITH子句预聚合：比如先算出每个user_id的订单总数和最新时间，再和用户表JOIN，比直接JOIN订单表后GROUP BY节省 90% 临时表空间 - 避免在子查询里用
ORDER BY ... LIMIT后再JOIN，MySQL 8.0 前不支持子查询中带LIMIT的物化优化
改用 MEMORY 引擎的隐藏代价
有人看到“临时表太大”第一反应是调高 tmp_table_size 并强制用 MEMORY 引擎，但 MEMORY 不支持 TEXT/BLOB 字段，也不支持 LIKE '%xxx' 这类非前缀匹配的索引查找，更关键的是：它用的是固定长度行存储，VARCHAR(255) 存一个字符也占 255 字节。
典型翻车场景：字段含 JSON 或长文本摘要，或者 GROUP BY 字段是 VARCHAR(1024) —— 内存立刻爆满，直接退回到磁盘临时表，还多了一次序列化开销。
- 确认字段类型：
DESCRIBE或SHOW CREATE TABLE看临时表涉及字段是否含TEXT、BLOB、JSON - 用
CHAR替代VARCHAR仅当长度高度稳定，否则反而浪费内存 -
MEMORY表的哈希索引不支持范围查询，ORDER BY仍需文件排序（Using filesort）
临时表引擎切换的实际影响
MySQL 5.7 默认磁盘临时表是 MyISAM，8.0+ 改为 InnoDB，但这个切换不是无感的。InnoDB 临时表要写 redo log、做 MVCC 快照、加行锁，单条插入比 MyISAM 慢 3–5 倍；而 MyISAM 在崩溃后无法恢复临时表内容，可能造成查询中断失败。
如果你的应用允许“查不准但快”，且临时表生命周期短、不涉及事务一致性，可以显式指定：SET SESSION internal_tmp_disk_storage_engine=MyISAM;，但得确保 skip_innodb 没开启，否则会静默失败。
- 修改后验证是否生效：
SELECT @@internal_tmp_disk_storage_engine; - MyISAM 临时表不支持
ALTER TABLE，所以任何需要后期加索引的场景都得在建表时一次性定义好 - InnoDB 临时表的
innodb_buffer_pool_size会影响其性能，但不会单独分配内存池，它共享主 buffer pool
