如何配置MySQL的内存临时表大小来优化复杂查询性能

MySQL创建内存临时表时，生效的是tmp_table_size与max_heap_table_size二者中的较小值；必须将两者设为相同值（如64M～256M），否则仍会因阈值过低触发磁盘落盘，导致ibtmp1或/tmp撑爆。

tmp_table_size 和 max_heap_table_size 到底谁生效？

MySQL 创建内存临时表时，tmp_table_size 和 max_heap_table_size 会同时被检查，最终取二者中**较小值**作为上限。只调大其中一个，另一个仍卡在默认 16MB，照样触发磁盘落盘。

• 常见错误现象：SHOW PROCESSLIST 中持续出现 Creating tmp table 状态，且查询变慢；SHOW STATUS LIKE 'Created_tmp_disk_tables' 数值快速上升
• 默认值太保守（通常 16MB），一个含 GROUP BY user_id, DATE(created_at) 的千万级订单聚合就容易超限
• 推荐设为相同值：64M～256M，具体看单次查询中间结果集大小和并发连接数（例如 100 个并发 × 128MB = 12.8GB 内存占用）
• 可先用会话级验证：SET SESSION tmp_table_size = 134217728; SET SESSION max_heap_table_size = 134217728;，观察 Created_tmp_disk_tables 是否下降

为什么 innodb_temp_data_file_path 要配多个固定大小文件？

默认的 ibtmp1 是单文件、自动扩展模式，高并发下极易因元数据锁争用、频繁扩展和碎片导致 I/O 延迟飙升——这不是“慢”，是“卡在系统层”。

• 使用场景：窗口函数密集分析、大批量导入、OLAP 类报表查询
• 必须显式配置为多文件 + 固定大小 + max: 上限，例如：innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G;ibtmp2:12M:autoextend:max:2G
• 每个文件独立分配/清理，降低锁竞争；max: 防止磁盘被撑爆（不加的话可能无限增长直到填满分区）
• 修改后需重启 MySQL 生效；旧 ibtmp1 不会自动删除，必须确保实例已干净关闭后再手动清理

如何判断你的查询真正在用内存临时表？

不能只看 SQL 里有没有 CREATE TEMPORARY TABLE —— MySQL 内部自动创建的临时表才是性能瓶颈主因，且完全不可见。

• 关键监控指标：Created_tmp_tables（总内存临时表数） vs Created_tmp_disk_tables（磁盘临时表数）；若后者占前者比例 >20%，说明大量落盘
• 执行计划中出现 Using temporary（尤其配合 Using filesort）是强信号，但注意：即使没这个提示，复杂 UNION 或嵌套 DISTINCT 也可能暗中建临时表
• 避免误判：TEXT/BLOB 字段、未索引的 ORDER BY、跨 JOIN 表的 GROUP BY 都会强制使用磁盘临时表，哪怕参数调得再大

MySQL 8.0.13+ 用户该用 TempTable 还是 MEMORY？

别再无脑设 ENGINE=MEMORY。8.0.13 起，MySQL 默认启用 TempTable 存储引擎替代 MEMORY，它支持 BLOB/TEXT、行格式更紧凑、并发性能更好。

• TempTable 是默认且推荐的，无需显式指定；只有当你明确需要 HASH 索引做等值查找时，才考虑 ENGINE=MEMORY
• 但注意：TempTable 仍受 tmp_table_size 和 max_heap_table_size 限制，超限一样落盘
• 如果业务依赖 MEMORY 的 HASH 索引特性，记得在创建临时表时显式写 ENGINE=MEMORY，否则会被静默转成 TempTable

配置不是调完就完事——真正容易被忽略的是：临时表是否真的被复用（还是每次新建）、字段类型是否无意中触发了磁盘回退（比如 VARCHAR(500) 实际存了 490 字符）、以及并发连接数乘以单次内存上限后的总开销是否超出物理内存余量。这些细节不盯住，参数调再大也白搭。