MySQL中的Online DDL(第一篇）(r11笔记第3天)

记得有一天快下班的时候，一位开发同事找到我说，需要对一个表做变更，数据量据说有上千万，而当时是使用的MySQL版本是5.5，这可如何是好，对于在线业务要求高的情况下，这种需求真是让人头疼。

而在早期的版本中，这种问题就更让人无语了。在Oracle中这个问题解决的较早，当然在很多技术实现细节上，Oracle和MySQL还是蛮大的差距。Oracle中有在线重定义的方案

这类问题的根本和数据的存储也密不可分。有兴趣可以看看。

MySQL中这类问题有了一种叫OSC的工具之后，情况有了很大的改观。最早是facebook来做的这件事情，后来Percona进行了改变，使用perl实现，因为功能全面，支持的完善，现在基本上成了标准的行业工具。简称pt-osc。

    在MySQL 5.5中，这类问题使用pt-osc来处理就很有效了，在MySQL 5.6推出的online DDL中，已经原生支持，在5.7中已经发展很不错了，如此一来，pt-osc的支持算是一种可选的方式。而也可以由此看出，技术上的重大突破会逐步降低维护的复杂度，所以水航船高，各行各业都有相似之处。

    pt工具本身的安装部署很简单，可以参考

简单的使用pt-table-checksu和pt-table-sync可以参考

首先说明不是所有的DDL都会持续很长时间，比如修改表名，这是一个很有意思的操作，无论表大小，操作效率都很高。

比如我们存在一个表  t_user_login_record，数据量2000万。

-rw-rw---- 1 mysql mysql       8840 Oct 13 17:04 t_user_login_record.frm

如果想修改为newtest

> alter table t_user_login_record rename to newtest;

这个过程本质上就是数据字典信息的修改。如果你可以理解的更通俗一点，就是修改文件名。

-rw-rw---- 1 mysql mysql       8840 Oct 13 17:04 newtest.frm

MySQL 5.5原生的DDL代价

为什么MySQL5.5中很多DDL操作的代价很高呢。因为很多场景的处理都是在做数据的复制。

比如我们添加一个字段，添加默认值。

alter table newtest add column newcol varchar(10) default '';

MySQL原生的操作就是创建一个临时的表，开始表数据的复制。

-rw-rw---- 1 mysql mysql       8840 Oct 13 17:04 newtest.frm

在MySQL5.5中，如果在DDL执行的过程中，在另外一个窗口中做一个insert操作，不好意思，这类操作就会阻塞，持续时间会很长。

insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2013-08-16 16:22:10','150581500032','572031626','183.128.143.113');

如果查看show processlist的结果，就会发现临时表复制的信息和锁的信息。

State                           | Info                                                         

如果查看show engine innodb status\G的结果，会发现一些很细致的锁信息。

---TRANSACTION 481BF2, not started

mysql tables in use 2, locked 2

可以看到锁的信息比我们想的要复杂一些。

当然这个阻塞的时长还是很不乐观的，可能十分钟，数十分钟，取决于DDL的时长。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2013-08-16 16:22:10','150581500032','572031626','183.128.143.113');

MySQL 5.7中的DDL对比

在MySQL 5.7中差别就很大了，一模一样的操作，在MySQL 5.7中还是创建一个临时数据表的数据复制。

-rw-r----- 1 mysql mysql       8874 Dec  5 16:47 newtest.frm

同样的DML语句全然没有压力。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2013-08-16 16:22:10','150581500032','572031626','183.128.143.113');

查看show engine innodb status\G的结果就有很大的差别。

mysql tables in use 1, locked 1怎么去理解online DDL的一些实现原理呢。我们还是可以使用pt-osc来做。

我们就配置一个用户，在5.7下面的语句有了改进，最好使用create user的方式。

 GRANT ALL  ON *.* TO 'pt_osc'@'test%' identified  by  'pt_osc';

然后使用pt-online-schema-change来完成。这里我们需要给表newtest添加一个索引，基于login_time字段

 ./pt-online-schema-change --host=10.11.128.99 -u pt_osc -p pt_osc   --alter='add  index ind_login_time_newtest(login_time)' --print --execute D=test,t=newtest

这个时候看看数据目录，内容就很丰富了。-rw-r----- 1 mysql mysql       8840 Dec  5 17:33 newtest.frm

可以很明显看到创建了3个触发器（针对增删改操作），创建了临时的表复制数据。

命令的部分输出如下：

Altering `test`.`newtest`...

Copying `test`.`newtest`:  98% 00:25 remain

这个过程用Percona的一张图来说明，用流程化的方式来解读。

简单题几个问题来加深对于online DDL的理解。

1.如果创建索引，这个过程中创建的索引是在源表上还是新表上？

答：要简单来论证可以使用strings来解读临时创建的数据表，这里是_newtest_new.frm,新创建的索引ind_login_time_newtest赫然在列。

# strings _newtest_new.frm

2. pt-osc在系统层面文件的变化情况是怎么样的？

答：我们可以去一些临界点来验证。

开始pt-osc的操作时，文件的情况如下。

-rw-r----- 1 mysql mysql       8840 Dec  5 17:33 newtest.frm

在变更完成前的一瞬间，文件情况如下,可以看到newtest.ibd和_newtest_new.ibd的切换。

-rw-r----- 1 mysql mysql       8840 Dec  5 17:33 newtest.frm

再次查看，触发器都会一一删除。

-rw-r----- 1 mysql mysql       8840 Dec  5 18:13 newtest.frm通过这个过程可以加深对于online DDL的实现原理的理解，不过MySQL 5.7中原生的online DDL原理和pt-osc还是有一些差别，仅仅作为一个参考。

所测试的场景都是使用了默认的选项copy而非inplace

ALTER TABLE的补充语法为：ALGORITHM [=] {DEFAULT|INPLACE|COPY} 

对于online DDL的操作，更多的细节稍后再来一篇继续补充完善。

欢迎关注我的公众号，真知灼见谈不上，重在技术分享交流。