MySQL在线DDL原理

评论

一、在线DDL概述及发展历程

DDL（Data Definition Language）是数据库内部的对象进行创建、删除、修改的操作语言，主要包括：加减列、更改列类型、加减索引等类型。数据库的模式(schema)会随着业务的发展不断变化，如果没有高效的DDL功能，每一次变更都有可能影响业务，甚至产生故障。在早期的 MySQL 版本中，DDL 操作（如创建索引等）通常都需要对数据表加锁，操作过程中 DML 操作都会被阻塞，影响正常业务。从 MySQL 5.6官方开始支持 ALTER TABLE 类型操作来避免数据拷贝，同时支持了在线上 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL。然而并不是所有的 DDL 操作都支持在线操作。到了 MySQL 5.7，在 5.6 的基础上又增加了一些新的特性，比如：增加了重命名索引支持，支持了数值类型长度的增大和减小，支持了 VARCHAR 类型的在线增大等。但是基本的实现逻辑和限制条件相比 5.6 并没有大的变化。MySQL 8.0 对 DDL 的实现重新进行了设计，其中一个最大的改进是 DDL 操作支持了原子特性，去掉server层保存成frm文件，统一保存InnoDB的数据字典中。MySQL8.0.12对Online DDL 的 ALGORITHM 参数增加了一个新的选项：INSTANT，只需修改数据字典中的元数据，无需拷贝数据也无需重建表，原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的，也不会阻塞 DML，仅修改元数据，MySQL8.0.29支持任意位置增加列，删除列。

二、语法

执行DDL的ALTER语句增加了新的关键字INSTANT，用户可以显式地指定，MySQL也会自动选择合适的算法，因此INSTANT DDL对用户是透明的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

