insert into...select需要注意的问题

长平狐 发布于 2012/11/01 11:57
阅读 356
收藏 0

平常习惯生产数据的时候,直接用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                         |
+----+------+-----------+------+---------+------+------------------------------+------------------------------------------+

2.然后将临时表的内容插入到目的表
然后在手册上查看了一下临时表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表的字段类型只能是定长的,即使你创建表的时候是变长,但它也会将它作为定长用。

这才是正确的答案。


原文链接:http://blog.csdn.net/zbszhangbosen/article/details/7441889
加载中
返回顶部
顶部