MYSQL 的老大难，instant ,inplace，copy， DDL 怎么弄， 我不想迷迷糊糊

DDL 操作一直是我们的 MYSQL 的一个软肋，从MYSQL 5.6 其实相关的alter 语句已经有了改变，也就是题目的的inplace 和 copy 。其实很多人都知道，但用的比较少，因为有pt-OSC 工具呀，还有另外一个工具gh-ost

（之前是写过的，如有需要可以自己往前翻翻）

维护现在有提起这串豆腐的原因就是MYSQL 8 发展的太快, pt 工具有点跟不上，根据官方的文档，8.013后的版本，PT的部分工具就开始有的时候使用上会出现各种问题。

所以我们在mysql 的正根 alter 语句在高版本上还的拿出来用，而这里面就牵扯，什么时候 inplace 什么时候 copy 到底这都是什么鬼 ？MYSQL 的 DDL 好累心。

OK 下面就是一段官方+测试的东西 + MGR MYSQL 8.018

本次主要是针对字段的DDL 的 增删改来进行的

从上面的8.0 提供的表来看

下面是mysql 5.7 提供的，可以很清晰的看出，的确死不一样了，多了一列叫Instant

但实际上可以看出这个立即能做的事情不多，adding a column ，setting a column default value , Dropping the column default value 这些才可以进行instant 但 最常用到的 adding a column也上面有一个* 号，这说明不可以都可以，是要有条件的。

条件：

1 要不你就添加字段，你要是混合使用alter table语句，那恕不进行instant的操作，例如一条语句又是加字段，又是删字段

2 字段只能加到表最后一列，你要是想在之间加什么字段，恕不管用

3 表的row_format 不能是压缩的 compressed 的格式

4 表里面有全文索引，no no no 不可以

5 临时表不可以

6 数据字典表不可以

添加字段还是蛮快的。下面我们在一个新表，并且一直插入数据的状态下，看看添加字段还这么惬意吗？

我们看看结果如何

DROP TABLE IF EXISTS test.test;

CREATE TABLE test.test(

id int(10) not null auto_increment,

name varchar(20) not null,

age smallint not null,

work_years smallint not null,

PRIMARY key (`id`)

)ENGINE INNODB DEFAULT CHARSET utf8 COMMENT 'test';

#清空数据

TRUNCATE table test.test;

*/

#定义存储过程

delimiter //

DROP PROCEDURE IF EXISTS insert_test_val;

##num_limit 要插入数据的数量,rand_limit 最大随机的数值

CREATE PROCEDURE insert_test_val()

BEGIN

DECLARE i int default 1;

DECLARE a varchar(20) ;

DECLARE b smallint ;

DECLARE c smallint ;

WHILE i<=1000000 do

set b = FLOOR(rand()*50);

set c = FLOOR(rand()*10);

if i mod 2 = 0 then

set a = 'peter';

elseif i mod 3 = 0 then

set a = 'jimmy';

elseif i mod 5 = 0 then

set a = 'Tim';

elseif i mod 4 = 0 then

set a = 'semon';

else

set a = 'lisa';

end if;

if b < 20 then

set b = b + 15;

end if;

INSERT into test.test values (null,a,b,c);

set i = i + 1;

END WHILE;

END

//

#调用存储过程

call insert_test_val();

我们在test 库建立一个表，并且往里面插入大量的数据，然后我们

alter table test add column column1 varchar(500) ,ALGORITHM=INSTANT;

在这个表上添加一个字段，结果如何

SESSION 1

SESSION 2

字段瞬间添加上了，但是存储过程在运行的途中直接报错，通过上表的实验证明 MYSQL 8 添加字段，不在是一个问题，PT 工具可以收手了。

当然这里添加的是一般的column如果你要添加自增的列，则就不可以这样做，还是需要不能进行 DML 操作，类似锁表的操作，好在MYSQL 里面添加自增序列的人不多，大多都是添加普通字段而已。

虽然可以瞬间将阻碍的DML 操作终止，并快速添加字段，但这在生产上来说对应用程序的某些事务性的操作时有害的，所以使用的时候，要小心，避免产生不愿意发生的“特殊情况”。instant 好处是只对数据字典中的元数据进行更改。在SE更改期间不需要获取元数据锁，也不涉及表的数据。这个更改也影响了LOCK=…语义。没有必要为INSTANT algorihtm指定锁。

任何不能立即完成的操作设置ALGORITHM=INSTANT，您将得到一个错误，如下所示。这里的思想是预先失败并快速失败，而不是进行无声的转换并在幕后切换到另一个算法。

所以这是要注意的。这个功能是由腾讯游戏的DBA 团队提出的功能改进。

那这个更改对实际当中的意义在哪里

1 对于大型表，这可能需要很长时间，特别是在复制设置中。 2 磁盘空间需求将增加一倍以上，大致与现有表的大小相同。 3 DDL操作需要大量资源，对CPU、内存和IO的要求很高。这将从用户事务中窃取资源。 4 如果涉及复制，用户可能需要等待更长的时间才能准备好从服务器。DDL完成后将外部化

粗浅的说完alter table 的 instant 的问题， 下面的说说经常要添加索引的问题，在添加索引时是不能使用instant的功能的。目前在MYSQL 8 里面的最优的还是inplace的方。(在你无法使用工具的时候)

我们继续，一个测试，我们往test表里面插入数据，同时在另一个线程添加索引。

session 1

session 2

他大致的操作步骤

• 新建frm临时文件
• 锁原表，不许DML，可以查询
• 按聚集索引顺序，查数据，找索引列数据，排序并插入到新的索引页中
• 原表不能读操作，也就是原表此时不提供读写服务
• 进行rename操作，替换frm文件，完成DDL过程

从上边的图可以看到，索引已经添加并且表中国的数据也一直在插入，并没有产生什么看似不良的影响。（以上操作在MGR 集群中操作）

当然这不能说明，就不会有问题，生产系统的复杂性不是我们可以想象的，所以以上测试仅仅代表他能，但对非常繁忙的系统还是要小心。

而算法inplace——顾名思义，它修改表的模式，而不创建原始表的临时表，而是修改原始表本身。在更改表模式(DDL)期间，它不会导致对原始表的读写锁(数据操作语言)

算法copy——顾名思义,它改变了模式的现有表创建一个新的临时表改变模式(在我们的例子中,添加一个新的列),迁移到新的临时表的数据,改变了链接到新表,滴旧表,完成了。

使用ALGORITHM=COPY子句运行的ALTER TABLE操作可以防止并发的DML操作。仍然允许并发查询。也就是说，表复制操作总是至少包含LOCK=SHARED(允许查询，但不允许DML)的并发限制。您可以通过指定LOCK=EXCLUSIVE来进一步限制此类操作的并发性，这可以防止DML和查询。

目前写到这里，因为发现这篇文字已经接近 2千多字了，有同学反映，写的字太多，看的头疼，所以......

最后在啰嗦一下，加字段您INSTANT， 建索引您inplace ，其他能inplace 别copy。磁盘空间不能抠，DDL操作部分Double 磁盘空间。