导读：如何查看DDL的进度？
使用MariaDB/Percona版本的一个便利之处就是可以及时查看DDL的进度，进而预估DDL耗时。
其实，在官方版本里也是可以查看DDL进度的，认真看手册的同学就能发现手册中有提到过：
You can monitor ALTER TABLE progress for InnoDB tables using Performance Schema.
如何开启
需要启用performance_schema，并设置2个地方：
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
测试
- ddl(copy方式)
mysql> select count(1) from sbtest2;
+----------+
| count(1) |
+----------+
| 9998951 |
+----------+
1 row in set (2.10 sec)
mysql> alter table sysbench.sbtest2 ALGORITHM=COPY, add test7 varchar(20) not null default 'test7';
另一个窗口查看进度:
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+-----------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------+----------------+----------------+
| stage/sql/copy to tmp table | 126959 | 9842742 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+-----------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------+----------------+----------------+
| stage/sql/copy to tmp table | 4494926 | 9842742 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+-----------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------+----------------+----------------+
| stage/sql/copy to tmp table | 8398679 | 9842742 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
Empty set (0.00 sec)
- ddl(inplace方式)
mysql> alter table sysbench.sbtest2 add test8 varchar(20) not null default 'test8';
另一个窗口查看
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) | 226282 | 703674 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+-----------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------------+----------------+----------------+
| stage/innodb/alter table (insert) | 988006 | 1210970 |
+-----------------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+----------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+----------------------------------+----------------+----------------+
| stage/innodb/alter table (flush) | 1184776 | 1184776 |
+----------------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+----------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+----------------------------------+----------------+----------------+
| stage/innodb/alter table (flush) | 1205192 | 1205192 |
+----------------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
Empty set (0.00 sec)
最后，也可以查看 events_stages_history 里记录的完整过程：
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (merge sort) | 896692 | 1210970 |
| stage/innodb/alter table (insert) | 1179932 | 1179932 |
| stage/innodb/alter table (flush) | 1219397 | 1219397 |
| stage/innodb/alter table (log apply table) | 1219781 | 1219781 |
| stage/innodb/alter table (end) | 1219781 | 1219781 |
| stage/innodb/alter table (log apply table) | 1220165 | 1220165 |
| stage/innodb/alter table (end) | 1154899 | 1154899 |
| stage/innodb/alter table (log apply table) | 1155283 | 1155283 |
| stage/sql/copy to tmp table | 9998951 | 9842742 |
| stage/innodb/alter table (read PK and internal sort) | 566352 | 703674 |
+------------------------------------------------------+----------------+----------------+
10 rows in set (0.00 sec)
一个ddl的执行过程(inplace)
从上面的结果我们也能看到，一个DDL执行过程包括下面几个主要阶段：
stage/innodb/alter table (read PK and internal sort)，读取主键（聚集索引），计算需要处理的data page数；
stage/innodb/alter table (merge sort)，处理ALTER TABLE影响的索引，每个索引跑一次（含主键索引）；
stage/innodb/alter table (insert)，同上；
stage/innodb/alter table (log apply index)，将执行DDL期间新增的DML操作应用到index上；
stage/innodb/alter table (flush)，flush阶段；
stage/innodb/alter table (log apply table)，将执行DDL期间新增的DML操作应用到table上；
stage/innodb/alter table (end)，收尾阶段。
局限
- 只支持MySQL 5.7+的版本
- 只支持InnoDB引擎表；
- 不支持spatial indexes。
查看当前所有ALTER TABLE的进度及其对应的ddl sql
mysql> SELECT ec.THREAD_ID, ec.EVENT_NAME, ec.WORK_COMPLETED, ec.WORK_ESTIMATED, pt.STATE, pt.INFO FROM performance_schema.events_stages_current ec left join performance_schema.threads th on ec.thread_id = th.thread_id left join information_schema.PROCESSLIST pt on th.PROCESSLIST_ID = pt.ID where pt.INFO like 'ALTER%'\G
*************************** 1. row ***************************
THREAD_ID: 1258
EVENT_NAME: stage/innodb/alter table (read PK and internal sort)
WORK_COMPLETED: 11366
WORK_ESTIMATED: 737631
STATE: altering table
INFO: alter table sysbench.sbtest2 add test9 varchar(20) not null default 'test9'
*************************** 2. row ***************************
THREAD_ID: 1314
EVENT_NAME: stage/innodb/alter table (read PK and internal sort)
WORK_COMPLETED: 22970
WORK_ESTIMATED: 197835
STATE: altering table
INFO: alter table sbtest3 add test1 varchar(257) not null default 'test1'
2 rows in set (0.00 sec)
