MYSQL之数据结构修改----MySQL大数据表结构更改手记
[ 2018-09-05 16:17:21 | 作者: admin ]
核心表 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分钟左右，因为整个流程只做了一次表复制；当然这过程系统资源占用率同样很高，处理完后即恢复正常！
评论Feed: http://blog.xg98.com/feed.asp?q=comment&id=2533
在测试服务器上测试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分钟左右，因为整个流程只做了一次表复制；当然这过程系统资源占用率同样很高，处理完后即恢复正常！
[最后修改由 admin， 于 2018-09-05 16:18:02]
评论Feed: http://blog.xg98.com/feed.asp?q=comment&id=2533
这篇日志没有评论。
此日志不可发表评论。
