mysql 的 alter table 操作性能小提示

通常情况下,修改表的结构一般不会有太大问题,无非就是一个 alter table 操作,但是对于大表做 alter 操作是一个大问题,请小伙伴们慎重。

  mysql执行大部分修改表结构操作方法是创建一个空表,从旧表查询所有的数据插入,然后删除旧表。如果内存不足,或者表很大,而且还有很多索引的情况下,可能需要花费数个小时,甚至数天才能完成。所以如果是操作线上数据库,甚至会影响到服务的正常进行,操作一定要慎重。如有需要可以查询其他方法操作进行。

在这里,如果是修改列的默认值的话,是有一个小技巧的。列的默认值是在 .frm 文件中的,所以修改默认值是可以直接修改这个文件而不用动到表本身的,但是mysql还没有采用这种优化方法,所有的 modify column 都将重建表。

通常情况下,大家修改表的默认值是:

   alter table 表名 modify column 字段 ...... default 值;

  这样操作的话,如果表里面有10000条数据,你会发现,这个语句其实是做了10000次的查询和10000次的插入,也就是拷贝了整张表。但其实你只是想改一个列的默认值而已。

如果用 alter column 来操作的话,那将是非常快的:

  比如: alter table 表名 alter column 字段 set default 值;

  这里你会发现只是一瞬间,也不会有多余的查询和插入。

从上面的例子我们看到,只修改表的 .frm 文件是很快的。但是mysql有时候会在没有必要的时候也重建表,如果愿意冒一些险,可以让mysql不重建表而做一些其他类型的修改。当然一定要记得备份,因为这个需要自己承担风险。

下面这些操作有可能不需要重新建表:

  • 移除(不是增加)一个列的 AUTO_INCREMENT 属性;
  • 增加、移除 或更改 ENUM 和 SET 常量。 如果移除的是已经有行数据用到其值的常量,查询将会返回一个空字符串;

基本的技术就是为想要的表结构创建一个新的 .frm 文件,然后替换掉之前已存在的表的 .frm 文件,像这样:

  1. 创建一张有相同结构的空表,并进行所需要的修改(例如增加 ENUM 的常量);
  2. 执行 FLUSH TABLES WITH READ LOCK 。这将会关闭所有正在使用的表,并且禁止任何表被打开;
  3. 交换 .frm 文件;
  4. 执行 UNLOCK TABLES 来释放第二步的读锁;

注意:再次强调,记得备份。

上一篇:$.ajax居然触发popstate事件?


下一篇:Java基础知识强化之集合框架笔记39:Set集合之HashSet存储字符串并遍历