MySQL数据库产生大量sql_xxxx_1.MAD/MAIA结尾的文件

MySQL 生成大量以 sql_xxx.mad、.mai 为后缀的文件，核心原因是 MyISAM 存储引擎的临时表 / 碎片文件累积（.mai 是 MyISAM 表的索引文件变体，.mad 是 MyISAM 数据文件的异常命名，本质对应标准 MyISAM 的 .MYI（索引）和 .MYD（数据）文件）。以下从「文件定位」「原因分析」「紧急处理」「根本优化」四个维度给出完整解决方案。

一、先明确文件本质与归属

1. 文件类型说明

文件后缀	对应标准后缀	类型	产生场景
.mai	.MYI	MyISAM 索引文件	MyISAM 表 / 临时表的索引存储，或查询排序 / 分组时生成的磁盘临时表索引
.mad	.MYD	MyISAM 数据文件	MyISAM 表 / 临时表的数据存储，或内存临时表不足时生成的磁盘临时表数据

 

这类文件通常出现在两个目录：

 

• MySQL 数据目录：show variables like 'datadir';（对应具体数据库的 MyISAM 表文件）；
• MySQL 临时目录：show variables like 'tmpdir';（查询生成的临时表文件，命名多为 #sqlxxx/sql_xxx 前缀）。

2. 快速判断是否为临时文件

执行以下命令（Linux 环境），查看文件是否为 MySQL 临时表：

 

# 查看文件归属进程（替换为实际文件路径）
lsof /tmp/sql_227b_1.mad

# 查看文件创建时间（临时文件通常是近期查询生成）
ls -l /tmp/sql_*

 

 

若文件命名含随机字符（如 sql_227b_1）、无对应数据库表名，且创建时间与高频查询时段重合，基本可判定为 MyISAM 磁盘临时表文件。

二、紧急处理：清理无效文件（避免磁盘占满）

步骤 1：安全停止 MySQL（防止文件被占用）

# CentOS/RHEL
systemctl stop mysqld

# Ubuntu/Debian
systemctl stop mysql

 

步骤 2：区分并删除无效文件

（1）删除临时目录的孤立文件

临时表文件（tmpdir 目录下）若不是活跃表，可直接删除：

 

# 进入临时目录（替换为 show variables like 'tmpdir' 的结果）
cd /tmp

# 仅删除 sql_ 开头的 .mad/.mai 文件（确认无业务表）
rm -rf sql_*.mad sql_*.mai

# 若有 #sql 开头的临时文件，一并清理
rm -rf #sql*.mad #sql*.mai

 

（2）清理 MyISAM 表碎片文件

数据目录下的 .mad/.mai 若对应业务表，不可直接删除，需通过 OPTIMIZE TABLE 整理碎片：

 

-- 登录 MySQL，查看 MyISAM 表的碎片情况
SELECT 
  TABLE_NAME, 
  DATA_FREE/1024/1024 AS DATA_FREE_MB  -- 碎片大小（MB）
FROM INFORMATION_SCHEMA.TABLES 
WHERE ENGINE='MyISAM' AND TABLE_SCHEMA='你的数据库名';

-- 对碎片表执行优化（低峰期操作，MyISAM 表会锁表）
OPTIMIZE TABLE `你的数据库名`.`你的表名`;

 

步骤 3：重启 MySQL 并验证

 

systemctl start mysqld
# 检查 MySQL 启动状态
systemctl status mysqld

 

三、根本原因分析（针对性优化）

大量生成这类文件的核心原因有 3 类，需逐一排查：

原因 1：临时表配置过小，内存临时表转磁盘

MySQL 执行复杂查询（排序、分组、JOIN）时，优先创建内存临时表（MEMORY 引擎），若超过 tmp_table_size/max_heap_table_size，会自动转为 MyISAM 磁盘临时表，生成 .mad/.mai 文件。

验证方法：

-- 查看磁盘临时表创建数（数值快速增长则异常）
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
-- 查看总临时表数
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
-- 计算比例：磁盘临时表占比 > 10% 即为异常

 

优化方案：

修改 MySQL 配置文件（my.cnf/my.ini），增大临时表内存阈值：

 

[mysqld]
# 内存临时表最大大小（根据服务器内存调整，8G 内存建议设为 128M）
tmp_table_size = 128M
# 必须与 tmp_table_size 一致（否则以较小值为准）
max_heap_table_size = 128M
# 临时目录改用内存分区（/dev/shm 是内存挂载，速度快且无磁盘文件）
tmpdir = /dev/shm
# MySQL 5.7+：默认临时表引擎改为 InnoDB（避免 MyISAM 临时表）
default_tmp_storage_engine = InnoDB

 

 

修改后重启 MySQL，验证配置生效：

 

SHOW VARIABLES LIKE 'tmp_table_size';  -- 应显示 134217728（128M）
SHOW VARIABLES LIKE 'default_tmp_storage_engine'; -- 应显示 InnoDB

 

原因 2：大量使用 MyISAM 存储引擎

