MySQL中CTAS的消耗巨大OS临时空间的问题

CREATE TABLE `demo` (
`n1` varchar(500) DEFAULT NULL,
`n2` varchar(1000) DEFAULT NULL,
`n3` varchar(2000) DEFAULT NULL

) ENGINE=InnoDB;

每个列只插入一个字符，共4194304行，表的大小只有137M

insert into demo select * from demo;

show proccesslist中显示"Copying to tmp table on disk"

[root@db1 tmp]# du -sh *sql*

14G #sql_ce8_0.MYD

8.0K #sql_ce8_0.MYI

[root@db1 tmp]# df -h

Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
18G 17G 0 100% /
/dev/sda1 99M 13M 82M 14% /boot

tmpfs 501M 0 501M 0% /dev/shm

它产生了16G的临时文件直到磁盘用完

It reports error at last:

ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_ce8_0.MYI'; try to repair it

原因分析：

MySQL会先生成一个临时表，而mysql另外一个致命的缺点是临时表是固定行宽的，新的临时表的大小为：

4194304*2*3500=27G

 而新的表的大小只有300MB,当然这是个极端的测试，不过一盘的表平均值可能只有最大值的1/4到1/10，对于一个大表需要4倍到10倍的临时空间。

可见MySQL对字段宽度的设计要求很苛刻,对大表还是导入导出比较好

排序测试的结果样，14G的剩余空间用完后报错：

SELECT COUNT(*) FROM (SELECT n3,n2,n1 FROM demo ORDER BY 3,2,1) AS b;
Error writing FILE '/tmp/MYQFL908' (Errcode: 28)

这个产生的临时文件看不见，磁盘占用空间却不断减少，lsof发现标示为deleted
[root@db1 fd]# lsof -p 27409|grep tmp
mysqld 27409 mysql 5u REG 253,0 0 2510819 /tmp/ibJRivao (deleted)
mysqld 27409 mysql 6u REG 253,0 0 2510820 /tmp/ibfTykhs (deleted)
mysqld 27409 mysql 7u REG 253,0 0 2510821 /tmp/ibMoQaow (deleted)
mysqld 27409 mysql 8u REG 253,0 0 2510822 /tmp/ibQTafNE (deleted)
mysqld 27409 mysql 12u REG 253,0 0 2510823 /tmp/ibzghspJ (deleted)
mysqld 27409 mysql 42u REG 253,0 8426356736 2510829 /tmp/MYa4ZhwG (deleted)
mysqld 27409 mysql 75u REG 253,0 5692981248 2510830 /tmp/MYQFL908 (deleted)

而起超过8G会写另外一个文件