平常习惯生产数据的时候,直接用insert into ...select这种语法,结果今天需要的数据量稍微一大就出错了,错误信息如下:
mysql> insert into t1 select null, b, c from t1;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_689f_0.MYI'; try to repair it
对这个错误信息,第一感觉很是奇怪,我这个表t1明明是innodb类型的,怎么会与myisam表扯上关系。于是查看了一下手册,发现了原因。对于insert into ...select 这种语法,如果源表和目的表为同一个表,那么它的处理流程是:
1.将源表select的结果放入一个临时表
可以通过show processlist来查看
mysql> show processlist;
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------+
| 1 | root | localhost | test | Query | 170 | Copying to tmp table on disk | insert into t1 select null, b, c from t1 |
| 2 | root | localhost | test | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------+
然后在手册上查看了一下临时表temporary table,没有对表的类型进行强制要求,但是我的参数
mysql> show variables like '%default%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
| default_week_format | 0 |
+------------------------+--------+
mysql为什么它创建的表为myisam类型呢?好了,这里先不谈这个问题。继续重点,表面上看去是/tmp/#sql_689f_0.MYI这个问题损坏了,但实际上是由于/tmp/的空间不够用了(在mysql里面经常遇到什么can't之类的错误,很多时候是由于没有权限或是没有磁盘空间了),确定它是由于没有磁盘空间是通过以下步骤来完成的:
1.mysql> insert into t1 select null, b, c from t1;
2.在上一步报错之前 df -h 可以查看到/tmp的占用率很快增长到100%
通过改变TMPDIR参数(指定到一个可用空间更大的目录)解决这个问题。
另外,为什么创建的临时表类型为myisam,我觉得可能是从性能上考虑的,因为这里创建的临时表就两个操作,insert进去、select出来没有其他的要求,用myisam表完全可以解决。
但是还遗留下了一个问题:每次生成的临时表的大小是源表的2倍。比如说t现在大小是1G,那么执行insert into t1 select null, b, c from t1;这时去查看生成的临时表的大小是2G。 没想明白原因。
多谢网友kerlion(itpub id)提醒,找到答案了:
由于源表里面有varchar类型的字段。而在创建临时表的时候,mysql默人将这些字段全部转换成char类型,我想它这么多的原因是为性能考虑的吧,典型的用空间换时间做法。
我也测试过源表没有varchar类型,那么将不会有上面的现象发生。
的确是有变长字段引起的,不过并没有我想的那么复杂。因为mysql中创建的临时表默认是memory类型,当达到一定规模以后就自动转换成myisam表,而memory表的字段类型只能是定长的,即使你创建表的时候是变长,但它也会将它作为定长用。
这才是正确的答案。