測試環境：
MySQL5.7
pt-online-schema-change 3.2.0
第一次加索引，使用MySQL原生的online DDL。
做一個超級慢的update，模拟長事務。
此時開始加索引：
檢視現場狀态：
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> 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> 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
做一次批量更新，模拟長事務：
使用 pt-online-schema-change删除索引：
[[email protected] ~]# 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時候，後邊所有會話都堆積的情況，避免影響業務。
檢視删除索引全部日志：
[[email protected] ~]# pt-online-schema-change --version
pt-online-schema-change 3.2.0
[[email protected] ~]# 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、删除觸發器
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)