社区首页 >专栏 >MySQL 实战笔记第04期：alter table 语句进度评估

MySQL 实战笔记第04期：alter table 语句进度评估

数据库交流

发布于 2022-04-25 01:00:11

1.3K00

代码可运行

文章被收录于专栏：悦专栏悦专栏

运行总次数：0

代码可运行

作者简介

无为，多年 MySQL DBA 工作经验，现就职于某知名互联网公司，对 MySQL、 Redis、PostgrepSQL 等主流数据库有一定了解，拥有丰富的一线运维经验。

有没有一种途径能让我们随时能了解 alter table 语句的执行状况呢？

答案是肯定的，今天我们就来一起学习下 MySQL alter table 语句进度评估。

1 官方文档

首先我们来看下官方文档对 alter table 语句的解释。

alter table 语句有七个不同的阶段事件，每个事件在其不同的阶段执行，具体如下：

stage/innodb/alter table (read PK and internal sort)：当 ALTER TABLE 处于读取主键阶段时，此阶段处于活动状态。它以 WORK_COMPLETED=0 和 WORK_ESTIMATED 设置为主键中的估计页数开头。阶段完成后，WORK_ESTIMATED 将更新为主键中的实际页数。
stage/innodb/alter table (merge sort)：对 ALTER TABLE 操作添加的每个索引重复此阶段。
stage/innodb/alter table (insert)：对 ALTER TABLE 操作添加的每个索引重复此阶段。
stage/innodb/alter table (log apply index)：此阶段包括在 ALTER TABLE 运行时生成的 DML 日志的应用。
stage/innodb/alter table (flush)：在此阶段开始之前，将根据刷新列 table 的长度以更准确的估算值更新 WORK_ESTIMATED。
stage/innodb/alter table (log apply table)：此阶段包括应用程序运行 ALTER TABLE 时生成的并发 DML 日志。此阶段的持续时间取决于 table 更改的程度。如果未在 table 上运行任何并发 DML，则此阶段是即时的。
stage/innodb/alter table (end)：包括刷新阶段之后出现的所有剩余工作，例如重新应用在 ALTER TABLE 运行时在 table 上执行的 DML。

2 示例

下面我们通过实验来试试 alter table 的进度查询

首先启用 stage/innodb/alter%：

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES'
       WHERE NAME LIKE 'stage/innodb/alter%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

小贴士 那开启 performance_schema 会不会影响性能呢？答：会影响性能，但我们只开了 stage/innodb/alter% 的，因此影响可以忽略不计

启用阶段事件监听，其中包括 events_stages_current ，events_stages_history 和 events_stages_history_long。

mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

运行 alter table 操作。在此示例中，将 middle_name 列添加到 employee 示例数据库的 employee 表中。

mysql> ALTER TABLE employees.employees ADD COLUMN middle_name varchar(14) AFTER first_name;
Query OK, 0 rows affected (9.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

进度查询

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) |            280 |           1245 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.01 sec)

如果 alter table 语句已经完成，则 events_stages_current 会返回一个空集，这种情况下可以查询 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 (read PK and internal sort) |            886 |           1213 |
| stage/innodb/alter table (flush)                     |           1213 |           1213 |
| stage/innodb/alter table (log apply table)           |           1597 |           1597 |
| stage/innodb/alter table (end)                       |           1597 |           1597 |
| stage/innodb/alter table (log apply table)           |           1981 |           1981 |
+------------------------------------------------------+----------------+----------------+
5 rows in set (0.00 sec)