mysql8.0怎么优化临时表存储_对比Memory引擎与TempTable引擎

MySQL 8.0 临时表存储优化：从 Memory 到 TempTable 的引擎变迁

MySQL 8.0 临时表默认用的是 TempTable，不是 Memory

从 MySQL 8.0.16 版本开始，一个容易被忽视但影响深远的变化发生了：internal_tmp_mem_storage_engine 参数的默认值，从我们熟悉的 MEMORY 切换成了 TEMPTABLE。这意味着什么？简单说，绝大多数由系统自动创建的隐式临时表——比如处理 GROUP BY、DISTINCT、UNION 或者窗口函数时产生的中间结果——现在默认都会使用 TEMPTABLE 引擎，而不再是老版本里的 MEMORY。这个默认值的切换，直接关系到数据库的内存消耗模式、磁盘回退机制以及排序操作的性能表现。

长期稳定更新的攒劲资源： >>>点此立即查看<<<

想知道你的数据库当前是什么设置？一条命令就能确认：
SELECT @@internal_tmp_mem_storage_engine;

这里有个常见的理解误区：很多朋友看到配置文件中还有 tmp_table_size 和 max_heap_table_size 这两个参数，就以为它们依然在主导临时表的内存行为。实际上，对于 TEMPTABLE 引擎来说，这两个参数的作用已经发生了变化——它们仅间接影响其内部内存池的大小上限。真正决定临时表数据是否会写入磁盘的那个“开关”，是另一个参数：temptable_max_ram。

Memory 引擎临时表的典型问题与适用场景

当然，MEMORY 引擎在 MySQL 8.0 中并没有消失，它依然存在，主要用在显式创建的临时表（比如执行 CREATE TEMPORARY TABLE ... ENGINE=MEMORY）或者某些特定的优化路径中。不过，它的几个“老毛病”也依然很实在：

• 不支持 BLOB/TEXT 类型：一旦临时表需要处理这类大字段，直接就会报错：ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns。
• 容量硬限制，不会自动转存磁盘：所有数据必须能放进单个内存页（受 max_heap_table_size 限制）。一旦超出，立刻报 ERROR 1114 (HY000): The table is full，没有商量的余地。
• 存储效率低：它不支持行级压缩，对变长字段的存储也不高效。举个例子，一个 VARCHAR(255) 的字段，即使你只存了一个字母 “a”，它也会占用 255 字节的内存，浪费现象比较严重。
• 并发瓶颈：缺乏针对高并发写入的优化，在频繁操作临时表的场景下，容易成为锁竞争的焦点。

那么，MEMORY 引擎就一无是处了吗？并非如此。它的优势在于确定性的快。如果你能严格把控临时表的字段类型（只用定长类型）、数据长度和总行数，并且确认不涉及任何大字段，那么在小规模、结构简单、确保纯内存操作的场景下，MEMORY 引擎依然能提供可预测的极低延迟。

TempTable 引擎的关键配置与调优点

而 TEMPTABLE，作为 MySQL 8.0 自研的内存与磁盘混合存储引擎，生来就是为了解决上述这些“硬伤”的。它的行为由几个关键参数共同控制，理解它们的联动关系是调优的核心：

• temptable_max_ram：这是内存池的上限，默认是 1GB。当临时表所需内存超过这个值时，新分配的内存页会直接写入磁盘临时文件（存放在 /tmp 或由 tmpdir 参数指定的路径）。注意，这不是整张表一次性落盘，而是按需分页落盘，更加灵活。
• tmp_table_size 和 max_heap_table_size：这两个参数对 TEMPTABLE 不直接限制单张临时表的大小，但会影响其内部的内存分配策略。只有当这两个值中较小的那个，小于或等于 temptable_max_ram 时，TEMPTABLE 才有可能实现全程内存操作。
• temptable_use_mmap：默认是 ON（启用），表示使用 mmap 来分配大块内存，这可以减少频繁 malloc 带来的开销。但是，在容器环境或内存资源受限的机器上，建议将其设为 OFF，以避免 mmap 占用过多虚拟地址空间而引发 OOM（内存溢出）问题。
• 磁盘位置：临时文件落盘的位置由 tmpdir 控制。务必确保这个路径所在的磁盘有充足的可用空间，并且 I/O 吞吐能力要够用（固态硬盘 SSD 的性能远优于机械硬盘 HDD）。

一个配置示例如下（写入 my.cnf 文件）：

temptable_max_ram = 2G
tmpdir = /ssd/tmp
temptable_use_mmap = OFF

怎么判断临时表到底用了哪种引擎、有没有落盘

光看执行计划（EXPLAIN）输出里的 “Using temporary” 提示是不够的——这只能说明查询使用了临时表，但无法揭示用的是哪种引擎、数据是否写入了磁盘。要了解真实情况，需要探查数据库运行时的状态：

• 启用性能模式（Performance Schema）监控：首先需要启用相关的事件采集器。例如：UPDATE performance_schema.setup_instruments SET ENABLED='YES' WHERE NAME LIKE 'stage/innodb/alter%';（实际使用时，需要根据具体阶段启用对应的 instruments）。
• 分析阶段事件：查询 performance_schema.events_stages_current 或其历史表，寻找阶段名称包含 creating sort index 或 writing to tmp table 的记录。结合 WORK_COMPLETED（已完成工作量）和 WORK_ESTIMATED（预估工作量）字段，可以判断操作的数据量是否远超内存容量。
• 查看全局状态计数器：这是最直接的方法之一。执行 SHOW GLOBAL STATUS LIKE 'Created_tmp%';，重点关注两个指标：
  – Created_tmp_tables：创建的临时表总数。
  – Created_tmp_disk_tables：其中在磁盘上创建的临时表数量。
  如果后者的占比持续超过 5%，就是一个明确的信号，说明 temptable_max_ram 的设置可能偏小，或者磁盘 I/O 已经成为瓶颈。
• 观察物理临时文件：临时文件本身会在 tmpdir 指定的目录下生成，文件名格式类似 #sql___.MYD，或者使用 InnoDB 的共享临时表空间文件 ibtmp1。通过 ls -l 命令观察这些文件的增长情况，也能获得直观感受。

实际情况可能会更复杂一些：同一个查询有可能混合使用两种引擎。例如，一个子查询的结果集使用了 TEMPTABLE，而外层的 JOIN 操作又触发了一个显式的 MEMORY 临时表。在调试这类复杂场景时，最好结合 EXPLAIN FORMAT=TREE 提供的更详细的执行树信息，和上述的状态计数器进行交叉验证，才能得出准确的结论。