Mysql如何高效ALTER TABL
ALTER TABLE 缺点
MySQL 的ALTER TABLE 操作的性能对大表来说是个大问题。
MySQL MySQL 执行大部分修改表结构操作的方法是用新结构的 创建一个，空表从旧表中查出所有数据插入，新表然后删除旧。表这样操作可能需要花费很长，时间 如内果存不足而表又很大而且还有很多索引的情况下尤其如此。
此许多人 都有这样的经验，ALTER ALTER TABLE 操作需要花费数个小时其
数天才能完成。
对于MySQL 5.5 及更早版本
一般而言大部分ALTER TABLE 操作将导致MySQL 服务中断,常用的技巧有两种:
- 一种是先在台一不提供服务的机上器执行ALTER TABLE 操作，然 后和提供服
务的主库进行切换。 - 另外 一技巧种 是““影子拷贝""。影拷贝子 的技巧是用老的表结构创建一张和源表一样表结构的新表，然后通过重命名和删表操作交换两张表
不是有所的ALTER TABLE 操作都会引起表重建的
下面这操作些 有是可能不需要重建表的，
- 移除((不 是增加) 一个列的AUTO_INCREMENT 属性。
- 增加、移除, 或更改ENUM 和SET 常量。如 果移除的是已经有行数据用到其值的常量， 查询将返回会 一个空字串值。
例：修改字段长度
假如要修改电影的默认租赁 期，限从三 天改到五天。下面传统方法
mysql> ALTER TABLE sakila.film
-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;
SHOW STATUS 显示这个语句做了1 000 次读和1 000 次插入 操作。换名话说，它拷贝了 整张表到 一 张新表甚至列类的 型、大小和可否为NULL 属性 都没改变。
当表数据比较大时，就会特别慢。
如何优化
理论上，MySQL 可以跳过创建新表的步骤。列的默认值实际上存在表的. frm 文件中，是可以直接修改.frm 文件的。然而MySQL 还没有采用这种优化的方法 ，所有的MODIFY COLUMN 操作都将导致表重建。
可过如下ALTER COLUMN 操作来改变列的默认值
mysql> ALTER TABLE sakila.film
-> ALTER COLUMN rental_duration SET DEFAULT 5;
这个语句会直接修改. frm 文件。所以，这个操作是非常快的。
例： 给表的某列增加一个常量
比如：以给sakila.flim 表的rating 列增加一个常量
SHOW COLUMNS FROM sakila.film LIKE ‘rating’;
rating 增加一个PG-14 的电影分级:
一般做法
ALTER TABLE sakila.film MODIFY COLUMN rating ENUM('G','PG','PG-13','PG-14','R','NC-17') DEFAULT 'G';
表数据量大得情况下，会特别慢，还可能长时间影响对外服务。
优化方案
- 创建一张有相同结构 的空表，并进行需要所的修改(例如增加ENUM 常量) 。
- 执行FLUSH TABLES WITH READ LOCK LOCK。这将会关闭所有正在使用的表 ，并且禁止任何表被打开。
- 交换 .frm 文件.
- 执行 UNLOCK TABLES 来释放第2步得读锁
备份数据
MUST: 一定要备份数据
执行命令创建表、锁表命令
mysql> CREATE TABLE sakila.film_new LIKE sakila.film;
mysql> ALTER TABLE sakila.film_new
-> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17', 'PG-14')
-> DEFAULT 'G';
mysql> FLUSH TABLES WITH READ LOCK
注意，我们是常量在 列表的末尾增加一个新的值。如果把新增的放值在，中间例如PG-13 之后则导致会已存在经 数据的的含义被改变: 已存在经 的了值将变成PG-14 ，而已存在经 的NC-17 将成为RR，等
交换.frm 文件
接下来用操作系统的命令交换.frm 文件
/var/lib/mysql/sakila# mv film.frm film_tmp.frm
/var/lib/mysql/sakila# mv film_new.frm film. frm
/vaxr/lib/mysql/sakila# mv film_tmp.frm film_new. frm
解锁、查看表字段
mysql> UNLOCK TABLES;
mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating';
删除辅助表
确认没问题删除辅助表
mysql> DROP TABLE sakila.film_new ;
原文地址：https://blog.csdn.net/FLGBgo/article/details/140328525
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！