MyISAM 是非事务引擎，频繁的 INSERT/UPDATE/DELETE 会导致表碎片化，且 MyISAM 表的索引 / 数据文件（.mai/.mad）会持续膨胀；而 InnoDB 引擎的文件统一管理（.ibd），无此类碎片化文件。

验证方法：

-- 查看数据库中 MyISAM 表数量
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 
WHERE ENGINE='MyISAM' AND TABLE_SCHEMA='你的数据库名';

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'default-storage-engine';

 

优化方案：

（1）批量将 MyISAM 表转为 InnoDB（推荐）

-- 单个表转换（先备份！）
ALTER TABLE `你的数据库名`.`你的表名` ENGINE=InnoDB;

-- 批量转换脚本（Linux 下执行，需替换数据库名）
mysql -uroot -p -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA='你的数据库名'" | while read table; do
  mysql -uroot -p -e "ALTER TABLE `你的数据库名`.${table} ENGINE=InnoDB;"
done

 

（2）设置默认引擎为 InnoDB

修改 my.cnf：

 

[mysqld]
default-storage-engine = InnoDB
default_tmp_storage_engine = InnoDB  # 临时表也用 InnoDB

 

原因 3：慢查询 / 低效 SQL 导致大量临时表

无索引的 JOIN、ORDER BY、GROUP BY、DISTINCT 等操作，会强制 MySQL 创建临时表，进而生成 .mad/.mai 文件。

验证方法：

开启慢查询日志，定位低效 SQL：

 

[mysqld]
# 开启慢查询
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
# 执行时间超过 1 秒的视为慢查询
long_query_time = 1
# 记录所有使用临时表的查询
log_queries_not_using_indexes = ON
log_tmp_table_ops = ON

 

 

重启 MySQL 后，分析慢查询日志：

 

# 使用 mysqldumpslow 分析慢查询
mysqldumpslow -s t /var/log/mysql/slow.log

 

优化方案：

1. 为查询的 JOIN、WHERE、ORDER BY、GROUP BY 字段添加索引：

   -- 示例：为 ORDER BY 字段添加索引
   CREATE INDEX idx_create_time ON `表名`(create_time);
   

    
    
2. 简化复杂查询：拆分大查询为多个小查询，避免一次性排序 / 分组大量数据；
3. 避免 SELECT *：仅查询需要的字段，减少临时表数据量；
4. 禁用 filesort/temporary：通过索引优化，让查询避免文件排序和临时表（执行 EXPLAIN 查看，Extra 列无 Using temporary/Using filesort 即为优化成功）。

原因 4：MySQL 异常终止导致临时文件残留

MySQL 进程崩溃、服务器断电等异常情况，会导致临时表文件未自动清理，累积后占用磁盘。

优化方案：

1. 配置 MySQL 自动重启：
    

   # CentOS/RHEL
   systemctl enable mysqld
   # 设置重启策略
   echo -e "[Service]\nRestart=always\nRestartSec=3" >> /etc/systemd/system/mysqld.service.d/restart.conf
   systemctl daemon-reload
   

    
    
2. 定期清理临时目录：添加定时任务（crontab），每日低峰期清理孤立临时文件：

   # 编辑 crontab
   crontab -e
   # 添加以下内容（每日 3 点清理 /tmp 下的 sql_* 临时文件）
   0 3 * * * rm -rf /tmp/sql_*.mad /tmp/sql_*.mai /tmp/#sql*.mad /tmp/#sql*.mai
   

    

四、长期维护策略

1. 监控磁盘临时表指标：定期检查 Created_tmp_disk_tables，超过阈值（如每小时增长 1 万）立即告警；
2. 定期优化表：对仍使用 MyISAM 的表，每月低峰期执行 OPTIMIZE TABLE 整理碎片；
3. 监控磁盘空间：对 datadir 和 tmpdir 目录设置磁盘使用率告警（如超过 80% 告警）；
4. 升级 MySQL 版本：MySQL 5.7/8.0 对临时表和存储引擎的处理更优，建议从 5.5/5.6 升级；
5. 禁用 MyISAM 引擎：MySQL 8.0 中 MyISAM 已被标记为过时，可通过配置禁用：

   [mysqld]
   disable_storage_engines = MyISAM
   

    

五、注意事项

1. 数据备份：转换存储引擎、执行 OPTIMIZE TABLE 前，务必用 mysqldump 备份数据；
2. 锁表风险：OPTIMIZE TABLE 对 MyISAM 表会加表锁，生产环境需在低峰期执行；
3. 内存限制：tmpdir = /dev/shm 需确保服务器内存充足（如 8G 内存的服务器，/dev/shm 建议不超过 2G）；
4. 配置验证：修改 my.cnf 后，需执行 mysqld --help --verbose | grep 配置项 验证配置是否生效。

总结

解决这类文件大量生成的核心逻辑是：先清理无效文件应急 → 优化临时表配置减少磁盘临时表 → 替换 MyISAM 为 InnoDB 根除碎片 → 优化慢查询避免临时表创建 → 长期监控防止复发。90% 的场景下，通过增大临时表内存阈值 + 转换为 InnoDB 引擎，即可彻底解决该问题。