MySQL online DDl原理

online DDL从5.6开始，不阻塞DML但是会阻塞所有的DDL，online有三种模式： INSTANT(8.0.12),INPLACE(rebuild),INPLACE(no-rebuild)，具体操作如下：

1、只修改表的元数据信息

1. 删除二级索引
2. 修改索引名（5.7）
3. 修改字段名
4. 设置（删除）字段的默认值
5. 增加varchar长度，如果表示字符串长度的字节数变化则会使用copy算法。如果减少varchar长度则不管是哪个版本都会使用copy算法
6. 修改自增主键的值，8.0没有持久化之前，这里修改的是内存的值
7. 重命名表
8. 添加外键，如果foreign_key_checks为off则只更新元数据，其他情况，包括删除外键都会使用copy算法

2、INPLACE方式

这种模式下，执行时间长度和表的大小成正比，执行过程会拷贝原表数据，会在原表的当前目录下创建一个临时的frm和ibd，可以通过监控临时文件的大小监控online ddl的进度

1. 创建索引，属于inplace no-rebuild
2. 添加主键
3. 删除主键并添加另外一个主键，altert table t drop primary key, add primary key(id),如果只是删除主键则只能使用copy算法
4. 增加字段，8.0.12开始瞬间完成，但是只能添加到末尾，从8.0.29开始可以添加到任何位置，使用的instant方式，当增加自增列时会阻塞DML
5. 删除字段
6. 调整字段顺序
7. 修改表的属性比如：row_format=compressed key_block_size=8
8. 修改字段的null属性
9. optimize table
10. alter table force / alter table engine = innodb

3、COPY

1. 修改字段定义，比如：调整字段类型， 调整varchar(255) --> varchar(256)
2. 删除主键
3. 转换字符集 alter table t convert to character set xxx collate xx

如何检查DDL的进度：

1、开启DDL相关的时间采集项
mysql> update performance_schema.setup_instruments set enabled ='yes' where name like 'stage/innodb/alter%';
Query OK, 0 rows affected (0.27 sec)
Rows matched: 8  Changed: 0  Warnings: 0

mysql> select * from performance_schema.setup_instruments where  name like 'stage/innodb/alter%';
+------------------------------------------------------+---------+-------+------------+------------+---------------+
| NAME                                                 | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION |
+------------------------------------------------------+---------+-------+------------+------------+---------------+
| stage/innodb/alter table (end)                       | YES     | YES   | progress   |          0 | NULL          |
| stage/innodb/alter table (flush)                     | YES     | YES   | progress   |          0 | NULL          |
| stage/innodb/alter table (insert)                    | YES     | YES   | progress   |          0 | NULL          |
| stage/innodb/alter table (log apply index)           | YES     | YES   | progress   |          0 | NULL          |
| stage/innodb/alter table (log apply table)           | YES     | YES   | progress   |          0 | NULL          |
| stage/innodb/alter table (merge sort)                | YES     | YES   | progress   |          0 | NULL          |
| stage/innodb/alter table (read PK and internal sort) | YES     | YES   | progress   |          0 | NULL          |
| stage/innodb/alter tablespace (encryption)           | YES     | YES   | progress   |          0 | NULL          |
+------------------------------------------------------+---------+-------+------------+------------+---------------+
8 rows in set (0.01 sec)
//以上8项代表online ddl的8个阶段，这个测试例子是在8.0中执行的

2、开启时间状态表
mysql> update performance_schema.setup_consumers set enabled='yes' where name like '%stages%';
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from performance_schema.setup_consumers where name like '%stages%';
+----------------------------+---------+
| NAME                       | ENABLED |
+----------------------------+---------+
| events_stages_current      | YES     |
| events_stages_history      | YES     |
| events_stages_history_long | YES     |
+----------------------------+---------+
3 rows in set (0.00 sec)

3、查看当前DDL进度,历史事件在events_stages_history表中
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 |         127850 |        1305412 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)

/*
event_name： 正在执行的事件名称，对于copy模式只有这一个事件，而inplace方式下会对应8个事件
work_completed：已经完成的工作量
work_estimated：整个DDL需要的工作量，预估值，随着时间的执行动态变化
*/

还有另外一种方式是查看sys.session表：

mysql> select * from session where conn_id = 64\G
*************************** 1. row ***************************
                thd_id: 132
               conn_id: 64
                  user: root@localhost
                    db: test
               command: Query
                 state: copy to tmp table
                  time: 527
     current_statement: alter table orders engine = innodb
     statement_latency: 8.79 m
              progress: 47.06
          lock_latency: 38.60 ms
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
        current_memory: -135676819 bytes
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 8.79 m
             trx_state: ACTIVE
        trx_autocommit: NO
                   pid: 92489
          program_name: mysql
1 row in set, 1 warning (0.40 sec)

一个小提示

• 8.0.12开始支持秒级加列，只能添加的末尾，从8.0.29开始可以加到任意位置。如果一个表通过instant增加列或者删除列，则会在information_schema.innodb_columns的total_row_versions记录版本号，如果超过64则会报错
• 秒级加列instant支持压缩表
• 只支持独立表空间
• 和不支持instant的DDL在一条语句中执行，也不能秒级加列