MYSQL之数据结构修改—-MySQL大数据表结构更改手记

核心表 2亿多数据量,索引+数据大小超80G,需要对该表结构作调整——drop两字段,然后新增5字段
在测试服务器上测试drop 两个字段,花费了30多分钟,并且内存占用率持续99.9%,其中3个cpu core占用80%以上(硬件16核,50G内存);虽然两列是drop掉了,但这么长的时间难以接受,基于此查看了官方文档中表结构修改处理流程以查找更佳解决办法:

http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#alter-table

以下为原文引用:

“ALTER TABLE运行时会对原表进行临时复制,在副本上进行更改,然后删除原表,再对新表进行重命名。在执行ALTER TABLE时,其它用户可以阅读原表,但是对表的更新和修改的操作将被延迟,直到新表生成为止。新表生成后,这些更新和修改信息会自动转移到新表上。

注意,如果您在执行ALTER TABLE时使用除了RENAME以外的选项,则MySQL会创建一个临时表。即使数据并不需要进行复制(例如当您更改列的名称时),MySQL也会这么操作。对于MyISAM表,您可以通过把myisam_sort_buffer_size系统变量设置到一个较高的值,来加快重新创建索引(该操作是变更过程中速度最慢的一部分)的速度。

如果您使用ALTER TABLE tbl_name RENAME TO new_tbl_name并且没有其它选项,则MySQL只对与table tbl_name相对应的文件进行重命名。不需要创建一个临时表。(您也可以使用RENAME TABLE语句对表进行重命名。”

##############引用结束

这就找到原因了,虽然是该动一列,但把整个表都拷贝了一次(如果是两列,需要操作两次),80多G的数据及逻辑操作花费这么长时间就能理解了。接着新增5个字段就不用试了,理论上需要复制表5次,时间更没法接受!

最后与dba商量改进方案,经测试只需要做一次表数据复制即可,流程是这样的:
1,create table_new ,并且包含去掉了2个多余字段+新增5字段
2,insert into table_new select * from table 复制所有的数据到新的表结构里
3,drop table ;  rename table_new to table;   删掉就表,并将新表名字改成原表名

这样总共花费也就15分钟左右,因为整个流程只做了一次表复制;当然这过程系统资源占用率同样很高,处理完后即恢复正常!

原文链接:http://hi.baidu.com/tianhuimin/item/28334a3cced447637d034b6d

留下评论