alter_option: {
    table_options
| ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
| ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
        (key_part,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
| ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
| ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
| DROP {CHECK | CONSTRAINT} symbol
| ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
| ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
| ALTER [COLUMN] col_name {
        SET DEFAULT {literal | (expr)}
    | SET {VISIBLE | INVISIBLE}
    | DROP DEFAULT
    }
| ALTER INDEX index_name {VISIBLE | INVISIBLE}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| {DISABLE | ENABLE} KEYS
| {DISCARD | IMPORT} TABLESPACE
| DROP [COLUMN] col_name
| DROP {INDEX | KEY} index_name
| DROP PRIMARY KEY
| DROP FOREIGN KEY fk_symbol
| FORCE
| LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
| ORDER BY col_name [, col_name] ...
| RENAME COLUMN old_col_name TO new_col_name
| RENAME {INDEX | KEY} old_index_name TO new_index_name
| RENAME [TO | AS] new_tbl_name
| {WITHOUT | WITH} VALIDATION
}

备注:
1.DEFAULT：MySQL自己选择锁定资源最少的方式
2.INSTANT：只需要更新数据字典中的元数据, 很快完成
3.INPLACE：此变更由InnoDB引擎独立完成, 不需要使用Redo log等, 可以节省开销
4.COPY：此变更会重建聚簇索引, 执行DDL的时候会创建临时表

常见DDL操作:

  • 二级索引

说明：第一次添加全文索引字段时需要重建表，之后就不需要了

  • 主键
  • 普通列

说明：重建聚簇索引总是需要拷贝表数据（InnoDB 是“索引组织表”），所以最好是在创建表的时候就定义好主键。如果创建表是没有指定主键。InnoDB 会选择第一个 NOT NULL 的 UNIQUE 索引作为主键，或者使用系统生成的 KEY。对聚簇索引来说，使用 INPLACE 模式比 COPY 模式要高效一些：不会产生 undo log 和 redo log，二级索引是有序的，所以可以按顺序加载，不需要使用变更缓冲区

说明：

并发 DML：当插入一个自增列时，不支持并发的 DML 操作，添加自增列时，大量的数据会被重新组织，代价高昂

重建表：添加列时，MySQL 8.0.12 之前版本需要重建表， MySQL 8.0.12 ALGORITHM=INSTANT 时不需要重建

INSTANT算法：添加列时，使用 INSTANT 算法有下面这些限制

  • 添加列操作不能和其它不支持 INSTANT 算法的操作合并为一条 ALTER TABLE 语句
  • 新增的列只能添加到表的最后，不能放到其它列的前面，在 MySQL 8.0.29 之后，支持在任意位置添加列
  • 不能将列添加到 ROW_FORMAT=COMPRESSED 的表中
  • 不能将列添加到包含 FULLTEXT 的表中
  • 不能将列添加到临时表中，临时表只支持 ALGORITHM=COPY
  • 不能将列添加到驻留在数据字典表空间中的表中
  • 在添加列的时候不会计算行的大小限制，该限制在执行 DML 操作插入或者更新表时才会被检查

删除列时，大量的数据需要被重新组织，代价高昂，在 MySQl8.0.29 之后，删除列支持 INSTANT 算法

重命名列时，确保只改变列名，不改变数据类型，这样才能支持并发的 DML 操作

扩展 VARCHAR 长度时，INPLACE 是有条件的，必须保证用于标识字符串长度的长度字节不变（这里说的都是字节，不是 VARCHAR 的字符长度，字节占用与采用的字符集有关，utf8 字符集下，一个字符占 3 个字节， utf8mb4 则 4 个字节）,当 VARCHAR 列长度在 0-255 个字节时，长度标识占用一个字节,当 VARCHAR 列长度大于 255 个字节时，长度标识占用两个字节。因此，INPLACE 只支持 0-255 个字节之间或者 256 个字节到更大的长度之间的变更。VARCHAR 列长度减小是不支持 INPLACE 的。

自增列值变更是修改的内存中的值，不是数据文件

设置列为 [NOT] NULL 时，大量的数据被重新组织，代价高昂

修改 ENUM 和 SET 类型的列定义时，是否需要表拷贝取决于已有元素的个数和插入成员的位置

  • 表

说明：
当字符集不同时，需要重建表

如果表中包含 FULLTEXT 的字段，则不支持 INPLACE

三、在线DDL执行流程 (MySQL5.7为例)

INPLACE变更流程

准备阶段
  • 创建新的临时frm文件
  • 持有EXCLUSIVE-MDL锁，禁止读写（DML不能并行）
  • 根据alter类型，确定执行方式
  • 更新数据字典的内存对象
  • 分配row_log对象记录增量（no-rebuild不需要）
  • 生成新的临时ibd文件（no-rebuild不需要）
    执行阶段
  • 降级EXCLUSIVE-MDL锁，允许读写
  • 扫描old_table的聚集索引每一条记录rec
  • 遍历新表的聚集索引和二级索引，逐一处理
  • 根据rec构造对应的索引项
  • 将构造索引项插入sort_buffer块
  • 将sort_buffer块插入新的索引
  • 处理ddl执行过程中产生的增量(仅rebuild类型需要)
    提交阶段
  • 升级到EXCLUSIVE-MDL锁，禁止读写（DML不能并行）
  • 重做最后row_log中最后一部分增量（no-rebuild不需要）
  • 更新innodb的数据字典表
  • 提交事务(刷事务的redo日志)
  • 修改统计信息
  • rename临时idb文件，frm文件(仅rebuild类型需要)
  • 变更完成

    COPY变更流程

    准备阶段
  • 对表元数据加MDL-S (共享锁)，读取元数据（DDL不并行，DML并行）
  • MDL-S 升级为MDL-F锁，禁止读写（DDL,DML不能并行）
  • 在SERVICE层通过create like 语句，创建临时表，Engine层生成对应的ibd,frm文件(8.0后没有frm文件)
    执行阶段
  • 修改临时表元数据(表结构)
  • 原表数据拷贝到临时表()
  • 删除原表及文件，重命名临时表及文件
    提交阶段
  • commit提交事务，释放锁。

    四、举例说明加字段的执行流程(MySQL5.7.31)

    alter table sbtest99 add column name2 varchar(32) not null defalt ‘’ after id; （重建主键，并且会重建所有的二级索引）在MySQL执行过程的主要流程
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    do_command(THD*)      --从连接中读取命令
    dispatch_command   --分发命令
    mysql_parse     
    mysql_execute_command   --命令执行
      Sql_cmd_alter_table::execute  服务层  
          check_access
          check_table_access
          check_grant      
          mysql_alter_table
              MDL_REQUEST_INIT
              mysql_prepare_alter_table
              create_table_impl                               服务层创建临时frm表
              check_if_supported_inplace_alter                检查是否支持INPLACE
              mysql_inplace_alter_table
                  handler::ha_prepare_inplace_alter_table                  
                      ha_innobase::prepare_inplace_alter_table innodb层执行对应准备阶段
                         prepare_inplace_alter_table_dict
                           innodbase_trx_allocte
                           trx_start_for_ddl
                           row_mysl_lock_dta_dictionary
                           row_create_table_for_mysql          innodb层执行增加#tmp开头ibd文件
                           dict_table_open_on_name
                           trx_commit_for_mysql
                           row_mysql_unlock_dictionary
                           row_log_allocate
                  handler::ha_inplace_alter_table
                      ha_innobase::inplace_alter_table         innodb层执行 对应执行阶段
                         row_merge_build_indexes               重建所有二级索引     
                             row_merge_read_clustered_index    读集群索引生成二级索引
                                row_merge_buf_sort
                                row_merge_insert_index_tuples  插入到新表(聚集索引+二级索引)
                                BtrBulk::insert
                                BtrBulk::finish
                                PageBulk::insert                                             
                             row_log_table_apply               应用表日志      
                              row_log_table_apply_ops
                                row_log_table_apply_op
                                  row_log_table_apply_delete
                                  row_log_table_apply_insert
                                    row_log_table_apply_convert_mrec
                                    row_log_table_apply_insert_low
                                       row_ins_clust_index_entry_low 插入集群索引
                                       row_ins_sec_index_entry_low   插入二级索引
                                  row_log_table_apply_update
                  handler::ha_commit_inplace_alter_table
                      ha_innobase::commit_inplace_alter_table   innodb层执行提交 对应提交
                         commit_try_rebuild                     提交重建事务                   
                             row_log_table_apply                最后应用日志
                             row_merge_rename_tables_dict       更新字典
                         row_merge_drop_table                   innodb合并ibd文件
                         mysql_rename_table                     服务层合并frm文件

    五、DML锁排查方式

    在线上进行DDL操作时，相对于其可能带来的系统负载，其实我们最担心的还是MDL其可能导致的阻塞问题。
    一旦DDL操作因获取不到MDL被阻塞，后续其它针对该表的其它操作都会被阻塞。如阻塞稍久的话，我们会看到Threads_running飙升，CPU告警。

针对MySQL MDL锁如何排查
引起MDL锁原因: 慢查询 ,表上有事务未提交

MySQL5.7+
首先，打开metadata locks的tracing功能。如何找出引起阻塞的会话

select
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
    a.object_schema as locked_schema,
    a.object_name as locked_table,
    'metadata lock' as locked_type,
    c.processlist_id as waiting_processlist_id,
    c.processlist_time as waiting_age,
    c.processlist_info as waiting_query,
    c.processlist_state as waiting_state,
    d.processlist_id as blocking_processlist_id,
    d.processlist_time as blocking_age,
    d.processlist_info as blocking_query,
    concat('kill ', d.processlist_id) as sql_kill_blocking_connection
from
    performance_schema.metadata_locks a
join performance_schema.metadata_locks b on a.object_schema = b.object_schema
and a.object_name = b.object_name
and a.lock_status = 'pending'
and b.lock_status = 'granted'
and a.owner_thread_id <> b.owner_thread_id
and a.lock_type = 'exclusive'
join performance_schema.threads c on a.owner_thread_id = c.thread_id
join performance_schema.threads d on b.owner_thread_id = d.thread_id;


select concat('kill ', i.trx_mysql_thread_id, ';')
from information_schema.innodb_trx i, (
    select max(time) as max_time
    from information_schema.processlist
    where state = 'waiting for table metadata lock'
      and (info like 'alter%'
      or info like 'create%'
      or info like 'drop%'
      or info like 'truncate%'
      or info like 'rename%'
  )) p
where timestampdiff(second, i.trx_started, now()) > p.max_time;

MySQL5.6

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT concat('kill ', i.trx_mysql_thread_id, ';')
FROM information_schema.innodb_trx i, (
    SELECT MAX(time) AS max_time
    FROM information_schema.processlist
    WHERE state = 'Waiting for table metadata lock'
      AND (info LIKE 'alter%'
      OR info LIKE 'create%'
      OR info LIKE 'drop%'
      OR info LIKE 'truncate%'
      OR info LIKE 'rename%'
  )) p
WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;

select object_type, object_schema, object_name, lock_type, lock_status, thread_id, processlist_id, processlist_info from performance_schema.metadata_locks inner join performance_schema.threads on thread_id = owner_thread_id where processlist_id <> connection_id();  
select * from information_schema.innodb_trx;
show full processlist

六、MySQL5.7跟踪DDL进度

启用跟踪配置可以动态开启

1
2
3
4
5
6
7
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执行进度

1
2
3
4
5
6
7
8
9
10
11
12
13
select stmt.sql_text,
       stage.event_name,
       concat(work_completed, '/', work_estimated) as progress,
       concat(round(100 * work_completed / work_estimated, 2), ' %') as processing_pct,
       sys.format_time(stage.timer_wait) as time_costs,
       concat(round((stage.timer_end - stmt.timer_start) / 1e12 *
                    (work_estimated - work_completed) / work_completed,
                    2),
              ' s') as remaining_seconds
  from performance_schema.events_stages_current     stage,
       performance_schema.events_statements_current stmt
 where stage.thread_id = stmt.thread_id
   and stage.nesting_event_id = stmt.event_id

七、总结

  • 严格按照数据库管理规范，特别是建表要有主键，字符集统一，避免COPY模式变更的。
  • 对于Online DDL选择闲时执行，确保对系统影响少。
  • 添加列时评估行大小限制。(在插入和更新表中的行的 DML 操作期间会检查行大小限制)。
  • 对于Online DDL INPLACE方式 执行对于重建表的操作要占用磁盘空间，确保磁盘空间足够。
  • 对于COPY方式，特别是大表建议使用gh-ost工具进行DDL变更，不阻塞DML语句。
  • 对于DDL操作要关注要主从延迟及DML锁的情况。

八、参考

gladlyDBA

个人简介。