MariaDB 临时表磁盘写入 tmpdir 配置优化建议？

MariaDB 临时表磁盘写入优化核心是将 tmpdir 指向高性能磁盘分区，并配合 tmp_table_size 控制内存临时表阈值。适用场景为复杂排序或分组查询频繁的业务，风险边界在于磁盘空间不足或权限配置错误会导致服务启动失败。

先说结论：优化目标是减少磁盘 I/O 延迟并确保临时文件写入稳定，而非单纯追求内存临时表比例。

适合：涉及大量 ORDER BY、GROUP BY 或 UNION 的查询场景。
先准备：独立的高速存储分区，确保 MySQL 用户有读写权限。
再验证：通过 Created_tmp_disk_tables 状态值确认磁盘临时表比例。

命令速用版

查看当前临时目录配置和临时表创建统计：

SHOW VARIABLES LIKE 'tmpdir';
SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';

临时修改内存临时表大小上限（会话级或全局级，示例值需根据内存调整）：

SET GLOBAL tmp_table_size = 268435456;
SET GLOBAL max_heap_table_size = 268435456;

为什么会这样

结论：内存临时表转磁盘临时表是性能下降的主要转折点。

MariaDB 在执行复杂查询时，优先在内存中创建临时表。当数据量超过 tmp_table_size 或 max_heap_table_size 限制时，引擎会自动将临时表转换为磁盘表，写入 tmpdir 指定的目录。磁盘 I/O 速度远低于内存，频繁写入会导致查询延迟增加。优化 tmpdir 本质是优化磁盘 I/O 性能，而调整大小阈值是为了在内存允许范围内减少磁盘写入。

分步处理

1. 确认当前磁盘临时表比例

执行查询负载后，计算 Created_tmp_disk_tables 与 Created_tmp_tables 的比值。如果比值过高，说明大量临时表落盘。

2. 准备独立存储分区

在高速 SSD 或 NVMe 磁盘上创建专用目录，例如 /data/mariadb_tmp。确保该分区有足够空间，避免占满系统根分区。

3. 修改配置文件

编辑 my.cnf 或 my.ini，在 [mysqld] 段落下添加或修改：

tmpdir = /data/mariadb_tmp
tmp_table_size = 256M
max_heap_table_size = 256M

注意：tmp_table_size 和 max_heap_table_size 建议设置为相同值，否则较小者生效。上述大小仅为示例值，需根据服务器内存容量规划。

4. 调整权限并重启

执行 chown -R mysql:mysql /data/mariadb_tmp 赋予权限。重启 MariaDB 服务使配置生效。

怎么验证是否生效

重启后再次执行 SHOW VARIABLES LIKE 'tmpdir' 确认路径已变更。在高负载查询期间，监控 Created_tmp_disk_tables 的增长速率。如果优化有效，相同负载下磁盘临时表创建次数应减少，或查询响应时间降低。公开资料中没有看到可靠的量化数据表明具体降低多少毫秒，需结合业务监控判断。

常见坑

1. 权限错误：tmpdir 目录所有者必须是运行 MariaDB 的系统用户，否则服务无法启动。

2. 使用 tmpfs 风险：将 tmpdir 设为内存盘（tmpfs）虽快，但可能因内存不足触发 OOM Killer 导致数据库进程被杀。

3. 多路径语法：部分版本支持 tmpdir 配置多个路径用冒号分隔，但需确认具体版本兼容性，配置错误可能导致写入失败。

4. 磁盘空间：临时表可能瞬间占用大量空间，需监控分区使用率，避免磁盘写满导致数据库只读。

常见问题

tmp_table_size 和 max_heap_table_size 有什么区别？

两者共同决定内存临时表的最大大小，实际限制取两者中的较小值。建议将两者设置为相同值以避免混淆。

可以将 tmpdir 设置为多个目录吗？

可以，在配置文件中用冒号或分号分隔多个路径，MariaDB 会轮询写入以平衡 I/O，但需确认版本支持情况。

如何监控临时表是否过多？

定期查询 SHOW STATUS LIKE 'Created_tmp_disk_tables'，并结合慢查询日志分析涉及临时表的 SQL 语句。

参考来源

MariaDB Knowledge Base, Server System Variables - tmpdir, https://mariadb.com/kb/en/server-system-variables/#tmpdir
MariaDB Knowledge Base, Server System Variables - tmp_table_size, https://mariadb.com/kb/en/server-system-variables/#tmp_table_size