Skip to content

Files

Latest commit

littlenekolittleneko
Saved 2022年11月15日 星期二 00时11分31秒 CST
Nov 14, 2022
08def65 · Nov 14, 2022

History

History
44 lines (34 loc) · 4.12 KB

1. MySQL DDL.md

File metadata and controls

44 lines (34 loc) · 4.12 KB

MySQL 5.5 版本及之前版本的 DDL 实现方式

上图不难看出，5.5 及之前版本 DDL 实现的方式存在如下问题：

  1. copy data 的过程需要耗费额外的存储空间，并且耗时很长。
  2. copy data 的过程有写锁，无法持续对业务提供正常服务。

虽然在 MySQL5.5 版本中增加了 IN-Place 方式，但依然会阻塞 INSERT、UPDATE、DELETE 操作。

MySQL 5.6 和 5.7 的 Online DDL

MySQL 5.6 的时候，支持了在线上 部分 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL，在 5.7 的时候又增加了更多的 Online DDL 操作。具体的支持可以查看 MySQL 的官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

现在，MySQL 的创建/删除索引（非全文索引）、加减列、更改列名、增加 varchar 的长度等操作已经支持 online，即操作过程中表是可以正常读写的；但是比如说更改列类型操作是不支持 online 的。

对于官方文档中一些名词的解释：

  • **In Place：**所有操作都在 InnoDB 引擎层完成，不需要经过临时表的中转
  • Rebuilds Table：涉及到表的重建，在原表路径下创建新的 .frm 和 .ibd 文件，消耗的 IO 会较多。期间（原表可以修改）会申请 row log 空间记录 DDL 执行期间的 DML 操作，这部分操作会在 DDL 提交阶段应用新的表空间中
  • **Permits Concurrent DML：**是否 online
  • Only Modifies Metadata

MySQL 5.7 Online DDL 流程

Online DDL operations can be viewed as having three phases:

  • Phase 1: Initialization In the initialization phase, the server determines how much concurrency is permitted during the operation, taking into account storage engine capabilities, operations specified in the statement, and user-specified ALGORITHM and LOCK options. During this phase, a shared upgradeable metadata lock is taken to protect the current table definition.
  • Phase 2: Execution In this phase, the statement is prepared and executed. Whether the metadata lock is upgraded to exclusive depends on the factors assessed in the initialization phase. If an exclusive metadata lock is required, it is only taken briefly during statement preparation.
  • Phase 3: Commit Table Definition In the commit table definition phase, the metadata lock is upgraded to exclusive to evict the old table definition and commit the new one. Once granted, the duration of the exclusive metadata lock is brief.

MySQL 5.7 Online DDL 使用限制与问题

  1. 仍然存在排他锁，有锁等待的风险。
  2. 跟 5.6 一样，增量日志大小是有限制的（由 innodb_online_alter_log_max_size 参数决定大小）
  3. 有可能造成主从延迟
  4. 无法暂停，只能中断

在 DDL 期间产生的数据，会按照正常操作一样，写入原表，记 redolog、undolog、binlog，并同步到从库去执行，只是额外会记录在 row log 中，并且写入 row log 的操作本身也会记录 redolog，而在提交阶段才进行 row log 重做，此阶段会锁表，此时主库（新表空间+row log）和从库（表空间）数据是一致的，在主库DDL 操作执行完成并提交，这个 DDL 才会写入 binlog 传到从库执行，在从库执行该 DDL 时，这个 DDL 对于从库本地来讲仍然是 online 的，也就是在从库本地直接写入数据是不会阻塞的，也会像主库一样产生 row log。但是对于主库同步过来 DML，此时会被阻塞，是 offline 的，DDL 是排他锁的在复制线程中也是一样，所以不只会阻塞该表，而是后续所有从主库同步过来的操作（主要是在复制线程并行时会排他，同一时间只有他自己在执行）。所以大表的 DDL 操作，会造成同步延迟。

其他 Online DDL 工具

PT-OSC（Percona Toolkit Online Schema Change）/Facebook-OSC

存在的风险：

  1. 触发器开销
  2. 触发器与原语句在同一个事物
  3. 无法暂停和限流