MySQL 派生表物化后怎么判断临时表是否溢出磁盘

MySQL 派生表物化后是否“溢出磁盘”，不能只看一项计数。先用 EXPLAIN 确认目标子查询真的走了物化路径，再把 tmp_table_size、temptable_max_ram、Created_tmp_disk_tables 和临时表空间放在同一条证据链里。尤其是 MySQL 8.x 的 TempTable 可能先使用内存映射文件，状态变量未必把这一步直接算进磁盘临时表。

先用 EXPLAIN 确认派生表确实进入物化路径

先不要急着调大内存参数。对包含 FROM 子查询的 SQL 执行 EXPLAIN FORMAT=TRADITIONAL 或 JSON 计划，确认计划中出现派生表相关节点。派生表可能被合并进外层查询，也可能被物化；如果根本没有物化，后面的临时表计数就不能简单归到它身上。

-- 只查看计划，不执行这条查询
EXPLAIN FORMAT=TRADITIONAL
SELECT d.customer_id, COUNT(*) AS item_count
FROM (
    -- 保留必要列，避免物化结果过宽
    SELECT customer_id, product_id
    FROM order_items
    WHERE created_at >= '2026-09-01'
) AS d
GROUP BY d.customer_id;

计划确认后，再记录派生表输出列数、过滤条件和聚合方式。一个包含宽字符串、重复行或大范围排序的派生结果，更容易触碰单个内存临时表的限制。

对照内存临时表阈值解释为什么会转盘

MySQL 8.4 默认以内存 TempTable 处理内部临时表。tmp_table_size限制单个内部内存临时表；temptable_max_ram控制 TempTable 可占用的全局内存；temptable_max_mmap和 temptable_use_mmap决定超过内存后是否经过内存映射文件。使用 MEMORY 引擎时，还要考虑 max_heap_table_size 与 tmp_table_size 中较小者。

-- 读取当前实例与会话的临时表相关边界
SHOW VARIABLES WHERE Variable_name IN (
    'internal_tmp_mem_storage_engine',
    'tmp_table_size',
    'max_heap_table_size',
    'temptable_max_ram',
    'temptable_max_mmap',
    'temptable_use_mmap',
    'tmpdir'
);

因此，“超过 tmp_table_size”和“已经占用可见磁盘文件”不是同一个判断。TempTable 的临时文件可能在创建后立即被打开并删除目录项，空间仍由操作系统占用；而 InnoDB on-disk internal temporary table 则会落到会话临时表空间。

用状态计数和 Performance Schema 做前后取样

最实用的排查方式是对同一连接做前后取样，不要拿服务器启动以来的累计值直接下结论。执行目标语句前后分别记录计数差值：

-- 先保存当前会话的累计值，避免把历史流量混进来
SHOW SESSION STATUS LIKE 'Created_tmp%';

-- 在两次采样之间执行目标查询

-- 再次采样，用前后差值判断本次查询的影响
SHOW SESSION STATUS LIKE 'Created_tmp%';

Created_tmp_tables增加，说明创建了内部临时表；Created_tmp_disk_tables增加，说明创建了内部磁盘临时表。但官方也说明，TempTable 使用内存映射文件时，这项磁盘计数存在覆盖不到的情况。可以再看 Performance Schema 的 TempTable 内存摘要：

-- 只读查看 TempTable 的内存与磁盘分配摘要
SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME IN (
    'memory/temptable/physical_ram',
    'memory/temptable/physical_disk'
);

如果 physical_disk出现非零值，说明 TempTable 曾经触及内存限制并使用了磁盘分配路径；如果两个状态变量都没有明显变化，则应回到执行计划检查目标派生表是否被合并，或确认查询是否被其他连接、其他语句干扰。

检查 InnoDB 临时表空间并选择修复方向

当确认磁盘路径被使用后，继续检查 InnoDB 临时表空间和目录容量。下面的查询用于查看全局临时表空间元数据：

-- 查看 InnoDB 临时表空间文件的大小与上限
SELECT FILE_NAME, TABLESPACE_NAME, ENGINE,
       TOTAL_EXTENTS * EXTENT_SIZE AS total_size_bytes,
       DATA_FREE, MAXIMUM_SIZE
FROM INFORMATION_SCHEMA.FILES
WHERE TABLESPACE_NAME = 'innodb_temporary';

-- 查看全局临时表空间的自动扩展策略
SELECT @@innodb_temp_data_file_path;

修复顺序建议是：先缩小派生表输出列、提前过滤、补齐能减少排序或回表的索引；仍然需要大中间结果时，再根据并发量评估 tmp_table_size 和 temptable_max_ram。最后给临时表空间设置可接受的上限并监控目录容量。直接把阈值调到很大，可能把单条查询的磁盘问题换成并发内存压力。

常见问题

Created_tmp_disk_tables 没增加，能断定没有落盘吗？

不能。TempTable 通过内存映射文件溢出时，官方文档明确指出该计数可能不覆盖它。应结合 memory/temptable/physical_disk 和临时表空间信息判断。

看到 DERIVED 就说明临时表已经写满磁盘了吗？

不是。DERIVED 说明计划里有派生表节点，是否物化、使用哪种存储路径和是否触碰阈值，要靠执行过程中的指标确认。

应该先调大 tmp_table_size 还是先改 SQL？

先改写和缩小中间结果通常更稳妥。只有在确认查询结构合理、并发内存预算充足时，才针对阈值做小范围调整。