Incorrect key file for table

收到报警，发现/目录满了，登上服务器一下 果然使用率99%，刚想看看是什么原因，发现/目录又自动恢复到28%

查看error.log,发现是由于一条sql排序占用临时表导致空间上涨
160621 14:54:55 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1e4d_0.MYI'; try to repair it
160621 14:54:55 [ERROR] Got an error from unknown thread, /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos5-64/target/BUILD/Percona-Server-5.5.20-rel24.1/Percona-Server-5.5.20-rel24.1/storage/myisam/mi_write.c:223
160621 14:54:55 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1e4d_1.MYI'; try to repair it
160621 14:54:55 [ERROR] Got an error from unknown thread, /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos5-64/target/BUILD/Percona-Server-5.5.20-rel24.1/Percona-Server-5.5.20-rel24.1/storage/myisam/mi_write.c:223
160621 14:54:55 [ERROR] /usr/sbin/mysqld: Sort aborted: Error writing file '/tmp/MYfehsTQ' (Errcode: 28)
160621 14:54:55 [Warning] Sort aborted, host: cyg_bg_yf_2.11, user: app_cyg_tl, thread: 1051904, query: select count(1) from (select o.* from t_tl_orders o
                where o.delete_flag=1

对于这样的sql，进行优化就是，从sql来看，就能发现sql中的排序是不需要的，其实就是一个统计数量。
explain 也是报错的
(root:133.163_cyg:Tue Jun 21 15:11:35 2016)[cyg_tl]> explain select count(1)   from (select g.*           from t_tl_goods g          order by stick desc, pub_start_time desc, stick_time desc) c;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_1e4d_0.MYI'; try to repair it

解决方案：

1.优化sql，将排序去掉就可以
2.调整mysql配置参数 tmpdir，将tmpdir指向更大的目录

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26390465/viewspace-2120656/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26390465/viewspace-2120656/