mysql alter table 原理_MySQL之高性能alter table操作

一、alter table 原理

MySQL的alter table操作的性能对大表来说，是一个大问题，为什么呢？

我们来看看alter table的底层原理，大多数情况下(有的时候未必会重建表)，当我们执行一个alter table操作后，其底层会执行如下几个操作：

用一个新的结构创建一个空表；

从旧表中查出所有数据插入新表；

然后删除旧表。

当数据表特别大的时候，这样的操作严重影响了效率，因此，在实际开发中，我们应该避免这样的耗时操作，采用高效的操作。

对于常见的场景，我们能使用的一般有两种方法：

一种是先在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换；

另一种技巧是影子拷贝；

影子拷贝：用要求的表结构创捷一张和源表无关的表，然后通过重命名和删表操作交换两张表。

影子拷贝举例：

mysql> create table new_table like old_table; #创建一张和原表一样结构的表

mysql> rename table old_table to tem_table,new_table to old_table; #重命名表名

mysql> drop table if exists tem_tablel; #删除旧表

但是我们考虑这样一种情况，如果我们只是简单的该表某一列的默认值等情况，那么我们也要赋值整个表的话，那是不是很繁琐？如以下代码语句(修改默认值为5)：

mysql> alter table table1

-> modify column col1 tinyint(3) not null default 5;

这条语句会拷贝整个表到一个新表，但是就是做一个修改默认值的操作，其他都没有改变。

理论上，我们应该跳过建表的和复制操作，直接修改字段属性即可，这样将会大大加快了整个执行效率。

二、修改.frm文件

我们知道，表的结构是存储在.frm文件中，如果我们能直接修改这个文件，而不必该表表本身，然而，在MySQL中，所有的modify column都会导致表重建。

但是alter column 命令却可以修改.frm文件，因此，如上的sql语句可以改写为如下：

mysql> alter table table1

-> alter column col1 tinyint(3) not null default 5;

这个语句直接修改.frm文件，不涉及表数据，所以，操作非常快。

三、扩展.frm文件的操作

注意：对.frm的操作是比较危险的操作，建议操作前，先对原.frm文件做一个备份。虽然快速，但是危险不推荐。

为了实现高性能的sql，很多时候，我们就是简单的改变表结构的一些信息，不需要对整个表的大量数据进行操作，因此，我们可以以.frm文件作为切入点，直接操作。

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

创建一张有相同结构的空表，并进行修改(例如增加enum常量)；

执行 flush with read lock；命令。(关闭所有正在使用的表，并禁止任何表被打开)

交换.frm文件；

执行unlock tables;命令。(释放第二步的读锁)

3.1 举例分析

下面以给sakila. film表的rating 列增加一一个常量为例来说明。当前列看起来如下:：

假设我们需要增加一个PG-14的分级:

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之后，则会导致已经存在的数据的含义被改变：已经存在的R值将变成PG-14，而已经存在的NC-17将成为R，等等。接下来用操作系统的命令交换.frm文件:

/var/lib/mysq1/sakila# mv film.frm film_tmp.frm

/var/lib/mysql/sakila# mv film_new.frm film.frm

/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

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

mysql> UNLOCK TABLES;

mysql> SHOW COLUMNS FROM sakila. film LIKE rating'G

最后需要做的是删除为完成这个操作而创建的辅助表：

mysql> DROP TABLE sakila.film_new;