测试环境：MySQL5.7pt-online-schema-change 3.2.0第一次加索引，使用MySQL原生的online DDL。做一个超级慢的update，模拟长事务。
mysql> update test set user_id='aa' where user_id like '%123%';
此时开始加索引：
mysql> alter table test add index idx_uid(user_id);
查看现场状态：
mysql> show processlist; +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ | 2 | pt_checksum | server-254-163:53960 | test | Sleep | 0 | | NULL | | 299 | repl | server-254-163:15492 | NULL | Binlog Dump | 1656769 | Master has sent all binlog to slave; waiting for more updates | NULL | | 308 | repl | server-254-163:31672 | NULL | Binlog Dump | 1656282 | Master has sent all binlog to slave; waiting for more updates | NULL | | 312 | root | 172.16.118.23:58883 | handong | Sleep | 6208 | | NULL | | 313 | root | 172.16.118.23:58884 | handong | Sleep | 7097 | | NULL | | 326 | root | localhost | NULL | Query | 0 | starting | show processlist | | 329 | root | localhost | handong | Query | 9 | updating | update test set user_id='aa' where user_id like '%123%' | | 332 | root | localhost | handong | Query | 3 | Waiting for table metadata lock | alter table test add index idx_uid(user_id) | +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ 8 rows in set (0.00 sec)
可以看到添加索引遇到了MDL（Waiting for table metadata lock） 新开一个窗口，继续进行简单查询：
mysql> select * from test limit 1;
此查询一直等待，未返回结果，查询线程状态：
mysql> show processlist; +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ | 2 | pt_checksum | server-254-163:53960 | test | Sleep | 0 | | NULL | | 299 | repl | server-254-163:15492 | NULL | Binlog Dump | 1656840 | Master has sent all binlog to slave; waiting for more updates | NULL | | 308 | repl | server-254-163:31672 | NULL | Binlog Dump | 1656353 | Master has sent all binlog to slave; waiting for more updates | NULL | | 312 | root | 172.16.118.23:58883 | handong | Sleep | 6279 | | NULL | | 313 | root | 172.16.118.23:58884 | handong | Sleep | 7168 | | NULL | | 326 | root | localhost | handong | Query | 45 | Waiting for table metadata lock | select * from test limit 1 | | 329 | root | localhost | handong | Query | 80 | updating | update test set user_id='aa' where user_id like '%123%' | | 332 | root | localhost | handong | Query | 74 | Waiting for table metadata lock | alter table test add index idx_uid(user_id) | | 333 | root | localhost | NULL | Query | 0 | starting | show processlist | +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ 9 rows in set (0.00 sec)
再次新开一个窗口，继续进行简单查询：
mysql> select * from test limit 1;
查看线程状态：
mysql> show processlist; +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ | 2 | pt_checksum | server-254-163:53960 | test | Sleep | 0 | | NULL | | 299 | repl | server-254-163:15492 | NULL | Binlog Dump | 1656925 | Master has sent all binlog to slave; waiting for more updates | NULL | | 308 | repl | server-254-163:31672 | NULL | Binlog Dump | 1656438 | Master has sent all binlog to slave; waiting for more updates | NULL | | 312 | root | 172.16.118.23:58883 | handong | Sleep | 6364 | | NULL | | 326 | root | localhost | handong | Query | 130 | Waiting for table metadata lock | select * from test limit 1 | | 329 | root | localhost | handong | Query | 165 | updating | update test set user_id='aa' where user_id like '%123%' | | 332 | root | localhost | handong | Query | 159 | Waiting for table metadata lock | alter table test add index idx_uid(user_id) | | 333 | root | localhost | handong | Query | 11 | Waiting for table metadata lock | select * from test limit 1 | | 334 | root | localhost | NULL | Query | 0 | starting | show processlist | +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ 9 rows in set (0.00 sec)
可以发现： 对于原生online DDL，在开始DDL之前，如果有长事务未释放，进行DDL的事务就会处于Waiting for table metadata lock状态，后续所有关于这张表的查询都会排队Waiting for table metadata lock。如果生产环境此表访问频繁，可能就会造成应用访问超时。 这次把刚才加的索引删掉，利用pt-online-schema-change做一次批量更新，模拟长事务：
mysql> update test set user_id='aaa' where user_id like '%123%';
使用 pt-online-schema-change删除索引：
[root@server-254-163 ~]# pt-online-schema-change --user=root --password=mysql -h localhost --socket=/mysql/mysql3307/mysqld.sock --port=3307 D=handong,t=test --alter "drop index idx_uid" --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --charset=utf8 --execute No slaves found. See --recursion-method if host server-254-163 has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 No foreign keys reference `handong`.`test`; ignoring --alter-foreign-keys-method. Altering `handong`.`test`... Creating new table... CREATE TABLE `handong`.`_test_new` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_uid` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=95418961 DEFAULT CHARSET=utf8mb4 Created new table handong._test_new OK. Altering new table... ALTER TABLE `handong`.`_test_new` drop index idx_uid Altered `handong`.`_test_new` OK. 2020-12-17T18:31:16 Creating triggers...
一直处于创建trigger状态，查询此时线程状态：
mysql> show processlist; +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | 2 | pt_checksum | server-254-163:53960 | test | Sleep | 0 | | NULL | | 299 | repl | server-254-163:15492 | NULL | Binlog Dump | 1659526 | Master has sent all binlog to slave; waiting for more updates | NULL | | 308 | repl | server-254-163:31672 | NULL | Binlog Dump | 1659039 | Master has sent all binlog to slave; waiting for more updates | NULL | | 326 | root | localhost | handong | Query | 0 | starting | show processlist | | 329 | root | localhost | handong | Query | 14 | updating | update test set user_id='aaa' where user_id like '%123%' | | 332 | root | localhost | handong | Sleep | 2760 | | NULL | | 333 | root | localhost | handong | Sleep | 2612 | | NULL | | 334 | root | localhost | NULL | Sleep | 2601 | | NULL | | 335 | root | localhost | handong | Query | 8 | Waiting for table metadata lock | CREATE TRIGGER `pt_osc_handong_test_del` AFTER DELETE ON `handong`.`test` FOR EACH ROW DELETE IGNORE | | 336 | root | localhost | handong | Sleep | 8 | | NULL | +-----+-------------+----------------------+---------+-------------+---------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec)
发现create trigger出现Waiting for table metadata lock 此时批量更新依然没有结束，重新开个窗口，进行简单查询：
mysql> select * from test limit 1; +---------+---------------------+ | id | user_id | +---------+---------------------+ | 4812551 | 1019092022432797988 | +---------+---------------------+ 1 row in set (0.01 sec)
发现很快就能返回结果，测试多次都没有问题。 pt-online-schema-change解决了DDL等待DML时候，后边所有会话都堆积的情况，避免影响业务。查看删除索引全部日志：
[root@server-254-163 ~]# pt-online-schema-change --version pt-online-schema-change 3.2.0 [root@server-254-163 ~]# pt-online-schema-change --user=root --password=mysql -h localhost --socket=/mysql/mysql3307/mysqld.sock --port=3307 D=handong,t=test --alter "drop index idx_uid" --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --charset=utf8 --execute No slaves found. See --recursion-method if host server-254-163 has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 No foreign keys reference `handong`.`test`; ignoring --alter-foreign-keys-method. Altering `handong`.`test`... Creating new table... CREATE TABLE `handong`.`_test_new` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_uid` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=95418961 DEFAULT CHARSET=utf8mb4 Created new table handong._test_new OK. Altering new table... ALTER TABLE `handong`.`_test_new` drop index idx_uid Altered `handong`.`_test_new` OK. 2020-12-17T18:31:16 Creating triggers... 2020-12-17T18:33:20 Created triggers OK. 2020-12-17T18:33:20 Copying approximately 95333225 rows... INSERT LOW_PRIORITY IGNORE INTO `handong`.`_test_new` (`id`, `user_id`) SELECT `id`, `user_id` FROM `handong`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 189639 copy nibble*/ SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `handong`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/ Copying `handong`.`test`: 4% 10:35 remain Copying `handong`.`test`: 8% 11:02 remain Copying `handong`.`test`: 12% 10:51 remain Copying `handong`.`test`: 15% 10:49 remain Copying `handong`.`test`: 19% 10:38 remain Copying `handong`.`test`: 22% 10:24 remain Copying `handong`.`test`: 25% 10:11 remain Copying `handong`.`test`: 29% 09:46 remain Copying `handong`.`test`: 32% 09:19 remain Copying `handong`.`test`: 36% 08:52 remain Copying `handong`.`test`: 39% 08:21 remain Copying `handong`.`test`: 43% 07:51 remain Copying `handong`.`test`: 46% 07:20 remain Copying `handong`.`test`: 50% 06:52 remain Copying `handong`.`test`: 54% 06:20 remain Copying `handong`.`test`: 57% 05:50 remain Copying `handong`.`test`: 61% 05:21 remain Copying `handong`.`test`: 64% 04:53 remain Copying `handong`.`test`: 68% 04:22 remain Copying `handong`.`test`: 71% 03:54 remain Copying `handong`.`test`: 75% 03:25 remain Copying `handong`.`test`: 79% 02:54 remain Copying `handong`.`test`: 82% 02:24 remain Copying `handong`.`test`: 86% 01:56 remain Copying `handong`.`test`: 89% 01:25 remain Copying `handong`.`test`: 93% 00:55 remain Copying `handong`.`test`: 96% 00:28 remain 2020-12-17T18:47:16 Copied rows OK. 2020-12-17T18:47:16 Analyzing new table... 2020-12-17T18:47:16 Swapping tables... RENAME TABLE `handong`.`test` TO `handong`.`_test_old`, `handong`.`_test_new` TO `handong`.`test` 2020-12-17T18:47:16 Swapped original and new tables OK. 2020-12-17T18:47:16 Dropping old table... DROP TABLE IF EXISTS `handong`.`_test_old` 2020-12-17T18:47:16 Dropped old table `handong`.`_test_old` OK. 2020-12-17T18:47:16 Dropping triggers... DROP TRIGGER IF EXISTS `handong`.`pt_osc_handong_test_del` DROP TRIGGER IF EXISTS `handong`.`pt_osc_handong_test_upd` DROP TRIGGER IF EXISTS `handong`.`pt_osc_handong_test_ins` 2020-12-17T18:47:16 Dropped triggers OK. Successfully altered `handong`.`test`.
1、创建一张新表_xxx_new ，对其做DDL操作2、创建3个触发器(delete\update\insert)在复制数据开始之后，将对源数据表继续进行数据修改的操作记录下来，以便在数据复制结束后执行这些操作，保证数据不会丢失3、复制数据，从源数据表复制数据到新表（分成多个chunk，小事务提交）4、修改外键相关的子表，根据修改后的数据，修改外键关联的子表5、将源数据表重命名为old表，将新表更改为源表名6、删除原表7、删除触发器
