MySQL 大数据量修改表结构问题

前言：

　　在系统正常运作一定时间后，随着市场、产品汪的需求不断变更，比较大的一些表结构面临不得不增加字段的方式来扩充满足业务需求；

 　  而 MySQL 在体量上了千万、亿级别数据的时候，Alter Table 的操作，可以让你等一天，而且在高峰期执行这种 SQL 让你的数据库也承担着压力。 

　　第一时间想到的解决方案就是新建一张表，去掉索引等关联关系，然后加上需要修改的字段，接着写上 insert select 语句进行导数据，　　

　　后面发现加上重建索引的操作，时间上几乎没有出入； 

　　针对这种问题，赶紧翻翻 《高性能 MySQL》 ，里面有写出一种解决方案，与大家分享一下。

 

一、工具 

　　// “影子拷贝”，针对不同的场景而言的方式，在一台不提供服务的机器上执行 Alter Table 操作，然后和提供服务器的机器进行切换；

　　Facebook 数据库运维团队的“online scherma change”工具：

　　　　https://launchpad.net/mysqlatfacebook

　　Shlomi Noach 的 openrak toolkit 工具：

　　　　https://launchpad.net/mysqlatfacebook

 

　　// 不是所有的 Alter Table 操作都会引起表重建。

 

 二、修改 .frm 文件 

　　// .frm MySQL 数据库表结构定义文件；（.myd 数据文件、.myi 索引文件、.idb 数据&索引文件 [前者 MyISAM 后者 InnoDB]）  

　　修改 .frm 文件非官方支持的，也没有文档记录，并且也有可能无法正常工作了，采用这些技术自己承担风险，做好备份！

　　下面这些操作有可能不需要重建表的：

　　1、移除（不是增加）一个列的 AUTO_INCREMENT 属性。

　　2、增加、移除，或更改 ENUM 和 SET 常量。如果移除的是已有行数据用到其值的常量，查询将会返回一个空字符串。

 

　　基本的技术是为想要的表结构创建一个新的 .frm 文件，然后用它替换掉已经存在的那张表的 .frm 文件，如下：

　　1、创建一张有相同结构的空表，并进行所需要的修改（例如增加 ENUM 常量）；

　　2、执行 FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开；

　　3、交换 .frm 文件；

　　4、执行 UNLOCK TABLE 来释放第 2 步的读锁；

 

　　假如我们需要为那些对电影更加谨慎的父母们增加一个 PG-14 的电影分级：

　　

  　  注意，我们是在常量列表的末尾增加一个新的值。如果把新增的值放在中间，

　　例如 PG-13 之后，则会导致已存在的数据的含义被改变：已经存在的 R 值将变成 PG-14，

　　而已经存在的 NC-17 将成为 R，等等。

　　

 　　接下来用操作系统的命令替换 .frm 文件：

/var/lib/mysql/sakial# mv film.frm film_tmp.frm
/var/lib/mysql/sakial# mv film_new.frm film.frm
/var/lib/mysql/sakial# mv film_tmp.frm film_new.frm

　　再回到 MySQL 命令行，现在可以解锁并查看变更后的效果了：

UNLOCK TABLES;
SHOW COLUMNS FROM sakila.film LIKE 'rating'\G

　　最后需要做的就是删除这个临时的辅助表了。

 

转载于:https://www.cnblogs.com/loongsoft/p/7269888.html