能否强制MySQL使用磁盘内部表？内存不足时的优化方案咨询

嘿，这两个问题问到点子上了——很多时候提前预判临时表大小，能避免不必要的内存转磁盘的开销，我来给你拆解清楚：

问题1：能否强制MySQL使用磁盘内部表？ #

完全可以！MySQL提供了多种机制，能绕过内存临时表的创建逻辑，直接使用磁盘存储的内部临时表，不用等内存不够了再触发转换。

问题2：提前知晓内部表放不下内存，怎么直接用磁盘表？临时设tmp_table_size=0可行吗？有更优方案吗？ #

咱们逐个说清楚：

关于tmp_table_size = 0的可行性 #

这个方法是有效的，但要注意细节：

• 你需要同时设置tmp_table_size = 0和max_heap_table_size = 0（因为MySQL会取这两个参数的较小值作为内存临时表的上限）。当两个值都为0时，MySQL会直接跳过内存临时表的创建，直接使用磁盘临时表。
• 建议只在当前会话临时设置，不要全局修改，避免影响其他不需要强制磁盘表的查询：

  SET SESSION tmp_table_size = 0;
  SET SESSION max_heap_table_size = 0;
  

  执行完目标查询后，还可以把参数改回原有值，不影响后续操作。

更优的精准控制方案 #

比起会话级修改参数，更推荐针对特定查询使用查询提示，这样只会影响目标查询，灵活性更高：

• 使用SQL_BIG_RESULT提示：当你明确知道某个查询会生成大临时表（比如涉及大量分组、排序，或是处理超大数据集），直接在查询语句中加上这个提示，MySQL会预判需要磁盘临时表，直接跳过内存表阶段：

  SELECT SQL_BIG_RESULT user_id, COUNT(*) 
  FROM large_log_table 
  GROUP BY user_id 
  ORDER BY COUNT(*) DESC;
  

• 如果你使用的是MySQL 8.0+版本，确保internal_tmp_disk_storage_engine参数设置为InnoDB（默认就是这个值），InnoDB的磁盘临时表性能比传统的MyISAM临时表要好得多，还支持事务隔离，稳定性更高。

额外提醒 #

要注意区分「内部临时表」和用户手动创建的临时表：我们这里说的是MySQL在执行查询过程中自动生成的内部临时表，针对这类表的强制磁盘存储，上面的方法完全适用。

内容的提问来源于stack exchange，提问作者guigoz