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

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

而在早期的版本中，这种问题就更让人无语了。在Oracle中这个问题解决的较早，当然在很多技术实现细节上，Oracle和MySQL还是蛮大的差距。Oracle中有在线重定义的方案物化视图prebuilt和在线重定义 (r10笔记第25天)，而且本身对于一些DDL的操作代价要比MySQL低。不过在碰到添加字段且加默认值的情况，在Exadata上跑性能测试，问题依旧是很棘手。以至于在有些业务中，对某些核心的大表，有些公司是使用视图来达到这种动态的字段扩展而非直接添加字段。这，应该算是一场技术上的硬仗。

这类问题的根本和数据的存储也密不可分。有兴趣可以看看。MySQL和Oracle的添加字段的处理差别 （r10笔记第73天）

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

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

pt工具本身的安装部署很简单，可以参考 Percona-toolkit的安装和配置(r8笔记第86天）

简单的使用pt-table-checksu和pt-table-sync可以参考MySQL主从不一致的修复过程（r10笔记第96天）

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

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

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

如果想修改为newtest

> alter table t_user_login_record rename to newtest; Query OK, 0 rows affected (0.03 sec)

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

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

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 -rw-rw---- 1 mysql mysql 3162505216 Oct 13 17:09 newtest.ibd -rw-rw---- 1 mysql mysql 8874 Dec 5 11:25 #sql-2931_4807af.frm -rw-rw---- 1 mysql mysql 58720256 Dec 5 11:25 #sql-2931_4807af.ibd

在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                                                         
--------------------------------+--------------------------------------------------------------
Waiting on empty queue          | NULL                                                         
copy to tmp table               | alter table newtest add column newcol varchar(10) default '' 
Waiting for table metadata lock | insert into newtest(game_type,login_time,login_account,cn_mas
NULL                            | show processlist      

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

---TRANSACTION 481BF2, not started MySQL thread id 4721141, OS thread handle 0x7f99780bc700, query id 18882493 localhost root Waiting for table metadata lock 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') ---TRANSACTION 4821A2, ACTIVE 1 sec inserting, thread declared inside InnoDB 253

mysql tables in use 2, locked 2 46 lock struct(s), heap size 6960, 6887 row lock(s), undo log entries 6887 MySQL thread id 4720559, OS thread handle 0x7f93710b9700, query id 18881573 localhost root copy to tmp table alter table newtest add column newcol varchar(10) default '' TABLE LOCK table `test`.`newtest` trx id 4821A2 lock mode IS TABLE LOCK table `test`.`#sql-2931_4807af` trx id 4821A2 lock mode IX TABLE LOCK table `test`.`#sql-2931_4807af` trx id 4821A2 lock mode AUTO-INC

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

当然这个阻塞的时长还是很不乐观的，可能十分钟，数十分钟，取决于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'); Query OK, 1 row affected (5 min 33.04 sec)

MySQL 5.7中的DDL对比

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

-rw-r----- 1 mysql mysql 8874 Dec 5 16:47 newtest.frm -rw-r----- 1 mysql mysql 3900702720 Dec 5 17:05 newtest.ibd -rw-r----- 1 mysql mysql 8840 Dec 5 17:33 #sql-6273_9989e.frm -rw-r----- 1 mysql mysql 46137344 Dec 5 17:33 #sql-ib276-3638407390.ibd

同样的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'); Query OK, 1 row affected (0.01 sec)

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

mysql tables in use 1, locked 1 0 lock struct(s), heap size 1136, 0 row lock(s) MySQL thread id 628894, OS thread handle 140140882102016, query id 9565763 localhost root altering table alter table newtest drop column newcol Trx read view will not see trx with id >= 909683, sees < 909682 怎么去理解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 -rw-r----- 1 mysql mysql 3527409664 Dec 5 17:37 newtest.ibd -rw-r----- 1 mysql mysql 8840 Dec 5 18:13 _newtest_new.frm -rw-r----- 1 mysql mysql 15728640 Dec 5 18:13 _newtest_new.ibd -rw-r----- 1 mysql mysql 1213 Dec 5 18:13 newtest.TRG -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_del.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_ins.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_upd.TRN

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

命令的部分输出如下：

Altering `test`.`newtest`... Creating new table... CREATE TABLE `test`.`_newtest_new` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键，自增长', 。。。, PRIMARY KEY (`id`), KEY `ind_tmp_account1` (`login_account`) ) ENGINE=InnoDB AUTO_INCREMENT=22681849 DEFAULT CHARSET=utf8 Created new table test._newtest_new OK. Waiting forever for new table `test`.`_newtest_new` to replicate to teststd.test.com... Altering new table... ALTER TABLE `test`.`_newtest_new` add index ind_login_time_newtest(login_time) Altered `test`.`_newtest_new` OK. 2016-12-05T18:13:31 Creating triggers... CREATE TRIGGER `pt_osc_test_newtest_del` AFTER DELETE ON `test`.`newtest` FOR EACH ROW DELETE IGNORE FROM `test`.`_newtest_new` WHERE `test`.`_newtest_new`.`id` <=> OLD.`id` 。。。创建INSERT,DELETE TRIGGER 2016-12-05T18:13:31 Created triggers OK. 2016-12-05T18:13:31 Copying approximately 22571280 rows... INSERT LOW_PRIORITY IGNORE INTO `test`.`_newtest_new` (`id`, `game_type`, `login_time`, `login_account`, `cn_master`, `client_ip`) SELECT `id`, `game_type`, `login_time`, `login_account`, `cn_master`, `client_ip` FROM `test`.`newtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 5214 copy nibble*/ SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`newtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/ Copying `test`.`newtest`: 1% 27:54 remain Copying `test`.`newtest`: 3% 27:43 remain

Copying `test`.`newtest`: 98% 00:25 remain 2016-12-05T18:45:16 Copied rows OK. 2016-12-05T18:45:16 Analyzing new table... 2016-12-05T18:45:16 Swapping tables... RENAME TABLE `test`.`newtest` TO `test`.`_newtest_old`, `test`.`_newtest_new` TO `test`.`newtest` 2016-12-05T18:45:16 Swapped original and new tables OK. 2016-12-05T18:45:16 Dropping old table... DROP TABLE IF EXISTS `test`.`_newtest_old` 2016-12-05T18:45:17 Dropped old table `test`.`_newtest_old` OK. 2016-12-05T18:45:17 Dropping triggers... DROP TRIGGER IF EXISTS `test`.`pt_osc_test_newtest_del`; DROP TRIGGER IF EXISTS `test`.`pt_osc_test_newtest_upd`; DROP TRIGGER IF EXISTS `test`.`pt_osc_test_newtest_ins`; 2016-12-05T18:45:17 Dropped triggers OK. Successfully altered `test`.`newtest`.

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

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

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

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

# strings _newtest_new.frm PRIMARY ind_tmp_account1 ind_login_time_newtest InnoDB ....

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

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

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

-rw-r----- 1 mysql mysql 8840 Dec 5 17:33 newtest.frm -rw-r----- 1 mysql mysql 3527409664 Dec 5 18:36 newtest.ibd -rw-r----- 1 mysql mysql 8840 Dec 5 18:13 _newtest_new.frm -rw-r----- 1 mysql mysql 4345298944 Dec 5 18:45 _newtest_new.ibd -rw-r----- 1 mysql mysql 1213 Dec 5 18:13 newtest.TRG -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_del.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_ins.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_upd.TRN

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

-rw-r----- 1 mysql mysql 8840 Dec 5 17:33 newtest.frm -rw-r----- 1 mysql mysql 3527409664 Dec 5 18:36 newtest.ibd -rw-r----- 1 mysql mysql 8840 Dec 5 18:13 _newtest_new.frm -rw-r----- 1 mysql mysql 4345298944 Dec 5 18:45 _newtest_new.ibd -rw-r----- 1 mysql mysql 1213 Dec 5 18:13 newtest.TRG -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_del.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_ins.TRN -rw-r----- 1 mysql mysql 39 Dec 5 18:13 pt_osc_test_newtest_upd.TRN

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

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

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

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

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